10.多对一的处理

10.多对一的处理

• 多个学生，对应一个老师
• 对与学生而言，关联——多个学生，关联一个老师【多对一】
• 对老师而言，集合——一个老师有很多学生【一对多】

  10.1创建数据库表（student teacher）

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

  10.2基本环境的搭建

 

 

10.2.1.实体类(@Data是使用lombok插件简化了实体类)

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

10.2.2.dao层

public interface TeacherMapper {
		//利用注解实现接口的查询
    @Select("select * from teacher where id = #{id}")
    Teacher getTeacher(@Param("id") int id);
}

10.2.3.TeacherMapper.xml

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

<!--绑定接口-->
<mapper namespace="com.itxiaofei.dao.TeacherMapper">
</mapper>

10.2.4.测试

public class Test {
    @org.junit.Test
    public void getTeacher(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
        Teacher teacher = mapper.getTeacher(1);
        System.out.println(teacher);
        sqlSession.close();
    }
}

问题疑惑：

1.增加@Tset注解出现@org.junit.Test,是因为与测试名称同名，不影响使用

2.如何修改*Mapper.xml的头文件

 

10.3按照查询嵌套处理（多对一）

10.3.1.dao

public interface StudentMapper {
    //查询所有学生信息，以及对应的老师信息
    public List<Student> getStudent();
}

10.3.2.实体类（Student and Teacher）

@Data
public class Student {
    public int id;
    public String name;
    //学生需要关联一个老师
    public Teacher teacher;
}

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

10.3.3.MybatisUtils

//sqlSessionFactory（工厂构建）——>sqlSession
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();
       }
   }
    public static SqlSession getSqlSession(){
        //设置为true以后都不用手动设置commit（提交事务）了
        return sqlSessionFactory.openSession(true);
    }
}

10.3.4.StudentMapper.xml

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

<mapper namespace="com.itxiaofei.dao.StudentMapper">
    <!--
    思路：
    1.查询所有学生的信息
    2.根据查询出来的学生信息的tid，寻找出对应的老师信息
    -->

    <select id="getStudent" resultMap="StudentTeacher">
        select * from student
    </select>
    <resultMap id="StudentTeacher" type="Student">
        <!--简单的属性 property：实体类的属性 column：数据库中的字段-->
        <result property="id" column="id"/>
        <result property="name" column="name"/>
        <!--复杂的属性，我们需要单独处理
        对象使用：association
        集合使用：collection
        -->
        <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
    </resultMap>

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

</mapper>

10.3.5.核心配置文件（mybatis-config）

<!--接口的注册-->
    <mappers>
        <mapper class="com.itxiaofei.dao.TeacherMapper"/>
        <mapper class="com.itxiaofei.dao.StudentMapper"/>
    </mappers>

10.3.6.测试

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

10.4.按照结果嵌套处理

10.4.1.StudentMapper

public List<Student> getStudent2();

10.4.2.StudentMapper.xml

<!--按照结果嵌套处理-->
    <select id="getStudent2" resultMap="StudentTeacher2">
        select s.id sid,s.name sname,t.name tname
        from Student s,teacher t
        where s.tid = t.id;
    </select>

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

10.4.3.测试

@Test
    public void getStudent2(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        List<Student> studentList2 = mapper.getStudent2();
        for (Student student : studentList2) {
            System.out.println(student);
        }
        sqlSession.close();
    }

回顾Mybatis多对一查询方式：

• 子查询
• 联表查询