# 分庫分表整合案例 >[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 ~~~  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)`   ## **微服務模式如何集成** 1.nacos中新建配置文件命名jeecg-sharding，內容拷貝上面單庫配置或者雙庫配置 2.jeecg-boot-starter-cloud模塊中加載分庫分表配置  3.jeecg-cloud-system-start 中引入分庫分表依賴，也可以引入系提供的jeecg-cloud-test-shardingsphere 測試模塊 ``` <dependency> <groupId>org.jeecgframework.boot</groupId> <artifactId>jeecg-boot-starter-shardingsphere</artifactId> </dependency> ```