【Mybatis】collection解决一对多关联查询

在上上篇中我们用级联属性的方式把数据查出来封装到Employee中，还有另一种方式实现数据的封装。

接下来小编将介绍collection标签的相关知识，包括嵌套查询和分段查询两种方式。

现在有员工和部门两张表，前两篇文章介绍的都是根据员工查部门，本篇文章介绍由部门查所有员工

首先请参照上上篇中步骤1，2，3 , 4，此处从第5步开始

一、嵌套查询

5.新增DepartmentMapper.java接口方法

  
  

    
    

     
     

    
    
    
    

     
     

      
      public interface DepartmentMapper {	
     
     
    
    

    
    

     
     

    
    
    
    

     
     
	
      
      public Department getDeptByIdPlus(Integer id);
     
     
    
    

    
    

     
     

    
    
    
    

     
     

      
      }
     
     
    
    
  
  

6.新增DepartmentMapper.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.atguigu.mybatis.dao.DepartmentMapper">
     
     
    
    

    
    

     
     

    
    
    
    

     
     
 
     
     
    
    

    
    

     
     

    
    
    
    

     
     
	
      
      <!-- collection嵌套结果集的方式，定义关联的集合类型元素的封装规则-->
     
     
    
    

    
    

     
     

    
    
    
    

     
     
	
     
     
    
    

    
    

     
     

    
    
    
    

     
     
	
      
      <!-- 嵌套结果集的方式，使用collection标签定义关联的集合类型的属性封装规则 -->
     
     
    
    

    
    

     
     

    
    
    
    

     
     
	
      
      <resultMap type="com.atguigu.mybatis.bean.Department" id="MyDept">
     
     
    
    

    
    

     
     

    
    
    
    

     
     
		
      
      <id column="did" property="id"/>
     
     
    
    

    
    

     
     

    
    
    
    

     
     
		
      
      <result column="dept_name" property="departmentName"/>
     
     
    
    

    
    

     
     

    
    
    
    

     
     
		
      
      <!-- 
     
     
    
    

    
    

     
     

    
    
    
    

     
     

      
      		collection定义关联结合类型的属性的封装规则
     
     
    
    

    
    

     
     

    
    
    
    

     
     

      
      		ofType：指定集合里面元素的类型
     
     
    
    

    
    

     
     

    
    
    
    

     
     

      
      		-->
     
     
    
    

    
    

     
     

    
    
    
    

     
     
		
      
      <collection property="emps" ofType="com.atguigu.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>
     
     
    
    

    
    

     
     

    
    
    
    

     
     
	
     
     
    
    

    
    

     
     

    
    
    
    

     
     
	
      
      <!-- public Department getDeptByIdPlus(Integer id); -->
     
     
    
    

    
    

     
     

    
    
    
    

     
     
	
      
      <select id="getDeptByIdPlus" resultMap="MyDept" >
     
     
    
    

    
    

     
     

    
    
    
    

     
     

      
      		SELECT
     
     
    
    

    
    

     
     

    
    
    
    

     
     

      
      			d.id did,
     
     
    
    

    
    

     
     

    
    
    
    

     
     

      
      			d.dept_name dept_name,
     
     
    
    

    
    

     
     

    
    
    
    

     
     

      
      			e.id,
     
     
    
    

    
    

     
     

    
    
    
    

     
     

      
      			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>
     
     
    
    
  
  

7.新增测试方法

  
  

    
    

     
     

    
    
    
    

     
     

      
      	@Test
     
     
    
    

    
    

     
     

    
    
    
    

     
     
	
      
      public void test06() throws IOException{
     
     
    
    

    
    

     
     

    
    
    
    

     
     
		
     
     
    
    

    
    

     
     

    
    
    
    

     
     

      
      		SqlSessionFactory sqlSesssionFactory = getSqlSesssionFactory();
     
     
    
    

    
    

     
     

    
    
    
    

     
     

      
      		SqlSession openSession = sqlSesssionFactory.openSession();
     
     
    
    

    
    

     
     

    
    
    
    

     
     
		
      
      try {
     
     
    
    

    
    

     
     

    
    
    
    

     
     

      
      			DepartmentMapper mapper = openSession.getMapper(DepartmentMapper.class);
     
     
    
    

    
    

     
     

    
    
    
    

     
     

      
      			Department deptByIdPlus = mapper.getDeptByIdPlus(
      
      2);
     
     
    
    

    
    

     
     

    
    
    
    

     
     

      
      			System.out.println(deptByIdPlus);
     
     
    
    

    
    

     
     

    
    
    
    

     
     

      
      			System.out.println(deptByIdPlus.getEmps());
     
     
    
    

    
    

     
     

    
    
    
    

     
     
			
     
     
    
    

    
    

     
     

    
    
    
    

     
     

      
      		} 
      
      finally {
     
     
    
    

    
    

     
     

    
    
    
    

     
     

      
      			openSession.close();
     
     
    
    

    
    

     
     

    
    
    
    

     
     

      
      		}
     
     
    
    

    
    

     
     

    
    
    
    

     
     

      
      	}
     
     
    
    
  
  

