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

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

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

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

1，准备工作

（1）假设我们有如下一张菜单表 menu，其中子菜单通过 parendId 与父菜单的 id 进行关联：  

（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>

最终获取到的结果如下：

（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>

（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>

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

（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>

（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>