Mybatis框架学习总结-表的关联查询

一对一关联

创建表和数据：创建一张教师表和班级表，这里假设一个老师只负责教一个班，那么老师和班级之间的关系就是一种一对一的关系。

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('teacher1');
INSERT INTO teacher(t_name) VALUES('teacher2');

INSERT INTO class(c_name, teacher_id) VALUES('class_a', 1);
INSERT INTO class(c_name, teacher_id) VALUES('class_b', 2);

定义实体类：

1.Teacher类，Teacher类时teacher表对应的实体类。

package com.fpc.Entity;

public class Teacher {
    private int t_id;
    private String t_name;
    public int getT_id() {
        return t_id;
    }
    public void setT_id(int t_id) {
        this.t_id = t_id;
    }
    public String getT_name() {
        return t_name;
    }
    public void setT_name(String t_name) {
        this.t_name = t_name;
    }
    
    @Override
    public String toString() {
        return "Teacher [t_id:" + t_id + ", t_name:" + t_name + "]";
    }
}

2.Classes类，Classes类是class表对应的实体类

package com.fpc.Entity;

public class Classes {
    private int c_id;
    private String c_name;
    
    private Teacher teacher;
    public int getC_id() {
        return c_id;
    }
    public void setC_id(int c_id) {
        this.c_id = c_id;
    }
    public String getC_name() {
        return c_name;
    }
    public void setC_name(String c_name) {
        this.c_name = c_name;
    }
    
    
    public Teacher getTeacher() {
        return teacher;
    }
    public void setTeacher(Teacher teacher) {
        this.teacher = teacher;
    }
    @Override
    public String toString() {
        return "Class [t_id:" + c_id + ", t_name:" + c_name + ",teacher:"+ teacher + "]";
    }
}

3.定义sql映射文件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="com.fpc.Mapping.classMapper">
    
   <resultMap type="com.fpc.Entity.Classes" id="ClassResultMap">
       <id property="c_id" column="t_id"/>
       <id property="c_name" column="c_name"/>
        <association property="teacher" column="teacher_id" javaType="com.fpc.Entity.Teacher">
            <id property="t_id" column="t_id"/>
            <id property="t_name" column="t_name"/>
        </association>
   </resultMap>
   
   <select id="getClass" resultMap="ClassResultMap">
        select * from class c ,teacher t where c_id=#{id} and c.teacher_id=t.t_id
    </select>
</mapper>

在conf.xm文件中注册classMapper.xml文件：

 <mapper resource="com/fpc/Mapping/classMapper.xml"/>

4.编写单元测试代码

String statement1 = "com.fpc.Mapping.classMapper.getClass";
Classes class1 = session.selectOne(statement1,2);
System.out.println(class1);

运行结果：

MyBatis一对一关联查询总结

MyBatis中使用association标签来解决一对一的关联查询，association标签可用的属性如下：

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

一对多关联

根据classId查询对应的班级信息，包括学生，老师

1.创建表和数据：基于上面一对一关联查询演示中，已经创建了班级表和教师表，因此这里只要再创建一张学生表就可以了：

CREATE TABLE student(
 s_id INT PRIMARY KEY AUTO_INCREMENT,
 s_name VARCHAR(20),
 class_id INT
);
INSERT INTO student(s_name,class_id) VALUES("student_a",1);
INSERT INTO student(s_name,class_id) VALUES("student_b",1);
INSERT INTO student(s_name,class_id) VALUES("student_c",2);
INSERT INTO student(s_name,class_id) VALUES("student_d",2);

创建的结果：

2.定义实体类Student：

package com.fpc.Entity;

public class Student {
    //定义属性，和student表中的字段对应
    private int id; //id ===> s_id;
    private String name; // name ===> s_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 "Student [id=" + id + " , name= " + name + "]"; 
    }
}

2.修改Classes类，添加一个List<Student>属性，使用一个List<Student>集合属性表示班级拥有的学生，如下：

package com.fpc.Entity;
import java.util.List;

public class Classes {
    private int c_id;
    private String c_name;
    
    private Teacher teacher;
    
    private List<Student> students;
    public int getC_id() {
        return c_id;
    }
    public void setC_id(int c_id) {
        this.c_id = c_id;
    }
    public String getC_name() {
        return c_name;
    }
    public void setC_name(String c_name) {
        this.c_name = c_name;
    }
    
    
    public Teacher getTeacher() {
        return teacher;
    }
    public void setTeacher(Teacher teacher) {
        this.teacher = teacher;
    }
    
    
    
    public List<Student> getStudents() {
        return students;
    }
    public void setStudents(List<Student> students) {
        this.students = students;
    }
    @Override
    public String toString() {
        return "Class [t_id:" + c_id + ", t_name:" + c_name + ",teacher:"+ teacher + ", students = " + students +"]";
    }
}

3.修改sql映射文件classMapper.xml

添加如下的SQL映射信息：

<?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.fpc.Mapping.classMapper">
    
   <resultMap type="com.fpc.Entity.Classes" id="ClassResultMap">
       <id property="c_id" column="t_id"/>
       <id property="c_name" column="c_name"/>
        <association property="teacher" column="teacher_id" javaType="com.fpc.Entity.Teacher">
            <id property="t_id" column="t_id"/>
            <id property="t_name" column="t_name"/>
        </association>
        
        <!-- ofType指定students集合中的对象类型 -->
        <collection property="students" ofType="com.fpc.Entity.Student">
            <id property="id" column="s_id"/>
            <id property="name" column="s_name"/>
        </collection>
   </resultMap>
   
   <select id="getClass" resultMap="ClassResultMap">
        select * from class c ,teacher t ,student s where c_id=#{id} and c.teacher_id=t.t_id and s.class_id=c.c_id 
    </select>
</mapper>

4.编写单元测试代码：

String resource = "conf.xml";
        
//        InputStream is = Test1.class.getClassLoader().getResourceAsStream(resource);
        Reader reader = Resources.getResourceAsReader(resource);
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
        String statement = "com.fpc.Mapping.userMapper.getUser";
        SqlSession session = sessionFactory.openSession();
        
        String statement1 = "com.fpc.Mapping.classMapper.getClass";
        Classes class1 = session.selectOne(statement1,1);
        System.out.println(class1);

运行结果：

MyBatis一对多关联查询总结

Mybatis中使用collection标签来解决一对多的关联查询，ofType属性指定集合中元素的对象类型