How to write the following MYSQL query in criteria query and hibernate query?

Question

How can I write the criteria query and hibernate query for the following MySQL query

SELECT * FROM (SELECT * FROM outdatadetail where algorithmno="a0025_d2" and stringno=01 ORDER BY testid desc) sub_query GROUP BY subjectid;

Any suggestions.

Answer 1

String sql = "SELECT * FROM (SELECT * FROM outdatadetail where algorithmno='a0025_d2' and stringno=01 ORDER BY testid desc) sub_query GROUP BY subjectid;";
Session session = getSession().getSessionFactory().getCurrentSession(); 
Query query = session.createSQLQuery(sql);

Answer 2

As far as I understand after reading the documentation and looking at examples you don't need a sub-query to do what you are trying to.

Basically you write 1 query and set a projection to do the grouping.

Criteria query = currentSession.createCriteria(OutDataDetail.class);
query.setProjection(Projections.groupProperty("subjectid").as("subjectid"));
query.add(Restrictions.eq("algorithmno", "a0025_d2"));
query.add(Restrictions.eq("stringno", "01"));
query.addOrder(Order.desc("testid"));
return query.list();

The Criteria API by itself is fairly useful. But its real power comes when you start using classes like Projection, Subqueries, Order etc. in conjunction with your Criteria.

If you want to use the Criteria API with a sub-query you can do the following:

DetachedCriteria subquery = currentSession.createCriteria(OutDataDetail.class);
subquery.add(Restrictions.eq("algorithmno", "a0025_d2"));
subquery.add(Restrictions.eq("stringno", "01"));
subquery.addOrder(Order.desc("testid"));

Criteria query = currentSession.createCriteria(OutDataDetail.class);
query.setProjection(Projections.groupProperty("subjectid").as("subjectid"));
query.add(Subqueries.exists(subquery);

return query.list();

Both implementations should return a list of OutDataDetail objects (assuming that's the object you are working with).

DISCLAIMER: I have not tried any of this. It may be that this will not work for you. This answer is written based on my knowledge of working with the Criteria API and its associated classes in the past, and the Hibernate 4.1 Manual. You can see the manual section on Projections and grouping here.