<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.jeecg.modules.board.mapper.BoardLightManagementMapper">
<resultMap id="boardLightRequestMap" type="org.jeecg.modules.board.dto.BoardLightRequestQueryVo">
<id property="deviceId" column="deviceId"/>
<result property="deviceName" column="device_name"/>
<result property="spec" column="spec"/>
<result property="userDepartmentLeader" column="user_department_leader"/>
<result property="userDepartmentLeaderName" column="userDepartmentLeaderName"/>
<collection property="deviceLight" ofType="org.jeecg.modules.board.dto.DeviceLightDto">
<id property="taskId" column="taskId"/>
<result property="updateUser" column="updateUser"/>
<result property="lightTypeCode" column="light_type_code"/>
<result property="status" column="status"/>
<result property="color" column="color"/>
<result property="handlerIds" column="handler_ids"/>
<result property="handlerNames" column="handler_names"/>
<result property="request" column="request"/>
</collection>
</resultMap>
<resultMap id="boardLightRequestMapByTwoStep" type="org.jeecg.modules.board.dto.BoardLightRequestQueryVo">
<id property="deviceId" column="deviceId"/>
<result property="deviceName" column="device_name"/>
<result property="spec" column="spec"/>
<result property="userDepartmentLeader" column="user_department_leader"/>
<result property="userDepartmentLeaderName" column="userDepartmentLeaderName"/>
<collection property="deviceLight" select="boardLightRequestListStepTwo" column="{deviceId=deviceId,lightTypeCodeList=lightTypeCodeList}">
<id property="taskId" column="taskId"/>
<result property="updateUser" column="updateUser"/>
<result property="lightTypeCode" column="light_type_code"/>
<result property="status" column="status"/>
<result property="color" column="color"/>
<result property="handlerIds" column="handler_ids"/>
<result property="handlerNames" column="handler_names"/>
<result property="request" column="request"/>
</collection>
</resultMap>
<select id="boardLightRequestList" parameterType="org.jeecg.modules.board.dto.BoardLightRequestQueryDto"
resultMap="boardLightRequestMap">
select md.id as deviceId,md.device_name,md.spec,md.user_department_leader,
mlt.light_type_code,mlt.status,mlt.id as taskId,mlt.update_user as updateUser,su.realname as userDepartmentLeaderName,
mlt.handler_ids,mlt.handler_names,matc.request,matc.colour as color
from mess_device md
left join management_lighting_task mlt on mlt.device_id=md.id
left join management_andon_type_config matc on matc.code=mlt.light_type_code and matc.is_delete=0
left join sys_user su on md.user_department_leader=su.id and su.del_flag=0
<where>
md.is_delete='0'
<if test="dto.statusList!=null">
and FIND_IN_SET(mlt.status,#{dto.statusList})
</if>
<if test="dto.workshopId!=null and dto.workshopId!=''">
and mlt.workshop_id = #{dto.workshopId}
</if>
<if test="dto.lightTypeCodeList!=null">
and FIND_IN_SET(mlt.light_type_code,#{dto.lightTypeCodeList})
</if>
</where>
order by mlt.create_time desc
</select>
<select id="boardLightRequestListStepOne" parameterType="org.jeecg.modules.board.dto.BoardLightRequestQueryDto" resultMap="boardLightRequestMapByTwoStep">
select md.id as deviceId,md.device_name,md.spec,md.user_department_leader,
su.realname as userDepartmentLeaderName, #{dto.lightTypeCodeList} lightTypeCodeList
from mess_device md
left join sys_user su on md.user_department_leader=su.id and su.del_flag=0
left join produce_workshop pw ON md.workshop_id = pw.id
<where>
md.is_delete='0' and md.is_collect_device = 1
<if test="dto.workshopId!=null and dto.workshopId!=''">
and mlt.workshop_id = #{dto.workshopId}
</if>
</where>
order by md.create_time desc
</select>
<select id="boardLightRequestListStepTwo" resultType="org.jeecg.modules.board.dto.DeviceLightDto">
select mlt.light_type_code,mlt.status,mlt.id as taskId,mlt.update_user as updateUser,
mlt.handler_ids,mlt.handler_names,matc.request,matc.colour as color
from management_lighting_task mlt
left join management_andon_type_config matc on matc.code=mlt.light_type_code
and matc.is_delete=0
<where>
mlt.status in (1, 2)
<if test="deviceId != null and deviceId != ''">
and mlt.device_id = #{deviceId}
</if>
<if test = "lightTypeCodeList != null and lightTypeCodeList != ''">
and FIND_IN_SET(mlt.light_type_code, #{lightTypeCodeList})
</if>
</where>
</select>
</mapper>
在这里插入代码片
resultmap的collection分布查询,第一步中的查询条件传到第二步,第一步的dto中有三个前端的参数,
@Data
@ApiModel(value ="安灯请求看板大屏数据查询参数",description="安灯请求看板大屏数据查询参数")
public class BoardLightRequestQueryDto {
@ApiModelProperty(value ="工作车间id")
private Long workshopId;
@ApiModelProperty(value ="把用户的选择的安灯请求编号,用逗号隔开,用字符串的形式传过来")
private String lightTypeCodeList;
@ApiModelProperty(value ="安灯请求状态list,包含待响应和处理中,用于筛选条件")
private String statusList;
}
分两步进行查询,第一步先查询设备相关信息,只用到了工作车间的筛选条件,后面两个筛选条件是第二部查询DeviceLightDto列表的条件,所以在第一步需要把dto中后面两个筛选条件传到第二步
```java
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import java.util.List;
@Data
@ApiModel(value ="安灯请求看板大屏list返回数据",description = "安灯请求看板大屏list返回数据")
public class BoardLightRequestQueryVo {
@ApiModelProperty(value ="设备id")
private String deviceId;
@ApiModelProperty(value ="设备名称")
private String deviceName;
@ApiModelProperty(value ="设备规格")
private String spec;
@ApiModelProperty(value ="设备使用部门负责人id")
private String userDepartmentLeader;
@ApiModelProperty(value ="设备使用部门负责人姓名")
private String userDepartmentLeaderName;
private List<DeviceLightDto> deviceLight;
}
这里直接在第一步的sql中加入#{dto.lightTypeCodeList} lightTypeCodeList
```java
<select id="boardLightRequestListStepOne" parameterType="org.jeecg.modules.board.dto.BoardLightRequestQueryDto" resultMap="boardLightRequestMapByTwoStep">
select md.id as deviceId,md.device_name,md.spec,md.user_department_leader,
su.realname as userDepartmentLeaderName, #{dto.lightTypeCodeList} lightTypeCodeList
from mess_device md
left join sys_user su on md.user_department_leader=su.id and su.del_flag=0
left join produce_workshop pw ON md.workshop_id = pw.id
<where>
md.is_delete='0' and md.is_collect_device = 1
<if test="dto.workshopId!=null and dto.workshopId!=''">
and mlt.workshop_id = #{dto.workshopId}
</if>
</where>
order by md.create_time desc
</select>
然后在resultmap中将第一步的dto中的lightTypeCodeList作为参数传给第二部
```java
<resultMap id="boardLightRequestMapByTwoStep" type="org.jeecg.modules.board.dto.BoardLightRequestQueryVo">
<id property="deviceId" column="deviceId"/>
<result property="deviceName" column="device_name"/>
<result property="spec" column="spec"/>
<result property="userDepartmentLeader" column="user_department_leader"/>
<result property="userDepartmentLeaderName" column="userDepartmentLeaderName"/>
<collection property="deviceLight" select="boardLightRequestListStepTwo" column="{deviceId=deviceId,lightTypeCodeList=lightTypeCodeList}">
<id property="taskId" column="taskId"/>
<result property="updateUser" column="updateUser"/>
<result property="lightTypeCode" column="light_type_code"/>
<result property="status" column="status"/>
<result property="color" column="color"/>
<result property="handlerIds" column="handler_ids"/>
<result property="handlerNames" column="handler_names"/>
<result property="request" column="request"/>
</collection>
</resultMap>
第二部拿到第一步传过来的dto.lightTypeCodeList在进行筛选查询
<select id="boardLightRequestListStepTwo" resultType="org.jeecg.modules.board.dto.DeviceLightDto">
select mlt.light_type_code,mlt.status,mlt.id as taskId,mlt.update_user as updateUser,
mlt.handler_ids,mlt.handler_names,matc.request,matc.colour as color
from management_lighting_task mlt
left join management_andon_type_config matc on matc.code=mlt.light_type_code
and matc.is_delete=0
<where>
mlt.status in (1, 2)
<if test="deviceId != null and deviceId != ''">
and mlt.device_id = #{deviceId}
</if>
<if test = "lightTypeCodeList != null and lightTypeCodeList != ''">
and FIND_IN_SET(mlt.light_type_code, #{lightTypeCodeList})
</if>
</where>
</select>
至此问题解决