分库分表ShardingSphere集成案例
最后更新于：2022-04-02 07:21:29
# 分库分表整合案例
[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-boot-module-system中新建application-sharding.yml分表配置文件，如下所示
```
spring:
shardingsphere:
props:
sql-show: true
datasource:
#添加分库数据源
ds0:
driverClassName: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3300/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-demo中引入shardingsphere依赖，jeecg已内置shardingsphere starter 引入即可
```
org.jeecgframework.boot
jeecg-boot-starter-shardingsphere
2.4.6
```
3. 在jeecg-boot-module-system中引入demo模块并启动，启动前激活shardingsphere配置文件，如下图
修改application.yml文件添加sharding用于加载application-sharding.yml配置
4.启动成功后浏览器输入http://localhost:8080/jeecg-boot/ 打开接口文档如下图
如下代码批量插入10条数据，根据分配规则logType未奇数的会插入sys_log1表中，logType未偶数的会插入sys_log0表中
测试结果如下
## **双库分表**
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)
';