SpringBoot集成RabbitMq，实现监听数据变化后更新内容 集成Spring AMQP1.可通过如下 Docker 命令 安装 RabbiMQ: docker run -it --rm --name rabbitmq -p 5672:5672 -p 15672:15672 rabbitmq:3.10-management2.登录 RabbiMQ 的 web 管理界面，创建虚拟主机novel: 3.项目中加入如下的 maven 依赖：<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-amqp</artifactId> </dependency>4.在 application.yml 配置文件中加入 RabbitMQ 的连接配置:spring: rabbitmq: addresses: "amqp://guest:guest@47.106.243.172" virtual-host: novel template: retry: # 开启重试 enabled: true # 最大重试次数 max-attempts: 3 # 第一次和第二次重试之间的持续时间 initial-interval: "3s"相关配置1.建立相关常量:/** * @author canace * @version 1.0 * @description AMQP 相关常量 * @date 2024/2/23 10:15 */ public class AmqpConsts { /** * 小说信息改变MQ */ public static class BookChangeMQ { /** * 小说信息改变交换机 */ public static final String EXCHANGE_NAME = "EXCHANGE-BOOK-CHANGE"; /** * Elasticsearch book 索引更新的队列 */ public static final String QUEUE_ES_UPDATE = "QUEUE-ES-BOOK-UPDATE"; /** * Redis book 缓存更新的队列 */ public static final String QUEUE_REDIS_UPDATE = "QUEUE-REDIS-BOOK-UPDATE"; } }2.创建 AMQP 配置类，配置各个交换机、队列以及绑定关系：/** * @author canace * @version 1.0 * @description AMPQ 配置类 * @date 2024/2/23 10:25 */ @Configuration public class AmqpConfig { /** * 小说信息改变交换机(广播模式) */ @Bean public FanoutExchange bookChangeExchange() { return new FanoutExchange(AmqpConsts.BookChangeMQ.EXCHANGE_NAME); } /** * Elasticsearch book 索引更新队列 */ @Bean public Queue esBookUpdateQueue() { return new Queue(AmqpConsts.BookChangeMQ.QUEUE_ES_UPDATE); } /** * Elasticsearch book 索引更新队列绑定到小说信息改变交换机 */ @Bean public Binding esBookUpdateQueueBinding() { return BindingBuilder.bind(esBookUpdateQueue()).to(bookChangeExchange()); } }3.配置 AMQP 消息管理类：/** * @author canace * @version 1.0 * @description AMQP 消息管理器 * @date 2024/2/23 10:32 */ @Component @RequiredArgsConstructor public class AmqpMsgManager { private final AmqpTemplate amqpTemplate; // 判断配置是否启动 RabbitMq @Value("${spring.amqp.enabled:false}") private boolean amqpEnabled; // 在小说进行信息更新或者新建时，只需调用这个接口即可实现 // 这里没有写更新书籍信息的接口，不做演示 // amqpMsgManager.sendBookChangeMsg(dto.getBookId()); public void sendBookChangeMsg(Long bookId){ if (amqpEnabled) { sendAmqpMessage(amqpTemplate, bookId); } } private void sendAmqpMessage(AmqpTemplate amqpTemplate, Object message) { // 如果在事务中则在事务执行完成后再发送，否则可以直接发送 if (TransactionSynchronizationManager.isActualTransactionActive()) { TransactionSynchronizationManager.registerSynchronization( new TransactionSynchronization() { @Override public void afterCommit() { amqpTemplate.convertAndSend(AmqpConsts.BookChangeMQ.EXCHANGE_NAME, null, message); } }); return; } // 将Java对象转为Amqp,并发送消息 amqpTemplate.convertAndSend(AmqpConsts.BookChangeMQ.EXCHANGE_NAME, null, message); } }4.配置队列监听器/** * @author canace * @version 1.0 * @description Rabbit 队列监听器 * @date 2024/2/23 10:58 */ @Component @RequiredArgsConstructor @ConditionalOnProperty(prefix = "spring", name = {"elasticsearch.enabled", "amqp.enabled"}, havingValue = "true") @Slf4j public class RabbitQueueListener { private final BookInfoMapper bookInfoMapper; private final ElasticsearchClient esClient; /** * 监听小说信息改变的 ES 更新队列，更新最新小说信息到 ES * 即如果调用了amqpMsgManager.sendBookChangeMsg(dto.getBookId());就在这里可以监听到，随后进行相关操作，这里是更新书籍信息到ES */ @RabbitListener(queues = AmqpConsts.BookChangeMQ.QUEUE_ES_UPDATE) @SneakyThrows public void updateEsBook(Long bookId) { log.info("监听到书籍信息更新"); BookInfo bookInfo = bookInfoMapper.selectById(bookId); IndexResponse response = esClient.index(i -> i .index(EsConsts.BookIndex.INDEX_NAME) .id(bookInfo.getId().toString()) .document(EsBookDto.build(bookInfo)) ); log.info("Indexed with version " + response.version()); } }注：当服务集群部署时，由于多个消费者绑定同一个队列是无法同时消费的，一个消息只能被一个消费者消费，所以刷新本地缓存的 MQ 队列命名应该使用固定名 + 唯一随机值这种动态形式。这样每次启动会生成一个新的队列，我们需要设置该队列的 autoDelete = true，让所有消费客户端连接断开时自动删除该队列

