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

Question

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?

Polymorphism

Answer 1

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)

Irritated

Sorry for not replying, had a lot of stuff to do. Just tested it and it works. Thank you! The final version is: select q from Question q where q.id in (:ids) ORDER BY FIELD(id, :ids) So you can edit your answer a little to be more complete. – Polymorphism

Just some extra tips.. check if the list os ids is empty and do not run the query in this case, hibernate will generate a invalid query and thus a syntax exception. – Irritated

Answer 2

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.

Sememe