java - JPQL Query 和 Native Query 在 spring-data-jpa 中返回不同的结果

Question

我spring-data-jpa在我的项目中使用。@Query我试图使用注释获取上周的记录。

JPQL

@Query("SELECT SUM(t.quantity) as QTY, SUM(t.retailAmount) as RETAIL_AMT, SUM(t.discountAmount) as DISCOUNT_AMT, SUM(t.netAmount) as NET_AMT, SUM(t.costAmount) as COST_AMT, SUM(t.profit) as PROFIT, t.transactionDate as DATE FROM SaleByTransaction t where t.transactionDate >= :date group by t.transactionDate")
public List<Object[]> aggregateTransactionsBasedOnDate(@Param("date") Date date);

本机查询

@Query(nativeQuery = true, value = "SELECT SUM(QTY) as QTY, SUM(RETAIL_AMT) as RETAIL_AMT, SUM(DISCOUNT_AMT) as DISCOUNT_AMT, SUM(NET_AMT) as NET_AMT, SUM(COST_AMT) as COST_AMT, SUM(PROFIT) as PROFIT, TRANS_DATE FROM SALE_BY_TRANSACTION where TRANS_DATE >= :date group by TRANS_DATE")
public List<Object[]> aggregateTransactionsBasedOnDate(@Param("date") Date date);

预期的记录应该是 6，因为我在数据库中有 6 天的记录。

JPQL 查询给出了 6 条记录，这是预期的。Native Query 给出 5 条记录，这是不正确的。

但是当我在我的数据库工具中运行本机查询时，它会获取正确的 6 条记录。使用 mysql 作为我的数据库。

我将值传递为“2018-05-22”。当我尝试调试本机查询时，即使我的条件为 TRANS_DATE >= :date，也没有获取 2018-05-22 的记录

任何人都面临过类似的问题。可能的原因是什么。

在这两种情况下由休眠打印的查询 -

本机查询

Hibernate: SELECT SUM(QTY) as QTY, SUM(RETAIL_AMT) as RETAIL_AMT, SUM(DISCOUNT_AMT) as DISCOUNT_AMT, SUM(NET_AMT) as NET_AMT, SUM(COST_AMT) as COST_AMT, SUM(PROFIT) as PROFIT, TRANS_DATE FROM SALE_BY_TRANSACTION where TRANS_DATE >= ? group by TRANS_DATE

JPQL

Hibernate: select sum(salebytran0_.qty) as col_0_0_, sum(salebytran0_.retail_amt) as col_1_0_, sum(salebytran0_.discount_amt) as col_2_0_, sum(salebytran0_.net_amt) as col_3_0_, sum(salebytran0_.cost_amt) as col_4_0_, sum(salebytran0_.profit) as col_5_0_, salebytran0_.trans_date as col_6_0_ from sale_by_transaction salebytran0_ where salebytran0_.trans_date>=? group by salebytran0_.trans_date

我的数据库中的记录，在我的数据库工具中使用 sql 查询获取。（使用本机查询时没有第一条记录）

QTY     RETAIL_AMT          DISCOUNT_AMT        NET_AMT             COST_AMT            PROFIT              DATE
54.0    586.760003566742    27.769999504089355  558.9900002479553   313.270001411438    245.75999808311462  2018-05-22
95.0    399.7000057697296   16.479999780654907  383.2200062274933   212.6899983882904   170.5800033658743   2018-05-23
64.0    609.2199983596802   25.700000166893005  583.5199975967407   369.6899971961975   213.89000129699707  2018-05-24
62.0    474.439998626709    60.47999978065491   413.95999908447266  257.2700011730194   156.75999996066093  2018-05-25
33.0    342.32999992370605  2.75                339.57999992370605  193.520001411438    146.08999752998352  2018-05-26
73.0    426.76999855041504  10.600000143051147  416.17000007629395  241.5299997329712   174.7099997550249   2018-05-27

标签： javamysqlspringjpaspring-data-jpa