Mybatis_association(一对一 )

association(一对一 )

实体类

@Data
public class EchartsDataRtn {
    private Integer buildingId;//宿舍楼id
    private String buildingName;//宿舍楼名称
    private Integer total;// 总人数
    private Integer inNum;// 入住人数
}

xml文件

    <!--查询宿舍楼名和宿舍楼id-->
    <select id="queryEchartsData" resultMap="queryByBuildingId">
        select * from d_building where campus_id = #{campusId}
    </select>
    <!--resultMap -->
    <resultMap id="queryByBuildingId" type="EchartsDataRtn">
        <id column="id" property="buildingId"/>
        <result column="name" property="buildingName"/>
        <!--association 对应子查询多个参数的时候,buildingName 没用上,演示用 -->
        <association column="{buildingId = id,buildingName = name}" property="total" select="queryTotal"/>
        <!--association对应子查询一个参数的时候,id是数据库字段,对应实体的buildingId -->
        <association column="id" property="inNum" select="queryInNum"/>
    </resultMap>
    <select id="queryTotal" resultType="java.lang.Integer">
        select IFNULL(sum(space), 0)
        from d_building bui
                 join d_room room on bui.id = room.building_id
                 join d_room_type type on room.type_id = type.id
        where room.`enable` = 1
          and bui.id = #{buildingId}
    </select>
    <!--注意:这里的传参是buildingId(实体的association) -->
    <select id="queryInNum" resultType="java.lang.Integer">
        select count(*)
        from d_room_student rs
                 join d_room room on rs.room_id = room.id
        where rs.state in (0, 1)
          and room.building_id = #{buildingId}
    </select>