Mybatis学习笔记8 - resultMap自定义结果集映射规则

一、resultMap自定义结果集映射规则

示例如下：

接口定义：
package com.mybatis.dao;

import com.mybatis.bean.Employee;

public interface EmployeeMapper {
    public Employee getEmpById(Integer id);
}

mapper定义：
<?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.mybatis.dao.EmployeeMapper">
    <!--
    自定义某个javaBean的封装规则
	type：自定义规则的Java类型
	id:唯一标示id，方便引用
	-->
    <resultMap id="EmpMap" type="com.mybatis.bean.Employee">
        <!--
        指定主键列的封装规则
		id定义主键会底层有优化；
		column：指定哪一列
		property：指定对应的javaBean属性
		-->
        <id column="id" property="id"/>
        <!-- 定义普通列封装规则 -->
        <result column="last_name" property="lastName"/>
        <!-- 其他不指定的列会自动封装：但是最好只要写resultMap就把全部的映射规则都写上。 -->
        <result column="email" property="email"/>
        <result column="gender" property="gender"/>
    </resultMap>
    <!--public Employee getEmpById(Integer id);-->
    <!-- resultMap:自定义结果集映射规则；  -->
    <select id="getEmpById" resultMap="EmpMap">
		select * from tbl_employee where id=#{id}
	</select>
</mapper>


测试代码：
package com.mybatis.demo;

import java.io.*;
import java.util.*;

import com.mybatis.bean.Employee;
import com.mybatis.dao.EmployeeMapper;
import org.apache.ibatis.io.*;
import org.apache.ibatis.session.*;
import org.junit.Test;

public class MyTest {
    public SqlSessionFactory getSqlSessionFactory() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        return new SqlSessionFactoryBuilder().build(inputStream);
    }

    @Test
    public void testResultMap() throws IOException {
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
        SqlSession openSession = sqlSessionFactory.openSession(true);
        try {
            EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
            Employee employee = mapper.getEmpById(1);
            System.out.println(employee);
        } finally {
            openSession.close();
        }
    }
}

 二、resultMap使用场景

(一)、查询Employee的同时查询员工对应的部门。

1、联合查询：级联属性封装结果集。

员工实体类Employee

package com.mybatis.bean;

public class Employee {
    private Integer id;
    private String lastName;
    private String email;
    private Integer gender;
    private Department dept;

    public Employee() {
    }

    public Employee(String lastName, String email, Integer gender) {
        this.lastName = lastName;
        this.email = email;
        this.gender = gender;
    }

    public Employee(Integer id, String lastName, String email, Integer gender) {
        this.id = id;
        this.lastName = lastName;
        this.email = email;
        this.gender = gender;
    }

    public Integer getId() {
        return id;
    }

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

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public Integer getGender() {
        return gender;
    }

    public void setGender(Integer gender) {
        this.gender = gender;
    }

    public Department getDept() {
        return dept;
    }

    public void setDept(Department dept) {
        this.dept = dept;
    }

    @Override
    public String toString() {
        return "Employee{" +
                "id=" + id +
                ", lastName='" + lastName + '\'' +
                ", email='" + email + '\'' +
                ", gender=" + gender +
                ", dept=" + dept +
                '}';
    }
}

部门实体类Deptment

package com.mybatis.bean;

public class Department {
    private Integer id;
    private String departmentName;

    public Department() {
    }

    public Department(Integer id, String departmentName) {
        this.id = id;
        this.departmentName = departmentName;
    }

    public Integer getId() {
        return id;
    }

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

    public String getDepartmentName() {
        return departmentName;
    }

    public void setDepartmentName(String departmentName) {
        this.departmentName = departmentName;
    }

    @Override
    public String toString() {
        return "Department{" +
                "id=" + id +
                ", departmentName='" + departmentName + '\'' +
                '}';
    }
}

建立部门表及修改员工表的sql脚本如下：

CREATE TABLE tbl_dept(
	id INT(11) PRIMARY KEY AUTO_INCREMENT,
	dept_name VARCHAR(255)
);

alter table tbl_employee add column d_id int(11);

alter table tbl_employee add constraint fk_emp_dept
foreign key(d_id) references tbl_dept(id);

示例如下：

接口定义：
package com.mybatis.dao;

import com.mybatis.bean.Employee;

