1. 初始化数据库
1.1 类目表初始化
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 项目结构
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;
@RestController
@RequestMapping ( "/category" )
public class CategoryController {
@Resource
private CategoryService categoryService;
@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 ( "查询失败" ) ;
}
@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 ( "新增失败" ) ;
}
@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 ( "修改失败" ) ;
}
@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 ( "删除失败" ) ;
}
@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 ( "查询失败" ) ;
}
@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 ( "查询失败" ) ;
}
@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 ;
public class Category implements Serializable {
private static final long serialVersionUID = 404235573904070855L ;
private Integer id;
private String name;
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 ;
public class CategoryVo {
private Integer id;
private String name;
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 ;
@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;
}
public static < T > Response < T > createSuccessResponse ( String successMessage, T data) {
return new Response < > ( responseCode. getSuccessCode ( ) , successMessage, data ) ;
}
public static < T > Response < T > createErrorResponse ( String errorMessage) {
return new Response < > ( responseCode. getErrorCode ( ) , errorMessage ) ;
}
public 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 ;
@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 ;
public interface CategoryService {
Category selectById ( Integer id) ;
List < Category > selectPage ( int start, int limit) ;
List < Category > selectAll ( ) ;
List < CategoryVo > selectTree ( ) ;
List < Category > selectList ( Category category) ;
int insert ( Category category) ;
int batchInsert ( List < Category > categorys) ;
Category update ( Category category) ;
int deleteById ( Integer id) ;
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 ;
@Service ( "categoryService" )
public class CategoryServiceImpl implements CategoryService {
@Resource
private CategoryMapper categoryMapper;
@Override
public Category selectById ( Integer id) {
return this . categoryMapper. selectById ( id) ;
}
@Override
public List < Category > selectPage ( int start, int limit) {
return this . categoryMapper. selectPage ( start, limit) ;
}
@Override
public List < Category > selectAll ( ) {
return this . categoryMapper. selectAll ( ) ;
}
@Override
public List < CategoryVo > selectTree ( ) {
List < Category > categoryList = categoryMapper. selectAll ( ) ;
List < CategoryVo > categoryVoList = categoryList. stream ( ) . map ( category -> {
CategoryVo categoryVo = new CategoryVo ( ) ;
BeanUtils . copyProperties ( category, categoryVo) ;
return categoryVo;
} ) . collect ( Collectors . toList ( ) ) ;
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;
}
@Override
public List < Category > selectList ( Category category) {
return this . categoryMapper. selectList ( category) ;
}
@Override
public int insert ( Category category) {
return this . categoryMapper. insert ( category) ;
}
@Override
public int batchInsert ( List < Category > categorys) {
return this . categoryMapper. batchInsert ( categorys) ;
}
@Override
public Category update ( Category category) {
this . categoryMapper. update ( category) ;
return this . selectById ( category. getId ( ) ) ;
}
@Override
public int deleteById ( Integer id) {
return this . categoryMapper. deleteById ( id) ;
}
@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 ;
public interface CategoryMapper {
Category selectById ( Integer id) ;
List < Category > selectPage ( @Param ( "start" ) int start, @Param ( "limit" ) int limit) ;
List < Category > selectAll ( ) ;
List < Category > selectList ( Category category) ;
int insert ( Category category) ;
int batchInsert ( List < Category > categorys) ;
int update ( Category category) ;
int deleteById ( Integer id) ;
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
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. 运行结果
GitHub地址:查询类目菜单树(https://github.com/zrj-coder/category)