芝麻＿糊

导航

mybatis学习（五）----实现关联表查询

一.一对一的表查询

查询班级表中班级号为1的对应的记录（包括教师的具体信息）

1.首先建立数据表

数据表class和techear，class表中只有一个外键techear_id，sql脚本如下：

 1 CREATE TABLE teacher(
 2     t_id INT PRIMARY KEY AUTO_INCREMENT, 
 3     t_name VARCHAR(20)
 4 );
 5 CREATE TABLE class(
 6     c_id INT PRIMARY KEY AUTO_INCREMENT, 
 7     c_name VARCHAR(20), 
 8     teacher_id INT
 9 );
10 ALTER TABLE class ADD CONSTRAINT fk_teacher_id FOREIGN KEY (teacher_id) REFERENCES teacher(t_id);    
11 
12 INSERT INTO teacher(t_name) VALUES('teacher1');
13 INSERT INTO teacher(t_name) VALUES('teacher2');
14 
15 INSERT INTO class(c_name, teacher_id) VALUES('class_a', 1);
16 INSERT INTO class(c_name, teacher_id) VALUES('class_b', 2);

建立好的数据表如下：

class表

techear表

2.实体类

Classes类对应class表，Teacher类对应teacher表

 1 package me.gacl.domain;
 2 
 3 import me.gacl.domain.Teacher;
 4 /**
 5  * 班级实体类
 6  */
 7 public class Classes {
 8     private int id;//id对应c_id
 9     private String name;//name对应c_name
10     private Teacher teacher;//因为class表中只有一个teacher_id外键，所以Class类中持有Teacher类的一个对象
11 
12     public int getId() {
13         return id;
14     }
15 
16     public void setId(int id) {
17         this.id = id;
18     }
19 
20     public String getName() {
21         return name;
22     }
23 
24     public void setName(String name) {
25         this.name = name;
26     }
27 
28     public Teacher getTeacher() {
29         return teacher;
30     }
31 
32     public void setTeacher(Teacher teacher) {
33         this.teacher = teacher;
34     }
35 
36     @Override
37     public String toString() {
38         return "Classes{" +
39                 "id=" + id +
40                 ", name='" + name + '\'' +
41                 ", teacher=" + teacher +
42                 '}';
43     }
44 }
 1 package me.gacl.domain;
 2 
 3 /**
 4  * 教师实体类
 5  */
 6 public class Teacher {
 7     private int id;//id对应t_id
 8     private String name;//name对应t_name
 9 
10     public int getId() {
11         return id;
12     }
13 
14     public void setId(int id) {
15         this.id = id;
16     }
17 
18     public String getName() {
19         return name;
20     }
21 
22     public void setName(String name) {
23         this.name = name;
24     }
25 
26     @Override
27     public String toString() {
28         return "Teacher{" +
29                 "id=" + id +
30                 ", name='" + name + '\'' +
31                 '}';
32     }
33 }

3.编写sql映射文件

teacher_classMapper.xml文件如下：
 1 <?xml version="1.0" encoding="utf-8" ?>
 2 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
 3 <!--namespace一般命名成报名+映射名-->
 4 <mapper namespace="me.gacl.mapping.teacher_classMapper">
 5     <!--查询班级为1的班级和教师信息-->
 6     <!--方法一：联表查询-->
 7     <select id="getClass_Teacher" parameterType="int" resultMap="classResultMap">
 8         select * from class,teacher where class.teacher_id=teacher.t_id and c_id=#{id}
 9     </select>
10     <!--使用resultMap指定实体类字段与数据表属性之间的一一对应关系-->
11     <resultMap id="classResultMap" type="Classes">
12         <id column="c_id" property="id"></id>
13         <result column="c_name" property="name"></result>
14         <!--使用association中的javaType指定关联表中对应的实体类的类型，
15         及实体类字段与数据表属性之间的一一对应关系-->
16         <association property="teacher" javaType="Teacher">
17             <id column="t_id" property="id"></id>
18             <result column="t_name" property="name"></result>
19         </association>
20     </resultMap>
21     <!--方法二，执行两步查询，引用另一步查询的查询结果-->
22     <!--首先查询出班级为1对应的教师信息-->
23     <select id="getClass_Teacher2" parameterType="int" resultMap="classResultMap2">
24         select * from class where c_id=#{id}
25     </select>
26     <resultMap id="classResultMap2" type="Classes">
27         <id column="c_id" property="id"></id>
28         <result column="c_name" property="name"></result>
29         <!--这里的column="teacher_id"一定不能少，因为teacher_id的值是作为参数传递给要引用的Select语句的
30         相当于mybatis执行resultSet.getInt("teacher_id")，where后面的id入参就是得到的teacher_id的值-->
31         <association property="teacher" column="teacher_id" select="getTeacher"></association>
32     </resultMap>
33     <!--然后根据上一步查询出的教师id到教师表中查询教师信息-->
34     <select id="getTeacher" parameterType="int" resultType="me.gacl.domain.Teacher">
35         select t_id id,t_name name from teacher where t_id=#{id}
36     </select>
37 </mapper>

