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 J开发者_如何学编程PA 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?

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.

I think you'll have to sort the items in Java:

public void sortItems(List<Item> items, final List<Long> ids) {
    Collections.sort(items, new Comparator<Item>() {
        @Override
        public int compare(Item i1, Item i2) {
            int index1 = ids.indexOf(i1.getId());
            int index2 = ids.indexOf(i2.getId());
            return Integer.valueOf(index1).compareTo(Integer.valueOf(index2));
        }
    });
}

Even old questions are asked much later again. I solved this using a mapping:

@Override
public List<MyItem> fetch(List<Long> ids) {
    // Create an identity map using the generic fetch. Rows are returned, but not necessarily in the same order
    Map<Long, MyItem> resultMap = super.fetch(ids).stream().collect(Collectors.toMap(MyItem::getId, Function.identity()));
    // Now return the rows in the same order as the ids, using the map.
    return ids.stream().map(id -> resultMap.get(id)).collect(Collectors.toList());
}

This particular case would be easily handled using ORDER BY id DESC, but I'm guessing that it's only a poorly chosen example.

I'd say it's not possible unless you can find a way to take advantage of SQL to do it.

You should have a persistence layer to map objects from a database into Java (you should not be passing ResultSet out of the persistence layer). It'd be trivial to do it there.

A little easier

items.sort(Comparator.comparingLong(i -> ids.indexOf(i.getId())));