Mybatis多表联查
Mybatis中实现了对数据库中的数据进行封装,那么进行多表查询时就会遇到查询结果不只是一个对象的数据,有可能是多个对象的混合,可以使用<resultMap>
标签进行映射
resultMap 标签
在<select>
标签中使用resultMap
可以完成与查询结果进行映射
1.一对一关系association
一对一关系是指关系数据库中两个表之间的一种关系,该关系中第一个表中的单个行只可以与第二个表中的一个行相关,且第二个表中的一个行也只可以与第一个表中的一个行相关。
使用 resultMap
实现关联单个对象,假设一位学生对应一个老师
多表联查sql语句
实体类entity
Student.java
public class Student {
private String sname;
private String sid;
private Teacher teacher;
/*无参构造,有参构造,get/set方法,toString方法*/
}
Tips:在Student
类中有teacher
属性,实现一对一关系
Teacher.java
public class Teacher {
private String tname;
private String id;
/*无参构造,有参构造,get/set方法,toString方法*/
}
Mapper映射
StudentMapper.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="Students">
<!-- 设计时尽量把属性字段和数据库字段设为相同 -->
<!-- 查询一条数据 -->
<select id="selectStudentById" parameterType="java.lang.String" resultMap="studentMap" >
SELECT
sid,
sname,
id,
tname
FROM
teacher t,
student s
WHERE
s.tid = t.id
AND s.sid = #{id}
</select>
<!-- 查询多条数据 -->
<select id="selectStudents" resultMap="studentMap" >
SELECT
sid,
sname,
id,
tname
FROM
teacher t
INNER JOIN student s ON s.tid = t.id;
</select>
<resultMap type="Student" id="studentMap">
<!-- id 表示主键列 -->
<!-- property 对应java实体类中属性名称 -->
<!-- column 对应数据库中列名 -->
<!-- association 表示一对一 -->
<id property="sid" column="sid" />
<result property="sname" column="sname" />
<!-- Teacher对象 -->
<association property="teacher" javaType="Teacher">
<id property="id" column="id" />
<result property="tname" column="tname" />
</association>
</resultMap>
</mapper>
测试
StudentTest.java
package com.zy.entity;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
public class StudentTest {
public static void main(String[] args) throws FileNotFoundException {
// 读取配置文件
InputStream in = new FileInputStream("src/main/resources/mybatis.xml");
// 解析配置文件
SqlSessionFactory sf = new SqlSessionFactoryBuilder().build(in);
SqlSession session = sf.openSession();
Student s = session.selectOne("Students.selectStudentById", "1");
System.out.println("多表联查返回一条数据:");
System.out.println(s);
List<Student> list = session.selectList("Students.selectStudents");
System.out.println("多表联查返回多条数据-----list:");
for (int i = 0; i < list.size(); i++) {
System.out.println(list.get(i));
}
}
}
2.一对多关系collection
一对多关系是关系数据库中两个表之间的一种关系,该关系中第一个表中的单个行可以与第二个表中的一个或多个行相关,但第二个表中的一个行只可以与第一个表中的一个行相关。
例如一个老师教授多个学生,那么老师与学生的关系就是一对多
数据库表还是teacher表与student表,数据不变
实体类entity
Teacher.java
public class Teacher {
private String tname;
private String id;
private List<Student> students;
/*无参构造,有参构造,get/set方法,toString方法*/
}
Tips:在Teacher实体类中多了一个List<Student>
学生集合属性,来表示一个老师教授多个学生
Student.java
public class Student {
private String sname;
private String sid;
/*无参构造,有参构造,get/set方法,toString方法*/
}
Mapper映射
TeacherMapper.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="Teachers">
<!-- 设计时尽量把属性字段和数据库字段设为相同 -->
<!-- 多表联查 -->
<select id="selectTeacherById" parameterType="java.lang.String"
resultMap="TeacherMap">
SELECT
id,
tname,
sid,
sname
FROM
teacher,
student
WHERE
teacher.id = student.tid and id = #{id}
</select>
<resultMap type="Teacher" id="TeacherMap">
<id property="id" column="id" />
<result property="tname" column="tname" />
<!-- ofType 关联类型 -->
<!-- collection 一对多 -->
<collection property="students" ofType="Student">
<id property="sid" column="sid" />
<result property="sname" column="sname" />
</collection>
</resultMap>
<!-- 子查询 -->
<select id="selectTeacherById1" parameterType="java.lang.String"
resultMap="TeacherMap1">
select * from teacher where id = #{id}
</select>
<resultMap type="Teacher" id="TeacherMap1">
<id property="id" column="id" />
<result property="tname" column="tname" />
<!-- ofType 关联类型 -->
<!-- collection 一对多 -->
<collection property="students" ofType="Student"
column="id" select="selectStudent">
</collection>
</resultMap>
<select id="selectStudent" parameterType="java.lang.String"
resultType="Student">
select * from student where tid = #{id}
</select>
</mapper>
Tips:javaType
顾名思义就是表示这个属性在java中所对应的类型,使用ofType
的原因是并没有创建一个可以明确表示这个List<Student>
集合的类,也可以理解List<Student>
不能单纯是一个Student
类型,使用ofType
表示属性List<Student>
所关联的java类是Student
类
测试
TeacherTest.java
package com.zy.entity;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
public class TeacherTest {
public static void main(String[] args) throws FileNotFoundException {
// 读取配置文件
InputStream in = new FileInputStream("src/main/resources/mybatis.xml");
// 解析配置文件
SqlSessionFactory sf = new SqlSessionFactoryBuilder().build(in);
SqlSession session = sf.openSession();
System.out.println("多表联查:");
Teacher t = session.selectOne("Teachers.selectTeacherById","1003");
System.out.println(t);
System.out.println("子查询:");
Teacher t1 = session.selectOne("Teachers.selectTeacherById1","1003");
System.out.println(t1);
session.close();
}
}
3.多对多关系
多对多关系是关系数据库中两个表之间的一种关系, 该关系中第一个表中的一个行可以与第二个表中的一个或多个行相关。第二个表中的一个行也可以与第一个表中的一个或多个行相关。
Tips:要表示多对多关系,必须创建第三个表,该表通常称为联接表,它将多对多关系划分为两个一对多关系。将这两个表的主键都插入到第三个表中,通过中间表将多对多拆分成两个一对多
例如一个学生有多个老师,一个老师教授多个学生,那么学生与老师的关系就是多对多
数据库表在之前的基础上稍作修改,加入第三张表stu_tea
实体类entity
Teacher.java
public class Teacher {
private String tname;
private String id;
private List<Student> students;
/*无参构造,有参构造,get/set方法,toString方法*/
}
Student.java
public class Student {
private String sname;
private String sid;
private String List<Teacher> teachers;
/*无参构造,有参构造,get/set方法,toString方法*/
}
Tips:为表示多对多关系,在Student
类与Teacher
类中都加入了对应的List
集合
Mapper映射
StudentMapper.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="Students">
<!-- 设计时尽量把属性字段和数据库字段设为相同 -->
<!-- 多表联查 -->
<select id="selectStudentsById" parameterType="java.lang.String" resultMap="StudentMap">
SELECT
s.sid,
s.sname,
t.id,
t.tname
FROM
student_copy s,
teacher_copy t,
stu_tea st
WHERE
s.sid = st.sid
AND t.id = st.id
AND s.sid = #{id}
</select>
<resultMap type="Student" id="StudentMap">
<id property="sid" column="sid"/>
<result property="sname" column="sname"/>
<collection property="teachers" ofType="Teacher">
<id property="id" column="id"/>
<result property="tname" column="tname"/>
</collection>
</resultMap>
<!-- 子查询 -->
<select id="selectStudentsById1" parameterType="java.lang.String" resultMap="StudentMap1">
select * from student_copy where sid = #{sid}
</select>
<resultMap type="Student" id="StudentMap1">
<id property="sid" column="sid"/>
<result property="sname" column="sname"/>
<collection property="teachers" column="sid" ofType="Teacher" select="selectTeachers"></collection>
</resultMap>
<select id="selectTeachers" parameterType="java.lang.String" resultType="Teacher">
SELECT
teacher_copy.id,
teacher_copy.tname
FROM
teacher_copy,
stu_tea
WHERE
stu_tea.id = teacher_copy.id
AND stu_tea.sid = #{sid}
</select>
</mapper>
TeacherMapper.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="Teachers">
<!-- 设计时尽量把属性字段和数据库字段设为相同 -->
<!-- 多表联查 -->
<select id="selectTeachersById" parameterType="java.lang.String" resultMap="TeacherMap">
SELECT
s.sid,
s.sname,
t.id,
t.tname
FROM
student_copy s,
teacher_copy t,
stu_tea st
WHERE
s.sid = st.sid
AND t.id = st.id
AND t.id = #{id}
</select>
<resultMap type="Teacher" id="TeacherMap">
<id property="id" column="id"/>
<result property="tname" column="tname"/>
<collection property="students" ofType="Student">
<id property="sid" column="sid"/>
<result property="sname" column="sname"/>
</collection>
</resultMap>
<!-- 子查询 -->
<select id="selectTeachersById1" parameterType="java.lang.String" resultMap="TeacherMap1">
select * from teacher_copy where id = #{id}
</select>
<resultMap type="Teacher" id="TeacherMap1">
<id property="id" column="id"/>
<result property="tname" column="tname"/>
<collection property="students" ofType="Student" column="id" select="selectStudents"></collection>
</resultMap>
<select id="selectStudents" resultType="Student" parameterType="java.lang.String">
SELECT
student_copy.sid,
student_copy.sname
FROM
student_copy,
stu_tea
WHERE
stu_tea.sid = student_copy.sid
AND stu_tea.id = #{id}
</select>
</mapper>
Tips:将多对多拆分成两个一对多,即有两个mapper
映射,通过Student
查询Teacher
,通过Teacher
查询Student
,双向的一对多查询实现多对多关系
测试
StudentTest.java
package com.zy.entity;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
public class StudentTest {
public static void main(String[] args) throws FileNotFoundException {
// 读取配置文件
InputStream in = new FileInputStream("src/main/resources/mybatis.xml");
// 解析配置文件
SqlSessionFactory sf = new SqlSessionFactoryBuilder().build(in);
SqlSession session = sf.openSession();
Student s = session.selectOne("Students.selectStudentsById","1");
System.out.println("多表联查:");
System.out.println(s);
Student s1 = session.selectOne("Students.selectStudentsById1","1");
System.out.println("子查询:");
System.out.println(s1);
session.close();
}
}
TeacherTest.java
package com.zy.entity;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
public class TeacherTest {
public static void main(String[] args) throws FileNotFoundException {
// 读取配置文件
InputStream in = new FileInputStream("src/main/resources/mybatis.xml");
// 解析配置文件
SqlSessionFactory sf = new SqlSessionFactoryBuilder().build(in);
SqlSession session = sf.openSession();
Teacher t = session.selectOne("Teachers.selectTeachersById","1003");
System.out.println("多表联查:");
System.out.println(t);
Teacher t1 = session.selectOne("Teachers.selectTeachersById1","1003");
System.out.println("子查询:");
System.out.println(t1);
session.close();
}
}
Tips:为了实现多对多关系,在Student
类与Teacher
类中都加入了对应的List
类型,导致查询结果中出现null