MyBatis框架学习(六)-MyBatis的多表查询

前言:通过前几章的学习我们已经了解到MyBatis的基本的用法,基本的增删改查以及动态sql,其实这些已经满足我们的基本的需要,下面我们将继续接着上次的项目讲解MyBatis的高级结果映射,主要处理数据库的一对一查询,一对多查询,关系型数据库我们经常处理这种关系,列如:一个汽车对应一个引擎,一个汽车有四个或者多个轮子.
在权限管理系统中我们都可以用MyBatis解决这些复杂的问题,在面对这些关系的时候我们可能要写多个方法分别查询这些数据,然后再组合到一起,这种方式特别适合大型系统上,由于分库分表可以减少表之间的查询,方便系统进行扩展,但是在一般的企业中我们是可以轻松应对这种一对一和一对多的关系,

一、一对一映射

1.首先我们建立一个管理员表和部门表,它们之间是一对一的关系,在建一个员工表,部门和员工之间是一对多的关系

create table department(
dept_id int not null,
dept_name varchar(32) not null,
primary key(dept_id)
);

create table employee(
emp_id int not null auto_increment,
emp_name varchar(32) not null,
gender varchar(16) not null,
email varchar(100) null,
salary decimal(10, 2) default 0 not null,
job_title varchar(50) null,
dept_id int not null,
primary key(emp_id),
constraint emp_fk foreign key(dept_id) references department(dept_id)
);


create table admin(
dept_id int not null auto_increment,
name varchar(32) not null,
primary key(dept_id)
);

INSERT INTO `admin` VALUES ('1000', '曹操');
INSERT INTO `admin` VALUES ('1001', '刘备');
INSERT INTO `admin` VALUES ('1002', '赵云');
INSERT INTO `admin` VALUES ('1003', '黄忠');
INSERT INTO `admin` VALUES ('1004', '孙策');
INSERT INTO `admin` VALUES ('1005', '孙权');
INSERT INTO `admin` VALUES ('1006', '小乔');
INSERT INTO `admin` VALUES ('1007', '曹植');


INSERT INTO `department` VALUES ('1000', '董事局');
INSERT INTO `department` VALUES ('1001', '产品部');
INSERT INTO `department` VALUES ('1002', '财务部');
INSERT INTO `department` VALUES ('1003', '技术部');
INSERT INTO `department` VALUES ('1004', '行政部');
INSERT INTO `department` VALUES ('1005', '销售部');
INSERT INTO `department` VALUES ('1006', '客服部');
INSERT INTO `department` VALUES ('1007', '管理层');

