Mybatis分步查询、ResultMap

Mybatis分步查询

Mybatis官网全局配置文件

resultMap官方文档

数据库表使用MySQL官方的employees数据库

全局配置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="jdbc.properties"/>
    <settings>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
        <setting name="autoMappingUnknownColumnBehavior" value="NONE"/>
        <setting name="logImpl" value="LOG4J"/>
        <setting name="autoMappingBehavior" value="FULL"/>
        <setting name="cacheEnabled" value="true"/>
<!--        <setting name="lazyLoadingEnabled" value="true"/>-->
        <setting name="multipleResultSetsEnabled" value="true"/>
        <setting name="useColumnLabel" value="true"/>
        <setting name="useGeneratedKeys" value="true"/>
        <setting name="defaultExecutorType" value="SIMPLE"/>
        <setting name="defaultStatementTimeout" value="25"/>
        <setting name="defaultFetchSize" value="100"/>
        <setting name="safeRowBoundsEnabled" value="false"/>
        <setting name="localCacheScope" value="SESSION"/>
        <setting name="jdbcTypeForNull" value="OTHER"/>
        <setting name="lazyLoadTriggerMethods" value="equals,clone,hashCode,toString"/>
    </settings>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper resource="mapper/EmployeeMapper.xml"/>
        <mapper resource="mapper/DepartmentMapper.xml"/>
        <mapper resource="mapper/SalaryMapper.xml"/>
    </mappers>
</configuration>

• autoMappingBehavior为true，resultMap中无需设置autoMapping，否则未指定映射的字段将被忽略

正常（一次性）查询

查询员工的同时将工资、部门信息一次性查出

<!--一次性查询resultMap-->
    <resultMap id="employee" type="com.rufeng.domain.Employee">
        <id property="empNo" column="emp_no"/>
        <association property="department" columnPrefix="d">
        </association>
        <collection property="salaries" columnPrefix="s"
                    ofType="com.rufeng.domain.Salary">
        </collection>
    </resultMap>

    <!--  设置前缀，不用为列指定映射  -->
    <sql id="deptEmpColumns">
        ${de}.emp_no as de_emp_no,
        ${de}.dept_no as de_dept_no,
        ${de}.from_date as de_from_date,
        ${de}.to_date as de_to_date
    </sql>

    <sql id="deptColumns">
        ${d}.dept_no as d_dept_no,
        ${d}.dept_name as d_dept_name
    </sql>

    <sql id="employeeColumns">
        ${e}.*
    </sql>

    <sql id="salaryColumns">
        ${s}.from_date as s_from_date,
        ${s}.to_date as s_to_date,
        ${s}.emp_no as s_emp_no,
        ${s}.salary as s_salary
    </sql>
    <select id="getById" resultMap="employee">
        select
        <include refid="deptColumns">
            <property name="d" value="d"/>
        </include>
        ,
        <include refid="deptEmpColumns">
            <property name="de" value="de"/>
        </include>
        ,
        <include refid="employeeColumns">
            <property name="e" value="e"/>
        </include>
        ,
        <include refid="salaryColumns">
            <property name="s" value="s"/>
        </include>
        from employees as e
        natural join dept_emp as de
        natural join departments as d
        inner join salaries as s on s.emp_no = e.emp_no
        where e.emp_no = #{id};
    </select>

数据库请求1次

分步查询

<select id="getByIdStep" resultMap="employeeStep">
        select *
        from employees
        where emp_no = #{id}
    </select>

<resultMap id="employeeStep" type="com.rufeng.domain.Employee">
        <id property="empNo" column="emp_no"/>
        <association property="department"
                     select="com.rufeng.mapper.DepartmentMapper.getByEmpId"
                     column="emp_no">

        </association>
        <collection property="salaries"
                    select="com.rufeng.mapper.SalaryMapper.getByEmpId"
                    column="emp_no"
                    ofType="com.rufeng.domain.Salary">
        </collection>
    </resultMap>

 <!--  DepartmentMapper.xml  -->
    <!--  根据员工id查部门  -->
    <select id="getByEmpId" resultType="com.rufeng.domain.Department">
        select *
        from dept_emp
                 natural join departments
        where emp_no = #{empId};
    </select>

<!--  SalaryMapper.xml  -->
    <!--  根据员工id查薪资  -->
    <select id="getByEmpId" resultType="com.rufeng.domain.Salary">
        select *
        from salaries
        where emp_no = #{empId}

• 不开启懒加载，查询员工时将会发起3次数据库请求，分别是

  • 由员工id查员工
  • 由员工id查部门
  • 由员工id查薪资

• 开启懒加载，部门和薪资在需要用到时发起查询