09【MyBatis多表关联查询】

三、MyBatis多表关联查询

3.1 表的关系

表关系有如下几种

• 一对多

• 多对一（MyBatis就是一对一）

• 一对一

• 多对多（一对多+一对多）

• 举例一：

一对多：一个部门下有多个员工

部门与员工的关系：一对多

多对一：多个员工属于一个部门。

员工与部门的关系：多对一

单独拿出一个员工来说，他都只能属于一个部门。所以，在mybatis中把多对一看成了一对一。

• 举例二：

一个用户对应一个身份证号码。那么用户与身份证号码关系就是一对一

• 举例三：

多对多：一个老师教多个学生。

老师与学生的关系：一对多

一个学生有多个老师。

学生与老师：一对多，这种双向一对多，就是多对多。

3.2 一对一查询

3.2.1 搭建环境

• SQL脚本：

drop table if exists dept;

CREATE TABLE `dept`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '部门名称',
  `location` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '部门地址',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 ;

INSERT INTO `dept` VALUES (1, '研发部', '中国台湾');
INSERT INTO `dept` VALUES (2, '市场部', '中国香港');
INSERT INTO `dept` VALUES (3, '行政部', '中国钓鱼岛');
INSERT INTO `dept` VALUES (4, '销售部', '中国江西');

drop table if exists emp;
CREATE TABLE `emp`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '员工姓名',
  `age` int(11) NULL DEFAULT NULL COMMENT '员工年龄',
  `addr` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '籍贯',
  `salary` decimal(10, 2) NULL DEFAULT NULL COMMENT '薪资',
  `dept_id` int(11) NULL DEFAULT NULL COMMENT '部门id',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `emp` VALUES (1, '张三', 20, '广西来宾', 7600.00, 1);
INSERT INTO `emp` VALUES (2, '李四', 22, '浙江绍兴', 6800.00, 4);
INSERT INTO `emp` VALUES (3, '小明', 25, '广东云浮', 6600.00, 2);
INSERT INTO `emp` VALUES (4, '小红', 23, '河南信阳', 7000.00, 3);
INSERT INTO `emp` VALUES (5, '张明', 25, '山东临沂', 8000.00, 1);

Emp实体类：

package com.dfbz.entity;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

/**
 * @author lscl
 * @version 1.0
 * @intro:
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Emp {
    private Integer id;
    private String name;
    private Integer age;
    private String addr;
    private Double salary;

    // 一个员工属于一个部门
    private Dept dept;
}

Dept实体类：

package com.dfbz.entity;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.util.List;

/**
 * @author lscl
 * @version 1.0
 * @intro:
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Dept {
    private Integer id;
    private String name;
    private String location;

    // 一个部门下有多个员工
    private List<Emp> empList;
}

3.2.2 需求分析

表关系：

我们以前的理解是，部门和员工的关系是一对多，员工和部门的关系是多对一；

但是我们拿出单个员工来说，一个员工只属于一个部门，因此在MyBatis中，多对一的关系简化成了一对一；

案例需求：查询所有员工信息，关联查询所属部门信息；在查询过程中，即使有的员工还没有部门也需要查询这个员工的信息，此时在查询过程中，员工表应该是主表；

• 分析SQL语句：

select e.*,d.* from emp e left join dept d on e.dept_id=d.id

3.2.3 dao接口

• EmpDao：

public interface EmpDao {
	List<Emp> findAll();
}

3.2.3 mapper.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="com.dfbz.dao.EmpDao">

    <select id="findAll" resultType="emp">
        select e.*,d.* from emp e left join dept d on e.dept_id=d.id
    </select>
</mapper>

3.2.4 测试

package com.dfbz.test;

import com.dfbz.dao.EmpDao;
import com.dfbz.entity.Emp;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

/**
 * @author lscl
 * @version 1.0
 * @intro:
 */
public class Demo01 {

    private SqlSessionFactory factory;
    private SqlSession session;
    private EmpDao empDao;

    @Before
    public void before() throws IOException {
        InputStream is = Resources.getResourceAsStream("SqlMapConfig.xml");
        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
        factory = builder.build(is);
        session = factory.openSession(true);
        empDao=session.getMapper(EmpDao.class);
    }

    @After
    public void after() throws IOException {
        session.close();
    }

    @Test
    public void test1() {
        List<Emp> empList = empDao.findAll();

        System.out.println(empList);
    }
}

3.2.5 配置MyBatis一对一关系

• 更改SQL语句：

SELECT
	e.id eId,
	e.NAME empName,
	e.age,
	e.addr,
	e.salary,
	e.dept_id deptId,
	d.NAME deptName,
	d.location 
FROM
	emp e
	left JOIN dept d ON e.dept_id = d.id

