# 分库分表整合案例 >[info] version: jeecgboot 2.4 本文旨在:讲解分库分表用法 ShardingSphere官方文档:https://shardingsphere.apache.org/document/current/cn/overview/ [TOC] ## **准备环境** 1. 数据表:`sys_log0(日志分表1)`,`sys_log1(日志分表2)`拷贝复制系统`sys_log`表即可 2. 数据库: ` jeecg-boot2`(拷贝jeecg-boot即可,双库分表使用) 3. 示例代码在`jeecg-cloud-test-shardingsphere`中编写,该示例场景用于插入日志时对日志进行分表存放,分表规则是根据日志类型进行取余计算余数为0的存放到`sys_log0`表中,余数为1的存到`sys_log1`表中 ## **单库分表** 1. 在`jeecg-cloud-test-shardingsphere`中新建`application-sharding.yml`分表配置文件,如下所示 ``` spring: shardingsphere: props: sql-show: true datasource: #添加分库数据源 ds0: driverClassName: com.mysql.cj.jdbc.Driver url: jdbc:mysql://localhost:3306/jeecg-boot?useSSL=false&useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai username: root type: com.alibaba.druid.pool.DruidDataSource password: root names: ds0 # 规则配置 rules: sharding: # 配置绑定表,每一行为一组 binding-tables: sys_log # 分布式序列算法配置 key-generators: snowflake: type: SNOWFLAKE props: worker-id: 123 # 分片算法配置 sharding-algorithms: table-classbased: props: strategy: standard # 自定义标准分配算法 algorithmClassName: org.jeecg.modules.test.sharding.algorithm.StandardModTableShardAlgorithm type: CLASS_BASED tables: # 逻辑表名称 sys_log: #配置具体表的数据节点 actual-data-nodes: ds0.sys_log$->{0..1} # 分表策略 table-strategy: standard: # 分片算法名称 sharding-algorithm-name: table-classbased # 分片列名称(对应数据库字段) sharding-column: log_type ``` 2. 在jeecg-boot-module-system中引入jeecg-cloud-test-shardingsphere模块并启动,启动前激活shardingsphere配置文件,如下图 修改`application.yml`文件添加`sharding`用于加载`application-sharding.yml`配置 ~~~ spring: application: name: jeecg-system # 加载分库分表配置 profiles: active: @profile.nane@,sharding ~~~ ![](https://img.kancloud.cn/0b/2c/0b2c115d78d856d20e655dfc746ba814_1061x400.png) 4.启动成功后浏览器输入http://localhost:8080/jeecg-boot/ 打开接口文档如下图 ![](https://img.kancloud.cn/9d/bd/9dbdb3f2fd39d28b70b69bbb70dc1c07_1171x365.png) 如下代码批量插入10条数据,根据分配规则logType未奇数的会插入sys_log1表中,logType未偶数的会插入sys_log0表中 ![](https://img.kancloud.cn/da/fa/dafa081ad35364d533e0a1362e714f2b_996x576.png) 测试结果如下 ![](https://img.kancloud.cn/50/65/5065e2c2a88f3fd98ed51b528279eb27_976x315.png) ![](https://img.kancloud.cn/cf/9e/cf9e5bac48000e0f0f510b86a0770acd_984x264.png) ## **双库分表** 1. 在`jeecg-cloud-test-shardingsphere`中新建`application-sharding2.yml`分表配置文件并激活配置文件(激活方式同单库分表),如下所示 ~~~ # 双库分表配置 spring: shardingsphere: props: sql-show: true datasource: ds0: driverClassName: com.mysql.cj.jdbc.Driver url: jdbc:mysql://localhost:3306/jeecg-boot?useSSL=false&useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai type: com.alibaba.druid.pool.DruidDataSource username: root password: root ds1: driverClassName: com.mysql.cj.jdbc.Driver url: jdbc:mysql://localhost:3306/jeecg-boot2?useSSL=false&useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai type: com.alibaba.druid.pool.DruidDataSource username: root password: root names: ds0,ds1 # 规则配置 rules: replica-query: # 负载均衡算法 load-balancers: round-robin: type: ROUND_ROBIN props: default: 0 data-sources: prds: primary-data-source-name: ds0 replica-data-source-names: ds1 load-balancer-name: round_robin sharding: # 配置绑定表,每一行为一组,绑定表会提高查询效率 binding-tables: - sys_log # 分布式序列算法配置 key-generators: snowflake: type: SNOWFLAKE props: worker-id: 123 # 分片算法配置 sharding-algorithms: table-classbased: props: strategy: standard algorithmClassName: org.jeecg.modules.test.sharding.algorithm.StandardModTableShardAlgorithm type: CLASS_BASED # 通过operate_type取模的方式确定数据落在哪个库 database-inline: type: INLINE props: algorithm-expression: ds$->{operate_type % 2} tables: # 逻辑表名称 sys_log: #配置具体表的数据节点 actual-data-nodes: ds$->{0..1}.sys_log$->{0..1} # 分库策略 database-strategy: standard: sharding-column: operate_type sharding-algorithm-name: database-inline # 分表策略 table-strategy: standard: # 分片算法名称 sharding-algorithm-name: table-classbased # 分片列名称 sharding-column: log_type ~~~ 2.编写双库分表测试代码 ~~~ /** * 双库分表测试 * @return */ @PostMapping(value = "/test2") @AutoLog(value = "双库分表") @ApiOperation(value = "双库分表", notes = "双库分表") public Result<?> test2() { for (int i = 20; i <= 30; i++) { ShardingSysLog shardingSysLog = new ShardingSysLog(); shardingSysLog.setLogContent("双库分表测试"); shardingSysLog.setLogType(i); shardingSysLog.setOperateType(i); shardingSysLogService.save(shardingSysLog); } return Result.OK(); } ~~~ 3.测试结果如下,可以看到operate_type%2==0的进入了`jeecg-boot 库(ds0)`,operate_type%2==1的进入了`jeecg-boot2库(ds1)` ![](https://img.kancloud.cn/6b/f0/6bf0b5b7f51047d7052e7cfbd129424d_1024x261.png) ![](https://img.kancloud.cn/f0/63/f06304fde180e39af00a72541ea6ba2a_935x252.png) ## **微服务模式如何集成** 1.nacos中新建配置文件命名jeecg-sharding,内容拷贝上面单库配置或者双库配置 2.jeecg-boot-starter-cloud模块中加载分库分表配置 ![](https://img.kancloud.cn/0a/32/0a32d675c4bf01139d91a070802a7227_1470x576.png) 3.jeecg-cloud-system-start 中引入分库分表依赖,也可以引入系提供的jeecg-cloud-test-shardingsphere 测试模块 ``` <dependency> <groupId>org.jeecgframework.boot</groupId> <artifactId>jeecg-boot-starter-shardingsphere</artifactId> </dependency> ```