Mybatis关联查询

mybatis中涉及到关联查询时有以下两种做法。

1.使用多次sql查询

    List<ArrangeTemplateReq> selectTemplateDetail();



    <select id="selectTemplateDetail" resultMap="arrangeTemplateInfo">
        select h.name as hospitalName, dep.name as departmentName, t.* from arrange_template t
        left join hospital h on t.hospital_id = h.id
        left join department dep on t.department_id = dep.id
    </select>

    <resultMap id="arrangeTemplateInfo" type="com.boco.hospital.basic.hospital.bean.req.ArrangeTemplateReq">
        <id property="id" column="id"/>
        <collection property="arrangeTemplateDetails" column="id" select="selectDetailsByTemplateId" ofType="com.boco.hospital.basic.hospital.bean.ArrangeTemplateDetailEntity"></collection>
    </resultMap>

    <select id="selectDetailsByTemplateId" parameterType="long" resultType="com.boco.hospital.basic.hospital.bean.ArrangeTemplateDetailEntity">
        select t.*,d.name as doctorName from arrange_template_detail t
        left join doctor d on t.doctor_id = d.id
        where t.arrange_template_id = #{id}
    </select>

2.使用单个sql查询

     List<ArrangeTemplateReq> selectTemplateDetail();



    <select id="selectTemplateDetail" resultMap="arrangeTemplateInfo">
        select t.id as id, h.name as hospitalName, dep.name as departmentName, t.name as templateName,atd.number_class_name as numberClassName, atd.id as detailId from arrange_template t
        left join hospital h on t.hospital_id = h.id
        left join department dep on t.department_id = dep.id left join arrange_template_detail atd on t.id = atd.arrange_template_id
    </select>

    <resultMap id="arrangeTemplateInfo" type="com.boco.hospital.basic.hospital.bean.req.ArrangeTemplateReq">
        <id property="id" column="id"/>
        <result property="hospitalName" column="hospitalName"/>
        <result property="departmentName" column="departmentName"/>
        <collection property="arrangeTemplateDetails" ofType="com.boco.hospital.basic.hospital.bean.ArrangeTemplateDetailEntity">
            <id column="detailId" property="id" />
            <result column="numberClassName" property="numberClassName" />
        </collection>
    </resultMap>

这两种做法各有利弊，在具体情况下可以考虑使用哪一个，第一种逻辑清晰，较好理解，可以支持分页查询。第二种是框架帮忙做了一下分类组合的操作，只执行一次sql，一定条件下效率较高，但是在需要分页查询时可能不容易得到想要的结果。

以上例子为一对多的情况，如果是一对一则将collection标签替换为association标签，将标签中的ofType属性换成javaType就可以了。同样支持者两种写法。