Register / Login
Win a copy of
97 Things Every Java Programmer Should Know
this week in the
Java in General
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 2 years 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
Time is the best teacher, but unfortunately, it kills all of its students - Robin Williams. tiny ad:
Devious Experiments for a Truly Passive Greenhouse!
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