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>
posted @ 2019-01-24 10:43  RosaDarker  阅读(...)  评论(...编辑  收藏