MyBatis之resultMap

如果我们在mybatis-config.xml中不设置<setting name="mapUnderscoreToCamelCase" value="true"/>，或者说，数据库中的字段到实体的映射并非简单的下划线改成驼峰命名，那么，我们该如何做到查询出的结果封装的正确性呢？

resultMap简单使用

我们注释掉全局配置文件中的<setting name="mapUnderscoreToCamelCase" value="true"/>，然后关注Employees getEmpById(Integer employeeId);这个查询。

sql映射文件中，我们以前的返回值是写的resultType，现在我们改成resultMap。resultType和resultMap只能二者选一。

 <resultMap id="mappingEmp" type="emp">
<id column="employee_id" property="employeeId"/>
<result column="first_name" property="firstName"/>
<result column="last_name" property="lastName"/>
<result column="email" property="email"/>
<result column="phone_number" property="phoneNumber"/>
<result column="job_id" property="jobId"/>
<result column="salary" property="salary"/>
<result column="commission_pct" property="commissionPct"/>
<result column="manager_id" property="managerId"/>
<result column="department_id" property="departmentId"/>
<result column="hiredate" property="hiredate"/>
</resultMap>
<!-- Employees getEmpById(Integer employeeId);-->
<select id="getEmpById" resultMap="mappingEmp">
select * from employees where employee_id=#{employeeId}
</select>

在mappingEmp中，我们完成数据库字段到java实例类属性的映射。

其中主键我们我们用<id/>标签，mybatis映射的时候将有优化。

其他的属性用<result/>标签。

对于像email这样的字段，虽然不用写映射，但是如果你写了resultMap，那么就要将所有字段都写出映射。

association

如果是联表查，比如这样的sql：

SELECT
e.employee_id employee_id,
e.first_name first_name,
e.email email,
e.department_id emp_depart_id,
d.`department_id` dept_depart_id,
d.`department_name` department_name,
d.`location_id` location_id,
d.`manager_id` manager_id
FROM
employees e,
departments d
WHERE e.`department_id` = d.`department_id`
AND e.`employee_id` = 101

这时我们通过employees表的department_id和departments的department_id将两张表连起来。

所以最终会查出连带的departments的信息。

那么此时sql映射要怎么写？

先看接口：

 Employees selectEmpAndDeptByAssociation(Integer id);

很简单，给id，查员工。

<!--Employees selectEmpAndDeptByAssociation(Integer id);-->
<select id="selectEmpAndDeptByAssociation" resultMap="mappingEmp">
SELECT
e.employee_id employee_id,
e.first_name first_name,
e.email email,
e.department_id emp_depart_id,
d.department_id dept_depart_id,
d.department_name department_name,
d.location_id location_id,
d.manager_id manager_id
FROM
employees e,departments d
WHERE
e.`department_id`=d.`department_id` AND e.employee_id=#{employee_id}
</select>

sql语句只要将e.employee_id=后面改成取值的表达式#{employee_id}就行了。

关键是resultMap怎么写。

因为会查出departments的信息，所以在员工类中，一定会有部门的属性。

@Alias("emp")
public class Employees {
private Integer employeeId;
private String firstName;
private String lastName;
private String email;
private String phoneNumber;
private String jobId;
private Double salary;
private Double commissionPct;
private Integer managerId;
private Integer departmentId;
private Date hiredate;
private Department department;
}

加上setter方法。

然后我们使用<association>标签做关联查询：

 <resultMap id="mappingEmp" type="emp">
<id column="employee_id" property="employeeId"/>
<result column="first_name" property="firstName"/>
<result column="last_name" property="lastName"/>
<result column="email" property="email"/>
<result column="phone_number" property="phoneNumber"/>
<result column="job_id" property="jobId"/>
<result column="salary" property="salary"/>
<result column="commission_pct" property="commissionPct"/>
<result column="manager_id" property="managerId"/>
<result column="emp_depart_id" property="departmentId"/>
<result column="hiredate" property="hiredate"/>
<association property="department" javaType="dept">
<id column="dept_depart_id" property="departmentId"/>
<result column="department_name" property="departmentName"/>
<result column="location_id" property="locationId"/>
<result column="manager_id" property="managerId"/>
</association>
</resultMap>

因为Department类我取了别名dept，所以对应的javaType我写成了dept。

当然这样的sql语句太复杂了，我们想做的其实是这两步：