springboot3+canal+rabbitMQ实现对缓存进行清除 Canal 工作原理Canal 模拟 MySQL slave 的交互协议，伪装自己为 MySQL slave ，向 MySQL master 发送dump 协议MySQL master 收到 dump 请求，开始推送 binary log 给 slave (即 canal )Canal 解析 binary log 对象(原始为 byte 流)环境准备本项目所有的环境都是在 Docker 环境下进行了，需要自行在安装 DockerMysql安装# 拉取镜像 docker pull mysql # 创建文件并写入内容 vim /usr/local/mysql/conf/my.cnf [client] default-character-set=utf8mb4 [mysql] default-character-set=utf8mb4 [mysqld] # 设置东八区时区 default-time_zone = '+8:00' # 设置密码验证规则，default_authentication_plugin参数已被废弃 # 改为authentication_policy #default_authentication_plugin=mysql_native_password authentication_policy=mysql_native_password # 限制导入和导出的数据目录 # 为空，不限制导入到处的数据目录； # 指定目录，必须从该目录导入到处，且MySQL不会自动创建该目录； # 为NULL，禁止导入与导出功能 #secure_file_priv=/var/lib/mysql secure_file_priv= init_connect='SET collation_connection = utf8mb4_0900_ai_ci' init_connect='SET NAMES utf8mb4' character-set-server=utf8mb4 collation-server=utf8mb4_0900_ai_ci skip-character-set-client-handshake skip-name-resolve [mysqld] log-bin=mysql-bin # 开启binlog binlog-format=ROW # 选择ROW模式 server_id=1 # 配置MySQL replaction需要定义，不和Canal的slaveId重复即可 # 运行mysql容器 docker run -p 3306:3306 --name mysql --restart=always --privileged=true \ -v /usr/local/mysql/log:/var/log/mysql \ -v /usr/local/mysql/data:/var/lib/mysql \ -v /usr/local/mysql/conf:/etc/mysql \ -v /etc/localtime:/etc/localtime:ro \ -e MYSQL_ROOT_PASSWORD=123456 -d mysql:latestCanal安装这里建议使用v1.1.6版本，v1.1.7版本使用时报错# mysql创建授权账号 CREATE USER canal IDENTIFIED BY 'canal'; GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'canal'@'%'; -- GRANT ALL PRIVILEGES ON *.* TO 'canal'@'%' ; FLUSH PRIVILEGES; # 先创建一个临时容器 docker run --name canal -p 11111:11111 \ -d canal/canal-server:v1.1.6 # 复制配置文件 docker cp canal:/home/admin/canal-server/conf/canal.properties /mydata/canal/conf docker cp canal:/home/admin/canal-server/conf/example/instance.properties /mydata/canal/conf # 删除该临时容器 docker rm -f canal # 新建canal容器 docker run --name canal -p 11111:11111 \ -v /mydata/canal/conf/instance.properties:/home/admin/canal-server/conf/example/instance.properties \ -v /mydata/canal/conf/canal.properties:/home/admin/canal-server/conf/canal.properties \ --privileged=true \ --restart=always \ -d canal/canal-server:v1.1.6 # 修改/mydata/canal/conf/canal.properties canal.serverMode = rabbitMQ canal.destinations = canal.shortlink # 可自定义 rabbitmq.host = 192.168.10.107 # rabbitmq地址 rabbitmq.virtual.host = / rabbitmq.exchange = canal.exchange # 可自定义 rabbitmq.username = guest rabbitmq.password = guest rabbitmq.deliveryMode = # 修改/mydata/canal/conf/instance.properties canal.instance.mysql.slaveId=111 # 和mysql的server_id不一样即可 canal.instance.master.address= 192.168.10.107:3306 # mysql地址 canal.instance.dbUsername= canal # 数据库用户名 canal.instance.dbPassword= canal # 数据库密码 canal.instance.filter.regex=link\\..* # 白名单，这里link是表示link的数据库，其余内容可至canal官网查看配置 canal.instance.filter.black.regex=mysql\\.slave_.*,link\\.BASE.* # link\\.BASE.*得添加，否则会报错 canal.mq.topic= canal.shortlink # 和canal.destinations一致Springboot引入依赖 <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-amqp</artifactId> </dependency>新建RabbitMQ配置类package com.study.config; import lombok.extern.slf4j.Slf4j; import org.springframework.amqp.rabbit.config.SimpleRabbitListenerContainerFactory; import org.springframework.amqp.rabbit.connection.ConnectionFactory; import org.springframework.amqp.rabbit.core.RabbitTemplate; import org.springframework.amqp.support.converter.Jackson2JsonMessageConverter; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; /** * @author canace * @version 1.0 * @description rabbitmq配置类 * @date 2024/1/30 14:21 */ @Configuration @Slf4j public class RabbitConfig { @Bean public RabbitTemplate rabbitTemplate(ConnectionFactory connectionFactory) { RabbitTemplate template = new RabbitTemplate(); template.setConnectionFactory(connectionFactory); template.setMessageConverter(new Jackson2JsonMessageConverter()); return template; } /** * template.setMessageConverter(new Jackson2JsonMessageConverter()); * 这段和上面这行代码解决RabbitListener循环报错的问题 */ @Bean public SimpleRabbitListenerContainerFactory rabbitListenerContainerFactory(ConnectionFactory connectionFactory) { SimpleRabbitListenerContainerFactory factory = new SimpleRabbitListenerContainerFactory(); factory.setConnectionFactory(connectionFactory); factory.setMessageConverter(new Jackson2JsonMessageConverter()); return factory; } }新建消息实体类package com.study.config; import lombok.Data; import lombok.NoArgsConstructor; import java.util.List; /** * @author canace * @version 1.0 * @description canal消息实体类 * @date 2024/1/30 14:06 */ @NoArgsConstructor @Data public class CanalMessage<T> { private String type; private String table; private List<T> data; private String database; private Long es; private Integer id; private Boolean isDdl; private List<T> old; private List<String> pkNames; private String sql; private Long ts; }新建监听类package com.study.config; import cn.hutool.json.JSONObject; import cn.hutool.json.JSONUtil; import com.study.cacheManager.GroupCacheManager; import lombok.RequiredArgsConstructor; import lombok.extern.slf4j.Slf4j; import org.springframework.amqp.rabbit.annotation.Exchange; import org.springframework.amqp.rabbit.annotation.Queue; import org.springframework.amqp.rabbit.annotation.QueueBinding; import org.springframework.amqp.rabbit.annotation.RabbitListener; import org.springframework.messaging.handler.annotation.Payload; import org.springframework.stereotype.Component; /** * @author canace * @version 1.0 * @description Canal + RabbitMQ 监听数据库变化 * @date 2024/1/30 13:59 */ @Component @Slf4j @RequiredArgsConstructor public class CanalListener { private final GroupCacheManager groupCacheManager; @RabbitListener(bindings = { @QueueBinding( value = @Queue(value = "canal.queue", durable = "true"), exchange = @Exchange(value = "canal.exchange"), key = "canal.shortlink" ) }) public void handleDataChange(@Payload CanalMessage<?> message) { String tableName = message.getTable(); if ("t_group".equals(tableName)) { if ("INSERT".equals(message.getType())) { // 新增只需要清空用户名的分组缓存即可 String username = JSONUtil.parseObj(message.getData().get(0)).getStr("username"); log.info("新增分组，清空用户名为{}的分组缓存", username); groupCacheManager.delCacheGroupListByUsername(username); } else { // 其他情况需要清空当前gid的缓存和用户名的缓存 String username = JSONUtil.parseObj(message.getData().get(0)).getStr("username"); String gid = JSONUtil.parseObj(message.getData().get(0)).getStr("gid"); log.info("修改或删除分组，清空用户名为{}的分组缓存和gid为{}的分组缓存", username, gid); groupCacheManager.delCacheGroupByGid(gid); groupCacheManager.delCacheGroupListByUsername(username); } } } }综上即可完成canal监听后由rabbitmq通知后，再对缓存进行处理

