1、目的
用于动态配置ShardingSphere-JDBC实现配置化分库分表
2、实现
2-1、导入MyBatis-Flex和ShardingSphere-JDBC的相关依赖
<dependency>
<groupId>com.mybatis-flex</groupId>
<artifactId>mybatis-flex-spring-boot-starter</artifactId>
<version>1.9.3</version>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
<version>5.1.1</version>
</dependency>
2-2、配置初始化的数据库连接用来加载配置，当然用配置中心来保存初始化数据的配置
spring.datasource.ds1.jdbc-url=jdbc:mysql://localhost/test?allowPublicKeyRetrieval=true spring.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driver spring.datasource.ds1.username=root spring.datasource.ds1.password=123456 spring.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
2-3、初始化数据源进行配置查询
初始化数据源配置类：
package com.mochenli.shardingshere.config;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import javax.sql.DataSource;
/**
* @author: MoChenLi
* @description:
* @createTime: 2024-06-27 21:23
*/
@Configuration
public class DataSourceConfig {
/**
* 根据配置文件构建数据源
* @return
*/
@Bean
@ConfigurationProperties(prefix = "spring.datasource.ds1")
public DataSource dataSourceOne(){
return DataSourceBuilder.create().build();
}
}
数据配置表以及数据
/* Navicat Premium Data Transfer Source Server : localhost Source Server Type : MySQL Source Server Version : 80034 (8.0.34) Source Host : localhost:3306 Source Schema : test Target Server Type : MySQL Target Server Version : 80034 (8.0.34) File Encoding : 65001 Date: 29/06/2024 17:52:36 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for databaseconfig -- ---------------------------- DROP TABLE IF EXISTS `databaseconfig`; CREATE TABLE `databaseconfig` ( `id` int NOT NULL AUTO_INCREMENT COMMENT '主键唯一标识', `jdbc_url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '数据库连接', `driver_class_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '数据库连接驱动', `username` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '数据库连接用户名', `password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '数据库连接密码', `Connection_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '数据库连接名称', PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of databaseconfig -- ---------------------------- INSERT INTO `databaseconfig` VALUES (1, 'jdbc:mysql://192.168.157.129:3310/db_order?allowPublicKeyRetrieval=true', 'com.mysql.cj.jdbc.Driver', 'root', '123456', 'server-order0'); INSERT INTO `databaseconfig` VALUES (2, 'jdbc:mysql://192.168.157.129:3311/db_order?allowPublicKeyRetrieval=true', 'com.mysql.cj.jdbc.Driver', 'root', '123456', 'server-order1'); SET FOREIGN_KEY_CHECKS = 1;
数据库配置对象类
package com.mochenli.shardingshere.entity;
import com.mybatisflex.annotation.Id;
import com.mybatisflex.annotation.KeyType;
import com.mybatisflex.annotation.Table;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
/**
* @author: MoChenLi
* @description:
* @createTime: 2024-06-29 17:28
*/
@Data
@NoArgsConstructor
@AllArgsConstructor
@Table("DataBaseConfig")
public class DataBaseConfig {
@Id(keyType = KeyType.Auto)
private Integer id;
private String jdbcUrl;
private String username;
private String password;
private String driverClassName;
private String connectionName;
}
mapper类
package com.mochenli.shardingshere.mapper;
import com.mochenli.shardingshere.entity.DataBaseConfig;
import com.mybatisflex.core.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
/**
* @author: MoChenLi
* @description:
* @createTime: 2024-06-29 17:31
*/
@Mapper
public interface DataBaseConfigMapper extends BaseMapper<DataBaseConfig> {
}
2-4、初始化ShardingDataSource数据源实现分库分表
分片算法可查看链接
package com.mochenli.shardingshere.config;
import com.mochenli.shardingshere.entity.DataBaseConfig;
import com.mochenli.shardingshere.mapper.DataBaseConfigMapper;
import com.mybatisflex.core.FlexGlobalConfig;
import com.mybatisflex.core.datasource.FlexDataSource;
import com.zaxxer.hikari.HikariDataSource;
import org.apache.shardingsphere.driver.api.ShardingSphereDataSourceFactory;
import org.apache.shardingsphere.infra.config.algorithm.ShardingSphereAlgorithmConfiguration;
import org.apache.shardingsphere.infra.config.mode.ModeConfiguration;
import org.apache.shardingsphere.sharding.api.config.ShardingRuleConfiguration;
import org.apache.shardingsphere.sharding.api.config.rule.ShardingTableRuleConfiguration;
import org.apache.shardingsphere.sharding.api.config.strategy.keygen.KeyGenerateStrategyConfiguration;
import org.apache.shardingsphere.sharding.api.config.strategy.sharding.ShardingStrategyConfiguration;
import org.apache.shardingsphere.sharding.api.config.strategy.sharding.StandardShardingStrategyConfiguration;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.*;
/**
* @author: MoChenLi
* @description:
* @createTime: 2024-06-19 13:53
*/
@Configuration
public class ShardingConfig {
private DataBaseConfigMapper dataBaseConfigMapper;
public ShardingConfig(DataBaseConfigMapper dataBaseConfigMapper) throws SQLException {
this.dataBaseConfigMapper = dataBaseConfigMapper;
FlexDataSource flexDataSource = FlexGlobalConfig.getDefaultConfig()
.getDataSource();
flexDataSource.addDataSource("getShardingDataSource",getShardingDataSource());
}
/**
* 配置shardingsphere的内存模式
* @return
*/
@Bean
public ModeConfiguration getModeConfiguration() {
ModeConfiguration modeConfiguration = new ModeConfiguration("Memory", null, false);
return modeConfiguration;
}
/**
* 构建ShardingDataSource数据源
* @return
* @throws SQLException
*/
public DataSource getShardingDataSource() throws SQLException {
//查询数据库的数据连接配置设置到dataSourceMap当中
Map<String, DataSource> dataSourceMap = new HashMap<>();
List<DataBaseConfig> dataBaseConfigs = dataBaseConfigMapper.selectAll();
for (DataBaseConfig dataBaseConfig : dataBaseConfigs) {
DataSource dataSource = createDataSource(dataBaseConfig);
dataSourceMap.put(dataBaseConfig.getConnectionName(), dataSource);
}
//以下分片配置的规则也可持久化从数据库查询出来进行配置 此处用于演示所需即静态配置
// 配置分片规则
ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
//算法相关配置
Map<String, ShardingSphereAlgorithmConfiguration> shardingSphereAlgorithmConfigurations = new HashMap<>();
Properties properties = new Properties();
properties.put("algorithm-expression","server-order$->{user_id % 2}");
ShardingSphereAlgorithmConfiguration shardingSphereAlgorithmConfiguration = new ShardingSphereAlgorithmConfiguration("INLINE",properties);
Properties properties1 = new Properties();
properties1.put("sharding-count","2");
ShardingSphereAlgorithmConfiguration shardingSphereAlgorithmConfiguration1 = new ShardingSphereAlgorithmConfiguration("MOD",properties1);
Properties properties2 = new Properties();
properties2.put("sharding-count","2");
ShardingSphereAlgorithmConfiguration shardingSphereAlgorithmConfiguration2 = new ShardingSphereAlgorithmConfiguration("HASH_MOD",properties2);
shardingSphereAlgorithmConfigurations.put("alg_inline_userid",shardingSphereAlgorithmConfiguration);
shardingSphereAlgorithmConfigurations.put("alg_mod",shardingSphereAlgorithmConfiguration1);
shardingSphereAlgorithmConfigurations.put("alg_hash_mod",shardingSphereAlgorithmConfiguration2);
shardingRuleConfig.setShardingAlgorithms(shardingSphereAlgorithmConfigurations);
//分库分表策略配置
Collection<ShardingTableRuleConfiguration> shardingTableRuleConfigurations = new ArrayList<>();
//分库策略
ShardingTableRuleConfiguration shardingTableRuleConfiguration = new ShardingTableRuleConfiguration("t_order","server-order$->{0..1}.t_order$->{0..1}");
ShardingStrategyConfiguration shardingStrategyConfiguration = new StandardShardingStrategyConfiguration("user_id","alg_mod");
shardingTableRuleConfiguration.setDatabaseShardingStrategy(shardingStrategyConfiguration);
//分表策略
ShardingStrategyConfiguration shardingStrategyConfigurationtable = new StandardShardingStrategyConfiguration("order_no","alg_hash_mod");
shardingTableRuleConfiguration.setTableShardingStrategy(shardingStrategyConfigurationtable);
shardingTableRuleConfigurations.add(shardingTableRuleConfiguration);
shardingRuleConfig.setTables(shardingTableRuleConfigurations);
//配置主键算法生成策略
Map<String, ShardingSphereAlgorithmConfiguration> keyGenerators = new HashMap<>();
ShardingSphereAlgorithmConfiguration shardingSphereAlgorithmConfigurationsk = new ShardingSphereAlgorithmConfiguration("SNOWFLAKE",null);
keyGenerators.put("alg_snowflake",shardingSphereAlgorithmConfigurationsk);
shardingRuleConfig.setKeyGenerators(keyGenerators);
//分布式id
KeyGenerateStrategyConfiguration keyGenerateStrategyConfiguration =new KeyGenerateStrategyConfiguration("id","alg_snowflake");
shardingTableRuleConfiguration.setKeyGenerateStrategy(keyGenerateStrategyConfiguration);
//属性设置运行sql打印显示
Properties properties3 = new Properties();
properties3.put("sql-show",true);
// 创建ShardingDataSource
DataSource dataSources = ShardingSphereDataSourceFactory.createDataSource(dataSourceMap, Collections.singleton(shardingRuleConfig),properties3 );
return dataSources;
}
/**
* 创建数据源连接
* @param dataBaseConfig
* @return
*/
public static DataSource createDataSource(DataBaseConfig dataBaseConfig) {
// 创建数据源，这里需要根据实际情况创建，例如使用HikariCP、Druid等连接池
HikariDataSource dataSource = new HikariDataSource();
dataSource.setDriverClassName(dataBaseConfig.getDriverClassName());
dataSource.setJdbcUrl(dataBaseConfig.getJdbcUrl());
dataSource.setUsername(dataBaseConfig.getUsername());
dataSource.setPassword(dataBaseConfig.getPassword());
//不使用连接池
//DriverManagerDataSource dataSource1 = new DriverManagerDataSource();
//dataSource1.setDriverClassName(dataBaseConfig.getDriverClassName());
//dataSource1.setUrl(dataBaseConfig.getJdbcUrl());
//dataSource1.setUsername(dataBaseConfig.getUsername());
//dataSource1.setPassword(dataBaseConfig.getPassword());
return dataSource;
}
}
2-5、两个数据库连接server-order0和server-order1的表结构如下：分别在两个库当中运行
SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for t_order0 -- ---------------------------- DROP TABLE IF EXISTS `t_order0`; CREATE TABLE `t_order0` ( `id` bigint NOT NULL, `order_no` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL, `user_id` bigint NULL DEFAULT NULL, `amount` decimal(10, 2) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Table structure for t_order1 -- ---------------------------- DROP TABLE IF EXISTS `t_order1`; CREATE TABLE `t_order1` ( `id` bigint NOT NULL, `order_no` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL, `user_id` bigint NULL DEFAULT NULL, `amount` decimal(10, 2) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic; SET FOREIGN_KEY_CHECKS = 1;
2-6、实体这个@Table(“t_order”)是逻辑表名ShardingConfig分库策略时设置的
package com.mochenli.shardingshere.entity;
import com.mybatisflex.annotation.Id;
import com.mybatisflex.annotation.KeyType;
import com.mybatisflex.annotation.Table;
import lombok.Data;
import java.math.BigDecimal;
/**
* @author: MoChenLi
* @description:
* @createTime: 2024-06-18 17:15
*/
@Table("t_order")
@Data
public class Order {
@Id(keyType = KeyType.Auto)
private Long id;
private String orderNo;
private Long userId;
private BigDecimal amount;
}
2-7、mapper
package com.mochenli.shardingshere.mapper;
import com.mochenli.shardingshere.entity.Order;
import com.mybatisflex.core.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
/**
* @author: MoChenLi
* @description:
* @createTime: 2024-06-18 17:18
*/
@Mapper
public interface OrderMapper extends BaseMapper<Order> {
}
3、测试
3-1、测试分库分表的新增
package com.mochenli.shardingshere.controller;
import com.mochenli.shardingshere.entity.Order;
import com.mochenli.shardingshere.mapper.OrderMapper;
import com.mybatisflex.core.datasource.DataSourceKey;
import lombok.AllArgsConstructor;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;
/**
* @author: MoChenLi
* @description:
* @createTime: 2024-06-29 18:11
*/
@RestController
@AllArgsConstructor
public class TestController {
private final OrderMapper orderMapper;
/**
* id是用分布式id雪花算法 所以不填
* 测试新增 因为前面配置是根据user_id进行分库 分库算法是:取模分片，算法类型：MOD 分片数量是 2
* 分表算法是根据 order_no 进行分表 分表算法是 哈希取模分片算法，类型：HASH_MOD 分片数量是 2
*/
@GetMapping("/testAddOrder")
public String addTOrder(){
//切换数据源 使用ShardingDataSource数据源
DataSourceKey.use("getShardingDataSource");
//进行分库分表插入
for (int i = 1; i <=60; i++) {
Order order = new Order();
order.setUserId(Long.valueOf(i));
order.setOrderNo("分表算法"+i);
orderMapper.insert(order);
}
return "success";
}
}
结果如下所示：
server_order0.t_order0
server_order0.t_order1
server_order1.t_order0
server_order1.t_order1
3-2、测试分页查询：
package com.mochenli.shardingshere.controller;
import com.mochenli.shardingshere.entity.Order;
import com.mochenli.shardingshere.mapper.OrderMapper;
import com.mybatisflex.core.datasource.DataSourceKey;
import com.mybatisflex.core.paginate.Page;
import com.mybatisflex.core.query.QueryWrapper;
import lombok.AllArgsConstructor;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;
/**
* @author: MoChenLi
* @description:
* @createTime: 2024-06-29 18:11
*/
@RestController
@AllArgsConstructor
public class TestController {
private final OrderMapper orderMapper;
/**
* 测试分页查询
* @return
*/
@GetMapping("/testPageOrder")
public Page<Order> getPage(){
//切换数据源 使用ShardingDataSource数据源
DataSourceKey.use("getShardingDataSource");
//进行分页查询
Page<Order> page = new Page<>();
page.setPageNumber(1);
page.setPageSize(27);
Page<Order> paginate = orderMapper.paginate(page, new QueryWrapper());
return paginate;
}
结果如下:
3-3、测试事务问题
1、正常情况
package com.mochenli.shardingshere.controller;
import com.mochenli.shardingshere.entity.DataBaseConfig;
import com.mochenli.shardingshere.entity.Order;
import com.mochenli.shardingshere.mapper.DataBaseConfigMapper;
import com.mochenli.shardingshere.mapper.OrderMapper;
import com.mybatisflex.core.datasource.DataSourceKey;
import com.mybatisflex.core.paginate.Page;
import com.mybatisflex.core.query.QueryWrapper;
import lombok.AllArgsConstructor;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* @author: MoChenLi
* @description:
* @createTime: 2024-06-29 18:11
*/
@RestController
@AllArgsConstructor
public class TestController {
private final OrderMapper orderMapper;
private final DataBaseConfigMapper dataBaseConfigMapper;
/**
* 测试事务问题
* 正常情况
*/
@GetMapping("/testTransaction")
public Map<String,Object> testTransaction(){
Map<String,Object> map = new HashMap<>();
List<DataBaseConfig> dataBaseConfigs = dataBaseConfigMapper.selectAll();
map.put("dataBaseConfigs",dataBaseConfigs);
DataSourceKey.clear();//清除数据源
//切换数据源 使用ShardingDataSource数据源
DataSourceKey.use("getShardingDataSource");
List<Order> orders = orderMapper.selectAll();
map.put("orders",orders);
return map;
}
}
结果：
2、出错进行事务回滚情况一
package com.mochenli.shardingshere.controller;
import com.mochenli.shardingshere.entity.DataBaseConfig;
import com.mochenli.shardingshere.entity.Order;
import com.mochenli.shardingshere.mapper.DataBaseConfigMapper;
import com.mochenli.shardingshere.mapper.OrderMapper;
import com.mybatisflex.core.datasource.DataSourceKey;
import com.mybatisflex.core.paginate.Page;
import com.mybatisflex.core.query.QueryWrapper;
import lombok.AllArgsConstructor;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* @author: MoChenLi
* @description:
* @createTime: 2024-06-29 18:11
*/
@RestController
@AllArgsConstructor
public class TestController {
private final OrderMapper orderMapper;
private final DataBaseConfigMapper dataBaseConfigMapper;
/**
* 测试事务问题
* 报错情况一 不分库的数据源报错 回滚
*/
@GetMapping("/testTransactionError")
@Transactional
public void testTransactionError() {
DataBaseConfig dataBaseConfig = new DataBaseConfig();
dataBaseConfig.setConnectionName("连接名称");
dataBaseConfig.setJdbcUrl("连接字符串");
dataBaseConfig.setUsername("用户名");
dataBaseConfig.setPassword("密码");
dataBaseConfig.setDriverClassName("驱动");
//进行新增
dataBaseConfigMapper.insert(dataBaseConfig);
//模拟报错
int i = 10 / 0;
DataSourceKey.clear();//清除数据源
//切换数据源 使用ShardingDataSource数据源
DataSourceKey.use("getShardingDataSource");
List<Order> orders = orderMapper.selectAll();
}
}
3、出错进行事务回滚情况二
package com.mochenli.shardingshere.controller;
import com.mochenli.shardingshere.entity.DataBaseConfig;
import com.mochenli.shardingshere.entity.Order;
import com.mochenli.shardingshere.mapper.DataBaseConfigMapper;
import com.mochenli.shardingshere.mapper.OrderMapper;
import com.mybatisflex.core.datasource.DataSourceKey;
import com.mybatisflex.core.paginate.Page;
import com.mybatisflex.core.query.QueryWrapper;
import lombok.AllArgsConstructor;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* @author: MoChenLi
* @description:
* @createTime: 2024-06-29 18:11
*/
@RestController
@AllArgsConstructor
public class TestController {
private final OrderMapper orderMapper;
private final DataBaseConfigMapper dataBaseConfigMapper;
/**
* 测试事务问题
* 报错情况二 分库分表的数据源报错 回滚
*/
@GetMapping("/testTransactionError1")
@Transactional
public void testTransactionError1() {
//切换数据源 使用ShardingDataSource数据源
DataSourceKey.use("getShardingDataSource");
for (int i = 1; i <= 5; i++) {
Order order = new Order();
//不出错 1%2=1应该在server_order1库当中
order.setUserId(1L);
//不出错 "事务回滚测试".hashCode() % 2 = 0 应该在 t_order0表当中;
order.setOrderNo("事务回滚测试");
orderMapper.insert(order);
}
//模拟报错
int k = 10 / 0;
DataSourceKey.clear();//清除数据源
List<DataBaseConfig> dataBaseConfigs = dataBaseConfigMapper.selectAll();
}
}
结果回滚了
补充：mybatis-plus不支持多数据源切换，得自己实现，且还得自己进行自定义事务增强
