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

实体类

package com.sin.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

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

package com.sin.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class Student {
    private int id;
    private String name;
    //多个学生可以是同一个老师，即多对一
    private Teacher teacher;
}

Mapper层

public interface StudentMapper {}

public interface TeacherMapper {}

StudentMapper.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.sin.mapper.StudentMapper">
</mapper>

TeacherMapper.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.sin.mapper.TeacherMapper">
</mapper>

StudentMapper.java

//获取所有学生及对应老师的信息
public List<Student> getStudents();

StudentMapper.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.sin.mapper.StudentMapper">

    <!--
       需求：获取所有学生及对应老师的信息.
       1. 获取所有学生的信息
       2. 根据获取的学生信息的老师ID，再查询老师表，获取该老师的信息
       3. 使用association 做 一个复杂类型的关联；使用它来处理关联查询
   -->
    <resultMap id="StudentTeacher" type="com.sin.pojo.Student">
        <!--association关联属性  property属性名 javaType属性类型 column在多的一方的表中的列名-->
        <!-- 多参数传法：column="{key=value,key=value}" -->
        <association property="teacher"  column="tid" javaType="com.sin.pojo.Teacher" select="getTeacher"/>
    </resultMap>
    <!-- 用于查询学生的方法，resultMap：找到resultMap的id="StudentTeacher"映射 -->
    <select id="getStudents" resultMap="StudentTeacher">
      select * from student
    </select>
    <!-- 用于查询老师的方法，resultType：返回类型 -->
    <select id="getTeacher" resultType="com.sin.pojo.Teacher">
        select * from teacher where id = #{tid}
    </select>
</mapper>

Mybatis-config.xml

注册Mapper

<mappers>
    <mapper resource="mapper/StudentMapper.xml"/>
</mappers>

测试

  @Test
    public void testGetStudents(){
        SqlSession session = MyBatisUtil.getSession();
        StudentMapper mapper = session.getMapper(StudentMapper.class);
        List<Student> students = mapper.getStudents();
        for (Student student: students){
            System.out.println("学生名:"+ student.getName()+",老师:"+student.getTeacher().getName());
        }
    }

测试结果

在这里插入图片描述

关联查询

上面的方法，是查询完学生，再一个一个查询老师，感觉性能会有点慢。

除了上面这种方式，还可以多表查询，一次查询出来。

StudentMapper.java

public List<Student> getStudents2();

StudentMapper.xml

 <!-- 结果封装 -->
    <resultMap id="StudentTeacher2" type="com.sin.pojo.Student">
        <id property="id" column="sid"/>
        <result property="name" column="sname"/>
        <!--property： 关联对象在Student实体类中的属性名-->
        <association property="teacher" javaType="com.sin.pojo.Teacher">
            <!-- 字段映射 -->
            <result property="name" column="tname"/>
        </association>
    </resultMap>
    <!-- 多表查询 -->
    <select id="getStudents2" resultMap="StudentTeacher2" >
    select s.id sid, s.name sname , t.name tname
    from student s,teacher t
    where s.tid = t.id
</select>

测试

@Test
public void testGetStudents2(){
    SqlSession session = MyBatisUtil.getSession();
    StudentMapper mapper = session.getMapper(StudentMapper.class);
    List<Student> students = mapper.getStudents2();
    for (Student student : students){
        System.out.println("学生名:"+ student.getName()+",老师:"+student.getTeacher().getName());
    }
}