多表分页条件查询
多表分页条件查询便不能像单表那样操作，这时我们可以通过写原生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)；
