使用Mybatis实现树查询

我们的项目中经常都需要用到树查询，今天简单的写一个关于部门的树查询

1，首先在准备好部门表以及相应的部门实体

public class SysDepartment implements java.io.Serializable {
    //id
    private String id;
    //部门编号
    private String deptCode;
    //部门名称
    private String deptName;
	//父id
    private String parentId;
	//路径
    private String path;
	
	//省略get和set方法
	
}

2，拓展部门实体，使它拥有子结构

public class SysDepartmentExt extends SysDepartment {
    //将children设置成自己，这样就形成了树结构
    List<SysDepartmentExt> children;
	
	//省略get和set方法
}

3，编写xml文件

<?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.yun.demo.extmapper.sys.SysDepartmentExtMapper">
    <resultMap id="DepartmentMap" type="com.yun.demo.extmapper.sys.extentity.SysDepartmentExt">
        <id column="id" jdbcType="VARCHAR" property="id"/>
        <result column="dept_code" jdbcType="VARCHAR" property="deptCode"/>
        <result column="dept_name" jdbcType="VARCHAR" property="deptName"/>
        <result column="parent_id" jdbcType="VARCHAR" property="parentId"/>
        <result column="path" jdbcType="VARCHAR" property="path"/>
        <collection property="children" column="id" select="getChildrenByPid">
        </collection>
    </resultMap>

    <select id="getChildrenByPid" parameterType="string" resultMap="DepartmentMap">
        select * from sys_department where parent_id=#{id} order by path asc
    </select>


    <select id="findDeptTree" parameterType="com.yun.demo.mapper.sys.entity.SysDepartment"
            resultMap="DepartmentMap">
        select * from sys_department
        <where>
            <if test="id !=null">
                and id=#{id}
            </if>
            <if test="deptName !=null">
                and dept_name like concat(concat('%',#{deptName,jdbcType=VARCHAR}),'%')
            </if>
			<if test="deptCode !=null">
                and dept_code like concat(concat('%',#{deptCode,jdbcType=VARCHAR}),'%')
            </if>
            <if test="parentId !=null">
                and parent_id = #{parentId}
            </if>
            <if test="parentId ==null">
                and parent_id is null
            </if>
        </where>
        order by path asc
    </select>

</mapper>

4，编写接口mapper

@Repository
public interface SysDepartmentExtMapper {
    List<SysDepartmentExt> findDeptTree(SysDepartment sysDepartment);

}

就这样一个简单的部门树查询就完成了