使用Spring Data JpaRepository按计数排序 [英] Order by count using Spring Data JpaRepository
问题描述
我正在使用Spring Data JpaRepository，我发现它非常易于使用。我实际上需要所有这些功能 - 分页，排序，过滤。不幸的是，有一件令人讨厌的事情似乎迫使我回归使用普通的JPA。
I am using Spring Data JpaRepository and I find it extremely easy to use. I actually need all those features - paging, sorting, filtering. Unfortunately there is one little nasty thing that seems to force me to fall back to use of plain JPA.
我需要按照相关集合的大小来订购。例如，我有：
I need to order by a size of associated collection. For instance I have:
@Entity
public class A{
@Id
private long id;
@OneToMany
private List<B> bes;
//boilerplate
}
我必须按<$ c排序$ c> bes.size（）
有没有办法以某种方式自定义排序仍然利用分页，过滤和其他Spring数据很棒？
Is there a way to somehow customize the ordering still taking the advantage of pagination, filtering and other Spring Data great features?
推荐答案
我使用以下提示和灵感解决了这个难题：
I've solved the puzzle using hints and inspirations from:
我没有意识到的第一个也是最重要的事情 spring-data 就是前夕n使用 @Query 自定义方法，只需将 Pageable 对象作为参数传递，即可创建分页查询。这可以通过spring-data文档明确说明，因为它虽然非常强大，但绝对不是很明显。
The first and most important thing I've not been aware of about spring-data is that even using @Query custom methods one can still create paging queries by simply passing the Pageable object as parameter. This is something that could have been explicitely stated by spring-data documentation as it is definitely not obvious though very powerful feature.
很好，现在是第二个问题 - 我该如何实际上按照JPA中相关集合的大小对结果进行排序？我已成功进入以下JPQL：
Great, now the second problem - how do I actually sort the results by size of associated collection in JPA? I've managed to come to a following JPQL:
select new package.AwithBCount(count(b.id) as bCount,c) from A a join a.bes b group by a
其中 AwithBCount 是查询结果实际映射到的类：
where AwithBCount is a class that the query results are actually mapped to:
public class AwithBCount{
private Long bCount;
private A a;
public AwithBCount(Long bCount, A a){
this.bCount = bCount;
this.a = a;
}
//getters
}
我很兴奋现在简单地定义我的存储库，如下面的那个
Excited that I can now simply define my repository like the one below
public interface ARepository extends JpaRepository<A, Long> {
@Query(
value = "select new package.AwithBCount(count(b.id) as bCount,c) from A a join a.bes b group by a",
countQuery = "select count(a) from A a"
)
Page<AwithBCount> findAllWithBCount(Pageable pageable);
}
我急忙尝试解决方案。完美 - 返回页面，但当我尝试按bCount排序时，我感到很失望。原来，由于这是一个ARepository（不是AwithBCount存储库），spring-data将尝试在A中查找bCount属性而不是AwithBCount。最后我最终得到了三种自定义方法：
I hurried to try my solution out. Perfect - the page is returned but when I tried to sort by bCount I got disappointed. It turned out that since this is a ARepository (not AwithBCount repository) spring-data will try to look for a bCount property in A instead of AwithBCount. So finally I ended up with three custom methods:
public interface ARepository extends JpaRepository<A, Long> {
@Query(
value = "select new package.AwithBCount(count(b.id) as bCount,c) from A a join a.bes b group by a",
countQuery = "select count(a) from A a"
)
Page<AwithBCount> findAllWithBCount(Pageable pageable);
@Query(
value = "select new package.AwithBCount(count(b.id) as bCount,c) from A a join a.bes b group by a order by bCount asc",
countQuery = "select count(a) from A a"
)
Page<AwithBCount> findAllWithBCountOrderByCountAsc(Pageable pageable);
@Query(
value = "select new package.AwithBCount(count(b.id) as bCount,c) from A a join a.bes b group by a order by bCount desc",
countQuery = "select count(a) from A a"
)
Page<AwithBCount> findAllWithBCountOrderByCountDesc(Pageable pageable);
}
...以及服务级别的一些额外条件逻辑（可能是用抽象存储库实现封装）。因此，尽管不是非常优雅，但这样做了 - 这种方式（具有更复杂的实体）我可以按其他属性排序，进行过滤和分页。
...and some additional conditional logic on service level (which could be probably encapsulated with an abstract repository implementation). So, although not extremely elegant, that made the trick - this way (having more complex entities) I can sort by other properties, do the filtering and pagination.
这篇关于使用Spring Data JpaRepository按计数排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