INSERT INTO `employee` VALUES ('1', '刘青云', '男', 'gtl@studymysql.com', '20000.00', '总经理', '1000');
INSERT INTO `employee` VALUES ('2', '陈木胜', '男', 'cms@studymysql.com', '12000.00', '经理', '1000');
INSERT INTO `employee` VALUES ('3', '彭于晏', '男', 'pyy@studymysql.com', '12000.00', '经理', '1000');
INSERT INTO `employee` VALUES ('4', '吴京', '男', 'wj@studymysql.com', '12000.00', '经理', '1000');
INSERT INTO `employee` VALUES ('5', '袁泉', '女', 'yq@studymysql.com', '12000.00', '经理', '1000');
INSERT INTO `employee` VALUES ('6', '江疏影', '女', 'jsy@studymysql.com', '8000.00', '主管', '1000');
INSERT INTO `employee` VALUES ('7', '廖启智', '男', 'lqz@studymysql.com', '8000.00', '主管', '1000');
INSERT INTO `employee` VALUES ('8', '释彦能', '男', 'syn@studymysql.com', '2000.00', '业务员', '1000');
INSERT INTO `employee` VALUES ('9', '姜皓文', '男', 'jhw@studymysql.com', '2000.00', '业务员', '1000');
INSERT INTO `employee` VALUES ('10', '吴廷烨', '男', 'wth@studymysql.com', '2000.00', '业务员', '1000');
INSERT INTO `employee` VALUES ('11', '洪天照', '男', 'htz@studymysql.com', '2000.00', '业务员', '1000');
INSERT INTO `employee` VALUES ('12', '麦兆辉', '男', 'mzh@studymysql.com', '18000.00', '总经理', '1001');
INSERT INTO `employee` VALUES ('13', '古天乐', '男', 'gtl@studymysql.com', '16000.00', '经理', '1001');
INSERT INTO `employee` VALUES ('14', '吴彦祖', '男', 'wyz@studymysql.com', '17000.00', '经理', '1001');
INSERT INTO `employee` VALUES ('15', '曾江', '男', 'zj@studymysql.com', '11800.00', '主管', '1001');
INSERT INTO `employee` VALUES ('16', '叶璇', '女', 'yx@studymysql.com', '11800.00', '主管', '1001');
INSERT INTO `employee` VALUES ('17', '黄奕', '女', 'hy@studymysql.com', '11800.00', '主管', '1001');
INSERT INTO `employee` VALUES ('18', '陈木胜', '男', 'cl@studymysql.com', '26000.00', '总经理', '1002');
INSERT INTO `employee` VALUES ('19', '谢霆锋', '男', 'xtf@studymysql.com', '18000.00', '经理', '1002');
INSERT INTO `employee` VALUES ('20', '成龙', '男', 'cl@studymysql.com', '18000.00', '经理', '1002');
INSERT INTO `employee` VALUES ('21', '杨采妮', '女', 'ycn@studymysql.com', '9000.00', '主管', '1002');
INSERT INTO `employee` VALUES ('22', '蔡卓妍', '女', 'czy@studymysql.com', '5000.00', '主管', '1002');
INSERT INTO `employee` VALUES ('23', '安志杰', '男', 'azj@studymysql.com', '3000.00', null, '1002');
INSERT INTO `employee` VALUES ('24', '尹子维', '女', 'yzw@studymysql.com', '3000.00', null, '1002');
INSERT INTO `employee` VALUES ('25', '王杰', '男', 'wj@studymysql.com', '4000.00', '业务员', '1002');
INSERT INTO `employee` VALUES ('26', '郭富城', '男', 'master@mysql.com', '21000.00', '总经理', '1003');
INSERT INTO `employee` VALUES ('27', '梁家辉', '男', '', '18000.00', '经理', '1003');
INSERT INTO `employee` VALUES ('28', '李治廷', '男', '', '18000.00', '经理', '1003');
INSERT INTO `employee` VALUES ('29', '彭于晏', '男', '', '18000.00', '经理', '1003');
INSERT INTO `employee` VALUES ('30', '杨采妮', '女', '', '14000.00', '主管', '1003');
INSERT INTO `employee` VALUES ('31', '马伊琍', '女', '', '12000.00', '主管', '1003');
INSERT INTO `employee` VALUES ('32', '林家栋', '男', '', '17000.00', '主管', '1003');
INSERT INTO `employee` VALUES ('33', '安志杰', '男', '', '3000.00', null, '1003');
INSERT INTO `employee` VALUES ('34', '钱嘉乐', '男', '', '3000.00', null, '1003');
INSERT INTO `employee` VALUES ('35', '尹子维', '女', '', '3000.00', null, '1003');
INSERT INTO `employee` VALUES ('36', '王敏德', '女', '', '3000.00', null, '1003');
INSERT INTO `employee` VALUES ('37', '黄芝琪', '女', '', '4000.00', '销售', '1003');
INSERT INTO `employee` VALUES ('38', '郑欣宜', '女', '', '3000.00', null, '1003');
INSERT INTO `employee` VALUES ('39', '文峰', '男', '', '3000.00', '销售', '1003');
INSERT INTO `employee` VALUES ('40', 'Andy Liu', '男', 'andy.liu@studymysql.com', '5000.00', '销售', '1003');
INSERT INTO `employee` VALUES ('41', '何华超', '男', '', '5000.00', null, '1003');
INSERT INTO `employee` VALUES ('42', '洪金宝', '男', '', '6000.00', null, '1004');
INSERT INTO `employee` VALUES ('43', '洪金宝', '男', '', '27000.00', '总经理', '1004');
INSERT INTO `employee` VALUES ('44', '刘德华', '男', '', '2000.00', '经理', '1004');
INSERT INTO `employee` VALUES ('45', '徐克', '男', '1122', '16000.00', '经理', '1004');
INSERT INTO `employee` VALUES ('46', '朱雨辰', '女', '', '18000.00', '主管', '1004');
INSERT INTO `employee` VALUES ('47', '李勤勤', '女', '', '8000.00', '主管', '1004');
INSERT INTO `employee` VALUES ('48', '冯嘉怡', '女', '', '3000.00', null, '1004');
INSERT INTO `employee` VALUES ('49', '陈沛妍', '男', '', '3000.00', null, '1004');
INSERT INTO `employee` VALUES ('50', '胡军', '男', '', '5000.00', '业务员', '1004');
INSERT INTO `employee` VALUES ('51', '冯绍峰', '男', '', '2000.00', '实习生', '1004');
INSERT INTO `employee` VALUES ('52', '彭于晏', '男', '', '2000.00', '实习生', '1004');
INSERT INTO `employee` VALUES ('53', '宋佳', '女', '', '2000.00', '实习生', '1004');
INSERT INTO `employee` VALUES ('54', '元彪', '男', '', '2000.00', '实习生', '1004');
INSERT INTO `employee` VALUES ('55', '张家辉', '男', 'zjh@studymysql.com', '5000.00', '业务', '1000');
INSERT INTO `employee` VALUES ('56', '张小辉', '男', 'zxh@studymysql.com', '9000.00', '经理', '1000');
INSERT INTO `employee` VALUES ('57', '张学有辉', '男', 'zxyh@studymysql.com', '8000.00', '主管', '1002');

