What is the best way of selecting a set of objects by given ids in given order with Hibernate/JPA

I want to select a number of objects with given ids but also in given order, something like:

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

But ordered the same way as ids in the parameter.

E.g. in mysql it can be done like this:

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

What is the开发者_运维知识库 best way?

Hibernate keeps the functions it doesn't know and pass them to SQL as they were written, so, assuming that you're using MySQL, have you tried to write your HQL with the 'ORDER BY FIELD' clause? Something like...

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

To read some updated, and I hope useful, informations about this topic read this answer related to the same problem but using postgres. There are also some database agnostic solutions.