How to keep order provided in “in” clause in Sprin

Question

This question already has an answer here:

• Spring data repository: findBySomething***In*** and result order 2 answers

I have a pretty simple query which retrieves values base on "in" clause. List that comes as "in" argument is appropriately sorted.

Query :

@Query(value = "select i from ItemEntity i where i.secondaryId in :ids")
List<ItemEntity> itemsIn(@Param("ids") List<UUID> ids, Pageable pageable);

I need results to be ordered the same way as List<UUID> ids, is it possible to achieve this without plain sql but only with the help of Spring Data and/or Hibernate.

Answer 1

You can do that by JPA too but you will have to create a comma separated list of ids in the order you want. In your case you can keep same order.

@Query(value = "select i from ItemEntity i where i.secondaryId in :ids 
       order by FIND_IN_SET(i.secondaryId, :idStr)")
List<ItemEntity> itemsIn(@Param("ids") List<UUID> ids, @Param("idStr") String idStr);

To create comma separated list you can java 8 stream:

ids.stream().map(Object::toString).collect(Collectors.joining(","));

Example:

SELECT id FROM User WHERE id in (2,3,1) 
ORDER BY FIND_IN_SET(id,"2,3,1");

Result:

+----+
| id |
+----+
|  2 |
|  3 |
|  1 |
+----+