mysql数据库两个结果集相减

文章目录

• 1.当出现数据库的列字段与实体类的不一致时;需要手动配置字段映射;
• 2.两张表以上的多表映射;

• 完成查询所有的员工(附带地查到员工对应的部门);注意有一对一的关系;
• 完成查询所有的部门;(还要查到部门下的员工);对于部门来说,一对多的关系;()(一个部门关联着多个员工);

本次笔记整理 resultMap 的使用 ;

mybatis3.0的文档–>https://mybatis.org/mybatis-3/zh/sqlmap-xml.html

1.当出现数据库的列字段与实体类的不一致时;需要手动配置字段映射;

在之前的基础上,
这次直接给用户数据表添加一个新的字段;info表示信息;

但是,实体类这边我在添加这个属性时,故意把它的属性名定义为information

package com.xiaozhi.pojo;

/**
 * @author by CSDN@小智RE0
 * @date 2021-10-27 1:48
 * 实体类User(用户类)
 */
public class User {
    //属性,id,账户,密码,地址;
    private int id;
    private String account;
    private String password;
    private String addRess;

    //添加的信息属性; 注意这里的属性名和数据库的列字段名完全不一致;
    private String information;

    //构造方法;
    public User() {

    }

    public User(int id, String account, String password, String addRess, String information) {
        this.id = id;
        this.account = account;
        this.password = password;
        this.addRess = addRess;
        this.information = information;
    }

    //这里构造方法初始化时就没包括ID;
    public User(String account, String password, String addRess, String information) {
        this.account = account;
        this.password = password;
        this.addRess = addRess;
        this.information = information;
    }


    //为信息 information 添加getter setter方法;

    public String getInformation() {
        return information;
    }

    public void setInformation(String information) {
        this.information = information;
    }


    //getter,setter;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getAccount() {
        return account;
    }

    public void setAccount(String account) {
        this.account = account;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getAddRess() {
        return addRess;
    }

    public void setAddRess(String addRess) {
        this.addRess = addRess;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", account='" + account + '\'' +
                ", password='" + password + '\'' +
                ", addRess='" + addRess + '\'' +
                ", information='" + information + '\'' +
                '}';
    }
}

这里的持久层接口;本次笔记整理,就先把之前定义的方法去掉;保留一个查询所有用户的方法;

package com.xiaozhi.mapper;
import com.xiaozhi.pojo.User;
import java.util.List;
/**
 * @author by CSDN@小智RE0
 * @date 2021-10-24 22:23
 * 持久层映射接口
 */
public interface UserMapper {

    //查询所有用户;
    List<User> findUser();
}

持久层映射配置文件UserMapper.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.xiaozhi.mapper.UserMapper">

    <!--查询所有用户-->
    <select id="findUser" resultType="user">
        select * from t_user
    </select>
</mapper>

核心配置文件mybatis-config.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>
    <!--引入外部的资源文件-->
    <properties resource="database.properties">
    </properties>
    <settings>
        <!--配置日志-->
        <setting name="logImpl" value="LOG4J"/>
        
        <!--设置开启驼峰命名匹配-->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>

    <!--配置别名-->
    <typeAliases>
        <!--为实体类User配置别名为user-->
        <typeAlias type="com.xiaozhi.pojo.User" alias="user"/>
    </typeAliases>

    <!-- environments 环境配置 , 这里的default来指定默认使用的环境变量 -->
    <environments default="development">
        <!-- environment 环境变量 ;可以写多个,尽量保证id不重名就行-->
        <!--开发的环境变量-->
        <environment id="development">
            <!--事务管理-->
            <transactionManager type="JDBC">
            </transactionManager>
            <!--dataSource:数据源-->
            <dataSource type="UNPOOLED">
                <!--数据库驱动,url连接,数据库账户,密码-->
                <property name="driver" value="${driver}" />
                <property name="url" value="${url}"/>
                <property name="username" value="${username}" />
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>

    <!--配置mapper映射-->
    <mappers>
        <mapper resource="mapper/UserMapper.xml"/>
    </mappers>
</configuration>

外部数据库链接资源database.properties

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/day20211024_study_mybatis_db?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai
username=root
password=123456

这时,去测试的话;

