数据库树形结构表查询处理

---

  数据库结构。注意表字段不要为 null，要设置默认值。

  代码结构。

  SpringBoot 工程。

// 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 测试。

// 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": []
                    }
                ]
            }
        ]
    }
}

---