MyBatis 高级映射与懒加载

MyBatis的高级映射是该框架非常重要的一个功能，在开发中有广泛应用，在面试中也是常考的内容，下面我们就学习下高级映射。

       引言：MyBatis的的高级映射分为关联和集合两种，关联是一对一关系的映射，集合是一对多关系的映射，都分为嵌套查询和嵌套结果两种实现方式;嵌套查询在数据量不大和关联不多的时候使用比较合适，当数据量大的时候，会出现N + 1问题，导致执行的SQL很多，加载很慢，另外也可以指定是否使用懒加载模式（请看下文中的说明）;嵌套结果使用联合查询，在大量数据的情况下能有效解决N + 1问题。

        注意：本学习使用的MyBatis的版本为3.4.6

 业务场景设计：

    实体部门，一个部门只有一个项目经理和一个产品经理，但是有多名员工，实体类如下：

public class Department {
	//id
	private int id;
	//部门名 eg.software
	private String name;
	//项目经理
	private Manager projectManage;
	//产品经理
	private Manager productManager;
	//多名员工
	private List<Emp> empList;
	
	public List<Emp> getEmpList() {
		return empList;
	}
	public void setEmpList(List<Emp> empList) {
		this.empList = empList;
	}
	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 Manager getProjectManage() {
		return projectManage;
	}
	public void setProjectManage(Manager projectManage) {
		this.projectManage = projectManage;
	}
	public Manager getProductManager() {
		return productManager;
	}
	public void setProductManager(Manager productManager) {
		this.productManager = productManager;
	}
	@Override
	public String toString() {
		return "Department [id=" + id + ", name=" + name + "]";
	}
	
}

public class Manager {
	//经理id
	private int id;
	private String name;
	private String position;
	private int age;
	//经理所在的部门id
	private int departmentId;
	
	public int getDepartmentId() {
		return departmentId;
	}
	public void setDepartmentId(int departmentId) {
		this.departmentId = departmentId;
	}
	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 getPosition() {
		return position;
	}
	public void setPosition(String position) {
		this.position = position;
	}
	public int getAge() {
		return age;
	}
	public void setAge(int age) {
		this.age = age;
	}
	@Override
	public String toString() {
		return "Manager [id=" + id + ", name=" + name + ", position=" + position + ", age=" + age + ", departmentId="
				+ departmentId + "]";
	}
	
}

 

public class Emp{
	//员工id
	private int id;
	private String name;
	private String gender;
	private int age;
	//员工所在的部门id
	private int departmentId;
	
	public int getDepartmentId() {
		return departmentId;
	}
	public void setDepartmentId(int departmentId) {
		this.departmentId = departmentId;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getGender() {
		return gender;
	}
	public void setGender(String gender) {
		this.gender = gender;
	}
	public int getAge() {
		return age;
	}
	public void setAge(int age) {
		this.age = age;
	}

	public int getId() {
		return id;
	}

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

	public  Emp clone() throws CloneNotSupportedException{
		return (Emp) super.clone();
	}

	@Override
	public String toString() {
		return "Emp{" +
				"id=" + id +
				", name='" + name + '\'' +
				", gender='" + gender + '\'' +
				", age=" + age +
				'}';
	}
}

SQL：

create table department (
	id int auto_increment,
	name varchar(128) not null,
	project_manage_id int not null,
	product_manage_id int not null,
	primary key (id)
);

create table manager (
	id int auto_increment,
	name varchar(128) not null,
	position varchar(64) not null,
	age int not null,
	department_id int not null,
	primary key (id)
);

insert into 
	manager
		 (name,position,age,department_id) 
	values 
		('張三','projm',32,1);
	insert into 
	manager
		 (name,position,age,department_id) 
	values 
		('李四','prodm',34,1);

insert into 
	department
		 (name,project_manage_id,product_manage_id) 
	values 
		('software',1,2);

 

关联查询 - 嵌套查询

   嵌套查询 - 示例

    格式：<association property =“author”column =“blog_author_id”javaType =“Author”select =“selectAuthor”/>

    需求：查询一个部门的时候，同时查询出该部门的项目经理

