英文:
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&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: "%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<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
得分: 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)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论