CODEWITHSUNDEEP
javahibernatejpa
Oleg Galako
Oleg Galako

Reputation: 1035

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?

Upvotes: 3

Views: 1657

Answers (2)

Bruno Medeiros
Bruno Medeiros

Reputation: 2399

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)

Upvotes: 4

lifeisfoo
lifeisfoo

Reputation: 16314

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.

Upvotes: 1

Related Questions