JPA三种分页查询

JPA三种分页条件查询

接触Jpa已有一段时间，从陌生到熟悉，接下来讲解三种分页条件查询的方法，主要是对单表、多表、union并集进行讲解，三种方法都是在实际工作中所运用到的，希望对观看这篇博客的你有所帮助，如果有什么问题，也请帮忙指出。

一、单表分页条件查询。

单表查询可使用jpa原生方法，用Specification 对数据进行条件查询，并不需要写sql
例子如下：

public ResponseResult getList(DruggistQualificationQuery query) {
        // Specification 单表可用，定义对应的模糊查询条件。 EqualcOMPARISON.OF为相等，LikeComparison.of为like查询，还有不相等，这些。
        Specification specification = SpecificationFactory.findPageList(
                EqualComparison.of("status", TableStatusEnum.VALID.getCode())
                , EqualComparison.of("loginId", query.getLoginId())
                , LikeComparison.of("name", query.getName())
                , EqualComparison.of("education", query.getEducation())
                , EqualComparison.of("professionClass", query.getProfessionClass())
        );
        // 封装成Pageable 便可分页查询                                                                     //根据modificationDate 倒序排序
        Pageable pageable = PageRequest.of(query.getPage() - 1, query.getSize(), Sort.Direction.DESC, "modificationDate");
        // 调用findAll 自带的方法，便可查出数据
        Page<DruggistQualificationEntity> pageResult = druggistQualificationRepository.findAll(specification, pageable);
        //  对Page数据进行封装，变成list
        List<DruggistQualificationView> resList = new ArrayList<>();
        for (DruggistQualificationEntity resEntity : pageResult.getContent()) {
            DruggistQualificationView resView = new DruggistQualificationView();
            BeanUtils.copyProperties(resEntity, resView);
            resList.add(resView);
        }
        // 返回查询总数和对应的数据
        return ResponseResultUtil.success(pageResult.getTotalElements(), resList);
    }

二、多表分页条件查询。
多表分页条件查询便不能像单表那样操作，这时我们可以通过写原生sql进行多表关联查询，jpa并不像mybatis那样可以直接使用List<泛型T> 去接收返回的数据，而是用了List<Map<String,Object>>去接收返回的参数，因为这里是用了Page分页，于是用Page<Map<String, Object>>，拿到查询的数据，转换成实体类，并进行封装，代码如下：

public ResponseResult getListByPage(xxxQuery req) {
        Pageable pageable = PageRequest.of(req.getPage() - 1, req.getSize());
        Page<Map<String, Object>> pageResult = xxxRepository.getList(req, pageable);
        List<xxxView> resList = new ArrayList<>();
        for (Map<String, Object> resMap : pageResult.getContent()) {
            xxxView resBean = JSON.parseObject(JSON.toJSONString(resMap), xxxView.class);
            resList.add(resBean);
        }
        return ResponseResultUtil.success(pageResult.getTotalElements(), resList);
    }

and if 作为模糊查询进行查询。 判断但数据不为null和‘’和，便触发查询条件。原生sql编写如下；

/**
     *  分页条件查询
     *
     * @param req 请求的参数
     * @param pageable 分页参数
     * @return 结果
     */
    @Query(value = "select t.id" +
            ",t.task_name" +
            ",t.project_id" +
            ",t.task_detail" +
            ",d.plan_end_date" +
            ",d.actual_start_date" +
            ",d.actual_end_date" +
            ",d.workload" +
            ",d.week_time" +
            ",d.finish_remark" +
            ",d.back_remark" +
            ",d.status as detail_status" +
            " from tb_task t,tb_task_detail d where " +
            " t.id=d.task_id " +
            " and IF(:#{#req.projectId} is not null && :#{#req.projectId} != '', t.project_id=:#{#req.projectId}, 1=1)" +
            " and IF(:#{#req.name} is not null && :#{#req.name} != '', t.task_name LIKE CONCAT('%', :#{#req.name}, '%'), 1=1)" +
            " and IF(:#{#req.dutyPeople} is not null && :#{#req.dutyPeople} != '', d.duty_people=:#{#req.dutyPeople}, 1=1)" +
            " and IF(:#{#req.detailWeekTime} is not null && :#{#req.detailWeekTime} != '', d.week_time=:#{#req.detailWeekTime}, 1=1)" +
            " and d.status in(:#{#req.detailStatusList})" +
            " order by d.modification_date desc "
            , countProjection = "t.id" //用于分页计数
            , nativeQuery = true) // 开启原生sql
    Page<Map<String, Object>> getList(@Param("req") xxxQuery req, Pageable pageable)；

