MyBatis使用resultMap自定义映射规则与关联映射_mybatis resultmap 布尔值映射-CSDN博客

一、分步查询

1.1什么时候可以用到分步查询

有的时候需要我们对数据库进行关联查询，比如Employee 持有另一个Department对象的一个引用，我们希望在查询Employee 的时候把Department也一同查询出来。这时候我们可以编写关联表的SQL 语句，从而将查询出来的数据使用resultMap进行映射。

我们也可以使用其他的方式将关联的信息查询出来，MyBatis 提供了分步查询的机制，把复杂的SQL 语句简单化。

关于MyBatis关联查询可参考博文：
MyBatis使用resultMap自定义映射规则与关联映射

1.2分步查询完成JavaBean 之间关联

需求：在查询Employee信息的时候，把其对应的Department信息也一并查询出来，这里使用分步查询的方法。使用关联映射的方法可以参考上面链接内的博文。

过程分析：

1根据员工id 向员工数据库中查询信息，获得员工的信息与部门的id。

2.根据部门的id 查询部门的信息。

3.对查询的信息进行封装。

由此可见，实现分步查询的方法就是将复杂的SQL 语句拆分成简单的多个SQL 语句，在某个SQL 执行结果的基础上再进行数据查询。

1.数据库表结构：

t_employee 数据库表结构(其中d_id用于关联t_dept表中的id)：

