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

约定不等于承诺〃 2023-07-13 04:20 26阅读 0赞

## 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)  
灵活匹配只支持字符串类型，其他类型只支持精确匹配

创建匹配器  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODM2MTM0Nw_size_16_color_FFFFFF_t_70]

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

分页查询接口

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODM2MTM0Nw_size_16_color_FFFFFF_t_70 1]

封装返回结果类

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODM2MTM0Nw_size_16_color_FFFFFF_t_70 2]

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

/** * 分页查询 * * @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 = mongoTemplate.count(query,CircuitConfig.class);
      
    
            //包装返回结果
            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
        }
    }

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODM2MTM0Nw_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200310180342788.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODM2MTM0Nw==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODM2MTM0Nw_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200310180208793.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODM2MTM0Nw==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODM2MTM0Nw_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200310180837384.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODM2MTM0Nw==,size_16,color_FFFFFF,t_70