查询数据的结果中存在多个对象中有同一个数据。类似于多个员工中含有同一个部门。
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>