Why am I getting JdbcSQLException (non-hex characters) with my H2 database / Spring boot application?

Question

So the short version, I'm guessing I've some sort of character encoding issue, or the DB is storing/returning the date in a format Hibernate/Spring-jpa doesn't like for some reason.

But I'm jiggered if I can work out what's going wrong!

Using Hibernate 5 to make use of J8 LocalDate stuff in entity props.

The database is being created and data inserted ok (you'll see in the log snippet below I get a date value back).

Log snippet:

2016-10-26 13:25:19.885 ERROR 1028 --- [nio-8080-exec-1] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.orm.jpa.JpaSystemException: 
Could not read entity state from ResultSet : EntityKey[uk.co.deditech.entity.Person#2]; 
nested exception is org.hibernate.exception.GenericJDBCException: Could not read entity state from ResultSet : 
EntityKey[uk.co.deditech.entity.Person#2]] with root cause org.h2.jdbc.JdbcSQLException: Hexadecimal string contains non-hex character: "2016-03-23" [90004-192]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:345) ~[h2-1.4.192.jar:1.4.192]
at org.h2.message.DbException.get(DbException.java:179) ~[h2-1.4.192.jar:1.4.192]
at org.h2.message.DbException.get(DbException.java:155) ~[h2-1.4.192.jar:1.4.192]
at org.h2.util.StringUtils.convertHexToBytes(StringUtils.java:986) ~[h2-1.4.192.jar:1.4.192]
at org.h2.value.Value.convertTo(Value.java:973) ~[h2-1.4.192.jar:1.4.192]
at org.h2.value.Value.getBytes(Value.java:422) ~[h2-1.4.192.jar:1.4.192]
at org.h2.jdbc.JdbcResultSet.getBytes(JdbcResultSet.java:1077) ~[h2-1.4.192.jar:1.4.192]
<snip>

Gradle:

compile("org.springframework.boot:spring-boot-starter-web")
compile("org.springframework.boot:spring-boot-starter-data-jpa")
compile("org.springframework.boot:spring-boot-starter-freemarker")
compile group: 'com.h2database', name: 'h2', version:'1.4.192'

Entity:

@Entity
@Table(name = "person")
public @Data class Person {
   ...
   @Column(name = "last_grading_date", nullable = true)
   private LocalDate lastGradingDate;
}

Spring boot auto DB creation script snippets:

schema.sql
create table PERSON
(
id int not null,
last_grading_date date
)

data.sql
insert into person (id, last_grading_date)
values (1, '2015-02-20');

Properties (issue was occurring before and after I added the encoding property below):

spring.datasource.url=jdbc:h2:mem:AZ;DB_CLOSE_DELAY=-1;
spring.datasource.driverClassName=org.h2.Driver
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
spring.datasource.sql-script-encoding=UTF-8

EDIT: After some more digging I discovered "validate" is a setting for the spring.jpa.hibernate.ddl-auto property. So I tried that.

I'm now getting the following error during startup...

Caused by: org.hibernate.tool.schema.spi.SchemaManagementException: Schema-validation: wrong column type encountered in column [last_grading_date] in table [person]; found [date (Types#DATE)], but expecting [binary(255) (Types#VARBINARY)]
at org.hibernate.tool.schema.internal.SchemaValidatorImpl.validateColumnType(SchemaValidatorImpl.java:105) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]

Answer 1

I got it working by adding this dependency in my pom:

<dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-java8</artifactId>
    <version>${hibernate.version}</version>
</dependency>

I dont know why it does not work out of the box, but with this dependency it fix the issue.

I also added this property under properties: <hibernate.version>5.0.5.Final</hibernate.version>

My sample code for reproducing:

Data.sql:

insert into person (id, last_grading_date)
values (1, '2015-02-20');

application.properties

spring.datasource.url=jdbc:h2:mem:AZ;DB_CLOSE_DELAY=-1;
spring.datasource.driverClassName=org.h2.Driver
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
spring.datasource.sql-script-encoding=UTF-8

PersonRepository

public interface PersonRepository extends JpaRepository<Person, Integer>{

}

Person

@Entity
@Table(name = "person")
public class Person {

    @Id
    @Column
    private int id;

    @Column(name = "last_grading_date", nullable = true)
    @Type(type = "java.time.LocalDate")
    private LocalDate lastGradingDate;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public LocalDate getLastGradingDate() {
        return lastGradingDate;
    }

    public void setLastGradingDate(LocalDate lastGradingDate) {
        this.lastGradingDate = lastGradingDate;
    }
}

Applcation

@SpringBootApplication
public class TestApplication implements CommandLineRunner{

    @Autowired
    PersonRepository repo;

    public static void main(String[] args) {
        SpringApplication.run(TestApplication.class, args);
    }

    @Override
    public void run(String... arg0) throws Exception {
        Person p = repo.findOne(1);
        System.out.println(p.getLastGradingDate());
    }
}

result: 2015-02-20

---

Added a working example on GitHub. The demo is build on Spring-boot, Java 8, Hibernate 5, maven and java.time.LocalDate.

Answer 2

JPA 2.1 was released before Java 8 and the Date and Time API simply didn’t exist at that point in time. Therefore the @Temporal annotation can only be applied to attributes of type java.util.Date and java.util.Calendar.

If you want to store a LocalDate attribute in a DATE column or a LocalDateTime in a TIMESTAMP column, you need to define the mapping to java.sql.Date or java.sql.Timestamp yourself.

Attribute converter are part of the JPA 2.1 specification and can therefore be used with any JPA 2.1 implementation, e.g. Hibernate or EclipseLink. I used Wildfly 8.2 with Hibernate 4.3 for the following examples.

Converting LocalDate

As you can see in the following code snippet, there isn’t much you need to do to create an attribute converter for LocalDate.

@Converter(autoApply = true)
public class LocalDateAttributeConverter implements AttributeConverter<LocalDate, Date> {

    @Override
    public Date convertToDatabaseColumn(LocalDate locDate) {
        return (locDate == null ? null : Date.valueOf(locDate));
    }

    @Override
    public LocalDate convertToEntityAttribute(Date sqlDate) {
        return (sqlDate == null ? null : sqlDate.toLocalDate());
    }
}

You need to implement the AttributeConverter<LocalDate, Date> interface with its two methods convertToDatabaseColumn and convertToEntityAttribute. As you can see on the method names, one of them defines the conversion from the type of the entity attribute (LocalDate) to the database column type (Date) and the other one the inverse conversion. The conversion itself is very simple because java.sql.Date already provides the methods to do the conversion to and from a LocalDate.

Additionally the attribute converter needs to be annotated with the @Converter annotation. Due to the optional autoApply=true property, the converter will be applied to all attributes of type LocalDate. Have a look here, if you want to define the usage of the converter for each attribute individually.

The conversion of the attribute is transparent to the developer and the LocalDate attribute can be used as any other entity attribute. You can use it as a query parameter for example.

LocalDate date = LocalDate.of(2015, 8, 11);
TypedQuery<MyEntity> query = this.em.createQuery("SELECT e FROM MyEntity e WHERE date BETWEEN :start AND :end", MyEntity.class);
query.setParameter("start", date.minusDays(2));
query.setParameter("end", date.plusDays(7));
MyEntity e = query.getSingleResult();

Converting LocalDateTime

The attribute converter for LocalDateTime is basically the same. You need to implement the AttributeConverter<LocalDateTime, Timestamp> interface and the converter needs to be annotated with the @Converter annotation. Similar to the LocalDateConverter, the conversion between a LocalDateTime and an java.sql.Timestamp is done with the conversion methods of Timestamp.

@Converter(autoApply = true)
public class LocalDateTimeAttributeConverter implements AttributeConverter<LocalDateTime, Timestamp> {

    @Override
    public Timestamp convertToDatabaseColumn(LocalDateTime locDateTime) {
        return (locDateTime == null ? null : Timestamp.valueOf(locDateTime));
    }

    @Override
    public LocalDateTime convertToEntityAttribute(Timestamp sqlTimestamp) {
        return (sqlTimestamp == null ? null : sqlTimestamp.toLocalDateTime());
    }
}

Example Entity

@Entity
public class MyEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "id", updatable = false, nullable = false)
    private Long id;

    @Column
    private LocalDate date;

    @Column
    private LocalDateTime dateTime;

    ...

}