注意：引用类都是用的别名，即类名

4.注册sql映射文件

 在mybatis_config.xml中注册teacher_classMapper.xml映射文件

1    <mappers>    
2         <mapper resource="mapping/teacher_classMapper.xml"></mapper>
3     </mappers>

5.编写测试类

 1 package me.gacl.domain;
 2 
 3 import me.gacl.Util.MyBatisUtil;
 4 import org.apache.ibatis.session.SqlSession;
 5 import java.util.List;
 6 /**
 7  * 联表查询的测试类
 8  */
 9 public class Class_TeacherTest {
10     public void testgetClass_teacher(){
11         SqlSession sqlSession = MyBatisUtil.getSqlSession(true);
12         String statement = "me.gacl.mapping.teacher_classMapper.getClass_Teacher";
13         Classes classes = sqlSession.selectOne(statement,1);
14         sqlSession.close();
15         System.out.println(classes);
16     }
17     public void testgetClass_teacher2(){
18         SqlSession sqlSession = MyBatisUtil.getSqlSession(true);
19         String statement = "me.gacl.mapping.teacher_classMapper.getClass_Teacher2";
20         List <Classes> list = sqlSession.selectList(statement,1);
21         sqlSession.close();
22         System.out.println(list);
23     }
24 
25     public static void main(String[] args) {
26         Class_TeacherTest class_teacherTest = new Class_TeacherTest();
27         //class_teacherTest.testgetClass_teacher();
28         class_teacherTest.testgetClass_teacher2();
29     }
30 }

测试结果：两个方法输出结果一样

6.总结

mybatis中实现一对一的多表查询，使用association标签实现，association标签的属性介绍如下：

properties：属性的名称

javaType：属性的类型

column：表中的外键字段名

select：使用另一个查询

二.实现一对多的联表查询

1.建立一个学生表，班级表与学生表示1：n的关系

sql脚本：

 1 CREATE TABLE student(
 2     s_id INT PRIMARY KEY AUTO_INCREMENT, 
 3     s_name VARCHAR(20), 
 4     class_id INT
 5 );
 6 INSERT INTO student(s_name, class_id) VALUES('student_A', 1);
 7 INSERT INTO student(s_name, class_id) VALUES('student_B', 1);
 8 INSERT INTO student(s_name, class_id) VALUES('student_C', 1);
 9 INSERT INTO student(s_name, class_id) VALUES('student_D', 2);
10 INSERT INTO student(s_name, class_id) VALUES('student_E', 2);
11 INSERT INTO student(s_name, class_id) VALUES('student_F', 2);

2.建立student实体类

package me.gacl.domain;

/**
 * 学生实体类
 */
public class Student {
    private int id;//id对应student表中的s_id
    private String name;//name对应student表中的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 + '\'' +
                '}';
    }
}

3.修改Classes类

类中添加学生对象

 1 package me.gacl.domain;
 2 
 3 import me.gacl.domain.Teacher;
 4 import java.util.List;
 5 /**
 6  * 班级实体类
 7  */
 8 public class Classes {
 9     private int id;//id对应c_id
10     private String name;//name对应c_name
11     private Teacher teacher;//因为class表中只有一个teacher_id外键，所以Class类中持有Teacher类的一个对象
12     private List<Student> students;//因为班级与学生是1：n的关系，因此使用List存储一个班的n个学生
13 
14     public int getId() {
15         return id;
16     }
17 
18     public void setId(int id) {
19         this.id = id;
20     }
21 
22     public String getName() {
23         return name;
24     }
25 
26     public void setName(String name) {
27         this.name = name;
28     }
29 
30     public Teacher getTeacher() {
31         return teacher;
32     }
33 
34     public void setTeacher(Teacher teacher) {
35         this.teacher = teacher;
36     }
37 
38     public List<Student> getStudents() {
39         return students;
40     }
41 
42     public void setStudents(List<Student> students) {
43         this.students = students;
44     }
45 
46     @Override
47     public String toString() {
48         return "Classes{" +
49                 "id=" + id +
50                 ", name='" + name + '\'' +
51                 ", teacher=" + teacher +
52                 ", students=" + students +
53                 '}';
54     }
55 }

4.编写映射文件

 1 <?xml version="1.0" encoding="utf-8" ?>
 2 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
 3 <!--namespace一般命名成报名+映射名-->
 4 <mapper namespace="me.gacl.mapping.student_classMapper">
 5     <!--查询班级为1的班级对应的学生和教师信息-->
 6     <!--方法一：联表查询-->
 7    <select id="getClassTeacherStudent" parameterType="int" resultMap="classResultMap">
 8        select * from class,teacher,student where class.teacher_id=teacher.t_id and class.c_id=student.class_id and c_id=#{id}
 9    </select>