    实现：在departmentMapper.xml中编写SQL及相关代码

<?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.pactera.spring.mapper.DepartmentMapper">
<resultMap      id="DepartmentResultMap2" 
				type="com.pactera.spring.model.Department">
			 <id column="id" property="id"/>
			 <result column="name" property="name"/>
		     <association 
                     property="projectManage"
					 column="project_manage_id"
					 javaType="com.pactera.spring.model.Manager"
					 select="selectManagerById"
		/>
			 </resultMap>
    <!-- 根据id查找部门-->
    <select id="selectById" parameterType="int"
		resultMap="DepartmentResultMap2">
		SELECT 
			id ,
		 	name ,
		  	project_manage_id
		 FROM 
		 	department 
		 WHERE id=#{id}
	</select>
   <!-- 根据id查找经理-->
   <select id="selectManagerById" parameterType="int" 
		resultType="com.pactera.spring.model.Manager">
		select id,name,position,age,department_id as departmentId
		from manager where id=#{id}
	</select>
</mapper>

使用弹簧整合的MyBatis的方式在控制器层调用服务 - >映射器的方式调用departmentMapper.xml中的selectById方法：

春天整合MyBatis的配置：

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/test_db?useSSL=true"/>
        <property name="username" value="root"/>
        <property name="password" value="root"/>
        <property name="maxActive" value="1000"/>
        <property name="initialSize" value="1000"/>
    </bean>
    <bean id="factoryBean" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="dataSource"/>
        <property name="mapperLocations" value="classpath:config/mapper/*.xml"/>
        <property name="configLocation" value="classpath:config/mybatis-config.xml"/>
        <!-- <property name="plugins" ref="list"/> -->
        <!--<property name="configurationProperties" ref="configurationProperties"/>-->
    </bean>
    <util:properties id="configurationProperties">
        <prop key="lazyLoadingEnabled">true</prop>
    </util:properties>
    <util:list id="list" value-type="org.apache.ibatis.plugin.Interceptor" list-class="java.util.ArrayList">
        <ref bean="interceptor"/>
    </util:list>
    <bean id="interceptor" class="com.pactera.spring.interceptor.MyBatisInterceptor"/>
    <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource"/>
    </bean>
    <bean id="mapperScanner" class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <property name="basePackage" value="com.pactera.spring.mapper"/>
    </bean>

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="config/config.properties">
        <property name="username" value="dev_user" />
        <property name="password" value="F2Fa3!33TYyg" />
    </properties> -->
    <settings>
        <setting name="logImpl" value="LOG4J"/>
        <!--<setting name="lazyLoadingEnabled" value="true"/>-->
        <!-- 所有的方法都会加载该对象所有的属性-->
       <!-- <setting name="aggressiveLazyLoading" value="false"/>-->
        <!--<setting name="lazyLoadTriggerMethods" value="equals,clone,hashCode,toString"/>-->

    </settings>
    <typeAliases>
        <package name="com.pactera.spring.model"/>
    </typeAliases>

</configuration>

道服务控制器

public interface DepartmentMapper {
	Department selectById(int id);
}

@Service
public class DepartmentService implements IDepartmentService{

    @Autowired
    DepartmentMapper departmentMapper;
    @Override
    public Department selectById(int id) {
        return departmentMapper.selectById(id);
    }
}

@RestController
@RequestMapping("/department")
public class DepartmentController {

	@Autowired
	IDepartmentService departmentService;

	@GetMapping("/select")
	public String getDepartment(@RequestParam("id") int id) {
		Department department = departmentService.selectById(id);
		department.getProjectManage().getAge();
		return "ok";
	}

	/**
	 * consumes-用来限制请求实体头域中的content-Type,只要请求中的实体头域content-Type头域值在consumes指定的类型中
	 * 则可以正常处理，否则会报406错误，406 Not Acceptable
	 * produces-用来限制请求头域中的Accept，只要该参数指定的值都不在请求头域中的Accept值中， 则报415错误，Unsupported
	 * Media Type；设置响应体中实体头域content-Type的作用 以上两处校验是在HandlerMapping中校验的
	 * 
	 * @param id
	 * @return
	 * @throws Exception
	 */

	@GetMapping(value = "/select/projM", consumes = "application/json", produces = { "text/html;charset=utf-8" })
	public String getDepartmentProjM(@RequestParam("id") int id) throws Exception {
		Department department = departmentService.selectById(id);
//		System.out.println(department.toString());
//		department.getId();
		return "查询成功";
	}
}

执行请求本地主机：8080 /部门/选择/ projM ID = 1，日志SQL显示如下： 

变量监控显示：

关联查询成功！

懒加载怎么处理呢？

    比如我现在有的service层方法不需要使用productManager属性，有的service层方法需要使用productManager属性，那么我们可以指定策略来完成这个需求。先看看懒加载相关的配置：

