8.jpg

Mybatis-高级结果映射(多对一、一对多查询处理)

如果这个世界总是这么简单就好了。
MyBatis 创建时的一个思想是:数据库不可能永远是你所想或所需的那个样子。 我们希望每个数据库都具备良好的第三范式或 BCNF 范式,可惜它们并不都是那样。 如果能有一种数据库映射模式,完美适配所有的应用程序,那就太好了,但可惜也没有。 而 ResultMap 就是 MyBatis 对这个问题的答案。

resultMap元素有很多子元素和一个值得深入探讨的结构。 下面是resultMap 元素的概念视图。
下面列举比较常用的结果集映射的标签和属性

常用标签

  • id :一个 ID 结果;标记出作为 ID 的结果可以帮助提高整体性能
  • result : 注入到字段或 JavaBean 属性的普通结果
  • association :一个复杂类型的关联;许多结果将包装成这种类型

    • 嵌套结果映射 – 关联可以是 resultMap 元素,或是对其它结果映射的引用
  • collection :一个复杂类型的集合

    • 嵌套结果映射 – 集合可以是 resultMap 元素,或是对其它结果映射的引用

常用属性

  • property:映射到列结果的字段或属性。如果 JavaBean 有这个名字的属性(property),会先使用该属性。否则
    MyBatis 将会寻找给定名称的字段(field)。 无论是哪一种情形,你都可以使用常见的点式分隔形式进行复杂属性导航。

比如,你可以这样映射一些简单的东西:“username”,或者映射到一些复杂的东西上:“address.street.number”。

  • column:数据库中的列名,或者是列的别名。一般情况下,这和传递给 resultSet.getString(columnName)
    方法的参数一样。
  • javaType:一个 Java 类的全限定名,或一个类型别名(关于内置的类型别名,可以参考上面的表格)。 如果你映射到一个
    JavaBean, MyBatis 通常可以推断类型。然而,如果你映射到的是 HashMap,那么你应该明确地指定 javaType

来保证行为与期望的相一致。

下面有两张相关联的Mysql表,思考如何通过结果集映射进行多对一查询

teacher

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

student

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;

通过teacherid作为外键关联到学生,多个学生对应一位老师

表中插入数据
teacher
在这里插入图片描述
student
在这里插入图片描述

实体类
teacher实体类

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实体类

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 +
                '}';
    }
}

查询所有学生的信息以及对应的老师

接口

public interface StudentMapper {
    public List<Student> selectAll();
}

mapper.xml
这里结果集映射有两种方法
方法一、写完整SQL,架构出结果集(速度快,阅读性好,不易维护)

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cn.kexing.mybatis.dao.StudentMapper">
    <select id="selectAll" resultMap="StudentTeacher">
        select s.id sid,s.`name` sname,t.id tid,t.`name` tname
        from student s LEFT JOIN teacher t
        ON t.id=s.tid
    </select>

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

javaType属性可不写,主要看如何规范
Mybatis得到mapper接口对象主要用的反射,所以有关mapper接口函数的信息可以省略

Test

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

结果

Student{id=1, name='小明', teacher=Teacher{id=1, name='秦老师'}}
Student{id=2, name='小红', teacher=Teacher{id=1, name='秦老师'}}
Student{id=3, name='小张', teacher=Teacher{id=1, name='秦老师'}}
Student{id=4, name='小李', teacher=Teacher{id=1, name='秦老师'}}
Student{id=5, name='小王', teacher=Teacher{id=1, name='秦老师'}}

方法二、通过类似子查询、SQL简单、映射较麻烦(可读性低、速度慢)

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cn.kexing.mybatis.dao.StudentMapper">
        <select id="selectAll" resultType="Student" resultMap="StudentTeacher">
            select * from student;
        </select>

        <resultMap id="StudentTeacher" type="Student">
            <association property="teacher" column="tid" select="getTeacher"></association>
        </resultMap>

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

结果

Student{id=1, name='小明', teacher=Teacher{id=1, name='秦老师'}}
Student{id=2, name='小红', teacher=Teacher{id=1, name='秦老师'}}
Student{id=3, name='小张', teacher=Teacher{id=1, name='秦老师'}}
Student{id=4, name='小李', teacher=Teacher{id=1, name='秦老师'}}
Student{id=5, name='小王', teacher=Teacher{id=1, name='秦老师'}}

仁者见仁智者见智,个人比较喜欢第一种方法

如何实现一对多、一个老师教多个学生

student实体类

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 + '\'' +
                ", tid=" + tid +
                '}';
    }
}

teacher实体类

//一对多,一个老师教多个学生
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;
    }

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

mapper接口

//根据老师id查询老师教的学生
    public Teacher selectTeacher(@Param("tid") int id);

和上面的多对一类似,这里主要是如何使用collection进行映射

mapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cn.kexing.mybatis.dao.TeacherMapper">

<select id="selectTeacher" resultMap="teacherResult">
    SELECT t.id tid,t.`name` tname,s.`name` sname,s.id sid
    FROM teacher t LEFT JOIN student s
    ON t.id=s.tid and t.id=#{tid}
</select>

    <resultMap id="teacherResult" type="Teacher">
        <id property="id" column="tid"></id>
        <result property="name" column="tname"></result>
        <collection property="students" ofType="Student">
            <result property="id" column="sid"></result>
            <result property="name" column="sname"></result>
            <result property="tid" column="tid"></result>
        </collection>
    </resultMap>
</mapper>

Test

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

结果

Teacher{id=1, name='秦老师', students=[Student{id=1, name='小明', tid=1}, Student{id=2, name='小红', tid=1}, Student{id=3, name='小张', tid=1}, Student{id=4, name='小李', tid=1}, Student{id=5, name='小王', tid=1}]}
最后修改:2021 年 11 月 15 日 05 : 24 PM
如果觉得我的文章对你有用,请随意赞赏