Mybaits之多表查询（联合查询、嵌套查询）

多表联合查询

(一) 多表联合查询概述

在开发过程中单表查询不能满足项目需求分析功能，对于复杂业务来讲，关联的表有几张，甚至几十张并且表与表之间的关系相当复杂。为了能够实业复杂功能业务，就必须进行多表查询，在mybatis中提供了多表查询的结果时映射标签，可以实现表之间的一对一、一对多、多对一、多对多关系映射。

(二) MyBatis多表查询之一对一

1. 构建数据库表:person(个人表) IdCard(身份证表)

#person表
CREATE TABLE person(
p_id INT NOT NULL AUTO_INCREMENT,
p_name VARCHAR(30),
PRIMARY KEY(p_id)
);

#IdCard表
CREATE TABLE idcard(
c_id INT NOT NULL AUTO_INCREMENT,
c_cardno VARCHAR(18),
c_uselife DATE,
c_person_id INT NOT NULL,
PRIMARY KEY(c_id),
FOREIGN KEY(c_person_id) REFERENCES person(p_id),
UNIQUE KEY(c_cardno),
UNIQUE KEY(c_person_id)
);

INSERT INTO person(p_name) VALUES('张三'),('李四');

INSERT INTO idcard(c_cardno,c_uselife,c_person_id)
VALUES('110112199012127821','2029-10-10',1);
INSERT INTO idcard(c_cardno,c_uselife,c_person_id)
VALUES('120114199911103491','2030-12-01',2);

2.准备项目环境

1) 构建maven项目，添加依赖

<dependencies>
<dependency>
   <groupId>org.mybatis</groupId>
   <artifactId>mybatis</artifactId>
   <version>3.4.6</version>
</dependency>
<dependency>
   <groupId>mysql</groupId>
   <artifactId>mysql-connector-java</artifactId>
   <version>5.1.47</version>
   <scope>runtime</scope>
</dependency>
<dependency>
   <groupId>junit</groupId>
   <artifactId>junit</artifactId>
   <version>4.12</version>
   <scope>test</scope>
</dependency>
<dependency>
   <groupId>log4j</groupId>
   <artifactId>log4j</artifactId>
   <version>1.2.17</version>
</dependency>
</dependencies>

2)编写框架配置文件sqlMapConfig.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!-- 配置 mybatis的环境 -->
<environments default="development">
<!-- 配置环境 -->
<environment id="development">
   <!-- 配置事务的类型 -->
   <transactionManager type="JDBC"></transactionManager>
   <!-- 配置连接数据库的信息:用的是数据源【连接池】-->
   <dataSource type="POOLED">
       <property name="driver" value="com.mysql.jdbc.Driver"/>
       <property name="url" value="jdbc:mysql://localhost:3306/mybatis002"/>
       <property name="username" value="root"/>
       <property name="password" value="root"/>
   </dataSource>
</environment>
</environments>
<mappers>
<package name="cn.offcn.dao"></package>
</mappers
</configuration>

3.方式一：嵌套结果方式

1)创建数据模型Person对象,IdCard对象

public class Person {
    private Integer id;
    private String name;
    private Idcard idcard;
}

public class Idcard {
    private Integer id;
    private String cardno;
    private Date uselife;
}

2)定义持久层接口PersonMapper

public interface PersonDao{

/**
    * 根据id查询person对象
    */
    Person getPersonById1(int i);
}

3)定义 PersonMapper.xml 文件中的查询配置信息

<resultMap id="BaseResultMap" type="Person">
        <id column="p_id" property="id"></id>
        <result column="p_name" property="name"></result>
    </resultMap>

    <resultMap id="PersonResultMap1" type="Person" extends="BaseResultMap">
        <association property="idcard" javaType="Idcard">
            <id column="c_id" property="id"></id>
            <result column="c_cardno" property="cardno"></result>
            <result column="c_uselife" property="uselife"></result>
        </association>
    </resultMap>

     <!--  根据部门id查找部门  以及所在部门所有员工的所有信息【嵌套结果查询，联合查询】  -->
    <select id="getPersonById1" parameterType="int" resultType="Person" resultMap="PersonResultMap1">
        select p.*,c.* from person p,idcard c where p.p_id=c.c_person_id and p.p_id=#{id}
    </select>