    1.lazyLoadingEnabled

    属于全局配置， 延迟加载的全局开关。当开启时，所有关联对象都会延迟加载。 特定关联关系中可通过设置fetchType属性来覆盖该项的开关状态。有效值 true | false，默认值 false。也就说默认情况下是不会延迟加载，所以我们的查询直接执行了嵌套查询的sql。

    2.aggressiveLazyLoading

    属于全局配置，当开启时，任何方法的调用都会加载该对象的所有属性。否则，每个属性会按需加载。有效值 true | false，默认值 false(true in ≤3.4.1)。

    3.fetchType

     属于局部配置，使用在mapper.xml中的resultMap标签的association标签中(当使用嵌套查询方式时)，该配置有效值为eager|lazy，无默认值，该值会覆盖全局配置中lazyLoadingEnabled。

   4.lazyLoadTriggerMethods

    属于全局配置，指定哪个对象的方法触发一次延迟加载。用逗号分隔的方法列表。默认值equals,clone,hashCode,toString。

     我们主要针前三个属性进行学习和测试

    开启全局延迟加载 -- 添加全局配置lazyLoadingEnabled并设置值为true

 

重新执行请求， sql执行日志如下：

设置嵌套查询的 fetchType属性为eager，代表本关联查询不使用懒加载，修改如下：

执行请求sql日志如下：

修改fetchType属性为lazy，在controller层方法中添加代码department.getId()后执行请求，sql日志显示如下：

可以看到没有执行嵌套查询，因为执行department.getId()方法时，不会触发嵌套查询。当我们修改aggressiveLazyLoading为true时，任何方法的调用都会加载该对象的所有属性，那么执行department.getId()方法时就会触发嵌套查询。现在修改aggressiveLazyLoading为true，执行请求，sql日志显示如下：

关联查询-嵌套结果

    格式：<association property="projectManage" javaType="com.pactera.spring.model.Manager"                      column="department_project_manage_id" resultMap="managerResultMap" />

    需求：查询一个部门的时候，同时查询出该部门的项目经理

    实现：在departmentMapper.xml中编写sql及相关代码

    异同：嵌套查询会产生两次sql查询，嵌套结果是一个sql查询，不过这个查询时是联合查询；

               嵌套查询可以使用懒加载模式，嵌套结果不能。

  修改后查询语句如下：

<resultMap id="DepartmentResultMap"
               type="com.pactera.spring.model.Department">
        <id column="department_id" property="id"/>
        <result column="department_name" property="name"/>
        <association property="projectManage"
                     javaType="com.pactera.spring.model.Manager"
                     column="department_project_manage_id" resultMap="managerResultMap"
        />
    </resultMap>
    <!-- 嵌套查询的映射集合-->
    <resultMap id="managerResultMap" type="com.pactera.spring.model.Manager" >
        <id column="manager_id" property="id"/>
        <result column="manager_name" property="name"/>
        <result column="manager_position" property="position"/>
        <result column="manager_age" property="age"/>
        <result column="manager_department_id" property="departmentId"/>
    </resultMap>
    <select id="selectById" parameterType="int"
            resultMap="DepartmentResultMap">
		SELECT 
			d.id as department_id,
		 	d.name as department_name,
		  	d.project_manage_id as department_project_manage_id,
		  
		  	m.id as manager_id,
			m.name as manager_name,
			m.position as manager_position,
			m.age as manager_age,
			m.department_id as manager_department_id
		 FROM 
		 	department d
		 left outer 
		 	join manager m 
		 on d.project_manage_id = m.id
		 WHERE d.id=#{id}
	</select>

执行请求后sql日志显示如下：

[DEBUG] 2018-12-17 16:31:53,787(18231) --> [catalina-exec-4] org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159): ==>  Preparing: SELECT d.id as department_id, d.name as department_name, d.project_manage_id as department_project_manage_id, m.id as manager_id, m.name as manager_name, m.position as manager_position, m.age as manager_age, m.department_id as manager_department_id FROM department d left outer join manager m on d.project_manage_id = m.id WHERE d.id=?   
[DEBUG] 2018-12-17 16:31:53,819(18263) --> [catalina-exec-4] org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159): ==> Parameters: 1(Integer)  
[DEBUG] 2018-12-17 16:31:53,836(18280) --> [catalina-exec-4] org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159): <==      Total: 1  

属性 columnPrefix--重用嵌套结果的映射结果集

业务场景：查询部门的时候同时查询出部门的项目经理和产品经理，

