Java category tree structure query

Java category tree structure query

1. Initialize the database

1.1 Initialization of category table

-- -------------------------------------
-- 类目测试 Table structure for category
-- -------------------------------------
DROP TABLE IF EXISTS `category`;
CREATE TABLE `category`  (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID主键',
  `name` varchar(64) COMMENT '名称',
  `pid` bigint(20) NOT NULL COMMENT '上级id',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '类目' ROW_FORMAT = Dynamic;

select * from category;
INSERT INTO `category`.`category`(`id`, `name`, `pid`) VALUES (1, '电子产品', 0);
INSERT INTO `category`.`category`(`id`, `name`, `pid`) VALUES (2, '手机', 1);
INSERT INTO `category`.`category`(`id`, `name`, `pid`) VALUES (3, '小米手机', 2);
INSERT INTO `category`.`category`(`id`, `name`, `pid`) VALUES (4, '苹果手机', 2);
INSERT INTO `category`.`category`(`id`, `name`, `pid`) VALUES (5, '电脑', 1);
INSERT INTO `category`.`category`(`id`, `name`, `pid`) VALUES (6, '联想电脑', 5);
INSERT INTO `category`.`category`(`id`, `name`, `pid`) VALUES (7, '戴尔电脑', 5);
INSERT INTO `category`.`category`(`id`, `name`, `pid`) VALUES (8, '家用电器', 0);
INSERT INTO `category`.`category`(`id`, `name`, `pid`) VALUES (9, '空调', 8);
INSERT INTO `category`.`category`(`id`, `name`, `pid`) VALUES (10, '美的空调', 9);
INSERT INTO `category`.`category`(`id`, `name`, `pid`) VALUES (11, '格力空调', 9);

2. Build the project

2.1 Project structure

Insert picture description here

2.2 Code file

CategoryController

package com.example.category.controller;

import com.example.category.entity.Category;
import com.example.category.entity.CategoryVo;
import com.example.category.service.CategoryService;
import org.springframework.beans.BeanUtils;
import org.springframework.web.bind.annotation.*;
import com.example.category.entity.Response;

import java.util.List;
import java.util.stream.Collectors;
import java.util.stream.Stream;

import javax.annotation.Resource;

import static org.apache.ibatis.ognl.DynamicSubscript.all;

/**
 * (Category)控制层
 *
 * @author makejava
 * @since 2021-06-03 07:20:41
 */
@RestController
@RequestMapping("/category")
public class CategoryController {
    /**
     * 服务对象
     */
    @Resource
    private CategoryService categoryService;

    /**
     * 通过主键查询单条数据
     *
     * @param category 参数对象
     * @return 单条数据
     */
    @RequestMapping(value = "get", method = RequestMethod.GET)
    public Response<Category> selectOne(Category category) {
        Category result = categoryService.selectById(category.getId());
        if (result != null) {
            return Response.createSuccessResponse("查询成功", result);
        }
        return Response.createErrorResponse("查询失败");
    }

    /**
     * 新增一条数据
     *
     * @param category 实体类
     * @return Response对象
     */
    @RequestMapping(value = "insert", method = RequestMethod.POST)
    public Response<Category> insert(@RequestBody Category category) {
        int result = categoryService.insert(category);
        if (result > 0) {
            return Response.createSuccessResponse("新增成功", category);
        }
        return Response.createErrorResponse("新增失败");
    }

    /**
     * 修改一条数据
     *
     * @param category 实体类
     * @return Response对象
     */
    @RequestMapping(value = "update", method = RequestMethod.PUT)
    public Response<Category> update(@RequestBody Category category) {
        Category result = categoryService.update(category);
        if (result != null) {
            return Response.createSuccessResponse("修改成功", result);
        }
        return Response.createErrorResponse("修改失败");
    }

    /**
     * 删除一条数据
     *
     * @param category 参数对象
     * @return Response对象
     */
    @RequestMapping(value = "delete", method = RequestMethod.DELETE)
    public Response<Category> delete(Category category) {
        int result = categoryService.deleteById(category.getId());
        if (result > 0) {
            return Response.createSuccessResponse("删除成功", null);
        }
        return Response.createErrorResponse("删除失败");
    }

    /**
     * 查询全部
     *
     * @return Response对象
     */
    @RequestMapping(value = "selectAll", method = RequestMethod.GET)
    public Response<List<Category>> selectAll() {
        List<Category> categorys = categoryService.selectAll();
        if (categorys != null) {
            return Response.createSuccessResponse("查询成功", categorys);
        }
        return Response.createErrorResponse("查询失败");
    }

    /**
     * 查询树形结构
     *
     * @return Response对象
     */
    @RequestMapping(value = "selectTree", method = RequestMethod.GET)
    public Response<List<CategoryVo>> selectTree() {
        List<CategoryVo> categoryVoList = categoryService.selectTree();
        if (categoryVoList != null) {
            return Response.createSuccessResponse("查询成功", categoryVoList);
        }
        return Response.createErrorResponse("查询失败");
    }

    /**
     * 分页查询
     *
     * @param start 偏移
     * @param limit 条数
     * @return Response对象
     */
    @RequestMapping(value = "selectPage", method = RequestMethod.GET)
    public Response<List<Category>> selectPage(Integer start, Integer limit) {
        List<Category> categorys = categoryService.selectPage(start, limit);
        if (categorys != null) {
            return Response.createSuccessResponse("查询成功", categorys);
        }
        return Response.createErrorResponse("查询失败");
    }

}

Category

package com.example.category.entity;

import java.io.Serializable;

/**
 * (Category)实体类
 *
 * @author makejava
 * @since 2021-06-03 07:20:41
 */
public class Category implements Serializable {
    private static final long serialVersionUID = 404235573904070855L;
    /**
    * 主键ID
    */
    private Integer id;
    /**
    * 名称
    */
    private String name;
    /**
    * 上级id
    */
    private Integer pid;

    public Category() {
    }

    public Category(Integer id, String name, Integer pid) {
        this.id = id;
        this.name = name;
        this.pid = pid;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }
    
    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
    
    public Integer getPid() {
        return pid;
    }

    public void setPid(Integer pid) {
        this.pid = pid;
    }

    @Override
    public String toString(){
        return "Category {" +
            "id : " + id + ", " +
            "name : " + name + ", " +
            "pid : " + pid + ", " +
        '}';
    }
}

CategoryVo

package com.example.category.entity;

import java.util.List;

/**
 * @author zrj
 * @date 2021/6/3
 * @since V1.0
 **/
public class CategoryVo {

    /**
     * 主键ID
     */
    private Integer id;
    /**
     * 名称
     */
    private String name;
    /**
     * 上级id
     */
    private Integer pid;

    /**
     * 子节点
     */
    private List<CategoryVo> children;

    public CategoryVo() {
    }

    public CategoryVo(Integer id, String name, Integer pid, List<CategoryVo> children) {
        this.id = id;
        this.name = name;
        this.pid = pid;
        this.children = children;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getPid() {
        return pid;
    }

    public void setPid(Integer pid) {
        this.pid = pid;
    }

    public List<CategoryVo> getChildren() {
        return children;
    }

    public void setChildren(List<CategoryVo> children) {
        this.children = children;
    }

    @Override
    public String toString() {
        return "CategoryVo{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", pid=" + pid +
                ", children=" + children +
                '}';
    }
}

Response

package com.example.category.entity;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

/**
 * @author zrj
 * @date 2021/6/2
 * @since V1.0
 **/
@Component
public class Response<T> {

    private static ResponseCode responseCode;
    /**
     * 提示消息
     */
    private String message;

    /**
     * 具体返回的数据
     */
    private T data;

    /**
     * 状态码
     */
    private String code;

    private Response(String code, String message, T data) {
        this.message = message;
        this.code = code;
        this.data = data;
    }

    private Response(String code, String msg) {
        this.message = msg;
        this.code = code;
    }

    @Autowired
    public Response(ResponseCode responseCode) {
        Response.responseCode = responseCode;
    }

    /**
     * 返回成功Response对象
     *
     * @param successMessage 成功提示信息
     * @param data           需要返回的数据
     * @return 成功信息
     */
    public static <T> Response<T> createSuccessResponse(String successMessage, T data) {
        return new Response<>( responseCode.getSuccessCode(), successMessage, data );
    }


    /**
     * 返回错误Response对象
     *
     * @param errorMessage 错误信息
     * @return 错误信息
     */
    public static <T> Response<T> createErrorResponse(String errorMessage) {
        return new Response<>( responseCode.getErrorCode(), errorMessage );
    }

    public Response() {
    }

    /**
     * 返回未登录状态码
     *
     * @param message 提示信息
     * @return Response
     */
    public static <T> Response<T> createUnLoginResponse(String message) {
        return new Response<>( responseCode.getAuthErrorCode(), message );
    }

    public String getMessage() {
        return message;
    }

    public void setMessage(String message) {
        this.message = message;
    }

    public T getData() {
        return data;
    }

    public void setData(T data) {
        this.data = data;
    }

    public String getCode() {
        return code;
    }

    public void setCode(String code) {
        this.code = code;
    }

}

ResponseCode

package com.example.category.entity;

import org.springframework.stereotype.Component;

/**
 * @author zrj
 * @date 2021/6/2
 * @since V1.0
 **/
@Component
public class ResponseCode {
    private String successCode = "200";

    private String errorCode = "500";

    private String authErrorCode = "300";

    public String getSuccessCode() {
        return successCode;
    }

    public void setSuccessCode(String successCode) {
        this.successCode = successCode;
    }

    public String getErrorCode() {
        return errorCode;
    }

    public void setErrorCode(String errorCode) {
        this.errorCode = errorCode;
    }

    public String getAuthErrorCode() {
        return authErrorCode;
    }

    public void setAuthErrorCode(String authErrorCode) {
        this.authErrorCode = authErrorCode;
    }
}

CategoryService

package com.example.category.service;

import com.example.category.entity.Category;
import com.example.category.entity.CategoryVo;

import java.util.List;
import java.util.Map;

/**
 * (Category)表服务接口
 *
 * @author makejava
 * @since 2021-06-03 07:20:43
 */
public interface CategoryService {

    /**
     * 通过ID查询单条数据
     *
     * @param id 主键
     * @return 实例对象
     */
    Category selectById(Integer id);

    /**
     * 分页查询
     *
     * @param start 查询起始位置
     * @param limit 查询条数
     * @return 对象列表
     */
    List<Category> selectPage(int start, int limit);

    /**
     * 查询全部
     *
     * @return 对象列表
     */
    List<Category> selectAll();

    /**
     * 查询全部
     *
     * @return 对象列表
     */
    List<CategoryVo> selectTree();
    
    /**
     * 通过实体作为筛选条件查询
     *
     * @param category 实例对象
     * @return 对象列表
     */
    List<Category> selectList(Category category);

    /**
     * 新增数据
     *
     * @param category 实例对象
     * @return 影响行数
     */
    int insert(Category category);
	
	/**
     * 批量新增
     *
     * @param categorys 实例对象的集合
     * @return 影响行数
     */
	int batchInsert(List<Category> categorys);
	
    /**
     * 修改数据
     *
     * @param category 实例对象
     * @return 修改
     */
    Category update(Category category);

    /**
     * 通过主键删除数据
     *
     * @param id 主键
     * @return 影响行数
     */
    int deleteById(Integer id);
    
    /**
     * 查询总数据数
     *
     * @return 数据总数
     */
    int count();
}

CategoryServiceImpl

package com.example.category.service.impl;

import com.example.category.entity.Category;
import com.example.category.entity.CategoryVo;
import com.example.category.mapper.CategoryMapper;
import com.example.category.service.CategoryService;
import org.springframework.beans.BeanUtils;
import org.springframework.stereotype.Service;

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

/**
 * (Category表)服务实现类
 *
 * @author makejava
 * @since 2021-06-03 07:20:44
 */
@Service("categoryService")
public class CategoryServiceImpl implements CategoryService {
    @Resource
    private CategoryMapper categoryMapper;

    /**
     * 通过ID查询单条数据
     *
     * @param id 主键
     * @return 实例对象
     */
    @Override
    public Category selectById(Integer id) {
        return this.categoryMapper.selectById(id);
    }

    /**
     * 分页查询
     *
     * @param start 查询起始位置
     * @param limit 查询条数
     * @return 对象列表
     */
    @Override
    public List<Category> selectPage(int start, int limit) {
        return this.categoryMapper.selectPage(start, limit);
    }

    /**
     * 查询所有
     *
     * @return 实例对象的集合
     */
     @Override
     public List<Category> selectAll() {
        return this.categoryMapper.selectAll();
     }

    @Override
    public List<CategoryVo> selectTree() {
         // 查询全部数据
        List<Category> categoryList = categoryMapper.selectAll();

        // categoryList转换为categoryVoList
        List<CategoryVo> categoryVoList = categoryList.stream().map(category -> {
            CategoryVo categoryVo = new CategoryVo();
            BeanUtils.copyProperties(category, categoryVo);
            return categoryVo;
        }).collect(Collectors.toList());

        //获取得所有parentId为0的数据,也就是一级目录
        //用于封装数据,取得他的孩子(也就是下级目录)的数据
        List<CategoryVo> categoryVoTree = categoryVoList.stream()
                .filter(categoryVo -> categoryVo.getPid().equals(0))
                .map((categoryVoParent) -> {
                    categoryVoParent.setChildren(getChildrenData(categoryVoParent, categoryVoList));
                    return categoryVoParent;
                }).collect(Collectors.toList());

        return categoryVoTree;
    }

    /**
     * 获取孩子(下级目录)的方法,递归实现
     */
    private List<CategoryVo> getChildrenData(CategoryVo root, List<CategoryVo> all) {
        List<CategoryVo> children = all.stream().filter(categoryVo ->
                categoryVo.getPid().equals(root.getId())
        ).map(categoryVo -> {
            categoryVo.setChildren(getChildrenData(categoryVo, all));
            return categoryVo;
        }).collect(Collectors.toList());
        return children;
    }

    /**
     * 根据条件查询
     *
     * @return 实例对象的集合
     */
    @Override
    public List<Category> selectList(Category category) {
        return this.categoryMapper.selectList(category);
    }
    
    /**
     * 新增数据
     *
     * @param category 实例对象
     * @return 实例对象
     */
    @Override
    public int insert(Category category) {
        return this.categoryMapper.insert(category);
    }

    /**
     * 批量新增
     *
     * @param categorys 实例对象的集合
     * @return 生效的条数
     */
    @Override
    public int batchInsert(List<Category> categorys) {
        return this.categoryMapper.batchInsert(categorys);
    }

    /**
     * 修改数据
     *
     * @param category 实例对象
     * @return 实例对象
     */
    @Override
    public Category update(Category category) {
        this.categoryMapper.update(category);
        return this.selectById(category.getId());
    }

    /**
     * 通过主键删除数据
     *
     * @param id 主键
     * @return 是否成功
     */
    @Override
    public int deleteById(Integer id) {
        return this.categoryMapper.deleteById(id);
    }
    
    /**
     * 查询总数据数
     *
     * @return 数据总数
     */
     @Override
     public int count(){
        return this.categoryMapper.count();
     }
}

CategoryMapper

package com.example.category.mapper;

import com.example.category.entity.Category;
import org.apache.ibatis.annotations.Param;
import java.util.List;
import java.util.Map;

/**
 * (Category)表数据库访问层
 *
 * @author makejava
 * @since 2021-06-03 07:22:32
 */
public interface CategoryMapper {

    /**
     * 通过ID查询单条数据
     *
     * @param id 主键
     * @return 实例对象
     */
    Category selectById(Integer id);
	
    /**
     * 分页查询
     *
     * @param start 查询起始位置
     * @param limit 查询条数
     * @return 对象列表
     */
    List<Category> selectPage(@Param("start") int start, @Param("limit") int limit);

    /**
     * 查询全部
     *
     * @return 对象列表
     */
    List<Category> selectAll();
    
    /**
     * 通过实体作为筛选条件查询
     *
     * @param category 实例对象
     * @return 对象列表
     */
    List<Category> selectList(Category category);

    /**
     * 新增数据
     *
     * @param category 实例对象
     * @return 影响行数
     */
    int insert(Category category);
	
	/**
     * 批量新增
     *
     * @param categorys 实例对象的集合
     * @return 影响行数
     */
	int batchInsert(List<Category> categorys);
	
    /**
     * 修改数据
     *
     * @param category 实例对象
     * @return 影响行数
     */
    int update(Category category);

    /**
     * 通过主键删除数据
     *
     * @param id 主键
     * @return 影响行数
     */
    int deleteById(Integer id);

    /**
     * 查询总数据数
     *
     * @return 数据总数
     */
    int count();
}

CategoryMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.category.mapper.CategoryMapper">
    <!-- 结果集 -->
    <resultMap type="com.example.category.entity.Category" id="CategoryMap">
        <result property="id" column="id" jdbcType="INTEGER"/>
        <result property="name" column="name" jdbcType="VARCHAR"/>
        <result property="pid" column="pid" jdbcType="INTEGER"/>
    </resultMap>
    
    <!-- 基本字段 -->
    <sql id="Base_Column_List">
        id, name, pid    </sql>
    
    <!-- 查询单个 -->
    <select id="selectById" resultMap="CategoryMap">
        select
          <include refid="Base_Column_List" />
        from category
        where id = #{id}
    </select>

    <!-- 分页查询 -->
    <select id="selectPage" resultMap="CategoryMap">
        select
        <include refid="Base_Column_List" />
        from category
        limit #{start},#{limit}
    </select>

    <!-- 查询全部 -->
    <select id="selectAll" resultMap="CategoryMap">
        select
        <include refid="Base_Column_List" />
        from category
    </select>

    <!--通过实体作为筛选条件查询-->
    <select id="selectList" resultMap="CategoryMap">
        select
        <include refid="Base_Column_List" />
        from category
        <where>
                    <if test="id != null">
                and id = #{id}
            </if>
                    <if test="name != null and name != ''">
                and name = #{name}
            </if>
                    <if test="pid != null">
                and pid = #{pid}
            </if>
                </where>
    </select>

    <!-- 新增所有列 -->
    <insert id="insert" keyProperty="id" useGeneratedKeys="true">
        insert into category(id, name, pid)
        values ( #{id}, #{name}, #{pid})
    </insert>
    
    <!-- 批量新增 -->
    <insert id="batchInsert">
        insert into category(id, name, pid)
        values 
        <foreach collection="categorys" item="item" index="index" separator=",">
        (
                        #{item.id},             #{item.name},             #{item.pid}         )
         </foreach>
    </insert>

    <!-- 通过主键修改数据 -->
    <update id="update">
        update category.category
        <set>
                    <if test="name != null and name != ''">
                name = #{name},
            </if>
                    <if test="pid != null">
                pid = #{pid},
            </if>
                </set>
        where id = #{id}
    </update>

    <!--通过主键删除-->
    <delete id="deleteById">
        delete from category where id = #{id}
    </delete>
    
    <!-- 总数 -->
    <select id="count" resultType="int">
        select count(*) from category
    </select>
</mapper>

application.yml

spring:
  profiles:
    active: dev

application-dev.yml

server:
  port: 8080

spring:
  datasource:
    username: root
    password: 123456
    url: jdbc:mysql://localhost:3306/category?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC
    driver-class-name: com.mysql.jdbc.Driver

mybatis:
  mapper-locations: classpath:mapper/*Mapper.xml
  type-aliases-package: com.example.category

#showSql
logging:
  level:
    com:
      example:
        mapper : debug

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.5.0</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.example</groupId>
    <artifactId>category</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>category</name>
    <description>Demo project for Spring Boot</description>
    <properties>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.4</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <!-- https://mvnrepository.com/artifact/com.google.guava/guava -->
        <dependency>
            <groupId>com.google.guava</groupId>
            <artifactId>guava</artifactId>
            <version>30.1.1-jre</version>
        </dependency>

    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

3. Operation results

Insert picture description here


GitHub address: Query category menu tree (https://github.com/zrj-coder/category)