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

h2, hibernate, java, spring-boot, spring-data-jpa

Solution

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`.

Problem

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] ```

Original source