Converting ZonedDateTime to MySQL Timestamp using CrudRepository

Question

I have the following code (simplified for clarity/brevity):

@Entity
@Table(name="mytable", schema="schemaName")
public class MobileDeviceData {
    @Id
    @Column(name="id")
    private String id;

    @Column(name="activitydetecteddate")
    private ZonedDateTime activityDetectedDate;
}

And CrudRepository Interface that is implemented by Springboot:

@Transactional
    public interface MobileDeviceDataDao extends CrudRepository<MobileDeviceData, String> {
}

With a column field of Timestamp in the table for activitydetecteddate.

When I try to save this to the database with the call

mobileDeviceDataDao.save(mobileDeviceDataList);

I get the following error:

2015-11-06 11:57:53.095  WARN 3198 --- [nio-8080-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 1292, SQLState: 22001
2015-11-06 11:57:53.095 ERROR 3198 --- [nio-8080-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : Data truncation: Incorrect datetime value: '\xAC\xED\x00\x05sr\x00\x0Djava.time.Ser\x95]\x84\xBA\x1B"H\xB2\x0C\x00\x00xpw\x15\x06\x00\x00\x07\xDF\x0B\x06\x10 \x05;?<\x80\x0' for column 'activityDetectedDate' at row 1
2015-11-06 11:57:53.096  INFO 3198 --- [nio-8080-exec-1] o.h.e.j.b.internal.AbstractBatchImpl     : HHH000010: On release of batch it still contained JDBC statements
2015-11-06 11:57:53.097  WARN 3198 --- [nio-8080-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Warning Code: 1292, SQLState: 22007
2015-11-06 11:57:53.097  WARN 3198 --- [nio-8080-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : Incorrect datetime value: '\xAC\xED\x00\x05sr\x00\x0Djava.time.Ser\x95]\x84\xBA\x1B"H\xB2\x0C\x00\x00xpw\x15\x06\x00\x00\x07\xDF\x0B\x06\x10 \x05;?<\x80\x0' for column 'activityDetectedDate' at row 1
2015-11-06 11:57:53.108 ERROR 3198 --- [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.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; nested exception is org.hibernate.exception.DataException: could not execute statement] with root cause

com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: '\xAC\xED\x00\x05sr\x00\x0Djava.time.Ser\x95]\x84\xBA\x1B"H\xB2\x0C\x00\x00xpw\x15\x06\x00\x00\x07\xDF\x0B\x06\x10 \x05;?<\x80\x0' for column 'activityDetectedDate' at row 1
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3868)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3806)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2470)

Can someone please tell me what I am doing wrong and how I can get the code correctly converted from ZonedDateTime to Timestamp.

What version of Hibernate are you using? Support for JDK8 types is only available in 5 with an extension module. — chrylis -cautiouslyoptimistic-, Nov 07 '15 at 01:49
I am using JDK 8. I am using Hibernate4 but I could go to 5 if needed. — DavidR, Nov 07 '15 at 14:02

score 4 · Accepted Answer · answered Nov 09 '15 at 17:31

4

I was able to find a solution to this problem that did not require Hibernate specific annotations in the code. I was able to implement this by adding these three jars to the classpath (I'm using Gradle):

compile 'org.hibernate:hibernate-core:5.0.3.Final'
compile 'org.hibernate:hibernate-entitymanager:5.0.3.Final'
compile 'org.hibernate:hibernate-java8:5.0.3.Final'

Hibernate was then able to correctly handle the new Java8 types.

answered Nov 09 '15 at 17:31

DavidR

6,622
13
56
70

Ah yes, I think this would be the ideal answer. Given that it's a built in type in the JDK, Hibernate should support it out of the box. This is definitely the best way to go for your particular problem. – Chris Thompson Nov 09 '15 at 20:53
I've tried this solution, but MySQL creates a tinyblob. When I add the Type-annotation `@Type(type ="org.hibernate.type.ZonedDateTimeType")` then MySQL creates a datetime field. How to debug this? – erwineberhard Jan 03 '16 at 14:15
@erwineberhard - What do you mean MySQL creates a tinyblob? Are you letting Hibernate create the DB or are you creating it yourself? – DavidR Jan 03 '16 at 16:09
@DavidR Hibernate creates the DB. – erwineberhard Jan 03 '16 at 18:24
@erwineberhard - That sounds like a bug with Hibernate and nothing to do with MySQL since it is just creating the table it was requested to do. If this is easily reproducible I would suggest opening a bug report with the Hibernate people. – DavidR Jan 03 '16 at 19:36

score 2 · Answer 2 · answered Nov 06 '15 at 23:58

The issue is that your underlying JPA provider doesn't know how to serialize that type. I've had this issue using joda time with my entities. The solution I've found is to use the Hibernate @Type annotation, although I'm sure there is a JPA way of doing this (rather than the provider specific one I've used). So what you'd do is this:

@Type(type ="org.hibernate.type.ZonedDateTimeType")
@Column(name="activitydetecteddate")
private ZonedDateTime activityDetectedDate;

This will tell Hibernate which class to use for the translation.

Edit It looks like @Convert is the annotation you'll want to use (based on this answer) if you want a portable converter (i.e. not tied to hibernate) although then I suspect you'll have to provide your own converter and at some point you'll have to tie yourself to something specific.

I think I found a better solution to this problem. Mine requires no extra annotation or code dependency to a third party. Let me know if you disagree? Although yours may be the correct answer for someone who can't freely migrate to the latest Hibernate? — DavidR, Nov 09 '15 at 17:35

Converting ZonedDateTime to MySQL Timestamp using CrudRepository

2 Answers2