SELECT * FROM employees WHERE employee_id=101;

SELECT * FROM departments WHERE department_id=90;

第一条sql查出来的department_id为90。

那么在mybaitis中我们也可以这么做。

首先为department建立实体类（早已建了）、mapper接口和mapper的xml文件：

package org.mybatis.lecture.dao;
import org.mybatis.lecture.bean.Department;
public interface DepartmentMapper {
Department getDepartmentById(Integer id);
}

<?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="org.mybatis.lecture.dao.DepartmentMapper">
<resultMap id="mappingDept" type="dept">
<id column="department_id" property="departmentId"/>
<result column="department_name" property="departmentName"/>
<result column="manager_id" property="managerId"/>
<result column="location_id" property="locationId"/>
</resultMap>
<!--Department getDepartmentById(Integer id);-->
<select id="getDepartmentById" resultMap="mappingDept">
select * from departments where department_id=#{departmentId}
</select>
</mapper>

然后我们在EmployeeMapper接口中写下分步查询的方法：Employees selectEmpAndDeptBySteps(Integer id);

为方法提供映射：

<resultMap id="mappingEmpByStep" type="emp">
<id column="employee_id" property="employeeId"/>
<result column="first_name" property="firstName"/>
<result column="last_name" property="lastName"/>
<result column="email" property="email"/>
<result column="phone_number" property="phoneNumber"/>
<result column="job_id" property="jobId"/>
<result column="salary" property="salary"/>
<result column="commission_pct" property="commissionPct"/>
<result column="manager_id" property="managerId"/>
<result column="department_id" property="departmentId"/>
<result column="hiredate" property="hiredate"/>
<association property="department"
select="org.mybatis.lecture.dao.DepartmentMapper.getDepartmentById"
column="department_id"/>
</resultMap>
<!-- Employees selectEmpAndDeptBySteps(Integer id);-->
<select id="selectEmpAndDeptBySteps" resultMap="mappingEmpByStep">
select * from employees where employee_id=#{employeeId}
</select>

在association标签中，property即为Employee实体类中的属性private Department department;。然后我们要告诉这个值来自哪里。我们的话是：从org.mybatis.lecture.dao.DepartmentMapper.getDepartmentById这个方法中找来。

那个select要提供一个department_id，当然，就是我们employee这里的department_id。

这样逻辑和我们思考的就是一样的。

测试：

@Test
public void testselectEmpAndDeptBySteps() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession sqlSession = sqlSessionFactory.openSession();
try {
EmployeeMapper employeeMapper = sqlSession.getMapper(EmployeeMapper.class);
Employees employees = employeeMapper.selectEmpAndDeptBySteps(101);
System.out.println(employees.getFirstName());
} finally {
sqlSession.close();
}
}

看一下控制台的sql语句：

DEBUG 03-14 18:24:25,722 ==>
Preparing: select * from employees where employee_id=?
(BaseJdbcLogger.java:143)
DEBUG 03-14 18:24:25,793 ==> Parameters: 101(Integer)
(BaseJdbcLogger.java:143)
DEBUG 03-14 18:24:25,892 ====>
Preparing: select * from departments where department_id=?
(BaseJdbcLogger.java:143)
DEBUG 03-14 18:24:25,893 ====> Parameters: 90(Integer)
(BaseJdbcLogger.java:143)
DEBUG 03-14 18:24:25,896 <====
Total: 1
(BaseJdbcLogger.java:143)
DEBUG 03-14 18:24:25,899 <==
Total: 1
(BaseJdbcLogger.java:143)
Neena

sql语句打印了两句，也就是进行了两次查询。

但是，我们只要求打印员工的first_name，并没有要求查对应的部门信息。可不可以这样，部门信息只有在我们要查的时候才查，也就是能不能提供一种懒加载的机制？

我们要使lazyLoadingEnabled为true，使aggressiveLazyLoading为false。

<settings>
<!--日志是由log4j来实现的-->
<setting name="logImpl" value="LOG4J"/>
<!--数据库下划线的语法到java驼峰命名的映射-->
<!--<setting name="mapUnderscoreToCamelCase" value="true"/>-->
<setting name="lazyLoadingEnabled" value="true"/>
<setting name="aggressiveLazyLoading" value="false"/>
</settings>

再进行上面的测试：

