mybatis的一对多和多对一的连接查询

实体类：

package com.entity;

import java.util.List;

public class Dept {

    private Integer deptId;
    private String deptName;
    private List<Employee> employees;

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

    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<Employee> getEmployees() {
        return employees;
    }

    public void setEmployees(List<Employee> employees) {
        this.employees = employees;
    }
}

　　

package com.entity;

import java.math.BigDecimal;

public class Employee {

    private Integer employeeId;
    private String employeeName;
    private Integer employeeGender;
    private BigDecimal employeeSalary;
    private Integer deptId;
    private Dept dept;

    @Override
    public String toString() {
        return "Employee{" +
                "employeeId=" + employeeId +
                ", employeeName='" + employeeName + '\'' +
                ", employeeGender=" + employeeGender +
                ", employeeSalary=" + employeeSalary +
                ", deptId=" + deptId +
                ", dept=" + dept +
                '}';
    }

    public Integer getEmployeeId() {
        return employeeId;
    }

    public void setEmployeeId(Integer employeeId) {
        this.employeeId = employeeId;
    }

    public String getEmployeeName() {
        return employeeName;
    }

    public void setEmployeeName(String employeeName) {
        this.employeeName = employeeName;
    }

    public Integer getEmployeeGender() {
        return employeeGender;
    }

    public void setEmployeeGender(Integer employeeGender) {
        this.employeeGender = employeeGender;
    }

    public BigDecimal getEmployeeSalary() {
        return employeeSalary;
    }

    public void setEmployeeSalary(BigDecimal employeeSalary) {
        this.employeeSalary = employeeSalary;
    }

    public Integer getDeptId() {
        return deptId;
    }

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

    public Dept getDept() {
        return dept;
    }

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

　　多对一的几种方式：

1.

<mapper namespace="com.dao.EmployeeDao">

