SpringBoot 中 Jpa PageRequest 分页 + Example 多参数 单表查询

SpringBoot 中 Jpa PageRequest 分页 + Example 多参数 单表查询

依赖

   <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>6.0.6</version>
        </dependency>

配置

spring:
  #通用的数据源配置
  datasource:
    driverClassName: com.mysql.jdbc.Driver
    url: jdbc:mysql://localhost:3306/main?useUnicode=true&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC
    username: root
    password: root
  jpa:
    #这个参数是在建表的时候，将默认的存储引擎切换为 InnoDB 用的
    database-platform: org.hibernate.dialect.MySQL5InnoDBDialect
    #配置在日志中打印出执行的 SQL 语句信息。
    show-sql: true
    hibernate:
      #配置指明在程序启动的时候要删除并且创建实体类对应的表
      # 第一次简表  create  后面用  update 不然每次重启工程会删除表并新建。
#      ddl-auto: create
      ddl-auto: update
      naming:
       implicit-strategy: org.hibernate.boot.model.naming.ImplicitNamingStrategyLegacyJpaImpl
       physical-strategy: org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl

pojo

@Data
@Entity(name = "circuitConfig")
public class CircuitConfig {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO) //主键自动增长
    public int id;
    @NotEmpty(message = "cjrId 不能为空")
    @Column(length = 200)
    public String cjrId;
    @NotEmpty(message = "syrId 不能为空")
    @Column(length = 200)
    public String syrId;
    @NotEmpty(message = "url 不能为空")
    public String url;
    /**
     * 截取后的url
     */
    @JsonIgnore
    @Column(length = 200)
    public String suBurl;


    @NotEmpty(message = "method 不能为空")
    public String method;
    /**
     * 请求方式 字符串还是 json
     *  1 表示 请求方式是 json
     *  0 表示 请求方式是 text
     */
    @NotNull(message = "contype 不能为空")
    @Column(length = 1)
    public Integer contype;
    /**
     * 1 表示 开票流程配置
     * 0 表示 其他配置
     */
    @NotNull(message = "type 不能为空")
    @Column(length = 1)
    public Integer type;

    public Date cjsj;


}

dao

@Repository
public interface CircuitConfigDao  extends JpaRepository<CircuitConfig,Integer> {
}

使用 PageRequest 进行分页查询
JPA中的接口

根据参数查询信息 Example 参数查询匹配器

可以使用 ExampleMatcher 匹配器 进行参数匹配

ExampleMatcher：ExampleMatcher携带有关如何匹配特定字段的详细信息，相当于匹配条件。
Example：由Probe和ExampleMatcher组成，用于查询。

缺点

属性不支持嵌套或者分组约束，比如这样的查询 firstname = ?0 or (firstname = ?1 and lastname = ?2)
灵活匹配只支持字符串类型，其他类型只支持精确匹配

创建匹配器

   <S extends T> List<S> findAll(Example<S> var1);

    <S extends T> List<S> findAll(Example<S> var1, Sort var2);

分页查询接口

封装返回结果类

分页查询 + 字符串匹配查询

  
    /**
     * 分页查询
     *
     * @param circuitConfig 查询条件
     * @return 流程配置数据
     */
    public ReturnMessage getListWithCriteria(PageRequestForCirVo<CircuitConfig> circuitConfig) {
        CircuitConfig object = circuitConfig.getObject();

        log.info("查询条件为 {}  页码 {} ， 每页数量  {} ", object, circuitConfig.getPage(), circuitConfig.getPageSize());
        if (circuitConfig.getPage() < 1) {
            circuitConfig.setPage(1);
        }
        if (circuitConfig.getPageSize() < 1) {
            circuitConfig.setPageSize(10);
        }

        //ExampleMatcher.GenericPropertyMatchers.startsWith()  模糊查询匹配开头，即{username}%
        //ExampleMatcher.GenericPropertyMatchers.contains())  全部模糊查询，即%{address}%
        //ExampleMatcher.GenericPropertyMatchers.contains()  全部模糊查询，即%{address}%

        Example<CircuitConfig> example = null;
        Pageable pageable = null;
        Page<CircuitConfig> listWithCriteria = null;
        if (object != null) {

            //查询条件
            ExampleMatcher matcher = ExampleMatcher.matching()
                    .withMatcher("contype", ExampleMatcher.GenericPropertyMatchers.contains())
                    .withMatcher("type", ExampleMatcher.GenericPropertyMatchers.contains())
                    .withMatcher("cjrId", ExampleMatcher.GenericPropertyMatchers.contains())
                    .withMatcher("syrId", ExampleMatcher.GenericPropertyMatchers.contains())
                    .withMatcher("url", ExampleMatcher.GenericPropertyMatchers.contains())
                    .withMatcher("suBurl", ExampleMatcher.GenericPropertyMatchers.contains())
                    .withMatcher("method", ExampleMatcher.GenericPropertyMatchers.contains())
                    //忽略字段，即不管password是什么值都不加入查询条件
                    .withIgnorePaths("cjsj")
                    /*.withIgnorePaths("contype")
                    .withIgnorePaths("type")*/
                    //，需要忽略掉
                    .withIgnorePaths("id");
            example = Example.of(object, matcher);

            //分页数据
            pageable = new PageRequest(circuitConfig.getPage() - 1, circuitConfig.getPageSize(), Sort.DEFAULT_DIRECTION, "id");
            //查询
            listWithCriteria = circuitConfigDao.findAll(example, pageable);

        } else {
            pageable = new PageRequest(circuitConfig.getPage() - 1, 10, Sort.Direction.ASC, "id");
            listWithCriteria = circuitConfigDao.findAll(pageable);

        }

        List<CircuitConfig> content = listWithCriteria.getContent();
        //返回空
        if (content == null || content.size() == 0) {
            log.info(" 暂无数据 ");
            return new ReturnMessage(CodeMsgEnum.NOT_DATA.getCode(), CodeMsgEnum.NOT_DATA.getMsg());
        }

        long total = content.size();

        //包装返回结果
        PageImpl<CircuitConfig> emptyPage = new PageImpl<>(content, pageable, total);
        log.info(" 返回结果为 ： {} ",JSON.toJSONString(emptyPage));
        return new ReturnMessage(CodeMsgEnum.OK.getCode(), CodeMsgEnum.OK.getMsg(), emptyPage);
    }

返回结果

{
    "code": 0,
    "msg": "成功",
    "data": {
        "content": [
            {
                "id": 1,
                "cjrId": "1",
                "syrId": "12",
                "url": "http://127.0.0.1:8080/api/template/xx",
                "method": "12",
                "contype": 1,
                "type": 1,
                "cjsj": "2020-03-10 08:00:00"
            },
            {
                "id": 2,
                "cjrId": "1",
                "syrId": "12",
                "url": "http://127.0.0.1:8080/api/template/xxxx",
                "method": "12",
                "contype": 1,
                "type": 1,
                "cjsj": "2020-03-10 08:00:00"
            }
        ],
        "last": true, 
        "totalPages": 1,
        "totalElements": 2,
        "number": 0,
        "size": 6,  // pageSize
        "sort": [
            {
                "direction": "ASC",
                "property": "id",
                "ignoreCase": false,
                "nullHandling": "NATIVE",
                "ascending": true,
                "descending": false
            }
        ],
        "first": true,
        "numberOfElements": 2
    }
}