Register / Login
this forum made possible by our volunteer staff, including ...
Stephan van Hulst
JPQL Query and Native Query returning different results in spring-data-jpa
posted 1 year ago
I am using
in my project. I was trying to fetch records for last last week using
@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);
Expected records should be 6 as I have records for 6 days in database.
JPQL Query is giving 6 records which is expected.
Native Query is giving 5 records, which is not correct.
But when I run the native query in my db tool, it fetches correct 6 records.
Using mysql as my database.
I am passing the value as '2018-05-22'. When I tried to debug native query is not fetching the records for 2018-05-22 even when I have condition as TRANS_DATE >= :date
Anyone have faced similar issue. What could be the possible reason.
Query printed by hibernate in both cases -
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
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
Records in my database, fetched using sql query in my db tool.(First record is not coming when using Native Query)
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
Don't get me started about those stupid
Boost this thread!
Hibernate JPQL/HQL: aggregate functions show results of wrong table/entity joined twice
Uni-directional Many-to-many problem
Problem with Hibernate Criteria, Projections and associations
Join Fetch using EclipseLink
I need a comma after my left outer join