如何查询树形结构的数据 如何查询树形结构的数据在遇到数据库保存的数据为树形结构，如多级分类列表或者评论等，此处以分类列表举例我们需要向前端返回如下部分数据[ { "id": "1-1", "name": "前端开发", "label": "前端开发", "parentid": "1", "isShow": 1, "orderby": 1, "isLeaf": 0, "childrenTreeNodes": [ { "id": "1-1-1", "name": "HTML/CSS", "label": "HTML/CSS", "parentid": "1-1", "isShow": 1, "orderby": 1, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-1-10", "name": "其它", "label": "其它", "parentid": "1-1", "isShow": 1, "orderby": 10, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-1-2", "name": "JavaScript", "label": "JavaScript", "parentid": "1-1", "isShow": 1, "orderby": 2, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-1-3", "name": "jQuery", "label": "jQuery", "parentid": "1-1", "isShow": 1, "orderby": 3, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-1-4", "name": "ExtJS", "label": "ExtJS", "parentid": "1-1", "isShow": 1, "orderby": 4, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-1-5", "name": "AngularJS", "label": "AngularJS", "parentid": "1-1", "isShow": 1, "orderby": 5, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-1-6", "name": "ReactJS", "label": "ReactJS", "parentid": "1-1", "isShow": 1, "orderby": 6, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-1-7", "name": "Bootstrap", "label": "Bootstrap", "parentid": "1-1", "isShow": 1, "orderby": 7, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-1-8", "name": "Node.js", "label": "Node.js", "parentid": "1-1", "isShow": 1, "orderby": 8, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-1-9", "name": "Vue", "label": "Vue", "parentid": "1-1", "isShow": 1, "orderby": 9, "isLeaf": 1, "childrenTreeNodes": null } ] }, { "id": "1-10", "name": "研发管理", "label": "研发管理", "parentid": "1", "isShow": 1, "orderby": 10, "isLeaf": 0, "childrenTreeNodes": [ { "id": "1-10-1", "name": "敏捷开发", "label": "敏捷开发", "parentid": "1-10", "isShow": 1, "orderby": 1, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-10-2", "name": "软件设计", "label": "软件设计", "parentid": "1-10", "isShow": 1, "orderby": 2, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-10-3", "name": "软件测试", "label": "软件测试", "parentid": "1-10", "isShow": 1, "orderby": 3, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-10-4", "name": "研发管理", "label": "研发管理", "parentid": "1-10", "isShow": 1, "orderby": 4, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-10-5", "name": "其它", "label": "其它", "parentid": "1-10", "isShow": 1, "orderby": 5, "isLeaf": 1, "childrenTreeNodes": null } ] }, { "id": "1-11", "name": "系统运维", "label": "系统运维", "parentid": "1", "isShow": 1, "orderby": 11, "isLeaf": 0, "childrenTreeNodes": [ { "id": "1-11-1", "name": "Linux", "label": "Linux", "parentid": "1-11", "isShow": 1, "orderby": 1, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-11-10", "name": "其它", "label": "其它", "parentid": "1-11", "isShow": 1, "orderby": 10, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-11-2", "name": "Windows", "label": "Windows", "parentid": "1-11", "isShow": 1, "orderby": 2, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-11-3", "name": "UNIX", "label": "UNIX", "parentid": "1-11", "isShow": 1, "orderby": 3, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-11-4", "name": "Mac OS", "label": "Mac OS", "parentid": "1-11", "isShow": 1, "orderby": 4, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-11-5", "name": "网络技术", "label": "网络技术", "parentid": "1-11", "isShow": 1, "orderby": 5, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-11-6", "name": "路由协议", "label": "路由协议", "parentid": "1-11", "isShow": 1, "orderby": 6, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-11-7", "name": "无线网络", "label": "无线网络", "parentid": "1-11", "isShow": 1, "orderby": 7, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-11-8", "name": "Ngnix", "label": "Ngnix", "parentid": "1-11", "isShow": 1, "orderby": 8, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-11-9", "name": "邮件服务器", "label": "邮件服务器", "parentid": "1-11", "isShow": 1, "orderby": 9, "isLeaf": 1, "childrenTreeNodes": null } ] }, { "id": "1-2", "name": "移动开发", "label": "移动开发", "parentid": "1", "isShow": 1, "orderby": 2, "isLeaf": 0, "childrenTreeNodes": [ { "id": "1-2-1", "name": "微信开发", "label": "微信开发", "parentid": "1-2", "isShow": 1, "orderby": 1, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-2-2", "name": "iOS", "label": "iOS", "parentid": "1-2", "isShow": 1, "orderby": 2, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-2-3", "name": "手游开发", "label": "手游开发", "parentid": "1-2", "isShow": 1, "orderby": 3, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-2-4", "name": "Swift", "label": "Swift", "parentid": "1-2", "isShow": 1, "orderby": 4, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-2-5", "name": "Android", "label": "Android", "parentid": "1-2", "isShow": 1, "orderby": 5, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-2-6", "name": "ReactNative", "label": "ReactNative", "parentid": "1-2", "isShow": 1, "orderby": 6, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-2-7", "name": "Cordova", "label": "Cordova", "parentid": "1-2", "isShow": 1, "orderby": 7, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-2-8", "name": "其它", "label": "其它", "parentid": "1-2", "isShow": 1, "orderby": 8, "isLeaf": 1, "childrenTreeNodes": null } ] }, { "id": "1-3", "name": "编程开发", "label": "编程开发", "parentid": "1", "isShow": 1, "orderby": 3, "isLeaf": 0, "childrenTreeNodes": [ { "id": "1-3-1", "name": "C/C++", "label": "C/C++", "parentid": "1-3", "isShow": 1, "orderby": 1, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-3-2", "name": "Java", "label": "Java", "parentid": "1-3", "isShow": 1, "orderby": 2, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-3-3", "name": ".NET", "label": ".NET", "parentid": "1-3", "isShow": 1, "orderby": 3, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-3-4", "name": "Objective-C", "label": "Objective-C", "parentid": "1-3", "isShow": 1, "orderby": 4, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-3-5", "name": "Go语言", "label": "Go语言", "parentid": "1-3", "isShow": 1, "orderby": 5, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-3-6", "name": "Python", "label": "Python", "parentid": "1-3", "isShow": 1, "orderby": 6, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-3-7", "name": "Ruby/Rails", "label": "Ruby/Rails", "parentid": "1-3", "isShow": 1, "orderby": 7, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-3-8", "name": "其它", "label": "其它", "parentid": "1-3", "isShow": 1, "orderby": 8, "isLeaf": 1, "childrenTreeNodes": null } ] }, { "id": "1-5", "name": "人工智能", "label": "人工智能", "parentid": "1", "isShow": 1, "orderby": 5, "isLeaf": 0, "childrenTreeNodes": [ { "id": "1-5-1", "name": "机器学习", "label": "机器学习", "parentid": "1-5", "isShow": 1, "orderby": 1, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-5-2", "name": "深度学习", "label": "深度学习", "parentid": "1-5", "isShow": 1, "orderby": 2, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-5-3", "name": "语音识别", "label": "语音识别", "parentid": "1-5", "isShow": 1, "orderby": 3, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-5-4", "name": "计算机视觉", "label": "计算机视觉", "parentid": "1-5", "isShow": 1, "orderby": 4, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-5-5", "name": "NLP", "label": "NLP", "parentid": "1-5", "isShow": 1, "orderby": 5, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-5-6", "name": "强化学习", "label": "强化学习", "parentid": "1-5", "isShow": 1, "orderby": 6, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-5-7", "name": "其它", "label": "其它", "parentid": "1-5", "isShow": 1, "orderby": 7, "isLeaf": 1, "childrenTreeNodes": null } ] }, { "id": "1-9", "name": "智能硬件/物联网", "label": "智能硬件/物联网", "parentid": "1", "isShow": 1, "orderby": 9, "isLeaf": 0, "childrenTreeNodes": [ { "id": "1-9-1", "name": "无线通信", "label": "无线通信", "parentid": "1-9", "isShow": 1, "orderby": 1, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-9-10", "name": "物联网技术", "label": "物联网技术", "parentid": "1-9", "isShow": 1, "orderby": 10, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-9-11", "name": "其它", "label": "其它", "parentid": "1-9", "isShow": 1, "orderby": 11, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-9-2", "name": "电子工程", "label": "电子工程", "parentid": "1-9", "isShow": 1, "orderby": 2, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-9-3", "name": "Arduino", "label": "Arduino", "parentid": "1-9", "isShow": 1, "orderby": 3, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-9-4", "name": "体感技术", "label": "体感技术", "parentid": "1-9", "isShow": 1, "orderby": 4, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-9-5", "name": "智能硬件", "label": "智能硬件", "parentid": "1-9", "isShow": 1, "orderby": 5, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-9-6", "name": "驱动/内核开发", "label": "驱动/内核开发", "parentid": "1-9", "isShow": 1, "orderby": 6, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-9-7", "name": "单片机/工控", "label": "单片机/工控", "parentid": "1-9", "isShow": 1, "orderby": 7, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-9-8", "name": "WinCE", "label": "WinCE", "parentid": "1-9", "isShow": 1, "orderby": 8, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-9-9", "name": "嵌入式", "label": "嵌入式", "parentid": "1-9", "isShow": 1, "orderby": 9, "isLeaf": 0, "childrenTreeNodes": [ { "id": "1-9-9-1", "name": "嵌入式基础", "label": "嵌入式基础", "parentid": "1-9-9", "isShow": 1, "orderby": 1, "isLeaf": 1, "childrenTreeNodes": null } ] } ] } ]由于返回的是一个List集合，因此需要定义一个DTO类表示分类信息的模型类@Data public class CourseCategoryTreeDto extends CourseCategory { //子节点 List<CourseCategoryTreeDto> childrenTreeNodes; }定义Controller类@Resource private CourseCategoryService categoryService; @GetMapping("/course-category/tree-nodes") public List<CourseCategoryTreeDto> queryCatTreeNodes(){ return categoryService.queryCatTreeNodes("1"); //此处1为根节点 }定义Mapperpublic interface CourseCategoryMapper extends BaseMapper<CourseCategory> { public List<CourseCategoryTreeDto> selectTreeNodes(String id); }写递归SQL（用于不确定有几级层级） <!-- SQL递归查询数据库树形结构 --> <select id="selectTreeNodes" parameterType="String" resultType="com.xuecheng.content.model.dto.CourseCategoryTreeDto"> WITH recursive t1 as( SELECT * from course_category WHERE id = #{id} UNION ALL SELECT c2.* from course_category c2 INNER JOIN t1 on t1.id = c2.parentid ) SELECT * from t1 order by t1.id, t1.orderby </select>内连接查询（确定有多少级层级） select one.id one_id, one.name one_name, one.parentid one_parentid, one.orderby one_orderby, one.label one_label, two.id two_id, two.name two_name, two.parentid two_parentid, two.orderby two_orderby, two.label two_label from course_category one inner join course_category two on one.id = two.parentid where one.parentid = 1 and one.is_show = 1 and two.is_show = 1 order by one.orderby, two.orderbyService和其实现类//Service类 public interface CourseCategoryService extends IService<CourseCategory> { List<CourseCategoryTreeDto> queryCatTreeNodes(String id); } //实现类 @Service public class CourseCategoryServiceImpl extends ServiceImpl<CourseCategoryMapper, CourseCategory> implements CourseCategoryService { @Resource CourseCategoryMapper courseCategoryMapper; /** * 根据id获取课程分类列表 * @param id 根节点id * @return 课程分类列表 */ @Override public List<CourseCategoryTreeDto> queryCatTreeNodes(String id) { //执行SQL，获取查询数据 List<CourseCategoryTreeDto> treeDtos = courseCategoryMapper.selectTreeNodes(id); //将结果转为map，便于后续找根节点，同时可以过滤当前id节点 //.filter(item -> !id.equals(item.getId()))不显示根节点 Map<String, CourseCategoryTreeDto> map = treeDtos.stream().filter(item -> !id.equals(item.getId())).collect(Collectors.toMap(CourseCategory::getId, value -> value, (key1, key2) -> key2)); List<CourseCategoryTreeDto> list = new ArrayList<>(); //遍历返回最终结果,排除根节点 treeDtos.stream().filter(item -> !id.equals(item.getId())).forEach(item ->{ //先将根节点的一级子节点放入数组 if (item.getParentid().equals(id)){ list.add(item); } //如果不为一级节点，就找到它的父节点，通过之前保存的map CourseCategoryTreeDto treeParent = map.get(item.getParentid()); //如果存在父节点就将其放入它的父节点的list中 if (treeParent != null){ //如果它的父节点的子节点列表为空，就新建一个对象存储 if (treeParent.getChildrenTreeNodes() == null){ treeParent.setChildrenTreeNodes(new ArrayList<>()); } //不为空就放入子节点中 //注意这里是需要获取子节点列表然后插入进入 treeParent.getChildrenTreeNodes().add(item); } }); return list; } } 前端显示示例

完美解决方案-雪花算法ID到前端之后精度丢失问题 一、现象是这样的下面我把异常的现象给大家描述一下，小伙伴建了一张表，表的主键是id BigINT，用来存储雪花算法生成的ID，嗯，这个没有问题！CREATE TABLE user ( id BIGINT(20) NOT NULL COMMENT '主键ID', #其他字段省略 );使用Long 类型对应数据库ID数据。嗯，也没有问题，雪花算法生成的就是一串数字，Long类型属于标准答案！@Data public class User { private Long id; //其他成员变量省略在后端下断点。看到数据响应以JSON响应给前端，正常{ id：1297873308628307970, //其他属性省略 }最后，这条数据返回给前端，前端接收到之后，修改这条数据，后端再次接收回来。奇怪的问题出现了：后端重新接收回来的id变成了： 12978733086283000000 ，不再是 1297873308628307970 二、分析问题我的第一感觉是，开发小伙伴把数据给搞混了，张冠李戴了，把XXX的对象ID放到了YYY对象的ID上。所以，就按照代码从前端到后端、从后端到前端调试跟踪了一遍。从代码的逻辑角度上没有任何问题。这时，我有点烦躁了，真的是耽误我下班了！但开工没有回头箭，既然坐下来了就得帮他解决，不然以后这队伍怎么带？想到这我又静下心来，开始思考。1297873308628300000 ---> 1297873308628307970 这两个数长得还挺像的，似乎是被四舍五入了。此时脑袋里面冒出一个想法，是精度丢失了么？哪里能导致精度丢失？服务端都是Long类型的id，不可能丢失前端是什么类型，JSON字符串转js对象，接收Long类型的是number上网查了一下Number精度是16位（雪花ID是19位的），So：JS的Number数据类型导致的精度丢失。问题是找到了！小伙伴投来敬佩的眼光，5分钟就把这问题发现了。可是发现了有什么用？得解决问题啊！三、解决问题开发小伙伴说：那我把所有的数据库表设计，id字段由Long类型改成String类型吧。我问他你有多少张表？他说100多张吧。100多张表还有100多个实体类需要改还有各种使用到实体类的Service层要改Service等改完Controller层要改关键的是String和Long都是常用类型，他还不敢批量替换小伙伴拿起电话打算订餐，说今晚的加班是无法避免了。我想了想说：你最好别改，String做ID查询性能会下降，我再想想！后端A到前端B出现精度丢失，要么改前端，要么改后端，要么…… 。“哎哎，你等等先别订餐，后端A到前端B你用的什么做的序列化？” 小伙伴告诉我说使用的是Jackson，这就好办了，Jackson我熟悉啊！解决思路：后端的ID(Long) ==> Jackson(Long转String) ==> 前端使用String类型的ID,前端使用js string精度就不会丢失了。 那前端再把String类型的19位数字传回服务端的时候，可以用Long接收么？当然可以，这是Spring反序列化参数接收默认支持的行为。最终方案就是：前端用String类型的雪花ID保持精度，后端及数据库继续使用Long(BigINT)类型不影响数据库查询执行效率。剩下的问题就是：在Spring Boot应用中，使用Jackson进行JSON序列化的时候怎么将Long类型ID转成String响应给前端。方案如下： import com.fasterxml.jackson.databind.ObjectMapper; import com.fasterxml.jackson.databind.module.SimpleModule; import com.fasterxml.jackson.databind.ser.std.ToStringSerializer; import org.springframework.boot.autoconfigure.condition.ConditionalOnMissingBean; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import org.springframework.context.annotation.Primary; import org.springframework.http.converter.json.Jackson2ObjectMapperBuilder; /** * 解决后端传给前端Long类型，前端处理后有可能失精问题 * 雪花算法19位，前端Number 16位 */ @Configuration public class JacksonConfig { @Bean @Primary @ConditionalOnMissingBean(ObjectMapper.class) public ObjectMapper jacksonObjectMapper(Jackson2ObjectMapperBuilder builder){ ObjectMapper objectMapper = builder.createXmlMapper(false).build(); // 全局配置序列化返回 JSON 处理 SimpleModule simpleModule = new SimpleModule(); //JSON Long ==> String simpleModule.addSerializer(Long.class, ToStringSerializer.instance); objectMapper.registerModule(simpleModule); return objectMapper; } }

Mybatis-plus自动代码生成工具 添加依赖 <dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus-boot-starter</artifactId> <version>3.2.0</version> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-freemarker</artifactId> </dependency> <dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus-generator</artifactId> <version>3.2.0</version> </dependency> import com.baomidou.mybatisplus.core.exceptions.MybatisPlusException; import com.baomidou.mybatisplus.core.toolkit.StringPool; import com.baomidou.mybatisplus.core.toolkit.StringUtils; import com.baomidou.mybatisplus.generator.AutoGenerator; import com.baomidou.mybatisplus.generator.InjectionConfig; import com.baomidou.mybatisplus.generator.config.*; import com.baomidou.mybatisplus.generator.config.po.TableInfo; import com.baomidou.mybatisplus.generator.config.rules.NamingStrategy; import com.baomidou.mybatisplus.generator.engine.FreemarkerTemplateEngine; import java.util.ArrayList; import java.util.List; import java.util.Scanner; /** * 代码生成器 */ // 演示例子，执行 main 方法控制台输入模块表名回车自动生成对应项目目录中 public class CodeGenerator { /** * <p> * 读取控制台内容 * </p> */ public static String scanner(String tip) { Scanner scanner = new Scanner(System.in); StringBuilder help = new StringBuilder(); help.append("请输入" + tip + "："); System.out.println(help.toString()); if (scanner.hasNext()) { String ipt = scanner.next(); if (StringUtils.isNotEmpty(ipt)) { return ipt; } } throw new MybatisPlusException("请输入正确的" + tip + "！"); } public static void main(String[] args) { // 代码生成器 AutoGenerator mpg = new AutoGenerator(); // 全局配置 GlobalConfig gc = new GlobalConfig(); String projectPath = System.getProperty("user.dir"); gc.setOutputDir(projectPath + "/src/main/java"); // gc.setOutputDir("D:\\test"); // gc.setAuthor("LXQ"); gc.setOpen(false); // gc.setSwagger2(true); 实体属性 Swagger2 注解 gc.setServiceName("%sService"); mpg.setGlobalConfig(gc); // 数据源配置 DataSourceConfig dsc = new DataSourceConfig(); dsc.setUrl("jdbc:mysql://localhost:3306/vueblog?useUnicode=true&useSSL=false&characterEncoding=utf8&serverTimezone=UTC"); // dsc.setSchemaName("public"); dsc.setDriverName("com.mysql.cj.jdbc.Driver"); dsc.setUsername("root"); dsc.setPassword("123456"); mpg.setDataSource(dsc); // 包配置 PackageConfig pc = new PackageConfig(); pc.setModuleName(null); pc.setParent("com.web.blog"); mpg.setPackageInfo(pc); // 自定义配置 InjectionConfig cfg = new InjectionConfig() { @Override public void initMap() { // to do nothing } }; // 如果模板引擎是 freemarker String templatePath = "/templates/mapper.xml.ftl"; // 如果模板引擎是 velocity // String templatePath = "/templates/mapper.xml.vm"; // 自定义输出配置 List<FileOutConfig> focList = new ArrayList<>(); // 自定义配置会被优先输出 focList.add(new FileOutConfig(templatePath) { @Override public String outputFile(TableInfo tableInfo) { // 自定义输出文件名 ， 如果你 Entity 设置了前后缀、此处注意 xml 的名称会跟着发生变化！！ return projectPath + "/src/main/resources/mapper/" + "/" + tableInfo.getEntityName() + "Mapper" + StringPool.DOT_XML; } }); cfg.setFileOutConfigList(focList); mpg.setCfg(cfg); // 配置模板 TemplateConfig templateConfig = new TemplateConfig(); templateConfig.setXml(null); mpg.setTemplate(templateConfig); // 策略配置 StrategyConfig strategy = new StrategyConfig(); strategy.setNaming(NamingStrategy.underline_to_camel); strategy.setColumnNaming(NamingStrategy.underline_to_camel); strategy.setEntityLombokModel(true); strategy.setRestControllerStyle(true); strategy.setInclude(scanner("表名，多个英文逗号分割").split(",")); strategy.setControllerMappingHyphenStyle(true); strategy.setTablePrefix("m_"); mpg.setStrategy(strategy); mpg.setTemplateEngine(new FreemarkerTemplateEngine()); mpg.execute(); } }