    <!--<insert id="insert">-->
        <!--<selectKey order="BEFORE" keyColumn="employeeId,employeeGender" keyProperty="employeeId,employeeGender" resultType="com.entity.Employee">-->
            <!--select employee_id employeeId,employee_gender employeeGender from employee where employee_id=13-->
        <!--</selectKey>-->
        <!--insert into subtable(employee_id,employee_name,employee_gender) values (#{employeeId},"mdzz",#{employeeGender})-->
    <!--</insert>-->

    <resultMap id="empResultMap" type="com.entity.Employee">
        <id property="employeeId" column="employee_id"></id>
        <result property="employeeName" column="employee_name"></result>
        <result property="employeeGender" column="employee_gender"></result>
        <result property="employeeSalary" column="employee_salary"></result>
        <result property="deptId" column="dept_id"></result>
        <result property="dept.deptId" column="dept_id"></result>
        <result property="dept.deptName" column="dept_name"></result>
    </resultMap>

    <select id="queryAll" resultMap="empResultMap">
        select employee_id,employee_name,employee_gender,employee_salary,e.dept_id,dept_name,d.dept_id from employee e left outer join dept d on e.dept_id = d.dept_id;
    </select>
</mapper>

　　2.

<resultMap id="empResultMap" type="com.entity.Employee">
        <id property="employeeId" column="employee_id"></id>
        <result property="employeeName" column="employee_name"></result>
        <result property="employeeGender" column="employee_gender"></result>
        <result property="employeeSalary" column="employee_salary"></result>
        <result property="deptId" column="dept_id"></result>
        <!--<result property="dept.deptId" column="dept_id"></result>-->
        <!--<result property="dept.deptName" column="dept_name"></result>-->
        <association property="dept" resultMap="deptResultMap"></association>
    </resultMap>

    <resultMap id="deptResultMap" type="com.entity.Dept">
        <id property="deptId" column="dept_id"></id>
        <result property="deptName" column="dept_name"></result>
    </resultMap>
    
    <select id="queryAll" resultMap="empResultMap">
        select employee_id,employee_name,employee_gender,employee_salary,e.dept_id,dept_name,d.dept_id from employee e left outer join dept d on e.dept_id = d.dept_id;
    </select>

　　2较之1的好处是单独把Dept类的resultMap提出来，可以复用

3.

<resultMap id="empResultMap" type="com.entity.Employee">
        <id property="employeeId" column="employee_id"></id>
        <result property="employeeName" column="employee_name"></result>
        <result property="employeeGender" column="employee_gender"></result>
        <result property="employeeSalary" column="employee_salary"></result>
        <result property="deptId" column="dept_id"></result>
        <!--<result property="dept.deptId" column="dept_id"></result>-->
        <!--<result property="dept.deptName" column="dept_name"></result>-->
        <association property="dept">
            <id property="deptId" column="dept_id"></id>
            <result property="deptName" column="dept_name"></result>
        </association>
    </resultMap>

    <select id="queryAll" resultMap="empResultMap">
        select employee_id,employee_name,employee_gender,employee_salary,e.dept_id,dept_name,d.dept_id from employee e left outer join dept d on e.dept_id = d.dept_id;
    </select>

　　只不过是第2中方法把<association>的resultMap省略，直接在里面配置管理实体的映射配置，无法做到2那样的resultMap复用

4.不推荐，n+1查询，耗费资源。解决方法有：内嵌resultMap或者使用内部缓存（cache）解决一部分

<resultMap id="empResultMap" type="com.entity.Employee">
        <id property="employeeId" column="employee_id"></id>
        <result property="employeeName" column="employee_name"></result>
        <result property="employeeGender" column="employee_gender"></result>
        <result property="employeeSalary" column="employee_salary"></result>
        <result property="deptId" column="dept_id"></result>
        <association property="dept" column="dept_id" select="getDept"></association>
    </resultMap>

<select id="queryAll" resultMap="empResultMap">
        select employee_id,employee_name,employee_gender,employee_salary,dept_id from employee;
    </select>
    
    <select id="getDept" resultType="com.entity.Dept">
        select dept_id,dept_name from dept where dept_id=#{deptId};
    </select>

　　也就是使用<association>的property指定实体类的字段，column是这个字段所代表的表的主键，即子表的外键（employee的外键dept_id），select指定<select>的id。

　　再一个注意点就是<select id="getDept">中的select语句的where条件。

 

 

一对多：写法和多对一类似

1.resultMap+collection

<mapper namespace="com.dao.DeptDao">
    <select id="queryAll" resultMap="deptResultMap">
        select employee_id,employee_name,employee_gender,employee_salary,e.dept_id,dept_name,d.dept_id from employee e right outer join dept d on e.dept_id = d.dept_id;
    </select>

    <resultMap id="deptResultMap" type="com.entity.Dept">
        <id property="deptId" column="dept_id"></id>
        <result property="deptName" column="dept_name"></result>
        <collection property="employees" resultMap="empResultMap"></association>
    </resultMap>

    <resultMap id="empResultMap" type="com.entity.Employee">
        <id property="employeeId" column="employee_id"></id>
        <result property="employeeName" column="employee_name"></result>
        <result property="employeeGender" column="employee_gender"></result>
        <result property="employeeSalary" column="employee_salary"></result>
        <result property="deptId" column="dept_id"></result>
    </resultMap>
</mapper>

　　另一种：n+1查询

<select id="queryAll" resultMap="deptResultMap">
        select dept_id,dept_name from dept;
    </select>

    <resultMap id="deptResultMap" type="com.entity.Dept">
        <id property="deptId" column="dept_id"></id>
        <result property="deptName" column="dept_name"></result>
        <collection property="employees" select="getEmp" column="dept_id"></collection>
    </resultMap>

    <select id="getEmp" resultMap="basicEmpResultMap">
        select employee_id,employee_name,employee_gender,employee_salary,dept_id from employee;
    </select>

    <resultMap id="basicEmpResultMap" type="com.entity.Employee">
        <id property="employeeId" column="employee_id"></id>
        <result property="employeeName" column="employee_name"></result>
        <result property="employeeGender" column="employee_gender"></result>
        <result property="employeeSalary" column="employee_salary"></result>
        <result property="deptId" column="dept_id"></result>
    </resultMap>

　　

public interface DeptDao {

    List<Dept> queryAll();
}

　　

public class DeptDaoTest {

    @Test
    public void testQueryAll() {
        DeptDao deptDao = MapperFactory.generateMapper(DeptDao.class);
        List<Dept> depts = deptDao.queryAll();
        System.out.println(depts);
    }
}