4)创建 PersonTest 测试类

 @Test
    // 根据人的id查询人与信息 和 身份证信息 【嵌套结果查询，连表查询】
    public void getPersonById1(){
        SqlSession session = MyBatisUtils.getSession();
        PersonMapper mapper = session.getMapper(PersonMapper.class);
        Person person =  mapper.getPersonById1(1);
        // 输出
        System.out.println(person);
        System.out.println(person.getIdcard().toString());
        MyBatisUtils.close(session);
    }

4.方式二：嵌套查询方式

1)创建数据模型Person对象,IdCard对象

public class Person {
    private Integer id;
    private String name;
    private Idcard idcard;
}

public class Idcard {
    private Integer id;
    private String cardno;
    private Date uselife;
}

2)定义持久层接口PersonMapper

public interface IdCardDao{
    /**
    * 根据c_person_id查询IdCard对象
    */
   Person getPersonById2(int i);
}

3)定义 PersonMapper.xml 文件中的查询配置信息

<!--  根据人的id查询人与信息 和 身份证信息 【嵌套查询，分表查询】  -->
    <select id="getPersonById2" parameterType="int" resultType="Person" resultMap="PersonResultMap2">
        select * from person where p_id=#{id}
    </select>

    <resultMap id="BaseResultMap" type="Person">
        <id column="p_id" property="id"></id>
        <result column="p_name" property="name"></result>
    </resultMap>
    
    <resultMap id="PersonResultMap2" type="Person" extends="BaseResultMap">
        <association property="idcard" javaType="Idcard" column="p_id"
                     select="com.chenshuang.mapper.IdcardMapper.getIdcardById">
        </association>
    </resultMap>

4)定义持久层接口IdcardMapper

public interface IdcardMapper {
    Idcard getIdcardById(int id);
}

5)定义 IdcardMapper.xml 文件中的查询配置信息

<mapper namespace="com.chenshuang.mapper.IdcardMapper">

    <resultMap id="BaseResultMap" type="Idcard">
        <id column="c_id" property="id"></id>
        <result column="c_cardno" property="cardno"></result>
        <result column="c_uselife" property="uselife"></result>
    </resultMap>
    <select id="getIdcardById" parameterType="int" resultType="Idcard" resultMap="BaseResultMap">
        select * from idcard where c_id=#{id}
    </select>
</mapper>

6)PersonTest 类中加入测试方法

 @Test
    // 根据人的id查询人与信息 和 身份证信息 【嵌套查询，分表查询】
    public void getPersonById2(){
        SqlSession session = MyBatisUtils.getSession();
        PersonMapper mapper = session.getMapper(PersonMapper.class);
        Person person =  mapper.getPersonById2(1);
        System.out.println(person);
        System.out.println(person.getIdcard().toString());
        MyBatisUtils.close(session);
    }

(三) MyBatis实现一对多查询

1.创建数据库表:department(部门表),employee(员工表)同时设定部门和员工表的关系

CREATE TABLE department(
d_id INT NOT NULL AUTO_INCREMENT,
d_name VARCHAR(100),
PRIMARY KEY(d_id)
);

CREATE TABLE employee(
e_id INT NOT NULL AUTO_INCREMENT,
e_name VARCHAR(30),
e_gender VARCHAR(6),
e_age INT,
e_depart_id INT,
PRIMARY KEY(e_id),
FOREIGN KEY(e_depart_id) REFERENCES department(d_id)
);

2.方式一:嵌套结果，连表查询的方式

1）创建数据模型department和employee

public class Department {
    private Integer id;
    private String name;
    private List<Employee> employees;
}

