SpringBoot JPA @Query Date类型参数注入问题

吴_大_鹏 2024-12-24 14:45:20

环境：

java8

spring boot：2.5.15

jpa：2.5.14

mysql：8.0.40-0ubuntu0.22.04.1

问题：为何我第二个方法，注入参数变为x'xxxx'这种，为何不是正确注入时间的，sql 1是想办法解决sql 2查询不正确弄出来的，求大佬指点下。

/** sql 1 (正确查询结果) */
@Query(value = "select SUM(som.quantity) from s_material som where date_format(som.create_time, '%y%m%d') >= date_format(:beginTime, '%y%m%d') and date_format(som.create_time, '%y%m%d') <= date_format(:endTime, '%y%m%d') and som.status = :status ", nativeQuery = true)
    Optional<Long> sumQuantity(@Param("status") Integer status, @Param("beginTime") String beginTime, @Param("endTime") String endTime);

/** sql 2 （异常查询结果）*/
    @Query(value = "select SUM(som.quantity) from s_material som where Date(som.create_time) between :beginTime and :endTime and som.status = :status ", nativeQuery = true)
    Optional<Long> sumQuantity(@Param("status") Integer status, @Param("beginTime") Date beginTime, @Param("endTime") Date endTime);

实际打印的SQL如下：

sql 1查询结果（正确）：
Hibernate: select SUM(som.quantity) from s_material som where (? is null or date_format(som.create_time, '%y%m%d') >= date_format(?, '%y%m%d')) and (? is null or date_format(som.create_time, '%y%m%d') <= date_format(?, '%y%m%d')) and som.status = ? 
EXECUTE SQL: 
EXPLAIN select SUM(som.quantity) from s_material som where date_format(som.create_time, '%y%m%d') >= date_format('2021-05-16', '%y%m%d') and date_format(som.create_time, '%y%m%d') <= date_format('2024-12-16', '%y%m%d') and som.status = 3 
查询结果: 18893

sql 2查询结果（错误）：
Hibernate: select SUM(som.quantity) from s_material som where Date(som.create_time) between ? and ? and som.status = ? 
EXECUTE SQL: 
EXPLAIN select SUM(som.quantity) from s_material som where Date(som.create_time) between x'aced00057372001c636e2e6875746f6f6c2e636f72652e646174652e4461746554696d65b51e93fbc5c4e4f00200044900166d696e696d616c44617973496e46697273745765656b5a00076d757461626c654c000e66697273744461794f665765656b74001a4c636e2f6875746f6f6c2f636f72652f646174652f5765656b3b4c000874696d655a6f6e657400144c6a6176612f7574696c2f54696d655a6f6e653b7872000e6a6176612e7574696c2e44617465686a81014b597419030000787077080000017970c180007800000000017e720018636e2e6875746f6f6c2e636f72652e646174652e5765656b00000000000000001200007872000e6a6176612e6c616e672e456e756d000000000000000012000078707400064d4f4e4441597372001a73756e2e7574696c2e63616c656e6461722e5a6f6e65496e666f24d1d3ce001d719b020008490008636865636b73756d49000a647374536176696e67734900097261774f666673657449000d7261774f6666736574446966665a001377696c6c474d544f66667365744368616e67655b00076f6666736574737400025b495b001473696d706c6554696d655a6f6e65506172616d7371007e000a5b000b7472616e736974696f6e737400025b4a787200126a6176612e7574696c2e54696d655a6f6e6531b3e9f57744aca10200014c000249447400124c6a6176612f6c616e672f537472696e673b787074000d417369612f5368616e676861699da620120000000001b774000000000000757200025b494dba602676eab2a502000078700000000401b7740001bcafd801ee62800036ee8070757200025b4a782004b512b1759302000078700000001fffdfdae01dc00001ffe0503f65828000ffe8b5052c400032ffe8ec0bb4980000fff269d192400032fff294ef65d80000fff2c631a4800032fff310da6b980000fff32dd5a0000032fff4d376dd980000fff5258da6c00032fff5524777180000fff59160cd000032fff5d1bcdc180000fff60c54a1800032fff63d9002580000fff681cfb4400032fff68a7d009800000007807d0cd000320007ab487aa800000007eee3611000320008207127a800000008664cd650003200089599d4a800000008db758350003200090d0349e800000009509e305000320009822bf6e800000009c5c6dd5000320009f754a3e80000001ec49332c00000' and x'aced00057372001c636e2e6875746f6f6c2e636f72652e646174652e4461746554696d65b51e93fbc5c4e4f00200044900166d696e696d616c44617973496e46697273745765656b5a00076d757461626c654c000e66697273744461794f665765656b74001a4c636e2f6875746f6f6c2f636f72652f646174652f5765656b3b4c000874696d655a6f6e657400144c6a6176612f7574696c2f54696d655a6f6e653b7872000e6a6176612e7574696c2e44617465686a81014b5974190300007870770800000193cb0c48007800000000017e720018636e2e6875746f6f6c2e636f72652e646174652e5765656b00000000000000001200007872000e6a6176612e6c616e672e456e756d000000000000000012000078707400064d4f4e4441597372001a73756e2e7574696c2e63616c656e6461722e5a6f6e65496e666f24d1d3ce001d719b020008490008636865636b73756d49000a647374536176696e67734900097261774f666673657449000d7261774f6666736574446966665a001377696c6c474d544f66667365744368616e67655b00076f6666736574737400025b495b001473696d706c6554696d655a6f6e65506172616d7371007e000a5b000b7472616e736974696f6e737400025b4a787200126a6176612e7574696c2e54696d655a6f6e6531b3e9f57744aca10200014c000249447400124c6a6176612f6c616e672f537472696e673b787074000d417369612f5368616e676861699da620120000000001b774000000000000757200025b494dba602676eab2a502000078700000000401b7740001bcafd801ee62800036ee8070757200025b4a782004b512b1759302000078700000001fffdfdae01dc00001ffe0503f65828000ffe8b5052c400032ffe8ec0bb4980000fff269d192400032fff294ef65d80000fff2c631a4800032fff310da6b980000fff32dd5a0000032fff4d376dd980000fff5258da6c00032fff5524777180000fff59160cd000032fff5d1bcdc180000fff60c54a1800032fff63d9002580000fff681cfb4400032fff68a7d009800000007807d0cd000320007ab487aa800000007eee3611000320008207127a800000008664cd650003200089599d4a800000008db758350003200090d0349e800000009509e305000320009822bf6e800000009c5c6dd5000320009f754a3e80000001ec49332c00000' and som.status = 3 
查询结果：0

...全文