MyBatis实现两种查询树形数据(嵌套结果集和递归查询)

原文链接: https://www.jb51.net/article/223571.htm

    树形结构数据在开发中十分常见,比如:菜单数、组织树, 利用 MyBatis 提供嵌套查询功能可以很方便地实现这个功能需求。而其具体地实现方法又有两种,下面分别通过样例进行演示。

方法一:使用嵌套结果集实现

1,准备工作

(1)假设我们有如下一张菜单表 menu,其中子菜单通过 parendId 与父菜单的 id 进行关联:  

原文:MyBatis - 两种查询树形数据的方法详解(嵌套结果集、递归查询)

(2)对应的实体类如下:

1
2
3
4
5
6
7
@Setter
@Getter
public class Menu {
    private Integer id;
    private String name;
    private List<Menu> children;
}

2,实现代码

(1)假设目前菜单只有两级,MyBatis 语句如下。其原理是通过关联查询,一次性将数据查询出来,然后根据 resultMap 的配置进行转换,构建目标实体类。

优点:只由于该方法需要访问一次数据库就可以了,不会造成严重的数据库访问消耗。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
<?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.example.demo.mapper.MenuMapper">
    <resultMap type="com.example.demo.bean.Menu" id="BaseResultMap">
        <id column="id" property="id"/>
        <result column="name" property="name"/>
        <collection property="children" ofType="com.example.demo.bean.Menu">
            <id column="id2" property="id"/>
            <result column="name2" property="name"/>
        </collection>
    </resultMap>
    <select id="getAllMenus" resultMap="BaseResultMap">
        select
            m1.id as id,
            m1.name as name,
            m2.id as id2,
            m2.name as name2
        from menu m1,menu m2
        where m1.`id`=m2.`parentId`
    </select>
</mapper>

最终获取到的结果如下:

原文:MyBatis - 两种查询树形数据的方法详解(嵌套结果集、递归查询)

(2)如果菜单有三级的话,则 MyBatis 语句做如下修改,再增加一个嵌套结果级即可:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
<?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.example.demo.mapper.MenuMapper">
    <resultMap type="com.example.demo.bean.Menu" id="BaseResultMap">
        <id column="id" property="id"/>
        <result column="name" property="name"/>
        <collection property="children" ofType="com.example.demo.bean.Menu">
            <id column="id2" property="id"/>
            <result column="name2" property="name"/>
            <collection property="children" ofType="com.example.demo.bean.Menu">
                <id column="id3" property="id"/>
                <result column="name3" property="name"/>
            </collection>
        </collection>
    </resultMap>
    <select id="getAllMenus" resultMap="BaseResultMap">
        select
            m1.id as id,
            m1.name as name,
            m2.id as id2,
            m2.name as name2,
            m3.id as id3,
            m3.name as name3
        from menu m1,menu m2,menu m3
        where m1.`id`=m2.`parentId` and m2.`id`=m3.`parentId`
    </select>
</mapper>

原文:MyBatis - 两种查询树形数据的方法详解(嵌套结果集、递归查询)

(3)如果菜单级别不确定,可能只有一级、或者有两级、或者有三级(最多三级),可以对 SQL 语句稍作修改,改成左连接即可:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
<?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.example.demo.mapper.MenuMapper">
    <resultMap type="com.example.demo.bean.Menu" id="BaseResultMap">
        <id column="id" property="id"/>
        <result column="name" property="name"/>
        <collection property="children" ofType="com.example.demo.bean.Menu">
            <id column="id2" property="id"/>
            <result column="name2" property="name"/>
            <collection property="children" ofType="com.example.demo.bean.Menu">
                <id column="id3" property="id"/>
                <result column="name3" property="name"/>
            </collection>
        </collection>
    </resultMap>
    <select id="getAllMenus" resultMap="BaseResultMap">
        select
            m1.id as id,
            m1.name as name,
            m2.id as id2,
            m2.name as name2,
            m3.id as id3,
            m3.name as name3
        from menu m1
            left join menu m2 on m1.id=m2.parentId
            left join menu m3 on m2.id=m3.parentId
        where m1.parentId=0
    </select>
</mapper>

原文:MyBatis - 两种查询树形数据的方法详解(嵌套结果集、递归查询)

方法二:使用递归查询实现

(1)下面代码使用递归查询出所有菜单(无论层级有多深):

    递归查询好处在于简单易懂,通过简单的配置就可以达到目标效果。不足之处在于由于需要多次查询数据库,如果结果集记录条数过大,会造成较大的数据库访问消耗。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
<?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.example.demo.mapper.MenuMapper">
    <resultMap type="com.example.demo.bean.Menu" id="BaseResultMap">
        <id column="id" property="id"/>
        <result column="name" property="name"/>
        <collection property="children" select="findMenuByParentId" column="id"/>
    </resultMap>
  
    <!--级联查询父菜单-->
    <select id="getAllMenus" resultMap="BaseResultMap" >
         select * from menu where parentId = 0
    </select>
  
    <!--级联查询子菜单-->
    <select id="findMenuByParentId" resultMap="BaseResultMap" >
         select * from menu where parentId = #{id}
    </select>
</mapper>

原文:MyBatis - 两种查询树形数据的方法详解(嵌套结果集、递归查询)

(2)关联查询还可以传递多个参数,此时传递部分 column 的值为多个键值对(由于这里传递的 name 其实没有用到,只是做个演示,下面的查询结果同前面的是一样的):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
<?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.example.demo.mapper.MenuMapper">
    <resultMap type="com.example.demo.bean.Menu" id="BaseResultMap">
        <id column="id" property="id"/>
        <result column="name" property="name"/>
        <collection property="children" select="findMenuByParentId" column="{id=id,name=name}"/>
    </resultMap>
  
    <!--级联查询父菜单-->
    <select id="getAllMenus" resultMap="BaseResultMap" >
         select * from menu where parentId = 0
    </select>
  
    <!--级联查询子菜单-->
    <select id="findMenuByParentId" resultMap="BaseResultMap" >
         select * from menu where parentId = #{id}
    </select>
</mapper>
posted @ 2022-09-22 17:35  枫树湾河桥  阅读(1434)  评论(0编辑  收藏  举报
Live2D