1）传统映射：

<?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="com.dfbz.dao.EmpDao">

    <resultMap id="empResultMap" type="emp">
        <id column="eId" property="id"></id>
        <result column="empName" property="name"></result>
        <result column="addr" property="addr"></result>
        <result column="salary" property="salary"></result>
        <result column="deptId" property="dept.id"></result>
        <result column="deptName" property="dept.name"></result>
        <result column="location" property="dept.location"></result>
    </resultMap>
    <select id="findAll" resultMap="empResultMap">
        SELECT
        e.id eId,
        e.NAME empName,
        e.addr,
        e.salary,
        e.dept_id deptId,
        d.name deptName,
        d.location
    FROM
        emp e
        LEFT JOIN dept d ON e.dept_id = d.id
    </select>
</mapper>

2）使用association标签映射

<resultMap id="empResultMap" type="emp">
    <id column="eId" property="id"></id>
    <result column="empName" property="name"></result>
    <result column="addr" property="addr"></result>
    
    <!--在使用关系映射时,即使查询出来的列明和实体类的属性名一致,也要手动映射-->
    <result column="salary" property="salary"></result>
    <association property="dept" javaType="com.dfbz.entity.Dept">
        <id column="deptId" property="id"></id>
        <result column="deptName" property="name"></result>
        <!--在使用关系映射时,即使查询出来的列明和实体类的属性名一致,也要手动映射-->
        <result column="location" property="location"></result>
    </association>
    
</resultMap>
<select id="findAll" resultMap="empResultMap">
    SELECT
    e.id eId,
    e.NAME empName,
    e.addr,
    e.salary,
    d.id deptId,
    d.name deptName,
    d.location
FROM
    emp e
    LEFT JOIN dept d ON e.dept_id = d.id
</select>

Tips：映射多表关联的结果集时，即使查询出来的列名与属性名一致，也要建立手动映射关系，否则将会导致查询的值无法映射到对应的列；

3.3 一对多查询

3.3.1 需求分析

需求：查询部门信息以及部门下面所属员工信息

分析：部门和员工的信息为一对多关系，并且查询过程中，如果有的部门没有员工，此时也需要将部门信息查询出来，因此在查询中，部门表应该是主表；

• 分析SQL语句：

SELECT
	e.id eId,
	e.NAME empName,
	e.addr,
	e.salary,
	e.dept_id deptId,
	d.NAME deptName,
	d.location 
FROM
	emp e
	right JOIN dept d ON e.dept_id = d.id

3.3.2 dao接口

List<Dept> findAll();

3.3.3 mapper.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="com.dfbz.dao.DeptDao">

    <resultMap id="deptResultMap" type="dept">
        <id column="deptId" property="id"></id>
        <result column="deptName" property="name"></result>
        
        <!-- 在使用关系映射时,即使查询出来的列明和实体类的属性名一致,也要手动映射 -->
        <result column="location" property="location"></result>

        <!--
            property: Dept对象中的属性
            ofType:集合中的泛型类型
        -->
        <collection property="empList" ofType="com.dfbz.entity.Emp">
            <id column="eId" property="id"></id>
            <result column="empName" property="name"></result>
            <result column="addr" property="addr"></result>
                
            <!-- 在使用关系映射时,即使查询出来的列明和实体类的属性名一致,也要手动映射 -->
            <result column="salary" property="salary"></result>
        </collection>
    </resultMap>
    <select id="findAll" resultMap="deptResultMap">
        SELECT
            e.id eId,
            e.NAME empName,
            e.addr,
            e.salary,
            e.dept_id deptId,
            d.NAME deptName,
            d.location
        FROM
            emp e
            right JOIN dept d ON e.dept_id = d.id
    </select>
</mapper>

3.3.4 测试

@Test
public void test2() {
    List<Dept> deptList = deptDao.findAll();
    System.out.println(deptList);
}

3.4 多对多关联

3.4.1 需求分析

示例：用户和角色

​ 一个用户可以有多个角色

​ 一个角色可以赋予多个用户

步骤：

​ 1、建立两张表：用户表，角色表让用户表和角色表具有多对多的关系。需要使用中间表，中间表需要外键引用用户表、角色表。

​ 2、建立两个实体类：用户实体类和角色实体类让用户和角色的实体类能体现出来多对多的关系各自包含对方一个集合引用

​ 3、建立两个配置文件用户的配置文件角色的配置文件

​ 4、实现配置：

​ 当我们查询用户时，可以同时得到用户所包含的角色信息

​ 当我们查询角色时，可以同时得到角色的所赋予的用户信息

3.4.2 建立表

