mybatis多对一自定义映射

mybatis多对一自定义映射

在mybatis中，通过配置文件设置自定义映射有一个标签

多对一的配置有3种方式，

什么叫多对一,举个栗子：

部门、员工的关系->>>

*员工 -> 部门 ===== 多对一*，一个员工对应一个部门，多个员工可以对应相同的部门

部门 -> 员工 ===== 一对多 一个部门包含多个员工

1.实体类

public class Employee {

    private Integer empId;
    private String name;
    private Integer gender;
    private Integer age;
    private Department dept;
  getter&setter .......
}

public class Department {

    private Integer dId;
    private String dName;
....
}

2.数据库中的表

CREATE TABLE `hr_emp` (
  `empid` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `gender` int(12) DEFAULT NULL,
  `age` int(12) DEFAULT NULL,
  `deptid` int(12) DEFAULT NULL,  --用来关联的字段
  PRIMARY KEY (`empid`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1011 DEFAULT CHARSET=utf8

---------department----------

CREATE TABLE `department` (
  `did` int(12) NOT NULL AUTO_INCREMENT, --用来关联的字段
  `dname` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`did`)
) ENGINE=InnoDB AUTO_INCREMENT=10087 DEFAULT CHARSET=utf8mb4

3.映射文件

方式一：

<resultMap id="empMap" type="Employee">
  <!--通过id来指定主键的映射，column代表字段，property代表实例类中的属性，区分大小写-->
  <id column="empid" property="empId"></id>
  <!--通过result来指定非主键字段的映射-->
  <result column="name" property="name"></result>
  <result column="gender" property="gender"></result>
  <result column="age" property="age"></result>
  <result column="did" property="dept.dId"></result>
  <result column="dname" property="dept.dName"></result>
</resultMap>

 <select id="getAllEmp" resultMap="empMap">
    SELECT
    e.empid,
    e.name,
    e.gender,
    e.age,
    e.deptid, --该字段必须与resultMap中的字段要对应上
    d.dname
    FROM hr_emp e
    LEFT JOIN department d
    ON e.deptid = d.did
  </select>

方式二：

<resultMap id="empMap" type="Employee">
  <id column="empid" property="empId"/>
  <result column="name" property="name"/>
  <result column="gender" property="gender"/>
  <result column="age" property="age"/>
  <association property="dept" javaType="Department">
    <id column="did" property="dId"/>
    <result column="dname" property="dName"/>
  </association>
</resultMap>

 <select id="getAllEmp" resultMap="empMap">
    SELECT
    e.empid,
    e.name,
    e.gender,
    e.age,
    d.did,
    d.dname
    FROM hr_emp e
    LEFT JOIN department d
    ON e.deptid = d.did
 </select>

方式三：

分布查询多对一

<mapper namespace="com.shufang.mapper.DepartmentMapper">
    <!-- Department getDeptById(String id); -->
    <select id="getDeptById" resultType="Department">
        select did ,dname from department where did = #{id}
    </select>
</mapper>

-------------------------------------------------------------
<resultMap id="empStepMap" type="Employee">
  <id column="empid" property="empId"/>
  <result column="name" property="name"/>
  <result column="gender" property="gender"/>
  <result column="age" property="age"/>
  <association property="dept" 
               select="com.shufang.mapper.DepartmentMapper.getDeptById"column="did">
    <id column="did" property="dId"/>
    <result column="dname" property="dName"/>
  </association>
</resultMap>

4.完整的Mapper文档

<?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.shufang.mapper.EmpDeptMapper">

    <!--自定义结果映射，多对一 方式一： -->
    <!--        <resultMap id="empMap" type="Employee">-->
    <!--            &lt;!&ndash;通过id来指定主键的映射&ndash;&gt;-->
    <!--            <id column="empid" property="empId"/>-->
    <!--            <result column="name" property="name"/>-->
    <!--            <result column="gender" property="gender"/>-->
    <!--            <result column="age" property="age"/>-->
    <!--            <result column="deptid" property="dept.dId"/>-->
    <!--            <result column="dname" property="dept.dName"/>-->
    <!--        </resultMap>-->

    <resultMap id="empMap" type="Employee">
        <id column="empid" property="empId"/>
        <result column="name" property="name"/>
        <result column="gender" property="gender"/>
        <result column="age" property="age"/>
        <association property="dept" javaType="Department">
            <id column="did" property="dId"/>
            <result column="dname" property="dName"/>
        </association>
    </resultMap>


    <resultMap id="empStepMap" type="Employee">
        <id column="empid" property="empId"/>
        <result column="name" property="name"/>
        <result column="gender" property="gender"/>
        <result column="age" property="age"/>
        <association property="dept" select="com.shufang.mapper.DepartmentMapper.getDeptById" column="did">
            <id column="did" property="dId"/>
            <result column="dname" property="dName"/>
        </association>
    </resultMap>

    <select id="getAllEmp" resultMap="empStepMap">
    SELECT
    e.empid,
    e.name,
    e.gender,
    e.age,
    d.did, --字段需要传给下一段SQL
    d.dname
    FROM hr_emp e
    LEFT JOIN department d
    ON e.deptid = d.did
    </select>
</mapper>

5.多对一映射的懒加载(lazy load)

在全局配置文件中配置

lazyLoadingEnabled	延迟加载的全局开关。当开启时，所有关联对象都会延迟加载。 特定关联关系中可通过设置 fetchType 属性来覆盖该项的开关状态。	true | false	false
aggressiveLazyLoading	开启时，任一方法的调用都会加载该对象的所有延迟加载属性。 否则，每个延迟加载属性会按需加载（参考 lazyLoadTriggerMethods)。	true | false	false

<!--进行一些mybatis的设置，可以在官网找到相应的配置，比如说将下划线转化成驼峰命名-->
<settings>
  <setting name="mapUnderscoreToCamelCase" value="true"/>
  
  <!--下面2个设置们可以保证在多对一的映射中，延迟加载未用到的SQL-->
  <setting name="lazyLoadingEnabled" value="true"/>
  <setting name="aggressiveLazyLoading" value="false"/>
</settings>