1

I am using spring-data-jpa in my project. I was trying to fetch records for last last week using @Query annotation.

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);

Native Query

@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 -

Native Query

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

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
Kuldeep Singh
  • 1,214
  • 4
  • 18
  • 45
  • Maybe usage of Date is wrong in JPQL (java.util.Date or javax.sql.Date). javax.sql.Date is supposed to be fore date only, javax.sql.Time for time only and javax.sql.Timestamp for both. Maybe there is a part of your date that is missing (just a guess). – B. Bri May 29 '18 at 09:09
  • On DB side, its data type as DATE. I am storing as '2018-05-27'. On Java side, I am using java.util.date. – Kuldeep Singh May 29 '18 at 09:13
  • So as far as I know DATE on SQL does not contains time of the day but java.util.Date does. Can you try using javax.sql.Date to see if it makes a difference? – B. Bri May 29 '18 at 09:19
  • Also can you check the parameters that will fill your query in the logs: spring.jpa.properties.hibernate.show_sql=true spring.jpa.properties.hibernate.use_sql_comments=true spring.jpa.properties.hibernate.format_sql=true – B. Bri May 29 '18 at 09:25
  • @Temporal(TemporalType.DATE) @Param("date") Date date could also help. – B. Bri May 29 '18 at 09:30
  • yes. This has something to do with java.util.date only. If I set hours,minutes,seconds, milliseconds in the date to 0, before passing to the Query method, it works and returns 6 records as expected. But not sure why it works after this. Should not this work before setting to 0 as well, as I am using TemporalType.DATE and in DB also storing only date part. – Kuldeep Singh May 30 '18 at 06:49

0 Answers0