What is the best way to select a set of objects by given identifiers in a given order with Hibernate / JPA

Question

I want to select several objects with the given identifiers, but also in the given order, something like:

<named-query name="getQuestionsByIds">
    <query><![CDATA[from Question q where q.id in (:ids)]]></query>
</named-query>

But it is ordered in the same way as identifiers in the parameter.

eg. in mysql this can be done as follows:

SELECT * FROM table ORDER BY FIELD( id, 23, 234, 543, 23 )

What is the best way?

Answer 1

Hibernate saves functions that it does not know, and passes them to SQL as they were written, so assuming you are using MySQL, have you tried writing your HQL with an ORDER BY FIELD clause? Sort of...

select q from Question q where q.id in (:ids) ORDER BY FIELD(id, :ids)

Answer 2

, , , , , postgres. .