DROP TABLE IF EXISTS `t_employee`;
CREATE TABLE `t_employee` (
  `id` int(11) unsigned zerofill NOT NULL AUTO_INCREMENT,
  `username` varchar(30) DEFAULT NULL,
  `gender` char(1) DEFAULT NULL,
  `email` varchar(20) DEFAULT NULL,
  `d_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8;

 
 
 
 1
2
3
4
5
6
7
8
9

t_dept数据库表结构：

DROP TABLE IF EXISTS `t_dept`;
CREATE TABLE `t_dept` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `dept_name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

 
 
 
 1
2
3
4
5
6

2.定义JavaBean

Employee类：

package com.jas.mybatis.bean;

public class Employee {
private Integer id;
private String username;
private Character gender;
private String email;
private Department department;
// 省略set、get 与toString 方法
}

Department类：

package com.jas.mybatis.bean;

public class Department {
private Integer id;
private String departmentName;
// 省略set、get 与toString 方法
}

3.定义mapper 接口

EmployeeMapper 接口：

package com.jas.mybatis.mapper;

import com.jas.mybatis.bean.Employee;

public interface EmployeeMapper {

<span class="token comment">/**
 * 根据员工id 获取员工信息以及对应的部门信息
 * 
 * @param id
 * @return
 */</span>
Employee <span class="token function">getEmpAndDeptById</span><span class="token punctuation">(</span>Integer id<span class="token punctuation">)</span><span class="token punctuation">;</span>

}

DepatmentMapper 接口：

package com.jas.mybatis.mapper;

import com.jas.mybatis.bean.Department;

public interface DepatmentMapper {

<span class="token comment">/**
 * 根据部门id 获得部门信息
 * 
 * @param id
 * @return
 */</span>
Department <span class="token function">getDeptById</span><span class="token punctuation">(</span>Integer id<span class="token punctuation">)</span><span class="token punctuation">;</span>

}

4.编写SQL 语句

DepartmentMpper.xmlSQL 映射文件：

<mapper namespace="com.jas.mybatis.mapper.DepatmentMapper">
	<!-- 根据部门的id 获得部门信息 -->
    <select id="getDeptById" resultType="com.jas.mybatis.bean.Department">
        SELECT id, dept_name departmentName FROM t_dept WHERE id = #{id}
    </select>
</mapper>

 
 
 
 1
2
3
4
5
6

EmployeeMapper.xmlSQL 映射文件：

<mapper namespace="com.jas.mybatis.mapper.EmployeeMapper">
    <resultMap type="com.jas.mybatis.bean.Employee" id="BaseEmployeeResultMap">
        <!-- 进行数据库字段与JavaBean 属性映射 -->
        <id column="id" property="id"/>
        <result column="username" property="username"/>
        <result column="gender" property="gender"/>
        <result column="email" property="email"/>
    <span class="token comment">&lt;!--
        使用association 标签完成分步查询
        
        property：Employee 类中对应的属性department
        select：另外一个映射语句的 ID，也就是DepartmentMpper.xml 中的getDeptById
        column：表示数据库中的哪个字段名作为参数传给select 中的SQL 语句，
        如果要传递多个参数，可以把多个参数封装成map，比如：{id = d_id, ...}
        
        分析：执行流程是先根据员工的id 查询到数据库中的信息，将对应的员工信息先进行映射封装，
        然后根据d_id 字段值，去部门表中查询部门信息，然后再对部门信息进行映射封装，
        从而获得完整的员工信息。
    --&gt;</span>
    <span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>association</span> <span class="token attr-name">property</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>department<span class="token punctuation">"</span></span> <span class="token attr-name">column</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>d_id<span class="token punctuation">"</span></span>
                 <span class="token attr-name">select</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>com.jas.mybatis.mapper.DepatmentMapper.getDeptById<span class="token punctuation">"</span></span><span class="token punctuation">&gt;</span></span>
        <span class="token comment">&lt;!--
            将数据库中的部门信息映射到Employee 类中的部门信息属性中
        --&gt;</span>
        <span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>id</span> <span class="token attr-name">column</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>d_id<span class="token punctuation">"</span></span> <span class="token attr-name">property</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>id<span class="token punctuation">"</span></span><span class="token punctuation">/&gt;</span></span>
        <span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>result</span> <span class="token attr-name">column</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>dept_name<span class="token punctuation">"</span></span> <span class="token attr-name">property</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>departmentName<span class="token punctuation">"</span></span><span class="token punctuation">/&gt;</span></span>
    <span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>association</span><span class="token punctuation">&gt;</span></span>
<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>resultMap</span><span class="token punctuation">&gt;</span></span>

<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;</span>select</span> <span class="token attr-name">id</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>getEmpAndDeptById<span class="token punctuation">"</span></span> <span class="token attr-name">resultMap</span><span class="token attr-value"><span class="token punctuation">=</span><span class="token punctuation">"</span>BaseEmployeeResultMap<span class="token punctuation">"</span></span><span class="token punctuation">&gt;</span></span>
    SELECT * FROM t_employee WHERE id = #{id}
<span class="token tag"><span class="token tag"><span class="token punctuation">&lt;/</span>select</span><span class="token punctuation">&gt;</span></span>

</mapper>

5.测试代码：

    private SqlSession getSqlSession() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream is = Resources.getResourceAsStream(resource);
    SqlSessionFactory sqlSessionFactory <span class="token operator">=</span> <span class="token keyword">new</span> <span class="token class-name">SqlSessionFactoryBuilder</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">.</span><span class="token function">build</span><span class="token punctuation">(</span>is<span class="token punctuation">)</span><span class="token punctuation">;</span>
    SqlSession sqlSession <span class="token operator">=</span> sqlSessionFactory<span class="token punctuation">.</span><span class="token function">openSession</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    
    <span class="token keyword">return</span> sqlSession<span class="token punctuation">;</span>
<span class="token punctuation">}</span>

