7、MyBatis框架——MyBatis对一对一关系的处理、分步查询、MyBatis对一对多关系的处理

目录

一、项目框架搭建

二、在实体类中添加额外属性实现多表查询

1、mybatis两表关联查询

（1）实体类类型映射规则

（2）代码演示

2、分步查询

（1）autoMapping开启自动映射

（2）封装SQL语句

（2）懒加载

三、MyBatis对一对多关系的处理

1、collection配置集合映射

2、代码演示 

3、规范mapper映射文件

---

一、项目框架搭建

（1）准备两个数据库表：员工表和部门表

CREATE DATABASE mybatisdatabase;
USE mybatisdatabase;CREATE TABLE emp(
id INT PRIMARY KEY AUTO_INCREMENT COMMENT '员工编号',
ename VARCHAR(20) NOT NULL COMMENT '员工姓名',
age INT NOT NULL COMMENT '年龄',
deptno INT NOT NULL COMMENT '部门编号'
);INSERT INTO emp(ename,age,deptno) VALUES
('tom',18,1),
('jack',20,1),
('小黑',19,2),
('老默',31,2),
('启强',24,2);CREATE TABLE dept(
id INT PRIMARY KEY AUTO_INCREMENT COMMENT '部门编号',
dept_name VARCHAR(20) NOT NULL COMMENT '部门名称',
`local` VARCHAR(20) NOT NULL COMMENT '部门地址'
);INSERT INTO dept(dept_name,`local`) VALUES
('市场部','安徽合肥'),
('财务部','江苏南京'),
('生产部','安徽芜湖');

（2）新建module--->java框架Maven工程--->完善工程目录

（3）在pom.xml中添加需要使用的依赖

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"><modelVersion>4.0.0</modelVersion><groupId>com.mybatis</groupId><artifactId>mybatis04</artifactId><version>1.0-SNAPSHOT</version><packaging>jar</packaging><name>mybatis04</name><url>http://maven.apache.org</url><properties><project.build.sourceEncoding>UTF-8</project.build.sourceEncoding></properties><dependencies><dependency><groupId>junit</groupId><artifactId>junit</artifactId><version>4.12</version><scope>test</scope></dependency><dependency><groupId>org.mybatis</groupId><artifactId>mybatis</artifactId><version>3.5.11</version></dependency><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>5.1.48</version></dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><version>1.18.24</version></dependency><dependency><groupId>log4j</groupId><artifactId>log4j</artifactId><version>1.2.17</version></dependency></dependencies>
</project>

（4）创建实体类和Mapper接口 

package com.mybatis.entity;import lombok.Data;@Data
public class Emp {private long id;private String ename;private long age;private long deptno;}

package com.mybatis.entity;import lombok.Data;@Data
public class Dept {private long id;private String deptName;private String local;}

（5）在resources目录下新建config文件，存放mybatis全局配置文件和外部数据源

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configurationPUBLIC "-//mybatis.org//DTD Config 3.0//EN""http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration><!--    引入外部数据源参数--><properties resource="config/jdbc.properties"></properties><settings><!--        开启驼峰映射--><setting name="mapUnderscoreToCamelCase" value="true"/><!--        开启日志打印--><setting name="logImpl" value="LOG4J"/></settings><!--    给表起别名--><typeAliases><package name="com.mybatis.entity"/></typeAliases><environments default="development"><environment id="development"><transactionManager type="JDBC"/><dataSource type="POOLED"><property name="driver" value="${jdbc.driver}"/><property name="url" value="${jdbc.url}"/><property name="username" value="${jdbc.username}"/><property name="password" value="${jdbc.password}"/></dataSource></environment></environments><mappers><package name="com.mybatis.mapper"/></mappers>
</configuration>

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://127.0.0.1:3306/mybatisdatabase
jdbc.username=root
jdbc.password=123456

（6）在resources目录下新建与Mapper接口层级相同的文件夹存放Mapper映射文件 

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.mybatis.mapper.EmpMapper"></mapper>

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.mybatis.mapper.DeptMapper"></mapper>

（7）在resources目录下添加日志配置文件log4j.properties 

#打印日志的级别：可控制打印信息，哪些打印，哪些不打印
#Console：打印窗口
log4j.rootLogger=DEBUG,Console
#Console
log4j.appender.Console=org.apache.log4j.ConsoleAppender
log4j.appender.console.Target=System.out
#设置打印格式
log4j.appender.Console.layout=org.apache.log4j.PatternLayout
#设置打印信息
log4j.appender.Console.layout.ConversionPattern=%d [%t] %-5p [%c] - %m%n
#打印日志级别：设置打印级别只要不是ERROR级别就不打印
log4j.logger.org.apache=ERROR
log4j.logger.org.mybatis=ERROR
log4j.logger.org.springframework=ERROR
#这个需要
log4j.logger.log4jdbc.debug=ERROR
log4j.logger.com.gk.mapper=ERROR
log4j.logger.jdbc.audit=ERROR
log4j.logger.jdbc.resultset=ERROR
#这个打印SQL语句非常重要
log4j.logger.jdbc.sqlonly=DEBUG
log4j.logger.jdbc.sqltiming=ERROR
log4j.logger.jdbc.connection=FATAL

