Mybatis学习笔记:class-06/07

10 多对一处理

多个学生，对应一个老师

对于学生而言，关联…多个学生关联一个老师【多对一】

对于老师而言，集合，一个老师，很学生【一对多】

10.1 测试环境搭建

​ 1.导入Lombok

​ 2.数据库新建teacher表和student表。并插入数据

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

​ 3.新建实体类Teacher，Student

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

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

​ 4.建立Mapper接口

//仅仅测试一个查询功能
public interface TeacherMapper {
    @Select("select * from teacher where id = #{tid}")
    Teacher getTeacher(@Param("tid") int id);
}

public interface StudentMapper {
}

​ 5.建立Mapper.xml文件,并在核心配置文件中绑定

<!--StudentMapper.xml-->
<?xml version="1.0" encoding="UTF8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.serene.dao.StudentMapper">

</mapper>

<!--TeacherMapper.xml-->
<?xml version="1.0" encoding="UTF8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.serene.dao.TeacherMapper">
    
</mapper>

<!--mybatis-config.xml-->
<mappers>
        <mapper class="com.serene.dao.TeacherMapper" />
        <mapper class="com.serene.dao.StudentMapper" />
</mappers>

​ 6.测试代码

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

​ 7.测试结果

10.2 连表查询处理

数据库中我们查询老师和学生的信息处理如下:

select s.id,s.name,t.name from student s,teacher t where s.tid = t.id

可以得到每个学生与老师的信息。在代码中我们应该如何处理？

代码中使用下面的可以看到查询出来的结果，老师信息为null：

//StudentMapper.java
public interface TeacherMapper {
    @Select("select * from teacher where id = #{tid}")
    Teacher getTeacher(@Param("tid") int id);
}
//StudentMapper.xml
<select id="getStudent" resultType="Student">
    select * from student
</select>
//Test.java
@Test
public void test1(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
    List<Student> studentList = mapper.getStudent();
    for (Student student : studentList) {
        System.out.println(student);
	}
}

1.按照查询嵌套处理：

<!--思路：
        1.查询所有的学生信息
        2.根据查询出来的学生的tid寻找特定的老师子查询)
      -->
<select id="getStudent" resultMap="ST">
    select * from student
</select>
<resultMap id="ST" type="Student">
    <result property="id" column="id"/>
    <result property="name" column="name"/>
    <!--复杂的属性，我们需要单独出来 对象：association 集合：collection-->
    <association property="teacher" column="tid" 
    	javaType="Teacher" select="getTeacher" />
</resultMap>

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

2.按照结果嵌套处理

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

<resultMap id="ST" type="student" >
    <result property="id" column="sid"/>
    <result property="name" column="sname"/>
    <association property="teacher" javaType="Teacher">
        <result property="id" column="tid"/>
        <result property="name" column="tname"/>
    </association>
</resultMap>

11 一对多

//一个老师对应多个学生，直接查询，学生结果为null
//接口类获取所有老师
List<Teacher> getTeacher();

<select id="getTeacher" resultType="Teacher">
	    select * from mybatis1.teacher
</select>

@Test
    public void test0(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
    List<Teacher> teacherList = mapper.getTeacher();
    for (Teacher teacher : teacherList) {
        System.out.println(teacher);
    }
} 

按照结果嵌套查询：

//实体类
public class Teacher {
    private int id;
    private String name;
    private List<Student> students;

}
//接口 获取指定老师下的所有学生老师信息
Teacher getTeacher1(@Param("tid") int id);

        <select id="getTeacher1" resultMap="TS">
            select s.id sid,s.name sname,t.name tname,t.id tid
            from student s,teacher t
            where s.tid=t.id and tid=#{tid}
        </select>
        <resultMap id="TS" 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>

public class DaoTest {
    @Test
    public void test1(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
        Teacher teacher = mapper.getTeacher1(1);
        System.out.println(teacher);
    }
}