您好,登录后才能下订单哦!
这篇文章主要为大家展示了“MyBatis如何查询树形数据”,内容简而易懂,条理清晰,希望能够帮助大家解决疑惑,下面让小编带领大家一起研究并学习一下“MyBatis如何查询树形数据”这篇文章吧。
(1)假设我们有如下一张菜单表 menu,其中子菜单通过 parendId 与父菜单的 id 进行关联:
(2)对应的实体类如下:
@Setter @Getter public class Menu { private Integer id; private String name; private List<Menu> children; }
(1)假设目前菜单只有两级,MyBatis 语句如下。其原理是通过关联查询,一次性将数据查询出来,然后根据 resultMap 的配置进行转换,构建目标实体类。
优点:只由于该方法需要访问一次数据库就可以了,不会造成严重的数据库访问消耗。
<?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 语句做如下修改,再增加一个嵌套结果级即可:
<?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 语句稍作修改,改成左连接即可:
<?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)下面代码使用递归查询出所有菜单(无论层级有多深):
递归查询好处在于简单易懂,通过简单的配置就可以达到目标效果。不足之处在于由于需要多次查询数据库,如果结果集记录条数过大,会造成较大的数据库访问消耗。
<?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 其实没有用到,只是做个演示,下面的查询结果同前面的是一样的):
<?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>
以上是“MyBatis如何查询树形数据”这篇文章的所有内容,感谢各位的阅读!相信大家都有了一定的了解,希望分享的内容对大家有所帮助,如果还想学习更多知识,欢迎关注亿速云行业资讯频道!
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。