学习笔记——Mybatis多表查询

Mybatis多表查询

多对一

public class Employee {
    private Long id;
    private String name;
    private Long dept_id;
    private Department dept;

public class Department {
    private Long id;
    private String name;

方法一：嵌套查询

查询第一个表employee，将里面的外键属性dept_id作为第二个查询语句的条件，查询第二个表department，最后自动映射

<!--    List<Employee> findEmpAll();-->
    <select id="findEmpAll" resultMap="emp_map">
        select * from employee
    </select>
    <resultMap id="emp_map" type="Employee">
        <association property="dept" column="dept_id" select="findDeptById"/>
    </resultMap>
    <select id="findDeptById" parameterType="Long" resultType="Department">
        select * from department where id = #{id}
    </select>

这种方法会导致最后查询出来的dept_id因用于第二个表的映射，而显示为null值。只能通过dept.id来获取部门id

方法二：嵌套结果

将多表数据全部查出来，然后手动映射resultMap

相对安全，推荐使用，基于性能考虑，员工要多一些，顺带把部门信息查出来

<!--    List<Employee> findEmpAll();-->
    <select id="findEmpAll" resultMap="emp_map">
        select e.*,d.id did,d.name dname from employee e join department d on e.dept_id = d.id
    </select>
    <resultMap id="emp_map" type="Employee">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <association property="dept" javaType="Department">
            <id property="id" column="did"/>
            <result property="name" column="dname"/>
        </association>
    </resultMap>

一对多

public class Department {
    private Long id;
    private String name;
    private List<Employee> emps;

public class Employee {
    private Long id;
    private String name;
    private Long dept_id;

一对多、多对一同理，主要注意的就是要把javaType换成ofType

<!--  List<Department> findDeptList();-->
    <select id="findDeptList" resultMap="dept_map">
        select e.*,d.id did,d.name dname from employee e join department d on e.dept_id = d.id
    </select>
    <resultMap id="dept_map" type="department">
        <id property="id" column="did"/>
        <result property="name" column="dname"/>
        <collection property="emps" ofType="employee">
            <id property="id" column="id"/>
            <result property="name" column="name"/>
            <result property="dept_id" column="dept_id"/>
        </collection>
    </resultMap>

站在部门看员工，一个部门对应多个员工

<!--    List<Department> findDeptAll();-->
    <select id="findDeptAll" resultMap="dept_map">
        select * from department
    </select>
    <resultMap id="dept_map" type="Department">
        <collection property="emps" column="id" select="findEmpByDid"/>
    </resultMap>
    <select id="findEmpByDid" parameterType="Long" resultType="Employee">
        select * from employee where dept_id = #{id}
    </select>

一对一的写法参考多对一，用association