MyBatis细细研磨(3)——查询详解

在java web开发中，无论是使用JDBC、MyBatis，还是使用Hibernate进行数据库操作，查询是最重要的内容。这就要求我们对数据库中的关系型数据要进行提取、组装，将其装换成我们需要的数据格式,因此我们要详细的了解查询结果集的封装

MyBatis自动映射

1. autoMappingBehavior自动映射

• NONE 取消自动映射
• PARTIAL 只会自动映射没有定义嵌套结果集映射的结果集
• FULL 自动映射任意复杂的结果集（无论是否嵌套)

2. mapUnderscoreToCamelCase 开启自动驼峰命名规则（camel case）映射

3. 使用ResultMap进行自定义组装结果集

简单的级联查询依赖封装

1. 创建两个数据表bd_user(用户)和bd_dept(部门)

CREATE TABLE `bd_dept` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `dept_name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `bd_user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `dept_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

2. 创建javaBean

public class User {
    private Long id;
    private String name;
    private Integer age;
    private Dept dept;
    //getter和setter
}

public class Dept { 
    private Long id;    
    private String deptName;    
    //getter和setter
}

3.在UserDao中定义方法queryUserAndDeptByUserId,在查询出用户信息的同时，要查询出该用户所在部门的完整信息

• 定义结果集ResultMap

<resultMap type="com.funshion.model.User" id="userAndDeptMap">
    <id column="id" property="id"/>
    <result column="name" property="name"/>
    <result column="age" property="age"/>
    <result column="dept_id" property="dept.id"/>
    <result column="dept_name" property="dept.deptName"/>
</resultMap>

• 定义查询

<select id="queryUserAndDeptByUserId" resultMap="userAndDeptMap">
        SELECT
        a.id,
        a.`name`,
        a.age,
        a.dept_id,
        b.dept_name 
    FROM
        bd_user a
        JOIN bd_dept b ON a.dept_id = b.id 
    WHERE
        a.id = #{userId}
</select>

使用association联合查询

• 定义结果集

<resultMap type="com.funshion.model.User" id="userAssocDeptMap">
    <id column="id" property="id" />
    <result column="name" property="name" />
    <result column="age" property="age" />
    <association property="dept" javaType="com.funshion.model.Dept">
        <id column="dept_id" property="id"/>
        <result column="dept_name" property="deptName" />
    </association>
</resultMap>

• 定义查询

<select id="queryUserAndDeptByUserId" resultMap="userAssocDeptMap">
        SELECT
        a.id,
        a.`name`,
        a.age,
        a.dept_id,
        b.dept_name 
    FROM
        bd_user a
        JOIN bd_dept b ON a.dept_id = b.id 
    WHERE
        a.id = #{userId}
</select>

使用association分步查询

1.定义根据ID查询Dept信息

<?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.funshion.dao.DeptDao">

    <select id="get" resultType="com.funshion.model.Dept">
        select * from bd_dept where id=#{id}
    </select>
</mapper>

2.将DeptDao的映射加入到MyBatis配置中

<mappers>
    <mapper resource="mapper/UserDao.xml"/>
    <mapper resource="mapper/DeptDao.xml"/>
</mappers>

3.定义分步查询结果集

<resultMap type="com.funshion.model.User" id="userAndDeptStepMap">
    <id column="id" property="id" />
    <result column="name" property="name" />
    <result column="age" property="age" />
    <association property="dept" select="com.funshion.dao.DeptDao.get" column="dept_id"></association>
</resultMap>

select 表示当前属性是调用select指定的方法查询的结果
column 表示指定那一列的值传递给select指定的方法

4.定义查询

<select id="queryUserAndDeptByStep" resultMap="userAndDeptStepMap">
    select * from bd_user where id=#{userId}
</select>

分步查询时，可以设置全局的变量lazyLoadingEnabled来控制是否需要进行延迟加载

使用collection查询集合属性

1.在Dept中增加属性List<User>

public class Dept { 
    private Long id;    
    private String deptName;    
    private List<User> users;
    //getter和setter
}

2.定义结果集

<resultMap type="com.funshion.model.Dept" id="deptAndUsersMap">
    <id column="id" property="id" />
    <result column="dept_name" property="deptName" />
    <collection property="users" ofType="com.funshion.model.User">
        <id column="uid" property="id" />
        <result column="name" property="name" />
        <result column="age" property="age" />
    </collection>
</resultMap>

3.定义查询

<select id="queryDeptAndUsers" resultMap="deptAndUsersMap">
    SELECT
        a.`id`,
        a.`dept_name`,
        b.`id` AS uid,
        b.`name`,
        b.`age`
    FROM
        bd_dept a
        JOIN bd_user b
        ON a.`id` = b.`dept_id`
    WHERE a.`id` =#{deptId}
</select>

使用collection进行分步查询

1.定义根据dept_id查询User

 <select id="queryByDeptId" resultType="com.funshion.model.User">
        select * from bd_user where dept_id=#{deptId}
    </select>

2.定义Dept结果集

<resultMap type="com.funshion.model.Dept" id="deptAndUsersStepMap">
    <id column="id" property="id" />
    <result column="dept_name" property="deptName" />
    <collection property="users" select="com.funshion.dao.UserDao.queryByDeptId" column="id">
    </collection>
</resultMap>

3.定义查询

<select id="queryDeptAndUsersByStep" resultMap="deptAndUsersStepMap">
    select * from bd_dept where id=#{deptId}
</select>

到这里，MyBatis中查询的核心内容基本全部介绍完了，当然在我们真正的工作开发中，会有很多动态SQL的内容，而动态SQL，要根据具体的业务进行不同的操作！