mybatis 父子级树形结构查询

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

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

### 1.建表语句 ###

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

### 2.建立返回数据结构 VO ###

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

public class ParentChildVo {
        
    
        private Integer id;
    
        private String name;
    
        private Integer parentId;
    
        private Date createTime;
    
        private List<ParentChildVo> children;
    }

### 3.mybatis 编写查询语句 ###

##### 3.1 mapper文件: #####

/**
         *  查询树形目录
         * @param parentId
         * @return
         */
       List<ParentChildVo> getParentChildTree(int parentId);

##### 3.2 mybatis文件 #####

<resultMap id="ParentChildTreeMap" type="com.ashen.game.common.vo.ParentChildVo">
        <id column="id" jdbcType="VARCHAR" property="id" />
        <id column="name" jdbcType="VARCHAR" property="name" />
        <id column="parentId" jdbcType="INTEGER" property="parentId" />
        <id column="createTime" jdbcType="VARCHAR" property="createTime" />
        <collection property="children" ofType="com.ashen.game.common.vo.ParentChildVo" column="id" select="getParentChildTree" fetchType="eager"/>
      </resultMap>
    
      
      <select id="getParentChildTree" resultMap="ParentChildTreeMap">
        SELECT
               a.id,
               a.name,
               a.parent_id as  parentId,
               a.create_time as createTime
        FROM parent_child AS a
        WHERE
          a.parent_id = #{parentId}
      </select>

##### 3.3 查询结果 #####

{
        
        "data": [
            {
        
                "id": 1,
                "name": "0-1",
                "parentId": 0,
                "createTime": "2022-09-13 11:59:13",
                "children": [
                    {
        
                        "id": 4,
                        "name": "1-1",
                        "parentId": 1,
                        "createTime": "2022-09-13 14:01:41",
                        "children": [
                            {
        
                                "id": 13,
                                "name": "1-1-1",
                                "parentId": 4,
                                "createTime": "2022-09-13 14:04:05",
                                "children": []
                            },
                            {
        
                                "id": 14,
                                "name": "1-1-2",
                                "parentId": 4,
                                "createTime": "2022-09-13 14:04:19",
                                "children": []
                            }
                        ]
                    },
                    {
        
                        "id": 5,
                        "name": "1-2",
                        "parentId": 1,
                        "createTime": "2022-09-13 14:01:58",
                        "children": []
                    },
                    {
        
                        "id": 6,
                        "name": "1-3",
                        "parentId": 1,
                        "createTime": "2022-09-13 14:02:11",
                        "children": [
                            {
        
                                "id": 15,
                                "name": "1-3-1",
                                "parentId": 6,
                                "createTime": "2022-09-29 14:04:34",
                                "children": []
                            },
                            {
        
                                "id": 16,
                                "name": "1-3-2",
                                "parentId": 6,
                                "createTime": "2022-09-13 14:04:45",
                                "children": []
                            }
                        ]
                    }
                ]
            },
            {
        
                "id": 2,
                "name": "0-2",
                "parentId": 0,
                "createTime": "2022-09-13 11:59:28",
                "children": [
                    {
        
                        "id": 7,
                        "name": "2-1",
                        "parentId": 2,
                        "createTime": "2022-09-13 14:02:24",
                        "children": []
                    },
                    {
        
                        "id": 8,
                        "name": "2-2",
                        "parentId": 2,
                        "createTime": "2022-09-13 14:02:49",
                        "children": []
                    },
                    {
        
                        "id": 9,
                        "name": "2-3",
                        "parentId": 2,
                        "createTime": "2022-09-13 14:03:03",
                        "children": []
                    }
                ]
            },
            {
        
                "id": 3,
                "name": "0-3",
                "parentId": 0,
                "createTime": "2022-09-13 14:01:20",
                "children": [
                    {
        
                        "id": 10,
                        "name": "3-1",
                        "parentId": 3,
                        "createTime": "2022-09-13 14:03:16",
                        "children": []
                    },
                    {
        
                        "id": 11,
                        "name": "3-2",
                        "parentId": 3,
                        "createTime": "2022-09-13 14:03:28",
                        "children": [
                            {
        
                                "id": 17,
                                "name": "3-2-1",
                                "parentId": 11,
                                "createTime": "2022-09-13 14:05:07",
                                "children": [
                                    {
        
                                        "id": 20,
                                        "name": "3-2-1-1",
                                        "parentId": 17,
                                        "createTime": "2022-09-30 14:06:02",
                                        "children": [
                                            {
        
                                                "id": 21,
                                                "name": "3-2-1-1-1",
                                                "parentId": 20,
                                                "createTime": "2022-09-13 14:06:19",
                                                "children": []
                                            },
                                            {
        
                                                "id": 22,
                                                "name": "3-2-1-1-2",
                                                "parentId": 20,
                                                "createTime": "2022-09-09 14:06:36",
                                                "children": []
                                            }
                                        ]
                                    }
                                ]
                            },
                            {
        
                                "id": 18,
                                "name": "3-2-2",
                                "parentId": 11,
                                "createTime": "2022-09-13 14:05:22",
                                "children": []
                            }
                        ]
                    },
                    {
        
                        "id": 12,
                        "name": "3-3",
                        "parentId": 3,
                        "createTime": "2022-09-13 14:03:39",
                        "children": [
                            {
        
                                "id": 19,
                                "name": "3-3-1",
                                "parentId": 12,
                                "createTime": "2022-09-13 14:05:37",
                                "children": []
                            }
                        ]
                    }
                ]
            }
        ],
        "message": "ok",
        "code": 0,
        "count": null,
        "token": null
    }

### 4.常见报错 ###

服务处理异常:

Type definition error: [simple type, class org.apache.ibatis.executor.loader.javassist.JavassistProxyFactory$EnhancedResultObjectProxyImpl]; 
    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) 
    (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” 即可。

[完整示例代码][Link 1]

[Link 1]: https://download.csdn.net/download/xyy1028/86514773