10     <!--使用resultMap指定实体类字段与数据表属性之间的一一对应关系-->
11     <resultMap id="classResultMap" type="Classes">
12         <id column="c_id" property="id"></id>
13         <result column="c_name" property="name"></result>
14         <!--使用association中的javaType指定关联表中对应的实体类的类型，
15         及实体类字段与数据表属性之间的一一对应关系-->
16         <association property="teacher" column="teacher_id" javaType="Teacher">
17             <id column="t_id" property="id"></id>
18             <result column="t_name" property="name"></result>
19         </association>
20         <collection property="students" ofType="Student">
21             <id column="s_id" property="id"></id>
22             <result column="s_name" property="name"></result>
23             <!--若没有指定属性class_id的对应column名称，则输出class_id为0
24             也可以学生实体类中不包含属性class_id,那么打印输出中就没有class_id信息-->
25             <result column="class_id" property="class_id"></result>
26         </collection>
27     </resultMap>
28     <!--方法二，执行两步查询，引用另一步查询的查询结果-->
29     <!--首先查询出班级为1对应的教师信息-->
30     <select id="getClassTeacherStudent2" parameterType="int" resultMap="classResultMap2">
31         select * from class where c_id=#{id}
32     </select>
33     <resultMap id="classResultMap2" type="Classes">
34         <id column="c_id" property="id"></id>
35         <result column="c_name" property="name"></result>
36         <!--这里的column="teacher_id"一定不能少，因为teacher_id的值是作为参数传递给要引用的Select语句的
37         相当于mybatis执行resultSet.getInt("teacher_id")，where后面的id入参就是得到的teacher_id的值-->
38         <association property="teacher" column="teacher_id" select="getTeacher"></association>
39         <!--这里的column="c_id"一定不能少，因为c_id的值是作为参数传递给要引用的Select语句的
40         相当于mybatis执行resultSet.getInt("c_id")，where后面的id入参就是得到的c_id的值-->
41         <collection property="students" column="c_id" select="getStudent"></collection>
42     </resultMap>
43     <!--然后根据上一步查询出的教师id到教师表中查询教师信息-->
44     <select id="getTeacher" parameterType="int" resultType="Teacher">
45         select t_id id,t_name name from teacher where t_id=#{id}
46     </select>
47     <!--然后根据第一步查询出的班级id到学生表中查询班级中的学生信息-->
48     <select id="getStudent" parameterType="int" resultType="Student">
49         select s_id id,s_name name from student where class_id=#{id}
50     </select>
51 </mapper>

5.mybatis_config.xml中注册student_classMapper映射文件

1  <mappers>      
2         <mapper resource="mapping/student_classMapper.xml"></mapper>
3     </mappers>

6.编写测试类

 1 package me.gacl.domain;
 2 
 3 import me.gacl.Util.MyBatisUtil;
 4 import org.apache.ibatis.session.SqlSession;
 5 
 6 /**
 7  * 测试一对多联表查询
 8  */
 9 public class Class_TeacherStudentTest {
10     public void getClassTeacherStudent(){
11         SqlSession sqlSession = MyBatisUtil.getSqlSession(true);
12         String statement = "me.gacl.mapping.student_classMapper.getClassTeacherStudent";
13         Classes classes = sqlSession.selectOne(statement,1);
14         sqlSession.close();
15         System.out.println(classes);
16     }
17     public void getClassTeacherStudent2(){
18         SqlSession sqlSession = MyBatisUtil.getSqlSession(true);
19         String statement = "me.gacl.mapping.student_classMapper.getClassTeacherStudent2";
20         Classes classes = sqlSession.selectOne(statement,2);
21         sqlSession.close();
22         System.out.println(classes);
23     }
24 
25     public static void main(String[] args) {
26         Class_TeacherStudentTest class_teacherStudentTest = new Class_TeacherStudentTest();
27         class_teacherStudentTest.getClassTeacherStudent();
28         //class_teacherStudentTest.getClassTeacherStudent2();
29     }
30 }

测试结果：两个方法的输出结果一样

7.一对多关联查询总结

mybatis中解决一对多查询问题使用的是collection标签，属性ofType表示指定集合中元素的对象类型

注意： toString()是Java中的一个内置方法，如果你在类里编写了toString，相当于覆盖了类中原有的toString，在System.out.print中会自动调用，因此每个实体类的toString()方法都要正确，最好使用自动生成的，不然System.out.print打印结果不对

posted on 2017-08-28 14:19  芝麻＿糊  阅读(430)  评论(0)  编辑  收藏  举报