Mybatis通过collection联合查询

package com.atgui.spring.mybatis.entities;

import java.util.List;

public class Department {
	private Integer id;
	private String name;
	private List<Employee> emps;
	@Override
	public String toString() {
		return "Department [id=" + id + ", name=" + name + ", emps=" + emps + "]";
	}
	public List<Employee> getEmps() {
		return emps;
	}
	public void setEmps(List<Employee> emps) {
		this.emps = emps;
	}
	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
}

package com.atgui.spring.mybatis.entities;

public class Employee {
	private Integer id;
	private String lastName;
	private String email;
	private double salary;
	private Integer deptId;
	private Department dept;
	
	public Department getDept() {
		return dept;
	}
	public void setDept(Department dept) {
		this.dept = dept;
	}
	public Employee() {
		super();
	}
	public Employee(Integer id, String lastName, String email, double salary, Integer deptId) {
		super();
		this.id = id;
		this.lastName = lastName;
		this.email = email;
		this.salary = salary;
		this.deptId = deptId;
	}
	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 double getSalary() {
		return salary;
	}
	public void setSalary(double salary) {
		this.salary = salary;
	}
	public Integer getDeptId() {
		return deptId;
	}
	public void setDeptId(Integer deptId) {
		this.deptId = deptId;
	}
	@Override
	public String toString() {
		return "Employee2 [id=" + id + ", lastName=" + lastName + ", email=" + email + ", salary=" + salary
				+ ", deptId=" + deptId + ", dept=" + dept + "]";
	}
	
	
}

<?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">
<!-- namespace：要实现的接口的全类名 -->
<mapper namespace="com.atguigu.mybatis.mapper.DepartmentMapper">

	  <select id="getDepartmentById" resultType="com.atguigu.mybatis.entities.Department">
	  	select id,name deptName from departments where id = #{deptId}
	  </select>
	  
	  <!-- 需求二：在将部门查出来的同时将部门下所有的员工也查询出来 -->
	  <select id="getDepartmentContainsEmps" resultMap="customMap">
	  	SELECT d.id d_id,d.name d_name,e.*
		FROM departments d
		LEFT JOIN employees e
		ON d.id = e.dept_id
		WHERE d.id = #{id};
	  </select>
	  
	  <!-- 自定义结果集 -->
	  <resultMap type="com.atguigu.mybatis.entities.Department" id="customMap">
	  	<!-- 映射主键 -->
	  	<id column="d_id" property="id"/>
	  	<!-- 映射其他列 -->
	  	<result column="d_name" property="deptName"/>
	  	<!-- 通过collection进行联合查询 
	  		property：指定要映射的属性的属性名
	  		ofType：指定集合中泛型的类型
	  	-->
	  	<collection property="emps" ofType="com.atguigu.mybatis.entities.Employee">
	  		<!-- 映射主键 -->
	  		<id column="id" property="id"/>
	  		<!-- 映射其他列 -->
	  		<result column="last_name" property="lastName"/>
	  		<result column="email" property="email"/>
	  		<result column="salary" property="salary"/>
	  	</collection>
	  </resultMap>
	  
	  <!-- 通过collection进行分步查询
	  		1.根据部门的id查询出Department对象
	  		2.根据Department的id查询出来部门下所有的员工
	  		3.将员工设置到Department对象中
	   -->
	  <select id="getDepartmentContainsEmpsByStep" resultMap="customMap2">
	  	select id,name deptName from departments where id = #{id}
	  </select>
	  
	  <!-- 自定义结果集 -->
	  <resultMap type="com.atguigu.mybatis.entities.Department" id="customMap2">
	  	<!-- 映射主键 -->
	  	<id column="id" property="id"/>
	  	<!-- 映射其他列 -->
	  	<result column="deptName" property="deptName"/>
	  	<!-- 通过connection进行分步查询 -->
	  	<collection property="emps" select="com.atguigu.mybatis.mapper.EmployeeMapper.getEmployeesByDeptId"
	  	 column="id"></collection>
	  </resultMap>
</mapper>

//联合查询部门和部门下所有员工
		@Test
		public void getDepartmentAndAllEmployeeById() throws IOException {
		//1.创建SQLSessionFactory对象
			SqlSessionFactory sqlSessionFactory = getSessionFactory();
			//回去sqlsession ，相当于jdbc中的connection
			SqlSession session = sqlSessionFactory.openSession();
			try {
				DepartmentMapper mapper = session.getMapper(DepartmentMapper.class);
				 Department departmentAndAllEmployeeById = mapper.getDepartmentAndAllEmployeeById(3);
				System.out.println(departmentAndAllEmployeeById); 	
				session.commit();
			} finally {
				session.close();
			}
		}

 

分步查询

<select id="getEmployeeById" resultType="com.atgui.spring.mybatis.entities.Employee">
      select * from employees where id=#{id}
  </select>

<select id="getDepartmentAndAllEmployeeByStep" resultMap="customMap2">
        select * from departments where id=#{id}
    </select>
    <resultMap type="com.atgui.spring.mybatis.entities.Department"
        id="customMap2">
        <id column="d_id" property="id"></id>
        <result column="d_name" property="name" />
        <collection property="emps"
            select="com.atgui.spring.mybatis.mapper.EmployeeMapper.getEmployeeById"
            column="id">
        </collection>
    </resultMap>