我正在尝试使用Hibernate将java Dates存储在MariaDB中。似乎不同的日期被视为相同。例如：new Date(1518458441039)和new Date(1518458441102)显然是不同的日期，但两者都不能存储在声明为唯一的字段中：
11:09:59.540 [main] ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper - (conn=502) Duplicate entry '2018-02-12 14:00:41' for key 'UK_plwo79yqbhw7h0yuxbrg5ru7q'
javax.persistence.PersistenceException: org.hibernate.exception.ConstraintViolationException: could not execute statement
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:147)
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:155)
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:162)
at org.hibernate.internal.SessionImpl.firePersist(SessionImpl.java:780)
at org.hibernate.internal.SessionImpl.persist(SessionImpl.java:758)
at aero.navblue.ingest.MainClass$1.cacheMiss(MainClass.java:134)
at aero.navblue.ingest.MainClass$1.cacheMiss(MainClass.java:122)
at aero.navblue.CacheMap.get(CacheMap.java:13)
at aero.navblue.ingest.MainClass.insertTimestamps(MainClass.java:144)
at aero.navblue.ingest.MainClass.main(MainClass.java:73)
Caused by: org.hibernate.exception.ConstraintViolationException: could not execute statement
at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:59)
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:97)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:208)
at org.hibernate.dialect.identity.GetGeneratedKeysDelegate.executeAndExtract(GetGeneratedKeysDelegate.java:57)
at org.hibernate.id.insert.AbstractReturningDelegate.performInsert(AbstractReturningDelegate.java:42)
at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2909)
at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3480)
at org.hibernate.action.internal.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:81)
at org.hibernate.engine.spi.ActionQueue.execute(ActionQueue.java:623)
at org.hibernate.engine.spi.ActionQueue.addResolvedEntityInsertAction(ActionQueue.java:277)
at org.hibernate.engine.spi.ActionQueue.addInsertAction(ActionQueue.java:258)
at org.hibernate.engine.spi.ActionQueue.addAction(ActionQueue.java:303)
at org.hibernate.event.internal.AbstractSaveEventListener.addInsertAction(AbstractSaveEventListener.java:318)
at org.hibernate.event.internal.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:275)
at org.hibernate.event.internal.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:182)
at org.hibernate.event.internal.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:113)
at org.hibernate.event.internal.DefaultPersistEventListener.entityIsTransient(DefaultPersistEventListener.java:189)
at org.hibernate.event.internal.DefaultPersistEventListener.onPersist(DefaultPersistEventListener.java:132)
at org.hibernate.event.internal.DefaultPersistEventListener.onPersist(DefaultPersistEventListener.java:58)
at org.hibernate.internal.SessionImpl.firePersist(SessionImpl.java:773)
... 6 more
Caused by: java.sql.SQLIntegrityConstraintViolationException: (conn=502) Duplicate entry '2018-02-12 14:00:41' for key 'UK_plwo79yqbhw7h0yuxbrg5ru7q'
at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.get(ExceptionMapper.java:171)
at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.getException(ExceptionMapper.java:110)
at org.mariadb.jdbc.MariaDbStatement.executeExceptionEpilogue(MariaDbStatement.java:228)
at org.mariadb.jdbc.MariaDbPreparedStatementClient.executeInternal(MariaDbPreparedStatementClient.java:216)
at org.mariadb.jdbc.MariaDbPreparedStatementClient.execute(MariaDbPreparedStatementClient.java:150)
at org.mariadb.jdbc.MariaDbPreparedStatementClient.executeUpdate(MariaDbPreparedStatementClient.java:183)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:205)
... 23 more
Caused by: java.sql.SQLException: Duplicate entry '2018-02-12 14:00:41' for key 'UK_plwo79yqbhw7h0yuxbrg5ru7q'
Query is: insert into adsb_position_timestamp (create_date, create_user, modify_date, modify_user, position_received_time) values (?, ?, ?, ?, ?), parameters [<null>,<null>,<null>,<null>,'2018-02-12 14:00:41.102']
at org.mariadb.jdbc.internal.util.LogQueryTool.exceptionWithQuery(LogQueryTool.java:153)
at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:254)
at org.mariadb.jdbc.MariaDbPreparedStatementClient.executeInternal(MariaDbPreparedStatementClient.java:209)
... 26 more
然后字段声明为：
@Temporal( TemporalType.TIMESTAMP )
@Column( name = "position_received_time", unique = true, nullable = false, length = 19, updatable = false )
public Date getPositionReceivedTime() {
return this.positionReceivedTime;
}
如果您使用休眠创建数据库，则可以使用columnDefinition="DATETIME(3)"来启用分数支持。
@Temporal( TemporalType.TIMESTAMP )
@Column(columnDefinition="DATETIME(3)", name = "position_received_time", unique = true, nullable = false, length = 19, updatable = false )
public Date getPositionReceivedTime() {
return this.positionReceivedTime;
}
否则，对于MariaDB，您可以使用以下内容：
CREATE TABLE example(
col_microsec DATETIME(6),
col_millisec TIME(3)
);
有关参考，您可以阅读：microseconds-in-mariadb。