SpringBoot集成MyBatis-Plus: 自定义Mapper 简单查询和分页查询

怼烎@ 2023-10-10 12:24 11阅读 0赞

## 项目说明 ##

本文介绍Spring Boot集成MyBatis-Plus框架后，  
基于已经创建好的Spring Boot工程，  
添加自定义的SQL实现复杂查询等操作。  
自定义SQL主要有方式：XML配置文件

## 1.XML配置文件方式（自定义Mapper 简单查询） ##

首先在Spring Boot中指定需要加载的XML文件，  
然后新建对应接口类的XML文件，  
开发对应接口方法的自定义SQL语句。

通过动态参数查询条件的工程信息，自定义接口如下：

public interface UcasProjectMapper extends BaseMapper<UcasProject> {
    	
    	List<UcasProjectVo> selectList(Map<String, Object> parameter);
    	
    }

首先修改application.properties文件：

#mybatis xml 文件配置
    mybatis.mapper-locations=classpath*:mapper/*Mapper.xml
    mybatis.type-aliases-package=com.zzg.entity

然后在src\\main\\resources目录下，  
创建目录mapper，  
在mapper下创建文件UcasProjectMapper.xml，  
在XML文件中开发自定义SQL语句：

<?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.digipower.mapper.UcasProjectMapper">
      <resultMap id="BaseResultMap" type="com.digipower.entity.UcasProject">
        <id column="sid" jdbcType="VARCHAR" property="sid" />
        <result column="item_no" jdbcType="VARCHAR" property="itemNo" />
        <result column="item_name" jdbcType="VARCHAR" property="itemName" />
        <result column="project_no" jdbcType="VARCHAR" property="projectNo" />
        <result column="project_name" jdbcType="VARCHAR" property="projectName" />
        <result column="project_category" jdbcType="VARCHAR" property="projectCategory" />
        <result column="project_address" jdbcType="VARCHAR" property="projectAddress" />
        <result column="project_location" jdbcType="VARCHAR" property="projectLocation" />
        <result column="plan_price" jdbcType="VARCHAR" property="planPrice" />
        <result column="longitude" jdbcType="VARCHAR" property="longitude" />
        <result column="latitude" jdbcType="VARCHAR" property="latitude" />
        <result column="winner_price" jdbcType="VARCHAR" property="winnerPrice" />
        <result column="contract_price" jdbcType="VARCHAR" property="contractPrice" />
        <result column="cons_permit_no" jdbcType="VARCHAR" property="consPermitNo" />
        <result column="plan_permit_no" jdbcType="VARCHAR" property="planPermitNo" />
        <result column="plan_project_no" jdbcType="VARCHAR" property="planProjectNo" />
        <result column="contract_start_dt" jdbcType="TIMESTAMP" property="contractStartDt" />
        <result column="contract_end_dt" jdbcType="TIMESTAMP" property="contractEndDt" />
        <result column="architecture_area" jdbcType="VARCHAR" property="architectureArea" />
        <result column="cons_range" jdbcType="VARCHAR" property="consRange" />
        <result column="send_dt" jdbcType="TIMESTAMP" property="sendDt" />
        <result column="cons_report_dt" jdbcType="TIMESTAMP" property="consReportDt" />
        <result column="tenderer" jdbcType="VARCHAR" property="tenderer" />
        <result column="year_serial_number" jdbcType="VARCHAR" property="yearSerialNumber" />
        <result column="cons_report_type" jdbcType="VARCHAR" property="consReportType" />
        <result column="send_package_type" jdbcType="VARCHAR" property="sendPackageType" />
        <result column="cons_unit_sid" jdbcType="VARCHAR" property="consUnitSid" />
        <result column="build_unit_sid" jdbcType="VARCHAR" property="buildUnitSid" />
        <result column="quality_supervision_unit_sid" jdbcType="VARCHAR" property="qualitySupervisionUnitSid" />
        <result column="recon_unit_sid" jdbcType="VARCHAR" property="reconUnitSid" />
        <result column="design_unit_sid" jdbcType="VARCHAR" property="designUnitSid" />
      </resultMap>
      
      <resultMap id="BaseResultMapVo" type="com.digipower.vo.UcasProjectVo"
    		extends="BaseResultMap">
    		<association property="consUnit"
    			javaType="com.digipower.entity.UcasUnit">
    				<id column="cons_sid" jdbcType="VARCHAR" property="sid" />
    			    <result column="cons_unit_name" jdbcType="VARCHAR" property="unitName" />
    			    <result column="cons_unit_type" jdbcType="VARCHAR" property="unitType" />
    			    <result column="cons_unit_legal_person" jdbcType="VARCHAR" property="unitLegalPerson" />
    			    <result column="cons_unit_legal_person_idcard" jdbcType="VARCHAR" property="unitLegalPersonIdcard" />
    			    <result column="cons_unit_add" jdbcType="VARCHAR" property="unitAdd" />
    			    <result column="cons_unit_contact_person" jdbcType="VARCHAR" property="unitContactPerson" />
    			    <result column="cons_unit_contact_tel" jdbcType="VARCHAR" property="unitContactTel" />
    			    <result column="cons_unit_qualification_level" jdbcType="VARCHAR" property="unitQualificationLevel" />
    		</association>
    		<association property="buildUnit"
    			javaType="com.digipower.entity.UcasUnit">
    				<id column="build_sid" jdbcType="VARCHAR" property="sid" />
    			    <result column="build_unit_name" jdbcType="VARCHAR" property="unitName" />
    			    <result column="build_unit_type" jdbcType="VARCHAR" property="unitType" />
    			    <result column="build_unit_legal_person" jdbcType="VARCHAR" property="unitLegalPerson" />
    			    <result column="build_unit_legal_person_idcard" jdbcType="VARCHAR" property="unitLegalPersonIdcard" />
    			    <result column="build_unit_add" jdbcType="VARCHAR" property="unitAdd" />
    			    <result column="build_unit_contact_person" jdbcType="VARCHAR" property="unitContactPerson" />
    			    <result column="build_unit_contact_tel" jdbcType="VARCHAR" property="unitContactTel" />
    			    <result column="build_unit_qualification_level" jdbcType="VARCHAR" property="unitQualificationLevel" />
    		</association>
    		<association property="qualitySupervisionUnit"
    			javaType="com.digipower.entity.UcasUnit">
    				<id column="quality_supervision_sid" jdbcType="VARCHAR" property="sid" />
    			    <result column="quality_supervision_unit_name" jdbcType="VARCHAR" property="unitName" />
    			    <result column="quality_supervision_unit_type" jdbcType="VARCHAR" property="unitType" />
    			    <result column="quality_supervision_unit_legal_person" jdbcType="VARCHAR" property="unitLegalPerson" />
    			    <result column="quality_supervision_unit_legal_person_idcard" jdbcType="VARCHAR" property="unitLegalPersonIdcard" />
    			    <result column="quality_supervision_unit_add" jdbcType="VARCHAR" property="unitAdd" />
    			    <result column="quality_supervision_unit_contact_person" jdbcType="VARCHAR" property="unitContactPerson" />
    			    <result column="quality_supervision_unit_contact_tel" jdbcType="VARCHAR" property="unitContactTel" />
    			    <result column="quality_supervision_unit_qualification_level" jdbcType="VARCHAR" property="unitQualificationLevel" />
    		</association>
    		<association property="reconUnit"
    			javaType="com.digipower.entity.UcasUnit">
    				<id column="recon_sid" jdbcType="VARCHAR" property="sid" />
    			    <result column="recon_unit_name" jdbcType="VARCHAR" property="unitName" />
    			    <result column="recon_unit_type" jdbcType="VARCHAR" property="unitType" />
    			    <result column="recon_unit_legal_person" jdbcType="VARCHAR" property="unitLegalPerson" />
    			    <result column="recon_unit_legal_person_idcard" jdbcType="VARCHAR" property="unitLegalPersonIdcard" />
    			    <result column="recon_unit_add" jdbcType="VARCHAR" property="unitAdd" />
    			    <result column="recon_unit_contact_person" jdbcType="VARCHAR" property="unitContactPerson" />
    			    <result column="recon_unit_contact_tel" jdbcType="VARCHAR" property="unitContactTel" />
    			    <result column="recon_unit_qualification_level" jdbcType="VARCHAR" property="unitQualificationLevel" />
    		</association>
    		<association property="designUnit"
    			javaType="com.digipower.entity.UcasUnit">
    				<id column="design_sid" jdbcType="VARCHAR" property="sid" />
    			    <result column="design_unit_name" jdbcType="VARCHAR" property="unitName" />
    			    <result column="design_unit_type" jdbcType="VARCHAR" property="unitType" />
    			    <result column="design_unit_legal_person" jdbcType="VARCHAR" property="unitLegalPerson" />
    			    <result column="design_unit_legal_person_idcard" jdbcType="VARCHAR" property="unitLegalPersonIdcard" />
    			    <result column="design_unit_add" jdbcType="VARCHAR" property="unitAdd" />
    			    <result column="design_unit_contact_person" jdbcType="VARCHAR" property="unitContactPerson" />
    			    <result column="design_unit_contact_tel" jdbcType="VARCHAR" property="unitContactTel" />
    			    <result column="design_unit_qualification_level" jdbcType="VARCHAR" property="unitQualificationLevel" />
    		</association>
      </resultMap>
      
      <sql id="Base_Column_List">
      	ucas_project.sid, ucas_project.item_no, ucas_project.item_name, ucas_project.project_no, ucas_project.project_name, ucas_project.project_category, 
      	ucas_project.project_address, ucas_project.project_location, ucas_project.plan_price, ucas_project.longitude, ucas_project.latitude, ucas_project.winner_price, 
      	ucas_project.contract_price, ucas_project.cons_permit_no, ucas_project.plan_permit_no, ucas_project.plan_project_no, ucas_project.contract_start_dt, ucas_project.contract_end_dt, 
        ucas_project.architecture_area, ucas_project.cons_range, ucas_project.send_dt, ucas_project.cons_report_dt, ucas_project.tenderer, ucas_project.year_serial_number, 
        ucas_project.cons_report_type, ucas_project.send_package_type, ucas_project.cons_unit_sid, ucas_project.build_unit_sid, ucas_project.quality_supervision_unit_sid, 
        ucas_project.recon_unit_sid, ucas_project.design_unit_sid,
        
        a.sid as cons_sid, a.unit_name as cons_unit_name, a.unit_type as cons_unit_type, a.unit_legal_person as cons_unit_legal_person, 
        a.unit_legal_person_idcard as cons_unit_legal_person_idcard, a.unit_add as cons_unit_add, a.unit_contact_person as cons_unit_contact_person, 
        a.unit_contact_tel as cons_unit_contact_tel, a.unit_qualification_level as cons_unit_qualification_level,
        
        b.sid as build_sid, b.unit_name as build_unit_name, b.unit_type as build_unit_type, b.unit_legal_person as build_unit_legal_person, 
        b.unit_legal_person_idcard as build_unit_legal_person_idcard, b.unit_add as build_unit_add, b.unit_contact_person as build_unit_contact_person, 
        b.unit_contact_tel as build_unit_contact_tel, b.unit_qualification_level as build_unit_qualification_level,
        
        c.sid as quality_supervision_sid, c.unit_name as quality_supervision_unit_name, c.unit_type as quality_supervision_unit_type, c.unit_legal_person as quality_supervision_unit_legal_person, 
        c.unit_legal_person_idcard as quality_supervision_unit_legal_person_idcard, c.unit_add as quality_supervision_unit_add, c.unit_contact_person as quality_supervision_unit_contact_person, 
        c.unit_contact_tel as quality_supervision_unit_contact_tel, c.unit_qualification_level as quality_supervision_unit_qualification_level,
        
        d.sid as recon_sid, d.unit_name as recon_unit_name, d.unit_type as recon_unit_type, d.unit_legal_person as recon_unit_legal_person, 
        d.unit_legal_person_idcard as recon_unit_legal_person_idcard, d.unit_add as recon_unit_add, d.unit_contact_person as recon_unit_contact_person, 
        d.unit_contact_tel as recon_unit_contact_tel, d.unit_qualification_level as recon_unit_qualification_level,
        
        e.sid as design_sid, e.unit_name as design_unit_name, e.unit_type as design_unit_type, e.unit_legal_person as design_unit_legal_person, 
        e.unit_legal_person_idcard as design_unit_legal_person_idcard, e.unit_add as design_unit_add, e.unit_contact_person as design_unit_contact_person, 
        e.unit_contact_tel as design_unit_contact_tel, e.unit_qualification_level as design_unit_qualification_level
      </sql>
      <sql id="condition">
      		<if test="itemName != null and itemName !=''">
      			and ucas_project.item_name like '%${itemName}%'
      		</if>
      		<if test="itemNo != null and itemNo !=''">
      			and ucas_project.item_no like '%${itemNo}%'
      		</if>
      		<if test="projectName != null and projectName !=''">
      			and ucas_project.project_name like '%${projectName}%'
      		</if>
      		<if test="projectNo != null and projectNo !=''">
      			and ucas_project.project_no like '%${projectNo}%'
      		</if>
      		<if test="consPermitNo != null and consPermitNo !=''">
      			and ucas_project.cons_permit_no like '%${consPermitNo}%'
      		</if>
      		<if test="startSendDt != null and startSendDt !=''">
      			and ucas_project.send_dt >= TO_DATE(#{startSendDt},'yyyy-mm-dd')
      		</if>
      		<if test="endSendDt != null and endSendDt !=''">
      			and ucas_project.send_dt <= TO_DATE(#{endSendDt},'yyyy-mm-dd')
      		</if>
      </sql>
      <sql id="conditionVo">
      		<if test="vo.itemName != null and vo.itemName !=''">
      			and ucas_project.item_name like '%${vo.itemName}%'
      		</if>
      		<if test="vo.itemNo != null and vo.itemNo !=''">
      			and ucas_project.item_no like '%${vo.itemNo}%'
      		</if>
      		<if test="vo.projectName != null and vo.projectName !=''">
      			and ucas_project.project_name like '%${vo.projectName}%'
      		</if>
      		<if test="vo.projectNo != null and vo.projectNo !=''">
      			and ucas_project.project_no like '%${vo.projectNo}%'
      		</if>
      		<if test="vo.consPermitNo != null and vo.consPermitNo !=''">
      			and ucas_project.cons_permit_no like '%${vo.consPermitNo}%'
      		</if>
      		<if test="vo.startSendDt != null and vo.startSendDt !=''">
      			and ucas_project.send_dt >= TO_DATE(#{vo.startSendDt},'yyyy-mm-dd')
      		</if>
      		<if test="vo.endSendDt != null and vo.endSendDt !=''">
      			and ucas_project.send_dt <= TO_DATE(#{vo.endSendDt},'yyyy-mm-dd')
      		</if>
      </sql>
      
      
      <select id="selectList" parameterType="map" resultMap="BaseResultMapVo">
      		select
    		<include refid="Base_Column_List"></include>
    		from ucas_project
    		left join ucas_unit a on a.sid = ucas_project.cons_unit_sid
    		left join ucas_unit b on b.sid = ucas_project.build_unit_sid
    		left join ucas_unit c on c.sid = ucas_project.quality_supervision_unit_sid
    		left join ucas_unit d on d.sid = ucas_project.recon_unit_sid
    		left join ucas_unit e on e.sid = ucas_project.design_unit_sid
      		where 1 = 1
      		<include refid="condition"></include>
      </select>
      
     </mapper>

在service 和service.impl下添加如下功能代码:

package com.digipower.service;
    
    import java.util.List;
    import java.util.Map;
    
    import com.baomidou.mybatisplus.core.metadata.IPage;
    import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
    import com.baomidou.mybatisplus.extension.service.IService;
    import com.digipower.entity.UcasProject;
    import com.digipower.vo.UcasProjectVo;
    
    public interface UcasProjectService extends IService<UcasProject> {
    
    	List<UcasProjectVo> selectList(Map<String, Object> parameter);
    
    }

package com.digipower.service.impl;
    
    import java.util.List;
    import java.util.Map;
    
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;
    
    import com.baomidou.mybatisplus.core.metadata.IPage;
    import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
    import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
    import com.digipower.entity.UcasProject;
    import com.digipower.mapper.UcasProjectMapper;
    import com.digipower.service.UcasProjectService;
    import com.digipower.vo.UcasProjectVo;
    
    @Service
    public class UcasProjectServiceImpl extends ServiceImpl<UcasProjectMapper, UcasProject> implements UcasProjectService {
    	
    	@Autowired
    	private UcasProjectMapper mapper;
    	
    	@Override
    	public List<UcasProjectVo> selectList(Map<String, Object> parameter) {
    		// TODO Auto-generated method stub
    		return mapper.selectList(parameter);
    	}
    }

在controller 层调用相关服务

// 查
    	@ApiOperation(httpMethod = "POST", value = "查询符合条件工程")
    	@RequestMapping(value = "/getListVo", method = { RequestMethod.POST })
    	@ApiImplicitParams({
    			@ApiImplicitParam(name = "itemName", value = "项目名称", required = false, dataType = "String", paramType = "query"),
    			@ApiImplicitParam(name = "itemNo", value = "项目编号", required = false, dataType = "String", paramType = "query"),
    			@ApiImplicitParam(name = "projectName", value = "工程名称", required = false, dataType = "String", paramType = "query"),
    			@ApiImplicitParam(name = "projectNo", value = "工程编号", required = false, dataType = "String", paramType = "query"),
    			@ApiImplicitParam(name = "consPermitNo", value = "施工许可证号", required = false, dataType = "String", paramType = "query"),
    			@ApiImplicitParam(name = "startSendDt", value = "发证开始时间", required = false, dataType = "String", paramType = "query"),
    			@ApiImplicitParam(name = "endSendDt", value = "发证结束时间", required = false, dataType = "String", paramType = "query") })
    	public Result getListVo(@RequestBody Map<String, Object> parame) {
    		List<UcasProjectVo> list = ucasProjectService.selectList(parame);
    		return Result.ok().setDatas(list);
    	}

效果展示:

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pob3V6aGl3ZW5nYW5n_size_16_color_FFFFFF_t_70][]

## 2.XML配置文件方式（自定义Mapper 分页查询） ##

在XML文件（UcasProjectMapper.xml）中添加自定义分页SQL语句和Mapper 接口映射：

<select id="selectPage" parameterType="map" resultMap="BaseResultMapVo">
      		select
    		<include refid="Base_Column_List"></include>
    		from ucas_project
    		left join ucas_unit a on a.sid = ucas_project.cons_unit_sid
    		left join ucas_unit b on b.sid = ucas_project.build_unit_sid
    		left join ucas_unit c on c.sid = ucas_project.quality_supervision_unit_sid
    		left join ucas_unit d on d.sid = ucas_project.recon_unit_sid
    		left join ucas_unit e on e.sid = ucas_project.design_unit_sid
      		where 1 = 1
      		<include refid="conditionVo"></include>
      </select>

package com.digipower.mapper;
    
    import java.util.List;
    import java.util.Map;
    
    import org.apache.ibatis.annotations.Param;
    
    import com.baomidou.mybatisplus.core.mapper.BaseMapper;
    import com.baomidou.mybatisplus.core.metadata.IPage;
    import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
    import com.digipower.entity.UcasProject;
    import com.digipower.vo.UcasProjectVo;
    
    public interface UcasProjectMapper extends BaseMapper<UcasProject> {
    	
    	List<UcasProjectVo> selectList(Map<String, Object> parameter);
    	
    	IPage<UcasProjectVo> selectPage(Page page, @Param("vo")Map<String, Object> parameter);
    }

在service 和service.impl下添加如下功能代码:

在controller 层调用相关服务

// 查
    	@ApiOperation(httpMethod = "POST", value = "查询符合条件工程")
    	@RequestMapping(value = "/getPageVo", method = { RequestMethod.POST })
    	@ApiImplicitParams({
    			@ApiImplicitParam(name = "itemName", value = "项目名称", required = false, dataType = "String", paramType = "query"),
    			@ApiImplicitParam(name = "itemNo", value = "项目编号", required = false, dataType = "String", paramType = "query"),
    			@ApiImplicitParam(name = "projectName", value = "工程名称", required = false, dataType = "String", paramType = "query"),
    			@ApiImplicitParam(name = "projectNo", value = "工程编号", required = false, dataType = "String", paramType = "query"),
    			@ApiImplicitParam(name = "consPermitNo", value = "施工许可证号", required = false, dataType = "String", paramType = "query"),
    			@ApiImplicitParam(name = "startSendDt", value = "发证开始时间", required = false, dataType = "String", paramType = "query"),
    			@ApiImplicitParam(name = "endSendDt", value = "发证结束时间", required = false, dataType = "String", paramType = "query"),
    			@ApiImplicitParam(name = "buildName", value = "建设单位名称", required = false, dataType = "String", paramType = "query"),
    			@ApiImplicitParam(name = "consName", value = "施工单位名称", required = false, dataType = "String", paramType = "query"),
    			@ApiImplicitParam(name = "qualitySupervisionName", value = "监理单位名称", required = false, dataType = "String", paramType = "query"),
    			@ApiImplicitParam(name = "reconName", value = "勘察单位名称", required = false, dataType = "String", paramType = "query"),
    			@ApiImplicitParam(name = "designName", value = "设计单位名称", required = false, dataType = "String", paramType = "query"),
    			@ApiImplicitParam(name = "yearSerialNumber", value = "年度流水号", required = false, dataType = "String", paramType = "query"),
    			@ApiImplicitParam(name = "itemType", value = "项目类型", required = false, dataType = "String", paramType = "query"),
    			@ApiImplicitParam(name = "sendPackageType", value = "发包类型", required = false, dataType = "String", paramType = "query")
    			
    	})
    	public Result getPageVo(@RequestBody Map<String, Object> parame) {
    		PageParame pageParame = this.initPageBounds(parame);
    		Page<UcasProjectVo> page = new Page<UcasProjectVo>(pageParame.getPage(), pageParame.getLimit());
    
    		IPage<UcasProjectVo> list = ucasProjectService.selectPage(page, parame);
    		if(CollectionUtils.isNotEmpty(list.getRecords())){
    			list.getRecords().stream().forEach(item->{
    				if(StringUtils.isNotEmpty(item.getTenderer())){
    					item.setTenderer(DesensitizationUtil.nameSensitive(item.getTenderer()));
    				}
    				if(StringUtils.isNotEmpty(item.getCompletedProjectPerson())){
    					item.setCompletedProjectPerson(DesensitizationUtil.nameSensitive(item.getCompletedProjectPerson()));
    				}
    				if(StringUtils.isNotEmpty(item.getCompletedProjectPersonTel())){
    					item.setCompletedProjectPersonTel(DesensitizationUtil.mobilePhoneSensitive(item.getCompletedProjectPersonTel()));
    				}
    				if(StringUtils.isNotEmpty(item.getBuildUnitManager())){
    					item.setBuildUnitManager(DesensitizationUtil.nameSensitive(item.getBuildUnitManager()));
    				}
    				if(StringUtils.isNotEmpty(item.getReviewer())){
    					item.setReviewer(DesensitizationUtil.nameSensitive(item.getReviewer()));
    				}
    				
    			});
    		}
    		return Result.ok().setDatas(list);
    	}

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pob3V6aGl3ZW5nYW5n_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20210120114214662.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pob3V6aGl3ZW5nYW5n,size_16,color_FFFFFF,t_70