自定义sql分页的步骤

Dao层定义查询接口，第一个参数必须为分页的参数Ipage，后面可带其他参数作为传入参数

定义自定义查询sql

网上很多博客里面写的多表sql分页查询没带参数，这里给一个带参数的列子

JAVA和xml文件如下：

myPageList为使用mybatisPlus写的，pageList和pageListCount为原始写法

可以看出myPageList跟pageListsql语句一模一样，只是第一个参数必须为Ipage

public interface WfPurchaseFrameDao extends SuperDao { List pageList(@Param("param") WfPurchaseFrameParam param); IPage myPageList(IPage page ,@Param("param") WfPurchaseFrameParam param); long pageListCount(@Param("param") WfPurchaseFrameParam param); }

    
            resultType="com.tongtech.biz.purchase.frame.model.domain.WfPurchaseFrame">

        select t.*,inst.proc_inst_id processInstanceId
        from t_wf_purchase_frame t
        left join t_flow_inst inst on inst.business_id = t.apply_id
        where t.`status` = '0'
        and inst.proc_inst_status ='1'
        
        and t.apply_person like concat('%',#{param.applyPerson},'%')
        
        
        and t.project_code like concat('%',#{param.projectCode},'%')
        
        
        and t.project_name like concat('%',#{param.projectName},'%')
        
        limit #{param.page},#{param.limit}
    





    

        select t.*,inst.proc_inst_id processInstanceId
        from t_wf_purchase_frame t
        left join t_flow_inst inst on inst.business_id = t.apply_id
        where t.`status` = '0'
        and inst.proc_inst_status ='1'
        
            and t.apply_person like concat('%',#{param.applyPerson},'%')
        
        
            and t.project_code like concat('%',#{param.projectCode},'%')
        
        
            and t.project_name like concat('%',#{param.projectName},'%')
        

    



    
            resultType="long">

        select count(1)
        from t_wf_purchase_frame t
        left join t_flow_inst inst on inst.business_id = t.apply_id
        where t.`status` = '0'
        and inst.proc_inst_status ='1'
        
            and t.apply_person like concat('%',#{param.applyPerson},'%')
        
        
            and t.project_code like concat('%',#{param.projectCode},'%')
        
        
            and t.project_name like concat('%',#{param.projectName},'%')
        
    

调用 controller层 【省略Service部分】：这里只需要在调用时传递一个Page(long current, long size)即可

@PostMapping(value = "/pageList")

public  BaseResponse> pageList(@RequestBody WfPurchaseFrameParam param){

   BaseResponse>baseResponse=new BaseResponse<>();

   Long page=

         StringHelper.isEmpty(param.getPage())?BizConstants.PAGE:Long.valueOf(param.getPage());

   Long limit=

         StringHelper.isEmpty(param.getLimit())?BizConstants.LIMIT:Long.valueOf(param.getLimit());

   Page resultPage=new Page<>(page,limit);

   // 这里不能使用QueryWrapper 来传递自定义参数

   QueryWrapper queryWrapper=this.createQuery(param);

   IPage resultList= wfPurchaseFrameService.myPageList(resultPage,param);

   BaseResponseList baseResponseList=new BaseResponseList<>();

   baseResponseList.setData(resultList.getRecords());

   baseResponseList.setTotal(resultList.getTotal());

   baseResponse.setData(baseResponseList);

   return baseResponse;

}

上面代码中说了不能使用wrapper查询条件构造器，原因为什么呢

${ew.customSqlSegment} 解析时会自动在前面带上where关键字，并且条件构建时不会带上表别名