Mybatis多对一 与一对多

查询数据的结果中存在多个对象中有同一个数据。类似于多个员工中含有同一个部门。

1.多对一--------SQL查询嵌套

<?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.liuy.springMybatis.dao.EmpMapper">
    <resultMap id="emp_dept" type="Emp">
    <!--复杂属性之对象association   
    property属性名，
    column传入嵌套SQL参数---查询的列名
    javaType嵌套SQL返回类型，
    select嵌套SQL的ID-->
        <association property="dept" column="deptId" javaType="Dept" select="selectDeptById"/>
    </resultMap>
    <select id="selectAll" resultMap="emp_dept">
        select * from tbl_emp where name like concat('%','w','%')
    </select>
    <select id="selectDeptById" resultType="com.liuy.springMybatis.entity.Dept">
        select deptName from tbl_dept where id=#{deptId}
    </select>
</mapper>

只需要调用selectAll可得到员工以及员工部门信息。查询日志结果

2.多对一--------结果集嵌套

    <resultMap id="emp_dept" type="Emp">
        <result property="id" column="id"/>
        <result property="name" column="name"/>
<!--  复杂属性之对象association   property复杂对象属性名，column---查询的列名，javaType嵌套SQL返回类型-->
        <association property="dept" column="deptId" javaType="Dept">
            <result property="deptName" column="deptName"/>
        </association>
    </resultMap>
    <select id="selectAll" resultMap="emp_dept">
        select tbl_emp.*,tbl_dept.deptName from tbl_emp,tbl_dept where tbl_emp.name like concat('%','w','%')  and tbl_emp.deptId=tbl_dept.id
    </select>

一对多：一个部门下有多个员工

1.一对多--------结果集嵌套

接收查询数据类Dept

@Data
public class Dept {
    int id;
    String deptName;
    String locAdd;
    List<Emp> emps;
}

Mapper.xml

    <resultMap id="Dept_emp" type="Dept">
        <result property="id" column="id"/>
        <result property="deptName" column="deptName"/>
        <collection property="emps" ofType="Emp">
            <result property="name" column="name"/>
        </collection>
    </resultMap>
    <select id="selectAll" resultMap="Dept_emp">
        select * from tbl_dept,tbl_emp where tbl_dept.id=#{id} and tbl_emp.deptId=tbl_dept.id
    </select>

查询结果 一个部门下 多个员工

2.一对多--------查询嵌套

<resultMap id="Dept_emp" type="Dept">
    <collection property="emps"  javaType="ArrayList" 
    ofType="Emp" select="selectEmpByDeptId" column="id"/>
</resultMap>
<select id="selectAll" resultMap="Dept_emp">
    select * from tbl_dept where tbl_dept.id=#{id}
</select>
<select id="selectEmpByDeptId" resultType="Emp">
    select * from tbl_emp where deptId=#{id}
</select>