Mybatis一对一关联查询

Mybatis一对一关联查询

有两张表，老师表teacher和班级表class，一个class班级对应一个teacher，一个teacher对应一个class

需求是根据班级id查询班级信息(带老师的信息)

创建teacher和class表：

 CREATE TABLE teacher (
    t_id INT PRIMARY KEY AUTO_INCREMENT,
    t_name VARCHAR(20)
);
CREATE TABLE class (
    c_id INT PRIMARY KEY AUTO_INCREMENT,
    c_name VARCHAR(20),
    teacher_id INT
);
ALTER TABLE class ADD CONSTRAINT fk_teacher_id FOREIGN KEY (teacher_id) REFERENCES
teacher(t_id);
INSERT INTO teacher(t_name) VALUES('LS1');
INSERT INTO teacher(t_name) VALUES('LS2');
INSERT INTO class(c_name, teacher_id) VALUES('bj_a', 1);
INSERT INTO class(c_name, teacher_id) VALUES('bj_b', 2);

首先定义实体类

老师Teacher类

package mybatis.bean;

public class Teacher {

    private int id;
    private String name;

    public Teacher() {

    }

    public Teacher(int id, String name) {
        super();
        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 + "]";
    }



}

班级Class类

package mybatis.bean;

public class Class {

    private int id;
    private String name;
    private Teacher teacher;

    public Class() {
    }

    public Class(int id, String name, Teacher teacher) {
        super();
        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 "Class [id=" + id + ", name=" + name + ", teacher=" + teacher + "]";
    }





}

要实现一对一关联查询，有两种方式

1.关联表查询

SELECT * FROM class c, teacher t WHERE c.teacher_id = t.t_id AND c.c_id = 1;

2.执行2次查询

先查询class表，获取teacher_id，再查询teacher表，获取teacher的信息

SELECT * FROM class where c_id = 1;
SELECT * FROM teacher where t_id = 1;//使用上面得到的teacher_id

这两种查询方式就引出了2中方式

• 嵌套结果: 使用嵌套结果映射来处理重复的联合结果的子集
• 嵌套查询：通过执行另外一个SQL 映射语句来返回预期的复杂类型

嵌套结果

使用嵌套结果映射来处理重复的联合结果的子集
可以理解为封装联表查询的数据(去除重复的数据)

select * from class c, teacher t where c.teacher_id=t.t_id and c.c_id=1

classMapper.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="mybatis.test5.classMapper">

    <!--根据id查询到一个班级信息（带老师信息） -->


    <!-- 嵌套结果 -->
    <select id="getClass" parameterType="int" resultMap="ClassResultMap">
        select * from class c, teacher t where c.teacher_id=t.t_id and c.c_id=#{id}
    </select>
    <resultMap type="mybatis.bean.Class" id="ClassResultMap">
        <id property="id" column="c_id" />
        <result property="name" column="c_name" />
        <association property="teacher" column="teacher_id" javaType="mybatis.bean.Teacher">
            <id property="id" column="t_id" />
            <result property="name" column="t_name" />
        </association>
    </resultMap>

</mapper>

查询过程如下：

mybatis.bean.Class clazz = session.selectOne("mybatis.test5.classMapper.getClass", 1);
System.out.println(clazz);

控制台输出结果为：

Class [id=1, name=bj_a, teacher=Teacher [id=1, name=LS1]]

这里使用association标签，association用于一对一的关联查询

• property - 对象属性的名称
• javaType - 对象属性的类型
• column - 所对应的外键字段名称
• select - 使用另一个查询封装的结果

嵌套查询

通过执行另外一个SQL 映射语句来返回预期的复杂类型

SELECT * FROM class WHERE c_id=1;
SELECT * FROM teacher WHERE t_id=1 //1 是上一个查询得到的teacher_id 的值

在classMapper.xml中的配置如下：

<!--嵌套查询  -->
<select id="getClass2" parameterType="int" resultMap="ClassResultMap2">
    select * from class where c_id=#{id}
</select>
<resultMap type="mybatis.bean.Class" id="ClassResultMap2">
    <id property="id" column="c_id" />
    <result property="name" column="c_name" />
    <association property="teacher" column="teacher_id" javaType="mybatis.bean.Teacher" select="getTeacher">
    </association>
</resultMap>
<!-- 使用了sql别名  -->
<select id="getTeacher" parameterType="int" resultType="mybatis.bean.Teacher">
    SELECT t_id id, t_name name FROM teacher WHERE t_id=#{id}
</select>

这里使用了association标签的select属性，其值对应为<select id="getTeacher" ...>中的id的值

这种方式，同样可以得到正确的结果