问题描述
我有一个非常简单的查询，它根据in"子句检索值.作为in"参数出现的列表被适当地排序.
I have a pretty simple query which retrieves values base on "in" clause. List that comes as "in" argument is appropriately sorted.
查询:
@Query(value = "select i from ItemEntity i where i.secondaryId in :ids")
List<ItemEntity> itemsIn(@Param("ids") List<UUID> ids, Pageable pageable);
我需要以与 List<UUID> 相同的方式对结果进行排序.ids，是否有可能在没有普通 sql 的情况下实现这一点，但只能在 Spring Data 和/或 Hibernate 的帮助下实现.
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.
推荐答案
你也可以通过 JPA 做到这一点，但你必须按照你想要的顺序创建一个逗号分隔的 id 列表.在您的情况下，您可以保持相同的顺序.
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);
要创建逗号分隔列表，您可以使用 java 8 流:
To create comma separated list you can java 8 stream:
ids.stream().map(Object::toString).collect(Collectors.joining(","));
例子:
SELECT id FROM User WHERE id in (2,3,1)
ORDER BY FIND_IN_SET(id,"2,3,1");
结果:
+----+
| id |
+----+
| 2 |
| 3 |
| 1 |
+----+
还有一种使用 JPQL 的替代方法:
There is also one alternative using JPQL:
You can use ,,FIELD'' instead of ,,FIND_IN_SET ''.
您可以在此处找到示例:https://stackoverflow.com/a/65943906/15101302
You can find the example here: https://stackoverflow.com/a/65943906/15101302
这篇关于如何保持“in"中提供的订单Spring Data JPA 或 Hibernate 中的子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！