a.联合查询语句修改如下：

再次外联查询一次manager表，并将产品经理的结果字段命名为带product_前缀的，前缀之后的名字都与managerResultMap结果映射中的字段名保持一致。

<select id="selectById" parameterType="int"
            resultMap="DepartmentResultMap">
		SELECT 
			d.id as department_id,
		 	d.name as department_name,
		  	d.project_manage_id as department_project_manage_id,
		    d.product_manage_id as department_product_manage_id,

		  	m.id as manager_id,
			m.name as manager_name,
			m.position as manager_position,
			m.age as manager_age,
			m.department_id as manager_department_id,

			mp.id as product_manager_id,
            mp.name as product_manager_name,
			mp.position as product_manager_position,
			mp.age as product_manager_age,
			mp.department_id as product_manager_department_id
		 FROM 
		 	department d
		 left outer 
		 	join manager m 
		 on d.project_manage_id = m.id
		 left outer
		 	join manager mp
		 on d.product_manage_id = mp.id
		 WHERE d.id=#{id}
	</select>

b.DepartmentResultMap修改如下：新增了产品经理的关联结果配置，columnPrefix的值一定是包含了下划线的，也就说我们要保证除了前缀外剩余的字段名都要与managerResultMap中的保持一致。

<resultMap id="DepartmentResultMap"
               type="com.pactera.spring.model.Department">
        <id column="department_id" property="id"/>
        <result column="department_name" property="name"/>
        <association property="projectManager"
                     javaType="com.pactera.spring.model.Manager"
                     column="department_project_manage_id" resultMap="managerResultMap"
        />
        <association property="productManager"
                     javaType="com.pactera.spring.model.Manager"
                     column="department_product_manage_id" resultMap="managerResultMap"
					 columnPrefix="product_"
        />

执行请求，请求结果修改为department的toString()方法结果，结果显示为：

Department{id=1, name='finance', projectManager=Manager [id=1, name=張三, position=projm, age=32, departmentId=1], productManager=Manager [id=2, name=李四, position=prodm, age=34, departmentId=1], empList=null}

集合查询

    现在根据查询一个部门的项目经理和产品经理已经完成了，但是现在我还要查询一个部门的所有员工，这个时候我们就可以使用集合查询，基本的格式为：

<collection property="posts" javaType="ArrayList" column="id" ofType="Post" select="selectPostsForBlog"/>或者

<collection property="posts" javaType="ArrayList" column="id" ofType="Post" resultMap="***Result"/>

前者为集合查询的嵌套查询后者为集合查询的嵌套结果

    嵌套查询

    1.编写嵌套查询的sql语句

<select id="selectEmpByDepartmentId" parameterType="int" resultType="com.pactera.spring.model.Emp">
        SELECT id,name,gender,age,department_id as departmentId FROM emp WHERE department_id =#{departmentId}
    </select>

 2.修改DepartmentResultMap，新增嵌套查询语句

<resultMap id="DepartmentResultMap"
               type="com.pactera.spring.model.Department">
        <id column="department_id" property="id"/>
        <result column="department_name" property="name"/>
        <association property="projectManager"
                     javaType="com.pactera.spring.model.Manager"
                     column="department_project_manage_id" resultMap="managerResultMap"
        />
        <association property="productManager"
                     javaType="com.pactera.spring.model.Manager"
                     column="department_product_manage_id" resultMap="managerResultMap"
					 columnPrefix="product_"
        />
        <!-- 新增-->
        <collection property="empList" column="department_id" select="selectEmpByDepartmentId"
                    ofType="com.pactera.spring.model.Emp"/>
    </resultMap>

 说明：collection标签中property属性为该集合在实体中的属性名，column为与集合元素关联的字段，比如本例子中，员工属于那个部门，所以该字段为部门id，最后在查询语句中使用部门id查询其所有的员工信息。select为嵌套查询的查询语句id，ofType为集合元素的类型。也可以添加javaType属性指定该集合的类型，但是MyBatis一般会推断出该类型，故一般不写此属性。

3.执行请求查看结果如下：

Department{id=1, name='finance', projectManager=Manager [id=1, name=張三, position=projm, age=32, departmentId=1], productManager=Manager [id=2, name=李四, position=prodm, age=34, departmentId=1], empList=[Emp{id=516926, name='jack', gender='f', age=20}, Emp{id=516927, name='rose', gender='m', age=19}, Emp{id=516928, name='lily', gender='m', age=45}, Emp{id=516929, name='joy', gender='f', age=17}, Emp{id=516930, name='jobs', gender='f', age=32}]}

