mybatis级联查询

<?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="cn.hx.property.dao.UserDao">

    <resultMap type="cn.hx.property.entity.User" id="UserMap">
        <result property="userId" column="user_id" jdbcType="INTEGER"/>
        <result property="username" column="username" jdbcType="VARCHAR"/>
        <result property="password" column="password" jdbcType="VARCHAR"/>
        <result property="salt" column="salt" jdbcType="VARCHAR"/>
        <result property="email" column="email" jdbcType="VARCHAR"/>
        <result property="mobile" column="mobile" jdbcType="VARCHAR"/>
        <result property="status" column="status" jdbcType="INTEGER"/>
        <result property="dept.deptId" column="dept_id" jdbcType="INTEGER"/>
        <result property="createTime" column="create_time" jdbcType="TIMESTAMP"/>
    </resultMap>
    <resultMap type="cn.hx.property.entity.User" id="UserWithDeptMap">
        <id property="userId" column="user_id" jdbcType="INTEGER"/>
        <result property="username" column="username" jdbcType="VARCHAR"/>
        <result property="password" column="password" jdbcType="VARCHAR"/>
        <result property="salt" column="salt" jdbcType="VARCHAR"/>
        <result property="email" column="email" jdbcType="VARCHAR"/>
        <result property="mobile" column="mobile" jdbcType="VARCHAR"/>
        <result property="status" column="status" jdbcType="INTEGER"/>
        <result property="createTime" column="create_time" jdbcType="TIMESTAMP"/>
        <!--  association针对对象 collection 集合   column 当前查询结果里面的一个字段    -->
       <!-- 第二种方式 <association property="dept" column="dept_id" select="cn.hx.property.dao.DeptDao.queryById"/>-->
        <!--第三种性能最好-->
        <association property="dept" javaType="cn.hx.property.entity.Dept" >
            <id property="deptId" column="dept_id"/>
            <result property="parentId" column="parent_id"/>
            <result property="name" column="name"/>
            <result property="orderNum" column="order_num"/>
            <result property="delFlag" column="del_flag"/>
        </association>
<!--   collection 里面的 ofType 表示集合里面的类型    -->
        <collection property="roles" ofType="cn.hx.property.entity.Role">
            <id property="roleId" column="role_id"/>
            <result property="deptId" column="dept_id"/>
            <result property="createTime" column="create_time"/>
            <result property="remark" column="remark"/>
            <result property="roleName" column="role_name"/>
        </collection>
    </resultMap>
    <select id="findByUserName" resultMap="UserWithDeptMap" >
        select *
        from sys_user u
        left join sys_dept d on u.dept_id = d.dept_id
        left join sys_user_role ur on u.user_id = ur.user_id
        left join sys_role r on ur.role_id=r.role_id
        <where>
            and username = #{username}
        </where>
    </select>

    <!--查询单个-->
    <select id="queryById" resultMap="UserMap">
        select user_id,
               username,
               password,
               salt,
               email,
               mobile,
               status,
               dept_id,
               create_time
        from property.sys_user
        where user_id = #{userId}
    </select>

    <!--查询指定行数据-->
    <select id="queryAllByLimit" resultMap="UserMap">
        select user_id,
               username,
               password,
               salt,
               email,
               mobile,
               status,
               dept_id,
               create_time
        from property.sys_user limit #{offset}, #{limit}
    </select>

    <!--通过实体作为筛选条件查询-->
    <select id="queryAll" resultMap="UserMap">
        select
        user_id, username, password, salt, email, mobile, status, dept_id, create_time
        from property.sys_user
        <where>
            <if test="userId != null">
                and user_id = #{userId}
            </if>
            <if test="username != null and username != ''">
                and username = #{username}
            </if>
            <if test="password != null and password != ''">
                and password = #{password}
            </if>
            <if test="salt != null and salt != ''">
                and salt = #{salt}
            </if>
            <if test="email != null and email != ''">
                and email = #{email}
            </if>
            <if test="mobile != null and mobile != ''">
                and mobile = #{mobile}
            </if>
            <if test="status != null">
                and status = #{status}
            </if>
            <if test="dept!=null and dept.deptId != null">
                and dept_id = #{dept.deptId}
            </if>
            <if test="createTime != null">
                and create_time = #{createTime}
            </if>
        </where>
    </select>
    <!--  resultType (数据库字段必须和实体属性一致才能封装该属性) 与
    resultMap （通过前面的resultMap看出，属性和字段如何对应，而且可以进行级联） -->



    <!--新增所有列-->
    <insert id="insert" keyProperty="userId" useGeneratedKeys="true">
        insert into property.sys_user(username, password, salt, email, mobile, status, dept_id, create_time)
        values (#{username}, #{password}, #{salt}, #{email}, #{mobile}, #{status}, #{dept.deptId}, #{createTime})
    </insert>

    <!--通过主键修改数据-->
    <update id="update">
        update property.sys_user
        <set>
            <if test="username != null and username != ''">
                username = #{username},
            </if>
            <if test="password != null and password != ''">
                password = #{password},
            </if>
            <if test="salt != null and salt != ''">
                salt = #{salt},
            </if>
            <if test="email != null and email != ''">
                email = #{email},
            </if>
            <if test="mobile != null and mobile != ''">
                mobile = #{mobile},
            </if>
            <if test="status != null">
                status = #{status},
            </if>
            <if test="dept!=null and dept.deptId != null">
                dept_id = #{dept.deptId},
            </if>
            <if test="createTime != null">
                create_time = #{createTime},
            </if>
        </set>
        where user_id = #{userId}
    </update>

    <!--通过主键删除-->
    <delete id="deleteById">
        delete
        from property.sys_user
        where user_id = #{userId}
    </delete>

</mapper>