aaberg / sql2o Goto Github PK

Also, column name should not be case sensitive, if sql2o not is in casesensitive property is false.

The addParameter() method for DateTime converts the value to a java.util.Date which then is converted to java.sql.Date, and the time factor is lost.

Joda DateTime should expect to carry a time value through to the database. Joda's LocalDate should be used when saving just a date without any time.

Is there a reason sql2o is on your own maven repo and not the central repo?

when using the addParameter(String, Date) method, db2 will fail when column is date datatype

Got the following problem with version Sql2o-0.2.8.jar

    ... 8 more

Caused by: java.lang.NullPointerException
at org.sql2o.Query.setField(Query.java:201)
at org.sql2o.Query.executeAndFetch(Query.java:271)
... 11 more

Sql2o is opening and closing the connection for every statement (Except when running in a transaction).
It should be possible to run multiple statements on the same connection

Currently JodaTimeConverter does this:

return new DateTime(val,DateTimeZone.UTC);

My DATETIMEs in mysql are stored in UTC, and my JVM default timezone is EST. Because of this, the Joda DateTime being returned is off. Joda is treating val as EST (my JVM timezone), and then converting it to UTC by shifting it based on my local timezone.

This gives me the desired result:

return new LocalDateTime(val).toDateTime(DateTimeZone.UTC);

Should this be the default, or should I just write my own converter?

I think quirks must be not just database-related but "application"-related.
Different projects have different coding|naming standarts|convections (different column naming scheme). Different projects have different type standarts|convections (VARCHAR vs NVARCHAR, CLOB vs NCLOB).

We need a way to tweak it once and use project-wide

Some of the Query.addParameter() overloads will throw a NullPointerException if the parameter value is set to NULL.

Ideally Query.executeAndFetchFirst would return after reading the first row from ResultSet, instead of reading the whole thing into a list and returning the first item.

This could be handled as follows:

1. make a new method "executeAndFetchIterable" that returns Iterable instead of List
2. have "executeAndFetch" call the iterable method and flush the result to List
3. have "executeAndFetchFirst" call the iterable method and only grab the first item

This lazy fetching would also improve the API, because right now if I select a million rows, I might run out of memory by calling executeAndFetch. But if an Iterable was returned, I could take responsibility and handle the million rows accordingly (maybe I am filling a table view dynamically or something, you get the idea).

I'm happy to take a stab at this if you'd like. It could get verbose (oh C#, how I miss "yield return"), but it shouldn't be bad. Also it wouldn't be difficult at all with something like Guava or commons-collection, but it doesn't look like you use either of these and I think that makes sense.

I'm newbie with GIT
I want to reset/resync my fork to current @aaberg repository (to get @aldenquimby changes)

MSSQL have UNIQUEIDENTIFIER type
ORACLE use RAW[16]
JDBC in/out as byte array

the problem is UUID is not just 16 bytes - it is
DWORDDWORD-WORD-WORD-WORD-WORDDWORDDWORD
so for MSSQL you must pass|recieve it in little-endian...
ORACLE at other side just store it as RAW data so you doesn't need to play with endianess

instead, it should use jodatime if available.

Instead it should use the dependency if available or fall back to java.util.Logger if not.

I try to map an Oracle CLOB column to a String.
Sql2o called toString on the Clob-object and gets a string ala "com.oracle.Clob@6756767".

I worked around the problem like this:

    private static class ClobConverter extends StringConverter {
        @Override
        public String convert(Object o) {
            if ( o instanceof Clob) {
                Clob clob = (Clob)o;
                try {
                    return clob.getSubString(1, (int)clob.length());
                } catch (SQLException e) {
                    throw new RuntimeException("Error reading clob-value", e);
                }
            } else {
                return super.convert(o);
            }
        }
    }

and

Convert.registerConverter(String.class, new ClobConverter());

AFAIK Oracle HotSpot can unload JIT-generated code since it become "cold". You must warmup each method just before time measurement.

Row.getObject method, should have an overload that uses the registered converters.

In Sql2o classe, I found a little mistake

private static DataSource _getJndiDatasource(String jndiLookup) {
    Context ctx = null;
    DataSource datasource = null;

    try {
        InitialContext context = new InitialContext();
        datasource = (DataSource) context.lookup(jndiLookup);
    }
    catch (NamingException e) {
        throw new RuntimeException(e);
    }
    finally {
        if (ctx != null) {
            try {
                ctx.close();
            }
            catch (NamingException e) {
                throw new RuntimeException(e);
            }
        }
    }

    return datasource;
}

InitialContext context = new InitialContext(); => ctx = new InitialContext();

Your project is great, simple and cool :-)