DEBUG 03-14 18:27:01,128 ==>
Preparing: select * from employees where employee_id=?
(BaseJdbcLogger.java:143)
DEBUG 03-14 18:27:01,201 ==> Parameters: 101(Integer)
(BaseJdbcLogger.java:143)
DEBUG 03-14 18:27:01,394 <==
Total: 1
(BaseJdbcLogger.java:143)
Neena

只进行了一次查询。

只有你去employees.getDepartment()时才会查departments表。

collection

一个部门可能对应多个员工，这是一对多的关系。比如我们可以这么写：

SELECT
d.`department_id` dept_department_id,
d.`department_name` department_name,
e.`employee_id` employee_id,
e.`last_name` last_name,
e.`department_id` emp_department_id
FROM
departments d
LEFT JOIN employees e
ON d.`department_id` = e.`department_id`
WHERE d.`department_id` = 30 ;

这时我们应该在Department实体类中加上private List<Employees> employeesList;并提供getter方法。

接口是这么写的：

Department getDepartmentAndItsEmployeesById(Integer departmentId);

我们希望给出department_id，查部门的信息，以及找出该部门对应的所有员工。

根据sql，我们的映射sql也很好写：

 <!-- Department getDepartmentAndItsEmployeesById(Integer departmentId);-->
<select id="getDepartmentAndItsEmployeesById" resultMap="mappingDeptWithList">
SELECT
d.`department_id` dept_department_id,
d.`department_name` department_name,
e.`employee_id` employee_id,
e.`last_name` last_name,
e.`department_id` emp_department_id
FROM
departments d
LEFT JOIN
employees e
ON d.`department_id` = e.`department_id`
WHERE
d.`department_id` = #{departmentId} ;
</select>

 <resultMap id="mappingDeptWithList" type="dept">
<id column="dept_department_id" property="departmentId"/>
<result column="department_name" property="departmentName"/>
<collection property="employeesList" ofType="emp">
<id column="employee_id" property="employeeId"/>
<result column="last_name" property="lastName"/>
<result column="emp_department_id" property="departmentId"/>
</collection>
</resultMap>

在resultMap中，我们使用了collection标签来映射private List<Employees> employeesList;。ofType就是我们为Employee取的别名。

同样的，上面的操作我们可以做成分步查询。

首先，我们要为EmployeeMapper提供一个给出department_id来查找employee的方法：

Employees selectEmpByDepartmentId(Integer departmentId);

<resultMap id="mappingEmpByDepartmentId" type="emp">
<id column="employee_id" property="employeeId"/>
<result column="first_name" property="firstName"/>
<result column="last_name" property="lastName"/>
<result column="email" property="email"/>
<result column="phone_number" property="phoneNumber"/>
<result column="job_id" property="jobId"/>
<result column="salary" property="salary"/>
<result column="commission_pct" property="commissionPct"/>
<result column="manager_id" property="managerId"/>
<result column="department_id" property="departmentId"/>
<result column="hiredate" property="hiredate"/>
</resultMap>
<!--Employees selectEmpByDepartmentId(Integer departmentId);-->
<select id="selectEmpByDepartmentId" resultMap="mappingEmpByDepartmentId">
select * from employees where department_id=#{departmentId}
</select>

然后在DepartmentMapper中写出根据department_id查找部门的方法（这是分步查询的第一步）：

 Department getDepartmentAndItsEmployeeBySteps(Integer departmentId);

 <resultMap id="mappingDeptByStep" type="dept">
<id column="department_id" property="departmentId"/>
<result column="department_name" property="departmentName"/>
<result column="manager_id" property="managerId"/>
<result column="location_id" property="locationId"/>
<association property="employeesList"
select="org.mybatis.lecture.dao.EmployeeMapper.selectEmpByDepartmentId"
column="department_id"/>
</resultMap>
<!--Department getDepartmentAndItsEmployeeBySteps(Integer departmentId);-->
<select id="getDepartmentAndItsEmployeeBySteps" resultMap="mappingDeptByStep">
select * from departments where department_id=#{departmentId}
</select>

我们同样使用了association并且把department中的department_id传到了org.mybatis.lecture.dao.EmployeeMapper.selectEmpByDepartmentId，拜托它查询对应的员工信息。

最后

以上就是花痴月饼为你收集整理的MyBatis之resultMap的全部内容，希望文章能够帮你解决MyBatis之resultMap所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。