Database tree structure table query processing


Insert picture description here


Database structure. Note that the table field should not be null, and the default value should be set.

Insert picture description here

Code structure.

Insert picture description here

SpringBoot project.


// pom.xml

<dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.16</version>
        </dependency>

        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.2.0</version>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.12</version>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.56</version>
        </dependency>


// application.yml

server:
  port: 8080

spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/mydb1?characterEncoding=utf-8&serverTimezone=Asia/Shanghai&useSSL=false
    username: root
    password: root

mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl


// AssetType.java

@Data
@NoArgsConstructor
@AllArgsConstructor

@TableName("asset_type")
public class AssetType implements Serializable {

    @TableId(value = "id",type = IdType.AUTO)
    private Integer id;

    // 记录父 id
    private Integer pid;

    private String name;

    // 子类数据
    @TableField(exist = false)
    private List<AssetType> subList;

}


// Result.java

@Data
@JsonInclude(JsonInclude.Include.NON_NULL)
public class Result<T> {

    private Integer code;   //返回码
    private String message; //返回消息

    private T data;         //返回数据

    public Result() {
        this.code = 0;
        this.message = "操作成功";
    }

    public Result(Integer code, String message) {
        this.code = code;
        this.message = message;
    }

    public Result(Integer code, String message, T data) {
        this.code = code;
        this.message = message;
        this.data = data == null ? (T) Collections.emptyMap() : data;
    }
}

// AssetTypeMapper.java

public interface AssetTypeMapper extends BaseMapper<AssetType> {
}


// AssetTypeService.java

public interface AssetTypeService {

    // 查
    public AssetType selectOne(Integer id);

    // 查所有
    public List<AssetType> selectAll();

    // 增
    public int insert(AssetType assetType);

    // 改
    public int update(AssetType assetType);

    // 组装 controller 数据, 查询一个节点和多级子节点的数据
    public AssetType selectOneAndSon(Integer id);

    // 组装 controller 数据, 查询父节点和多级子节点的数据
    public AssetType selectRootAndSon();

}


// AssetTypeServiceImpl.java

@Service
public class AssetTypeServiceImpl implements AssetTypeService {

    @Autowired
    private AssetTypeMapper assetTypeMapper;

    @Override
    public AssetType selectOne(Integer id) {
        AssetType assetType = assetTypeMapper.selectById(id);
        return assetType;
    }

    @Override
    public List<AssetType> selectAll() {
        List<AssetType> assetTypes = assetTypeMapper.selectList(null);
        return assetTypes;
    }

    @Override
    @Transactional
    public int insert(AssetType assetType) {
        int i = assetTypeMapper.insert(assetType);
        return i;
    }

    @Override
    @Transactional
    public int update(AssetType assetType) {
        int i = assetTypeMapper.updateById(assetType);
        return i;
    }

    @Override
    public AssetType selectOneAndSon(Integer id) {
        List<AssetType> assetTypes = selectAll();

        for (AssetType assetType : assetTypes) {
            if (assetType.getId() == id) {
                attr(assetTypes, assetType);
                return assetType;
            }
        }
        return null;
    }

    @Override
    public AssetType selectRootAndSon() {
        List<AssetType> assetTypes = selectAll();

        for (AssetType assetType : assetTypes) {
            if (assetType.getPid() == null || assetType.getPid() == 0) {
                attr(assetTypes, assetType);
                return assetType;
            }
        }
        return null;
    }

    // 回调遍历填充数据
    void attr(List<AssetType> list, AssetType assetType) {

        List<AssetType> subList = new ArrayList<>();
        for (AssetType type : list) {
            if (type.getPid() == assetType.getId()) {
                subList.add(type);
                attr(list, type);
            }
        }
        assetType.setSubList(subList);
    }
}

Controller test.

// Demo3Application.java

@SpringBootApplication
@MapperScan("com.example.demo3.mapper")
public class Demo3Application {

    public static void main(String[] args) {
        SpringApplication.run(Demo3Application.class, args);
    }

}

// AssetTypeController.java

@RestController
@RequestMapping("/asset")
public class AssetTypeController {

    @Autowired
    private AssetTypeService assetTypeService;

    // 新增
    @PostMapping("/add")
    public Result add(@RequestBody AssetType assetType) {
        int i = assetTypeService.insert(assetType);
        return i == 1 ? new Result(0, "添加成功") : new Result(1,"添加失败");
    }

    // 查询所有
    @GetMapping("/all")
    public Result selectRootAndSon() {
        AssetType assetType = assetTypeService.selectRootAndSon();
        return new Result(0,"查询成功",assetType);
    }

    // 查一个节点和下多级子节点
    @GetMapping("/one/{id}")
    public Result selectOneAndSon(@PathVariable Integer id) {
        AssetType assetType = assetTypeService.selectOneAndSon(id);
        return new Result(0,"查询成功",assetType);
    }


}


// 遍历结果

{
    "code": 0,
    "message": "查询成功",
    "data": {
        "id": 34,
        "pid": 0,
        "name": "资产分类",
        "subList": [
            {
                "id": 35,
                "pid": 34,
                "name": "电视机",
                "subList": [
                    {
                        "id": 37,
                        "pid": 35,
                        "name": "小米电视",
                        "subList": []
                    },
                    {
                        "id": 38,
                        "pid": 35,
                        "name": "华为电视",
                        "subList": []
                    }
                ]
            },
            {
                "id": 36,
                "pid": 34,
                "name": "电冰箱",
                "subList": [
                    {
                        "id": 39,
                        "pid": 36,
                        "name": "美的冰箱",
                        "subList": []
                    }
                ]
            }
        ]
    }
}