sql to hql throwing exception

Question

I'm using hibernate in my project and I'm trying to convert an existing sql query from DaoImplementation class to hql,

The sql query I have is

JdbcTemplate select = new JdbcTemplate(dataSource);
    String sql = "SELECT * FROM (SELECT site_id,rtc,sigplan,cycle_time,health,phase_no,phase_time,active_groups,groupscolour,ip "+
                 "FROM status_data where  rtc>='" + fromDate + "' and rtc<'" + toDate + "' and "+
                 "site_id=" + SiteId + " order by rtc desc limit "+recordLimit+" )as temp ORDER BY RTC ASC";

I wrote the hql version to get data from HealthLog table as

String hql = " select f from (select h from HealthLog h where rtc>='"+fromDate+"' and rtc <'"+toDate+"' "
            + "and siteId = "+siteId+" order by rtc desc limit "+limit+" ) as f order by rtc asc ";
    return super.readListByHql(hql);

But the above hql throws the following exception

org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: ( near line 1, column 16 [ select f from (select h from com.traff.hibernate.model.HealthLog as h where rtc>='1974-08-01 14:10:00.0' and rtc <'1974-09-01 23:46:20.6' and siteId = 20 order by rtc desc limit 50000 ) as f order by rtc asc ]
at org.hibernate.hql.internal.ast.QuerySyntaxException.convert(QuerySyntaxException.java:54)
at org.hibernate.hql.internal.ast.QuerySyntaxException.convert(QuerySyntaxException.java:47)
at org.hibernate.hql.internal.ast.ErrorCounter.throwQueryException(ErrorCounter.java:79)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:276)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:180)
at org.hibernate.hql.intern

I also tried the below code snippet but that giving me wrong results

Criteria criteria = createEntityCriteria();     
    criteria.add(Restrictions.ge("rtc", fromDate));
    criteria.add(Restrictions.lt("rtc", toDate));
    criteria.add(Restrictions.eq("siteId", siteId));
    criteria.setMaxResults(limit);
    criteria.addOrder(Order.asc("rtc"));            
    criteria2 = criteria;
    criteria2.addOrder(Order.desc("rtc"));      
    return criteria2.list();

Which is the correct way to achieve the result?

Answer 1

First of all, as already mentioned in the comments, you cannot do a subquery within the FROM clause in HQL. See: Hibernate Documentation

Secondly, the limit keyword is not supported by HQL. Usually you would use query.setFirstResult(0) and query.setMaxResults(recordLimit) methods where query has the type of the Query Interface. But since you are using the limit in a subquery, there is no way. See: How to set a limit to inner query in Hibernate?

Some options:

1. Use a native SQLQuery
2. Since you are only sorting in the outer Query. You could only execute the inner query and sort in Java.

Example for Option 2:

Session session = factory.openSession();
Query query = session
    .createQuery("FROM HealthLog "
        + "WHERE rtc >= :rtcL and rtc < :rtcG and siteId = :siteId "
        + "ORDER BY rtc DESC");
query.setParameter("rtcL", fromDate);
query.setParameter("rtcG", toDate);
query.setParameter("siteId", siteId);
query.setFirstResult(0);
query.setMaxResults(recordLimit);
List<HealthLog> res = query.list();
session.close();

Collections.sort(res, new Comparator<HealthLog>() {
  public int compare(HealthLog o1, HealthLog o2) {
    return o1.getRtc().compareTo(o2.getRtc());
  }
});

The query above returns HealthLogs with all attributes. If you want to only retrieve specific attributes, you can add a SELECT new HealthLog(siteId,rtc,sigplan,cycle_time,...) to your Query with a fitting constructor in HealthLog.

Please note that the code snippet might not be ready to use, since i do not know your model and attribute names.