單體架構集成分庫分表 · JeecgBoot 開發文檔

# 單體架構模式下集成分庫分表整合案例 >[info] version: jeecgboot 3.4 本文旨在：通過jeecg-system-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即可,分庫分表使用) ### **引入依賴(下面2個依賴二選一)** 不含測試用例引入如下依賴 ~~~ <dependency> <groupId>org.jeecgframework.boot</groupId> <artifactId>jeecg-boot-starter-shardingsphere</artifactId> </dependency> ~~~ 含測試用例引入如下依賴，jeecg-cloud-test-shardingsphere默認集成測試用例便于測試，該示例場景用于插入日志時對日志進行分表存放，分表規則是根據日志類型進行取余計算余數為0的存放到`sys_log0`表中，余數為1的存到`sys_log1`表中 ~~~ <dependency> <groupId>org.jeecgframework.boot</groupId> <artifactId>jeecg-cloud-test-shardingsphere</artifactId> <version>${jeecgboot.version}</version> </dependency> ~~~ ### **編寫分片規則** 對應配置文件中 algorithmClassName: org.jeecg.sharding.algorithm.StandardModTableShardAlgorithm ~~~ package org.jeecg.sharding.algorithm; import org.apache.shardingsphere.sharding.api.sharding.standard.PreciseShardingValue; import org.apache.shardingsphere.sharding.api.sharding.standard.RangeShardingValue; import org.apache.shardingsphere.sharding.api.sharding.standard.StandardShardingAlgorithm; import java.util.Collection; import java.util.Properties; /** * 用于處理使用單一鍵 * 根據分片字段的值和sharding-count進行取模運算 * SQL 語句中有>，>=, <=，<，=，IN 和 BETWEEN AND 操作符，都可以應用此分片策略。 * * @author zyf */ public class StandardModTableShardAlgorithm implements StandardShardingAlgorithm<Integer> { private Properties props = new Properties(); /** * 用于處理=和IN的分片 * * @param collection 目標分片的集合(表名) * @param preciseShardingValue 邏輯表相關信息 * @return */ @Override public String doSharding(Collection<String> collection, PreciseShardingValue<Integer> preciseShardingValue) { for (String name : collection) { Integer value = preciseShardingValue.getValue(); //根據值進行取模，得到一個目標值 if (name.indexOf(value % 2+"") > -1) { return name; } } throw new UnsupportedOperationException(); } /** * 用于處理BETWEEN AND分片，如果不配置RangeShardingAlgorithm，SQL中的BETWEEN AND將按照全庫路由處理 * * @param collection * @param rangeShardingValue * @return */ @Override public Collection<String> doSharding(Collection<String> collection, RangeShardingValue<Integer> rangeShardingValue) { return collection; } /** * 初始化對象的時候調用的方法 */ @Override public void init() { } /** * 對應分片算法（sharding-algorithms）的類型 * * @return */ @Override public String getType() { return "STANDARD_MOD"; } @Override public Properties getProps() { return this.props; } /** * 獲取分片相關屬性 * * @param properties */ @Override public void setProps(Properties properties) { this.props = properties; } } ~~~ ### **修改配置文件** ## **單庫分表配置** 1. 在`application-dev.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.啟動成功后瀏覽器輸入http://localhost:8080打開接口文檔如下圖 ![](https://img.kancloud.cn/87/2b/872b82bf4c0817d0a77399763a686c0c_1337x603.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. 在`application-dev.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.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 ~~~ ## 啟動jeecg-system-start，控制臺有如下輸出代表分庫分表集成成功 ![](https://img.kancloud.cn/3c/37/3c373e7dad3ea5540a2071243aea1ae2_1293x101.png) 2.測試插入和查詢接口 ![](https://img.kancloud.cn/fc/a1/fca111b06bc87b3dc0947525c1aedbec_1444x993.png) 示例代碼： ![](https://img.kancloud.cn/f3/6b/f36b84129280ccfe04a78c8e84ba5ff6_1229x1073.png) 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)