Mybatis 子查询
在查询数据库时,需要以查询结果为查询条件进行关联查询。
在mybatis中通过association标签和collection标签实现子查询。
1. collection(集合)和association(关联)的区别
collection用于一对多关系, association用于一对一和多对一
实例代码:
public class User{
private Card card_one; //一对一,映射时使用association
private List<Card> card_many; //一对多,映射时使用collection
}
2. 标签属性
property: 集合属性的名称,如User的card_one和card_many
ofType: 集合中元素的类型,如Card(谨慎起见,应带上包名)
select: 子查询的ID
column: 传给子查询的参数
javaType: 一般为ArrayList
示例:
<collection property="实体类属性名"
ofType="包名.实体类名"
column="{传入参数名1 = 对应的数据表名称, ...}"
select="子查询ID"
javaType="java.util.ArrayList" />
3.传入参数注意事项
子查询的参数中:
- 有
<if test="">
时,需要指定别名,如:column="{projectId=project_id}"
- 没有
<if test="">
时,有时不能有别名,否则会出现注入参数为空,如:column="project_id"
4.代码示例
mybatis实现部门树结构查询,子查询使用和父查询一样的resultMap,递归查询子部门
组织结构
<resultMap id="departmentTreeMap" type="com.cdqd.app.entity.DepartmentEntity">
<id column="department_id" property="departmentId" jdbcType="INTEGER" />
<result column="department_name" property="departmentName" jdbcType="VARCHAR" />
<result column="department_level" property="departmentLevel" jdbcType="INTEGER" />
<result column="parent_id" property="parentId" jdbcType="INTEGER" />
<result column="leader_id" property="leaderId" jdbcType="INTEGER" />
<result column="department_status" property="departmentStatus" jdbcType="INTEGER" />
<result column="department_remark" property="departmentRemark" jdbcType="VARCHAR" />
<result column="nick_name" property="leaderName" jdbcType="VARCHAR" />
<result column="user_name" property="leaderLoginName" jdbcType="VARCHAR" />
<result column="user_tel" property="leaderTel" jdbcType="VARCHAR" />
<collection property="children"
ofType="com.cdqd.app.entity.DepartmentEntity"
column="{departmentId = department_id}"
select="selectWithLeader"
javaType="java.util.ArrayList" />
</resultMap>
第一级部门
<select id="selectWithChildren" resultMap="departmentTreeMap" parameterType="java.util.HashMap">
select
d.*,
u.nick_name,
u.user_name,
u.user_tel
from department d
left join user_info u on d.leader_id = u.user_id
<where>
d.department_status != 2
<!--department_level = 0时为公司,不显示,从公司直属部门开始查询-->
<if test="startDepartmentId == null">
and d.department_level = 1
</if>
<if test="startDepartmentId != null">
and d.department_id = #{startDepartmentId, jdbcType = INTEGER}
</if>
</where>
</select>
子部门查询
<select id="selectWithLeader" resultMap="departmentTreeMap">
select
d.*,
u.nick_name,
u.user_name,
u.user_tel
from department d
left join user_info u on d.leader_id = u.user_id
<where>
d.department_status != 2
<if test="departmentId != null">
and d.parent_id = #{departmentId}
</if>
</where>
</select>
喜欢《名侦探狄仁杰》里诸葛王朗的一句话:你开心也是一天,不开心也是一天,何不给自己一个大大的微笑?《冰与火之歌》说:The living should smile, for the dead cannot.(活着的人理应欢笑,因为死者无法做到)。
人生苦短,应当及时行乐,大声欢笑。