Jpa使用Specification分页查询

背景

虽然我很讨厌Jpa但是架不住别人用，那还是要熟悉下的

开始

版本：

• springboot 2.7.15
• JDK 11
• mysql-connector-j 8.1.0

Pom

<dependency>
    <groupId>org.springframework.bootgroupId>
    <artifactId>spring-boot-starter-data-jpaartifactId>
dependency>

<dependency>
    <groupId>com.mysqlgroupId>
    <artifactId>mysql-connector-jartifactId>
    <version>8.1.0version>
dependency>
1
2
3
4
5
6
7
8
9
10

配置文件

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/test?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=true
    username: test
    password: test
    driver-class-name: com.mysql.cj.jdbc.Driver
  jpa:
    hibernate:
      ddl-auto: update
    #打印sql
    show-sql: true
1
2
3
4
5
6
7
8
9
10
11

实体类

一方

@Entity
@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder(toBuilder = true)
@Table(name = "rule_group", indexes = {@Index(name = "idx_timer",  columnList="create_time")})
public class RuleGroup {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id", columnDefinition = "BIGINT NOT NULL AUTO_INCREMENT COMMENT '自增ID'")
    private Long id;

    @Column(name = "group_name", unique = true, nullable = false)
    private String groupName;

    @Column(name = "group_code", unique = true, nullable = false)
    private String groupCode;

    @Column(name = "group_desc", nullable = false, length = 512)
    private String groupDesc;

    @OneToMany(cascade = CascadeType.ALL, orphanRemoval = true)
    @JoinColumn(name = "rule_group_id")
    private List<Rule> ruleList;

    @Column(name = "create_time", nullable = false)
    @CreationTimestamp
    @Temporal(TemporalType.TIMESTAMP)
    private Date createTime;

    @Column(name = "update_time", nullable = false)
    @UpdateTimestamp
    @Temporal(TemporalType.TIMESTAMP)
    private Date updateTime;

    @Column(name = "create_by", nullable = false, length = 512)
    private String createBy;

    @Column(name = "update_by", nullable = false, length = 512)
    private String updateBy;

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

多方

@Entity
@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder(toBuilder = true)
@Table(name = "rule", indexes = {@Index(name = "idx_timer",  columnList="create_time")})
public class Rule {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id", columnDefinition = "BIGINT NOT NULL AUTO_INCREMENT COMMENT '自增ID'")
    private Long id;

    @Column(name = "rule_group_id", columnDefinition = "bigint(20) NOT NULL DEFAULT '0' COMMENT '规则组ID'")
    private Long ruleGroupId;

    @Column(name = "rule_name", unique = true, nullable = false)
    private String ruleName;

    @Column(name = "rule_code", unique = true, nullable = false)
    private String ruleCode;

    @Column(name = "rule_desc", unique = true, nullable = false)
    private String ruleDesc;

    @Column(name = "create_time", nullable = false)
    @CreationTimestamp
    @Temporal(TemporalType.TIMESTAMP)
    private Date createTime;

    @Column(name = "update_time", nullable = false)
    @UpdateTimestamp
    @Temporal(TemporalType.TIMESTAMP)
    private Date updateTime;

    @Column(name = "create_by", nullable = false, length = 512)
    private String createBy;

    @Column(name = "update_by", nullable = false, length = 512)
    private String updateBy;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

RuleDao

@Repository
public interface RuleDao extends JpaRepository<Rule, Long>, JpaSpecificationExecutor<Rule> {
}
1
2
3

RuleGroupDao

@Repository
public interface RuleGroupDao extends JpaRepository<RuleGroup, Long>, JpaSpecificationExecutor<RuleGroup> {
}
1
2
3

服务层使用

@Resource
private RuleDao ruleDao;

@Resource
private RuleGroupDao ruleGroupDao;


@Override
public Page<Rule> searchRule(SearchRulesVo vo) {
    return ruleDao.findAll((Specification<Rule>) (root, query, cb) -> {
        List<Predicate> predicates = predicatesRule(root, cb, vo);
        Predicate[] pre = new Predicate[predicates.size()];
        return cb.and(predicates.toArray(pre));
    }, PageRequest.of(vo.getPage() - 1, vo.getSize(), Sort.by(Sort.Direction.DESC, "createTime")));
}

private List<Predicate> predicatesRule(Root<Rule> root, CriteriaBuilder cb, SearchRulesVo vo) {
    List<Predicate> pres = new ArrayList<>();

    if (Objects.nonNull(vo.getRuleId()) && vo.getRuleId() > 0) {
        pres.add(cb.equal(root.get("id"), vo.getRuleId()));
    } else if (Objects.nonNull(vo.getRuleIdList()) && vo.getRuleIdList().size() > 0) {
        pres.add(root.get("id").in(vo.getRuleIdList()));
    }

    if (Objects.nonNull(vo.getRuleGroupId()) && vo.getRuleGroupId() > 0) {
        pres.add(cb.equal(root.get("ruleGroupId"), vo.getRuleGroupId()));
    } else if (Objects.nonNull(vo.getRuleGroupIdList()) && vo.getRuleGroupIdList().size() > 0) {
        pres.add(root.get("ruleGroupId").in(vo.getRuleGroupIdList()));
    }

    if (StringUtils.hasText(vo.getRuleName())) {
        pres.add(cb.like(root.get("ruleName"), "%" + vo.getRuleName() + "%"));
    }

    if (StringUtils.hasText(vo.getRuleCode())) {
        pres.add(cb.equal(root.get("ruleCode"), vo.getRuleCode()));
    }

    return pres;
}

@Override
public Page<RuleGroup> searchRuleGroup(SearchRulesVo vo) {
    return ruleGroupDao.findAll((Specification<RuleGroup>) (root, query, cb) -> {
        List<Predicate> predicates = predicatesRuleGroup(root, cb, vo);
        Predicate[] pre = new Predicate[predicates.size()];
        return cb.and(predicates.toArray(pre));
    }, PageRequest.of(vo.getPage() - 1, vo.getSize(), Sort.by(Sort.Direction.DESC, "createTime")));
}

private List<Predicate> predicatesRuleGroup(Root<RuleGroup> root, CriteriaBuilder cb, SearchRulesVo vo) {
    List<Predicate> pres = new ArrayList<>();
    if (Objects.nonNull(vo.getRuleGroupId()) && vo.getRuleGroupId() > 0) {
        pres.add(cb.equal(root.get("id"), vo.getRuleGroupId()));
    } else if (Objects.nonNull(vo.getRuleGroupIdList()) && vo.getRuleGroupIdList().size() > 0) {
        pres.add(root.get("id").in(vo.getRuleGroupIdList()));
    }
    if (StringUtils.hasText(vo.getRuleGroupName())) {
        pres.add(cb.like(root.get("groupName"), "%" + vo.getRuleGroupName() + "%"));
    }
    if (StringUtils.hasText(vo.getRuleGroupCode())) {
        pres.add(cb.equal(root.get("groupCode"), vo.getRuleGroupCode()));
    }
    return pres;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66

cb提供了常用的where拟合函数，蛮好用的，整体也不复杂
就这样