public class Employee {
    private Integer id;
    private String name;
    private String gender;
    private Integer age;
    private Department depart;
}

2）部门持久层DepartmentMapper 接口中加入查询方法

public interface DepartmentMapper {	
    Department getDepartmentById1(int id);
    Department getDepartmentById2(int id);
}

3）部门持久层 DepartmentMapper.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="cn.offcn.mapper.DepartmentDao">
 <select id="getDepartmentById" resultMap="DepartemntResultMap2">
     SELECT d.*,e.* FROM department d, employee e WHERE
     e.e_depart_id=d.d_id AND d.d_id=#{id}
 </select>
<resultMap id="DepartemntResultMap" type="Department">
    <id column="d_id" property="id"></id>
    <result column="d_name" property="name"></result>
    <collection property="emps" ofType="Employee">
         <id column="e_id" property="id"></id>
         <result column="e_name" property="name"></result>
         <result column="e_gender" property="gender"></result>
         <result column="e_age" property="age"></result>
    </collection>
</resultMap>
collection：当属性为集合时，使用collection标签进行映射。

4）测试方法

 @Test
 public void testGetDepartentById() throws  Exception{
     //获取SqlSession对象
     SqlSession session = MyBatisUtils.getSession();
     //调用SqlSession 创建 UserDao接口的代理对象
     DepartmentDao departmentDao = session.getMapper(DepartmentDao.class);
     Department dept=departmentDao.getDepartentById(1);
     //打印
     System.out.println(dept);
     //提交事务
     session.commit();
     //关闭连接
     MyBatisUtils.close(session);
 }

3.方式二:嵌套查询，分表查询的方式

1）定义员工的持久层接口EmployeeMapper

public interface EmployeeDao {
public List<Employee> getEmployeeByDepartId(int id);
}

2）定义DepartmentMapper.xml 文件中的查询配置信息

<select id="getEmployeeByDepartId" resultType="Employee">
    select e_id id,e_name name,e_gender gender,e_age age
    from employee where e_depart_id=#{id}
</select>

3）定义DepartmentMapper.xml配置文件

<select id="getDepartmentById" resultMap="DepartemntResultMap">
    select * from department where d_id=#{id}
 </select>
<resultMap id="DepartemntResultMap" type="Department">
     <id column="d_id" property="id"></id>
     <result column="d_name" property="name"></result>
     <collection property="emps" ofType="Employee" column="d_id"
              select="cn.offcn.mapper.EmployeeMapper.getEmployeeByDepartId">
     </collection>
</resultMap>
此处变为单表查询，使分表查询方式进行查询。

4）定义 EmployeeMapper.xml 文件中的查询配置信息

public interface EmployeeMapper {
    List<Employee> getEmployeeById(int i);
}

5）定义 EmployeeMapper.xml 配置文件

    <resultMap id="BaseResultMap" type="employee">
        <id column="e_id" property="id"></id>
        <result column="e_name" property="name"></result>
        <result column="e_gender" property="gender"></result>
        <result column="e_age" property="age"></result>
    </resultMap>
    
<!--
        property 设置本类型中的复杂属性
        javaType 复杂数据的数据类型
        column 表示取sql结果集中的 指定字段
        select 表示调查的查询 如果本次查询和调查的查询没有在同一个命令空间中，调用时候一定要加命名空间
-->
    <resultMap id="EmployeeResultMap1" type="employee" extends="BaseResultMap">
        <association property="depart" javaType="Department" column="e_depart_id"
                     select="com.chenshuang.mapper.DepartmentMapper.getDepartmentById"></association>
    </resultMap>
    <!-- 根据员工id查找员工以及所在部门的所有信息【嵌套查询,分表查询】-->
    <select id="getEmployeeById2" parameterType="int" resultType="Employee" resultMap="EmployeeResultMap1">
        select * from employee where e_id=#{id}
    </select>

6）测试方法