public interface EmployeeMapper {
    public Employee getEmpByIdWithDept(Integer id);
}

mapper定义：
<?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.mybatis.dao.EmployeeMapper">
    <!--联合查询：级联属性封装结果集-->
    <resultMap id="EmpMap" type="com.mybatis.bean.Employee">
        <id column="id" property="id"/>
        <result column="last_name" property="lastName"/>
        <result column="gender" property="gender"/>
        <result column="did" property="dept.id"/>
        <result column="dept_name" property="dept.departmentName"/>
    </resultMap>
    <!--public Employee getEmpById(Integer id);-->
    <select id="getEmpByIdWithDept" resultMap="EmpMap">
        SELECT e.id id,e.last_name last_name,e.gender gender,e.d_id d_id,
		d.id did,d.dept_name dept_name FROM tbl_employee e,tbl_dept d
		WHERE e.d_id=d.id AND e.id=#{id}
    </select>
</mapper>

测试代码：
package com.mybatis.demo;

import java.io.*;
import java.util.*;

import com.mybatis.bean.Employee;
import com.mybatis.dao.EmployeeMapper;
import org.apache.ibatis.io.*;
import org.apache.ibatis.session.*;
import org.junit.Test;

public class MyTest {
    public SqlSessionFactory getSqlSessionFactory() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        return new SqlSessionFactoryBuilder().build(inputStream);
    }

    @Test
    public void testResultMap() throws IOException {
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
        SqlSession openSession = sqlSessionFactory.openSession(true);
        try {
            EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
            Employee employee = mapper.getEmpByIdWithDept(1);
            System.out.println(employee);
        } finally {
            openSession.close();
        }
    }
}

2、联合查询：使用association定义关联的单个对象的封装规则

示例如下：

接口定义：
package com.mybatis.dao;

import com.mybatis.bean.Employee;

public interface EmployeeMapper {
    public Employee getEmpByIdWithDept(Integer id);
}

mapper定义：
<?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.mybatis.dao.EmployeeMapper">
    <!--使用association定义关联的单个对象的封装规则-->
    <resultMap id="EmpMap" type="com.mybatis.bean.Employee">
        <id column="id" property="id"/>
        <result column="last_name" property="lastName"/>
        <result column="gender" property="gender"/>
        <!--
        association可以指定联合的javaBean对象
		property="dept"：指定哪个属性是联合的对象
		javaType:指定这个属性对象的类型[不能省略]
		-->
        <association property="dept" javaType="com.mybatis.bean.Department">
            <id column="did" property="id"/>
            <result column="dept_name" property="departmentName"/>
        </association>
    </resultMap>
    <!--public Employee getEmpById(Integer id);-->
    <select id="getEmpByIdWithDept" resultMap="EmpMap">
        SELECT e.id id,e.last_name last_name,e.gender gender,e.d_id d_id,
		d.id did,d.dept_name dept_name FROM tbl_employee e,tbl_dept d
		WHERE e.d_id=d.id AND e.id=#{id}
    </select>
</mapper>

测试代码：
package com.mybatis.demo;

import java.io.*;
import java.util.*;

import com.mybatis.bean.Employee;
import com.mybatis.dao.EmployeeMapper;
import org.apache.ibatis.io.*;
import org.apache.ibatis.session.*;
import org.junit.Test;

public class MyTest {
    public SqlSessionFactory getSqlSessionFactory() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        return new SqlSessionFactoryBuilder().build(inputStream);
    }

    @Test
    public void testResultMap() throws IOException {
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
        SqlSession openSession = sqlSessionFactory.openSession(true);
        try {
            EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
            Employee employee = mapper.getEmpByIdWithDept(1);
            System.out.println(employee);
        } finally {
            openSession.close();
        }
    }
}

 3、使用association进行分步查询

DeptmentMapper接口定义：
package com.mybatis.dao;

import com.mybatis.bean.Department;

public interface DeptmentMapper {
    public Department getDeptById(Integer id);
}

DeptmentMapper.xml定义
<?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.mybatis.dao.DeptmentMapper">
    <!--public Department getDeptById(Integer id);-->
    <select id="getDeptById" resultType="com.mybatis.bean.Department">
        select id,dept_name departmentName from tbl_dept where id=#{id}
    </select>
</mapper>

EmployeeMapper接口定义：
package com.mybatis.dao;

