一.一对一的表查询
查询班级表中班级号为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打印结果不对
转载于:https://www.cnblogs.com/zhima-hu/p/7443679.html
相关资源：结合mybatis-plus实现简单不需要写sql的多表查询
