Java数据库表设计树形结构 数据处理返回树形结构JSON报文

1、创建表和数据

CREATE TABLE `shop_tree` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `cat_id` varchar(32) DEFAULT NULL COMMENT '分类ID',
  `cat_name` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '分类名称',
  `cat_pid` varchar(32) DEFAULT NULL COMMENT '分类父ID',
  `cat_level` varchar(32) DEFAULT NULL COMMENT '分类当前层级',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb3;

/*Data for the table `shop_tree` */

insert  into `shop_tree`(`id`,`cat_id`,`cat_name`,`cat_pid`,`cat_level`) values (1,'1','家电产品','','1'),(2,'2','电视','1','2'),(3,'3','冰箱','1','2'),(4,'4','洗衣机','1','2'),(5,'5','空调','1','2'),(6,'6','数码产品','','1'),(7,'7','电脑','6','2'),(8,'8','显卡','6','2'),(9,'9','CPU','6','2'),(10,'10','内存条','6','2'),(11,'11','惠普','7','3'),(12,'12','联想','7','3'),(13,'13','华硕','8','3'),(14,'14','金士顿','10','3'),(15,'15','英特尔','9','3');

2、sql语句和代码编写

  <select id="queryIdTree" resultType="com.xyz.mop.manage.bean.pojo.ShopTree" >
        SELECT * FROM SHOP_TREE
  </select>

实体类

package com.xyz.mop.manage.bean.pojo;

import lombok.Data;

import java.util.List;

@Data
public class ShopTree {
    private long id;
    private String catId;
    private String catName;
    private String catPid;
    private String catLevel;
    private List<ShopTree> children;

}

Dao层

package com.xyz.mop.manage.mapper;


import com.xyz.mop.manage.bean.pojo.ShopTree;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;

@Mapper
public interface ArtcontentMapper {

    List<ShopTree> queryIdTree();

}

测试类

package com.xyz.mop.manage.util;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.serializer.SerializerFeature;
import com.xyz.mop.manage.bean.pojo.ShopTree;
import com.xyz.mop.manage.mapper.ArtcontentMapper;
import lombok.extern.log4j.Log4j2;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import javax.annotation.Resource;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;

/**
 * @author lianJiaYu
 * @date 2021/5/8 16:58
 */
@Log4j2
@SpringBootTest
@RunWith(SpringJUnit4ClassRunner.class)
//@ControllerAdvice
public class Test1 {

    @Resource
    ArtcontentMapper artcontentMapper;


    @Test
    public void selectTree() {
        List<ShopTree> shopTreeList = artcontentMapper.queryIdTree();
        //查出所有一级
        List<ShopTree> oneList = shopTreeList.stream().filter(shopTree -> "1".equals(shopTree.getCatLevel())).collect(Collectors.toList());
        //查出所有二级
        List<ShopTree> towList = shopTreeList.stream().filter(shopTree -> "2".equals(shopTree.getCatLevel())).collect(Collectors.toList());
        //查出所有三级
        List<ShopTree> threeList = shopTreeList.stream().filter(shopTree -> "3".equals(shopTree.getCatLevel())).collect(Collectors.toList());


        oneList.forEach(one -> {
            towList.forEach(tow -> {
                if (one.getCatId().equals(tow.getCatPid())) {
                    List<ShopTree> children = one.getChildren();
                    if (children == null) {
                        children = new ArrayList<>();
                        one.setChildren(children);
                    }
                    children.add(tow);
                }
            });
        });
        //2级放1级
        System.out.println(JSON.toJSONString(oneList, SerializerFeature.DisableCircularReferenceDetect));


        towList.forEach(tow -> {
            threeList.forEach(three -> {
                if (tow.getCatId().equals(three.getCatPid())) {
                    List<ShopTree> children = tow.getChildren();
                    if (children == null) {
                        children = new ArrayList<>();
                        tow.setChildren(children);
                    }
                    children.add(three);
                }
            });
        });

        //3级放2级
        System.out.println(JSON.toJSONString(oneList, SerializerFeature.DisableCircularReferenceDetect));
    }
}

字段定义

 

 数据展示

 cat_id和cat_pid是父子关系

 结果打印

[{
	"catId": "1",
	"catLevel": "1",
	"catName": "家电产品",
	"catPid": "",
	"children": [{
		"catId": "2",
		"catLevel": "2",
		"catName": "电视",
		"catPid": "1",
		"id": 2
	}, {
		"catId": "3",
		"catLevel": "2",
		"catName": "冰箱",
		"catPid": "1",
		"id": 3
	}, {
		"catId": "4",
		"catLevel": "2",
		"catName": "洗衣机",
		"catPid": "1",
		"id": 4
	}, {
		"catId": "5",
		"catLevel": "2",
		"catName": "空调",
		"catPid": "1",
		"id": 5
	}],
	"id": 1
}, {
	"catId": "6",
	"catLevel": "1",
	"catName": "数码产品",
	"catPid": "",
	"children": [{
		"catId": "7",
		"catLevel": "2",
		"catName": "电脑",
		"catPid": "6",
		"children": [{
			"catId": "11",
			"catLevel": "3",
			"catName": "惠普",
			"catPid": "7",
			"id": 11
		}, {
			"catId": "12",
			"catLevel": "3",
			"catName": "联想",
			"catPid": "7",
			"id": 12
		}],
		"id": 7
	}, {
		"catId": "8",
		"catLevel": "2",
		"catName": "显卡",
		"catPid": "6",
		"children": [{
			"catId": "13",
			"catLevel": "3",
			"catName": "华硕",
			"catPid": "8",
			"id": 13
		}],
		"id": 8
	}, {
		"catId": "9",
		"catLevel": "2",
		"catName": "CPU",
		"catPid": "6",
		"children": [{
			"catId": "15",
			"catLevel": "3",
			"catName": "英特尔",
			"catPid": "9",
			"id": 15
		}],
		"id": 9
	}, {
		"catId": "10",
		"catLevel": "2",
		"catName": "内存条",
		"catPid": "6",
		"children": [{
			"catId": "14",
			"catLevel": "3",
			"catName": "金士顿",
			"catPid": "10",
			"id": 14
		}],
		"id": 10
	}],
	"id": 6
}]