@Test
public void testGetDepartentById() throws  Exception{
//获取SqlSession对象
SqlSession session = MyBatisUtils.getSession();
//调用SqlSession 创建 UserDao接口的代理对象
DepartmentDao departmentDao = session.getMapper(DepartmentDao.class);
Department dept=departmentDao.getDepartentById(1);
//打印
System.out.println(dept);
//提交事务
session.commit();
//关闭连接
MyBatisUtils.close(session);
}

(四) MyBatis实现多对一查询

1.创建数据库表:department(部门表),employee(员工表)同时设定部门和员工表的关系

CREATE TABLE department(
d_id INT NOT NULL AUTO_INCREMENT,
d_name VARCHAR(100),
PRIMARY KEY(d_id)
);

CREATE TABLE employee(
e_id INT NOT NULL AUTO_INCREMENT,
e_name VARCHAR(30),
e_gender VARCHAR(6),
e_age INT,
e_depart_id INT,
PRIMARY KEY(e_id),
FOREIGN KEY(e_depart_id) REFERENCES department(d_id)
);

2.方式一:嵌套结果，连表查询的方式

1）创建数据模型department和employee

public class Department {
    private Integer id;
    private String name;
    private List<Employee> employees;
}

public class Employee {
    private Integer id;
    private String name;
    private String gender;
    private Integer age;
    private Department depart;
}

2）员工持久层EmployeeMapper接口中加入查询方法

public interface EmployeeMapper {
    Employee getEmployeeById1(int i);
}

3）部门持久层 EmployeeMapper.xml映射文件配置

  <resultMap id="BaseResultMap" type="employee">
        <id column="e_id" property="id"></id>
        <result column="e_name" property="name"></result>
        <result column="e_gender" property="gender"></result>
        <result column="e_age" property="age"></result>
    </resultMap>

<!--  property 设置本类型中的复杂属性
        javaType 复杂数据的数据类型   -->
    <resultMap id="EmployeeResultMap" type="employee" extends="BaseResultMap">
        <association property="depart" javaType="Department">
            <id column="d_id" property="id"></id>
            <result column="d_name" property="name"></result>
        </association>
    </resultMap>

    <!-- 根据员工id查找员工以及所在部门的所有信息【嵌套结果查询，联合查询】   -->
    <select id="getEmployeeById1" parameterType="int" resultType="Employee" resultMap="EmployeeResultMap">
        SELECT e.*,d.* from employee e,department d WHERE e.e_depart_id = d.d_id AND e.e_id=#{id}
    </select>

4）测试方法

@Test
//  根据员工id查找员工以及所在部门的所有信息【嵌套结果查询，联合查询】
public void getEmployeeById1(){
    SqlSession session = MyBatisUtils.getSession();
    EmployeeMapper mapper = session.getMapper(EmployeeMapper.class);
    Employee employee = mapper.getEmployeeById1(1);
    System.out.println(employee.toString()+"部门---："+employee.getDepart().toString());
    MyBatisUtils.close(session);
}

3.方式二:嵌套查询，分表查询的方式

1）定义员工的持久层接口DepartmentMapper

public interface DepartmentMapper {
    Department getDepartmentById(int id);
}

2）员工持久层EmployeeMapper接口中加入查询方法

public interface EmployeeMapper {

    Employee getEmployeeById2(int i);

    List<Employee> getEmployeeById(int i);
}

3）定义EmployeeMapper.xml配置文件

    <resultMap id="BaseResultMap" type="employee">
        <id column="e_id" property="id"></id>
        <result column="e_name" property="name"></result>
        <result column="e_gender" property="gender"></result>
        <result column="e_age" property="age"></result>
    </resultMap>

<!--
        property 设置本类型中的复杂属性
        javaType 复杂数据的数据类型
        column 表示取sql结果集中的 指定字段
        select 表示调查的查询 如果本次查询和调查的查询没有在同一个命令空间中，调用时候一定要加命名空间
