java - Java LocalDateTime 和 db 之间的正确映射

Question

我有一个 spring-boot 1.2.1 应用程序，使用 Liquibase 来管理我的数据库。我还将 Spring-data-jpa 与 hibernate 一起使用。我有一张 table :

<createTable tableName="ADDRESS">
    <column name="address_id" type="bigint" autoIncrement="true">
        <constraints primaryKey="true" nullable="false"/>
    </column>
    <column name="address_line1" type="varchar(500)"/>
    <column name="address_line2" type="varchar(500)"/>
    <column name="address_line3" type="varchar(500)"/>
    <column name="city" type="varchar(500)"/>
    <column name="state" type="varchar(50)"/>
    <column name="zip" type="varchar(50)"/>
    <column name="status" type="varchar(100)"/>
    <column name="address_type" type="varchar(100)"/>
    <column name="begin_date_time" type="DATETIME"/>
    <column name="end_date_time" type="DATETIME"/>
</createTable>

和一个实体:

@Entity
@Table(name = "ADDRESS")
public class Address {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long addressId;
    private String addressLine1;
    private String addressLine2;
    private String addressLine3;
    private String city;
    private String state;
    private String zip;

    @Enumerated(EnumType.STRING)
    private Status status;

    @Enumerated(EnumType.STRING)
    private AddressType addressType;

    private LocalDateTime beginDateTime;
    private LocalDateTime endDateTime;
     // getters & setters

当我尝试使用这张表时:

Address officeAddress = new Address();
officeAddress.setAddressLine1( "123 Somewhere Lane" );
officeAddress.setAddressLine2( "Suite 100" );
officeAddress.setAddressLine3( "line 3" );
officeAddress.setCity( "Glenwood Springs" );
officeAddress.setState( "CO" );
officeAddress.setZip( "12345-789" );
officeAddress.setAddressType( AddressType.OFFICE );
officeAddress.setStatus( Status.ACTIVE );
officeAddress.setBeginDateTime( LocalDateTime.of( 2014, 8, 26, 12, 01 ) );

return addressRepository.save( officeAddress );

我得到一个异常(exception):

015-04-16 08:12:17.351  WARN 13866 --- [    Test worker] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 22007, SQLState: 22007
2015-04-16 08:12:17.352 ERROR 13866 --- [    Test worker] o.h.engine.jdbc.spi.SqlExceptionHelper   : Cannot parse "TIMESTAMP" constant "aced00057372000d6a6176612e74696d652e536572955d84ba1b2248b20c00007870770905000007de081a0cfe78"; SQL statement:
insert into address (address_id, address_line1, address_line2, address_line3, address_type, begin_date_time, city, end_date_time, state, status, zip) values (null, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) -- (NULL, ?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10) [22007-172]
2015-04-16 08:12:19.377  WARN 13866 --- [    Test worker] o.s.w.c.s.GenericWebApplicationContext   : Exception encountered during context initialization - cancelling refresh attempt

我将其用于 H2(测试)和 PostgreSQL(部署)。我以为 Liquibase docs表明这是声明表列的方式，但显然有问题。我确实需要日期和时间，但不确定是否需要时区信息。

有人知道正确的映射吗？

Answer 1

您可以使用转换器:

import javax.persistence.AttributeConverter;
import javax.persistence.Converter;
import java.sql.Timestamp;
import java.time.LocalDateTime;

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

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

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

并且:

import javax.persistence.AttributeConverter;
import javax.persistence.Converter;
import java.sql.Date;
import java.time.LocalDate;

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

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

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