使用 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
}

我必须按 bes.size()

有没有办法以某种方式自定义排序，同时仍然利用分页、过滤和其他 Spring Data 的强大功能?

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:

1. 使用@Query 注释限制结果集 by Koitoer
2. 如何在 JPA 中按 count() 排序 by MicSim
3. 我自己的详尽实验

1. Limiting resultset using @Query anotations by Koitoer
2. How to order by count() in JPA by MicSim
3. Exhaustive experiments on my own

关于spring-data 是即使使用 @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屋！