MyBatis-05-处理一对多和多对一

八、处理多对一关系

什么是多对一关系？
- 以公司部门和员工举例：一个公司部门下面可以有很多员工，但是一个员工只能属于一个部门
  - 从部门角度来看，一个部门下可以有多个员工，这就是一对多的关系
  - 从员工角度来看，多个员工均可以为同一个部门工作，这就是多对一的关系

8.1 构造测试环境

数据库设计

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, '秦老师');
INSERT INTO teacher(`id`, `name`) VALUES (2, '许老师');

CREATE TABLE `student` (
`id` INT(10) NOT NULL,
`name` VARCHAR(30) DEFAULT NULL,
`tid` INT(10) DEFAULT NULL,
PRIMARY KEY (`id`),
) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('1', '小明1', '1');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('2', '小红1', '1');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('3', '小张1', '1');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('4', '小李1', '1');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('5', '小王1', '1');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('6', '小明2', '2');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('7', '小红2', '2');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('8', '小张2', '2');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('9', '小李2', '2');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('10', '小王2', '2');

实体类设计

Teacher实体类

package com.pbx.pojo;

/**
 * @author BruceXu
 * @date 2020/11/4
 */
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;
    }

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

Student实体类

package com.pbx.pojo;

/**
 * @author BruceXu
 * @date 2020/11/4
 */
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 + '\'' +
                ", teacher=" + teacher +
                '}';
    }
}

mapper接口

TeacherMapper.java

package com.pbx.mapper;

import com.pbx.pojo.Teacher;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * @author BruceXu
 * @date 2020/11/4
 */
public interface TeacherMapper {

    @Select("select * from teacher")
    List<Teacher> getTeacher();
    @Select("select * from teacher where id = #{tid}")
    Teacher getTeacherById(@Param("tid") int id);

}

StudentMapper.java

package com.pbx.mapper;

import com.pbx.pojo.Student;

import java.util.List;

/**
 * @author BruceXu
 * @date 2020/11/4
 */
public interface StudentMapper {
    List<Student> getStudent();
}

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.pbx.mapper.StudentMapper">
    <select id="getStudent" resultType="Student">
        select * from student
    </select>
</mapper>

mybatis-config.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <properties resource="db.properties">
        <property name="password" value="111111"/>
    </properties>
    <typeAliases>
        <typeAlias type="com.pbx.pojo.Student" alias="Student"/>
        <typeAlias type="com.pbx.pojo.Teacher" alias="Teacher"/>
    </typeAliases>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${user}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <package name="com.pbx.mapper"/>
    </mappers>
</configuration>

注意：Student实体类的设计
- 假设有这么一个需求，我们在查询学生的时候，需要输出对应老师的姓名。那么我们在student实体类的设计时，就要设计成Student类中包含一个Teacher对象。在代码上使用这个解决方案，避免在数据库中使用外键进行对应。

8.2 尝试查询老师信息和学生信息

尝试SQL查询

SELECT s.id, s.name, t.name 
from student s, teacher t
where s.tid = t.id;
/*
+----+-------+--------+
| id | name  | name   |
+----+-------+--------+
|  1 | 小明1 | 秦老师 |
|  2 | 小红1 | 秦老师 |
|  3 | 小张1 | 秦老师 |
|  4 | 小李1 | 秦老师 |
|  5 | 小王1 | 秦老师 |
|  6 | 小明2 | 许老师 |
|  7 | 小红2 | 许老师 |
|  8 | 小张2 | 许老师 |
|  9 | 小李2 | 许老师 |
| 10 | 小王2 | 许老师 |
+----+-------+--------+
*/

代码

import com.pbx.mapper.StudentMapper;
import com.pbx.mapper.TeacherMapper;
import com.pbx.pojo.Student;
import com.pbx.pojo.Teacher;
import com.pbx.utils.MyBatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.List;

/**
 * @author BruceXu
 * @date 2020/11/4
 */
public class MapperTest {
    @Test
    public void getTeacher() {
        System.out.println("getTeacher():");
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
        List<Teacher> teachers = mapper.getTeacher();
        for (Teacher teacher : teachers) {
            System.out.println(teacher);
        }
        Teacher teacher1 = mapper.getTeacherById(1);
        System.out.println(teacher1);
        Teacher teacher2 = mapper.getTeacherById(2);
        System.out.println(teacher2);
        System.out.println("===================");
        sqlSession.close();
    }

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

}

结果
问题：
- 老师信息可以正确查询，但是学生中的老师信息无法正确查询
- 因为数据库的字段名没有和实体类中的属性进行映射

8.3 进行查询嵌套处理

修改StudentMapper.xml

