大家好,又见面了,我是你们的朋友全栈君。
自定义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 "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.tongtech.biz.purchase.frame.dao.WfPurchaseFrameDao">
<select id="pageList" parameterType="com.tongtech.biz.purchase.frame.model.dto.WfPurchaseFrameParam"
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'
<if test="param.applyPerson != null and param.applyPerson != ''">
and t.apply_person like concat('%',#{param.applyPerson},'%')
</if>
<if test="param.projectCode != null and param.projectCode != ''">
and t.project_code like concat('%',#{param.projectCode},'%')
</if>
<if test="param.projectName != null and param.projectName != ''">
and t.project_name like concat('%',#{param.projectName},'%')
</if>
limit #{param.page},#{param.limit}
</select>
<select id="myPageList" parameterType="com.tongtech.biz.purchase.frame.model.dto.WfPurchaseFrameParam" 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'
<if test="param.applyPerson != null and param.applyPerson != ''">
and t.apply_person like concat('%',#{param.applyPerson},'%')
</if>
<if test="param.projectCode != null and param.projectCode != ''">
and t.project_code like concat('%',#{param.projectCode},'%')
</if>
<if test="param.projectName != null and param.projectName != ''">
and t.project_name like concat('%',#{param.projectName},'%')
</if>
</select>
<select id="pageListCount" parameterType="com.tongtech.biz.purchase.frame.model.dto.WfPurchaseFrameParam"
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="param.applyPerson != null and param.applyPerson != ''">
and t.apply_person like concat('%',#{param.applyPerson},'%')
</if>
<if test="param.projectCode != null and param.projectCode != ''">
and t.project_code like concat('%',#{param.projectCode},'%')
</if>
<if test="param.projectName != null and param.projectName != ''">
and t.project_name like concat('%',#{param.projectName},'%')
</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=
StringHelper.isEmpty(param.getPage())?BizConstants.PAGE:Long.valueOf(param.getPage());
Long limit=
StringHelper.isEmpty(param.getLimit())?BizConstants.LIMIT:Long.valueOf(param.getLimit());
Page<WfPurchaseFrame> resultPage=new Page<>(page,limit);
// 这里不能使用QueryWrapper 来传递自定义参数
QueryWrapper<WfPurchaseFrame> queryWrapper=this.createQuery(param);
IPage<WfPurchaseFrame> resultList= wfPurchaseFrameService.myPageList(resultPage,param);
BaseResponseList<WfPurchaseFrame> baseResponseList=new BaseResponseList<>();
baseResponseList.setData(resultList.getRecords());
baseResponseList.setTotal(resultList.getTotal());
baseResponse.setData(baseResponseList);
return baseResponse;
}
上面代码中说了不能使用wrapper查询条件构造器,原因为什么呢
${ew.customSqlSegment} 解析时会自动在前面带上where关键字,并且条件构建时不会带上表别名
发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/153259.html原文链接:https://javaforall.cn
【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛
【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...