Hibernate 5.4 - unable to persist Java Instant

Question

I have a Kotlin app built on Spring Boot 2.3.4. This version of Spring Boot includes Hibernate 5.4.21. I've also imported mysql-connector-java 8.0.21.

Whenever I try to persist an Instant object into my database, I get the following exception:

com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Incorrect datetime value: '\xAC\xED\x00\x05sr\x00\x0Djava.time.Ser\x95]\x84\xBA\x1B"H\xB2\x0C\x00\x00xpw\x0D\x02\x00\x00\x00\x00_\xCDvA)\xE6Jxx' for column 'myDateTimeColumn' at row 1

Here's the object I'm trying to persist, in Kotlin; it only has two fields:

@Entity
class RoomEntity{
    @Id
    lateinit var roomId: String
    lateinit var lastAccessedAt: Instant
}

The table I'm trying to save it to has two columns: roomId is a CHAR(7), and lastAccessedAt is a TIMESTAMP.

The code that does the save is only 2 lines long:

val sql = "INSERT INTO mydb.rooms (roomId, lastAccessedAt) VALUES (?, ?);"
db.update(sql, "1234567", Instant.now())

I've looked at other SO posts similar to my issue, but the answers all say to update to version 5.2+ of Hibernate, which I am using. What could be causing this exception?

After you upgrade Hibernate, did you upgrade the schema or set the persistence unit to automatically update the schema? Java 8 types has been natively supported with Hibernate 5.2+, so the problem is likely due to mismatch of schema and entity types? Would be helpful if you can share the relevant entities annotation and db schema. — Alex
– Alex, Commented Dec 7, 2020 at 9:16

Christian Beikov · Accepted Answer · 2020-12-10 09:41:48Z

0

MySQL has a default precision for timestamps that is different from what the SQL standard expects. Also see here: https://dev.mysql.com/doc/refman/5.6/en/fractional-seconds.html

The fsp value, if given, must be in the range 0 to 6. A value of 0 signifies that there is no fractional part. If omitted, the default precision is 0. (This differs from the standard SQL default of 6, for compatibility with previous MySQL versions.)

You can fix this by using registering custom types in a dialect e.g.

public class SaneMySQLDialect extends MySQL5InnoDBDialect {

    public SaneMySQLDialect() {
        registerColumnType( Types.TIME, "time(6)" );
        registerColumnType( Types.TIMESTAMP, "datetime(6)" );
    }

    @Override
    public String getTableTypeString() {
        return " ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_bin";
    }
}

edited Dec 10, 2020 at 9:41

answered Dec 7, 2020 at 8:58

Christian Beikov

16.6k2 gold badges36 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mathew Alden Over a year ago

But Hibernate 5.2+ has native support for Java 8 time, so you don't need this library anymore.

Mathew Alden Over a year ago

I'll try this out and get back to you; thanks!

Mathew Alden · Accepted Answer · 2020-12-14 01:41:12Z

0

I never did figure out how to store an Instant in the DB. It was much easier to simply use a java.sql.Timestamp instead in my Data Access Layer.

So my Entity looks like this:

@Entity
class RoomEntity{
    @Id
    lateinit var roomId: String
    lateinit var lastAccessedAt: Timestamp

    fun toRoom(): Room {
        return Room(roomId, lastAccessedAt.toInstant());
    }
}

And I persist a new Room row with this code:

val sql = "INSERT INTO mydb.rooms (roomId, lastAccessedAt) VALUES (?, ?);"
db.update(sql, room.roomId, Timestamp.from(room.lastAccessedAt))

Where my room class looks like this:

data class Room(var roomId: String, var lastAccessedAt: Instant)

answered Dec 14, 2020 at 1:41

Mathew Alden

1,7504 gold badges26 silver badges39 bronze badges

Collectives™ on Stack Overflow

Hibernate 5.4 - unable to persist Java Instant

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related