<mapper namespace="com.pbx.mapper.StudentMapper">
    <select id="getStudent" resultMap="getStudent1">
        select * from student
    </select>

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

    <resultMap id="getStudent1" type="Student">
        <result property="id" column="id" />
        <result property="name" column="name" />
        <association property="teacher" column="tid" javaType="Teacher" select="getTeacher" />
    </resultMap>
</mapper>

说明
- property代表实体类中的属性名，column代表数据库中的字段名
- 使用查询嵌套处理时，javaType要为子查询语句中对应的实体类类型

测试

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

8.4 进行结果嵌套处理

修改StudentMapper.xml文件如下

<mapper>
	<select id="getStudent2" resultMap="getStudent2">
        select s.id sid, s.name sname , t.name tname
        from student s,teacher t
        where s.tid = t.id
    </select>
    <resultMap id="getStudent2" type="Student">
        <result property="id" column="sid"/>
        <result property="name" column="sname" />
        <association property="teacher" javaType="Teacher">
            <result property="name" column="tname" />
        </association>
    </resultMap>
</mapper>

说明
- 如果实体类中存在其他实体类的引用时，在编写resultMap时，需要使用association标签进行关联

测试

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

这边查出来老师的id=0是因为没有映射teacher中的id。这里可以根据需求进行操作。如果需求需要显示出老师的id，那么只需要加一条映射即可
- 注意，映射配置中的column必须是select中查出来的列名，不然还是会显示id为0

错误示例

mapper.xml

<mapper>
    <select id="getStudent2" resultMap="getStudent2">
        select s.id sid, s.name sname , t.name tname
        from student s,teacher t
        where s.tid = t.id
    </select>
    <resultMap id="getStudent2" 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>
</mapper>

运行结果

正确示例

mapper.xml

<mapper>
    <select id="getStudent2" resultMap="getStudent2">
        select s.id sid, s.name sname , t.name tname, s.tid stid
        from student s,teacher t
        where s.tid = t.id
    </select>
    <resultMap id="getStudent2" type="Student">
        <result property="id" column="sid"/>
        <result property="name" column="sname" />
        <association property="teacher" javaType="Teacher">
            <result property="id" column="stid" />
            <result property="name" column="tname" />
        </association>
    </resultMap>
</mapper>

运行结果

8.5 总结

查询嵌套类似于SQL中的嵌套查询
结果嵌套类似于SQL中的连表查询
若实体类中存在其他实体类对象的引用，需要使用association标签进行映射处理
- property —— 实体类中属性名
- JavaType —— 实体类中所引用其他实体类的对象类型

九、处理一对多关系

9.1 构造测试环境

与8.1中的环境一样，唯一不同点在于实体类的设计

Teacher.java

public class Teacher {
    private int id;
    private String name;
    private List<Student> studentList;
}

Student.java

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

9.2 尝试查询

测试

import com.pbx.mapper.StudentMapper;
import com.pbx.mapper.TeacherMapper;
import com.pbx.pojo.Student;
import com.pbx.pojo.Teacher;
import com.pbx.utils.MyBatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.List;

/**
 * @author BruceXu
 * @date 2020/11/5
 */
public class TestMapper {

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

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

        session.close();
    }
}

结果
- 同样的可以看出，在没有手动映射的时候，查询老师是查不出学生信息的

9.3 查询嵌套处理

mapper.xml

<mapper namespace="com.pbx.mapper.TeacherMapper">
    <select id="getTeacher" resultMap="getTeacher1">
        select * from teacher
    </select>
    <resultMap id="getTeacher1" type="Teacher">
        <collection property="studentList" javaType="ArrayList" ofType="Student" column="id" select="getStudent"/>
    </resultMap>
    <select id="getStudent" resultType="Student">
        select * from student where tid = #{id}
    </select>
</mapper>

测试

9.4 结果嵌套处理

mapper.xml

<mapper>
    <select id="getTeacher2" resultMap="getTeacher2">
        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="getTeacher2" type="Teacher">
        <result property="id" column="tid"/>
        <result property="name" column="tname"/>
        <collection property="studentList" ofType="Student">
            <result property="id" column="sid"/>
            <result property="name" column="sname"/>
            <result property="tid" column="tid"/>
        </collection>
    </resultMap>
</mapper>

测试

posted @ 2020-11-06 16:16 PrimaBruceXu 阅读(73) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

PrimaBruceXu——多看，多记，多做，多练

MyBatis-05-处理一对多和多对一

八、处理多对一关系

8.1 构造测试环境

8.2 尝试查询老师信息和学生信息

8.3 进行查询嵌套处理

8.4 进行结果嵌套处理

8.5 总结

九、处理一对多关系

9.1 构造测试环境

9.2 尝试查询

9.3 查询嵌套处理

9.4 结果嵌套处理

公告