三、多表分页条件查询union多表分页条件查询。
1、使用union必须查询的列对应。
2、分页的计数需要自己重写
简单的单表并联单表可以参考此博客：单表union单表

多表条件union多表条件，会比较麻烦：
多表会出现的问题：多表的时候各个表会有各自的别名，jpa会默认去拿到最前的那个表别名，会照成并集后起的别名无效。代码如下：

    @Query(value = "select s.* " +
            " from (select s.id, s.code,s.stage, s.nature, s.status, s.warning_status, s.creation_date,s.weekly_type,s.modification_date, p.product_code, p.product_name,p.status as 'product_status'" +
            " from tb_project s " +
            " left join tb_product p " +
            " on p.status <> 0 and s.product_id = p.`id` " +
            " left join tb_user tu on tu.login_id = s.manager " +
            " where s.status <> 0 " +
            " and if(:#{#queryReq.name} is not null && :#{#queryReq.name} != '', s.name like CONCAT('%',:#{#queryReq.name},'%'),1=1) " +
            " and if(:#{#queryReq.code} is not null && :#{#queryReq.code} != '', s.code like CONCAT('%',:#{#queryReq.code},'%'),1=1) " +
            " union " +
            " select t.id, t.code,t.stage, t.nature, t.status, t.warning_status, t.creation_date,t.weekly_type,t.modification_date, p.product_code, p.product_name,p.status as 'product_status'"+
            " from tb_project t " +
            " left join tb_product p " +
            " on p.status <> 0 and t.product_id = p.`id` " +
            " left join tb_user tu on tu.login_id = t.manager " +
            " left join tb_project_roles r on t.id = r.project_id" +
            " where t.status <> 0 " +
            " and if(:#{#queryReq.name} is not null && :#{#queryReq.name} != '', t.name like CONCAT('%',:#{#queryReq.name},'%'),1=1) " +
            " and if(:#{#queryReq.code} is not null && :#{#queryReq.code} != '', t.code like CONCAT('%',:#{#queryReq.code},'%'),1=1) ) s"
            , nativeQuery = true, countQuery = "select count(s1.id) from (select s.id from tb_project s left join tb_product p on p.status <> 0 and s.product_id = p.id left join tb_user tu on tu.login_id = s.manager where s.status <> 0 " +
            " and if(:#{#queryReq.name} is not null && :#{#queryReq.name} != '', s.name like CONCAT('%',:#{#queryReq.name},'%'),1=1) " +
            " and if(:#{#queryReq.code} is not null && :#{#queryReq.code} != '', s.code like CONCAT('%',:#{#queryReq.code},'%'),1=1) " +
            " union select t.id from tb_project t left join tb_product p on p.status <> 0 and t.product_id = p.id left join tb_user tu on tu.login_id = t.manager left join tb_project_roles r on t.id = r.project_id where t.status <> 0 " +
            " and if(:#{#queryReq.name} is not null && :#{#queryReq.name} != '', t.name like CONCAT('%',:#{#queryReq.name},'%'),1=1) " +
            " and if(:#{#queryReq.code} is not null && :#{#queryReq.code} != '', t.code like CONCAT('%',:#{#queryReq.code},'%'),1=1)" +
            ") as s1")
    Page<Map<String, Object>> getListByPage(@Param("queryReq") ProjectQuery queryReq, @Param("pageable") Pageable pageable);

代码讲解：
1、上面的格式可简单看成：select a.* from ( A as a union B as b) as a ，并集后的别名也要是a 因为jpa会默认去拿到第一个表的别名，在后面排序就会出现order by a.creation_date desc; 如果并集后取名为其他，就会出现报错，查无此a.creation_dete

2、union并集数据后，便不能用countProjection = “s.id” 去计数。需要我们去重写计数的sql,使用countQuery 进行计算总数进行分页。sql和上面写法一样，查询的是数量count(s1.id)，这是你会发现，查找数量时，union并集查询的并不需要去取相同的别名，便可以拿到此次查询的总数。
（注意分页条件union上下要一致，不然模糊查询的时候会出现筛选不一致，业务需求）

三种方法从简单到复杂，但都不是难以理解的，大部分报错的原因无非就是自己不够细心，写错了单词或者用错了关联词。如果这篇文章对你有所帮助，可以点个赞哦！谢谢~