mybatis 查询树形结构

1. 简介

对于树结构数据,通过mybatis的 mapper的xml文件实现递归查询.
一般每条记录都有一个类似parentId的字段

2. xml文件示例

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org/DTD Mapper 3.0" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.iflytek.consumers.ibuilding.produce.dao.MenuMapper">

    <resultMap id="baseResult" type="com.skd.entity.MenuPo">
        <id property="id" column="id"/>
        <collection
                property="childList"
                ofType="com.skd.entity.MenuPo"
                column="{parentId=id}"
                select="getTreeWithId"
        />
    </resultMap>
    <!--
        id不为空,parentId为空: 查询该节点的数结构(包括节点本身)
        id为空,parentId不为空: 查询该节点的数结构(不包括节点本身)
    -->
    <select id="getTreeWithId" resultMap="baseResult">
        SELECT * FROM menu
        <where>
            <if test="id != null and id != ''">
               and  id = #{id}
            </if>
            <if test="parentId != null and parentId != ''">
                and parent_id = #{parentId}
            </if>
        </where>

    </select>

    <resultMap id="allTree" type="com.skd.entity.MenuPo">
        <id property="id" column="id"/>
        <collection
                property="childList"
                ofType="com.skd.entity.MenuPo"
                column="{parentId=id}"
                select="getAllTree"
        />
    </resultMap>
    <!--
        parentId为空: 查询整个树结构
    -->
    <select id="getAllTree" resultMap="allTree">
        SELECT * FROM menu
        <where>
            <if test="parentId == null ">
                and parent_id is null
            </if>
            <if test="parentId != null and parentId != ''">
                and parent_id = #{parentId}
            </if>
        </where>

    </select>

</mapper>