MyBatis(4),关联映射

MyBatis(4),关联映射

关联关系

一对一：例如一个人对应一张身份证
一对多：例如一个班级有多个学生
多对多：例如老师和学生的关系，一个老师对应多个学生,一个学生对应多个老师

一对一

准备实体类

使用的是EMP表和DEPT表
Emp.java

package com.yangqihang.bean;

import java.util.Date;

public class Emp{
    private Integer empno;
    private String ename;
    private String job;
    private Integer mgr;
    private Date hiredate;
    private Double sal;
    private Double comm;
    private Dept dept;
    //省略Getter和Setter
    //省略toString

Dept.java

package com.yangqihang.bean;

public class Dept {
    private Integer deptno;
    private String dname;
    private String loc;
    //省略Getter和Setter
    //省略toString

嵌套结果方式

需要SQL语句进行关联查询
EmpDao.java

package com.yangqihang.dao;

import com.yangqihang.bean.Emp;

public interface EmpDao {
    Emp selectByEmpno(Integer empno);
}

EmpDao.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.yangqihang.dao.EmpDao">
    <resultMap id="EmpDeptResultMap" type="com.yangqihang.bean.Emp">
        <id property="empno" column="empno"></id>
        <result property="ename" column="ename"></result>
        <result property="job" column="job"></result>
        <result property="mgr" column="mgr"></result>
        <result property="hiredate" column="hiredate"></result>
        <result property="sal" column="sal"></result>
        <result property="comm" column="comm"></result>
        <association property="dept" javaType="com.yangqihang.bean.Dept">
            <id property="deptno" column="deptno"></id>
            <id property="dname" column="dname"></id>
            <id property="loc" column="loc"></id>
        </association>
    </resultMap>

    <select id="selectByEmpno" resultMap="EmpDeptResultMap">
        select e.*,d.dname,d.loc from emp e left join dept d on e.deptno = d.deptno where e.empno = #{empno}
    </select>
</mapper>

嵌套查询方式

EmpDao.java同上
DeptDao.java

package com.yangqihang.dao;

import com.yangqihang.bean.Dept;

public interface DeptDao {

    Dept selectByDeptno(Integer deptno);
}

EmpDao.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.yangqihang.dao.EmpDao">
    <resultMap id="EmpDeptResultMap" type="com.yangqihang.bean.Emp">
        <id property="empno" column="empno"></id>
        <result property="ename" column="ename"></result>
        <result property="job" column="job"></result>
        <result property="mgr" column="mgr"></result>
        <result property="hiredate" column="hiredate"></result>
        <result property="sal" column="sal"></result>
        <result property="comm" column="comm"></result>
        <association property="dept" column="deptno" select="com.yangqihang.dao.DeptDao.selectByDeptno"></association>
    </resultMap>

    <select id="selectByEmpno" resultMap="EmpDeptResultMap">
        select e.*,d.dname,d.loc from emp e left join dept d on e.deptno = d.deptno where e.empno = #{empno}
    </select>
</mapper>

association标签中的column属性表示指定传入select方法的值是哪个列下的数据

DeptDao.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.yangqihang.dao.DeptDao">
    <select id="selectByDeptno" resultType="com.yangqihang.bean.Dept">
        select * from dept where deptno = #{deptno}
    </select>
</mapper>

一对多

准备实体类

Emp.java

package com.yangqihang.bean;

import java.util.Date;

public class Emp{
    private Integer empno;
    private String ename;
    private String job;
    private Integer mgr;
    private Date hiredate;
    private Double sal;
    private Double comm;
    private Integer deptno;
    //省略Getter和Setter
    //省略toString

Dept.java

package com.yangqihang.bean;

import java.util.List;

public class Dept {
    private Integer deptno;
    private String dname;
    private String loc;
    private List<Emp> empList;
    //省略Getter和Setter
    //省略toString

嵌套结果方式

DeptDao.java

package com.yangqihang.dao;

import com.yangqihang.bean.Dept;

public interface DeptDao {
    Dept selectByDeptno(Integer deptno);
}

DeptDao.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.yangqihang.dao.DeptDao">
    <resultMap id="DeptEmpResultMap" type="com.yangqihang.bean.Dept">
        <id property="deptno" column="deptno"></id>
        <result property="dname" column="dname"></result>
        <result property="loc" column="loc"></result>
        <collection property="empList" ofType="com.yangqihang.bean.Emp">
            <id property="empno" column="empno"></id>
            <result property="ename" column="ename"></result>
            <result property="job" column="job"></result>
            <result property="mgr" column="mgr"></result>
            <result property="hiredate" column="hiredate"></result>
            <result property="sal" column="sal"></result>
            <result property="comm" column="comm"></result>
            <result property="deptno" column="deptno"></result>
        </collection>
    </resultMap>

    <select id="selectByDeptno" resultMap="DeptEmpResultMap">
        select e.*,d.dname,d.loc from dept d left join emp e on d.deptno = e.deptno where d.deptno =#{deptno}
    </select>
</mapper>

嵌套查询方式

DeptDao.java同上
EmpDao.java

package com.yangqihang.dao;

import com.yangqihang.bean.Emp;

import java.util.List;

public interface EmpDao {
    List<Emp> selectByDeptno(Integer deptno);
}

DeptDao.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.yangqihang.dao.DeptDao">
    <resultMap id="DeptEmpResultMap" type="com.yangqihang.bean.Dept">
        <id property="deptno" column="deptno"></id>
        <result property="dname" column="dname"></result>
        <result property="loc" column="loc"></result>
        <collection property="empList" column="deptno" select="com.yangqihang.dao.EmpDao.selectByDeptno"></collection>
    </resultMap>

    <select id="selectByDeptno" resultMap="DeptEmpResultMap">
        select * from dept where deptno = #{deptno}
    </select>
</mapper>

EmpDao.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.yangqihang.dao.EmpDao">
    <select id="selectByDeptno" resultType="com.yangqihang.bean.Emp">
        select * from emp where deptno = #{deptno}
    </select>
</mapper>

多对多

多对多与一对多实现类似，不过会增加中间表

延迟查询

当我们在进行表关联的时候，有可能在查询结果的时候不需要关联对象的属性值，那么此时可以通过延迟加载来实现功能,例如有时候只需要查询Dept表的dname属性,而不需要再查询emp表中的数据,可以使用延迟查询

//只需要输出dname
String dname = dao.selectByDeptno(10).getDname();
System.out.println(dname);

单独设置
可以在关联查询中设置fetchType属性为lazy

<collection property="empList" column="deptno" select="com.yangqihang.dao.EmpDao.selectByDeptno" fetchType="lazy"></collection>

或者在全局配置中开启延时加载

<settings>
    <setting name="lazyLoadingEnabled" value="true"/>
</settings>

当全局设置开启的时候,但是希望一个sql查询不适用延时策略可以设置fetchType属性为eager