DROP TABLE IF EXISTS `role`;
CREATE TABLE `role`  (
  `id` int(11) NOT NULL,
  `role_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `role_desc` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `id`(`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `role` VALUES (1, '保洁', '扫地');
INSERT INTO `role` VALUES (2, '厨师', '做饭');
INSERT INTO `role` VALUES (3, '保安', '维护治安');

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `birthday` date NULL DEFAULT NULL,
  `sex` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `address` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `user` VALUES (1, '张三', '1999-02-04', '男', '佛山');
INSERT INTO `user` VALUES (2, '李四', '1998-01-15', '女', '湛江');

DROP TABLE IF EXISTS `user_role`;
CREATE TABLE `user_role`  (
  `uid` int(11) NOT NULL COMMENT '用户编号',
  `rid` int(11) NOT NULL COMMENT '角色编号',
  PRIMARY KEY (`uid`, `rid`) USING BTREE,
  INDEX `FK_Reference_10`(`rid`) USING BTREE,
  CONSTRAINT `FK_Reference_10` FOREIGN KEY (`rid`) REFERENCES `role` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `FK_Reference_9` FOREIGN KEY (`uid`) REFERENCES `user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `user_role` VALUES (1, 1);
INSERT INTO `user_role` VALUES (2, 1);
INSERT INTO `user_role` VALUES (1, 2);
INSERT INTO `user_role` VALUES (2, 2);
INSERT INTO `user_role` VALUES (2, 3);

3.4.3 实体类

• User：

package com.dfbz.entity;


import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.util.Date;
import java.util.List;

/**
 * @author lscl
 * @version 1.0
 * @intro:
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
public class User {
    private int  id;
    private String username;
    private Date birthday;
    private String sex;
    private String address;

    // 一个用户，有多个角色
    private List<Role> roles;
}

• Role：

package com.dfbz.entity;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.util.List;

/**
 * @author lscl
 * @version 1.0
 * @intro:
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Role {
    private int  roleId;
    private String roleName;
    private String roleDesc;

    // 一个角色，对应多个用户
    private List<User> users;
}

3.4.4 dao

• UserDao：

public interface UserDao {
    List<User> findAll();
}

• RoleDao：

public interface RoleDao {
    List<Role> findAll();
}

3.4.5 实现User到Role的多对多

• SQL语句：

SELECT
	u.*,
	r.id rid,
	r.role_name,
	r.role_desc 
FROM
	USER u
	LEFT JOIN user_role ur ON u.id = ur.uid
	LEFT JOIN role r ON r.id = ur.rid

• mapper.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="com.dfbz.dao.UserDao">
    <resultMap id="userResultMap" type="user">

        <id property="id" column="id"></id>
        <result property="username" column="username"></result>
        <result property="birthday" column="birthday"></result>
        <result property="sex" column="sex"></result>
        <result property="address" column="address"></result>

        <collection property="roles" ofType="role">
            <id property="roleId" column="rid"></id>
            <result property="roleName" column="role_name"></result>
            <result property="roleDesc" column="role_desc"></result>
        </collection>
    </resultMap>
    <select id="findAll" resultMap="userResultMap">
    select
        u.*,
        r.id rid,
        r.role_name,
        r.role_desc
    
        from user u
        left join user_role ur on u.id=ur.uid
        left join role r on r.id=ur.rid
    </select>
</mapper>

测试类：

@Test
public void test3() {
    List<User> userList = userDao.findAll();
    System.out.println(userList);
}

3.4.6 实现Role到User的多对多

• SQL语句：

SELECT
	r.*,
	u.id uid,
	u.username,
	u.birthday,
	u.sex,
	u.address 
FROM
	role r
	LEFT JOIN user_role ur ON r.id = ur.rid
	LEFT JOIN USER u ON u.id = ur.uid

• mapper.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="com.dfbz.dao.RoleDao">

    <resultMap id="roleResultMap" type="role">
        <id property="roleId" column="id"></id>
        <result property="roleName" column="role_name"></result>
        <result property="roleDesc" column="role_desc"></result>
        <collection property="users" ofType="user">
            <id property="id" column="uid"></id>
            <result property="username" column="username"></result>
            <result property="birthday" column="birthday"></result>
            <result property="sex" column="sex"></result>
            <result property="address" column="address"></result>
        </collection>
    </resultMap>

    <select id="findAll" resultMap="roleResultMap">
        select
            r.*,
            u.id uid,
            u.username,
            u.birthday,
            u.sex,
            u.address

        from role r
            left join user_role ur on r.id=ur.rid
            left join user u on u.id = ur.uid
    </select>
</mapper>

• 测试类：

@Test
public void test4() {
    List<Role> roleList = roleDao.findAll();
    System.out.println(roleList);
}