import com.mybatis.bean.Employee;

public interface EmployeeMapper {
    public Employee getEmpByIdWithDept(Integer id);
}

EmployeeMapper.xml定义：
<?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.mybatis.dao.EmployeeMapper">
    <!--
        使用association进行分步查询：
		1、先按照员工id查询员工信息
		2、根据查询员工信息中的d_id值去部门表查出部门信息
		3、部门设置到员工中；
	-->
    <resultMap type="com.mybatis.bean.Employee" id="MyEmpByStep">
        <id column="id" property="id"/>
        <result column="last_name" property="lastName"/>
        <result column="email" property="email"/>
        <result column="gender" property="gender"/>
        <!-- association定义关联对象的封装规则
            select:表明当前属性是调用select指定的方法查出的结果
            column:指定将哪一列的值传给这个方法

            流程：使用select指定的方法（传入column指定的这列参数的值）查出对象，并封装给property指定的属性
         -->
        <association property="dept"
                     select="com.mybatis.dao.DeptmentMapper.getDeptById"
                     column="d_id">
        </association>
    </resultMap>

    <select id="getEmpByIdWithDept" resultMap="MyEmpByStep">
       select * from tbl_employee where id=#{id}
    </select>
</mapper>

测试代码：
package com.mybatis.demo;

import java.io.*;
import java.util.*;

import com.mybatis.bean.Department;
import com.mybatis.bean.Employee;
import com.mybatis.dao.DeptmentMapper;
import com.mybatis.dao.EmployeeMapper;
import org.apache.ibatis.io.*;
import org.apache.ibatis.session.*;
import org.junit.Test;

public class MyTest {
    public SqlSessionFactory getSqlSessionFactory() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        return new SqlSessionFactoryBuilder().build(inputStream);
    }

    @Test
    public void testResultMap() throws IOException {
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
        SqlSession openSession = sqlSessionFactory.openSession(true);
        try {
            EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
            Employee employee = mapper.getEmpByIdWithDept(1);
            System.out.println(employee);
            System.out.println(employee.getDept());
        } finally {
            openSession.close();
        }
    }
}

4、延迟加载(懒加载、按需加载)

Employee==>Dept：
每次查询Employee对象的时候，部门信息在使用的时候再去查询；
只需在分步查询的基础之上加上两个配置：

<settings>
	<setting name="lazyLoadingEnabled" value="true"/>
	<setting name="aggressiveLazyLoading" value="false"/>
</settings>

(二)、查询部门的同时查询出该部门的所有员工。

1、嵌套结果集的方式，使用collection标签定义关联的集合类型的属性

Department实体类修改如下：

package com.mybatis.bean;

import java.util.List;

public class Department {
    private Integer id;
    private String departmentName;
    private List<Employee> emps;

    public Department() {
    }

    public Department(Integer id, String departmentName) {
        this.id = id;
        this.departmentName = departmentName;
    }

    public Integer getId() {
        return id;
    }

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

    public String getDepartmentName() {
        return departmentName;
    }

    public void setDepartmentName(String departmentName) {
        this.departmentName = departmentName;
    }

    public List<Employee> getEmps() {
        return emps;
    }

    public void setEmps(List<Employee> emps) {
        this.emps = emps;
    }

    @Override
    public String toString() {
        return "Department{" +
                "id=" + id +
                ", departmentName='" + departmentName + '\'' +
                ", emps=" + emps +
                '}';
    }
}

示例如下：

接口定义：
package com.mybatis.dao;

import com.mybatis.bean.Department;

public interface DeptmentMapper {
    public Department getDeptByIdWithEmp(Integer id);
}

mapper定义：
<?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.mybatis.dao.DeptmentMapper">
    <!--嵌套结果集的方式，使用collection标签定义关联的集合类型的属性封装规则  -->
    <resultMap id="MyDept" type="com.mybatis.bean.Department">
        <id column="did" property="id"/>
        <result column="dept_name" property="departmentName"/>
        <!--
			collection定义关联集合类型的属性的封装规则
			ofType:指定集合里面元素的类型
		-->
        <collection property="emps" ofType="com.mybatis.bean.Employee">
            <id column="eid" property="id"/>
            <result column="last_name" property="lastName"/>
            <result column="email" property="email"/>
            <result column="gender" property="gender"/>
        </collection>
    </resultMap>
    <select id="getDeptByIdWithEmp" resultMap="MyDept">
        SELECT d.id did,d.dept_name dept_name,
				e.id eid,e.last_name last_name,e.email email,e.gender gender
		FROM tbl_dept d
		LEFT JOIN tbl_employee e
		ON d.id=e.d_id
		WHERE d.id=#{id}
    </select>
