H__D  
【Mybatis】MyBatis之表的关联查询（五）

　　本章介绍Mybatis之表的关联查询

一对一关联

　　查询员工信息以及员工的部门信息

　　1、准备表employee员工表，department部门表

1 CREATE TABLE `employee` (
2   `id` int(11) NOT NULL AUTO_INCREMENT,
3   `last_name` varchar(255) DEFAULT NULL,
4   `gender` char(1) DEFAULT NULL,
5   `email` varchar(255) DEFAULT NULL,
6   `dept_id` int(11) DEFAULT NULL COMMENT '部门ID',
7   PRIMARY KEY (`id`)
8 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1 CREATE TABLE `department` (
2   `id` int(11) NOT NULL AUTO_INCREMENT,
3   `dep_name` varchar(255) DEFAULT NULL,
4   PRIMARY KEY (`id`)
5 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

　　2、新建员工实体类（EmployeePlus.java）,以及部门实体类（Department.java）

 1 package com.hd.test.pojo;
 2 
 3 public class EmployeePlus {
 4 
 5     private Integer id;
 6     private String lastName;
 7     private String gender;
 8     private String email;
 9     
10     private Department dept;
11     
12     public EmployeePlus() {
13         // TODO Auto-generated constructor stub
14     }
15     
16     public EmployeePlus(String lastName, String gender, String email) {
17         super();
18         this.lastName = lastName;
19         this.gender = gender;
20         this.email = email;
21     }
22     
23     
24     public EmployeePlus(Integer id, String lastName, String gender, String email) {
25         super();
26         this.id = id;
27         this.lastName = lastName;
28         this.gender = gender;
29         this.email = email;
30     }
31 
32 
33     public Integer getId() {
34         return id;
35     }
36     public void setId(Integer id) {
37         this.id = id;
38     }
39     public String getLastName() {
40         return lastName;
41     }
42     public void setLastName(String lastName) {
43         this.lastName = lastName;
44     }
45     public String getGender() {
46         return gender;
47     }
48     public void setGender(String gender) {
49         this.gender = gender;
50     }
51     public String getEmail() {
52         return email;
53     }
54     public void setEmail(String email) {
55         this.email = email;
56     }
57 
58     public Department getDept() {
59         return dept;
60     }
61 
62 
63     public void setDept(Department dept) {
64         this.dept = dept;
65     }
66 
67     @Override
68     public String toString() {
69         return "EmployeePlus [id=" + id + ", lastName=" + lastName + ", gender=" + gender + ", email=" + email
70                 + ", dept=" + dept + "]";
71     }
72     
73     
74 }

 

 1 package com.hd.test.pojo;
 2 
 3 public class Department {
 4     
 5     private Integer id;
 6     private String deptName;
 7     
 8     public Integer getId() {
 9         return id;
10     }
11     public void setId(Integer id) {
12         this.id = id;
13     }
14     public String getDeptName() {
15         return deptName;
16     }
17     public void setDeptName(String deptName) {
18         this.deptName = deptName;
19     }
20     
21     @Override
22     public String toString() {
23         return "Department [id=" + id + ", deptName=" + deptName + "]";
24     }
25     
26 }

　　3、编辑sql映射文件EmployeeMapperPlus.xml，文件中有2种resultMap，都可以使用

 1 <?xml version="1.0" encoding="UTF-8" ?>
 2 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
 3 
 4 <mapper namespace="com.hd.test.mapper.EmployeeMapperPlus">
 5 
 6     <!-- 联合查询：级联属性封装结果集 -->
 7     <!-- 封装方式1:普通封装 -->
 8     <resultMap type="com.hd.test.pojo.EmployeePlus" id="MyEmployeeDept">
 9         <id column="id" property="id"/>
10         <result column="last_name" property="lastName"/>
11         <result column="gender" property="gender"/>
12         <result column="did" property="dept.id"/>
13         <result column="dept_name" property="dept.deptName"/>
14     </resultMap>
15     
16     <!-- 封装方式2:采用association标签的方式封装 -->
17     <resultMap type="com.hd.test.pojo.EmployeePlus" id="MyEmployeeDept2">
18         <id column="id" property="id"/>
19         <result column="last_name" property="lastName"/>
20         <result column="gender" property="gender"/>
21             
22         <association property="dept" javaType="com.hd.test.pojo.Department">
23             <id column="did" property="id"/>
24             <result column="dept_name" property="deptName"/>
25         </association>
26     </resultMap>
27     
28     <select id="getMyEmployeeDept" resultMap="MyEmployeeDept" >  <!-- resultMap="MyEmployeeDept2" -->
29         SELECT
30             emp.id,
31             emp.last_name,
32             emp.gender,
33             dept.id AS did,
34             dept.dep_name AS dept_name 
35         FROM
36             employee emp
37             LEFT JOIN department dept ON emp.dept_id = dept.id
38         WHERE 
39             emp.id = #{id}
40     </select>
41 
42 </mapper>

　　4、在mybatis-config.xml文件中注册EmployeeMapperPlus.xml

1 <mappers>
2     <!-- 添加sql射文件到Mybatis的全局配置文件中 -->
3     <mapper resource="mapper/EmployeeMapperPlus.xml" />
4 </mappers>

　　5、编写接口（DepartmentPlusMapper.java）

1 package com.hd.test.mapper;
2 
3 import com.hd.test.pojo.DepartmentPlus;
4 
5 public interface DepartmentPlusMapper {
6     
7     public DepartmentPlus getDeparmentAndEmp(Integer id);
8     
9 }

 　　6、测试类

 1 @Test
 2 public void test01() throws IOException {
 3     
 4     InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
 5     SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
 6     
 7     SqlSession session = sqlSessionFactory.openSession();
 8     
 9     try {
10         EmployeeMapperPlus mapper = session.getMapper(EmployeeMapperPlus.class);
11         EmployeePlus employeePlus = mapper.getMyEmployeeDept(1);
12         System.out.println(employeePlus);
13         
14     } catch (Exception e) {
15         e.printStackTrace();
16     }finally {
17         session.close();
18     }
19     
20 }

　　7、执行结果，可以看到员工和部门信息同时查出来了，如下：

　　　　 

 　　一对一关联查询拓展

　　　　a、分步的形式查询，即先查员工信息，然后查部门执行，需要执行2次sql

　　　　　　1）编辑EmployeeMapperPlus.xml文件，新增内容

 1 <resultMap type="com.hd.test.pojo.EmployeePlus" id="MyEmployeeByStep">
 2     <id column="id" property="id"/>
 3     <result column="last_name" property="lastName"/>
 4     <result column="gender" property="gender"/>
 5         
 6     <association property="dept" select="com.hd.test.mapper.DepartmentMapper.getDeparmentById" column="dept_id">
 7         
 8     </association>
 9 </resultMap>
10 
11 <select id="getMyEmployeeByStep" resultMap="MyEmployeeByStep" >
12     SELECT id, last_name, gender, dept_id FROM employee WHERE id = #{id}
13 </select> 

　　　　　　2）新增一个 DepartmentMapper.xml 文件，并且也在mybatis-config.xml文件中注册

1 <?xml version="1.0" encoding="UTF-8" ?>
2 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
3 
4 <mapper namespace="com.hd.test.mapper.DepartmentMapper">
5     <select id="getDeparmentById" resultType="com.hd.test.pojo.Department">
6         select id, dep_name deptName from department where id =
7         #{id}
8     </select>
9 </mapper>

 　　　　　　3、调用方法如下：

 1 @Test
 2 public void test02() throws IOException {
 3     
 4     InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
 5     SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
 6     
 7     SqlSession session = sqlSessionFactory.openSession();
 8     
 9     try {
10         EmployeeMapperPlus mapper = session.getMapper(EmployeeMapperPlus.class);
11         EmployeePlus employeePlus = mapper.getMyEmployeeByStep(1);
12         System.out.println(employeePlus.getLastName());
13         System.out.println(employeePlus.getDept());
14     } catch (Exception e) {
15         e.printStackTrace();
16     }finally {
17         session.close();
18     }
19     
20 }

　　　　　　4、执行结果如下，可以从日志中看到，先执行sql查询了员工信息表的数据，然后在执行sql查询了部门表的数据，分两次查询

　　　　　　　　

　　　　b、分步的形式懒加载查询，即先查员工信息，然后在需要使用部门信息的时候，在去查询部门信息

　　　　　　方法1：在上面分步的形式查询基础上，在mybatis-config.xml文件中设置全局变量，然后执行

1 <settings>
2     <!-- 延迟加载的全局开关。当开启时，所有关联对象都会延迟加载。 特定关联关系中可通过设置fetchType属性来覆盖该项的开关状态。 -->
3     <setting name="lazyloadingenabled" value="true"/>
4     <!-- 当开启时，任何方法的调用都会加载该对象的所有属性。否则，每个属性会按需加载 -->
5     <setting name="aggressivelazyloading" value="false"/>
6 </settings>　　　　

　　　　　　　　　　　　

　　　　　　方法2：在上面分步的形式查询基础上，在sql配置文件中，配置resultMap中association标签的 fetchType 属性，fetchType 属性有2个值：lazy（懒加载），eager（立即加载），然后执行

1 <resultMap type="com.hd.test.pojo.EmployeePlus" id="MyEmployeeByStep">
2     <id column="id" property="id"/>
3     <result column="last_name" property="lastName"/>
4     <result column="gender" property="gender"/>
5         
6     <association property="dept" select="com.hd.test.mapper.DepartmentMapper.getDeparmentById" column="dept_id" fetchType="lazy">
7     </association>
8 </resultMap>

　　　　　　优先级：association标签的 fetchType 属性设置的优先级高于mybatis-config.xml文件中的e设置 

一对多关联

　　查询部门信息，以及部门下所有员工信息

　　1、表结构上，2张表，员工和部门

　　2、新建部门实体类（DepartmentPlus.java）,以及员工实体类（Employee.java）

　　DepartmentPlus.java

 1 package com.hd.test.pojo;
 2 
 3 import java.util.List;
 4 
 5 public class DepartmentPlus {
 6     
 7     private Integer id;
 8     private String deptName;
 9     
10     private List<Employee> emps;
11     
12     public Integer getId() {
13         return id;
14     }
15     public void setId(Integer id) {
16         this.id = id;
17     }
18     public String getDeptName() {
19         return deptName;
20     }
21     public void setDeptName(String deptName) {
22         this.deptName = deptName;
23     }
24     @Override
25     public String toString() {
26         return "DepartmentPlus [id=" + id + ", deptName=" + deptName + ", emps=" + emps + "]";
27     }
28     public List<Employee> getEmps() {
29         return emps;
30     }
31     public void setEmps(List<Employee> emps) {
32         this.emps = emps;
33     }
34     
35 }

　　Employee.java  

 1 package com.hd.test.pojo;
 2 
 3 public class Employee {
 4 
 5     private Integer id;
 6     private String lastName;
 7     private String gender;
 8     private String email;
 9     
10 
11     public Integer getId() {
12         return id;
13     }
14     public void setId(Integer id) {
15         this.id = id;
16     }
17     public String getLastName() {
18         return lastName;
19     }
20     public void setLastName(String lastName) {
21         this.lastName = lastName;
22     }
23     public String getGender() {
24         return gender;
25     }
26     public void setGender(String gender) {
27         this.gender = gender;
28     }
29     public String getEmail() {
30         return email;
31     }
32     public void setEmail(String email) {
33         this.email = email;
34     }
35     @Override
36     public String toString() {
37         return "Employee [id=" + id + ", lastName=" + lastName + ", gender=" + gender + ", email=" + email + "]";
38     }
39     
40     
41 }

　　3、编辑sql映射文件DepartmentPlusMapper.xml，文件中有2种查询方式，一种一次性查处所有数据，另一种分步查询数据

<?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.hd.test.mapper.DepartmentPlusMapper">
    
    <!--嵌套结果集的方式，使用collection标签定义关联的集合类型的属性封装规则  -->
    <resultMap type="com.hd.test.pojo.DepartmentPlus" id="DeparmentAndEmpMap">
        <id column="id" property="id"/>
        <id column="dep_name"  property="deptName"/>
        <!-- 
            collection定义关联集合类型的属性的封装规则 
            ofType:指定集合里面元素的类型
        -->
        <collection property="emps" ofType="com.hd.test.pojo.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="getDeparmentAndEmp" resultMap="DeparmentAndEmpMap">
        SELECT
            dept.id,
            dept.dep_name,
            emp.id AS eid,
            emp.last_name,
            emp.gender 
        FROM
            department dept
            LEFT JOIN employee emp ON dept.id = emp.dept_id 
        WHERE dept.id = #{id}
    </select>
    
    <!-- 分步查询方式  -->
    <resultMap type="com.hd.test.pojo.DepartmentPlus" id="DeparmentAndEmpMapByStep">
        <id column="id" property="id"/>
        <id column="dep_name"  property="deptName"/>
        <collection property="emps" select="com.hd.test.mapper.EmployeeMapper.getEmployeeByDeptId" column="id" fetchType="eager">
        </collection>
    </resultMap>
    

    <!-- 
        resultMap-collection 扩展：多列的值传递过去, 将多列的值封装map传递；
            column="{key1=column1,key2=column2}"
            
        如下也是可行的
        <collection property="emps" select="com.hd.test.mapper.EmployeeMapper.getEmployeeByDeptId" column="{deptId=id}" fetchType="eager">
        </collection>
     -->

    <select id="getDeparmentAndEmpMapByStep" resultMap="DeparmentAndEmpMapByStep">
        SELECT id, dep_name FROM department WHERE id = #{id}
    </select>
    
</mapper>

　　4、编辑EmployeeMapper.xml文件

1 <?xml version="1.0" encoding="UTF-8" ?>
2 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
3 
4 <mapper namespace="com.hd.test.mapper.EmployeeMapper">
5     <select id="getEmployeeByDeptId" resultType="com.hd.test.pojo.Employee">
6         select id, last_name lastName, gender, email from employee where dept_id = #{deptId}
7     </select>
8 </mapper>

　　5、在mybatis-config.xml文件中注册DepartmentPlusMapper.xml 和 EmployeeMapper.xml文件

　　6、编写接口（DepartmentPlusMapper.java）

 1 package com.hd.test.mapper;
 2 
 3 import com.hd.test.pojo.DepartmentPlus;
 4 
 5 public interface DepartmentPlusMapper {
 6     
 7     public DepartmentPlus getDeparmentAndEmp(Integer id);
 8     
 9     public DepartmentPlus getDeparmentAndEmpMapByStep(Integer id);
10 
11 }

　　7、测试类　　

 1 @Test
 2 public void test03() throws IOException {
 3     
 4     InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
 5     SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
 6     
 7     SqlSession session = sqlSessionFactory.openSession();
 8     
 9     try {
10         DepartmentPlusMapper mapper = session.getMapper(DepartmentPlusMapper.class);
11         DepartmentPlus departmentPlus = mapper.getDeparmentAndEmp(1);
12         System.out.println(departmentPlus);
13         // System.out.println(employeePlus.getDept());
14     } catch (Exception e) {
15         e.printStackTrace();
16     }finally {
17         session.close();
18     }
19     
20 }
21 
22 
23 @Test
24 public void test04() throws IOException {
25     
26     InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
27     SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
28     
29     SqlSession session = sqlSessionFactory.openSession();
30     
31     try {
32         DepartmentPlusMapper mapper = session.getMapper(DepartmentPlusMapper.class);
33         DepartmentPlus departmentPlus = mapper.getDeparmentAndEmpMapByStep(1);
34         System.out.println(departmentPlus.getDeptName());
35         System.out.println(departmentPlus.getEmps());
36     } catch (Exception e) {
37         e.printStackTrace();
38     }finally {
39         session.close();
40     }
41     
42 }

　　8、执行结果，可以看到部门信息以及部门下所有员工信息同时查出来了，如下：

　　　　a、一次性查询方法：getDeparmentAndEmp()，结果：　

　　　　　　

　　　　b、分步查询方法：getDeparmentAndEmpMapByStep()，结果：

　　　　　　

 

posted on 2019-01-26 17:03  H__D  阅读(729)  评论(0)  编辑  收藏  举报
 
Copyright © 2024 H__D
Powered by .NET 9.0 on Kubernetes
博客园