Mybatis3.1-[tp_32-33]-_映射文件_select_resultMap关联查询_association分步查询_延迟加载

笔记要点
出错分析与总结

工程组织

1.定义接口

　　DepartmentMapper

package com.dao;

import com.bean.Department;

public interface DepartmentMapper {
    public Department getDeptById(Integer id);
}

　　EmployeeMapperPlus

package com.dao;
import com.bean.*;
public interface EmployeeMapperPlus {
    public Employee getEmpByIdStep(Integer id);  //分步查询

    public Employee getEmpAndDept(Integer id);//关联查询

    public Employee getEmpAndDept2(Integer id); //关联查询 ,使用association
}

2.定义XML映射文件

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.dao.DepartmentMapper">
    <!--public Department getDeptById(Integer id);-->
    <select id="getDeptById" resultType="com.bean.Department">
        select id,dept_name departmentName from tbl_dept
        where id=#{id}
    </select>

    <!--
    public class Department {
    private Integer id;
    private String departmentName;
    private List<Employee> emps;

    public Department getDeptByIdPlus(Integer id);
    -->
    <select id="getDeptByIdPlus" resultMap="">
         select id,dept_name departmentName from tbl_dept
        where id=#{id}
    </select>

</mapper>

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.dao.EmployeeMapperPlus">
   <!--ResultMap ;自定义结果集映射规则;
        type: 自定义规则的Java类型;id: 唯一的标识,方便引用-->
    <resultMap id="MySimpleEmp" type="com.bean.Employee">
        <!--指定主键列的封装规则,id定义主键,底层会有优化规则;
        column : 指定结果集的具体的那一列; property:指定的JavaBean对应的属性-->
        <id column="id" property="id"/>
        <!--定义普通列的封装规则-->
        <result column="last_name" property="lastName"/>
        <!--,其他不指定的列会自动封装;但是, 我们只要写ResultMap,就把剩下的映射全部都写上-->
        <result column="email" property="email"/>
        <result column="gender" property="gender"/>
    </resultMap>

    <!--public Employee getEmpById(Integer id);   注意进行更改为resultMap-->
    <select id="getEmpById" resultMap="MySimpleEmp">
        select * from tbl_employee where id=#{id}
    </select>

    <!--场景1,方法1:使用级联属性的方式
        查询Employee的同时查询员工对应的部门Employee.dept=Department.id;
        输出员工对应的部门的全部信息:
                 id  last_name  gender   did  dept_name
    -->
    <resultMap id="MyDifEmp" type="com.bean.Employee">
        <!--column : 指定结果集的具体的那一列; property:指定的JavaBean对应的属性-->
        <id column="id" property="id"/>
        <result column="last_name" property="lastName"/>
        <result column="gender" property="gender"/>
        <result column="email" property="email"/>
        <result column="did" property="dept.id"/>
        <result column="dept_name" property="dept.departmentName"/>
    </resultMap>
    <!--public Employee getEmpAndDept2(Integer id); //association 定义封装规则!-->

    <select id="getEmpAndDept" resultMap="MyDifEmp">
          SELECT e.id id,e.last_name last_name,e.`gender` gender,e.`d_id` d_id,
            d.id did,d.`dept_name` dept_name,email
            FROM tbl_employee e,tbl_dept d
            WHERE e.`d_id`=d.`id` AND e.id=#{id};
    </select>

    <!--方法2:-使用association可以指定联合的javaBean的对象
     -->
    <resultMap id="MyDifEmp2" type="com.bean.Employee">
        <!--column : 指定结果集的具体的那一列; property:指定的JavaBean对应的属性-->
        <id column="id" property="id"/>
        <result column="last_name" property="lastName"/>
        <result column="gender" property="gender"/>
        <result column="email" property="email"/>

    <!--使用association可以指定联合的javaBean的对象;(定义单个对象的封装规则!)
        property="dept";指定那个属性是联合的对象;javaType="dept";指定那个属性对象的类型;-->
        <association property="dept" javaType="com.bean.Department">
            <id column="did" property="id"/>
            <result column="dept_name" property="departmentName"/>
        </association>

    </resultMap>
    <!--public Employee getEmpAndDept2(Integer id);   //关联查询-->
    <select id="getEmpAndDept2" resultMap="MyDifEmp2">
          SELECT e.id id,e.last_name last_name,e.`gender` gender,e.`d_id` d_id,
            d.id did,d.`dept_name` dept_name,email
            FROM tbl_employee e,tbl_dept d
            WHERE e.`d_id`=d.`id` AND e.id=#{id};
    </select>

    <!--使用association进行分步查询,
                 1.先按照员工的id查询员工信息;
                 2.根据查询员工信息中的d_id值去部门表查出部门信息;
                 3;,部门设置到员工中
    -->
    <!--id  last_name   gender  email      d_id-->
    <resultMap id="MyEmpByStep" type="com.bean.Employee">
        <id column="id" property="id" />
        <result column="last_name" property="lastName"/>
        <!--association 定义关联对象的封装规则
            select : 表明当前属性是调用select总置顶的方法查出的结果!
          总的流程: 使用select 指定的方法(传入)查出对象,并封装给property
         -->
        <association column="d_id" property="dept"
                     select="com.dao.DepartmentMapper.getDeptById">
        </association>
    </resultMap>

    <!--public Employee getEmpByIdStep(Integer id);-->
    <select id="getEmpByIdStep" resultMap="MyEmpByStep">
        select * from tbl_employee
        where id=#{id}
    </select>

    <!--可以使用延迟加载,(按需加载,或者叫做懒加载)
        Employee==>dept:
        我们可以每次查询Employee对象的时候,都将一起查询出来;
        部门信息在我们需要使用的时候再去查询,分段查询的基础之上加两个配置;
    -->
    <!-- 场景2: 查询部门的时候将部门对应的所有员工的信息也全部查询出来

    -->

</mapper>

3.编写测试关联查询的 代码

public SqlSessionFactory getSqlSessionFactory() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream=Resources.getResourceAsStream(resource);
        return new SqlSessionFactoryBuilder().build(inputStream);
    }
    @Test
    public void test06() throws Exception {

        SqlSession openSession = getSqlSessionFactory().openSession();
        try {
            EmployeeMapperPlus mapper = openSession.getMapper(EmployeeMapperPlus.class);
            System.out.println("---tp_30---多表关联查询,级联属性的封装结果!--------");
            Employee employee = mapper.getEmpAndDept(1);
            System.out.println(employee);
            System.out.println("---tp_31--多表关联查询,使用association进行连接!--------");
            Employee employee2 = mapper.getEmpAndDept2(1);
            System.out.println(employee2);
            openSession.commit();//默认是不自动提交数据的,需要我们自己手动提交
        } finally {
            openSession.close();
        }
    }