二、在实体类中添加额外属性实现多表查询

package com.mybatis.entity;import lombok.Data;@Data
public class Emp {private long id;private String ename;private long age;private long deptno;//添加额外属性private Dept dept;}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.mybatis.mapper.EmpMapper"><select id="select" resultType="emp">select * from emp</select>
</mapper>

package com.mybatis.mapper;import com.mybatis.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.Before;
import org.junit.Test;import java.io.IOException;
import java.io.InputStream;
import java.util.List;import static org.junit.Assert.*;public class EmpMapperTest {SqlSessionFactory sqlSessionFactory = null;@Beforepublic void init(){System.out.println("init()被执行");InputStream resourceAsStream = null;try {resourceAsStream = Resources.getResourceAsStream("config/mybatis-config.xml");} catch (IOException e) {e.printStackTrace();}sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);}@Testpublic void select() {//创建SqlSession会话SqlSession sqlSession = sqlSessionFactory.openSession();//获取EmpMapper接口的动态代理对象EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);//通过接口调用方法List<Emp> select = empMapper.select();for (Emp emp:select){System.out.println("emp = " + emp);}//关闭资源sqlSession.close();}
}

 dept是空的，因为目前只查询了员工表（emp）

1、mybatis两表关联查询

查询的结果只有员工表(emp)的数据，deft依旧为空，dept为实体类对象，mybatis目前还不能自动赋值 

（1）实体类类型映射规则

association标签：连接两个表，获取多个表中的信息，以及在一对一、多对多的关系中获取相关数据

        property：实体类对象

<resultMap id="empMap" type="emp"><id property="id" column="id"></id><result column="ename" property="ename"></result><result property="age" column="age"></result><result column="deptno" property="deptno"></result><association property="dept"><id column="did" property="id"></id><result property="deptName" column="dept_name"></result><result property="local" column="local"></result></association></resultMap>

（2）代码演示

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.mybatis.mapper.EmpMapper"><resultMap id="empMap" type="emp"><id property="id" column="id"></id><result column="ename" property="ename"></result><result property="age" column="age"></result><result column="deptno" property="deptno"></result><association property="dept"><id column="did" property="id"></id><result property="deptName" column="dept_name"></result><result property="local" column="local"></result></association></resultMap><select id="selectByEmpJoinDept" resultMap="empMap">SELECT emp.*,dept.id AS did,dept.dept_name,dept.`local` FROM emp JOIN dept ON emp.deptno = dept.id</select>
</mapper>

package com.mybatis.mapper;import com.mybatis.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.Before;
import org.junit.Test;import java.io.IOException;
import java.io.InputStream;
import java.util.List;import static org.junit.Assert.*;public class EmpMapperTest {//创建SqlSessionFactory工厂对象SqlSessionFactory sqlSessionFactory = null;@Beforepublic void init(){System.out.println("init()");InputStream resourceAsStream;try {resourceAsStream = Resources.getResourceAsStream("config/mybatis-config.xml");} catch (IOException e) {throw new RuntimeException(e);}sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);}@Testpublic void selectByEmpJoinDept() {//创建SqlSession会话SqlSession sqlSession = sqlSessionFactory.openSession();//获取EmpMapper动态代理对象EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);//通过接口调用方法List<Emp> empList = empMapper.selectByEmpJoinDept();for (Emp emp:empList){System.out.println("emp = " + emp);}//关闭资源sqlSession.close();}
}

2、分步查询

在做多表查询时，有时我们不需要所有表的数据，但一条SQL语句会查询出所有表的数据,大大降低了数据库的性能，我们可根据分步查询解决这个弊端

（1）autoMapping开启自动映射

自动映射默认是关闭的（但代码运行时也会自动开启），可以通过设置autoMapping的属性值为“true”开启自动映射，不能够完成自动映射的字段，会按照已设置的映射规则进行映射 

<resultMap id="empMap" type="emp" autoMapping="true"><association property="dept"><id column="did" property="id"></id><result property="deptName" column="dept_name"></result><result property="local" column="local"></result></association></resultMap>

（2）封装SQL语句

我们可通过封装从表数据的方式，在需要从表的数据时查询从表，以此来实现分步查询。

在association标签中使用select属性和column属性：

        select：指定一条SQL语句；

