Mybatis学习笔记（四）：多对一（association ）、一对多处理（collection)

10、多对一的处理

• 多个学生，对应一个老师
• 对于学生来说，关联…多个学生，关联一个老师【多对一】
• 对于老师而言，集合… 一个老师，有多个学生【一对多】

搭建环境：

建立一张老师表、一张学生表

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

测试环境搭建

工具类MybatisUtils

public class MybatisUtils {
    private static SqlSessionFactory sqlSessionFactory;

    static {
        try {
            //使用mybatis第一步，获取sqlSessionFactory对象
            String resource = "mybatis-config.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    //既然有了 SqlSessionFactory，我们可以从中获得 SqlSession 的实例。SqlSession 提供了在数据库执行 SQL 命令所需的所有方法。
    public static SqlSession getSqlSession(){
        //设置为true，自动提交事务
        return sqlSessionFactory.openSession(true);
    }

}

1.导入lombok

<dependencies>
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <version>1.18.20</version>
        <scope>provided</scope>
    </dependency>
</dependencies>

2.新建实体类 Teacher、Student

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

@Data
public class Student {
    private int id;
    private String name;
    private Teacher teacher;
}

3.新建Mapper接口

public interface StudentMapper {
    //查询所有学生，并且显示其对应的老师的名字
    List<Student> getStudent();

    List<Student> getStudent2();
}

public interface TeacherMapper {
    @Select("select * from teacher where id=#{tid}")
    Teacher getTeacher(@Param("tid") int tid);
}

4.建立Mapper.xml文件

StudentMapper.xml

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

<mapper namespace="com.yong.dao.StudentMapper">

    <select id="getStudent2" resultMap="StudentAndTeacher2">
        select s.id sid,s.name sname,t.name tname
        from student s 
        join teacher t 
        on s.tid=t.id
    </select>
    
    <resultMap id="StudentAndTeacher2" type="Student">
        <result property="id" column="sid"/>
        <result property="name" column="sname"/>
        <association property="teacher" javaType="Teacher">
            <result property="name" column="tname"/>
        </association>
    </resultMap>


    <!--==============================-->

    <select id="getStudent" resultMap="StudentAndTeacher">
        select * from student
    </select>

    <resultMap id="StudentAndTeacher" type="Student">
        <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
    </resultMap>

    <select id="getTeacher" resultType="Teacher">
        select * from teacher where id=#{id}
    </select>
</mapper>

TeacherMapper.xml暂时不用，可以创建空表

5.在核心配置文件中绑定注册我们的Mapper接口或者文件！【多种方式】

<mappers>
    <!--        将包内的映射器接口实现全部注册为映射器-->
    <!--        <package name="com.yong.dao"/>-->

    <!--        <mapper resource="com/yong/dao/TeacherMapper.xml"/>-->
    <!--        <mapper resource="com/yong/dao/StudentMapper.xml"/>-->
    <mapper class="com.yong.dao.TeacherMapper"/>
    <mapper class="com.yong.dao.StudentMapper"/>
</mappers>

6.测试查询是否成功

在sql中多表查询可以直接用sql进行表连接查出来，但是在mybatis中不行，因为mybatis中的查询结果

是一个对象。

按照查询嵌套处理

解决思路：

​ 1.查询所有学生

​ 2.根据查询出来的学生的tid，寻找对应的老师！

​ 3.使用resultMap，结果集映射。

​ 4.在resultMap中，association：对象，collection：集合

​ 5.嵌套查询语句(子查询)。javaType：对象的类型，select嵌套的标签

<select id="getStudent" resultMap="StudentAndTeacher">
    select * from student
</select>

<resultMap id="StudentAndTeacher" type="Student">
    <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
</resultMap>

<select id="getTeacher" resultType="Teacher">
    select * from teacher where id=#{tid}
</select>

按照结果嵌套处理

<select id="getStudent2" resultMap="StudentAndTeacher2">
    select s.id sid,s.name sname,t.name tname
    from student s 
    join teacher t 
    on s.tid=t.id
</select>

<resultMap id="StudentAndTeacher2" type="Student">
    <result property="id" column="sid"/>
    <result property="name" column="sname"/>
    <association property="teacher" javaType="Teacher">
        <result property="name" column="tname"/>
    </association>
</resultMap>

11、一对多

比如：一个老师有多个学生，那么对于老师而言，就是一对多的关系

复杂的类型 我们需要单独处理

环境搭建

和上面的一样

实体类

@Data
public class Teacher {
    private int id;
    private String name;

    //一个老师有多个学生
    private List<Student> students;
}

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

接口

public interface TeacherMapper {
    //获取老师
    List<Teacher> getTeachers();

    //根据id获取老师和他的学生
    List<Teacher> getTeacher(@Param("tid") int id);

    //根据id获取老师和他的学生
    List<Teacher> getTeacher2(@Param("tid") int id);

}

TeacherMapper

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

<mapper namespace="com.yong.dao.TeacherMapper">

    <select id="getTeachers" resultType="Teacher">
        select * from teacher;
    </select>

    <select id="getTeacher" resultMap="TeacherAndStudent">
        select s.id sid,s.name sname,t.name tname,t.id tid
        from student s
        join teacher t
        on s.tid=t.id and t.id=#{tid}
    </select>
    <resultMap id="TeacherAndStudent" type="Teacher">
        <result property="id" column="tid"/>
        <result property="name" column="tname"/>
        <collection property="students" ofType="Student">
            <result property="id" column="sid"/>
            <result property="name" column="sname"/>
            <result property="tid" column="tid"/>
        </collection>

    </resultMap>

    <!--=====================================================-->
    <select id="getTeacher2" resultMap="TeacherAndStudent2">
        select * from teacher where id=#{tid};
    </select>
    
    <resultMap id="TeacherAndStudent2" type="Teacher">
        <collection property="students" column="id" javaType="ArrayList" ofType="Student" select="getStudentByTeacherId"/>
    </resultMap>
    
    <select id="getStudentByTeacherId" resultType="Student">
        select * from student where tid=#{tid};
    </select>
</mapper>

按照查询嵌套处理

<select id="getTeacher2" resultMap="TeacherAndStudent2">
    select * from teacher where id=#{tid};
</select>

<resultMap id="TeacherAndStudent2" type="Teacher">
    <collection property="students" column="id" javaType="ArrayList" ofType="Student" select="getStudentByTeacherId"/>
</resultMap>

<select id="getStudentByTeacherId" resultType="Student">
    select * from student where tid=#{tid};
</select>

按照结果嵌套处理

<select id="getTeacher" resultMap="TeacherAndStudent">
    select s.id sid,s.name sname,t.name tname,t.id tid
    from student s
    join teacher t
    on s.tid=t.id and t.id=#{tid}
</select>
<resultMap id="TeacherAndStudent" type="Teacher">
    <result property="id" column="tid"/>
    <result property="name" column="tname"/>
    <collection property="students" ofType="Student">
        <result property="id" column="sid"/>
        <result property="name" column="sname"/>
        <result property="tid" column="tid"/>
    </collection>

</resultMap>

小结

1.关联：association 【多对一】

2.集合：collection【一对多】

3.javaType ：指定实体类中属性的类型

4.ofType：用来指定映射到List或者集合中的pojo类型，泛型中的约束类型

注意点：

• 保证SQL的可读性，尽量保证通俗易懂
• 注意一对多和多对一中，属性名和字段的问题。
• 如果问题不好排查，可以使用日志，建议使用log4j