Mybatis一对多查询

最新推荐文章于 2023-11-12 20:18:45 发布

额头一凉

最新推荐文章于 2023-11-12 20:18:45 发布

阅读量224

点赞数

文章标签： mybatis java mysql

本文链接：https://blog.csdn.net/apple_56005588/article/details/130015340

版权

**注*:当查询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>