英文:
Spring Boot JPA Hibernate - Storing Date with millisecond precision
问题
Spring Boot 版本:'2.3.4.RELEASE'
Java 11
org.springframework.boot:spring-boot-starter-jdbc
org.springframework.boot:spring-boot-starter-data-jpa
spring-data-jpa-2.3.4.RELEASE
运行时(mysql:mysql-connector-java)
服务器数据库 MariaDB (版本 10.5.5-MariaDB)
Java MariaDB 连接器 J:2.6.0[稳定]
我正在尝试在 Hibernate 中以毫秒精度持久化 java.sql.Timestamp 对象。我需要将带有毫秒的日期保存到数据库中。例如:2020-10-08 03:23:38.454。
我的领域(domain):
import java.sql.Timestamp;
@Entity
@Data
@Table(name = "date_test")
public class DateTestDomain {
@Id
@Column(nullable = false, name = "date", columnDefinition = "TIMESTAMP(3)")
@Temporal(TemporalType.TIMESTAMP)
private Calendar dateTest;
}
我的仓库(repo):
@Repository
public interface DateTestRepo extends JpaRepository<DateTestDomain, Timestamp> {
}
将日期保存到数据库:
private final JdbcTemplate db;
...
long testTime = 1602120218454L;
Timestamp dateTimeStamp = new Timestamp(testTime);
db.update("INSERT INTO date_test" + " (date) VALUES("" + dateTimeStamp + "")");
更新:SQL 的结果是我需要的正确结果!!! 这个方法运行得很完美:
2020-10-08 03:23:38.454
但是使用 Hibernate/JPA 结果为 FALSE。
调试跟踪:
2020-10-09 22:26:53.120 Hibernate: insert into date_test (date) values (?)
2020-10-09 22:26:53.122 TRACE 95038 --- [restartedMain] o.h.type.descriptor.sql.BasicBinder : binding parameter [1] as [TIMESTAMP] - [2020-10-09 22:26:53.044]
Calendar calendar = Calendar.getInstance();
Date date = new Date();
calendar.setTimeInMillis(date.getTime());
dateTestDomain.setDateTest(calendar);
dateTestRepo.save(dateTestDomain);
SQL 的结果:使用 Hibernate 的 SQL 插入,小数秒总是被设置为 **.000**:
> 2020-10-09 22:26:53.000
请帮助。我需要通过 JPA 以毫秒精度保存时间到数据库。
更新:
我尝试了 SQL 方言:
org.hibernate.dialect.MySQL5InnoDBDialect,然后是 org.hibernate.dialect.MySQL55InnoDBDialect,然后是 org.hibernate.dialect.MariaDB103Dialect,最后是 org.hibernate.dialect.MariaDB105Dialect,
但都没有成功。
更新1:
Hibernate: INSERT INTO date_test (timestamp, local_date_time, local_date_timea) VALUES (NOW(3), ?, ?)
2020-10-10 15:33:29.099 TRACE 44072 --- [restartedMain] o.h.type.descriptor.sql.BasicBinder : binding parameter [1] as [TIMESTAMP] - [2020-10-10 15:33:29.051]
2020-10-10 15:33:29.100 TRACE 44072 --- [restartedMain] o.h.type.descriptor.sql.BasicBinder : binding parameter [2] as [TIMESTAMP] - [java.util.GregorianCalendar[time=1602336809051,areFieldsSet=true...
结果 SQL:
2020-10-10 15:33:29.101,2020-10-10 13:33:29.000,2020-10-10 15:33:29.000。
还有一个问题:
数据库日期:
2020-10-10 16:19:42.578
2020-10-10 16:20:47.000
2020-10-10 16:20:47.888
2020-10-10 16:20:47.892
2020-10-10 16:20:47.896
2020-10-10 16:20:47.900
Hibernate: select datetestdo0_.timestamp as timestam1_0_ from date_test datetestdo0_ where datetestdo0_.timestamp>?
binding parameter [1] as [TIMESTAMP] - [2020-10-10 16:20:47.893]
2020-10-10 16:20:47.888
2020-10-10 16:20:47.892
2020-10-10 16:20:47.896
2020-10-10 16:20:47.9
jdbcsql:
select timestamp from date_test where timestamp>"2020-10-10 16:20:47.893"
2020-10-10 16:20:47.896
2020-10-10 16:20:47.900
JPA/Hibernate 无法处理毫秒...
英文:
Spring Boot version '2.3.4.RELEASE'
Java 11
org.springframework.boot:spring-boot-starter-jdbc
org.springframework.boot:spring-boot-starter-data-jpa
spring-data-jpa-2.3.4.RELEASE
runtime(mysql:mysql-connector-java)
Server DB MariaDB (ver. 10.5.5-MariaDB)
Java MariaDB Connector J:2.6.0[stable]
I'm trying to persist a java.sql.Timestamp object in Hibernate with millisecond precision. I need save dates to db with milliseconds. For example: 2020-10-08 03:23:38.454.
my domain:
import java.sql.Timestamp;
@Entity
@Data
@Table(name = "date_test")
public class DateTestDomain {
@Id
@Column(nullable = false, name = "date", columnDefinition = "TIMESTAMP(3)")
@Temporal(TIMESTAMP)
private Calendar dateTest;
}
my repo:
@Repository
public interface DateTestRepo extends JpaRepository<DateTestDomain, Timestamp> {
}
save date to db:
private final JdbcTemplate db;
...
long testTime = 1602120218454L;
Timestamp dateTimeStamp = new Timestamp(testTime);
db.update("INSERT INTO date_test" + " (date) VALUES( \"" + dateTimeStamp + "\")");
UPD: Result of sql is right as I need!!! This method working perfect:
2020-10-08 03:23:38.454
But with hibernate/JPA result is FALSE.
Debug Trace:
2020-10-09 22:26:53.120 Hibernate: insert into date_test (date) values (?)
2020-10-09 22:26:53.122 TRACE 95038 --- [ restartedMain] o.h.type.descriptor.sql.BasicBinder : binding parameter [1] as [TIMESTAMP] - [2020-10-09 22:26:53.044]
Calendar calendar = Calendar.getInstance();
Date date = new Date();
calendar.setTimeInMillis(date.getTime());
dateTestDomain.setDateTest(calendar);
dateTestRepo.save(dateTestDomain);
Result of sql: the fractional seconds are always set to .000 with hibernate sql insert:
>2020-10-09 22:26:53.000
please help. I need save to db time with millisecond precision throw JPA.
UPD:
I try sql dialect:
org.hibernate.dialect.MySQL5InnoDBDialect than org.hibernate.dialect.MySQL55InnoDBDialect than org.hibernate.dialect.MariaDB103Dialect than org.hibernate.dialect.MariaDB105Dialect
without success.
UPD 1:
Hibernate: INSERT INTO date_test (timestamp, local_date_time, local_date_timea) VALUES (NOW(3), ?, ?)
2020-10-10 15:33:29.099 TRACE 44072 --- [ restartedMain] o.h.type.descriptor.sql.BasicBinder : binding parameter [1] as [TIMESTAMP] - [2020-10-10 15:33:29.051]
2020-10-10 15:33:29.100 TRACE 44072 --- [ restartedMain] o.h.type.descriptor.sql.BasicBinder : binding parameter [2] as [TIMESTAMP] - [java.util.GregorianCalendar[time=1602336809051,areFieldsSet=true...
Result SQL:
2020-10-10 15:33:29.101, 2020-10-10 13:33:29.000, 2020-10-10 15:33:29.000.
And one more problem:
DB dates:
2020-10-10 16:19:42.578
2020-10-10 16:20:47.000
2020-10-10 16:20:47.888
2020-10-10 16:20:47.892
2020-10-10 16:20:47.896
2020-10-10 16:20:47.900
Hibernate: select datetestdo0_.timestamp as timestam1_0_ from date_test datetestdo0_ where datetestdo0_.timestamp>?
binding parameter [1] as [TIMESTAMP] - [2020-10-10 16:20:47.893]
2020-10-10 16:20:47.888
2020-10-10 16:20:47.892
2020-10-10 16:20:47.896
2020-10-10 16:20:47.9
jdbcsql:
select timestamp from date_test where timestamp>"2020-10-10 16:20:47.893"
2020-10-10 16:20:47.896
2020-10-10 16:20:47.900
jpa/hibernate not working with milliseconds...
答案1
得分: 1
自从 JPA 2.2 版本开始,支持使用 Java 8 的日期和时间 API。我尚未尝试是否能解决您的问题,但您可以尝试使用 Java 8 的 LocalDateTime
而不是 Calendar
类型。
替换为:
@Id
@Column(nullable = false, name = "date", columnDefinition = "TIMESTAMP")
@Temporal(TIMESTAMP)
private LocalDateTime localDateTime;
英文:
Since JPA 2.2 there is support of java8 date and time API. I have not tried if it will solve your problem or not but can you try with java8's LocalDateTime
instead of Calendar
type.
Replace:
@Id
@Column(nullable = false, name = "date", columnDefinition = "TIMESTAMP")
@Temporal(TIMESTAMP)
private LocalDateTime localDateTime;
答案2
得分: 1
Calendar calendar = Calendar.getInstance();
calendar.setTimeInMillis(calendar.getTimeInMillis());
尝试使用这个,而不是使用 new Date()。
英文:
Calendar calendar = Calendar.getInstance();
calendar.setTimeInMillis(calendar.getTimeInMillis());
Try this one instead of using new Date()
答案3
得分: 0
这不是一个直接的解决方案,而是一个变通方法,运行得很好:
不要存储实际日期,而是通过调用date.getTime()
方法来存储日期的long
值,存储这个结果,在从日期中获取日期时,使用new Date(longValue)
来使用数据库中存储的longValue
重新创建原始日期,这种方法至少保留了毫秒精度。
英文:
This is not a direct solution but a work around, works perfectly:
Instead of storing the actual date, just store the long
value of your date, by calling the date.getTime()
method, store this result, when getting the date from the date use new Date(longValue)
to recreate your original date using the longValue
stored in your database, this approach at least preserves your millisecond precision
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论