一对多映射

一对多映射

创建学生表，并插入数据

mysql> 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);

查询

要求：查询班级id是1的班级的老师，学生信息

分析

两种方式可选择：
1. 多表关联查询

select * from class c ,teacher t, student s 
            where c.teacher_id = t.t_id and  s.class_id = c.c_id and c.c_id=1;
 +------+--------+------------+------+--------+------+--------+----------+
| c_id | c_name | teacher_id | t_id | t_name | s_id | s_name | class_id |
+------+--------+------------+------+--------+------+--------+----------+
|    1 | bj_a   |          1 |    1 | LS1    |    1 | xs_A   |        1 |
|    1 | bj_a   |          1 |    1 | LS1    |    2 | xs_B   |        1 |
|    1 | bj_a   |          1 |    1 | LS1    |    3 | xs_C   |        1 |
+------+--------+------------+------+--------+------+--------+----------+
3 rows in set
 ```           
2. 单表多次查询
下一个查询根据前一个查询的结果作为where的条件
```sql
mysql> select * from class where c_id = 1;
+------+--------+------------+
| c_id | c_name | teacher_id |
+------+--------+------------+
|    1 | bj_a   |          1 |
+------+--------+------------+
1 row in set

mysql> select * from teacher where t_id = 1;
+------+--------+
| t_id | t_name |
+------+--------+
|    1 | LS1    |
+------+--------+
1 row in set

mysql> select * from student where class_id = 1;
+------+--------+----------+
| s_id | s_name | class_id |
+------+--------+----------+
|    1 | xs_A   |        1 |
|    2 | xs_B   |        1 |
|    3 | xs_C   |        1 |
+------+--------+----------+
3 rows in set




<div class="se-preview-section-delimiter"></div>

用mybatis的多对一实现

首先创建Student实体类

package com.bart.mybatis.beans;

public class Student {
    @Override
    public String toString() {
        return "Student [id=" + id + ", name=" + name + "]";
    }
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    private int id;
    private String name;
    public Student(int id, String name) {
        super();
        this.id = id;
        this.name = name;
    }
    public Student() {
        super();
    }
}




<div class="se-preview-section-delimiter"></div>

配置mapper

<?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="com.bart.mybatis.mapper.classesMapper2">

    <!-- 
        方式1 
        多表关联查询
        select * from class c ,teacher t, student s 
            where c.teacher_id = t.t_id and  s.class_id = c.c_id and c.c_id=1;
     -->
    <select id="getClasses" parameterType="int" resultMap="getClassesMap">
       select * from class c ,teacher t, student s 
            where c.teacher_id = t.t_id and  s.class_id = c.c_id and c.c_id=#{id}
    </select>

    <resultMap type="_Classes" id="getClassesMap">
        <id property="id" column="c_id"/>
        <result property="name" column="c_name"/>
        <association javaType="_Teacher" property="teacher" >
            <id property="id" column="t_id"/>
            <result property="name" column="t_name"/>
        </association>
        <!-- 注意这里 一对多 用的是 ofType -->
        <collection property="list" ofType="_Student">
            <id property="id" column="s_id"/>
            <result property="name" column="s_name"/>
        </collection>
    </resultMap>
    <!-- 
        方式2 多次查询，互相依赖
        select * from class where c_id = 1;
        select * from teacher where t_id = teacher_id;
        select * from student where class_id = c_id;
     -->

    <select id="getClasses2" parameterType="int" resultMap="getClasses2Map">
        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 from student where class_id = #{id}
    </select>
    <resultMap type="_Classes" id="getClasses2Map">
        <id property="id" column="c_id"/>
        <result property="name" column="c_name"/>
        <association property="teacher" column="teacher_id" select="getTeacher"></association>
        <collection property="list" column="c_id" select="getStudent"></collection>
    </resultMap>

</mapper>





<div class="se-preview-section-delimiter"></div>

配置conf.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!-- 
    development : 开发模式
    work : 工作模式
 -->
    <!-- 加载db.properties -->
    <properties resource="db.properties">
    </properties>

    <!-- 配置别名，可以再mapper中使用，方便操作 -->
    <typeAliases>
        <typeAlias type="com.bart.mybatis.beans.Classes" alias="_Classes"/>
        <typeAlias type="com.bart.mybatis.beans.Teacher" alias="_Teacher"/>
        <typeAlias type="com.bart.mybatis.beans.Student" alias="_Student"/>
    </typeAliases>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC" />
            <dataSource type="POOLED">
                <property name="driver" value="${driver}" />
                <property name="url" value="${url}" />
                <property name="username" value="${name}" />
                <property name="password" value="${password}" />
            </dataSource>
        </environment>
    </environments>

    <!-- 映射mapper配置文件 -->
    <mappers>
       <mapper resource="com/bart/mybatis/mapper/classesMapper2.xml"/>
    </mappers>

</configuration>





<div class="se-preview-section-delimiter"></div>

测试类

/**   一对多查询
     * 获得classes对象并且得到对应的teacher和student
     */
    @Test//方式一
    public void getClasses3(){

        SqlSessionFactory factory = DBUtil.getSessionFactory();
        SqlSession session = factory.openSession();

        String statement = "com.bart.mybatis.mapper.classesMapper2.getClasses";
        Classes classes = session.selectOne(statement,1);
        System.out.println(classes);
        session.close();
    }
    @Test//方式二
    public void getClasses4(){

        SqlSessionFactory factory = DBUtil.getSessionFactory();
        SqlSession session = factory.openSession();

        String statement = "com.bart.mybatis.mapper.classesMapper2.getClasses2";
        Classes classes = session.selectOne(statement,1);
        System.out.println(classes);
        session.close();
    }




<div class="se-preview-section-delimiter"></div>

输出结果

### 输出结果
```java
Classes [id=1, name=bj_a, 
    teacher=Teacher [id=1, name=LS1], 
list=[Student [id=1, name=xs_A], 
    Student [id=2, name=xs_B], 
    Student [id=3, name=xs_C]]]