Mybatis--多表联查

1. 1. 实体类中包含对象的普通写法：

Employee和Department都是实体类

<resultMap type="Employee" id="result1">

<id column="id" property="id" />

<result column="last_name" property="lastName" />

<result column="email" property="email" />

<result column="gender" property="gender" />

<!-- 1.联合查询（使用对象进行关联） -->

<!-- <result column="dept_id" property="dept.id"/>

 <result column="dname"   property="dept.dname"/> -->

<!-- 2.关联查询,可以关联一个独立的对象 -->

<association property="dept"

javaType="com.hwua.entity.Department">

<id column="dept_id" property="id" />

<result column="dname" property="dname" />

</association>

</resultMap>

<select id="findById" parameterType="Long" resultMap="result1">

select e.id,e.last_name,e.email,e.gender,e.dept_id,d.dname

from t_emps e,t_dept d where e.dept_id=d.id and e.id=#{id}

</select>

1. 1. 实体类中包含对象的分步写法

<!-- 分步查询 -->

<resultMap type="Employee" id="result2">

<id column="id" property="id" />

<result column="last_name" property="lastName" />

<result column="email" property="email" />

<result column="gender" property="gender" />

<!-- 3.使用association实现分段查询（分步），将一条sql拆分

property 是pojo中定义的以类为属性的名称：private Department dept;

select可以调用一个接口的方法

column 表示入参,这个接口需要传入的参数 -->

<association property="dept"

select="com.hwua.dao.DeptMapper.findById" 

column="dept_id">

<id column="id" property="id" />

<result column="dname" property="dname" />

</association>

</resultMap>

<select id="findByIdStep" parameterType="Long"

resultMap="result2">

select e.id,e.last_name,e.email,e.gender,e.dept_id from

t_emps e where e.id=#{id}

</select>

1. 1. 实体类中包含集合的普通写法

<resultMap type="com.hwua.entity.Department" id="result1">

<id column="id" property="id" />

<result column="dname" property="dname" />

<!-- 员工信息 collection:封装集合对象

ofType:专门封装集合对象时，用来指定对象类型，不能用javaType -->

<collection property="emps"

ofType="com.hwua.entity.Employee">

<id column="eid" property="id" />

<result column="last_name" property="lastName" />

<result column="email" property="email" />

<result column="gender" property="gender" />

</collection>

</resultMap>

<select id="findFullById" parameterType="Long"

resultMap="result1">

select d.id,dname,e.id eid,e.last_name,e.email,e.gender

from t_dept d LEFT JOIN t_emps e

on e.dept_id=d.id

where d.id=#{1}

</select>

1. 1. 实体类中包含集合的分步写法

<!-- 分步查询 -->

<resultMap type="com.hwua.entity.Department" id="result2">

<id column="id" property="id" />

<result column="dname" property="dname" />

<collection property="emps"

select="com.hwua.dao.EmpMapper.findByDeptId" column="id">

<id column="id" property="id" />

<result column="last_name" property="lastName" />

<result column="email" property="email" />

<result column="gender" property="gender" />

</collection>

</resultMap>

<select id="findFullByIdStep" parameterType="Long"

resultMap="result2">

select d.id,dname from t_dept d where d.id=#{1}

</select>