mybatis 父子级树形结构查询

柔情只为你懂 2024-04-07 15:32 201阅读 0赞

针对父子级数据目录查询, 以前都是逐级的去根据父级id查询子集目录, 查出后最后再在代码中拼成树形结构, 相当复杂,我们可以利用 mybatis 提供的 collection 标签自动组织树形结构, 接下来我们一步一步看该如何建立

1.建表语句

  1. CREATE TABLE `parent_child` (
  2.   `id` int(11) NOT NULL AUTO_INCREMENT,
  3.   `name` varchar(255) NOT NULL,
  4.   `parent_id` int(11) DEFAULT NULL COMMENT '父级id',
  5.   `create_time` varchar(32) NOT NULL,
  6.   PRIMARY KEY (`id`) USING BTREE,
  7.   KEY `index_parent_child_id` (`id`) USING BTREE,
  8.   KEY `index_type_name` (`name`) USING BTREE,
  9.   KEY `index_parent_id` (`parent_id`) USING BTREE
  10. ) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
  12. INSERT INTO `parent_child` VALUES ('1', '0-1', '0', '2022-09-13 11:59:13');
  13. INSERT INTO `parent_child` VALUES ('2', '0-2', '0', '2022-09-13 11:59:28');
  14. INSERT INTO `parent_child` VALUES ('3', '0-3', '0', '2022-09-13 14:01:20');
  15. INSERT INTO `parent_child` VALUES ('4', '1-1', '1', '2022-09-13 14:01:41');
  16. INSERT INTO `parent_child` VALUES ('5', '1-2', '1', '2022-09-13 14:01:58');
  17. INSERT INTO `parent_child` VALUES ('6', '1-3', '1', '2022-09-13 14:02:11');
  18. INSERT INTO `parent_child` VALUES ('7', '2-1', '2', '2022-09-13 14:02:24');
  19. INSERT INTO `parent_child` VALUES ('8', '2-2', '2', '2022-09-13 14:02:49');
  20. INSERT INTO `parent_child` VALUES ('9', '2-3', '2', '2022-09-13 14:03:03');
  21. INSERT INTO `parent_child` VALUES ('10', '3-1', '3', '2022-09-13 14:03:16');
  22. INSERT INTO `parent_child` VALUES ('11', '3-2', '3', '2022-09-13 14:03:28');
  23. INSERT INTO `parent_child` VALUES ('12', '3-3', '3', '2022-09-13 14:03:39');
  24. INSERT INTO `parent_child` VALUES ('13', '1-1-1', '4', '2022-09-13 14:04:05');
  25. INSERT INTO `parent_child` VALUES ('14', '1-1-2', '4', '2022-09-13 14:04:19');
  26. INSERT INTO `parent_child` VALUES ('15', '1-3-1', '6', '2022-09-29 14:04:34');
  27. INSERT INTO `parent_child` VALUES ('16', '1-3-2', '6', '2022-09-13 14:04:45');
  28. INSERT INTO `parent_child` VALUES ('17', '3-2-1', '11', '2022-09-13 14:05:07');
  29. INSERT INTO `parent_child` VALUES ('18', '3-2-2', '11', '2022-09-13 14:05:22');
  30. INSERT INTO `parent_child` VALUES ('19', '3-3-1', '12', '2022-09-13 14:05:37');
  31. INSERT INTO `parent_child` VALUES ('20', '3-2-1-1', '17', '2022-09-30 14:06:02');
  32. INSERT INTO `parent_child` VALUES ('21', '3-2-1-1-1', '20', '2022-09-13 14:06:19');
  33. INSERT INTO `parent_child` VALUES ('22', '3-2-1-1-2', '20', '2022-09-09 14:06:36');

2.建立返回数据结构 VO

get set方法这里省略自行补上就行

  1. public class ParentChildVo {
  4.     private Integer id;
  6.     private String name;
  8.     private Integer parentId;
  10.     private Date createTime;
  12.     private List<ParentChildVo> children;
  13. }

3.mybatis 编写查询语句

3.1 mapper文件:
  1. /**
  2.      *  查询树形目录
  3.      * @param parentId
  4.      * @return
  5.      */
  6.    List<ParentChildVo> getParentChildTree(int parentId);