2.创建一个entity实体类
Employee类

package com.dqsy.mybatis.entity;

import java.math.BigDecimal;

public class Employee {
private int empId;
private String empName;
private String gender;
private String email;
private BigDecimal salary;
private String jobTitle;
private int deptId;
public int getEmpId() {
return empId;
}
public void setEmpId(int empId) {
this.empId = empId;
}
public String getEmpName() {
return empName;
}
public void setEmpName(String empName) {
this.empName = empName;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public BigDecimal getSalary() {
return salary;
}
public void setSalary(BigDecimal salary) {
this.salary = salary;
}
public String getJobTitle() {
return jobTitle;
}
public void setJobTitle(String jobTitle) {
this.jobTitle = jobTitle;
}
public int getDeptId() {
return deptId;
}
public void setDeptId(int deptId) {
this.deptId = deptId;
}
@Override
public String toString() {
return "Employee [empId=" + empId + ", empName=" + empName + ", gender=" + gender + ", email=" + email
+ ", salary=" + salary + ", jobTitle=" + jobTitle + ", deptId=" + deptId + "]";
}
}

Admin类

package com.dqsy.mybatis.entity;

public class Admin {
private int deptId;
private String name;
public int getDeptId() {
return deptId;
}
public void setDeptId(int deptId) {
this.deptId = deptId;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
@Override
public String toString() {
return "Admin [deptId=" + deptId + ", name=" + name + "]";
}

}

Department类

package com.dqsy.mybatis.entity;

public class Department {
private int deptId;
private String deptName;
public int getDeptId() {
return deptId;
}
public void setDeptId(int deptId) {
this.deptId = deptId;
}
public String getDeptName() {
return deptName;
}
public void setDeptName(String deptName) {
this.deptName = deptName;
}
@Override
public String toString() {
return "Department [deptId=" + deptId + ", deptName=" + deptName + "]";
}
}

3.创建**Mapper接口和**Mapper.xml

package com.dqsy.mybatis.mapper;

import java.util.List;

import com.dqsy.mybatis.dto.EmpDto;
import com.dqsy.mybatis.entity.Employee;

public interface EmployeeMapper {
public List<Employee> getEmpByEmpDto(EmpDto empDto);
}

<?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.dqsy.mybatis.mapper.EmployeeMapper">
<!-- sql片段 -->
<sql id="sql_collection">
emp_id,emp_name,gender,email,salary,job_title,dept_id
</sql>
<select id="getEmpByEmpDto" resultType="employee">
select 
<include refid="sql_collection"></include>
from employee
<!--where 标签有两种用途:1.添加where关键字,2.判断第一个不需要and  -->
<where>
<if test="employee.empName != null">
and emp_name like '${employee.empName}%'
</if>
<if test="employee.gender != null">
and gender = #{employee.gender}
</if>
<!-- and dept_id in(1000,1001,1002)
collection：输入参数的属性
open:循环前的符号
close:循环后的符号
separator:分隔符
 -->
<if test="ids != null">
<foreach collection="ids" open="dept_id in(" close=")" 
item="id" separator=",">
#{id}
</foreach>
</if>
</where>
</select>
</mapper>

package com.dqsy.mybatis.mapper;

import com.dqsy.mybatis.entity.Admin;

public interface AdminMapper {
public void addAdmin(Admin admin);
}

<?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.dqsy.mybatis.mapper.AdminMapper">
<insert  id="addAdmin" keyProperty="admin">
<selectKey keyProperty="deptId" keyColumn="dept_id" resultType="int" order="AFTER">
select last_insert_id() as deptId
</selectKey>
insert into admin(name) values(#{name})
</insert>
</mapper>

4.创建一个包装类dto(Empdto.java)

package com.dqsy.mybatis.dto;

import java.util.List;

import com.dqsy.mybatis.entity.Employee;

public class EmpDto {
private Employee employee;
private List<Integer> ids;
public List<Integer> getIds() {
return ids;
}

public void setIds(List<Integer> ids) {
this.ids = ids;
}

public Employee getEmployee() {
return employee;
}

public void setEmployee(Employee employee) {
this.employee = employee;
}
}

5.创建一个MyBatisTest测试文件

package com.dqsy.mybatis.test;


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

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.Before;
import org.junit.Test;

import com.dqsy.mybatis.dto.EmpDto;
import com.dqsy.mybatis.entity.Admin;
import com.dqsy.mybatis.entity.Employee;
import com.dqsy.mybatis.mapper.AdminMapper;
import com.dqsy.mybatis.mapper.EmployeeMapper;


public class MyBatisTest {
private SqlSessionFactory sqlSessionFactory;
@Before
public void init() throws IOException{
String configFile = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(configFile);
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
}
@Test
public void TestGetUserById(){
SqlSession sqlSession = sqlSessionFactory.openSession();
EmployeeMapper empMapper = sqlSession.getMapper(EmployeeMapper.class);
EmpDto empDto = new EmpDto();
Employee employee = new Employee();
employee.setEmpName("吴");
employee.setGender("男");
empDto.setEmployee(employee);
List<Integer> ids = new ArrayList<Integer>();
ids.add(1000);
ids.add(1001);
ids.add(1002);
List<Employee> emps = empMapper.getEmpByEmpDto(empDto);
for(Employee emp : emps){
System.out.println(emp);
}
sqlSession.close();
}
@Test
public void TestSelectKey(){
SqlSession sqlSession = sqlSessionFactory.openSession();
AdminMapper adminMapper = sqlSession.getMapper(AdminMapper.class);
Admin  admin= new Admin();
admin.setName("大庆师范");
adminMapper.addAdmin(admin);
sqlSession.commit();
sqlSession.close();
}
}

至此一个简单的框架搭建成功,下面我们将继续学习高级映射
一对一映射方法一:
在这我们先创建一个AdminMapper.java的接口

编写一个一对一的sql语句

查询语句

结果:

方法二:主要适用不规则的字段,或者特定的字段.
常见一个DepartmentMapper的接口

编写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="com.dqsy.mybatis.mapper.DepartmentMapper">
<!-- 主表的映射 -->
<resultMap type="Department" id="adminResultMap">
<id column="dept_id" property="deptId"/>
<result column="dept_name" property="deptName"/>
<!-- 一对一的从表 
association:用于一对一,多对一,那个一的映射
property:Department对象里新加的那个admin的属性名称
javaType:属性的类型
-->
<association property="admin" javaType="admin">
<id column="dept_id" property="deptId"/>
<result column="name" property="name"/>
</association>
</resultMap>

<!-- 一对一方法二 -->
<select id="getDepartmentTypeTwo" resultMap="adminResultMap">
select d.dept_id,d.dept_name,a.name
from admin a inner join department d on a.dept_id=d.dept_id
</select>
</mapper>

测试

结果

这就是用resultMap配置一对一的映射.

关联嵌套结果映射,需要关联多个表,将所需要的值一次性取出来,这种方式的好处是减少数据库的查询次数,减轻数据库的压力,缺点则是需要写复杂的sql语句,并且当嵌套结果复杂时不容易一次性写正确,由于要在应用服务器上将结果映射到不同的类上,因此会增加服务器的压力,当一定会使用嵌套结果,并且整个复杂的sql执行速度很快时,则建议使用嵌套查询.

二、一对多映射

上面我们用两种方法实现了一对一的映射,下面我们将继续学习collection集合嵌套的结果映射.
首先创建一个DepartmentMapper的接口

编写映射文件(一对多方法一)

修改Department的实体类

编写测试文件

结果

注意在这里emps集合里包含这个部门的所有成员信息,注意在这以面向对象的方式对待.
方法二:就是应用,一对一方法一的理论进行的.