一.一对一的表查询
查询班级表中班级号为1的对应的记录（包括教师的具体信息）
1.首先建立数据表
数据表class和techear，class表中只有一个外键techear_id，sql脚本如下：
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);
建立好的数据表如下：
class表
techear表
2.实体类
Classes类对应class表，Teacher类对应teacher表
package me.gacl.domain; import me.gacl.domain.Teacher;
/**
* 班级实体类
*/
public class Classes {
private int id;//id对应c_id
private String name;//name对应c_name
private Teacher teacher;//因为class表中只有一个teacher_id外键，所以Class类中持有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 "Classes{" +
"id=" + id +
", name='" + name + '\'' +
", teacher=" + teacher +
'}';
}
}
package me.gacl.domain; /**
* 教师实体类
*/
public class Teacher {
private int id;//id对应t_id
private String name;//name对应t_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 + '\'' +
'}';
}
}
3.编写sql映射文件
teacher_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">
<!--namespace一般命名成报名+映射名-->
<mapper namespace="me.gacl.mapping.teacher_classMapper">
<!--查询班级为1的班级和教师信息-->
<!--方法一：联表查询-->
<select id="getClass_Teacher" parameterType="int" resultMap="classResultMap">
select * from class,teacher where class.teacher_id=teacher.t_id and c_id=#{id}
</select>
<!--使用resultMap指定实体类字段与数据表属性之间的一一对应关系-->
<resultMap id="classResultMap" type="Classes">
<id column="c_id" property="id"></id>
<result column="c_name" property="name"></result>
<!--使用association中的javaType指定关联表中对应的实体类的类型，
及实体类字段与数据表属性之间的一一对应关系-->
<association property="teacher" javaType="Teacher">
<id column="t_id" property="id"></id>
<result column="t_name" property="name"></result>
</association>
</resultMap>
<!--方法二，执行两步查询，引用另一步查询的查询结果-->
<!--首先查询出班级为1对应的教师信息-->
<select id="getClass_Teacher2" parameterType="int" resultMap="classResultMap2">
select * from class where c_id=#{id}
</select>
<resultMap id="classResultMap2" type="Classes">
<id column="c_id" property="id"></id>
<result column="c_name" property="name"></result>
<!--这里的column="teacher_id"一定不能少，因为teacher_id的值是作为参数传递给要引用的Select语句的
相当于mybatis执行resultSet.getInt("teacher_id")，where后面的id入参就是得到的teacher_id的值-->
<association property="teacher" column="teacher_id" select="getTeacher"></association>
</resultMap>
<!--然后根据上一步查询出的教师id到教师表中查询教师信息-->
<select id="getTeacher" parameterType="int" resultType="me.gacl.domain.Teacher">
select t_id id,t_name name from teacher where t_id=#{id}
</select>
</mapper>
注意：引用类都是用的别名，即类名
4.注册sql映射文件
在mybatis_config.xml中注册teacher_classMapper.xml映射文件
<mappers>
<mapper resource="mapping/teacher_classMapper.xml"></mapper>
</mappers>
5.编写测试类
package me.gacl.domain; import me.gacl.Util.MyBatisUtil;
import org.apache.ibatis.session.SqlSession;
import java.util.List;
/**
* 联表查询的测试类
*/
public class Class_TeacherTest {
public void testgetClass_teacher(){
SqlSession sqlSession = MyBatisUtil.getSqlSession(true);
String statement = "me.gacl.mapping.teacher_classMapper.getClass_Teacher";
Classes classes = sqlSession.selectOne(statement,1);
sqlSession.close();
System.out.println(classes);
}
public void testgetClass_teacher2(){
SqlSession sqlSession = MyBatisUtil.getSqlSession(true);
String statement = "me.gacl.mapping.teacher_classMapper.getClass_Teacher2";
List <Classes> list = sqlSession.selectList(statement,1);
sqlSession.close();
System.out.println(list);
} public static void main(String[] args) {
Class_TeacherTest class_teacherTest = new Class_TeacherTest();
//class_teacherTest.testgetClass_teacher();
class_teacherTest.testgetClass_teacher2();
}
}
测试结果：两个方法输出结果一样
6.总结
mybatis中实现一对一的多表查询，使用association标签实现，association标签的属性介绍如下：
properties：属性的名称
javaType：属性的类型
column：表中的外键字段名
select：使用另一个查询
二.实现一对多的联表查询
1.建立一个学生表，班级表与学生表示1：n的关系
sql脚本：
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', 1);
INSERT INTO student(s_name, class_id) VALUES('student_D', 2);
INSERT INTO student(s_name, class_id) VALUES('student_E', 2);
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类
类中添加学生对象
package me.gacl.domain; import me.gacl.domain.Teacher;
import java.util.List;
/**
* 班级实体类
*/
public class Classes {
private int id;//id对应c_id
private String name;//name对应c_name
private Teacher teacher;//因为class表中只有一个teacher_id外键，所以Class类中持有Teacher类的一个对象
private List<Student> students;//因为班级与学生是1：n的关系，因此使用List存储一个班的n个学生 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;
} public List<Student> getStudents() {
return students;
} public void setStudents(List<Student> students) {
this.students = students;
} @Override
public String toString() {
return "Classes{" +
"id=" + id +
", name='" + name + '\'' +
", teacher=" + teacher +
", students=" + students +
'}';
}
}
4.编写映射文件
<?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">
<!--namespace一般命名成报名+映射名-->
<mapper namespace="me.gacl.mapping.student_classMapper">
<!--查询班级为1的班级对应的学生和教师信息-->
<!--方法一：联表查询-->
<select id="getClassTeacherStudent" parameterType="int" resultMap="classResultMap">
select * from class,teacher,student where class.teacher_id=teacher.t_id and class.c_id=student.class_id and c_id=#{id}
</select>
<!--使用resultMap指定实体类字段与数据表属性之间的一一对应关系-->
<resultMap id="classResultMap" type="Classes">
<id column="c_id" property="id"></id>
<result column="c_name" property="name"></result>
<!--使用association中的javaType指定关联表中对应的实体类的类型，
及实体类字段与数据表属性之间的一一对应关系-->
<association property="teacher" column="teacher_id" javaType="Teacher">
<id column="t_id" property="id"></id>
<result column="t_name" property="name"></result>
</association>
<collection property="students" ofType="Student">
<id column="s_id" property="id"></id>
<result column="s_name" property="name"></result>
<!--若没有指定属性class_id的对应column名称，则输出class_id为0
也可以学生实体类中不包含属性class_id,那么打印输出中就没有class_id信息-->
<result column="class_id" property="class_id"></result>
</collection>
</resultMap>
<!--方法二，执行两步查询，引用另一步查询的查询结果-->
<!--首先查询出班级为1对应的教师信息-->
<select id="getClassTeacherStudent2" parameterType="int" resultMap="classResultMap2">
select * from class where c_id=#{id}
</select>
<resultMap id="classResultMap2" type="Classes">
<id column="c_id" property="id"></id>
<result column="c_name" property="name"></result>
<!--这里的column="teacher_id"一定不能少，因为teacher_id的值是作为参数传递给要引用的Select语句的
相当于mybatis执行resultSet.getInt("teacher_id")，where后面的id入参就是得到的teacher_id的值-->
<association property="teacher" column="teacher_id" select="getTeacher"></association>
<!--这里的column="c_id"一定不能少，因为c_id的值是作为参数传递给要引用的Select语句的
相当于mybatis执行resultSet.getInt("c_id")，where后面的id入参就是得到的c_id的值-->
<collection property="students" column="c_id" select="getStudent"></collection>
</resultMap>
<!--然后根据上一步查询出的教师id到教师表中查询教师信息-->
<select id="getTeacher" parameterType="int" resultType="Teacher">
select t_id id,t_name name from teacher where t_id=#{id}
</select>
<!--然后根据第一步查询出的班级id到学生表中查询班级中的学生信息-->
<select id="getStudent" parameterType="int" resultType="Student">
select s_id id,s_name name from student where class_id=#{id}
</select>
</mapper>
5.mybatis_config.xml中注册student_classMapper映射文件
<mappers>
<mapper resource="mapping/student_classMapper.xml"></mapper>
</mappers>
6.编写测试类
package me.gacl.domain; import me.gacl.Util.MyBatisUtil;
import org.apache.ibatis.session.SqlSession; /**
* 测试一对多联表查询
*/
public class Class_TeacherStudentTest {
public void getClassTeacherStudent(){
SqlSession sqlSession = MyBatisUtil.getSqlSession(true);
String statement = "me.gacl.mapping.student_classMapper.getClassTeacherStudent";
Classes classes = sqlSession.selectOne(statement,1);
sqlSession.close();
System.out.println(classes);
}
public void getClassTeacherStudent2(){
SqlSession sqlSession = MyBatisUtil.getSqlSession(true);
String statement = "me.gacl.mapping.student_classMapper.getClassTeacherStudent2";
Classes classes = sqlSession.selectOne(statement,2);
sqlSession.close();
System.out.println(classes);
} public static void main(String[] args) {
Class_TeacherStudentTest class_teacherStudentTest = new Class_TeacherStudentTest();
class_teacherStudentTest.getClassTeacherStudent();
//class_teacherStudentTest.getClassTeacherStudent2();
}
}
测试结果：两个方法的输出结果一样
7.一对多关联查询总结
mybatis中解决一对多查询问题使用的是collection标签，属性ofType表示指定集合中元素的对象类型
注意： toString()是Java中的一个内置方法，如果你在类里编写了toString，相当于覆盖了类中原有的toString，在System.out.print中会自动调用，因此每个实体类的toString()方法都要正确，最好使用自动生成的，不然System.out.print打印结果不对
