天天看點
傳回

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();
        }
    }
}
      
<?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>
mybatis sql java xml 封裝
上一篇: PHP imagecharup - 垂直地畫一個字元
下一篇: 淺析Mybatis的resultMap的用法以及關聯結果集映射

繼續閱讀