MybatisPlus自定义sql分页查询

自定义sql分页的步骤

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

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

JAVA和xml文件如下：

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

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

public interface WfPurchaseFrameDao extends SuperDao<WfPurchaseFrame>
{
    List<WfPurchaseFrame> pageList(@Param("param") WfPurchaseFrameParam param);

    IPage<WfPurchaseFrame> myPageList(IPage<WfPurchaseFrame> page ,@Param("param") WfPurchaseFrameParam param);

    long pageListCount(@Param("param") WfPurchaseFrameParam param);
}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-////DTD Mapper 3.0//EN" "/dtd/">
<mapper namespace="">

    <select  parameterType=""
            resultType="">

        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'
        <if test=" != null and  != ''">
        and t.apply_person like concat('%',#{},'%')
        </if>
        <if test=" != null and  != ''">
        and t.project_code like concat('%',#{},'%')
        </if>
        <if test=" != null and  != ''">
        and t.project_name like concat('%',#{},'%')
        </if>
        limit #{},#{}
    </select>


    <select  parameterType="" resultType="">

        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'
        <if test=" != null and  != ''">
            and t.apply_person like concat('%',#{},'%')
        </if>
        <if test=" != null and  != ''">
            and t.project_code like concat('%',#{},'%')
        </if>
        <if test=" != null and  != ''">
            and t.project_name like concat('%',#{},'%')
        </if>

    </select>

    <select  parameterType=""
            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'
        <if test=" != null and  != ''">
            and t.apply_person like concat('%',#{},'%')
        </if>
        <if test=" != null and  != ''">
            and t.project_code like concat('%',#{},'%')
        </if>
        <if test=" != null and  != ''">
            and t.project_name like concat('%',#{},'%')
        </if>
    </select>

</mapper>

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

@PostMapping(value = "/pageList")
public  BaseResponse<BaseResponseList<WfPurchaseFrame>> pageList(@RequestBody WfPurchaseFrameParam param){
   BaseResponse<BaseResponseList<WfPurchaseFrame>>baseResponse=new BaseResponse<>();
   Long page=
         (())?:(());
   Long limit=
         (())?:(());
   Page<WfPurchaseFrame> resultPage=new Page<>(page,limit);
   // 这里不能使用QueryWrapper 来传递自定义参数
   QueryWrapper<WfPurchaseFrame> queryWrapper=(param);
   IPage<WfPurchaseFrame> resultList= (resultPage,param);
   BaseResponseList<WfPurchaseFrame> baseResponseList=new BaseResponseList<>();
   (());
   (());
   (baseResponseList);
   return baseResponse;
}

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

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

秒客网

MybatisPlus自定义sql分页查询

相关文章