【三】MyBatis的SQL映射文件 ----- 一对多的关联查询

浏览数：30 / 时间：2015年06月12日

一对多关联查询的解决方案也有两种：嵌套结果和嵌套查询

在上一篇博客中已经讲了一对一的关联查询，在MySQL数据库中已经存在了班级表 class 和教师表 teacher。为了满足一对多关联查询的要求，还需要创建一张学生表 student。这样，在查询班级信息的同时，可以得到教师和学生的信息。

创建 student 表的 SQL 语句如下：

CREATE TABLE student(
    s_id INT PRIMARY KEY AUTO_INCREMENT, 
    s_name VARCHAR(20), 
    class_id INT
);
INSERT INTO student(s_name, class_id) VALUES(‘xs_A‘, 1);
INSERT INTO student(s_name, class_id) VALUES(‘xs_B‘, 1);
INSERT INTO student(s_name, class_id) VALUES(‘xs_C‘, 1);
INSERT INTO student(s_name, class_id) VALUES(‘xs_D‘, 2);
INSERT INTO student(s_name, class_id) VALUES(‘xs_E‘, 2);
INSERT INTO student(s_name, class_id) VALUES(‘xs_F‘, 2);

重写班级实体类 Class 如下：

SQL映射文件如下：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="day03.bean.classMapper">
    <!-- 方式一：嵌套结果 -->
    <select id="getClass" parameterType="int" resultMap="getClassMap">
        SELECT *
        FROM class c,student s,teacher t WHERE c.c_id = s.class_id AND
        c.teacher_id = t.t_id AND c.c_id = #{id}
    </select>

    <resultMap type="Class" id="getClassMap">
        <id property="id" column="c_id" />
        <result property="name" column="c_name" />
        <association property="teacher" javaType="Teacher">
            <id property="id" column="t_id" />
            <result property="name" column="t_name" />
        </association>
        <!-- ofType:指定集合元素的类型 -->
        <collection property="students" ofType="Student">
            <id property="id" column="s_id" />
            <result property="name" column="s_name" />
            <result property="classId" column="class_id" />
        </collection>
    </resultMap>

    <!-- 方式二：嵌套查询：通过执行另外一个SQL映射语句来返回预期的复杂类型 
        SELECT * FROM class WHERE c_id=1; 
        SELECT * FROM teacher WHERE t_id=1 //值1 是上一个查询得到的teacher_id字段的值
        SELECT * FROM student WHERE class_id=1 //值1是第一个查询得到的c_id字段的值 -->
    <select id="getClass2" parameterType="int" resultMap="getClassMap2">
        SELECT * FROM class WHERE c_id = #{id}
    </select>
    <select id="getTeacher" parameterType="int" resultType="Teacher">
        SELECT t_id id,t_name name FROM teacher WHERE t_id = #{id}
    </select>
    <select id="getStudent" parameterType="int" resultType="Student">
        SELECT s_id id,s_name name,class_id classId FROM student WHERE s_id = #{id}
    </select>
    <resultMap type="Class" id="getClassMap2">
        <id property="id" column="c_id" />
        <result property="name" column="c_name" />
        <result/>
        <association property="teacher" javaType="Teacher" select="getTeacher" column="teacher_id"/>
        <collection property="students" select="getStudent" column="c_id" ofType="Student"/>
    </resultMap>
</mapper>