8.控制台信息

二、分段查询

5.新增EmployeeMapperPlus.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.atguigu.mybatis.dao.EmployeeMapperPlus">
     
     
    
    

    
    

     
     

    
    
    
    

     
     
	
      
      <select id="getEmpsByDeptId" resultType="com.atguigu.mybatis.bean.Employee">
     
     
    
    

    
    

     
     

    
    
    
    

     
     

      
      	 	select * from tbl_employee where d_id=#{deptId}
     
     
    
    

    
    

     
     

    
    
    
    

     
     
	 
      
      </select>
     
     
    
    

    
    

     
     

    
    
    
    

     
     

      
      <mapper/>
     
     
    
    
  
  

6.DepartmentMapper.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.atguigu.mybatis.dao.EmployeeMapperPlus">
     
     
    
    

    
    

     
     

    
    
    
    

     
     
	
     
     
    
    

    
    

     
     

    
    
    
    

     
     

      
      <resultMap type="com.atguigu.mybatis.bean.Department" id="MyDeptStep">
     
     
    
    

    
    

     
     

    
    
    
    

     
     
		
      
      <id column="id" property="id"/>
     
     
    
    

    
    

     
     

    
    
    
    

     
     
		
      
      <id column="dept_name" property="departmentName"/>
     
     
    
    

    
    

     
     

    
    
    
    

     
     
		
      
      <collection property="emps"
     
     
    
    

    
    

     
     

    
    
    
    

     
     
			
      
      select=
      
      "com.atguigu.mybatis.dao.EmployeeMapperPlus.getEmpsByDeptId"
     
     
    
    

    
    

     
     

    
    
    
    

     
     
			
      
      column=
      
      "{deptId=id}" 
      
      fetchType=
      
      "lazy">
      
      </collection>
     
     
    
    

    
    

     
     

    
    
    
    

     
     

      
      </resultMap>
     
     
    
    

    
    

     
     

    
    
    
    

     
     
	
      
      <!-- public Department getDeptByIdStep(Integer id); -->
     
     
    
    

    
    

     
     

    
    
    
    

     
     
	
      
      <select id="getDeptByIdStep" resultMap="MyDeptStep">
     
     
    
    

    
    

     
     

    
    
    
    

     
     

      
      		select id,dept_name departmentName from tbl_dept where id=#{id}
     
     
    
    

    
    

     
     

    
    
    
    

     
     
	
      
      </select>
     
     
    
    

    
    

     
     

    
    
    
    

     
     
 
     
     
    
    

    
    

     
     

    
    
    
    

     
     

      
      <mapper/>
     
     
    
    
  
  

7.新增测试方法

  
  

    
    

     
     

    
    
    
    

     
     

      
      @Test
     
     
    
    

    
    

     
     

    
    
    
    

     
     
	
      
      public void test06() throws IOException{
     
     
    
    

    
    

     
     

    
    
    
    

     
     
		
     
     
    
    

    
    

     
     

    
    
    
    

     
     

      
      		SqlSessionFactory sqlSesssionFactory = getSqlSesssionFactory();
     
     
    
    

    
    

     
     

    
    
    
    

     
     

      
      		SqlSession openSession = sqlSesssionFactory.openSession();
     
     
    
    

    
    

     
     

    
    
    
    

     
     
		
      
      try {
     
     
    
    

    
    

     
     

    
    
    
    

     
     

      
      			DepartmentMapper mapper = openSession.getMapper(DepartmentMapper.class);
     
     
    
    

    
    

     
     

    
    
    
    

     
     

      
      			Department deptByIdStep = mapper.getDeptByIdStep(
      
      1);
     
     
    
    

    
    

     
     

    
    
    
    

     
     

      
      			System.out.println(deptByIdStep);		
     
     
    
    

    
    

     
     

    
    
    
    

     
     

      
      		} 
      
      finally {
     
     
    
    

    
    

     
     

    
    
    
    

     
     

      
      			openSession.close();
     
     
    
    

    
    

     
     

    
    
    
    

     
     

      
      		}
     
     
    
    

    
    

     
     

    
    
    
    

     
     

      
      	}
     
     
    
    
  
  

8.控制台信息：分步查询效果