注*:当查询sql不同表之间有相同字段名,一定要另起别名区分开来!!!
如果id两个表id名相同 实体类的list 只有一条数据
如果普通字段名相同,赋予默认值
1.首先对应好实体类之间的关系
@Data
public class ItemVO {
private String id;
private String menuId;
private String name;
private String type;
private String sort;
private Date createTime;
private String createBy;
private Date modifyTime;
private String modifyBy;
private Boolean delFlag;
private List<SlotVO> slotList;
}
@Data
public class SlotVO {
private String id;
private String itemId;
private Integer sort;
private String location;
private Date createTime;
private String createBy;
private Date modifyTime;
private String modifyBy;
private Boolean delFlag;
private List<TriggerPO> triggerList;
}
@Data
public class TriggerPO{
private String id;
private String slotId;
private Integer slotType;
private String label;
}
2.Mapper接口
public interface CopyMapper {
List<ItemVO> selectAllByMenuId(String menuId);
}
3.mapper.xml
<resultMap id="itemAllResultMap" type="cc.crrc.manage.business.phm.monitoringconfig.entity.ItemVO">
<id property="id" column="id"/>
<result property="menuId" column="menu_id"/>
<result property="name" column="name"/>
<result property="type" column="type"/>
<result property="sort" column="iSort"/>
<result property="createTime" column="icreate_time"/>
<result property="createBy" column="icreate_by"/>
<result property="modifyTime" column="imodify_time"/>
<result property="modifyBy" column="imodify_by"/>
<result property="delFlag" column="idel_flag"/>
<collection property="slotList" ofType="cc.crrc.manage.business.phm.monitoringconfig.entity.SlotVO" resultMap="slotResultMap"/>
</resultMap>
<resultMap id="slotResultMap" type="cc.crrc.manage.business.phm.monitoringconfig.entity.SlotVO">
<id property="id" column="slotId"/>
<result property="itemId" column="sitem_id"/>
<result property="sort" column="sSort"/>
<result property="location" column="location"/>
<result property="createTime" column="screate_time"/>
<result property="createBy" column="screate_by"/>
<result property="modifyTime" column="smodify_time"/>
<result property="modifyBy" column="smodify_by"/>
<result property="delFlag" column="sdel_flag"/>
<collection property="triggerList" resultMap="triggerResultMap" ofType="cc.crrc.manage.business.phm.monitoringconfig.entity.TriggerPO"/>
</resultMap>
<resultMap id="triggerResultMap" type="cc.crrc.manage.business.phm.monitoringconfig.entity.TriggerPO">
<id column="triggerId" property="id" />
<result column="slot_id" property="slotId" />
<result column="label" property="label" />
</resultMap>
<sql id="selectAll" >
select item.id,
item.menu_id,
item.name,
item.type,
item.sort as iSort,
item.create_time as icreate_time,
item.create_by as icreate_by,
item.modify_time as imodify_time,
item.modify_by as imodify_by,
item.del_flag as idel_flag,
slot.id as slotId,
slot.sort as sSort,
slot.location,
slot.slot_type,
slot.create_time as screate_time,
slot.create_by as screate_by,
slot.modify_time as smodify_time,
slot.modify_by as smodify_by,
slot.del_flag as sdel_flag,
slot.item_id as sitem_id,
trigger.id as triggerId,
trigger.slot_id,
trigger.label,
trigger.sort as tsort,
from monitor_table_item item
left join monitor_slot slot on item.id = slot.item_id and slot.del_flag = false
left join monitor_trigger trigger on trigger.slot_id = slot.id and trigger.del_flag = false
</sql>
<select id="selectAllByMenuId" resultMap="itemAllResultMap">
<include refid="selectAll"/>
where item.del_flag = false
and item.menu_id = #{menuId}
</select>