6-Mybatis的一对多关联查询及延迟加载

这篇文章主要内容：

1. 一对多关联查询
2. 一对多关联查询的延迟加载
   因为与5-Mybatis的一对以关联查询及延迟加载有重复，所以部分内容没有黏贴

1. 准备数据库

2. 数据库配置文件

3. POJO

Emp.java

 package com.qwy.bean;

import java.util.Date;

/**
 * @author qwy
 * @create 2021-04-08 21:18
 **/
public class Emp {
    private Integer empno;
    private String ename;
    private String job;
    private Integer mgr;
    private Date hiredate;
    private float sal;
    private float comm;
    private Integer deptno;

    public Emp() {
    }

    public Emp(Integer empno, String ename, String job, Integer mgr, Date hiredate, float sal, float comm, Integer deptno) {
        this.empno = empno;
        this.ename = ename;
        this.job = job;
        this.mgr = mgr;
        this.hiredate = hiredate;
        this.sal = sal;
        this.comm = comm;
        this.deptno = deptno;
    }


    public Integer getEmpno() {
        return empno;
    }

    public void setEmpno(Integer empno) {
        this.empno = empno;
    }

    public String getEname() {
        return ename;
    }

    public void setEname(String ename) {
        this.ename = ename;
    }

    public String getJob() {
        return job;
    }

    public void setJob(String job) {
        this.job = job;
    }

    public Integer getMgr() {
        return mgr;
    }

    public void setMgr(Integer mgr) {
        this.mgr = mgr;
    }

    public Date getHiredate() {
        return hiredate;
    }

    public void setHiredate(Date hiredate) {
        this.hiredate = hiredate;
    }

    public float getSal() {
        return sal;
    }

    public void setSal(float sal) {
        this.sal = sal;
    }

    public float getComm() {
        return comm;
    }

    public void setComm(float comm) {
        this.comm = comm;
    }

    public Integer getDeptno() {
        return deptno;
    }

    public void setDeptno(Integer deptno) {
        this.deptno = deptno;
    }

    @Override
    public String toString() {
        return "Emp{" +
                "empno=" + empno +
                ", ename='" + ename + '\'' +
                ", job='" + job + '\'' +
                ", mgr=" + mgr +
                ", hiredate=" + hiredate +
                ", sal=" + sal +
                ", comm=" + comm +
                ", deptno=" + deptno +
                '}';
    }
}

Dept.java

 package com.qwy.bean;

import java.util.List;

/**
 * @author qwy
 * @create 2021-04-08 21:25
 **/
public class Dept {
    private Integer deptno;
    private String dname;
    private String loc;
    /*一个不对应多个员工*/
    private List<Emp> emps;
    public Dept() {
    }

    public Dept(Integer deptno, String dname, String loc) {
        this.deptno = deptno;
        this.dname = dname;
        this.loc = loc;
    }

    public List<Emp> getEmps() {
        return emps;
    }

    public void setEmps(List<Emp> emps) {
        this.emps = emps;
    }

    public Integer getDeptno() {
        return deptno;
    }

    public void setDeptno(Integer deptno) {
        this.deptno = deptno;
    }

    public String getDname() {
        return dname;
    }

    public void setDname(String dname) {
        this.dname = dname;
    }

    public String getLoc() {
        return loc;
    }

    public void setLoc(String loc) {
        this.loc = loc;
    }

    @Override
    public String toString() {
        return "Dept{" +
                "deptno=" + deptno +
                ", dname='" + dname + '\'' +
                ", loc='" + loc + '\'' +
                '}';
    }
}

4. 接口（DeptMapper.java和EmpMapper.java）

DeptMapper.java

package com.qwy.mapper;

import com.qwy.bean.Dept;

/**
 * @author qwy
 * @create 2021-04-08 23:38
 **/
public interface DeptMapper {
    /*根据部门编号获取部门信息以及部门下所有员工信息*/
    public Dept getDeptAndEmpsByDeptno(Integer deptno);
    /*根据部门编号获取部门信息*/
    public Dept getDeptByDeptno(Integer deptno);
}

Empmapper.java

 package com.qwy.mapper;

import com.qwy.bean.Emp;

import java.util.List;

/**
 * @author qwy
 * @create 2021-04-08 23:38
 **/
public interface EmpMapper {
    /*根据部门编号获取员工信息*/
    public List<Emp> getEmpsByDeptno(Integer deptno);
}

5. 全局配置文件

6. 日志文件

