# 分库分表整合案例 >[info] version: jeecgboot 3.4 本文旨在:通过jeecg-system-cloud-start项目集成分库分表例子,进行讲解分库分表用法 ShardingSphere官方文档:https://shardingsphere.apache.org/document/current/cn/overview [TOC] ## **准备环境** 1. 数据表:`sys_log0(日志分表1)`,`sys_log1(日志分表2)`拷贝复制系统`sys_log`表即可 2. 数据库: ` jeecg-boot2`(拷贝jeecg-boot即可,分库分表使用) ### **示例代码** 示例代码在`jeecg-cloud-test-shardingsphere`中编写,该示例场景用于插入日志时对日志进行分表存放,分表规则是根据日志类型进行取余计算余数为0的存放到`sys_log0`表中,余数为1的存到`sys_log1`表中 ## **单库分表** 1. 在nacos中新建`jeecg-sharding.yaml`分表配置文件,如下所示 ~~~ spring: shardingsphere: datasource: names: ds0 ds0: driverClassName: com.mysql.cj.jdbc.Driver url: jdbc:mysql://jeecg-boot-mysql:3306/jeecg-boot?useSSL=false&useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai username: root password: root type: com.alibaba.druid.pool.DruidDataSource props: sql-show: true 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-system-cloud-start项目,引入新增的nacos配置 jeecg-sharding.yaml ~~~ - optional:nacos:jeecg-sharding.yaml ~~~ ![](https://img.kancloud.cn/b1/17/b1171cfa81ced5d269f3a8d2119758a0_1766x727.png) > 提醒:引入的配置文件名字要和nacos创建的文件保持一致,不然会报错。另外注意尽量nacos中的配置尽量不要有中文。 3、引入分库分表测试模块 jeecg-cloud-test-shardingsphere ![](https://img.kancloud.cn/aa/61/aa61c83b4e4997e0870638e6cb0e52ba_1536x884.png) 4.启动成功后浏览器输入http://localhost:9999 打开接口文档如下图 ![](https://img.kancloud.cn/90/77/9077d07f60fd20bd04c35d057aca92fd_1496x837.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. 在nacos中新建`jeecg-sharding-multi.yaml` 分库配置文件 ~~~ spring: shardingsphere: datasource: names: ds0,ds1 ds0: driverClassName: com.mysql.cj.jdbc.Driver url: jdbc:mysql://jeecg-boot-mysql: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://jeecg-boot-mysql:3306/jeecg-boot2?useSSL=false&useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai type: com.alibaba.druid.pool.DruidDataSource username: root password: root props: sql-show: true 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 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. 修改jeecg-system-cloud-start项目,引入新增的nacos配置 jeecg-sharding-multi.yaml ~~~ - optional:nacos:jeecg-sharding-multi.yaml ~~~ ![](https://img.kancloud.cn/fb/8d/fb8da16b55b354f05d13a09babe191bd_1559x826.png) 3.测试插入和查询接口 ![](https://img.kancloud.cn/fc/a1/fca111b06bc87b3dc0947525c1aedbec_1444x993.png) 示例代码: ![](https://img.kancloud.cn/f3/6b/f36b84129280ccfe04a78c8e84ba5ff6_1229x1073.png) 4.测试结果如下,可以看到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)