Spring数据jpa按嵌套对象集合大小排序

Question

我正在使用 Spring Boot 应用程序，并且存在以下 2 个实体。

人.java

@Entity

@Data

@EqualsAndHashCode(callSuper = true)

@AllArgsConstructor

@NoArgsConstructor

@Table(name = "PERSON")

public class Person extends BaseEntity {

    @NotNull

    @Enumerated(EnumType.STRING)

    private StatusType status;

    @JsonIgnore

    @ManyToMany(fetch = FetchType.EAGER)

    @JoinTable(

            name = "PERSON_ADDRESS",

            joinColumns = @JoinColumn(

                    name = "person_id", referencedColumnName = "id"),

            inverseJoinColumns = @JoinColumn(

                    name = "address_id", referencedColumnName = "id"))

    private Collection<Info> addresses;

}

地址.java

@Entity

@Data

@EqualsAndHashCode(callSuper = true)

@AllArgsConstructor

@NoArgsConstructor

@Table(name = "ADDRESS")

public class Address extends BaseEntity {

    @NotNull

    private String address;

    @ManyToMany(mappedBy = "addresses")

    private Collection<Person> persons;

}

我正在使用JpaRepository如下。

@Repository

public interface PersonRepository extends JpaRepository<Person, Long> {

}

我使用以下语句来实现分页和排序。

Page<Person> allPersons = personRepository.findAll(pageable);

pageable的实例在哪里org.springframework.data.domain.Pageable。

我可以使用Person. 但我想根据地址集合，根据Address每个Person实体的记录数进行排序。

简而言之，我想根据 Collection 的集合大小对Persons 进行排序。排序顺序（ASC 或 DESC）来自前端。

知道Pageable对象应该如何实现它吗？还没有返回任何重复的Person，如果一个以上的记录Address存在一个Person。

Answer 1

这是根据使用 Spring Data JpaRepository 按计数排序的解决方案。在我的实验中，我尝试将 Repository 定义如下，但存在问题；因为我们正在定义PersonRepository基于 addressCount 进行排序的查询，spring 数据会查看 Person。@Repositorypublic interface PersonRepository extends JpaRepository<Person, Long> {&nbsp; &nbsp; @Query(&nbsp; &nbsp; &nbsp; &nbsp; value = "select p from Person p join p.addresses ad group by p",&nbsp; &nbsp; &nbsp; &nbsp; countQuery = "select count(p) from Person p"&nbsp; &nbsp; )&nbsp; &nbsp; Page <Person> findAllWithAddressCount(Pageable pageable);}因此，作为一种解决方法，我尝试将排序逻辑转移到查询定义本身中，最终得到了两个版本的 ASC 和 DESC 模式：@Repositorypublic interface PersonRepository extends JpaRepository <Person, Long> {&nbsp; &nbsp; @Query(&nbsp; &nbsp; &nbsp; &nbsp; value = "select p from Person p join p.addresses ad group by p Order By addressCount asc",&nbsp; &nbsp; &nbsp; &nbsp; countQuery = "select count(p) from Person p"&nbsp; &nbsp; )&nbsp; &nbsp; Page<Person> findAllOrderByAddressCountAsc(Pageable pageable);&nbsp; &nbsp; @Query(&nbsp; &nbsp; &nbsp; &nbsp; value = "select p from Person p join p.addresses ad group by p Order By addressCount desc",&nbsp; &nbsp; &nbsp; &nbsp; countQuery = "select count(p) from Person p"&nbsp; &nbsp; )&nbsp; &nbsp; Page<Person> findAllOrderByAddressCountDesc(Pageable pageable);}希望这会有所帮助。