MyBatis学习（四）

1.多对一查询处理

多对一的理解：

• 多个学生对应一个老师
• 对于学生，就是多对一的现象，即从学生这边关联一个老师！

实现步骤
1.搭建环境：创建数据库

use mybatis;

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.编写数据库对应的实体类

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

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

3.实体类对应的接口

//多对一查询
public interface StudentMapper {
    //查询学生的全部信息，以及老师的信息。根据结果嵌套处理
    List<Student> getStudentList();

    //查询学生的全部信息，以及老师的信息。 根据查询嵌套处理
    List<Student> getStudentList2();
}

4.编写Mapper接口对应的mapper.xml配置文件

<?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.chen.Dao.StudentMapper">
    <!--
        1.查询全部学生的信息
        2.查询老师信息
    -->
    <!--根据结果嵌套处理-->
    <select id="getStudentList" resultMap="StudentList">
        select stu.id as stuId,stu.name as stuName,tea.id as teaId,tea.name as teaName
        from student as stu,teacher as tea
        where stu.tid=tea.id;
    </select>

    <!--association:一个复杂类型的关联；许多结果将包装成这种类型(多对一是关联)
        嵌套结果映射 – 关联可以是 resultMap 元素，或是对其它结果映射的引用-->
    <resultMap id="StudentList" type="student">
        <result property="id" column="stuId"/>
        <result property="name" column="stuName"/>
        <association property="teacher" javaType="Teacher">
            <result property="id" column="teaId"/>
            <result property="name" column="teaName"/>
        </association>
    </resultMap>
    <!--=====================================================-->
    <!--
        1.查询全部学生的信息
        2.查询老师信息
    -->
    <!--根据查询嵌套处理-->
    <select id="getStudentList2" resultMap="studentAndteacher">
        select * from mybatis.student;
    </select>

    <resultMap id="studentAndteacher" type="student">
        <result property="id" column="id"/>
        <result property="name" column="name"/>
        <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
    </resultMap>

    <select id="getTeacher" resultType="teacher">
        select * from mybatis.teacher;
    </select>
</mapper>

5.测试

    @Test
    public void getStudentListTest(){
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        List<Student> studentList = mapper.getStudentList();
        for (Student student : studentList) {
            System.out.println(student);
        }

        sqlSession.close();
    }

    @Test
    public void getStudentList2Test(){
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        List<Student> studentList = mapper.getStudentList2();
        for (Student student : studentList) {
            System.out.println(student);
        }

        sqlSession.close();
    }

小结

按照查询进行嵌套处理就像SQL中的子查询

按照结果进行嵌套处理就像SQL中的联表查询

2.一对多查询处理

一对多理解：

• 一个老师拥有多个学生
• 对于老师，就是一个一对多的现象，即一个老师拥有一群学生（集合）
  1.创建数据库（如上1）
  2.创建实体类

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student2 {

    private  int id;
    private String name;
    private int tid;
}

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Teacher2 {
    private int id;
    private String name;
    private List<Student2> student2;
}

3.实体类对应的接口

//一对多查询
public interface TeacherMapper2 {

    //查询指定老师和学生信息  <!--根据结果嵌套处理-->
    Teacher2 getTeacher(@Param("id") int id);
}

4.编写Mapper接口对应的mapper.xml配置文件

<?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.chen.Dao2.TeacherMapper2">

    <!--连表查询-->
    <select id="getTeacher" resultMap="teacherAndstudent2">
        select stu.id as stuId,stu.name as stuName,tea.id as teaId,tea.name as teaName
        from student as stu,teacher as tea
        where stu.tid=tea.id and tea.id=#{id};
    </select>

    <resultMap id="teacherAndstudent2" type="Teacher2">
        <result property="id" column="teaId"/>
        <result property="name" column="teaName"/>
        <!--对于复杂属性：我们单独处理   association:对象   collection：集合-->
        <collection property="student2" ofType="Student2">
            <result property="id" column="stuId"/>
            <result property="name" column="stuName"/>
            <result property="tid" column="teaId"/>
        </collection>
    </resultMap>
</mapper>

5.测试

    @Test
    public void getTeacherTest(){
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        TeacherMapper2 mapper = sqlSession.getMapper(TeacherMapper2.class);
        Teacher2 teacher = mapper.getTeacher(1);
        System.out.println(teacher);
        sqlSession.close();
    }

小结

1、关联-association

2、集合-collection

3、所以association是用于一对一和多对一，而collection是用于一对多的关系

4、JavaType和ofType都是用来指定对象类型的

JavaType是用来指定pojo中属性的类型

ofType指定的是映射到list集合属性中pojo的类型。

注意说明：

1、保证SQL的可读性，尽量通俗易懂

2、根据实际要求，尽量编写性能更高的SQL语句

3、注意属性名和字段不一致的问题

4、注意一对多和多对一 中：字段和属性对应的问题

5、尽量使用Log4j，通过日志来查看自己的错误