3.2 mybatis文件
  1. <!-- 树形结构 -->
  2.   <resultMap id="ParentChildTreeMap" type="com.ashen.game.common.vo.ParentChildVo">
  3.     <id column="id" jdbcType="VARCHAR" property="id" />
  4.     <id column="name" jdbcType="VARCHAR" property="name" />
  5.     <id column="parentId" jdbcType="INTEGER" property="parentId" />
  6.     <id column="createTime" jdbcType="VARCHAR" property="createTime" />
  7.     <collection property="children" ofType="com.ashen.game.common.vo.ParentChildVo" column="id" select="getParentChildTree" fetchType="eager"/>
  8.   </resultMap>
  10.   <!--查询树形分组-->
  11.   <select id="getParentChildTree" resultMap="ParentChildTreeMap">
  12.     SELECT
  13.            a.id,
  14.            a.name,
  15.            a.parent_id as  parentId,
  16.            a.create_time as createTime
  17.     FROM parent_child AS a
  18.     WHERE
  19.       a.parent_id = #{parentId}
  20.   </select>
3.3 查询结果
  1. {
  3.     "data": [
  4.         {
  6.             "id": 1,
  7.             "name": "0-1",
  8.             "parentId": 0,
  9.             "createTime": "2022-09-13 11:59:13",
  10.             "children": [
  11.                 {
  13.                     "id": 4,
  14.                     "name": "1-1",
  15.                     "parentId": 1,
  16.                     "createTime": "2022-09-13 14:01:41",
  17.                     "children": [
  18.                         {
  20.                             "id": 13,
  21.                             "name": "1-1-1",
  22.                             "parentId": 4,
  23.                             "createTime": "2022-09-13 14:04:05",
  24.                             "children": []
  25.                         },
  26.                         {
  28.                             "id": 14,
  29.                             "name": "1-1-2",
  30.                             "parentId": 4,
  31.                             "createTime": "2022-09-13 14:04:19",
  32.                             "children": []
  33.                         }
  34.                     ]
  35.                 },
  36.                 {
  38.                     "id": 5,
  39.                     "name": "1-2",
  40.                     "parentId": 1,
  41.                     "createTime": "2022-09-13 14:01:58",
  42.                     "children": []
  43.                 },
  44.                 {
  46.                     "id": 6,
  47.                     "name": "1-3",
  48.                     "parentId": 1,
  49.                     "createTime": "2022-09-13 14:02:11",
  50.                     "children": [
  51.                         {
  53.                             "id": 15,
  54.                             "name": "1-3-1",
  55.                             "parentId": 6,
  56.                             "createTime": "2022-09-29 14:04:34",
  57.                             "children": []
  58.                         },
  59.                         {
  61.                             "id": 16,
  62.                             "name": "1-3-2",
  63.                             "parentId": 6,
  64.                             "createTime": "2022-09-13 14:04:45",
  65.                             "children": []
  66.                         }
  67.                     ]
  68.                 }
  69.             ]
  70.         },
  71.         {
  73.             "id": 2,
  74.             "name": "0-2",
  75.             "parentId": 0,
  76.             "createTime": "2022-09-13 11:59:28",
  77.             "children": [
  78.                 {
  80.                     "id": 7,
  81.                     "name": "2-1",
  82.                     "parentId": 2,
  83.                     "createTime": "2022-09-13 14:02:24",
  84.                     "children": []
  85.                 },
  86.                 {
  88.                     "id": 8,
  89.                     "name": "2-2",
  90.                     "parentId": 2,
  91.                     "createTime": "2022-09-13 14:02:49",
  92.                     "children": []
  93.                 },
  94.                 {
  96.                     "id": 9,
  97.                     "name": "2-3",
  98.                     "parentId": 2,
  99.                     "createTime": "2022-09-13 14:03:03",
  100.                     "children": []
  101.                 }
  102.             ]
  103.         },
  104.         {
  106.             "id": 3,
  107.             "name": "0-3",
  108.             "parentId": 0,
  109.             "createTime": "2022-09-13 14:01:20",
  110.             "children": [
  111.                 {
  113.                     "id": 10,
  114.                     "name": "3-1",
  115.                     "parentId": 3,
  116.                     "createTime": "2022-09-13 14:03:16",
  117.                     "children": []
  118.                 },
  119.                 {
  121.                     "id": 11,
  122.                     "name": "3-2",
  123.                     "parentId": 3,
  124.                     "createTime": "2022-09-13 14:03:28",
  125.                     "children": [
  126.                         {
  128.                             "id": 17,
  129.                             "name": "3-2-1",
  130.                             "parentId": 11,
  131.                             "createTime": "2022-09-13 14:05:07",
  132.                             "children": [
  133.                                 {
  135.                                     "id": 20,
  136.                                     "name": "3-2-1-1",
  137.                                     "parentId": 17,
  138.                                     "createTime": "2022-09-30 14:06:02",
  139.                                     "children": [
  140.                                         {
  142.                                             "id": 21,
  143.                                             "name": "3-2-1-1-1",
  144.                                             "parentId": 20,
  145.                                             "createTime": "2022-09-13 14:06:19",
  146.                                             "children": []
  147.                                         },
  148.                                         {
  150.                                             "id": 22,
  151.                                             "name": "3-2-1-1-2",
  152.                                             "parentId": 20,
  153.                                             "createTime": "2022-09-09 14:06:36",
  154.                                             "children": []
  155.                                         }
  156.                                     ]
  157.                                 }
  158.                             ]
  159.                         },
  160.                         {
  162.                             "id": 18,
  163.                             "name": "3-2-2",
  164.                             "parentId": 11,
  165.                             "createTime": "2022-09-13 14:05:22",
  166.                             "children": []
  167.                         }
  168.                     ]
  169.                 },
  170.                 {
  172.                     "id": 12,
  173.                     "name": "3-3",
  174.                     "parentId": 3,
  175.                     "createTime": "2022-09-13 14:03:39",
  176.                     "children": [
  177.                         {
  179.                             "id": 19,
  180.                             "name": "3-3-1",
  181.                             "parentId": 12,
  182.                             "createTime": "2022-09-13 14:05:37",
  183.                             "children": []
  184.                         }
  185.                     ]
  186.                 }
  187.             ]
  188.         }
  189.     ],
  190.     "message": "ok",
  191.     "code": 0,
  192.     "count": null,
  193.     "token": null
  194. }