        column：指定主表的哪一字段作为参数传递 

（2）懒加载

上面我们使用的SQL语句无论你是否需要关联表（dept）中的数据，都会去查询关联表中的数据，当我们只需要emp表中的数据时也会去查询dept表，降低了数据库的性能

按需加载，先从表单查询，需要时再从关联表去关联查询，能大大提升数据库性能

在association标签中设置fetchType属性值为"lazy"开启懒加载，分步查询正式完成 

package com.mybatis.mapper;import com.mybatis.entity.Emp;import java.util.List;public interface EmpMapper {List<Emp> select();}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.mybatis.mapper.EmpMapper"><resultMap id="empMap" type="emp" autoMapping="true"><association property="dept" select="deptById" column="deptno" fetchType="lazy"><id column="did" property="id"></id><result property="deptName" column="dept_name"></result><result property="local" column="local"></result></association></resultMap><select id="select" resultMap="empMap">select * from emp</select><select id="deptById" resultType="dept">select * from dept where id = #{deptno}</select></mapper>

package com.mybatis.mapper;import com.mybatis.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.Before;
import org.junit.Test;import java.io.IOException;
import java.io.InputStream;
import java.util.List;import static org.junit.Assert.*;public class EmpMapperTest {//创建SqlSessionFactory工厂对象SqlSessionFactory sqlSessionFactory = null;@Beforepublic void init(){System.out.println("init()");InputStream resourceAsStream;try {resourceAsStream = Resources.getResourceAsStream("config/mybatis-config.xml");} catch (IOException e) {throw new RuntimeException(e);}sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);}@Testpublic void select() {//创建SqlSession会话SqlSession sqlSession = sqlSessionFactory.openSession();//获取EmpMapper动态代理对象EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);//通过接口调用方法List<Emp> select = empMapper.select();for (Emp emp:select){System.out.println("ename:"+emp.getEname());}//关闭资源sqlSession.close();}
}

三、MyBatis对一对多关系的处理

一对一关系：一个员工只属于一个部门

一对多关系：一个部门有多个员工 

以dept为主表，查询每个部门中的所有员工

1、collection配置集合映射

一个部门对应的员工查询结果是一个Emp对象的集合，MyBatis中提供了对集合配置映射的标签：collection

        --->ofType:指定集合中的数据类型

2、代码演示 

package com.mybatis.entity;import lombok.Data;import java.util.List;@Data
public class Dept {private long id;private String deptName;private String local;//添加额外属性private List<Emp> emps;}

package com.mybatis.mapper;import com.mybatis.entity.Dept;import java.util.List;public interface DeptMapper {List<Dept> select();
}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.mybatis.mapper.DeptMapper"><resultMap id="deptMap" type="dept" autoMapping="true"><collection property="emps" ofType="emp" select="selectEmp" column="id" fetchType="lazy"><id property="id" column="id"></id><result column="ename" property="ename"></result><result property="age" column="age"></result><result column="deptno" property="deptno"></result></collection></resultMap><select id="select" resultMap="deptMap">select * from dept</select><select id="selectEmp" resultType="emp">select * from emp where deptno = #{id}</select>
</mapper>

package com.mybatis.mapper;import com.mybatis.entity.Dept;
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 java.io.IOException;
import java.io.InputStream;
import java.util.List;import static org.junit.Assert.*;public class DeptMapperTest {//创建SqlSessionFactory工厂对象SqlSessionFactory sqlSessionFactory = null;@Beforepublic void init(){System.out.println("init()");InputStream resourceAsStream;try {resourceAsStream = Resources.getResourceAsStream("config/mybatis-config.xml");} catch (IOException e) {throw new RuntimeException(e);}sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);}@Testpublic void select() {//创建SqlSession会话SqlSession sqlSession = sqlSessionFactory.openSession();//获取DeptMapper动态代理对象DeptMapper deptMapper = sqlSession.getMapper(DeptMapper.class);//通过接口调用方法List<Dept> select = deptMapper.select();for (Dept dept:select){System.out.println("dept = " + dept.getDeptName());}sqlSession.close();}
}

3、规范mapper映射文件

每个表的查询语句应该在自己的mapper文件下，我们以上使用的对两个表查询的SQL语句定义在了一个mapper文件中，

两个mapper文件的SQL语句可通过nameSpace属性值调用

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.mybatis.mapper.EmpMapper"><select id="selectEmp" resultType="emp">select * from emp where deptno = #{id}</select></mapper>

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.mybatis.mapper.DeptMapper"><resultMap id="deptMap" type="dept" autoMapping="true"><collection property="emps" ofType="emp" select="com.mybatis.mapper.EmpMapper.selectEmp" column="id" fetchType="lazy"><id property="id" column="id"></id><result column="ename" property="ename"></result><result property="age" column="age"></result><result column="deptno" property="deptno"></result></collection></resultMap><select id="select" resultMap="deptMap">select * from dept</select></mapper>