//查询所有用户;
@Test
public  void findAllUser(){
    //使用工具类创建sqlSession;
    SqlSession sqlSession = MyBatisUtils.getSqlSession();
    //获取代理对象;
    UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
    //调用方法;
    List<User> user = userMapper.findUser();
    //以流的方式遍历;
    user.forEach(System.out::println);
    //关闭sqlSession;
    sqlSession.close();
}

查询时注意到info信息这一列查出来全是null空值

那么就得请出resultMap结果集映射了;它的ID是唯一标识符;
然后,它里面的<id>标签主要用来配置主键的映射;<result>标签配置非主键的映射;
column用来配置数据表的列字段名;然后property用来配置实体类的属性名;

那么只需要在UserMapper.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.xiaozhi.mapper.UserMapper">

    <!--可配置resultMap 进行数据库的列字段名与类的属性字段相对应-->
    <resultMap id="userMap" type="user">
        <!--ID,主键映射-->
        <id column="id" property="id"/>
        <!--其他列的映射-->
        <result column="account" property="account"/>
        <result column="password" property="password"/>
        <result column="add_ress" property="addRess"/>
        <result column="info" property="information"/>
    </resultMap>

    <!--查询所有用户; 这里的resultMap直接用id指向即可-->
    <select id="findUser" resultMap="userMap">
        select * from t_user
    </select>
</mapper>

同样地,再去查询所有用户,信息就有显示了

---

2.两张表以上的多表映射;

首先,去新建两张表;部门表;员工表

-- 创建部门表;
CREATE TABLE IF NOT EXISTS t_dept(
    `id` INT PRIMARY KEY AUTO_INCREMENT COMMENT 'Id',
    `name` VARCHAR(15) COMMENT '部门名称',
     -- 这里的操作人ID是来自于之前的用户表,
    `optionId` INT COMMENT '操作人Id' 
);

-- 创建员工表;
CREATE TABLE IF NOT EXISTS t_employee(
    `id` INT PRIMARY KEY AUTO_INCREMENT COMMENT 'ID',
    `name` VARCHAR(15) COMMENT '员工姓名',
    `sex` char(1),
     `deptID` INT COMMENT '部门ID',
     -- 这里的操作人ID是来自于之前的用户表,
     `optionId` INT COMMENT '操作人Id'
);

简单地存入一点数据;

部门表

员工表

OK,直接去pojo包下创建员工类;部门类;

package com.xiaozhi.pojo;

/**
 * @author by CSDN@小智RE0
 * @date 2021-10-29 19:49
 * 员工实体类;
 */
public class Employee {
    private int id;
    private String name;
    private String sex;
    //关联着部门和用户;
    private Dept dept;
    private User user;

    //构造方法;
    public Employee() {
    }

    public Employee(String name, String sex, Dept dept, User user) {
        this.name = name;
        this.sex = sex;
        this.dept = dept;
        this.user = user;
    }

    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 String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public Dept getDept() {
        return dept;
    }

    public void setDept(Dept dept) {
        this.dept = dept;
    }

    public User getUser() {
        return user;
    }

    public void setUser(User user) {
        this.user = user;
    }

    @Override
    public String toString() {
        return "Employee{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", sex='" + sex + '\'' +
                ", dept=" + dept +
                ", user=" + user +
                '}';
    }
}

部门类

package com.xiaozhi.pojo;

import java.util.List;
/**
 * @author by CSDN@小智RE0
 * @date 2021-10-29 19:41
 * 部门表;
 */
public class Dept {
    private int id;
    private String name;

    //关联员工;
    private List<Employee> employees;

    //关联操作的用户;
    private User user;

    public Dept() {
    
    }

    public Dept(String name, List<Employee> employees, User user) {
        this.name = name;
        this.employees = employees;
        this.user = user;
    }

    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 List<Employee> getEmployees() {
        return employees;
    }

    public void setEmployees(List<Employee> employees) {
        this.employees = employees;
    }

    public User getUser() {
        return user;
    }

    public void setUser(User user) {
        this.user = user;
    }

    @Override
    public String toString() {
        return "Dept{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", employees=" + employees +
                ", user=" + user +
                '}';
    }
}

在mybatis的核心配置文件mybatis-config.xml中先去配置别名