4.常见报错

服务处理异常:

  1. Type definition error: [simple type, class org.apache.ibatis.executor.loader.javassist.JavassistProxyFactory$EnhancedResultObjectProxyImpl]; 
  2. nested exception is com.fasterxml.jackson.databind.exc.InvalidDefinitionException: No serializer found for class org.apache.ibatis.executor.loader.javassist.JavassistProxyFactory$EnhancedResultObjectProxyImpl and no properties discovered to create BeanSerializer (to avoid exception, disable SerializationFeature.FAIL_ON_EMPTY_BEANS) 
  3. (through reference chain: com.dataojo.docloud.model.vo.ResultMap[\"data\"]->java.util.ArrayList[0]->com.dataojo.docloud.model.vo.DirectoryGroupTreeVo_$$_jvstb9_0[\"children\"]->java.util.ArrayList[0]->com.dataojo.docloud.model.vo.DirectoryGroupTreeVo_$$_jvstb9_0[\"children\"]->java.util.ArrayList[0]->com.dataojo.docloud.model.vo.DirectoryGroupTreeVo_$$_jvstb9_0[\"handler\"])
  • 报错原因：在mabatis中sql查询时，使用了进行一对多的查询，而collection默认的为懒加载，所以导致return 给前端时报错, 加上 fetchType=“eager” 即可。

完整示例代码

发表评论

表情：
评论列表 （有 0 条评论，201人围观）

还没有评论，来说两句吧...

相关阅读

    相关 MyBatis树形结构查询

    前言 对于树形结构的数据库设计通常是基于继承关系设计的，也就是通过父ID关联来实现的，还有就是基于左右值编码设计。本文以继承关系设计的树形结构来讨论下MyBatis树形结

    港控/mmm°港控/mmm°/ 0 赞/ 499 阅读