hibernate - How to get select within a select using Criteria

Question

I have the following mysql statement SELECT * FROM (SELECT * FROM NINJA ORDER BY NAME LIMIT 0,5) AS TABLE ORDER BY NAME DESC. I don't know how am I gonna convert it into hibernate criteria. The reason Im doing this kind of select statement is to get the first 5 result then order it descending. In my current criteria in which Im doing the normal Order.addOrder(Order.desc(field)) what happens is it gets the last 5 result of the whole record.

Please help. Thanks in advance.

UPDATE:

Below are some of my codes :

Criteria ninjaCriteria = session.createCriteria(Ninja.class);
ninjaCriteria.setFirstResult(firstResult);
ninjaCriteria.setMaxResults(maxResult);
if (isAscending)
    ninjaCriteria.addOrder(Order.asc(field));
else
    ninjaCriteria.addOrder(Order.desc(field));

Note: firstResult, maxResult, isAscending, and field are variables.

Even with HQL derived table is not possible: http://stackoverflow.com/questions/2433729/subquery-using-derived-table-in-hibernate-hql, and with criteria query you can go as far as sub-query with DetachedCriteria and another restriction is that DetachedCriteria does not have setMaxResult — Snehal Patel, Jun 05 '13 at 11:06

score 3 · Answer 1 · answered Jun 02 '13 at 10:55

3

Try this one. i hope it will work

Criteria ninjaCriteria = session.createCriteria(Ninja.class);
 ninjaCriteria.addOrder(Order.desc("NAME"));
ninjaCriteria.setMaxResults(5);

answered Jun 02 '13 at 10:55

Arvind

1,548
17
23

2

This will get the last five sorted in descending order. The OP wants the _first_ five sorted in descending order. – Old Pro Jun 05 '13 at 15:53

score 2 · Answer 2 · answered Jun 05 '13 at 15:21

The only way how you can solve this without using pure SQL, for my opinion, is to select the list of Ninjas as usual:

Criteria ninjaCriteria = session.createCriteria(Ninja.class);
ninjaCriteria.addOrder(Order.asc("NAME"));
ninjaCriteria.setMaxResults(5);

create a custom Comparator:

/**
 * Compare Ninjas by Name descending
 */
private static Comparator<Ninja> ninjaNameComparator = new Comparator<Ninja>() {
    public int compare(Ninja ninja1, Ninja ninja2) {
        return ninja2.getName().compareTo(ninja1.getName());
    }
};

and use it to sort that list descending:

Collection<Ninja> ninjas = ninjaDao.listNinja();
Collections.sort(ninjas, ninjaNameComparator);

From bussiness point of view it's absolutely identical and do not required hard solutions.

hibernate - How to get select within a select using Criteria

2 Answers2