社区首页 >问答首页 >ManyToMany表上的SpringBoot2-JPA+可分页问题

问ManyToMany表上的SpringBoot2-JPA+可分页问题
EN

Stack Overflow用户

提问于 2020-08-09 19:34:18

回答 1查看 42关注 0票数 0

在Spring 2 JPA中，我有以下两个多到多个实体。

1-劳动：

@Entity
public class Labor {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;
    @Column(length = 100, nullable = false)
    private String name;
    @Column(length = 50)
    private String mobile;
    private Date dateOfBirth;
    private boolean male;
    private boolean active;
    private String brife;
    @Column(length = 500)
    private String specifcation;
    @ManyToMany(cascade = CascadeType.ALL)
    @JoinTable(name = "labor_tag",
            joinColumns = @JoinColumn(name = "labor_id"),
            inverseJoinColumns = @JoinColumn(name = "tag_id"))
    private Set<Tag> tags = new HashSet<>();
}

和标签表：

@Entity
public class Tag {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;
    @Column(length = 100, unique = true)
    private String name;
    private boolean active = true;
    @ManyToMany(cascade = CascadeType.ALL, mappedBy = "tags")
    @JsonIgnore
    private Set<Labor> labors = new HashSet<>();
}

然后，我定义了劳动存储库，以查询带有特定标签ID、性别或年龄的劳动者

@Repository
public interface LaborReoistory extends JpaRepository<Labor, Long> {

    @Query("select l from Labor l join l.tags t where (:tid is null or t.id in :tid) and " +
            "(:isMale is null or :isMale = TRUE) and " +
            "((:startYear is null or :endYear is null or :startYear >  :endYear) or year(l.dateOfBirth) >= :startYear and year(l.dateOfBirth) <= :endYear)")
    Page<Labor> findLaborsByCondition(@Param("tid") final long[] tid,
                                      @Param("isMale") final Boolean isMale,
                                      @Param("startYear") final Integer startYear,
                                      @Param("endYear") final Integer endYear,
                                      final Pageable pageable);

}

当我在控制器中使用这个存储库时，我会在totalElements属性中找到Pagable返回到labor_tag中的记录的计数(在本例中是16条记录)，但是我真正想要的是在给定条件下让totalElements依赖于Labors。JPA Pagable是否支持这样的查询，或者如何找到解决办法？

谢谢

spring-boot

jpa

spring-boot-jpa

回答 1

Stack Overflow用户

回答已采纳

发布于 2020-08-09 22:46:28

加入后，将出现重复的Labor，但totalElements是使用查询的行总数的计数。因此，您应该使用Distinct on Labour来获得不同劳动的计数。

@Query("select distinct l from Labor l join l.tags t where (:tid is null or t.id in :tid) and " +
            "(:isMale is null or :isMale = TRUE) and " +
            "((:startYear is null or :endYear is null or :startYear >  :endYear) or year(l.dateOfBirth) >= :startYear and year(l.dateOfBirth) <= :endYear)")