Mybatis多表联接查询（一对多分级查询）

Mybatis多表联接查询（一对多分级查询）

• 将emp表和dept表通过dept_id联接查询

1.对dept表进行修改

• 因为在以dept对emp表进行一对多查询时查询出的emp表数据为一个集合数组，因此在dept表内添加属性为emp的集合List

dept.java

package com.mybatis.pojo;

import java.util.List;

public class Dept
{
   private Integer deptId;
   private String deptName;
   private List<Emp> emps;
   public Dept() {
   }

   public Dept(Integer deptId, String deptName) {
       this.deptId = deptId;
       this.deptName = deptName;
   }

   public Integer getDeptId() {
       return deptId;
   }

   public void setDeptId(Integer deptId) {
       this.deptId = deptId;
   }

   public String getDeptName() {
       return deptName;
   }

   public void setDeptName(String deptName) {
       this.deptName = deptName;
   }

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

   @Override
   public String toString() {
       return "Dept{" +
               "deptId=" + deptId +
               ", deptName='" + deptName + '\'' +
               ", emps=" + emps +
               '}';
   }

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

}

emp .java

package com.mybatis.pojo;

public class Emp
{
    private Integer empId;
    private  String empName;
    private  Integer age;
    private  String gender;
    private  Dept dept;

    public Dept getDept() {
        return dept;
    }

    @Override
    public String toString() {
        return "Emp{" +
                "empId=" + empId +
                ", empName='" + empName + '\'' +
                ", age=" + age +
                ", gender='" + gender + '\'' +
                ", dept=" + dept +
                '}';
    }

    public void setDept(Dept dept) {
        this.dept = dept;
    }

    public Integer getEmpId() {
        return empId;
    }

    public void setEmpId(Integer empId) {
        this.empId = empId;
    }

    public String getEmpName() {
        return empName;
    }

    public void setEmpName(String empName) {
        this.empName = empName;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }

    public Emp(Integer empId, String empName, Integer age, String gender) {
        this.empId = empId;
        this.empName = empName;
        this.age = age;
        this.gender = gender;
    }

    public Emp() {
    }

}

2.对dept的mapper接口和mapper.xml文件进行修改

DeptMapper接口(因为是分级查询所以先对dept表进行查询)

package com.mybatis.mapper;

import com.mybatis.pojo.Dept;
import org.apache.ibatis.annotations.Param;

public interface DeptMapper {
    Dept getDeptAndEmpBystep(@Param("DeptId")Integer deptId);
}

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.mybatis.mapper.DeptMapper">
<!--对resultMap进行配置用collection对集合进行映射
		property为集合名
		select为查询emp表其接口所用到的类
		column为下面select语句中结果值中的数据
		-->
    <resultMap id="getDeptAndEmpResultMapStep" type="Dept">
        <id column="dept_id" property="deptId"></id>
        <result column="dept_name" property="deptName"></result>
        <collection property="emps"
                    select="com.mybatis.mapper.EmpMapper.getDeptAndEmpByStep"
                    column="dept_id"></collection>
    </resultMap>
    <select id="getDeptAndEmpBystep" resultMap="getDeptAndEmpResultMapStep">
        select * from dept where dept_id=#{DeptId}
    </select>
</mapper>

3.因为是分级查询在查询完dept表后对emp表进行查询，对其mapper接口和xml文件进行编写

EmpMapper接口

package com.mybatis.mapper;

import com.mybatis.pojo.Emp;
import org.apache.ibatis.annotations.Param;

public interface EmpMapper {
   Emp getDeptAndEmpByStep(@Param("deptId")Integer id);
}

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.mybatis.mapper.EmpMapper">
 
    <select id="getDeptAndEmpByStep" resultType="Emp">
        select * from emp where dept_id=#{deptId}
    </select>
</mapper>

4.测试数据

Test.java

 @Test
    public void getDeptAndEmpBystep(){
    //这SqlSessionUtil.getSqlSession()是将前段的获取SqlSession的带码封装到一个类中
        SqlSession sqlSession = SqlSessionUtil.getSqlSession();
        DeptMapper mapper = sqlSession.getMapper(DeptMapper.class);
        Dept dept = mapper.getDeptAndEmpBystep(2);
        System.out.println(dept);
    }

• 在使用分级查询时可以对其config.xml进行配置延迟加载

 <settings>
        <!--将下划线映射为驼峰-->
        <setting name="mapUnderscoreToCamelCase" value="true"/>

        <!--开启延迟加载-->
        <setting name="lazyLoadingEnabled" value="true"/>
    </settings>

• 因为在分级查询时查询数据将会执行两端sql代码，如果当你只想对dept中的某个数据进行查询时例如

@Test
    public void getDeptAndEmpBystep(){
        SqlSession sqlSession = SqlSessionUtil.getSqlSession();
        DeptMapper mapper = sqlSession.getMapper(DeptMapper.class);
        Dept dept = mapper.getDeptAndEmpBystep(2);
        System.out.println(dept.getDeptName());
    }

• 这里就只是对dept表中的姓名进行输出因此对DeptMapper.xml文件进行修改

   <resultMap id="getDeptAndEmpResultMapStep" type="Dept">
        <id column="dept_id" property="deptId"></id>
        <result column="dept_name" property="deptName"></result>
         <!--fetchType在延迟加载开启时对当前分布加载进行设置是否立即加载
        eager立即加载
        lazy延迟加载
        -->
        <collection property="emps" fetchType="lazy"
                    select="com.mybatis.mapper.EmpMapper.getDeptAndEmpByStep"
                    column="dept_id"></collection>
    </resultMap>
    <select id="getDeptAndEmpBystep" resultMap="getDeptAndEmpResultMapStep">
        select * from dept where dept_id=#{DeptId}
    </select>

• 这个时候再调用代码时只会掉用dept表的查询语句