java 为什么我的 H2 数据库/Spring 启动应用程序会收到 JdbcSQLException（非十六进制字符）？

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

提问by DaFoot

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.

所以简短的版本，我猜我有某种字符编码问题，或者数据库正在以 Hibernate/Spring-jpa 出于某种原因不喜欢的格式存储/返回日期。

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.

使用 Hibernate 5 在实体道具中使用 J8 LocalDate 的东西。

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:

Spring boot 自动数据库创建脚本片段：

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.

编辑：经过更多的挖掘，我发现“验证”是 spring.jpa.hibernate.ddl-auto 属性的设置。所以我试过了。

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]

回答by Patrick

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

我通过在我的 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>

我还在属性下添加了这个属性： <hibernate.version>5.0.5.Final</hibernate.version>

My sample code for reproducing:

我的复制示例代码：

Data.sql:

数据.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

结果： 2015-02-20

---

---

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

在GitHub 上添加了一个工作示例。该演示是建立在Spring-boot，Java 8，Hibernate 5，maven和java.time.LocalDate。

回答by Vazgen Torosyan

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.

JPA 2.1 在 Java 8 之前发布，日期和时间 API 在那个时间点根本不存在。因此@Temporal 注释只能应用于java.util.Date 和java.util.Calendar 类型的属性。

If you want to store a LocalDateattribute in a DATEcolumn or a LocalDateTimein a TIMESTAMPcolumn, you need to define the mapping to java.sql.Dateor java.sql.Timestampyourself.

如果要LocalDate在DATE列中存储属性或LocalDateTime在TIMESTAMP列中存储属性，则需要定义到java.sql.Date或的映射java.sql.Timestamp。

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.

属性转换器是 JPA 2.1 规范的一部分，因此可以与任何 JPA 2.1 实现一起使用，例如 Hibernate 或 EclipseLink。我在以下示例中使用了 Wildfly 8.2 和 Hibernate 4.3。

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.

正如您在下面的代码片段中看到的那样，为 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 convertToDatabaseColumnand 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.Datealready provides the methods to do the conversion to and from a LocalDate.

您需要AttributeConverter<LocalDate, Date>使用它的两个方法convertToDatabaseColumn和来实现接口convertToEntityAttribute。正如您在方法名称中看到的那样，其中一个定义了从实体属性的类型 ( LocalDate) 到数据库列类型 ( Date) 的转换，另一个定义了逆转换。转换本身非常简单，因为java.sql.Date已经提供了在LocalDate.

Additionally the attribute converter needs to be annotated with the @Converterannotation. 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.

此外，属性转换器需要使用注释进行@Converter注释。由于可选的 autoApply=true 属性，转换器将应用于 LocalDate 类型的所有属性。如果您想单独为每个属性定义转换器的用法，请看这里。

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

属性的转换对开发人员是透明的，并且该LocalDate属性可以用作任何其他实体属性。例如，您可以将其用作查询参数。

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 LocalDateTimeis 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 LocalDateTimeand an java.sql.Timestampis done with the conversion methods of Timestamp.

的属性转换器LocalDateTime基本相同。您需要实现AttributeConverter<LocalDateTime, Timestamp>接口，并且转换器需要使用@Converter 注解进行注解。与 类似LocalDateConverter，aLocalDateTime和 an之间的转换java.sql.Timestamp是通过 的转换方法完成的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;

    ...

}

回答by ashario

In my case (Spring Boot 1.5.10) all I needed to do was add the following to the pom.xml properties section

在我的情况下（Spring Boot 1.5.10），我需要做的就是将以下内容添加到 pom.xml 属性部分

<hibernate.version>5.2.12.Final</hibernate.version>

It looks like by default this version of Spring Boot uses Hibernate 5.0.12.Final

看起来这个版本的 Spring Boot 默认使用 Hibernate 5.0.12.Final

回答by Asoub

You didn't specify the type of the column for last_grading_datein hibernate. You can use:

您没有last_grading_date在休眠中指定列的类型。您可以使用：

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

Change LocalDateclass to java.sql.Dateif that doesn't work.

如果这不起作用LocalDate，java.sql.Date请将课程更改为。