Java 类目树形结构查询

墨蓝 2022-10-17 13:42 176阅读 0赞

### Java 类目树形结构查询 ###

*  1. 初始化数据库
 *   *  1.1 类目表初始化
 *  2. 构建项目
 *   *  2.1 项目结构
     *  2.2 代码文件
     *   *  CategoryController
         *  Category
         *  CategoryVo
         *  Response
         *  ResponseCode
         *  CategoryService
         *  CategoryServiceImpl
         *  CategoryMapper
         *  CategoryMapper.xml
         *  application.yml
         *  application-dev.yml
         *  pom.xml
 *  3. 运行结果

# 1. 初始化数据库 #

## 1.1 类目表初始化 ##

-- -------------------------------------
    -- 类目测试 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. 构建项目 #

## 2.1 项目结构 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NTgzNjU1_size_16_color_FFFFFF_t_70]

## 2.2 代码文件 ##

### 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/> 
        </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>
            
            <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. 运行结果 #

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NTgzNjU1_size_16_color_FFFFFF_t_70 1]  
[GitHub地址：查询类目菜单树(https://github.com/zrj-coder/category)][GitHub_https_github.com_zrj-coder_category]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NTgzNjU1_size_16_color_FFFFFF_t_70]: /images/20221014/9ae5d3d22c5b45c7955216b4723742a8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NTgzNjU1_size_16_color_FFFFFF_t_70 1]: /images/20221014/b57605389cf940bbb986a3c5f77c0c1e.png
[GitHub_https_github.com_zrj-coder_category]: https://github.com/zrj-coder/category