<!--配置别名-->
<typeAliases>
    <!--为实体类User配置别名为user-->
    <typeAlias type="com.xiaozhi.pojo.User" alias="user"/>
    <!--为实体类员工类配置别名-->
    <typeAlias type="com.xiaozhi.pojo.Employee" alias="employee"/>
    <!--为部门的实体类配置别名-->
    <typeAlias type="com.xiaozhi.pojo.Dept" alias="dept"/>
</typeAliases>

完成查询所有的员工(附带地查到员工对应的部门);注意有一对一的关系;

在mapper包下创建EmployeeMapper持久层接口;创建查询员工的方法;

package com.xiaozhi.mapper;

import com.xiaozhi.pojo.Employee;
import java.util.List;
/**
 * @author by CSDN@小智RE0
 * @date 2021-10-29 20:28
 */
public interface EmployeeMapper {
    //根据ID查询员工;
    Employee getEmpById(Integer id);

    //查询所有员工;
    List<Employee> getAllEmp();
}

在resources包下的mapper文件夹下创建EmployeeMapper.xml配置文件

这时,resultMap就要配置关联部门表和用户管理表;
用association即可;其中的property表示实体类中的属性名;而javaType表示实体类中定义属性的类型;

<?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.xiaozhi.mapper.EmployeeMapper">
    <!--定义映射关系map-->
    <resultMap id="empmap" type="employee">
        <id column="id" property="id"/>
        <result column="name" property="name"/>
        <result column="sex" property="sex"/>
        <!--关联部门表;property:在员工类中的定义属性变量名, javaType:表示在员工类中定义的部门类属性的类型(这里用dept是因为之前在核心配置文件中配置了别名为dept)-->
        <association property="dept" javaType="dept">
            <result column="dname" property="name"/>
        </association>

        <!--关联用户管理表-->
        <association property="user" javaType="user">
            <result column="account" property="account"/>
        </association>
    </resultMap>
    <select id="getEmpById" resultMap="empmap">
          SELECT
          e.`id`,
          e.`name`,
          e.`sex`,
          d.`name` dname ,
          u.`account`
          FROM t_employee e
          LEFT JOIN  t_dept d ON  e.`deptID`=d.`id`
          LEFT JOIN  t_user u ON  u.`id`=e.`optionId`
          where e.`id`=#{id}
    </select>
    <select id="getAllEmp" resultMap="empmap">
          SELECT
          e.`id`,
          e.`name`,
          e.`sex`,
          d.`name` dname ,
          u.`account`
          FROM t_employee e
          LEFT JOIN  t_dept d ON  e.`deptID`=d.`id`
          LEFT JOIN  t_user u ON  u.`id`=e.`optionId`
    </select>
</mapper>

别忘了,还要在核心配置文件mybatis-config.xml中配置mapper映射文件的路径

<!--配置mapper映射-->
<mappers>
    <mapper resource="mapper/UserMapper.xml"/>
    <mapper resource="mapper/EmployeeMapper.xml"/>
</mappers>

那么,测试试试看吧

package com.xiaozhi.mytest;

import com.xiaozhi.mapper.EmployeeMapper;
import com.xiaozhi.pojo.Employee;
import com.xiaozhi.utils.MyBatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.List;
/**
 * @author by CSDN@小智RE0
 * @date 2021-10-29 20:43
 */
public class TestEmployee {
    //根据Id查询员工;
    @Test
    public void getEmpByID(){
        //获取sqlSession
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        //获取代理对象;
        EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
        //根据ID查询员工;
        Employee emp = mapper.getEmpById(3);
        //控制台输出;
        System.out.println(emp);
        //关闭
        sqlSession.close();
    }

    //查询所有员工;
    @Test
    public void getAllEmp(){
        //获取sqlSession;
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        //获取代理类对象;
        EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
        //调用方法;
        List<Employee> allEmp = mapper.getAllEmp();
        //控制台输出;
        allEmp.forEach(System.out::println);
        //关闭;
        sqlSession.close();
    }
}

先执行getEmpByID()根据Id查询员工的方法;

其实;可以debug调试这个执行过程;

结果中直接是包含着部门类以及用户类的对象;然后查到需要的属性

在执行getAllEmp()查询所有的员工;

debug调试执行过程;

查到一块集合;其中每个员工的对象都包含这个一个对应的部门对象和用户对象;

