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?

user_412446 · Answer

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

user_4493048 · Answer

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)

Hibernate 5.4 - unable to persist Java Instant

2 Answers