dmex38

Or maybe the name should be executeAndFetchTableLazy?
Or executeAndMap?
Or maybe an overload of the executeAndFetchLazy with no parameters?

Either way, it should return a ResultSetIterable<Row>, or something similar.

Right now, converters are used when setting a property in java on the way out of sql. I think they should go both directions, because for any custom converters, the reverse logic is usually needed to convert a java property into the appropriate format for inserting into sql.

For example, if I had a field on a POJO of type java.time.Year (I use java 8), and a corresponding INT column in the database, I would add the following converter right now (ignoring exceptions for simplicity):

public class YearConverter implements Converter<Year>
{
    @Override
    public Year convert(Object val) throws ConverterException
    {
        return Year.of((int) val);
    }
}

However I would also need to add some custom code before calling Query#addParameter():

if (Year.class.equals(value.getClass()))
{
    int actualValueToPutInDatabase = ((Year)value).getValue();
    // now addParameter
}

I think this logic should live in the converter. Here are two possibilities for how that could work:

• to/from database methods, make sql2o do addParameter work:

public class YearConverter implements Converter<Year>
{
    @Override
    public Year fromDatabase(Object val) throws ConverterException
    {
        // equivalent of current "convert"
    }

    @Override
    public Object toDatabase(Year val)
    {
        return val.getValue();
    }
}

For this to work, sql2o would have to check for a registered converter when Query#addParameter() is called, then call #toDatabase(), then call #addParameter() with the result.

• let uses do addParameter directly:

public class YearConverter implements Converter<Year>
{
    @Override
    public Year convert(Object val) throws ConverterException
    {
        // equivalent of current "convert"
    }

    @Override
    public void addParameter(NamedParameterStatement stmt, String name, Year val) throws SQLException
    {
        stmt.setInt(name, val.getValue());
    }
}

For this to work, sql2o would have to check for a registered converter when Query#addParameter() is called, then call the converter's #addParameter().

From a users perspective, I think the first approach is much cleaner and clearer, because the job of the converter is to go to/from the database. However it would be easier to implement the second approach, and it would give users more control over functionality.

I did this test on SQL Server but I asumme it will happend on Oracle and other relational databases.

If we have a stored procedure that have output parameters and it returns a set of data, there is no way to get the content of the parameters and the Cursor returned by the Stored procedure.

Example:

The following stored procedure have two output parameters and inside has a Select Statement:

CREATE PROCEDURE [dbo].[play_GetData]
@returnedStatus INT OUTPUT,
@returnedMessage VARCHAR(2000) OUTPUT
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
--* Assume the query is quite more complicated.
SELECT Column1, Column2, Column3
FROM TableName
--*
SET @returnedStatus = 0
SET @returnedMessage = 'SUCCESS'
END TRY
--
BEGIN CATCH
--
SET @returnedStatus = ERROR_NUMBER()
SET @returnedMessage = ERROR_MESSAGE()
RAISERROR(@returnedMessage, 16, 1)
--
END CATCH
--
END

There is no way to get both returned data and the updated content in the parameters.

Thanks!

