3

I have been working with Spring Data JPA and MYSQL last couple of months and it has been a quite successful and smooth experience. In there i am using java 8 LocalDateTime to store the date time fields and the JPA automatically maps those fields in to mysql tinyblob columns.

Recently i got a requirement to add some data through a script to the system. In Order to fill the date time columns, i have created MYSQL TIMESTAMP variables and inserted into tinyblob columns. However system started complaining SerializationException and the root cause for that is this converted datetime columns. then i had a look at the date time columns inserted through the application as below

select CAST(drop_off_time AS CHAR(10000) CHARACTER SET utf8) From job

enter image description here

it looks like that when you insert through the application, it inserted as some kind of java serialization. However through a mysql script we can not replicate that functionality.

Now i have two options. 1) I need to find a way to write a mysql script to generate the Date time columns similar to the application. 2) I need to change the Spring data JPA mapping from tinyblob to an another data type which mysql scripts can support

Appreciate your help

Thanks, Keth

EDIT

After following the answers and comments provided below, i was able to find a simple solution

if you use Hibernate 5.0+, you can drop in

<dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-java8</artifactId>
    <version>${hibernate.version}</version>
</dependency>

Then the system starts mapping the java 8 LocalDateTime prperties in to mysql DATETIME columns

keth
  • 793
  • 2
  • 11
  • 36
  • 1
    Depending on which Hibernate version the Java 8 date time API isn't supported and that will be stored as binary instead of a timestamp. – M. Deinum Sep 04 '17 at 11:59
  • 6
    LocalDateTime should not be stored in blobs. That makes it impossible to query on the date, makes it impossible to use the value in anything other than Java, and takes much more disk space than necessary. Recent versions of Hibernate support LocalDateTime out of the box, stored as date/timestamp (see http://docs.jboss.org/hibernate/orm/5.2/userguide/html_single/Hibernate_User_Guide.html#basic-provided). Which version of spring/hibernate are you using? – JB Nizet Sep 04 '17 at 12:00
  • 3
    Check this depyendency, it will add java8 datetime api support to hibernate: http://search.maven.org/#search%7Cgav%7C1%7Cg%3A%22org.hibernate%22%20AND%20a%3A%22hibernate-java8%22 As far as I know it's available for Hibernate 5 only – Pusker György Sep 04 '17 at 12:03
  • 1
    agree, BLOB is fatal. Not only query limited to Java. Cannot be properly sorted by DB server – Jacek Cz Sep 04 '17 at 12:08
  • 2
    I'd expect that `LocalDateTime` would be mapped to `TIMESTAMP` (without TZ) to begin with. I don't believe there can be anything in the form of a db script that will "fix" your data - you can, however, iterate over your whole dataset in hava and properly write deserialized data back into timestamp column. Other than that, see [this question](https://stackoverflow.com/questions/29517508/how-to-persist-jsr-310-types-with-spring-data-jpa) – M. Prokhorov Sep 04 '17 at 12:09
  • @JBNizet i am using hibernate 5.0.12.Final – keth Sep 04 '17 at 12:29
  • 2
    Then, as the documentattion for that version says (see http://docs.jboss.org/hibernate/orm/5.0/userguide/html_single/Hibernate_User_Guide.html#basic-provided), you should add the hibernate-java8 jar file to your dependencies. Or you could upgrade to the latest version. – JB Nizet Sep 04 '17 at 14:45

1 Answers1

6

According to JPA 2.1 LocalDateTime isn't suppoorted oficially (probably in short time JPA 2.,2 will be official). Hibernate 5 support as 'early release'

Portable and supported since JPA 2.0 is javax.persistence.AttributeConverter, works very well on all JPA providers (and makes nothing bad on Hibernate 5)

@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());
}
}
Jacek Cz
  • 1,872
  • 1
  • 15
  • 22