JPA Criteria Query, ordering the result using a list of ordered ids

Question

Lets say I have an "Item" table in my database and an "Item" entity object in my Java code JPA mapping.

And I am given a sorted list of "Item" ids, for which I want to retrieve the actual "Items" from the database, with the same ordering as in the given list.

Now I can't really just retrieve the items first and then order them because I only want to retrieve a small range of these items from the database after the ordering.

Is this possible to do? I know that there are ways of doing this using pure SQL, e.g.

Ordering by the order of values in a SQL IN() clause

ORDER BY the IN value list

Lukas Eder · Answer 1 · 2021-08-20T08:27:51.257

I usually call this technique "sort indirection", where you sort a data set according to another pre-sorted data set. In SQL, you could use the FIELD() function in MySQL (as per your linked questions), or in more standard SQL, you'd use something like:

ORDER BY 
  CASE value WHEN 3 THEN 1
             WHEN 5 THEN 2
             WHEN 1 THEN 3
                    ELSE 4 END,
  secondary_criteria

This would produce values ordered as [3, 5, 1, ...]

Now, CASE is something you can do with the criteria API as well:

cb.selectCase()
  .when(..., ...)
  .when(..., ...)
  .when(..., ...)
  .otherwise(...)

JPA Criteria Query, ordering the result using a list of ordered ids

1 Answers1