Proper Mapping between Java LocalDateTime and db

Question

I have a spring-boot 1.2.1 app, using Liquibase to manage my database. I also use Spring-data-jpa with hibernate. I have a 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>

and an entity:

@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

When I try and use this table:

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 );

I get an 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

I am using this against H2 (tests) and PostgreSQL (deployment). I thought the Liquibase docs indicate this is the way to declare the table columns, but clearly something is wrong. I do want the date & time, but unsure if I need the timezone information.

Does anyone know the proper mapping for this?

This can depend on the jpa implementation you use. For a general solution, you can use [converters](https://weblogs.java.net/blog/montanajava/archive/2014/06/17/using-java-8-datetime-classes-jpa) in jpa 2.1+. If you use hibernate, you might be interested in the [hibernate-java8](https://hibernate.atlassian.net/browse/HHH-8844) module. — pozs, Apr 16 '15 at 14:38
That looks like versions before hibernate 5 don't support all the Java 8 time features. I am using Hibernate 4.3.4.Final, and am reluctant to swap it out with Hibernate 5.0.0.Beta1. — sonoerin, Apr 16 '15 at 14:58
Ok, I created converters for Date, Time, DateTime and annotated each column in the entities with that. Apparently that is the solution until Hibernate 5 comes out with a fix. — sonoerin, Apr 16 '15 at 16:05

score 8 · Answer 1 · answered Jun 17 '16 at 06:47

You can use converters:

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;
   }
}

And:

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;
    }
}

Proper Mapping between Java LocalDateTime and db

1 Answers1

Linked