Query class executeUpdate()
this.connection.setKeys(this.returnGeneratedKeys ?statement.getStatement().getGeneratedKeys() : null);

java.sql.SQLException: 不允许的操作

If an exception occures in the database, while executing batch update, the database connection is not closed correctly.

please tell me why is possible to NOT cache it

any proposed way control this?
ResultSet.getObject and PrepatredStatement.setObject don't enought :(

maybe instead of ResultSet.getObject->Converter.convert->Pojo.setProperty You need something like Extractor.extractValue(ResultSet)->Pojo.setProperty?

this will also solve ugly workaround for Oracle timestamp?

It's all right when used mysql. But throws exceptions when uses oracle 11.2.0.1. The driver is ojdbc6.jar. It's normal when I used PreparedStatement.

Caused by: java.lang.ArrayIndexOutOfBoundsException
at java.lang.System.arraycopy(Native Method)
at oracle.jdbc.driver.T4CRowidAccessor.unmarshalOneRow(T4CRowidAccessor.java:205)
at oracle.jdbc.driver.T4C8Oall.readRXD(T4C8Oall.java:648)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:328)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:205)
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1008)
at oracle.jdbc.driver.OraclePreparedStatement.executeForRowsWithTimeout(OraclePreparedStatement.java:9870)
at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:9974)
at oracle.jdbc.driver.OracleStatementWrapper.executeBatch(OracleStatementWrapper.java:213)
at org.sql2o.tools.NamedParameterStatement.executeBatch(NamedParameterStatement.java:340)
at org.sql2o.Query.executeBatch(Query.java:411)
... 6 more

If you have a base model class:

public class MyBasePojo {
    private int val1;

    public int getVal1() { return val1; }
}

And a class that extends it:

public class MyPojo extends MyBasePojo {
    private int val2;

    public int getVal2() { return val1; }
}

The following will fail:

MyPojo p = getNewInstanceOfMyPojo();
sql2oConnection.createQuery ("insert into mytable(va1, val2) values (:val1, :val2)")
    .bind(p)
    .executeUpdate();

It fails because properties from MyBasePojo are not binded. Only properties declared in the top-level class are binded.

Sometimes in a batch mode it is nice to do an asynchronous execution of data. Traditionally It could be achieved by spawning a thread manually or using executor service. However if you are doing this a lot in your application it ends up adding a lot of code bloat.

Apache Dbutils 2.0 actually started this but it doesn't look like Dbutils is that much active anymore. However for an up and coming library like sql2o it'll be a nice addition.

For anyone interested in checking out dbutils async support here is the link:

https://github.com/apache/commons-dbutils/blob/2_0/src/main/java/org/apache/commons/dbutils2/AsyncExecutor.java

I want to propose to re-design new (2.0) release version architecture|api using following principles

