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, 'Dr.Lee'); 

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', 'Alice', '1'); 
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('2', 'Bob', '1'); 
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('3', 'Curry', '1'); 
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('4', 'David', '1'); 
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('5', 'Ellen', '1');

---

01 多对一处理

什么是多对一？

举例说明，数据库的学生表中的多个学生对应一个老师

---

在IDEA中跟据数据表和测试所需环境（多对一）创建对应的实体类

com.hooi.pojo.Student

package com.hooi.pojo;

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

    public Student() {
    }

    public Student(int id, String name, Teacher teacher) {
        this.id = id;
        this.name = name;
        this.teacher = teacher;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Teacher getTeacher() {
        return teacher;
    }

    public void setTeacher(Teacher teacher) {
        this.teacher = teacher;
    }

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", teacherName=" + teacher.getName() +
                '}';
    }
}

com.hooi.pojo.Teacher

package com.hooi.pojo;

public class Teacher {

    private int id;
    private String name;

    public Teacher() {
    }

    public Teacher(int id, String name) {
        this.id = id;
        this.name = name;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
}

测试:关键字association

StudentMapper接口：

package com.hooi.dao;

import com.hooi.pojo.Student;

import java.util.List;

public interface StudentMapper {

    //查询学生的所有信息 方式1
    public abstract List<Student> getStudents();

    //查询学生的所有信息 方式2
    public abstract List<Student> getStuents2();
}

对应的Mapper映射文件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.hooi.dao.StudentMapper">
    <!--方式1 嵌套查询语句-->
    <select id="getStudents" resultMap="S-T1">
        select * from mybatis.student
    </select>
    <!--映射结果集-->
    <resultMap id="S-T1" type="student">
        <id column="id" property="id"/>
        <result column="name" property="name" />
        <!--关联数据库Teacher表-->
        <association column="tid" property="teacher" javaType="teacher" select="getTeacher"/>
    </resultMap>
    <!--查询Teacher表中的t.name-->
    <select id="getTeacher" resultType="teacher">
        select t.name from mybatis.teacher as t where id=#{id}
    </select>

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

    <!--方式2 直接关联查询结果-->

    <select id="getStudents2" resultMap="S-T2">
        select s.id , s.name , t.name as tName from mybatis.student as s , mybatis.teacher as t
        where s.tid = t.id
    </select>

    <resultMap id="S-T2" type="student">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <!--直接关联Teacher-->
        <association property="teacher" javaType="teacher" >
            <!--关联结果-->
            <result property="name" column="tName"/>
        </association>
    </resultMap>
</mapper>

Mybatis配置文件

 <mappers>
     <mapper resource="com/hooi/dao/studentMapper.xml"/>
 </mappers>

Junit测试代码：

package com.hooi.dao;

import com.hooi.pojo.Student;
import com.hooi.utils.MybatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;


import java.util.List;

public class StudentMapperTest {
    @Test
    public void getStudents(){
        SqlSession session = MybatisUtil.getSqlSession();
        StudentMapper mapper = session.getMapper(StudentMapper.class);
        List<Student> students = mapper.getStudents();
        for (Student student : students) {
            System.out.println(student);
        }
        session.close();
    }

    @Test
    public void getStudents2(){
        SqlSession session = MybatisUtil.getSqlSession();
        StudentMapper mapper = session.getMapper(StudentMapper.class);
        List<Student> students = mapper.getStudents2();
        for (Student student : students) {
            System.out.println(student);
        }
        session.close();
    }
}

测试结果：

1. getStudents()测试结果

2. getStudents2()测试结果

---

02 一对多处理

什么是一对多？

举例说明，一个老师对应多个学生

---

在IDEA中跟据数据表和测试所需环境（一对多）创建对应的实体类

com.hooi.pojo.Student

package com.hooi.pojo;

public class Student {
    private int id;
    private String name;
    private int tid;
    
    public Student() {
    }

    public Student(int id, String name, int tid) {
        this.id = id;
        this.name = name;
        this.tid = tid;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getTid() {
        return tid;
    }

    public void setTid(int tid) {
        this.tid = tid;
    }

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", name='" + name + '}';
    }
}

com.hooi.pojo.Teacher

package com.hooi.pojo;

import java.util.List;

public class Teacher {

    private int id;
    private String name;
    private List<Student> students;

    public Teacher() {
    }

    public Teacher(int id, String name, List<Student> students) {
        this.id = id;
        this.name = name;
        this.students = students;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public List<Student> getStudents() {
        return students;
    }

    public void setStudents(List<Student> students) {
        this.students = students;
    }

}

测试:关键字collection

TeacherMapper接口：

package com.hooi.dao;

import com.hooi.pojo.Teacher;

public interface TeacherMapper {
    //根据ID查询老师的信息 方式1
    public abstract Teacher getTeacher(int id);
    //根据ID查询老师的信息 方式2
    public abstract Teacher getTeacher2(int id);
}

对应的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.hooi.dao.TeacherMapper">
    <!--方式1 关联结果集-->
    <select id="getTeacher" resultMap="T-S">
        select t.name tname, s.id sid, s.name sname
        from mybatis.teacher t, mybatis.student s
        where t.id = s.tid and t.id =#{id}
    </select>

    <resultMap id="T-S" type="teacher">
        <result property="name" column="tname"/>
        <collection property="students" ofType="Student">
            <id column="sid" property="id"/>
            <result column="sname" property="name"/>
        </collection>
    </resultMap>
    
    <!--=========================================================================-->

    <!--方式2 关联数据库-->

    <select id="getTeacher2" resultMap="T-S2">
        select * from mybatis.teacher
    </select>

    <resultMap id="T-S2" type="teacher">
        <result property="name" column="name"/>
        <collection column="id" property="students" javaType="ArrayList" ofType="student" select="getStudents"/>
    </resultMap>

    <select id="getStudents" resultType="student">
        select * from mybatis.student where tid = #{id}
    </select>
</mapper>

Mybatis配置文件

 <mappers>
     <mapper resource="com/hooi/dao/teacherMapper.xml"/>
 </mappers>

Junit测试代码：

package com.hooi.dao;

import com.hooi.pojo.Teacher;
import com.hooi.utils.MybatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

public class TeacherMapperTest {
    @Test
    public void getTeacher(){
        SqlSession session = MybatisUtil.getSqlSession();
        TeacherMapper mapper = session.getMapper(TeacherMapper.class);
        Teacher teacher = mapper.getTeacher(1);

        System.out.println(teacher.getName());
        System.out.println(teacher.getStudents());

        session.close();
    }

    @Test
    public void getTeacher2(){
        SqlSession session = MybatisUtil.getSqlSession();
        TeacherMapper mapper = session.getMapper(TeacherMapper.class);
        Teacher teacher = mapper.getTeacher2(1);

        System.out.println(teacher.getName());
        System.out.println(teacher.getStudents());

        session.close();
    }
}

测试结果：

1. getTeacher()测试结果

2. getTeacher2()测试结果

---