MyBatis 学习总结（六）——一对多联表查询

一对多联表查询

与一对一联表查询差不多，有2种方式:嵌套结果和嵌套查询
定义使用到的实体类(get set 等必备方法自行添加)：

public class Student {
    private int id;
    private String name;
}

public class Classes {
    private int id;
    private String name;
    private Teacher teacher;
    private List<Student> students;
}

方式一（嵌套结果）：

1.编写配置文件
ClassMapper.xml

<mapper namespace="com.wsb.mybatis.beans.ClassMapper">
    <select id="selectClass" parameterType="int" resultMap="getClassMap">
        SELECT *
        FROM class c, student s
        WHERE c.c_id = s.class_id AND c.c_id = #{id}
    </select>
    <resultMap type="Class" id="getClassMap">
        <id property="id" column="c_id" />
        <result property="name" column="c_name" />
        <!-- 集合是用 collection 标签，用于封装查询到的students数据集合 -->
        <collection property="students" ofType="Student">
            <id property="id" column="s_id" />
            <result property="name" column="s_name" />
        </collection>
    </resultMap>
</mapper>

2.测试：

public class TestClass {

    @Test
    public void testSelect(){

        //MyBatisUtil 是我自己写的工具类，用户获取 SqlFactory 的对象
        SqlSession session = MyBatisUtil.getFactory().openSession();
        String statement = "com.wsb.mybatis.beans.ClassMapper.selectClass";
        Class class1 = session.selectOne(statement , 1);
        session.commit();
        System.out.println(class1);
        session.close();
    }
}

3。结果：Class [id=1, name=bj_a, teacher=null, students=[Student [id=1, name=xs_A], Student [id=2, name=xs_B], Student [id=3, name=xs_C], Student [id=7, name=xs_A], Student [id=8, name=xs_B], Student [id=9, name=xs_C]]]

方式二（嵌套查询）：

ClassMapper.xml

<mapper namespace="com.wsb.mybatis.beans.ClassMapper">

    <select id="selectClass" parameterType="int" resultMap="ClassResultMap">
        SELECT * FROM class WHERE c_id = #{id}
    </select>
    <select id="selectTeacher" parameterType="int" resultType="Teacher">
        SELECT t_id id, t_name name FROM
        teacher WHERE t_id = #{id}
    </select>
    <select id="selectStudent" parameterType="int" resultType="Student">
        SELECT s_id id, s_name name FROM
        student WHERE class_id = #{id}
    </select>
    <resultMap type="Class" id="ClassResultMap">
        <id property="id" column="c_id" />
        <result property="name" column="c_name" />
        <association property="teacher" column="teacher_id"
            select="selectTeacher">
        </association>
        <!-- 通过使用  select="selectStudent" 查询数据并封装成 List<Student> -->
        <collection property="students" column="c_id" select="selectStudent">
        </collection>
    </resultMap>

结果不测试，与第一种方式一样