搜索到
7
篇与
Java
的结果
-
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; } }
-
SpringBoot2.7.9+Mybatis-Plus3.5.3.1+ShardingSphere-JDBC5.3.1实现分库分表 ShardingSphere-JDBC采用Docker实现主从复制实现新增主节点配置文件mkdir -p /wolfcode/mysql/write_db/conf vi /wolfcode/mysql/write_db/conf/my.cnf #新增内容如下 [mysqld] #服务器唯一id，注意在集群中不要出现一样的 server-id=1 binlog_format=STATEMENT log-bin=master-bin log-bin-index=master-bin.index启动MySQL主节点docker run -d --name write_db -p 3306:3306 \ -v /wolfcode/mysql/write_db/conf:/etc/mysql/conf.d \ -v /wolfcode/mysql/write_db/data:/var/lib/mysql \ -e MYSQL_ROOT_PASSWORD=root \ mysql进行master容器，登录mysqldocker exec -it write_db /bin/bash mysql -uroot -proot #查看主节点状态 show master status;在mysql内部创建slave同步账号-- 创建slave用户 create user 'slave_user'@'%'; -- 设置密码 alter user 'slave_user'@'%' identified with mysql_native_password by 'root'; -- 授予复制权限 grant replication slave on *.* to 'slave_user'@'%'; -- 刷新权限 flush privileges;从节点1配置信息#退出mysql和docker exit; exit; mkdir -p /wolfcode/mysql/read_db0/conf vi /wolfcode/mysql/read_db0/conf/my.cnf #新增内容如下 [mysqld] #服务器唯一id，注意在集群中不要出现一样的 server-id=2 relay-log-index=slave-relay-bin.index relay-log=slave-replay-bin #启动从节点1 docker run -d --name read_db0 -p 3307:3306 \ -v /wolfcode/mysql/read_db0/conf:/etc/mysql/conf.d \ -v /wolfcode/mysql/read_db0/data:/var/lib/mysql \ -e MYSQL_ROOT_PASSWORD=root \ mysql #进入从节点容器，并登录mysql docker exec -it read_db0 /bin/bash mysql -uroot -proot #进入mysql执行命令 #docker inspect -f '{{.Name}} => {{.NetworkSettings.IPAddress }}' $(docker ps -aq) 查看ip change master to master_host='172.17.0.2', master_user='slave_user', master_password='root', master_port=3306, master_log_file='master-bin.000004', master_log_pos=156; #启动从节点1并查看状态 start slave; show slave status \G # Slave_IO_Running: Yes Slave_SQL_Running: Yes从节点2配置信息#退出mysql和docker exit; exit; mkdir -p /wolfcode/mysql/read_db1/conf vi /wolfcode/mysql/read_db1/conf/my.cnf #新增内容如下 [mysqld] #服务器唯一id，注意在集群中不要出现一样的 server-id=3 relay-log-index=slave-relay-bin.index relay-log=slave-replay-bin #启动从节点2 docker run -d --name read_db1 -p 3308:3306 \ -v /wolfcode/mysql/read_db1/conf:/etc/mysql/conf.d \ -v /wolfcode/mysql/read_db1/data:/var/lib/mysql \ -e MYSQL_ROOT_PASSWORD=root \ mysql #进入从节点容器，并登录mysql docker exec -it read_db1 /bin/bash mysql -uroot -proot #进入mysql执行命令 #docker inspect -f '{{.Name}} => {{.NetworkSettings.IPAddress }}' $(docker ps -aq) 查看ip #show master logs; 查看主节点log日志名 change master to master_host='172.17.0.2', master_user='slave_user', master_password='root', master_port=3306, master_log_file='master-bin.000004', master_log_pos=156; #启动从节点2并查看状态 start slave; show slave status \G # Slave_IO_Running: Yes Slave_SQL_Running: Yes创建完成后连接三个数据库，主表的变化，从表都会进行更新 --> Ip地址相同，端口不同3306，3307，3308使用ShardingSphere-JDBC（springboot框架）引入maven依赖 <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter</artifactId> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <optional>true</optional> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> <dependency> <groupId>org.apache.shardingsphere</groupId> <artifactId>shardingsphere-jdbc-core</artifactId> <version>5.3.1</version> </dependency> <dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus-boot-starter</artifactId> <version>3.5.3.1</version> </dependency> <dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus-generator</artifactId> <version>3.5.3.1</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.27</version> </dependency> <dependency> <groupId>org.freemarker</groupId> <artifactId>freemarker</artifactId> <version>2.3.28</version> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.yaml</groupId> <artifactId>snakeyaml</artifactId> <version>1.33</version> </dependency>配置#在resources目录下新建sharding.yaml #数据源配置 dataSources: write_db: dataSourceClassName: com.zaxxer.hikari.HikariDataSource driverClassName: com.mysql.jdbc.Driver jdbcUrl: jdbc:mysql://192.168.31.128:3306/db_user?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&serverTimezone=Asia/Shanghai&useSSL=false&allowPublicKeyRetrieval=true username: root password: root read_db0: dataSourceClassName: com.zaxxer.hikari.HikariDataSource driverClassName: com.mysql.jdbc.Driver jdbcUrl: jdbc:mysql://192.168.31.128:3307/db_user?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&serverTimezone=Asia/Shanghai&useSSL=false&allowPublicKeyRetrieval=true username: root password: root read_db1: dataSourceClassName: com.zaxxer.hikari.HikariDataSource driverClassName: com.mysql.jdbc.Driver jdbcUrl: jdbc:mysql://192.168.31.128:3308/db_user?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&serverTimezone=Asia/Shanghai&useSSL=false&allowPublicKeyRetrieval=true username: root password: root rules: # 读写分离 - !READWRITE_SPLITTING dataSources: readwrite_ds: staticStrategy: writeDataSourceName: write_db readDataSourceNames: - read_db0 - read_db1 loadBalancerName: random #负载均衡 loadBalancers: random: type: RANDOM #显示sql日志 props: sql-show: true #application.yml spring: datasource: driver-class-name: org.apache.shardingsphere.driver.ShardingSphereDriver url: jdbc:shardingsphere:classpath:sharding.yamltest下新建一个Generator.java,用于mybatis-plus 代码生成public class Generator { @Test void generatorDomain() { FastAutoGenerator.create("jdbc:mysql://192.168.31.128:3306/db_user?serverTimezone=UTC&useSSL=false", "root", "root") .globalConfig(builder -> { builder.author("canace") // 设置作者 // .enableSwagger(f) // 开启 swagger 模式 .fileOverride() // 覆盖已生成文件 .outputDir("F:\\JavaFile\\ShardingSphereJDBC\\src\\main\\java"); // 指定输出目录 }) .packageConfig(builder -> { builder.parent("com.study.shardingspherejdbc") // 设置父包名 .moduleName(null) // 设置父包模块名 .pathInfo(Collections.singletonMap(OutputFile.xml, "F:\\JavaFile\\ShardingSphereJDBC\\src\\main\\resources")); // 设置mapperXml生成路径 }) .strategyConfig(builder -> { builder.addInclude("sys_user") // 设置需要生成的表名 .addTablePrefix("t_", "c_"); // 设置过滤表前缀 }) .templateEngine(new FreemarkerTemplateEngine()) // 使用Freemarker引擎模板，默认的是Velocity引擎模板 .execute(); } }springboot启动类上加mybatis-plus注解@SpringBootApplication @MapperScan("com.study.shardingspherejdbc.mapper") public class ShardingSphereJdbcApplication { public static void main(String[] args) { SpringApplication.run(ShardingSphereJdbcApplication.class, args); } }test文件内测试 @Autowired private ISysUserService iSysUserService; /** * 插入数据测试，三个数据库都保存了该数据 */ @Test void testInsert() { SysUser sysUser = new SysUser(); sysUser.setLoginName("canace1"); sysUser.setUserName("admin"); sysUser.setPhoneName("12345678999"); sysUser.setPassword("123456"); sysUser.setSalt("1234"); iSysUserService.save(sysUser); } /** * 读取测试，数据从两个读数据库中随机读取 */ @Test void testRead(){ for (int i = 0; i < 5; i++) { List<SysUser> sysUsers = iSysUserService.list(); sysUsers.forEach(System.out::println); System.out.println("=========================="); } }事务情况测试 /** * 没有使用事务测试，存入写数据库write_db，从读数据中读取 */ @Test void testNoTransactional() { SysUser sysUser = new SysUser(); sysUser.setLoginName("canace2"); sysUser.setUserName("admin2"); sysUser.setPhoneName("12345678999"); sysUser.setPassword("123456"); sysUser.setSalt("1234"); iSysUserService.save(sysUser); System.out.println("=========================="); List<SysUser> sysUsers = iSysUserService.list(); sysUsers.forEach(System.out::println); } /** * 使用事务测试，该数据由于事务没有提交，没有存入到任何数据库 * 但是在读取时会从读数据库中读，并且读到该数据 * Actual SQL: write_db ::: SELECT id,login_name,user_name,phone_name,password,salt FROM sys_user */ @Test @Transactional void testTransactional() { SysUser sysUser = new SysUser(); sysUser.setLoginName("canace3"); sysUser.setUserName("admin3"); sysUser.setPhoneName("12345678999"); sysUser.setPassword("123456"); sysUser.setSalt("1234"); iSysUserService.save(sysUser); System.out.println("=========================="); List<SysUser> sysUsers = iSysUserService.list(); sysUsers.forEach(System.out::println); }负载均衡策略配置rules: # 读写分离 - !READWRITE_SPLITTING dataSources: readwrite_ds: staticStrategy: writeDataSourceName: write_db readDataSourceNames: - read_db0 - read_db1 #负载均衡算法名称 loadBalancerName: weight # loadBalancerName: random # loadBalancerName: round_robin # 负载均衡算法配置 loadBalancers: weight: type: WEIGHT #权重 props: read_db0: 1 #两个读数据库进行1:2的权重进行读取 read_db1: 2 # round_robin: # type: ROUND_ROBIN #轮转 # props: # transaction-read-query-strategy: FIXED_PRIMARY #transaction-read-query-strategy说明：所以策略都有这个，事务内读请求路由策略，可选值：FIXED_PRIMARY(路由到 primary)、FIXED_REPLICA(根据随机策略选择一个固定的 replica)、DYNAMIC_REPLICA(根据随机策略路由到不同的 replica)，默认值：FIXED_PRIMARY。 # random: # type: RANDOM #随机轮转负载均衡算法(ROUND_ROBIN)随即负载均衡算法(RANDOM)权重负载均衡算法(WEIGHT)分库分表实现创建新容器docker run -d \ -p 3309:3306 \ -v /wolfcode/mysql/customer_data0/conf:/etc/mysql/conf.d \ -v /wolfcode/mysql/customer_data0/data:/var/lib/mysql \ -e MYSQL_ROOT_PASSWORD=root \ --name customer_data0 \ mysql #进入从节点容器，并登录mysql docker exec -it customer_data0 /bin/bash mysql -uroot -proot创建数据库create database customer_data; use customer_data; CREATE TABLE `bus_statement` ( `statement_no` bigint(0) NOT NULL AUTO_INCREMENT, `customer_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL, `customer_phone` bigint(0) NULL DEFAULT NULL, `actual_arrival_time` datetime(0) NULL DEFAULT NULL, `service_type` bigint(0) NULL DEFAULT NULL, `status` int(255) UNSIGNED ZEROFILL NULL DEFAULT NULL, `total_amount` decimal(10, 0) NULL DEFAULT NULL, `create_time` datetime(0) NULL DEFAULT NULL, `info` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL, PRIMARY KEY (`statement_no`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;生成bus_satatement相关代码 @Test void generatorDomain() { FastAutoGenerator.create("jdbc:mysql://192.168.31.128:3309/customer_data?serverTimezone=UTC&useSSL=false", "root", "root") .globalConfig(builder -> { builder.author("canace") // 设置作者 // .enableSwagger(f) // 开启 swagger 模式 .fileOverride() // 覆盖已生成文件 .outputDir("F:\\JavaFile\\ShardingSphereJDBC\\src\\main\\java"); // 指定输出目录 }) .packageConfig(builder -> { builder.parent("com.study.shardingspherejdbc") // 设置父包名 .moduleName(null) // 设置父包模块名 .pathInfo(Collections.singletonMap(OutputFile.xml, "F:\\JavaFile\\ShardingSphereJDBC\\src\\main\\resources\\mapper")); // 设置mapperXml生成路径 }) .strategyConfig(builder -> { builder.addInclude("bus_statement") // 设置需要生成的表名 .addTablePrefix("t_", "c_"); // 设置过滤表前缀 }) .templateEngine(new FreemarkerTemplateEngine()) // 使用Freemarker引擎模板，默认的是Velocity引擎模板 .execute(); }新增yaml信息 customer_data0: dataSourceClassName: com.zaxxer.hikari.HikariDataSource driverClassName: com.mysql.jdbc.Driver jdbcUrl: jdbc:mysql://192.168.31.128:3309/customer_data?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&serverTimezone=Asia/Shanghai&useSSL=false&allowPublicKeyRetrieval=true username: root password: root #进行分片 - !SHARDING tables: bus_statement: #表名 actualDataNodes: customer_data0.bus_statement # 由数据源名 + 表名组成（参考 Inline 语法规则）垂直分表 /** * 测试垂直分表，两个不同的表正确插入自己的表中 */ @Test void testVerticalShardingWrite(){ SysUser sysUser = new SysUser(); sysUser.setLoginName("canace4"); sysUser.setUserName("admin4"); sysUser.setPhoneName("12345678999"); sysUser.setPassword("123456"); sysUser.setSalt("1234"); iSysUserService.save(sysUser); BusStatement busStatement = new BusStatement(); busStatement.setCustomerName("canace"); busStatement.setCustomerPhone(12345678888L); busStatement.setActualArrivalTime(new Date()); busStatement.setCreateTime(new Date()); busStatement.setServiceType(0L); busStatement.setTotalAmount(new BigDecimal(588)); busStatement.setInfo("无"); iBusStatementService.save(busStatement); }分布式序列ID现在的服务基本是分布式、微服务形式的，而且大数据量也导致分库分表的产生，对于水平分表就需要保证表中 id 的全局唯一性。对于 MySQL 而言，一个表中的主键 id 一般使用自增的方式，但是如果进行水平分表之后，多个表中会生成重复的 id 值。那么如何保证水平分表后的多张表中的 id 是全局唯一性的呢？雪花算法是其中一个用于解决分布式 id 的高效方案，也是许多互联网公司在推荐使用的。#进行分片 - !SHARDING tables: bus_statement: #表名 actualDataNodes: customer_data0.bus_statement # 由数据源名 + 表名组成（参考 Inline 语法规则） keyGenerateStrategy: #分布式序列策略 column: statement_no # 自增列名称，缺省表示不使用自增主键生成器 keyGeneratorName: snowflake # 分布式序列算法名称 雪花算法 keyGenerators: snowflake: type: SNOWFLAKE /** * 测试雪花算法 */ @Test void testSnowFlake(){ BusStatement busStatement = new BusStatement(); busStatement.setCustomerName("canace2"); busStatement.setCustomerPhone(12345678888L); busStatement.setActualArrivalTime(new Date()); busStatement.setCreateTime(new Date()); busStatement.setServiceType(0L); busStatement.setTotalAmount(new BigDecimal(588)); busStatement.setInfo("无"); iBusStatementService.save(busStatement); }水平分表复制表bus_statement为bus_statement1，之前表改为bus_statement0#进行分片 - !SHARDING tables: bus_statement: #表名 # actualDataNodes: customer_data0.bus_statement # 由数据源名 + 表名组成（参考 Inline 语法规则） actualDataNodes: customer_data0.bus_statement$->{0..1} #支持多表，即bus_statement0和bus_statement1 tableStrategy: # 分表策略，同分库策略 standard: # 用于单分片键的标准分片场景 shardingColumn: statement_no # 分表列名称 shardingAlgorithmName: t_mod # 分表算法名称 keyGenerateStrategy: #分布式序列策略 column: statement_no # 自增列名称，缺省表示不使用自增主键生成器 keyGeneratorName: snowflake # 分布式序列算法名称 雪花算法 # 分片算法配置 shardingAlgorithms: t_mod: type: MOD props: sharding-count: 2 keyGenerators: snowflake: type: SNOWFLAKE /** * 水平分表测试 */ @Test void testTableShardingInsert(){ for (int i = 0; i < 15; i++) { BusStatement busStatement = new BusStatement(); busStatement.setCustomerName("canace2"+ i); busStatement.setCustomerPhone(12345678888L); busStatement.setActualArrivalTime(new Date()); busStatement.setCreateTime(new Date()); busStatement.setServiceType(0L); busStatement.setTotalAmount(new BigDecimal(588)); busStatement.setInfo("无"); iBusStatementService.save(busStatement); } } /** * 水平分表测试数据是否都查询出来了 */ @Test void testTableShardingRead(){ List<BusStatement> list = iBusStatementService.list(); list.forEach(System.out::println); }水平分库创建新容器docker run -d \ -p 3310:3306 \ -v /wolfcode/mysql/customer_data1/conf:/etc/mysql/conf.d \ -v /wolfcode/mysql/customer_data1/data:/var/lib/mysql \ -e MYSQL_ROOT_PASSWORD=root \ --name customer_data1 \ mysql创建表create database customer_data; use customer_data; CREATE TABLE `bus_statement0` ( `statement_no` bigint(0) NOT NULL AUTO_INCREMENT, `customer_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL, `customer_phone` bigint(0) NULL DEFAULT NULL, `actual_arrival_time` datetime(0) NULL DEFAULT NULL, `service_type` bigint(0) NULL DEFAULT NULL, `status` int(255) UNSIGNED ZEROFILL NULL DEFAULT NULL, `total_amount` decimal(10, 0) NULL DEFAULT NULL, `create_time` datetime(0) NULL DEFAULT NULL, `info` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL, PRIMARY KEY (`statement_no`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic; CREATE TABLE `bus_statement1` ( `statement_no` bigint(0) NOT NULL AUTO_INCREMENT, `customer_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL, `customer_phone` bigint(0) NULL DEFAULT NULL, `actual_arrival_time` datetime(0) NULL DEFAULT NULL, `service_type` bigint(0) NULL DEFAULT NULL, `status` int(255) UNSIGNED ZEROFILL NULL DEFAULT NULL, `total_amount` decimal(10, 0) NULL DEFAULT NULL, `create_time` datetime(0) NULL DEFAULT NULL, `info` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL, PRIMARY KEY (`statement_no`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;配置 customer_data1: dataSourceClassName: com.zaxxer.hikari.HikariDataSource driverClassName: com.mysql.jdbc.Driver jdbcUrl: jdbc:mysql://192.168.31.128:3310/customer_data?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&serverTimezone=Asia/Shanghai&useSSL=false&allowPublicKeyRetrieval=true username: root password: root #进行分片 - !SHARDING tables: bus_statement: #表名 # actualDataNodes: customer_data0.bus_statement # 由数据源名 + 表名组成（参考 Inline 语法规则） actualDataNodes: customer_data$->{0..1}.bus_statement$->{0..1} #支持多表，即bus_statement0和bus_statement1 tableStrategy: # 分表策略 standard: # 用于单分表键的标准分表场景 shardingColumn: statement_no # 分表列名称 shardingAlgorithmName: t_mod # 分表算法名称 databaseStrategy: #分库策略 standard: # 用于单分片键的标准分片场景 shardingColumn: customer_phone # 分片列名称 shardingAlgorithmName: t_inline # 分片算法名称 keyGenerateStrategy: #分布式序列策略 column: statement_no # 自增列名称，缺省表示不使用自增主键生成器 keyGeneratorName: snowflake # 分布式序列算法名称 雪花算法 # 分片算法配置 shardingAlgorithms: t_mod: type: MOD props: sharding-count: 2 t_inline: type: INLINE props: algorithm-expression: customer_data$->{customer_phone % 2} keyGenerators: snowflake: type: SNOWFLAKE /** * 水平分片测试，查看是否按照手机号码将数据分片到两个数据库中 */ @Test void testDatabaseShardingInsert(){ for (int i = 0; i < 5; i++) { BusStatement busStatement = new BusStatement(); busStatement.setCustomerName("canace"+ i); busStatement.setCustomerPhone(12345678888L); busStatement.setActualArrivalTime(new Date()); busStatement.setCreateTime(new Date()); busStatement.setServiceType(0L); busStatement.setTotalAmount(new BigDecimal(588)); busStatement.setInfo("无"); iBusStatementService.save(busStatement); } for (int i = 0; i < 10; i++) { BusStatement busStatement = new BusStatement(); busStatement.setCustomerName("dddd"+ i); busStatement.setCustomerPhone(99999999999L); busStatement.setActualArrivalTime(new Date()); busStatement.setCreateTime(new Date()); busStatement.setServiceType(0L); busStatement.setTotalAmount(new BigDecimal(688)); busStatement.setInfo("无"); iBusStatementService.save(busStatement); } } /** * 查看两个库两张表数据都查出来了 */ @Test void testDatabaseShardingRead(){ List<BusStatement> list = iBusStatementService.list(); list.forEach(System.out::println); }分片算法自动分片算法取模分片算法 MOD哈希取模分片算法 HASH_MOD基于分片容量的范围分片算法 VOLUME_RANGE基于分片边界的范围分片算法 BOUNDARY_RANGE自动时间段分片算法 AUTO_INTERVAL标准分片算法行表达式分片算法 INLINE时间范围分片算法 INTERVAL基于Cosld的固定时间范围的分片算法 COSID_INTERVAL基于Cosld的雪花ID固定时间范围的分片算法 COSID_INTERVAL_SNOWFLAKE基于Cosld的取模分片算法 COSID_MOD复合分片算法 COMPLEX_INLINEHint分片算法 HINT_INLINE自定义类分片算法详见：https://shardingsphere.apache.org/document/current/cn/user-manual/common-config/builtin-algorithm/sharding
