【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.控制台信息：分步查询效果