MyBatis 框架ResultMap一对多查询

2021-10-28 0 By admin

MyBatis 框架中使用 ResultMap 包装SQL语言的结果集,ResultMap 可以帮助我们处理多表查询中一对一查询和一对多查询。

一、环境提前搭建

1.1、创建实体类

// 部门实体类

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Dept {
    private Integer id;
    private String name;
    private List<Employee> employees;
}

// 员工实体类

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Employee {
    private Integer id;
    private String name;
}

二、联合查询

2.1、创建 Mapper 接口

// 部门 Mapper 接口

public interface DeptMapper {
    Dept get(int id);
}

// 员工 Mapper 接口

public interface EmployeeMapper {
    
}

2.2、Mapper.xml 映射文件

// 部门映射文件 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="org.hong.mapper.DeptMapper">
  <resultMap id="deptBase" type="org.hong.pojo.Dept">
    <id column="id" property="id"></id>
    <result column="name" property="name"></result>
  </resultMap>

  <resultMap id="dept2" type="org.hong.pojo.Dept" extends="deptBase">
    <!-- 方式一 -->
    <collection property="employees" ofType="org.hong.pojo.Employee">
      <id column="eid" property="id"></id>
      <result column="ename" property="name"></result>
    </collection>
  </resultMap>

  <resultMap id="dept3" type="org.hong.pojo.Dept" extends="deptBase">
    <!-- 方式二 -->
    <collection property="employees"
      ofType="org.hong.pojo.Employee"
      resultMap="org.hong.mapper.EmployeeMapper.employeeBase">
    </collection>
  </resultMap>

  <select id="get" resultMap="dept2">
    select d.*, e.id eid, e.name ename from dept d inner join employee e on d.id = e.did where d.id = #{id}
  </select>
</mapper>

// 员工映射文件 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="org.hong.mapper.EmployeeMapper">
  <resultMap id="employeeBase" type="org.hong.pojo.Employee">
    <id column="eid" property="id"></id>
    <id column="ename" property="name"></id>
  </resultMap>
</mapper>

三、嵌套查询

3.1、创建 Mapper 接口

// DeptMapper.java

public interface DeptMapper {
    Dept get(int id);
}

// EmployeeMapper.java

public interface EmployeeMapper {
    List<Employee> getByDid(int did);
}

3.2、创建 Mapper 映射文件

// 部门 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="org.hong.mapper.DeptMapper">
  <resultMap id="deptBase" type="org.hong.pojo.Dept">
    <id column="id" property="id"></id>
    <result column="name" property="name"></result>
  </resultMap>

  <resultMap id="dept1" type="org.hong.pojo.Dept" extends="deptBase">
    <!--
      collection: 定义关联集合类型的属性的封装规则
      ofType: 指定集合里面元素的类型
      select: 表明当前属性是调用select指定的方法查出的结果
      column: 指定将那一列的值传给select
     -->
    <collection property="employees"
      column="id"
      select="org.hong.mapper.EmployeeMapper.getByDid"></collection>
  </resultMap>
  <select id="get" resultMap="dept1">
    select * from dept where id = #{id}
  </select>
</mapper>

// 员工 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="org.hong.mapper.EmployeeMapper">
  <select id="getByDid" resultType="org.hong.pojo.Employee">
    select * from employee where did = #{did}
  </select>
</mapper>