测试结果

---tp_30---多表关联查询,级联属性的封装结果!--------
DEBUG 12-01 15:13:43,362 ==>  Preparing: SELECT e.id id,e.last_name last_name,e.`gender` gender,e.`d_id` d_id, d.id did,d.`dept_name` dept_name,email FROM tbl_employee e,tbl_dept d WHERE e.`d_id`=d.`id` AND e.id=?;   (BaseJdbcLogger.java:145) 
DEBUG 12-01 15:13:43,380 ==> Parameters: 1(Integer)  (BaseJdbcLogger.java:145) 
DEBUG 12-01 15:13:43,391 <==      Total: 1  (BaseJdbcLogger.java:145) 
Employee{id=1, lastName='jerry', email='jerry@163.com', gender='1', dept=Department{id=1, departmentName='开发部'}}
---tp_31--多表关联查询,使用association进行连接!--------
DEBUG 12-01 15:13:43,391 ==>  Preparing: SELECT e.id id,e.last_name last_name,e.`gender` gender,e.`d_id` d_id, d.id did,d.`dept_name` dept_name,email FROM tbl_employee e,tbl_dept d WHERE e.`d_id`=d.`id` AND e.id=?;   (BaseJdbcLogger.java:145) 
DEBUG 12-01 15:13:43,392 ==> Parameters: 1(Integer)  (BaseJdbcLogger.java:145) 
DEBUG 12-01 15:13:43,393 <==      Total: 1  (BaseJdbcLogger.java:145) 
Employee{id=1, lastName='jerry', email='jerry@163.com', gender='1', dept=Department{id=1, departmentName='开发部'}}

Process finished with exit code 0

在全局配置文件中 开启延迟加载 (按需加载,或者叫做懒加载)

 <settings>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
        <setting name="jdbcTypeForNull" value="NULL"/>
        <!--显示地指定每个我们需要更改的配置的值,及时他是默认的;防止版本替换带来的问题-->
        <setting name="lazyLoadingEnabled" value="true"/>
        <setting name="aggressiveLazyLoading" value="false"/>

    </settings>

代码1   (仍使用上面的环境配置信息, 此代码不调用到Department 数据库 )

@Test
    public void test07() throws Exception{
        SqlSession openSession = getSqlSessionFactory().openSession();
        try {
            EmployeeMapperPlus mapper = openSession.getMapper(EmployeeMapperPlus.class);
            System.out.println("---tp_32---多表关联查询,使用association进行分布查询-----");
            Employee employee = mapper.getEmpByIdStep(1);
            System.out.println(employee.getEmail());
    //        System.out.println(employee.getDept());

            openSession.commit();//默认是不自动提交数据的,需要我们自己手动提交

        }finally {
            openSession.close();
        }
    }

结果1 (没有进行Dept 上数据库的关联查询)

---tp_32---多表关联查询,使用association进行分布查询-----
DEBUG 12-01 15:18:57,230 ==>  Preparing: select * from tbl_employee where id=?   (BaseJdbcLogger.java:145) 
DEBUG 12-01 15:18:57,249 ==> Parameters: 1(Integer)  (BaseJdbcLogger.java:145) 
DEBUG 12-01 15:18:57,308 <==      Total: 1  (BaseJdbcLogger.java:145) 
jerry@163.com

代码2  (仍使用上面的环境配置信息, 此代码可以调用到Department 数据库 )

  EmployeeMapperPlus mapper = openSession.getMapper(EmployeeMapperPlus.class);
            System.out.println("---tp_32---多表关联查询,使用association进行分布查询-----");
            Employee employee = mapper.getEmpByIdStep(1);
      //      System.out.println(employee.getEmail());
            System.out.println(employee.getDept());

            openSession.commit();//默认是不自动提交数据的,需要我们自己手动提交

结果2

---tp_32---多表关联查询,使用association进行分布查询-----
DEBUG 12-01 15:19:50,044 ==>  Preparing: select * from tbl_employee where id=?   (BaseJdbcLogger.java:145) 
DEBUG 12-01 15:19:50,066 ==> Parameters: 1(Integer)  (BaseJdbcLogger.java:145) 
DEBUG 12-01 15:19:50,129 <==      Total: 1  (BaseJdbcLogger.java:145) 
DEBUG 12-01 15:19:50,129 ==>  Preparing: select id,dept_name departmentName from tbl_dept where id=?   (BaseJdbcLogger.java:145) 
DEBUG 12-01 15:19:50,130 ==> Parameters: 1(Integer)  (BaseJdbcLogger.java:145) 
DEBUG 12-01 15:19:50,131 <==      Total: 1  (BaseJdbcLogger.java:145) 
Department{id=1, departmentName='开发部'}