MybatisPlus自定义SQL如何支持多表分页查询
MybatisPlus自定义SQL如何支持多表分页查询
MybatisPlus自定义多条件分页查询
MybatisPlus自定义SQL如何支持多表分页查询
前言
一、介绍
二、QueryVo
二、Controller层
三、Service层
四、Mapper层
4.1 Mapper类
4.2 多表查询的sql
4.3 Mapper.xml
MybatisPlus自定义SQL如何支持多表分页查询
前言
在本文，详细写一下使用mapper.xml实现mybatisplus多条件分页查询的写法。
一、介绍
其实还是比较简单的，直接看下面的需求：
数据库图和多条件查询页面的图如上所示。
二、QueryVo
首先根据要查询的条件，定义相应的QueryVo类。
package com.jztai.cellpathology.pojo;
import lombok.Data;
import java.util.Date;
@Data
public class SampleQueryVo {
private String sampleNum;
private String patientName;
private Integer patientAge;
private Integer sampleType;
private Integer reportType;
private Date sampleStartSendDate;
private Date sampleEndSendDate;
private Date sampleStartDate;
private Date sampleEndDate;
private Integer sampleStatus;
private Integer instiutionId;
private Integer doctorId; // 送检医生编号
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
二、Controller层
@GetMapping("samples/page/{page}/size/{size}")
@ApiOperation("分页查询所有的接口")
public Page<SamplePageVo> list(SampleQueryVo sampleQueryVo, String name, @PathVariable("page") Integer pagenum, @PathVariable("size") Integer size) {
Page<SamplePageVo> userPage = sampleService.listSamplePage(sampleQueryVo, pagenum, size);
return userPage;
}
1
2
3
4
5
6
三、Service层
public interface SampleService extends IService<TbSample> {
Page<SamplePageVo> listSamplePage(SampleQueryVo queryVo,Integer page,Integer size);
}
1
2
3
4
下面是实现
@Service
public class SampleServiceImpl extends ServiceImpl<TbSampleMapper, TbSample> implements SampleService {
@Override
public Page<SamplePageVo> listSamplePage(SampleQueryVo queryVo, Integer page, Integer size) {
// 构造分页参数
Page<SamplePageVo> pages = new Page<SamplePageVo>(page, size);
// 在这里封装where条件
QueryWrapper<SamplePageVo> queryWrapper = new QueryWrapper<SamplePageVo>();
// baseMapper就是指代的TbSampleMapper类。
return this.baseMapper.selectSamplePageVoPage(pages, queryWrapper);
}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
四、Mapper层
4.1 Mapper类
public interface TbSampleMapper extends BaseMapper<TbSample> {
// 直接按照这个格式写
// 加上Page<SamplePageVo> page即可实现分页， @Param("ew")指定在封装Sql的时候的参数名
Page<SamplePageVo> selectSamplePageVoPage(Page<SamplePageVo> page, @Param("ew") Wrapper<SamplePageVo> queryWrapper);
}
1
2
3
4
5
6
7
4.2 多表查询的sql
这里特别注意的是，我在查主表的时候，字段不改名，在查询子表的时候字段名改成 Javabean的属性名.字段名 ，mybatisplus会自动将字段封装到指定的bean里面，也可能是mybatis都这样处理的，我记得之前在新冠报告项目里面mybatis好像没有给封装，需要自己写ResultMap建立映射的。
SELECT sample_id,creater.user_id as 'creater.user_id',creater.user_name AS 'creater.user_name',updater.user_id AS 'updater.user_id',updater.user_name AS 'updater.user_name',patient.patient_id AS 'patient.patient_id',patient.patient_name AS 'patient.patient_name',institution.instiution_id AS 'institution.instiution_id',institution.institution_name AS 'institution.institution_name',department.instiution_id AS 'department.instiution_id',department.institution_name AS 'department.institution_name',doctor.user_id AS 'doctor.user_id',doctor.user_name AS 'doctor.user_name',sample.update_time AS 'sample.update_time',telephone,samplestatus.dict_id AS 'samplestatus.dict_id',samplestatus.dict_value AS 'samplestatus.dict_value',sampletype.dict_id AS 'sampletype.dict_id',sampletype.dict_value AS 'sampletype.dict_value',reporttype.dict_id AS 'reporttype.dict_id',reporttype.dict_value AS 'reporttype.dict_value',sample_date,sample_send_date,sample.remark,diagnosticiandoc.user_id,diagnosticiandoc.user_name
FROM tb_sample AS sample
LEFT JOIN tb_user AS creater ON sample.creater_id = creater.user_id
LEFT JOIN tb_user AS updater ON sample.updater_id = updater.user_id
LEFT JOIN tb_user AS doctor ON sample.updater_id = doctor.user_id
LEFT JOIN tb_patient AS patient ON patient.patient_uuid = sample.patient_uuid
LEFT JOIN tb_institution AS institution ON institution.instiution_id = sample.instiution_id
LEFT JOIN tb_institution AS department ON department.instiution_id = sample.department_id
LEFT JOIN tb_dictionary AS sampletype ON sample.sample_type = sampletype.dict_id
LEFT JOIN tb_dictionary AS reporttype ON sample.report_type = reporttype.dict_id
LEFT JOIN tb_dictionary AS samplestatus ON sample.sample_status = samplestatus.dict_id
LEFT JOIN tb_user AS diagnosticiandoc ON sample.diagnostician = diagnosticiandoc.user_id
LEFT JOIN tb_user AS reviewer ON sample.review_doctor = reviewer.user_id
where
// 后面跟查询条件，此处省略。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
4.3 Mapper.xml
<sql id="Base_Column_List_With_ALL_MESSAGE">
sample_id
,creater.user_id as 'creater.user_id',
creater.user_name AS 'creater.user_name',
updater.user_id AS 'updater.user_id',
updater.user_name AS 'updater.user_name',
patient.patient_id AS 'patient.patient_id',
patient.patient_name AS 'patient.patient_name',
institution.instiution_id AS 'institution.instiution_id',
institution.institution_name AS 'institution.institution_name',
department.instiution_id AS 'department.instiution_id',
department.institution_name AS 'department.institution_name',
doctor.user_id AS 'doctor.user_id',
doctor.user_name AS 'doctor.user_name',
sample.update_time AS 'sample.update_time',
telephone,samplestatus.dict_id AS 'samplestatus.dict_id',
samplestatus.dict_value AS 'samplestatus.dict_value',
sampletype.dict_id AS 'sampletype.dict_id',
sampletype.dict_value AS 'sampletype.dict_value',
reporttype.dict_id AS 'reporttype.dict_id',
reporttype.dict_value AS 'reporttype.dict_value',
sample_date,sample_send_date,sample.remark,
diagnosticiandoc.user_id,diagnosticiandoc.user_name
</sql>
<resultMap id="BaseResultMap"
type="com.jztai.cellpathology.pojo.SamplePageVo">
<id column="sample_id" property="sampleId" jdbcType="INTEGER"/>
<result column="sample_date" property="sampleDate"
jdbcType="DATETIMEOFFSET"/>
<result column="sample_send_date" property="sampleSendDate"
jdbcType="DATETIMEOFFSET"/>
<result column="sample.remark" property="remark"
jdbcType="VARCHAR"/>
</resultMap>
<select id="selectSamplePageVoPage"
parameterType="com.jztai.cellpathology.pojo.SampleQueryVo"
resultMap="BaseResultMap">
select
<include refid="Base_Column_List_With_ALL_MESSAGE"/>
FROM tb_sample AS sample
LEFT JOIN tb_user AS creater ON sample.creater_id = creater.user_id
LEFT JOIN tb_user AS updater ON sample.updater_id = updater.user_id
LEFT JOIN tb_user AS doctor ON sample.updater_id = doctor.user_id
LEFT JOIN tb_patient AS patient ON patient.patient_uuid = sample.patient_uuid
LEFT JOIN tb_institution AS institution ON institution.instiution_id = sample.instiution_id
LEFT JOIN tb_institution AS department ON department.instiution_id = sample.department_id
LEFT JOIN tb_dictionary AS sampletype ON sample.sample_type = sampletype.dict_id
LEFT JOIN tb_dictionary AS reporttype ON sample.report_type = reporttype.dict_id
LEFT JOIN tb_dictionary AS samplestatus ON sample.sample_status = samplestatus.dict_id
LEFT JOIN tb_user AS diagnosticiandoc ON sample.diagnostician = diagnosticiandoc.user_id
LEFT JOIN tb_user AS reviewer ON sample.review_doctor = reviewer.user_id
<!-- 如果QueryWrapper存在的话，就拼where条件，因为QueryWrapper其实就是在封装where条件-->
<if test="ew.emptyOfWhere == false">
${ew.customSqlSegment}
</if>
</select>
————————————————
版权声明：本文为CSDN博主「Turbo码先生」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/qq_34037358/article/details/116173960