7. 映射文件

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="com.qwy.mapper.DeptMapper">
    <!--根据部门编号获取部门信息以及部门下所有员工信息*/
    public Dept getDeptAndEmpsByDeptno(Integer deptno);-->
    <resultMap id="getDeptAndEmpsByDeptnoMap" type="com.qwy.bean.Dept">
        <!--配置部门的映射关系-->
        <id column="dno" property="deptno"></id>
        <result column="dname" property="dname"></result>
        <result column="loc" property="loc"></result>
        <!--配置一对多的关系：
        property：部门类中属性名
        column：属性名关联的字段
        ofType： property属性的类型（集合中元素的类型）
         -->
        <collection property="emps" column="deptno" ofType="com.qwy.bean.Emp">
            <id column="empno" property="empno"></id>
            <result column="ename" property="ename"></result>
            <result column="job" property="job"></result>
            <result column="mgr" property="mgr"></result>
            <result column="hiredate" property="hiredate"></result>
            <result column="sal" property="sal"></result>
            <result column="comm" property="comm"></result>
            <result column="deptno" property="deptno"></result>
        </collection>
    </resultMap>
    <select id="getDeptAndEmpsByDeptno" resultMap="getDeptAndEmpsByDeptnoMap" parameterType="int">
        select e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,e.deptno,d.deptno dno,d.dname,d.loc
        from emp e ,dept d
        where e.deptno= d.deptno and d.deptno=#{deptno}
    </select>
    <!--public Dept getDeptByDeptno(Integer deptno);-->
    <resultMap id="getDeptByDeptnoMap" type="com.qwy.bean.Dept">
        <id column="dno" property="deptno"></id>
        <result column="dname" property="dname"></result>
        <result column="loc" property="loc"></result>
        <!--配置一对多关联：
        property：部门类中属性名
        column：属性名关联的字段
        ofType： property属性的类型（集合中元素的类型）
        select:引用其他查询，如果是其他命名空间下的需要指定命名空间
        fetchType：使用懒加载
        -->
        <collection  property="emps" ofType="com.qwy.bean.Emp" column="deptno"
                    select="com.qwy.mapper.EmpMapper.getEmpsByDeptno"
                    fetchType="lazy"></collection>
    </resultMap>
    <select id="getDeptByDeptno" resultMap="getDeptByDeptnoMap" parameterType="int">
        select d.deptno ,d.dname,d.loc from dept d where d.deptno=#{deptno}
    </select>
</mapper>

EmpMapper.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.qwy.mapper.EmpMapper">
    <!--/*根据部门编号获取员工信息*/
    public List<Emp> getEmpsByDeptno(Integer deptno);-->
    <select id="getEmpsByDeptno" resultType="com.qwy.bean.Emp" parameterType="int">
        select e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,e.deptno from emp e where e.deptno=#{deptno}
    </select>
</mapper>

8. 测试类

package com.qwy.test;

import com.qwy.bean.Dept;
import com.qwy.bean.Emp;
import com.qwy.mapper.DeptMapper;
import com.qwy.mapper.EmpMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Before;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

/**
 * @author qwy
 * @create 2021-04-08 23:50
 **/
public class TestMybatis {
    private SqlSessionFactory sqlSessionFactory;
    @Before
    public void getSqlSessionFactory() throws IOException {
        String resource = "mybatis-config.xml";
        //加载核心配置文件
        InputStream inputStream = Resources.getResourceAsStream(resource);
        //创建SqlSessionFactory工厂
        sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    }
    @Test
    public void testGetEmpAndDeptByEmpno(){
        SqlSession sqlSession = sqlSessionFactory.openSession();
        try{
            DeptMapper deptMapper = sqlSession.getMapper(DeptMapper.class);
            /**
             * 虽然我们只需部门信息，但是SQL还是：
             * select e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,e.deptno,d.deptno dno,d.dname,d.loc from emp e ,dept d where e.deptno= d.deptno and d.deptno=?
             */
            Dept dept = deptMapper.getDeptAndEmpsByDeptno(10);
            System.out.println("dept = " + dept);
           /* List<Emp> emps = dept.getEmps();
            for (int i = 0; i < emps.size(); i++) {
                System.out.println("emps = " + emps.get(i));
            }*/
        }finally {
            sqlSession.close();
        }
    }
    @Test
    public void testGetEmpAndDeptByEmpno2(){
        SqlSession sqlSession = sqlSessionFactory.openSession();
        try{
            DeptMapper deptMapper = sqlSession.getMapper(DeptMapper.class);
            //执行SQL：select d.deptno ,d.dname,d.loc from dept d where d.deptno=?
            Dept dept = deptMapper.getDeptByDeptno(10);
            System.out.println("dept = " + dept);
            //执行SQL：select e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,e.deptno from emp e where e.deptno=?
           /* List<Emp> emps = dept.getEmps();
            for (int i = 0; i <emps.size() ; i++) {
                System.out.println("emps = " + emps.get(i));

            }*/
        }finally {
            sqlSession.close();
        }
    }
}

源码下载