Spring Data Jpa使用——自定义条件查询和分页查询接口

背景

• 开发中，需要使用Spring JPA作为业务持久层操作，大家都知道，只要使用一个接口继承Jpa的相关接口就可以实现数据持久化操作，现在需要继承一个接口，具有多条件及分页查询的方法。

软件环境

• Java 1.8
• Spring Boot 2.2.1.RELEASE
• Spring Data Jpa 2.2.1.RELEASE

思路

• 写一个公共的基础接口，继承JPA的相关接口
• 这个接口继承接口JpaRepository<T, ID>和JpaSpecificationExecutor<T>
  • JpaRepository<T, ID>接口具有基本的数据CURD操作、分页查询以及条件查询方法，缺少具有分页和条件同时查询的方法
  • JpaSpecificationExecutor<T>具有分页和条件同时查询的方法：Page<T> findAll(@Nullable Specification<T> var1, Pageable var2);

实例

• 实体类

package com.canaan.minemanage.entities.mysql.statistic;

import javax.persistence.*;
import java.io.Serializable;
import java.util.Date;

@Entity
@Table(name = "receiver_miner_record")
public class ReceiverMinerRecord implements Serializable {
    private static final long serialVersionUID = 1L;

    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "serialno", columnDefinition = "BIGINT COMMENT '流水号'")
    @Id
    private Integer serialno;
    @Column(name = "global_code", columnDefinition = "varchar(36) NOT NULL COMMENT '全局流水号'")
    private String globalCode;
    @Column(name = "farm_code", columnDefinition = "varchar(36) NOT NULL COMMENT '场地编号'")
    private String farmCode;
    @Column(name = "dna", columnDefinition = "varchar(36) COMMENT 'dna编号'")
    private String dna;
    @Column(name = "sn", columnDefinition = "varchar(36)  COMMENT 'sn编号'")
    private String sn;

    public String getGlobalCode() {
        return globalCode;
    }

    public void setGlobalCode(String globalCode) {
        this.globalCode = globalCode;
    }

    public Integer getOffMark() {
        return offMark;
    }

    public void setOffMark(Integer offMark) {
        this.offMark = offMark;
    }

    public static long getSerialVersionUID() {
        return serialVersionUID;
    }

    public Integer getSerialno() {
        return serialno;
    }

    public void setSerialno(Integer serialno) {
        this.serialno = serialno;
    }

    public String getFarmCode() {
        return farmCode;
    }

    public void setFarmCode(String farmCode) {
        this.farmCode = farmCode;
    }

    public String getDna() {
        return dna;
    }

    public void setDna(String dna) {
        this.dna = dna;
    }

    public String getSn() {
        return sn;
    }

    public void setSn(String sn) {
        this.sn = sn;
    }

    @Override
    public String toString() {
        return "ReceiverMiner{" +
                "serialno='" + serialno + '\'' +
                ", farmCode='" + farmCode + '\'' +
                ", dna='" + dna + '\'' +
                ", sn='" + sn + '\''
                '}';
    }
}

• JPA
  • 基础接口

@NoRepositoryBean
public interface BaseRepository<T> extends JpaRepository<T, Long>, JpaSpecificationExecutor<T> {
}

• • 接口实现

@Repository
public interface ReceiverMinerRecordResp extends BaseRepository<ReceiverMinerRecord> {

}

• 业务实现

public RetResult findListExtend(MillListCondition millListCondition) {
    String farmCode = millListCondition.getFarmCode();
    List<String> dnaNames = millListCondition.getDnaNames();
    Integer pageIndex = millListCondition.getPageIndex();
    Integer pageSize = millListCondition.getPageSize();
    
    //条件查询
    Specification<ReceiverMinerRecord> specification = new Specification<ReceiverMinerRecord>() {
        @SneakyThrows
        @Override
        public Predicate toPredicate(Root<ReceiverMinerRecord> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
            List<Predicate> predicateList = new ArrayList<>();

            //farmCode
            if (StrUtil.isNotEmpty(farmCode)) {
                predicateList.add(criteriaBuilder.equal(root.get("farmCode"), farmCode));
            }
            
            //dna
            if (CollectionUtils.isNotEmpty(dnaNames)) {
                Predicate dnaPredicate = root.get("dna").in(dnaNames);
                predicateList.add(dnaPredicate);
            }

            Predicate[] pre = new Predicate[predicateList.size()];
            pre = predicateList.toArray(pre);
            return criteriaQuery.where(pre).getRestriction();
        }
    };

    //分页
    Pageable pageable = null;
    if (pageIndex != 0) {
        pageable = PageRequest.of(pageIndex - 1, pageSize, Sort.Direction.DESC, "minerIp");
    }

    //查询
    Page<ReceiverMinerRecord> receiverMinerRecordPage = receiverMinerRecordResp.findAll(specification, pageable);
    pageTotal = receiverMinerRecordPage.getTotalElements();
    List<ReceiverMinerRecord> receiverMinerRecords = receiverMinerRecordPage.getContent();

    return ResultUtil.success(200, receiverMinerRecords, "查询列表成功", pageTotal);

}