-->
    <resultMap id="EmployeeResultMap1" type="employee" extends="BaseResultMap">
        <association property="depart" javaType="Department" column="e_depart_id"
                     select="com.chenshuang.mapper.DepartmentMapper.getDepartmentById"></association>
    </resultMap>
    <!-- 根据员工id查找员工以及所在部门的所有信息【嵌套查询,分表查询】-->
    <select id="getEmployeeById2" parameterType="int" resultType="Employee" resultMap="EmployeeResultMap1">
        select * from employee where e_id=#{id}
    </select>

4）定义 DepartmentMapper文件中的查询配置信息

public interface DepartmentMapper {
    Department getDepartmentById(int id);
}

5）定义DepartmentMapper.xml 配置文件

    <resultMap id="BaseResultMap" type="Department">
            <id column="d_id" property="id"></id>
            <result column="d_name" property="name"></result>
        </resultMap>

    <select id="getDepartmentById" parameterType="int" resultType="Department" resultMap="BaseResultMap">
        select * from department where d_id=#{id}
    </select>

6）测试方法

 @Test
    // 根据员工id查找员工以及所在部门的所有信息【嵌套查询,分表查询】
    public void getEmployeeById2(){
        SqlSession session = MyBatisUtils.getSession();
        EmployeeMapper mapper = session.getMapper(EmployeeMapper.class);
        Employee employee = mapper.getEmployeeById2(1);
        System.out.println(employee.toString()+"部门---："+employee.getDepart().toString());
        MyBatisUtils.close(session);
    }

(五) MyBatis实现多对多查询

1.创建数据库表:student(学生表),teacher(教师表) 同时设定教师和学生表的关系 多对多

CREATE TABLE teacher(
		t_id INT NOT NULL auto_increment,
		t_name VARCHAR(30),
		PRIMARY KEY(t_id)
);

create TABLE student_teacher(
	st_sid INT not NULL,
	st_tid INT NOT NULL,
	PRIMARY KEY(st_sid,st_tid),
	FOREIGN KEY(st_sid) REFERENCES student(s_id),
	FOREIGN KEY(st_tid) REFERENCES teacher(t_id)
);

2.方式一:嵌套结果，连表查询的方式

1）创建数据模型Teacher和 Student 和 中间类 studentTeacher

public class Student {
    private Integer id;
    private  String name;
    private String gender;
    private Integer age;
    private String birthday;
    private List<StudentTeacher> studentTeachers;
}

public class Teacher {
    private Integer id;
    private String name;
    private List<StudentTeacher> studentTeachers;
}

public class StudentTeacher {
    private Integer sid;
    private Integer tid;
    private Student student;
    private Teacher teacher;
}

2）学生持久层StudentMapper接口中加入查询方法

public interface StudentMapper {

    Student getStudentById1(int i);

}

3）学生持久层StudentMapper.xml映射文件配置

   <!--  根据学生id查 学生信息 和老师的信息 【嵌套结果查询，联合查询】  -->
    <select id="getStudentById1" parameterType="int" resultType="Student" resultMap="StudentResultMap1">
        SELECT s.*,t.*,st.*
        FROM student s,teacher t,student_teacher st
        where s.id=st.st_sid AND t.t_id=st.st_tid AND s.id=#{id}
    </select>

    <resultMap id="StudentResultMap1" type="Student">
        <id column="id" property="id"></id>
        <result column="name" property="name"></result>
        <result column="gender" property="gender"></result>
        <result column="age" property="age"></result>
        <result column="s_birthday" property="birthday"></result>
        <collection property="studentTeachers" ofType="StudentTeacher" >
            <result column="st_sid" property="sid"></result>
            <result column="st_tid" property="tid"></result>
            <association property="teacher" javaType="Teacher">
                <id column="t_id" property="id"></id>
                <result column="t_name" property="name"></result>
            </association>
        </collection>
    </resultMap>

4）测试方法

