本篇Mybatis框架学习笔记;紧跟之前的学习
Mybatis框架学习笔记(3)–>

本次笔记整理 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;}@Overridepublic 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;}@Overridepublic 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;}@Overridepublic 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">SELECTe.`id`,e.`name`,e.`sex`,d.`name` dname ,u.`account`FROM t_employee eLEFT 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">SELECTe.`id`,e.`name`,e.`sex`,d.`name` dname ,u.`account`FROM t_employee eLEFT 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查询员工;@Testpublic void getEmpByID(){//获取sqlSessionSqlSession sqlSession = MyBatisUtils.getSqlSession();//获取代理对象;EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);//根据ID查询员工;Employee emp = mapper.getEmpById(3);//控制台输出;System.out.println(emp);//关闭sqlSession.close();}//查询所有员工;@Testpublic 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">SELECTd.`id`,d.`name` dname,e.`name` ename,u.`account`FROM t_dept dLEFT 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">SELECTd.`id`,d.`name` dname,e.`name` ename,u.`account`FROM t_dept dLEFT 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查询部门;@Testpublic 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();}//获取所有的部门;@Testpublic 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调试;可看到查询的过程;查到部门类的集合了,然后每个部门类中又存放着员工类的集合;