How do I modify my `@SQLUpdate` to convert a `Joda` `DateTime` object into an `h2`-readable timestamp?

How do I modify my @SQLUpdate to convert a Joda DateTime object into an h2-readable timestamp?

I have a DAO object, MyDao, with an insert method.

public interface MyDao extends Transactional<MyDao> {
    @SqlUpdate(
        "INSERT INTO my_table "(id, original_date, later_date)" +
        "VALUES (:id, :originalDate, :laterDate)"
    void insert(@BindBean MyObject myObject);
}

Here is MyObject:

import java.util.UUID;
import org.joda.time.DateTime;

public class MyObject {
    private UUID id;
    private DateTime originalDate;
    private DateTime laterDate;

    public MyObject(UUID id, DateTime originalDate, DateTime laterDate) {
        this.id = id;
        this.originalDate = originalDate;
        this.laterDate = laterDate;
    }

}

In the migrations.xml file (I'm using http://www.liquibase.org/xml/ns/dbchangelog), I have:

<column name="original_date" type="timestamp">
    <constraints nullable="false"/>
</column>
<column name="later_date" type="timestamp">
    <constraints nullable="false"/>
</column>

Here is my test:

@Test
public void test() {
    DateTime dt = new DateTime(2015, 5, 29, 8, 34);
    UUID uuid = new UUID(5, 8);
    MyObject myObject = new MyObject(uuid, dt, dt);
    mydao.insert(myObject)
    // assertEquals and other code here
}

I tried to test this, but got an error like this:

org.skife.jdbi.v2.exceptions.UnableToExecuteStatementException: org.h2.jdbc.JdbcSQLException: Cannot parse "TIMESTAMP" constant "aced0005737200166f72672e6a6f64612e74696d652e4461746554696d65b83c78646a5bddf90200007872001f6f72672e6a6f64612e74696d652e626173652e426173654461746554696d65fffff9e14f5d2ea30200024a0007694d696c6c69734c000b694368726f6e6f6c6f677974001a4c6f72672f6a6f64612f74696d652f4368726f6e6f6c6f67793b78700000014b030b30c0737200276f72672e6a6f64612e74696d652e6368726f6e6f2e49534f4368726f6e6f6c6f67792453747562a9c811667137502703000078707372001f6f72672e6a6f64612e74696d652e4461746554696d655a6f6e652453747562a62f019a7c321ae3030000787077150013416d65726963612f4c6f735f416e67656c65737878"; SQL statement:

[SQL statement]

at org.h2.util.DateTimeUtils.parseDateValue(DateTimeUtils.java:275)
at org.h2.value.ValueTimestamp.parseTry(ValueTimestamp.java:140)
at org.h2.value.ValueTimestamp.parse(ValueTimestamp.java:120)
at org.h2.value.Value.convertTo(Value.java:862)
at org.h2.table.Column.convert(Column.java:148)
at org.h2.command.dml.Insert.insertRows(Insert.java:143)
at org.h2.command.dml.Insert.update(Insert.java:114)
at org.h2.command.CommandContainer.update(CommandContainer.java:78)
at org.h2.command.Command.executeUpdate(Command.java:254)
at org.h2.jdbc.JdbcPreparedStatement.execute(JdbcPreparedStatement.java:198)
at org.skife.jdbi.v2.SQLStatement.internalExecute(SQLStatement.java:1328)
at org.skife.jdbi.v2.Update.execute(Update.java:56)
at org.skife.jdbi.v2.sqlobject.UpdateHandler$2.value(UpdateHandler.java:62)
at org.skife.jdbi.v2.sqlobject.UpdateHandler.invoke(UpdateHandler.java:75)
at org.skife.jdbi.v2.sqlobject.SqlObject.invoke(SqlObject.java:175)
at org.skife.jdbi.v2.sqlobject.SqlObject$1.intercept(SqlObject.java:75)
at org.skife.jdbi.v2.sqlobject.CloseInternalDoNotUseThisClass$$EnhancerByCGLIB$$4df23516.insert(<generated>)

Solution

We can do this by registering argument factory to DBI.

public class DateTimeArgumentFactory implements ArgumentFactory<DateTime> {
    @Override
    public boolean accepts(Class<?> expectedType, Object value, StatementContext ctx) {
        return value != null && DateTime.class.isAssignableFrom(value.getClass());
    }

    @Override
    public Argument build(Class<?> expectedType, final DateTime value, StatementContext ctx) {
        return new Argument() {
            @Override
            public void apply(int position, PreparedStatement statement, StatementContext ctx) throws SQLException {
                statement.setTimestamp(position, new java.sql.Timestamp(value.getMillis()));
            }
        };
    }
}

  dbi.registerArgumentFactory(new DateTimeArgumentFactory());

This will automatically take care of converting DateTime to sql TimeStamp.