完成查询所有的部门;(还要查到部门下的员工);对于部门来说,一对多的关系;()(一个部门关联着多个员工);

在定义部门类时,就已经考虑到了这一点;

OK,在mapper包下创建DeptMapper部门类的持久层接口;定义查询方法;

package com.xiaozhi.mapper;

import com.xiaozhi.pojo.Dept;

import java.util.List;
/**
 * @author by CSDN@小智RE0
 * @date 2021-10-29 20:50
 */
public interface DeptMapper {

    //根据ID查询部门;
    Dept getDeptById(Integer id);

    //查询所有部门;
    List<Dept> getAllDept();
}

那么在写mapper映射文件的时候,就得考虑考虑怎么关联这(一对多/多对一)的关系了;
使用collection进行集合关联;其中的javaType就得指明类中属性的类型 ;ofType得表示映射到list集合属性中的类的类型;

在resources包下的mapper文件夹下创建DeptMapper.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.xiaozhi.mapper.DeptMapper">
    
    <resultMap id="deptmap" type="dept">
        <id column="id" property="id"/>
        <result column="dname" property="name"/>
        
        <!--关联用户管理表;一对一-->
        <association property="user" javaType="user">
            <result column="account" property="account"/>
        </association>

        <!--一对多-->
        <!--包含员工表 javaType 类中属性的类型 为List;ofType表示映射到list集合属性中的类的类型部门类;-->
        <collection property="employees" javaType="list" ofType="employee">
            <result column="ename" property="name"/>
        </collection>
    </resultMap>
    <!--根据Id查询部门;-->
    <select id="getDeptById" resultMap="deptmap">
        SELECT
        d.`id`,
        d.`name` dname,
        e.`name` ename,
        u.`account`
        FROM t_dept d
        LEFT JOIN t_user u     ON u.`id`=d.`optionId`
        LEFT JOIN t_employee e ON e.`deptID`=d.`id`
        WHERE d.`id`=#{id}
    </select>


    <select id="getAllDept" resultMap="deptmap">
        SELECT
        d.`id`,
        d.`name` dname,
        e.`name` ename,
        u.`account`
        FROM t_dept d
        LEFT JOIN t_user u     ON u.`id`=d.`optionId`
        LEFT JOIN t_employee e ON e.`deptID`=d.`id`
    </select>
</mapper>

当然,也需要在核心配置文件mybatis-config.xml中配置映射文件的路径

<!--配置mapper映射-->
<mappers>
    <mapper resource="mapper/UserMapper.xml"/>
    <mapper resource="mapper/EmployeeMapper.xml"/>
    <mapper resource="mapper/DeptMapper.xml"/>
</mappers>

OK,测试;

package com.xiaozhi.mytest;
import com.xiaozhi.mapper.DeptMapper;
import com.xiaozhi.pojo.Dept;
import com.xiaozhi.pojo.Employee;
import com.xiaozhi.utils.MyBatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.List;
/**
 * @author by CSDN@小智RE0
 * @date 2021-10-29 21:42
 */
public class TestDept {
    //根据Id查询部门;
    @Test
    public void getDeptById(){
        //调用工具类;
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        
        DeptMapper mapper = sqlSession.getMapper(DeptMapper.class);
        Dept deptById = mapper.getDeptById(1);

        System.out.println("获取部门名->"+deptById.getName());
        System.out.println("---获取部门员工---");
        deptById.getEmployees().forEach(System.out::println);

        sqlSession.close();
    }

    //获取所有的部门;
    @Test
    public void getAllDepts(){
        //调用工具类;
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        
        DeptMapper mapper = sqlSession.getMapper(DeptMapper.class);
        //调用方法;
        List<Dept> allDept = mapper.getAllDept();

        for (Dept dept : allDept) {
            //获取部门名;
            System.out.println(dept.getName()+"----->");
            List<Employee> employees = dept.getEmployees();
            //获取部门下的员工名;
            for (Employee employee : employees) {
                System.out.println(employee.getName());
            }
        }
        //关闭;
        sqlSession.close();
    }
}

先试试执行根据Id查询部门getDeptById()

debug调试一下;
查询出来的员工对象存入一个集合中了

执行查询所有部门的方法getAllDepts()

debug调试;可看到查询的过程;查到部门类的集合了,然后每个部门类中又存放着员工类的集合;