<span class="token annotation punctuation">@Test</span>
<span class="token keyword">public</span> <span class="token keyword">void</span> <span class="token function">resultMapTest01</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token keyword">throws</span> IOException <span class="token punctuation">{</span>
    
    SqlSession sqlSession <span class="token operator">=</span> <span class="token function">getSqlSession</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    
    EmployeeMapper employeeMapper <span class="token operator">=</span> sqlSession<span class="token punctuation">.</span><span class="token function">getMapper</span><span class="token punctuation">(</span>EmployeeMapper<span class="token punctuation">.</span><span class="token keyword">class</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    Employee employee <span class="token operator">=</span> employeeMapper<span class="token punctuation">.</span><span class="token function">getEmpAndDeptById</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    
    System<span class="token punctuation">.</span>out<span class="token punctuation">.</span><span class="token function">println</span><span class="token punctuation">(</span>employee<span class="token punctuation">)</span><span class="token punctuation">;</span>
    sqlSession<span class="token punctuation">.</span><span class="token function">close</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span>

6.查看结果：

这里写图片描述

从上面的执行结果可以看出在进行Employee查询结果信息封装的时候，向数据库发了两条SQL 语句，分别获得员工信息与部门信息。通过把复杂的关联SQL 语句进行分步，最终完成了分步查询。

一般情况下Department getDeptById(Integer id);方法在实际的开发过程中是必须要定义的一个方法，这样在进行关联查询的时候，我们只要使用现成的方法即可，不需要再定义复杂的关联SQL 语句。分步查询还有一个好处就是可以实现延迟加载，这个延迟加载功能在MyBatis 中可以说是一个非常重要的功能。

下面我们就来看看分步查询是如何完成延迟加载的。

二、延迟加载

2.1什么是延迟加载

延迟加载也可以理解为按需加载。即在你需要的时候才进行的加载方式，才会向数据库发送SQL 语句，不需要的时候不会向数据库中发送SQL。这样做可以用于减轻数据库的压力。

比如上面的分步查询，我们通过分步查询分别查询出了员工信息与部门信息，并把这些信息封装成了一个Employee对象。如果我们并不需要查询员工的部门信息，在查询员工信息的时候，由于分步查询了部门信息，所以这时候部门信息也会被查出来，但是查询出来的部门信息是不需要的。本来向数据库发送一条SQL 语句就可以了，这时候发送了两条。显然这样做不是好的解决办法，这时候我们就可以使用延迟加载。

2.2实现延迟加载

其实在MyBais 中实现延迟加载的功能是很容易的，只需要在全局的配置文件中进行对应的setting设置即可。

    <settings>
        <!-- 延迟加载的全局开关。当开启时，所有关联对象都会延迟加载，默认为false  -->
        <setting name="lazyLoadingEnabled" value="true"/>
        <!-- 设置属性按需加载 -->
        <setting name="aggressiveLazyLoading" value="false"/>
    </settings>

 
 
 
 1
2
3
4
5
6

你有没有觉得还需要点什么，其实只要配置了上面的两个属性，延迟加载就已经开启了。

2.3测试：

如果我们只要获得员工的姓名：

	// 省略了 getSqlSession() 方法
	@Test
    public void resultMapTest01() throws IOException {
    SqlSession sqlSession <span class="token operator">=</span> <span class="token function">getSqlSession</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    
    EmployeeMapper employeeMapper <span class="token operator">=</span> sqlSession<span class="token punctuation">.</span><span class="token function">getMapper</span><span class="token punctuation">(</span>EmployeeMapper<span class="token punctuation">.</span><span class="token keyword">class</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    Employee employee <span class="token operator">=</span> employeeMapper<span class="token punctuation">.</span><span class="token function">getEmpAndDeptById</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    
    System<span class="token punctuation">.</span>out<span class="token punctuation">.</span><span class="token function">println</span><span class="token punctuation">(</span>employee<span class="token punctuation">.</span><span class="token function">getUsername</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    sqlSession<span class="token punctuation">.</span><span class="token function">close</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>      
<span class="token punctuation">}</span>

执行结果：
这里写图片描述
开启延迟加载后只发送了一条SQL 语句。

下面我们来看看在没有开启延迟加载的情况下获得员工的姓名：
这里写图片描述
发现即使你不想要获得员工的部门信息，由于执行的是分步查询，所以还是会发送两条SQL 语句，这显然不是我们想要的。

如果我们想要获得员工的部门信息：

    @Test
    public void resultMapTest01() throws IOException {
    SqlSession sqlSession <span class="token operator">=</span> <span class="token function">getSqlSession</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    
    EmployeeMapper employeeMapper <span class="token operator">=</span> sqlSession<span class="token punctuation">.</span><span class="token function">getMapper</span><span class="token punctuation">(</span>EmployeeMapper<span class="token punctuation">.</span><span class="token keyword">class</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    Employee employee <span class="token operator">=</span> employeeMapper<span class="token punctuation">.</span><span class="token function">getEmpAndDeptById</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    
    System<span class="token punctuation">.</span>out<span class="token punctuation">.</span><span class="token function">println</span><span class="token punctuation">(</span>employee<span class="token punctuation">.</span><span class="token function">getDepartment</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    sqlSession<span class="token punctuation">.</span><span class="token function">close</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span>