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

Viewed 470

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<MyEnity> 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 'dispatcherServlet'
2563-04-10 12:19:03.282 [http-nio-8080-exec-2] INFO  o.s.web.servlet.DispatcherServlet - Initializing Servlet 'dispatcherServlet'
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&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&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: "%d{yyyy-MM-dd HH:mm:ss.SSS} [%t] %-5level %logger{36} - %msg%n"
1 Answers

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)

Related