十一、MyBatis实现多对一映射处理

准备

数据库表
员工表（t_employee）

部门表（t_department）

表所对应的类
员工 t_employee

部门 t_department

方式一（级联方式处理映射关系）

以通过员工id查询员工信息以及部门信息为例。
接口

public interface EmployeeMapper {

    //通过员工的eid查询员工的所有信息
    Employee selectEmployee(@Param("eid")Integer eid);

}

映射文件

<?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"><!--约束，约束不同xml中所写的标签也不同-->
<mapper namespace="com.xxx.mapper.EmployeeMapper"><!--接口-->

    <resultMap id="empMap" type="com.xxx.pojo.Employee">
        <id property="eid" column="eid"></id>
        <result property="ename" column="ename"></result>
        <result property="age" column="age"></result>
        <result property="sex" column="sex"></result>
        
        <result property="dept.did" column="did"></result>
        <result property="dept.dname" column="dname"></result>

    </resultMap>


    <select id="selectEmployee" resultMap="empMap">
        SELECT t_employee.*,t_department.* FROM t_employee LEFT JOIN t_department ON t_employee.did=t_department.did WHERE t_employee.eid=#{eid}
    </select>
</mapper>

测试

		EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);

        //查询员工eid=1的所有信息
        Employee employee = mapper.selectEmployee(1);

        System.out.println(employee);

方式二（使用association处理映射关系）

以通过员工id查询员工信息以及部门信息为例。
接口

public interface EmployeeMapper {

    //通过员工的eid查询员工的所有信息
    Employee selectEmployee(@Param("eid")Integer eid);

}

映射文件

<?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"><!--约束，约束不同xml中所写的标签也不同-->
<mapper namespace="com.xxx.mapper.EmployeeMapper"><!--接口-->

    <resultMap id="empMap" type="com.xxx.pojo.Employee">
        <id property="eid" column="eid"></id>
        <result property="ename" column="ename"></result>
        <result property="age" column="age"></result>
        <result property="sex" column="sex"></result>

        <association property="dept" javaType="com.xxx.pojo.Department">
            <id property="did" column="did"></id>
            <result property="dname" column="dname"></result>
        </association>

    </resultMap>


    <select id="selectEmployee" resultMap="empMap">
        SELECT t_employee.*,t_department.* FROM t_employee LEFT JOIN t_department ON t_employee.did=t_department.did WHERE t_employee.eid=#{eid}
    </select>
</mapper>

测试

		EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);

        //查询员工eid=1的所有信息
        Employee employee = mapper.selectEmployee(1);

        System.out.println(employee);

方式三（分步查询）

以通过员工id查询员工信息以及部门信息为例。
接口
第一步接口

public interface EmployeeMapper {

    //通过员工的did查询员工的所有信息
    Employee selectEmployee(@Param("eid")Integer eid);

}

第二步接口

public interface DepartmentMapper {

    //通过员工的did查询员工的所有信息
    Department selectDepartment(@Param("did")Integer did);

}

映射文件
第一步映射文件

<?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"><!--约束，约束不同xml中所写的标签也不同-->
<mapper namespace="com.xxx.mapper.EmployeeMapper"><!--接口-->

    <resultMap id="empMap" type="com.xxx.pojo.Employee">
        <id property="eid" column="eid"></id>
        <result property="ename" column="ename"></result>
        <result property="age" column="age"></result>
        <result property="sex" column="sex"></result>
<!--此处的select中写的是第二步接口中的方法的全路径，colum中写的是第一步查出的那个数据作为第二步的参数-->
        <association property="dept"
                     select="com.xxx.mapper.DepartmentMapper.selectDepartment"
                     column="did">
        </association>

    </resultMap>


    <select id="selectEmployee" resultMap="empMap">
        SELECT t_employee.*,t_department.* FROM t_employee LEFT JOIN t_department ON t_employee.did=t_department.did WHERE t_employee.eid=#{eid}
    </select>
</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"><!--约束，约束不同xml中所写的标签也不同-->
<mapper namespace="com.xxx.mapper.DepartmentMapper"><!--接口-->


    <select id="selectDepartment" resultType="com.xxx.pojo.Department">
        select * from t_department where did=#{did}
    </select>
</mapper>

测试

		EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);

        //查询员工eid=1的所有信息
        Employee employee = mapper.selectEmployee(1);

        System.out.println(employee);