1. Separation of Concerns - each component must solve 1 concrete task, every component can be replaced by custom implementation
2. Using well-known GoF patterns instead self-made solutions (for example PojoMetadata is awful, it must be separated by PojoMetadataProvider && PojoMetadata)
3. Must not prevent to write plain-old JDBC-code (must be able to get Connection from sql2o.Connection, PreparedStatement from Query, ResultSet from IterableResultSet (since we can use third-party library wich consumes JDBC objects, as example - converting ResultSet to CSV)
4. Must be able to consume JDBC objects at any step (must be create sql2o.Connection from Connection, Query from PreparedStatement, must be able to transform ResultSet to all possible forms (List|Table|LazyTable|IterableResultSet (since sometimes we write not project-from-scratch but code wich obtains JDBC objects outside)

We are missing test-coverage for the reflection setter classes:

• org.sql2o.reflection.FieldSetter
• org.sql2o.reflection.MethodSetter
• org.sql2o.reflection.ReflectionFieldSetterFactory
• org.sql2o.reflection.ReflectionMethodSetterFactory
• org.sql2o.reflection.ReflectionObjectConstructorFactory

sorry for my poor english

I propose you to use ReflectASM or something like it for faster object creation and property assignment

1. convert FieldSetter to interface and use factory to obtain it
2. use ServiceLoader api to obtain FieldSetter factory failing back to yor current reflection-based implementation
3. allow to pass FieldSetter factory to sql2o objects (to override sql2o default behavior)
   this will allow to write third-party FieldSetters optimal for some JVM implementations

QuirksMode - this is really ugly (not Java-way) solution. Better way is to declare iterface Quirks and move all quirks-related code into interface methods. QuirksMode must implement this interface. This will allow to create third-party Quirks implementation for other databases|drivers

API - need easy API to invoke sql2o ResultSet->POJO code for someone who can't use sql2o "from the scratch" (sometimes you need to write code obtaining jdbc Connection or Statement or ResultSet from caller and you can't change caller code).

https://github.com/aaberg/sql2o/blob/master/core/src/main/java/org/sql2o/Query.java#L219

1. getDeclaredMethods - this means inheritance not work
2. absent static modifier check check for static modifier (is it "as designed" to allow static bean properties can be added to Query params?
3. absent private modifier check so adding a private property to bean will cause runtime exception (keep in mind you can share this bean with someone else, the bean code is not 100% under your control)
4. seems like you analyze method name and lowercase it just like java.beans.Introspector do - why not just reuse it?
5. you does not analyze fields so there are no way to pass single anonymous object in Dapper.NET style:

executeQuery("select @name, @age", new Object{
    int age = 31;
    String name = "dimzon"
})

some fixes will definitly cause backward compatibility breaks ;(
so I propose

1. keep old bind as is and mark it @Deprecated
2. write new method bindObject with proper implementation and behavior
3. to branch between "bean-style" and "dapper-style" use marker interface pattern ParamObject

// just empty interface
public interface ParamObject{}

executeQuery("select @name, @age", new ParamObject{
    int age = 31;
    String name = "dimzon"
})

this is my know-how :) represent array as XML string and pass as CLOB parameter
Your SQL code must expect XML - most of databases have XML-to-TABLE functions

crashes if the POJO has a int field where we try to set a null value

public void setProperty(Object obj, Object value) {
    try {
        this.method.invoke(obj, value);
    } catch (IllegalAccessException e) {
        throw new Sql2oException("error while calling setter method with name " + method.getName() + " on class " + obj.getClass().toString(), e);
    } catch (InvocationTargetException e) {
        throw new Sql2oException("error while calling setter method with name " + method.getName() + " on class " + obj.getClass().toString(), e);
    }
}

I know this is not a ORM and that's what is good about this project, but just as you can map a bean from a select result, i think it would be good to bind a bean to an update or insert query...

Something like .createQuery("UPDATE clients SET date = :date, name = :name, regn = :regn, active = :active WHERE id = :id").bind(client).executeUpdate();

bindig all the attributes of client as paramaters (Asuming client has id, date, name, regn, active attributes).

I know it can be done, but i don't know if this goes against what you are aiming for with this project.

Tell me what you think.

Thanks.

There should be a TimeConverter.

I understand that Table class provides a generic way of accessing results of an arbitrary SQL but sometimes it is convenient to just get a List<Map<String, Object>>. For example if you need to pass this to another API. Embedding code of sql2o outside persistence layer may not be a good idea. It could be achieved by either providing a method that internally does this translation of Table into this list or some other strategy. Either way it'd be a helpful addition in my opinion.

    private boolean caseSensitive;
    private boolean autoDeriveColumnNames;
    private Map<String, String> columnMappings;

this properties looks like common but are related to concrete ResultSetHandlerFactory implementation... I propose to make able to change whole ResultSetHandClerFactory implementation. The default one will have those properties so you can cast to DefaultResultFactory class and set them...

Or according GoF patterns this settings can be more generic as PropertyBindingStrategy api....

Any of those changes will break backward compatibility. So I propose to finalize current version and reinvent refactoring wheel...

Caused by: org.sql2o.converters.ConverterException: Cannot convert type class oracle.sql.TIMESTAMP to java.util.Date
at org.sql2o.converters.DateConverter.convert(DateConverter.java:25)
at org.sql2o.converters.DateConverter.convert(DateConverter.java:14)
at org.sql2o.reflection.Pojo.setProperty(Pojo.java:84)

version: 1.0.2

https://github.com/EtnaSoftwareSystems/ormia

// MyDAO.java
public interface MyDAO {
  @Select("SELECT * FROM person WHERE id = ?)
  Person getPersonById(String id);
}

// MyAPP.java
public class MyAPP {
  public static void main(String[] args) {
    Person p = ORMia.getData(MyDAO.class).getPersonById("1234");
  }
}

we can implement this in sql2o using already existing infrastructure

In my close-source ORM I do not just use ? for parameter, let me describe why...
the problem is - transparency.
I believe for query SELECT ? ResultSet column type MUST match parameter type.
Unfortunally this is not true for most DBs. That's why my "Quirks" analogue have String escapeTypecast(int sqlType) method. So SELECT @paramName after processing looks like SELECT (CAST ? AS TIMESTAMP). That's why I use such interface:

public interface ParamSetter {
    String getSubstitutionString();
    void setParameter(PreparedStatement ps, int index) throws SQLException;
}

Once I get some Object as parameter value I'm trying to cast it to ParamSetter.
If it's not ParamSetter I'm invoke my "Quirks" ParamSetter paramFromObject(Object obj) method.

Another problem is how to detect SqlType for null java values. I'm using special DBNull class wich contains type info...

When I use sql2o to insert date time in a DB column of type timestamp it is only inserting date and not the time.

    return sql2o.createQuery(sql)
        .addParameter("createTime", new Date())
        .executeUpdate()
        .getResult();

The create time column gets a value as: 2014-04-02 00:00:00.0

Same thing if I do via Spring jdbctemplate or JDBI gets value as: 2014-04-02 17:43:44.0

It seems sql2o is only inserting date component and dropping the time.

This seems to be the issue with Mysql 5.5 and Postgres 9.2. I haven't checked with other DB.

Whe using PostgreSQL, all SELECT statements will fail with message:
org.postgresql.util.PSQLException: ERROR: syntax error at or near "RETURNING"

Seems to be related to this issue

I have a case where I need to override/modify the value loaded from db.
I want to do this in a setter but the current version of sql2o modifies the property directly.

ResultSetHandler from apache db utils is a good api separation concept.
at one side you can provide your own hand-coded implementation
at other side you can use sql2o RS->POJO code
at third side you can write your own generic code
so I propose to add ResultSetHandler interface and ResultSetHandlerFactory interface. Default ResultSetHandlerFactory implementation will use sql2o RS->POJO code.

executeScalarList should use registered converters.

As pointed out by dimzon in pull request #67

The initialize method initializes and fills the cache in a non-threadsafe way

Have you all considered supporting built-in column mappings? That is a setting which would allow POJOs with a field named "myField" to map to a database column called "MY_FIELD" ?

I love this project and think it has a lot of promise for others like me who detest Hibernate. I understand Sql2o is not meant to be an ORM and not generating, but adding the same column mappings on every single query is rather annoying.

I'll fork and submit a PR with the code for your consideration.

When running a select query with column labels (aliases) in HSQLDB, sql2o is still trying to use column names when mapping to java classes. This is caused by a behavior in HSQLDB, that is different from most other databases. the ResultSet.getColumnName() method will still return the real column name, even though a label was used. To get the label with HSQLDB, ResultSet.getColumnLabel().

Until fixed in sql2o, get_column_name=false can be set in the jdbc connection url, which will emulate the behavior of most other databases.