Hot questions for Using Joda-Time in h2

Question:

I'm using JPA - Hibernate 2.1 with Joda DateTime. For DateTime conversion I'm using org.jadira.usertype to work with Hibernate. This works all fine for development and local testing with H2 in-memory DB. But on our Jenkins server the tests always fail with some strange SQLExceptions.

WARN  o.h.e.jdbc.spi.SqlExceptionHelper - SQL Error: 90003, SQLState: 90003
ERROR o.h.e.jdbc.spi.SqlExceptionHelper - Hexadecimal string with odd number of characters: "2015-01-22 14:15:52.965"; SQL statement:

And:

WARN  o.h.e.jdbc.spi.SqlExceptionHelper - SQL Error: 90004, SQLState: 90004
ERROR o.h.e.jdbc.spi.SqlExceptionHelper - Hexadecimal string contains non-hex character: "2015-01-22 14:15:52.98"; SQL statement:

It's pretty hard to find out why it's working on my laptop and not on the server. The only difference is that the server is running on an Ubuntu VM and I'm using Windows 8.1 - do I need to consider something like e.g. that OpenJDK could cause problem? Or is this a know H2 DB issue - but I could find anything? Any idea why and what I can do to solve it?

Thank you!

The field which causes problem:

@DateTimeFormat(iso = DateTimeFormat.ISO.DATE_TIME)//tried to remove this without success
private DateTime lastSeen;

persistence.xml - JPA properties.

<property name="jpaProperties">
        <props>
            <prop key="hibernate.hbm2ddl.auto">create-drop</prop>
            <prop key="hibernate.show_sql">true</prop>
            <prop key="hibernate.dialect">org.hibernate.dialect.H2Dialect</prop>
            <prop key="hibernate.connection.CharSet">utf-8</prop>
            <prop key="hibernate.connection.characterEncoding">utf-8</prop>
            <prop key="hibernate.connection.useUnicode">true</prop>
            <prop key="hibernate.hbm2ddl.import_files">test-import.sql</prop>
            <prop key="jadira.usertype.autoRegisterUserTypes">true</prop>
        </props>
    </property>

Answer:

I think you missed the @Column and the @Type annotations:

@Type(type="org.jadira.usertype.dateandtime.joda.PersistentDateTime")
@DateTimeFormat(iso = DateTimeFormat.ISO.DATE_TIME)
private DateTime lastSeen;

Question:

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>)

Answer:

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()));
            }
        };
    }
}

Register this argument factory to DBI by,

  dbi.registerArgumentFactory(new DateTimeArgumentFactory());

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

Question:

I have an odd exception appearing when I try to run a unit test. Here is the stack trace:

javax.persistence.PersistenceException: [PersistenceUnit: testPU] Unable to build Hibernate SessionFactory
    at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.persistenceException(EntityManagerFactoryBuilderImpl.java:1249)
    at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.access$600(EntityManagerFactoryBuilderImpl.java:120)
     ... more
Caused by: org.hibernate.MappingException: Could not instantiate persister org.hibernate.persister.entity.SingleTableEntityPersister
    at org.hibernate.persister.internal.PersisterFactoryImpl.create(PersisterFactoryImpl.java:174)
    at org.hibernate.persister.internal.PersisterFactoryImpl.createEntityPersister(PersisterFactoryImpl.java:135)
    at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:401)
    at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1859)
    at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl$4.perform(EntityManagerFactoryBuilderImpl.java:857)
    ... 39 more
Caused by: java.lang.ClassCastException: org.hibernate.type.SerializableType cannot be cast to org.hibernate.type.VersionType
    at org.hibernate.tuple.PropertyFactory.buildVersionProperty(PropertyFactory.java:181)
    at org.hibernate.tuple.entity.EntityMetamodel.<init>(EntityMetamodel.java:218)
    at org.hibernate.persister.entity.AbstractEntityPersister.<init>(AbstractEntityPersister.java:520)
    at org.hibernate.persister.entity.SingleTableEntityPersister.<init>(SingleTableEntityPersister.java:148)
    at sun.reflect.GeneratedConstructorAccessor29.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
    at org.hibernate.persister.internal.PersisterFactoryImpl.create(PersisterFactoryImpl.java:163)
    ... 43 more

Here are the libraries and frameworks that I'm using:

  1. JPA 2.0
  2. org.hibernate:hibernate-entitymanager:4.3.5.Final
  3. com.h2database:h2:1.4.188
  4. junit:junit:4.11
  5. Intellij IDE (I'm just running the test here)
  6. org.hibernate:hibernate-envers:4.3.5.Final

Well, I bet anyone who looks at this wants to see the persistense.xml file so here it is:

<?xml version="1.0" encoding="UTF-8"?>
<persistence xmlns="http://java.sun.com/xml/ns/persistence" version="2.0">
    <persistence-unit name="testPU" transaction-type="RESOURCE_LOCAL">
        <provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>
        <exclude-unlisted-classes>true</exclude-unlisted-classes>

There are many classes here so I've cut them out to keep things readable

        <properties>
            <property name="javax.persistence.jdbc.driver" value="org.h2.Driver" />
            <property name="javax.persistence.jdbc.url" value="jdbc:h2:mem:test;MODE=PostgreSQL;INIT=create schema if not exists test;DB_CLOSE_DELAY=-1" />
            <property name="javax.persistence.jdbc.user" value="sa" />
            <property name="javax.persistence.jdbc.password" value="" />
            <property name="hbm2ddl.auto" value="create" />
            <property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQL9Dialect"/>
            <property name="hibernate.show_sql" value="false"/>
        </properties>
    </persistence-unit>
</persistence>

The unit test is basic. It just finds a record by a value. Here is the code:

@Test
  public void testFindByNaturalKeyReturnsBusinessUnit() {
    final BusinessUnit businessUnit = mock(BusinessUnit.class);
    assertThat(businessUnitDao.findByNaturalKey(businessUnit.getNaturalKey()).getId(), is(businessUnit.getId()));
  }

Finally, the entity is really complex. I'm not sure if posting the code is going to help. I guess that there is a relationship at fault in the entity so I'm looking into that.


Answer:

OK, there is an answer. The entity class used JodaTime for some of the fields. This required the persistence.xml file to have some additional properties set.

Example of the JodaTime fields:

@javax.persistence.Column(name = "active_date")
private org.joda.time.DateTime activeDate;

Finally, the fixed persistence.xml file:

<?xml version="1.0" encoding="UTF-8"?>
<persistence xmlns="http://java.sun.com/xml/ns/persistence" version="2.0">
    <persistence-unit name="testPU" transaction-type="RESOURCE_LOCAL">
        <provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>
        <exclude-unlisted-classes>true</exclude-unlisted-classes>

There are many classes here so I've cut them out to keep things readable

        <properties>
            <property name="javax.persistence.jdbc.driver" value="org.h2.Driver" />
            <property name="javax.persistence.jdbc.url" value="jdbc:h2:mem:test;MODE=PostgreSQL;INIT=create schema if not exists test;DB_CLOSE_DELAY=-1" />
            <property name="javax.persistence.jdbc.user" value="sa" />
            <property name="javax.persistence.jdbc.password" value="" />
            <property name="hbm2ddl.auto" value="create" />
            <property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQL9Dialect"/>
            <property name="hibernate.show_sql" value="false"/>
            <property name="jadira.usertype.autoRegisterUserTypes" value="true"/>
            <property name="jadira.usertype.databaseZone" value="jvm"/>
            <property name="jadira.usertype.javaZone" value="jvm"/>
        </properties>
    </persistence-unit>
</persistence>