Search code examples
javajpaplayframeworkhql

JPQL/HQL - Is it possible to specify the order, by providing a list of IDs?

(Note: I'm using the Play! framework, which uses Hibernate as the JPA implementation.)

I have a list of IDs and I want to get the Items from the database by keeping the order that is used in my ID list.

Let's say my list is: 444,222,333,111

I want the JPQL/HQL to return the Item with id #444 first, then the one with id #222, etc.

I tried something like: 

id in (444,222,333,111) order by id=444 DESC, id=222 DESC, id=333 DESC, id=111 DESC

But it doesn't seem to work.

Is it possible or will I have to forget the "order by" part and manually re-order the Items once returned?

Solution

  • If there's no "natural" order then it's likely you'll need to order manually; you might be able to rely on DB ordering depending on the DB and query construction, but IMO that's risky.

    On the plus side, unless you have a huge number of objects, the overhead is trivial.