15、mybatis一对多关联查询 collection定义关联集合封装规则及懒加载

1、collection定义关联集合封装规则单步查询

1）、Dept增加集合属性

package com.mi.pojo;

import java.util.List;

public class Dept {
    private int id;
    private String deptName;

    private List<Employee> emps;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getDeptName() {
        return deptName;
    }

    public void setDeptName(String deptName) {
        this.deptName = deptName;
    }

    @Override
    public String toString() {
        return "Dept{" +
                "id=" + id +
                ", deptName='" + deptName + '\'' +
                ", emps=" + emps +
                '}';
    }
}

2）、DeptMapper增加查询接口

package com.mi.dao;

import com.mi.pojo.Dept;

public interface DeptMapper {

    public Dept getDeptById(Integer id);

    public Dept getDeptByIdPlus(Integer id);
}

3）、DeptMapper.xml增加collection配置

<?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.mi.dao.DeptMapper">
    <select id="getDeptById" resultType="com.mi.pojo.Dept">
        select id,dept_name deptName from dept where id = #{id}
    </select>

    <!--
        collection嵌套结果集的方式，定义关联的集合类型元素的封装规则
    -->
    <resultMap id="myDept" type="com.mi.pojo.Dept">
        <id column="id" property="id"/>
        <result column="dept_name" property="deptName"/>
        <!--collection定义关联集合类型的属性的封装规则
            ofType：指定集合里面元素的类型
        -->
        <collection property="emps" ofType="com.mi.pojo.Employee">
            <id column="e_id" property="id"/>
            <result column="last_name" property="lastName"/>
            <result column="gender" property="gender"/>
        </collection>
    </resultMap>

    <!--查询部门的时候将部门对应的所有员工信息也查询出来-->
    <select id="getDeptByIdPlus" resultMap="myDept">
        select d.id,d.dept_name,e.id e_id,e.last_name,e.gender
        from dept d left join employee e
        on d.id = e.d_id
        where d.id = #{id}
    </select>
</mapper>

4）、Test

    @Test
    public void getDeptByIdPlus() throws IOException {
        //1、获取SqlSessionFactory对象
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
        //2、获取Sqlsesion对象
        SqlSession sqlSession = sqlSessionFactory.openSession();

        try {
            //3、获取接口的实现类对象
            //会为接口自动创建一个代理对象，代理对象去执行增删改查方法
            DeptMapper mapper = sqlSession.getMapper(DeptMapper.class);

            Dept dept = mapper.getDeptByIdPlus(1);
            System.out.println(dept);
//            System.out.println(employee.getDept());
        }finally {
            sqlSession.close();
        }
    }

5）、Result

DEBUG 09-13 18:16:31,851 ==>  Preparing: select d.id,d.dept_name,e.id e_id,e.last_name,e.gender from dept d left join employee e on d.id = e.d_id where d.id = ?   (BaseJdbcLogger.java:143) 
DEBUG 09-13 18:16:31,931 ==> Parameters: 1(Integer)  (BaseJdbcLogger.java:143) 
DEBUG 09-13 18:16:31,964 <==      Total: 2  (BaseJdbcLogger.java:143) 
Dept{id=1, deptName='开发部', emps=[Employee{id=3, lastName='jerry', gender='0'}, Employee{id=5, lastName='kite', gender='1'}]}

Process finished with exit code 0

2、collection定义关联集合封装规则分步查询

6）、EmployeeMapper

package com.mi.dao;

import com.mi.pojo.Employee;
import org.apache.ibatis.annotations.MapKey;
import org.apache.ibatis.annotations.Param;

import java.util.List;
import java.util.Map;

public interface EmployeeMapper {

    public Employee getEmpByIdStep(Integer id);

    public List<Employee> getEmpsByDeptId(Integer deptId);
}

7）、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.mi.dao.EmployeeMapper">
    <select id="getEmpsByDeptId" resultType="com.mi.pojo.Employee">
        select * from employee where d_id = #{deptId}
    </select>
</mapper>

8）、DeptMapper.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.mi.dao.DeptMapper">
    <resultMap id="myDeptStep" type="com.mi.pojo.Dept">
        <id column="id" property="id"/>
        <result column="dept_name" property="deptName"/>
        <collection property="emps" select="com.mi.dao.EmployeeMapper.getEmpsByDeptId" 
        column="id" fetchType="lazy">
        <!--lazy 表示延迟加载--><!--如果是多个参数可以写为column={key1=column1,key2=column2}例如column = {deptId=id}-->
        </collection>
    </resultMap>
    <select id="getDeptByIdStep" resultMap="myDeptStep">
        select id,dept_name from dept where id = #{id}
    </select>
</mapper>

9）、Test

@Test
    public void getDeptByIdStep() throws IOException {
        //1、获取SqlSessionFactory对象
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
        //2、获取Sqlsesion对象
        SqlSession sqlSession = sqlSessionFactory.openSession();

        try {
            //3、获取接口的实现类对象
            //会为接口自动创建一个代理对象，代理对象去执行增删改查方法
            DeptMapper mapper = sqlSession.getMapper(DeptMapper.class);

            Dept dept = mapper.getDeptByIdStep(1);
            System.out.println(dept);
//            System.out.println(employee.getDept());
        }finally {
            sqlSession.close();
        }
    }