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.