mybatis/mybatisplus的xml文件子查询案例

前言

之前学习mybatis的时候，有各种one2many,many2many的连表查询业务。最近公司项目采用的是mybatis plus持久层框架，其实大差不差。在网上百度的有关子查询的xml文件sql语句也有，感觉看起来吃力，只是贴个代码。
由于我这边结合业务走通了，故做个详细的操作过程。

业务对象

涉及到两张表的业务对象，monitor_data和place_list，这两张表的关系是通过equip_id联系的，业务需求是：通过传参数gis_code的值先查询place_list表的equip_id，再以查询结果的equip_id值作为参数查询monitor_data得所有相关记录。
首先贴出来两张表及Entity

@Data
@TableName("place_list")
public class PlaceList {
	//用集合封装monitor_data表中的数据
	private Set<MonitorData> monitorData;  
    
	private java.lang.String id;
	private java.lang.String equipId;
	private java.lang.String equipType;
	private java.lang.String placeId;
	private java.lang.String placeAddress;
	private java.lang.String placeName;
	private java.lang.String province;
	private java.lang.String city;
	private java.lang.String country;
	private java.math.BigDecimal lng;
	private java.math.BigDecimal lat;
	private java.lang.Integer nextconnInterval;
	private java.math.BigDecimal warnValue;
	private java.math.BigDecimal alramValue;
	private java.math.BigDecimal outValue;
	private java.math.BigDecimal nodeDepth;
	private java.math.BigDecimal nodeBottomElev;
	private java.math.BigDecimal nodePipeDiameter;
	private java.math.BigDecimal installDepth;
	private java.math.BigDecimal installLineLength;
	private java.math.BigDecimal installOffset;
	private java.lang.String createBy;
	private java.util.Date createTime;
	private java.lang.String updateBy;
	private java.util.Date updateTime;
	private java.lang.String corpCode;
	private java.lang.String prjCode;
	private java.lang.String delFlag;
	private java.lang.String monitorPoint;
    private java.lang.String gisCode;
	private java.lang.String status;
}

@Data
@TableName("monitor_data")
public class MonitorData {
    
	private java.lang.String id;
	private java.lang.String equipId;
	private java.util.Date monitorTime;
	private java.math.BigDecimal monitorLiquid;
	private java.math.BigDecimal measureFlow;
	private java.math.BigDecimal measureVelo;
	private java.math.BigDecimal measureRain;
	private java.math.BigDecimal measureTurbidity;
	private java.math.BigDecimal measureVoltage;
	private java.lang.Integer measureVoltrate;
	private java.math.BigDecimal measureTemp;
	private java.math.BigDecimal measureHumidity;
	private java.math.BigDecimal measureDo;
	private java.lang.String createBy;
	private java.util.Date createTime;
	private java.lang.String updateBy;
	private java.util.Date updateTime;
	private java.lang.String corpCode;
	private java.lang.String prjCode;
	private java.lang.String delFlag;
}

三层设计

由于monitor_data对象的三层在mybatis plus中非常简单，没有代码，就不展示了。
主要展示place_list对象：。
先看一下目录结构图：

Controller

代码只展示关键部分逻辑，很多平台提供的切面功能就忽略了，大家操作时把握重点
根据传参gisCode调用service层代码

@Slf4j
@RestController
@RequestMapping("/placelist/placeList")
public class PlaceListController {
	@Autowired
	private IPlaceListService placeListService;

	@RequestMapping(value = "/queryByGisCode")
	public Result<PlaceList> queryByGisCode(@RequestParam(name="gisCode",required=true) String gisCode) {
		Result<PlaceList> result = new Result<PlaceList>();

		PlaceList placeList = placeListService.getMonitorDataByGisCode(gisCode);
		if(placeList==null) {
			result.error500("未找到对应实体！");
		}else {
			result.setResult(placeList);
			result.setSuccess(true);
		}
		return result;
	}
}

Service

接口：

IPlaceListService.java
public interface IPlaceListService extends IService<PlaceList> {

    int deleteAll();

    PlaceList getMonitorDataByGisCode(String gisCode);
}

实现类：

PlaceListServiceImpl .java
@Service
public class PlaceListServiceImpl extends ServiceImpl<PlaceListMapper, PlaceList> implements IPlaceListService {

    @Resource
    private PlaceListMapper placeListMapper;

    @Override
    public PlaceList getMonitorDataByGisCode(String gisCode) {
        return placeListMapper.selectMonitorDataByGisCode(gisCode);
    }
}

Dao层

接口：

PlaceListMapper .java
public interface PlaceListMapper extends BaseMapper<PlaceList> {
    PlaceList selectMonitorDataByGisCode(String gisCode);
}

动态代理xml文件：

<?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.ecidi.modules.outdata.placelist.mapper.PlaceListMapper">
    <select id="selectByGisCode" resultType="com.ecidi.modules.outdata.monitordata.entity.MonitorData">
      select * from monitor_data where equip_id = #{equip_id}
    </select>

    <resultMap id="placeListMapper" type="com.ecidi.modules.outdata.placelist.entity.PlaceList">
        <id column="id" property="id"/>
        <result column="equip_id" property="equipId"/>
        <result column="gis_code" property="gisCode"/>
        <collection property="monitorData" ofType="com.ecidi.modules.outdata.monitordata.entity.MonitorData"
                    select="selectByGisCode" column="equip_id"/>
    </resultMap>

    <select id="selectMonitorDataByGisCode" resultMap="placeListMapper">
        select equip_id from place_list where gis_code=#{gisCode}
    </select>
</mapper>

最关键的就是这个xml文件，resultMap 配置的是查询返回数据的Map，
指定type是主表place_list表中的字段，collection相当于在placelist的Entity中添加的private Set monitorData; ，取集合之意。
collection中的select是指定查询monitor_data表中的数据的配置项<select id=“selectByGisCode”

测试

查看控制台日志：

2020-04-21 16:33:01 [http-nio-8080-exec-3] DEBUG   at org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159)- ==>  Preparing: SELECT equip_id FROM place_list WHERE place_list.corp_code = 'A01' AND gis_code = ? 
2020-04-21 16:33:01 [http-nio-8080-exec-3] DEBUG   at org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159)- ==> Parameters: JWS037101(String)
2020-04-21 16:33:01 [http-nio-8080-exec-3] DEBUG   at org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159)- ====>  Preparing: SELECT * FROM monitor_data WHERE monitor_data.corp_code = 'A01' AND equip_id = ? 
2020-04-21 16:33:01 [http-nio-8080-exec-3] DEBUG   at org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159)- ====> Parameters: 19135118(String)