Spring Boot JPA Hibernate – 存储毫秒精度的日期

huangapple go评论110阅读模式
英文:

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 = &quot;date_test&quot;)
public class DateTestDomain {
    @Id
    @Column(nullable = false, name = &quot;date&quot;, columnDefinition = &quot;TIMESTAMP(3)&quot;)
    @Temporal(TIMESTAMP)
    private Calendar dateTest;
}

my repo:

@Repository
public interface DateTestRepo extends JpaRepository&lt;DateTestDomain, Timestamp&gt; {
}

save date to db:

private final JdbcTemplate db;
...
        long testTime = 1602120218454L;
        Timestamp dateTimeStamp = new Timestamp(testTime);
        db.update(&quot;INSERT INTO date_test&quot; + &quot; (date) VALUES( \&quot;&quot; + dateTimeStamp + &quot;\&quot;)&quot;);

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 = &quot;date&quot;, columnDefinition = &quot;TIMESTAMP&quot;)
@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

huangapple
  • 本文由 发表于 2020年10月8日 20:13:57
  • 转载请务必保留本文链接:https://go.coder-hub.com/64262300.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定