I'm trying to fetch inventories which is opened at last month, for traditional SQL DATE_SUB function should be used here for date calculation. So the HQL is like following
|
1 2 |
select count(id) from Inventory where type=1 and agent.id=:userId and openTime>=CONCAT(YEAR(DATE_SUB(NOW(), INTERVAL 1 MONTH)), '-', MONTH(DATE_SUB(NOW(), INTERVAL 1 MONTH)), '-', '21', ' 00:00:00') and openTime<=CONCAT(YEAR(DATE_SUB(NOW(), INTERVAL 1 MONTH)), '-', MONTH(DATE_SUB(NOW(), INTERVAL 1 MONTH)), '-', '31', ' 23:59:59') |
But following error appeared when running it in Hibernate QL Query Consonle in Intellij IDEA
|
1 2 |
unexpected token: INTERVAL near line 1, column 125 [select count(id) from com.dfweb.model.Inventory where type=1 and agent.id=:userId and openTime>=CONCAT(YEAR(DATE_SUB(NOW(), INTERVAL 1 MONTH)), '-', MONTH(DATE_SUB(NOW(), INTERVAL 1 MONTH)), '-', '21', ' 00:00:00') and openTime<=CONCAT(YEAR(DATE_SUB(NOW(), INTERVAL 1 MONTH)), '-', MONTH(DATE_SUB(NOW(), INTERVAL 1 MONTH)), '-', '31', ' 23:59:59')] |
Unfortunatelly the INTERVAL keyword is not supported in HQL.
Solution
An alternative is to calculate the date in Java and pass the result dates to HQL as parameters. The code is like following
|
1 2 3 4 5 |
def openedCount = (Integer)this.session.createQuery("select count(id) from Inventory where type=1 and agent.id=:userId and openTime>=:startTime and openTime<=:endTime") .setString("startTime", DateUtil.getLastMonthFirstDay()+" 00:00:00") .setString("endTime", DateUtil.getLastMonthLastDay()+" 23:59:59") .setParameter("userId", user.id) .uniqueResult() |
Implementation code for DateUtil.getLastMonthFirstDay and DateUtil.getLastMonthLastDay
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
public static String getLastMonthFirstDay(){ Calendar calendar = Calendar.getInstance(); calendar.add(Calendar.MONTH, -1); calendar.set(Calendar.DAY_OF_MONTH, calendar.getActualMinimum(Calendar.DAY_OF_MONTH)); return new SimpleDateFormat(DATE_FORMAT).format(calendar.getTime()); } public static String getLastMonthLastDay(){ Calendar calendar = Calendar.getInstance(); calendar.add(Calendar.MONTH, -1); calendar.set(Calendar.DAY_OF_MONTH, calendar.getActualMaximum(Calendar.DAY_OF_MONTH)); return new SimpleDateFormat(DATE_FORMAT).format(calendar.getTime()); } |