嵌套结果

1.编写嵌套结果的映射结果集

<resultMap id="empResultMap" type="com.pactera.spring.model.Emp">
        <id column="emp_id" property="id"/>
        <result column="emp_name" property="name"/>
        <result column="gender" property="gender"/>
        <result column="emp_age" property="age"/>
        <result column="emp_department_id" property="departmentId"/>
    </resultMap>

2.修改DepartmentResultMap，新增嵌套结果语句

<resultMap id="DepartmentResultMap"
               type="com.pactera.spring.model.Department">
        <id column="department_id" property="id"/>
        <result column="department_name" property="name"/>
        <association property="projectManager"
                     javaType="com.pactera.spring.model.Manager"
                     column="department_project_manage_id" resultMap="managerResultMap"
        />
        <association property="productManager"
                     javaType="com.pactera.spring.model.Manager"
                     column="department_product_manage_id" resultMap="managerResultMap"
					 columnPrefix="product_"
        />
        <!-- 新增-->
        <!--<collection property="empList" column="department_id" select="selectEmpByDepartmentId"
                    ofType="com.pactera.spring.model.Emp"/>-->
        <!-- 新增集合嵌套结果-->
        <collection property="empList" column="department_id" resultMap="empResultMap" ofType="com.pactera.spring.model.Emp"/>
    </resultMap>

3.修改selectById的查询语句，新增emp列映射和外联emp表

<select id="selectById" parameterType="int"
            resultMap="DepartmentResultMap">
		SELECT 
			d.id as department_id,
		 	d.name as department_name,
		  	d.project_manage_id as department_project_manage_id,
		    d.product_manage_id as department_product_manage_id,

		  	m.id as manager_id,
			m.name as manager_name,
			m.position as manager_position,
			m.age as manager_age,
			m.department_id as manager_department_id,

			mp.id as product_manager_id,
            mp.name as product_manager_name,
			mp.position as product_manager_position,
			mp.age as product_manager_age,
			mp.department_id as product_manager_department_id,

            e.id as emp_id,
            e.name as emp_name,
            e.gender as emp_gender,
            e.age as emp_age,
            e.department_id as emp_department_id

		 FROM 
		 	department d
		 left outer 
		 	join manager m 
		 on d.project_manage_id = m.id
		 left outer
		 	join manager mp
		 on d.product_manage_id = mp.id
		 left outer
		 	join emp e
		 on d.id = e.id
		 WHERE d.id=#{id}
	</select>

4.执行请求，结果如下：

Department{id=1, name='finance', projectManager=Manager [id=1, name=張三, position=projm, age=32, departmentId=1], productManager=Manager [id=2, name=李四, position=prodm, age=34, departmentId=1], empList=[]}

查询成功！！！

将empResultMap单独作为一个映射结果集可以提高复用率，如果不需要复用，则可以简化DepartmentResultMap书写如下：

<resultMap id="DepartmentResultMap"
               type="com.pactera.spring.model.Department">
        <id column="department_id" property="id"/>
        <result column="department_name" property="name"/>
        <association property="projectManager"
                     javaType="com.pactera.spring.model.Manager"
                     column="department_project_manage_id" resultMap="managerResultMap"
        />
        <association property="productManager"
                     javaType="com.pactera.spring.model.Manager"
                     column="department_product_manage_id" resultMap="managerResultMap"
					 columnPrefix="product_"
        />
        <!-- 新增-->
        <!--<collection property="empList" column="department_id" select="selectEmpByDepartmentId"
                    ofType="com.pactera.spring.model.Emp"/>-->
        <!-- 新增集合嵌套结果-->
        <!--<collection property="empList" column="department_id" resultMap="empResultMap" ofType="com.pactera.spring.model.Emp"/>-->
        <collection property="empList" column="department_id" ofType="com.pactera.spring.model.Emp">
            <id column="emp_id" property="id"/>
            <result column="emp_name" property="name"/>
            <result column="gender" property="gender"/>
            <result column="emp_age" property="age"/>
            <result column="emp_department_id" property="departmentId"/>
        </collection>
    </resultMap>

如上，则empResultMap结果映射就不需要了。

如果出现如关联相似的相同元素集合，可以使用属性columnPrefix做区分。

总结

    MyBatis的的高级映射功能，在数据量不大的情况下，嵌套查询和嵌套结果都是不错的选择，当然你可以根据自己的业务需求以及自己的编程爱好进行选择。