@Test
    // 根据学生id查 学生信息 和老师的信息 【嵌套结果查询，联合查询】
    public void getStudentById1(){
        SqlSession session = MyBatisUtils.getSession();
        StudentMapper mapper = session.getMapper(StudentMapper.class);
        Student student = mapper.getStudentById1(3);

        System.out.println("=========   输出：  ==================");
        System.out.println(student);
        int i=1;
        for (StudentTeacher st : student.getStudentTeachers()) {
            System.out.println("老师"+i+":"+st.getTeacher().getName());
            i++;
        }
        System.out.println("=====================================");
        MyBatisUtils.close(session);
    }

3.方式二:嵌套查询，分表查询的方式

1）创建数据模型Teacher和 Student 和 中间类 studentTeacher

public class Student {
    private Integer id;
    private  String name;
    private String gender;
    private Integer age;
    private String birthday;
    private List<StudentTeacher> studentTeachers;
}

public class Teacher {
    private Integer id;
    private String name;
    private List<StudentTeacher> studentTeachers;
}

public class StudentTeacher {
    private Integer sid;
    private Integer tid;
    private Student student;
    private Teacher teacher;
}

2）学生持久层StudentMapper接口中加入查询方法

public interface StudentMapper {

    Student getStudentById2(int i);

}

3）定义StudentMapper.xml配置文件

     <!--  根据学生id查 学生信息 和老师的信息 【嵌套查询，分表查询】  -->
    <select id="getStudentById2" parameterType="int" resultType="Student" resultMap="StudentResultMap2">
        select * from student where id=#{id}
    </select>

    <resultMap id="BaseResultMap" type="Student">
        <id column="id" property="id"></id>
        <result column="name" property="name"></result>
        <result column="gender" property="gender"></result>
        <result column="age" property="age"></result>
        <result column="s_birthday" property="birthday"></result>
    </resultMap>

    <resultMap id="StudentResultMap2" type="Student" extends="BaseResultMap">
        <collection property="studentTeachers" ofType="StudentTeacher" column="id"
                    select="com.chenshuang.mapper.StudentTeacherMapper.getStudentTeacherBySid"></collection>
    </resultMap>

4）定义 StudentTeacherMapper文件中的查询配置信息

public interface StudentTeacherMapper {
    StudentTeacher getStudentTeacherBySid(int sid);
}

5）定义StudentTeacherMapper.xml 配置文件

<resultMap id="BaseResultMap" type="StudentTeacher">
        <result column="st_sid" property="sid"></result>
        <result column="st_tid" property="tid"></result>
</resultMap>

<resultMap id="StudentTeacherResultMap" type="StudentTeacher" extends="BaseResultMap">
        <association property="teacher" javaType="Teacher" column="st_tid"
                     select="com.chenshuang.mapper.TeacherMapper.getTeacherById">
        </association>
</resultMap>

<select id="getStudentTeacherBySid" parameterType="int" resultType="StudentTeacher" resultMap="StudentTeacherResultMap">
        select * from student_teacher where st_sid=#{id}
</select>

6）定义 TeacherMapper文件中的查询配置信息

public interface TeacherMapper {
    Teacher getTeacherById(int id);
}

7）定义 TeacherMapper.xml 配置文件

<resultMap id="BaseResultMap" type="Teacher">
    <id column="t_id" property="id"></id>
    <result column="t_name" property="name"></result>
</resultMap>
    
<select id="getTeacherById" resultType="Teacher" parameterType="int" resultMap="BaseResultMap">
   select * from teacher where t_id=#{id}
</select>

6）测试方法

@Test
    // 根据学生id查 学生信息 和老师的信息 【嵌套查询，分表查询】
    public void getStudentById2(){
        SqlSession session = MyBatisUtils.getSession();
        StudentMapper mapper = session.getMapper(StudentMapper.class);
        Student student = mapper.getStudentById2(3);

        System.out.println("=========   输出：  ==================");
        System.out.println(student);
        int i=1;
        for (StudentTeacher st : student.getStudentTeachers()) {
            System.out.println("老师"+i+":"+st.getTeacher().getName());
            i++;
        }
        System.out.println("=====================================");
        MyBatisUtils.close(session);
    }