</mapper>

测试代码：
package com.mybatis.demo;

import java.io.*;
import java.util.*;

import com.mybatis.bean.Department;
import com.mybatis.bean.Employee;
import com.mybatis.dao.DeptmentMapper;
import com.mybatis.dao.EmployeeMapper;
import org.apache.ibatis.io.*;
import org.apache.ibatis.session.*;
import org.junit.Test;

public class MyTest {
    public SqlSessionFactory getSqlSessionFactory() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        return new SqlSessionFactoryBuilder().build(inputStream);
    }

    @Test
    public void testResultMap() throws IOException {
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
        SqlSession openSession = sqlSessionFactory.openSession(true);
        try {
            DeptmentMapper mapper=openSession.getMapper(DeptmentMapper.class);
            Department department=mapper.getDeptByIdWithEmp(1);
            System.out.println(department);
        } finally {
            openSession.close();
        }
    }
}

2、分步查询

示例代码：

EmployeeMapper接口定义：
package com.mybatis.dao;

import com.mybatis.bean.Employee;

import java.util.List;

public interface EmployeeMapper {
    public List<Employee> getEmpsByDeptId(Integer deptId);
}

EmployeeMapper.xml文件定义：
<?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.mybatis.dao.EmployeeMapper">
    <select id="getEmpsByDeptId" resultType="com.mybatis.bean.Employee">
       select * from tbl_employee where d_id=#{deptId}
    </select>
</mapper>

DeptmentMapper接口定义：
package com.mybatis.dao;

import com.mybatis.bean.Department;

public interface DeptmentMapper {
    public Department getDeptByIdWithEmpStep(Integer id);
}

DeptmentMapper.xml文件定义：
<?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.mybatis.dao.DeptmentMapper">
    <resultMap id="MyDeptStep" type="com.mybatis.bean.Department">
        <id column="id" property="id"/>
        <id column="dept_name" property="departmentName"/>
        <collection property="emps"
                    select="com.mybatis.dao.EmployeeMapper.getEmpsByDeptId"
                    column="id"/>
    </resultMap>

    <select id="getDeptByIdWithEmpStep" resultMap="MyDeptStep">
      select id,dept_name departmentName from tbl_dept where id=#{id}
    </select>
</mapper>

测试代码：
package com.mybatis.demo;

import com.mybatis.bean.Department;
import com.mybatis.dao.DeptmentMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;

public class MyTest {
    public SqlSessionFactory getSqlSessionFactory() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        return new SqlSessionFactoryBuilder().build(inputStream);
    }

    @Test
    public void testResultMap() throws IOException {
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
        SqlSession openSession = sqlSessionFactory.openSession(true);
        try {
            DeptmentMapper mapper = openSession.getMapper(DeptmentMapper.class);
            Department department = mapper.getDeptByIdWithEmpStep(2);
            System.out.println(department);
        } finally {
            openSession.close();
        }
    }
}

扩展：多列的值传递过去：

　　将多列的值封装map传递；
　　column="{key1=column1,key2=column2}"
　　fetchType="lazy"：表示使用延迟加载；
　　　　- lazy：延迟加载
　　　　- eager：立即加载

<?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.mybatis.dao.DeptmentMapper">
    <resultMap id="MyDeptStep" type="com.mybatis.bean.Department">
        <id column="id" property="id"/>
        <id column="dept_name" property="departmentName"/>
        <!-- 扩展：多列的值传递过去：
			将多列的值封装map传递；
			column="{key1=column1,key2=column2}"
		fetchType="lazy"：表示使用延迟加载；
				- lazy：延迟
				- eager：立即
	    -->
        <collection property="emps"
                    select="com.mybatis.dao.EmployeeMapper.getEmpsByDeptId"
                    column="{deptId=id}" fetchType="lazy"/>
    </resultMap>

    <select id="getDeptByIdWithEmpStep" resultMap="MyDeptStep">
      select id,dept_name departmentName from tbl_dept where id=#{id}
    </select>
</mapper>