[Java知识库]mybatis一对多，一对一使用场景

前提：建表语句

CREATE TABLE `department` (
  `id` varchar(16) NOT NULL,
  `department_name` varchar(16) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

CREATE TABLE `employee` (
  `id` varchar(16) COLLATE utf8_unicode_520_ci NOT NULL,
  `name` varchar(16) COLLATE utf8_unicode_520_ci DEFAULT NULL,
  `department_id` varchar(16) COLLATE utf8_unicode_520_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_520_ci

一对一场景使用association标签

场景：一个员工对应一个部门的关系映射
实体类：

public class Employee {
    private String id;
    private String name;
    private Department department;

public class Department {
    private String id;
    private String departName;

xml标签映射使用

<resultMap id="employeeResultMap" type="com.guochu.system.employee.pojo.Employee">
        <id column="id" property="id"></id>
        <result column="name" property="name"></result>
        <association property="department" javaType="com.guochu.system.employee.pojo.Department">
            <id column="departId" property="id"></id>
            <result column="department_name" property="departName"></result>
        </association>
</resultMap>

sql语句：

<select id="selectEmployeeById" resultMap="employeeResultMap">
        select e.id ,e.name ,d.id as departId, d.department_name
        from employee e left join department d on e.department_id = d.id
        where e.id = #{id}
 </select>

使用association进行分步查询，延迟加载

概念：比如查询员工信息和对应的部门信息，以前使用的是连表查询，这样查询的缺点是连表多的时候产生笛卡尔积会加重mysql的负担。分步 查询的意思为先查询员工，拿到查询到的员工信息后再去查询部门信息。在分步查询中可以配置延迟加载，即在使用的时候才会查询。
实体类：

public class Employee {
    private String id;
    private String name;
    private Department department;

public class Department {
    private String id;
    private String departName;

xml标签映射使用

<resultMap id="employeeResultMap" type="com.guochu.system.employee.pojo.Employee">
        <id column="id" property="id"></id>
        <result column="name" property="name"></result>
        <association property="department" select="com.guochu.system.employee.mapper.DepartmentMapper.selectDepartmentById"
        column="id = department_id">  -- 如果是多条件加，隔开就行
        </association>
    </resultMap>

    <select id="selectEmployeeById" resultMap="employeeResultMap">
        select id ,name, department_id
        from employee
        where id = #{id}
    </select>

<select id="selectDepartmentById" resultType="com.guochu.system.employee.pojo.Department">
        select id, department_name as departName
        from department
        where id = #{id}
    </select>

开启懒加载需要在配置文件中添加如下：

<setting name="lazyLoadingEnabled" value="true"/>
<setting name="aggressiveLazyLoading" value="false"/>
<setting name="lazyLoadTriggerMethods" value=""/>

一对多场景使用collection标签

场景：一个部门有多个员工
实体类：

public class Department {
    private String id;
    private String departName;
    private List<Employee> employees;

public class Employee {
    private String id;
    private String name;

xml标签映射使用

<resultMap id="departResultMap" type="com.guochu.system.employee.pojo.Department">
        <id column="id" property="id"/>
        <result column="departName" property="departName"/>
        <collection property="employees" ofType="com.guochu.system.employee.pojo.Employee">
            <id column="eid" property="id"></id>
            <result column="name" property="name"/>
        </collection>
    </resultMap>

<select id="selectDepartmentById" resultMap="departResultMap">
        select d.id, d.department_name as departName,e.id as eid,e.name
        from department d left join employee e on e.department_id = d.id
        where d.id = #{id}
    </select>