Mybatis学习（四) —— association 和 collection 的使用

---

前言

我们通常会遇到两组对象一对多或者多对一的关系。

例如：
多对一：一个班50名都有一个班主任老师，即多名同学关联一位老师。
一对多：一名班主任老师管理班里50个同学，即一个集合概念。

针对于上述情况，若想查询某些同学共有的老师是谁，或者一名老师带了多少位同学，则需进行老师信息表teacher与学生信息表student联表查询，就会用到下面介绍的association和collection关键词。

一、多对一关系 —— association

经过前言对多对一关系举例的说明，下面将以代码的方式实现：

1）数据库构建：分别构建测试用的学生表student和老师表teacher，其中student.tid=teacher.id

CREATE TABLE `teacher` (
  `id` INT(10) NOT NULL,
  `name` VARCHAR(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO teacher(`id`, `name`) VALUES (1, '秦老师'); 

CREATE TABLE `student` (
  `id` INT(10) NOT NULL,
  `name` VARCHAR(30) DEFAULT NULL,
  `tid` INT(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fktid` (`tid`),
  CONSTRAINT `fktid` FOREIGN KEY (`tid`) REFERENCES `teacher` (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('1', '小明', '1'); 
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('2', '小红', '1'); 
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('3', '小张', '1'); 
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('4', '小李', '1'); 
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('5', '小王', '1');

2. 搭建代码运行环境
   ① 实体类（此处省略了get/set/toString的代码，自己练习时需补充）

public class Student {
    private int id;
    private String name;
    // 学生关联老师，则需要Teacher的对象
    private Teacher teacher;
}

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

②StudentMapper接口

public interface StudentMapper {
    List<Student> getStudents();
}

③MybatisUtils工具类

public class MybatisUtils {
    private static SqlSessionFactory sqlSessionFactory;
    static {
        try {
            String resource = "mybatis-config.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    // 获得处理sql语句的对象
    public static SqlSession getSqlSession(){
        return sqlSessionFactory.openSession();
    }
}

④mybatis-config.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>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&amp;characterEncoding=utf8&amp;useSSl=true"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
		<mapper class="com.ali.mapper.StudentMapper"></mapper>
    </mappers>

</configuration>

⑤ 接口的实现StudentMapper.xml
有两种方法用于实现：

• 基于子查询的：

SELECT student.id, student.name, student.tid
FROM student
WHERE student.tid = ( SELECT teacher.id FROM teacher )

• 基于结果查询的：

SELECT s.id,s.name,s.tid,t.name  FROM student AS s,teacher AS t
WHERE t.id = s.tid

下面程序中使用的两种方法均基于上面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接口 -->
<mapper namespace="com.ali.mapper.StudentMapper">

<!--    1.按照查询嵌套处理->子查询 -->
    <select id="getStudents" resultMap="studentMap" resultType="com.ali.pojo.Student">
        select * from mybatis.student
    </select>
    <resultMap id="studentMap" type="com.ali.pojo.Student">
        <result property="id" column="id"></result>
        <result property="name" column="name"></result>
        <association property="teacher" column="tid" javaType="com.ali.pojo.Teacher" select="getTeachers"></association>
    </resultMap>
    <select id="getTeachers" resultType="com.ali.pojo.Teacher">
        select * from mybatis.teacher where id = #{id}
    </select>

<!--    2.按照结果查询->嵌套查询-->
    <select id="getStudents" resultMap="studentMapper">
        select s.id sid, s.name sname, t.id tid, t.name tname
        from mybatis.student s,mybatis.teacher t
        where s.tid = t.id;
    </select>
    <resultMap id="studentMapper" type="com.ali.pojo.Student">
        <result property="id" column="sid"></result>
        <result property="name" column="sname"></result>
        <result property="tid" column="stid"></result>
        <association property="teacher" javaType="com.ali.pojo.Teacher">
            <result property="id" column="tid"></result>
            <result property="name" column="tname"></result>
        </association>
    </resultMap>
</mapper>

⑥测试

public class UserMapperTest {
    @Test
    public void test () {
        // 1.获取执行sal的对象sqlSession
        SqlSession sqlSession = MybatisUtils.getSqlSession();

        // 2.获取并执行sql语句 方法：getMapper
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        mapper.getStudents()
        for (Student student : students) {
            System.out.println(student);
        }
        // 3.关闭sqlSession连接
        sqlSession.close();
    }
}

二、一对多关系 —— collection

下面将以代码的形式实现依据老师的id查询该老师对应的所有学生的信息。（省略与标题一中重复的代码）
① 实体类

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

public class Teacher {
    private int id;
    private String name;
    // 一名老师有多个学生，用集合表示
    private List<Student> students;
}    

②接口TeachserMapper

public interface TeacherMapper {
    List<Teacher> getTeacher(int id);
}

③接口的实现TeacherMapper.xml
使用两种不同的方法进行实现：

• 基于子查询：
  思路为分别查询两个表中的信息，以teacher,.id=student.id连接两表

SELECT * FROM teacher WHERE teacher.id = '程序传入的需查询的老师id'
SELECT * FROM student WHERE student.tid = teacher.id

• 基于结果查询：

SELECT s.id,s.name,s.tid,t.name  FROM student AS s,teacher AS t
WHERE t.id = s.tid AND t.id  = '程序传入的需查询的老师id'

具体代码实现如下：

<?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接口 -->
<mapper namespace="com.ali.mapper.TeacherMapper">

<!--    1.基于子查询实现-->
    <select id="getTeacher1" resultMap="teacherMap">
        select * from mybatis.teacher
        where mybatis.teacher.id = #{id}
    </select>
    <resultMap id="teacherMap" type="com.ali.pojo.Teacher">
        <result property="name" column="name"></result>
        <collection property="students" column="id" javaType="ArrayList" ofType="com.ali.pojo.Student" select="studentMap">
        </collection>
    </resultMap>
    <select id="studentMap" resultType="com.ali.pojo.Student">
        select * from mybatis.student where tid = #{id};
    </select>
    
<!--    2. 基于结果查询实现-->
    <select id="getTeacher" resultMap="teacherInfo">
        select s.id,s.name,t.id tid,t.name
        from mybatis.student s, mybatis.teacher t
        where t.id = s.tid and t.id = #{id}
    </select>
    <resultMap id="teacherInfo" type="com.ali.pojo.Teacher">
        <result property="id" column="id"></result>
        <result property="name" column="name"></result>
        <collection property="students" ofType="com.ali.pojo.Student">
            <result property="id" column="id"></result>
            <result property="name" column="name"></result>
            <result property="tid" column="tid"></result>
        </collection>
    </resultMap>
</mapper>