mybatis之级联查询

根据部门号，查询对应的部门信息和员工信息

一、对应的pojo类

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Emp implements Serializable {
    private Integer empno;
    private String ename;
    private String job;
    private Integer mgr;
    private Date hiredate;
    private Double sal;
    private Double comm;
    private Integer deptno;
    // 组合一个Dept对象作为自己的属性
//    private Dept dept;

}

@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class Dept implements Serializable {

    private Integer deptno;
    private String dname;
    private String loc;

    //list 集合中存入 emp 对象
    private List<Emp> empList;
}

 

二、mapper

2.1 DeptMapper

/**
 * 根据deptno 查询部门信息
 * @param deptno
 * @return
 */
public Dept findDept(Integer deptno);

2.2 EmpMapper

/**
 * 根据部门号查询多个 员信息
 * @param deptno
 * @return
 */
public List<Emp> findAllEmpByDeptNO(Integer deptno);

三、xml

3.1 EmpMapper.xml

<select id="findAllEmpByDeptNO" resultType="com.yan.pojo.Emp">
    select * from emp where deptno = #{deptno}
</select>

3.2 DeptMapper.xml

<!--级联查询的方式-->
<resultMap id="findDeptMap" type="com.yan.pojo.Dept">
    <id column="deptno" property="deptno"/>
    <result column="dname" property="dname" />
    <result column="loc" property="loc" />
    <!--
    property="empList"
                select="com.yan.mapper.EmpMapper.findAllEmpByDeptNO" 要调用的另一个的sql语句
                javaType="list" 对应的类型
                column="deptno" 传入的参数
                jdbcType="INTEGER" 传入参数的数据库的类型
                fetchType="eager" 积极加载  lazy 是懒加载
    -->
    <collection property="empList"
                select="com.yan.mapper.EmpMapper.findAllEmpByDeptNO"
                javaType="list"
                column="deptno"
                jdbcType="INTEGER"
                fetchType="eager"
    >
    </collection>
</resultMap>
<!---->
<select id="findDept" resultMap="findDeptMap">
    select * from dept where deptno = #{deptno}
</select>

四、测试
 

 @Test
    public void test01(){

        try {
            SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
            SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(Resources.getResourceAsStream("applicationContext-mybatis.xml"));
            SqlSession sqlSession = sqlSessionFactory.openSession();
            DeptMapper deptMapper = sqlSession.getMapper(DeptMapper.class);

            Dept dept = deptMapper.findDept(20);
            System.out.println(dept);

            List<Emp> empList = dept.getEmpList();
            for (Emp emp : empList) {
                System.out.println(emp);
            }

            /*EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);
            List<Emp> emps = empMapper.findAllEmpByDeptNO(20);
            for (Emp emp : emps) {
                System.out.println(emp);
            }
*/

            sqlSession.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

测试结果：

Dept(deptno=20, dname=RESEARCH, loc=DALLAS, empList=[Emp(empno=7369, ename=SMITH, job=CLERK, mgr=7902, hiredate=Wed Dec 17 00:00:00 CST 1980, sal=800.0, comm=null, deptno=20), Emp(empno=7566, ename=JONES, job=MANAGER, mgr=7839, hiredate=Thu Apr 02 00:00:00 CST 1981, sal=2975.0, comm=null, deptno=20), Emp(empno=7788, ename=SCOTT, job=ANALYST, mgr=7566, hiredate=Sun Apr 19 00:00:00 CDT 1987, sal=3000.0, comm=null, deptno=20), Emp(empno=7876, ename=ADAMS, job=CLERK, mgr=7788, hiredate=Sat May 23 00:00:00 CDT 1987, sal=1100.0, comm=null, deptno=20), Emp(empno=7902, ename=FORD, job=ANALYST, mgr=7566, hiredate=Thu Dec 03 00:00:00 CST 1981, sal=3000.0, comm=null, deptno=20)])
Emp(empno=7369, ename=SMITH, job=CLERK, mgr=7902, hiredate=Wed Dec 17 00:00:00 CST 1980, sal=800.0, comm=null, deptno=20)
Emp(empno=7566, ename=JONES, job=MANAGER, mgr=7839, hiredate=Thu Apr 02 00:00:00 CST 1981, sal=2975.0, comm=null, deptno=20)
Emp(empno=7788, ename=SCOTT, job=ANALYST, mgr=7566, hiredate=Sun Apr 19 00:00:00 CDT 1987, sal=3000.0, comm=null, deptno=20)
Emp(empno=7876, ename=ADAMS, job=CLERK, mgr=7788, hiredate=Sat May 23 00:00:00 CDT 1987, sal=1100.0, comm=null, deptno=20)
Emp(empno=7902, ename=FORD, job=ANALYST, mgr=7566, hiredate=Thu Dec 03 00:00:00 CST 1981, sal=3000.0, comm=null, deptno=20)