Java.Util.Date 查询问题与 JPA 以及 Hibernate 绑定参数

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

Java.Util.Date Query Problem with JPA and Hibernates Binding Parameter

问题

我目前正在使用 Spring Boot。现在我想通过 JpaRepository 使用 Java.util.date 查询数据,我在我的接口中有以下代码。

List<MyEntity> findByDate(Date date);

我使用了 Java.util.date,由于个人原因无法更改为 java.time。我确保了 MyEntity 类中的日期字段具有 TemporalType.DATE,就像这样:

@Temporal(TemporalType.DATE) private Date date;

而且 MySQL 中的日期字段也是 date 类型。

我尝试使用上述的 findByDate(Date date) 方法,方法中的 date 参数在我的数据库中显然存在,但我总是得到一个空的列表...

其他像 findByName(String name)findAll() 这样的方法都正常工作。

我已经尝试记录 Hibernate 的 SQL 语句,发现绑定参数 [DATE] 可能处于不同的格式?在我的数据库中是 'yyyy-mm-dd'。

以下是我使用 id 和 date 进行查询时的日志,我注意到日志底部绑定了查询语句中的 Id,但日期没有绑定:

2563-04-10 12:19:03.359 [http-nio-8080-exec-2] TRACE o.h.type.descriptor.sql.BasicBinder - binding parameter [1] as [DATE] - [Tue Apr 15 00:00:00 ICT 1477]

这是我的 .yml 属性配置:

server:
  port: 8080
  servlet:
    context-path: /api/xxx/

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/xxx?useUnicode=true&amp;useJDBCCompliantTimezoneShift=true&amp;useLegacyDatetimeCode=false&amp;serverTimezone=UTC
    driverClassName: com.mysql.cj.jdbc.Driver
    username: xxx
    password:
    continueOnError: false
    maximum-pool-size: 20
    minimum-idle: 0
    idle-timeout: 10000
    connection-timeout: 10000
    max-lifetime: 10000
    auto-commit: true

  jpa:
    show-sql: false
    hibernate:
      ddlAuto: none
    properties:
      hibernate:
        dialect: org.hibernate.dialect.MySQLDialect
        format_sql: true

startDayInWeek: 2

logging:
  level:
    com.zaxxer.hikari.HikariConfig: DEBUG
    com.ntt.th: DEBUG
  pattern: "%d{yyyy-MM-dd HH:mm:ss.SSS} [%t] %-5level %logger{36} - %msg%n"
英文:

I'm currently working with Springboot. now I want to query data by Java.util.date via JpaRepository and I have the following code in my interface.

List&lt;MyEnity&gt; findByDate(Date date);

I use Java.util.date and I have a personal reason that I couldn't change to java.time. I've ensured that
MyEntity Class in date field has TemporalType.DATE like this

@Temporal(TemporalType.DATE) private Date date;

and also the date field in MySQL is also date type

enter image description here
I tried to use the findByDate(Date date) method above and the date paramter in the method is obviously exist in my database but I always get an Empty List...

The other methods like findByName(String name) or findAll() work just fine.

I've tried to log the SQL statement from hibernate and I found that the binding parameter [DATE] might be in a different format? in my db is 'yyyy-mm-dd'

here is a log where I query with id and date. and at the bottom of the log I notice that the Id is binded with query statement, but date won't

    2563-04-10 12:18:54.649 [restartedMain] INFO  o.s.b.d.a.ConditionEvaluationDeltaLoggingListener - Condition evaluation unchanged
2563-04-10 12:19:03.241 [http-nio-8080-exec-2] INFO  o.a.c.c.C.[.[.[/api/productionplan] - Initializing Spring DispatcherServlet &#39;dispatcherServlet&#39;
2563-04-10 12:19:03.282 [http-nio-8080-exec-2] INFO  o.s.web.servlet.DispatcherServlet - Initializing Servlet &#39;dispatcherServlet&#39;
2563-04-10 12:19:03.307 [http-nio-8080-exec-2] INFO  o.s.web.servlet.DispatcherServlet - Completed initialization in 8 ms
2563-04-10 12:19:03.346 [http-nio-8080-exec-2] DEBUG org.hibernate.SQL - 
    select
        planibtout0_.planibtoutid as planibto1_23_,
        planibtout0_.ibtqty as ibtqty2_23_,
        planibtout0_.finisheddate as finished3_23_,
        planibtout0_.finishedshiftid as finished4_23_,
        planibtout0_.itemid as itemid6_23_,
        planibtout0_.itemclassid as itemclas5_23_,
        planibtout0_.planfinishedgoodid as planfini7_23_,
        planibtout0_.planibtoutlotid as planibto8_23_,
        planibtout0_.planqty as planqty9_23_,
        planibtout0_.producerstoreid as produce10_23_,
        planibtout0_.sellerstoreid as sellers11_23_,
        planibtout0_.updateat as updatea12_23_,
        planibtout0_.updateby as updateb13_23_
    from
        planibtout planibtout0_
    where
        planibtout0_.producerstoreid=1117
        and planibtout0_.finisheddate=?
2563-04-10 12:19:03.359 [http-nio-8080-exec-2] TRACE o.h.type.descriptor.sql.BasicBinder - binding parameter [1] as [DATE] - [Tue Apr 15 00:00:00 ICT 1477]
2563-04-10 12:19:03.363 [http-nio-8080-exec-2] INFO  c.n.t.t.s.c.SellerStoreController - -Data Not Found- No record found in database

here is my .yml properties

    server:
  port: 8080
  servlet:
    context-path: /api/xxx/

spring:
    datasource:
      url: jdbc:mysql://localhost:3306/xxx?useUnicode=true&amp;useJDBCCompliantTimezoneShift=true&amp;useLegacyDatetimeCode=false&amp;serverTimezone=UTC
      driverClassName: com.mysql.cj.jdbc.Driver
      username: xxx
      password: 
      continueOnError: false
      maximum-pool-size: 20
      minimum-idle: 0
      idle-timeout: 10000
      connection-timeout: 10000
      max-lifetime: 10000
      auto-commit: true

    jpa:
      show-sql: false
      hibernate:
        ddlAuto: none
      properties:
        hibernate:
          dialect: org.hibernate.dialect.MySQLDialect
          format_sql: true

startDayInWeek: 2

logging:
  level:
    com.zaxxer.hikari.HikariConfig: DEBUG
    com.ntt.th: DEBUG
  pattern:
    console: &quot;%d{yyyy-MM-dd HH:mm:ss.SSS} [%t] %-5level %logger{36} - %msg%n&quot;

答案1

得分: 1

实际上,Date 绑定没有问题,这就是为什么查询执行得很好。可能的问题是应用程序时区和数据库时区不匹配。

您正在使用印度支那时区(ICT)的时间 Tue Apr 15 00:00:00 ICT 1477,但是您在数据库中使用的是 serverTimezone=UTC(在 JDBC URL 中),这意味着您正在使用UTC时区作为数据库的时区。

因此,您可以通过将数据库的时区更改为 serverTimezone=ICT 来使用印度支那时区(ICT)。

英文:

Actually, there is no problem with Date binding that why query executed perfectly.
May be problem is Application Timezone and Database timezone is not matching.

You are sending DATE with IndoChina Timezone(ICT) Tue Apr 15 00:00:00 ICT 1477 but you are using serverTimezone=UTC(In JDBC Url) for database which means you are using UTC Timezone for Database.

So, you can change the timezone for database using serverTimezone=ICT to use IndoChina Timezone(ICT)

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

发表评论

匿名网友

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

确定