ShardingSphere多租户分库分表动态加载、切换数据源1.需求说明
要实现多租户动态加载、切换数据源，并进⾏分表操作。
表结构参考：
CREATE TABLE `tenant_info` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`TENANT_ID` varchar(255) DEFAULT NULL COMMENT '租户id',
`TENANT_NAME` varchar(255) DEFAULT NULL COMMENT '租户名称',
`DATASOURCE_URL` varchar(255) DEFAULT NULL COMMENT '数据源url',
`DATASOURCE_USERNAME` varchar(255) DEFAULT NULL COMMENT '数据源⽤户名',
`DATASOURCE_PASSWORD` varchar(255) DEFAULT NULL COMMENT '数据源密码',
`DATASOURCE_DRIVER` varchar(255) DEFAULT NULL COMMENT '数据源驱动',
`SYSTEM_ACCOUNT` varchar(255) DEFAULT NULL COMMENT '系统账号',
`SYSTEM_PASSWORD` varchar(255) DEFAULT NULL COMMENT '账号密码',
`SYSTEM_PROJECT` varchar(255) DEFAULT NULL COMMENT '系统PROJECT',
`STATUS` tinyint(1) DEFAULT NULL COMMENT '是否启⽤（1是0否）',
`CREATE_TIME` datetime DEFAULT NULL COMMENT '创建时间',
`UPDATE_TIME` datetime DEFAULT NULL COMMENT '更新时间',
`type` int(255) DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='多租户表';
2.相关依赖
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>1.2.12</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-aop</artifactId>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.2.0</version>
</dependency>
3.MybatisConfig⽂件
l.fig;
springboot aop
import com.alibaba.druid.filter.Filter;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.wall.WallConfig;
import com.alibaba.druid.wall.WallFilter;
import parser.ISqlParser;
import sion.parsers.BlockAttackSqlParser;
import sion.plugins.PaginationInterceptor;
slf4j.Slf4j;
import org.apache.ibatis.plugin.Interceptor;
batis.spring.SqlSessionFactoryBean;
batis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.t.properties.ConfigurationProperties;
import t.annotation.Bean;
import t.annotation.Configuration;
import t.annotation.Primary;
import io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import ansaction.PlatformTransactionManager;
import ansaction.annotation.EnableTransactionManagement;
import javax.sql.DataSource;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* 初始化配置，提供默认数据源
* @author  wyf
* @date  2021/10/9 11:31
*/
@Slf4j
@EnableTransactionManagement
@Configuration
@MapperScan("ute.mapper")
public class MybatisConfig {
@Primary
@Bean("master")
@ConfigurationProperties(prefix = "spring.shardingsphere.datasource")
public DataSource master() {
DruidDataSource druidDataSource = new DruidDataSource();//如果不是分库分表的话，可以new DataSource()。不然会不到初始的数据源        List<Filter> filterList = new ArrayList<>();
filterList.add(wallFilter());
druidDataSource.setProxyFilters(filterList);
System.out.println("初始化yml配置的默认数据库...");
return druidDataSource;
}
/*
@Bean("slave")
@ConfigurationProperties(prefix = "spring.datasource.slave")
public DataSource slave() {
return new DruidDataSource();
}*/
@Bean("dynamicDataSource")
public DataSource dynamicDataSource() {
Map<Object, Object> map = new HashMap<>(1);
map.put("master", master());
return new DynamicDataSource(master(), map);
}
@Bean(name="ibatiesConfig")
@ConfigurationProperties(prefix = "figuration")
public org.apache.ibatis.session.Configuration ibatiesConfig(){
return new org.apache.ibatis.session.Configuration();
}
@Bean
public WallFilter wallFilter() {
WallFilter wallFilter = new WallFilter();
wallFilter.setConfig(wallConfig());
return wallFilter;
}
@Bean
public WallConfig wallConfig() {
WallConfig config = new WallConfig();
//允许⼀次执⾏多条语句
config.setMultiStatementAllow(true);
/
/允许⾮基本语句的其他语句
config.setNoneBaseStatementAllow(true);
//允许包含注释的 SQL 执⾏
config.setCommentAllow(true);
return config;
}
@Bean
public SqlSessionFactoryBean sqlSessionFactoryBean(@Qualifier("dynamicDataSource") DataSource dynamicDataSource,
@Qualifier("ibatiesConfig") org.apache.ibatis.session.Configuration ibatiesConfig) throws Exception {
SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
//重点，使分页插件⽣效
Interceptor[] plugins = new Interceptor[1];
plugins[0] = paginationInterceptor();
sessionFactory.setPlugins(plugins);
// 配置数据源，此处配置为关键配置，如果没有将 dynamicDataSource作为数据源则不能实现切换
sessionFactory.setDataSource(dynamicDataSource);
// 扫描Model
sessionFactory.setTypeAliasesPackage("ute.domain");
// 扫描映射⽂件
sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/*.xml"));        sessionFactory.setConfiguration(ibatiesConfig);
return sessionFactory;
}
@Bean
public PlatformTransactionManager transactionManager() {
// 配置事务管理, 使⽤事务时在⽅法头部添加@Transactional注解即可
return new DataSourceTransactionManager(dynamicDataSource());
}
/**
* 加载分页插件
*
* @return
*/
@Bean
public PaginationInterceptor paginationInterceptor() {
PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
List<ISqlParser> sqlParserList = new ArrayList<>();
// 攻击 SQL 阻断解析器、加⼊解析链
sqlParserList.add(new BlockAttackSqlParser());
paginationInterceptor.setSqlParserList(sqlParserList);
return paginationInterceptor;
}
}
3.DynamicDataSource⽂件
l.fig;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.util.StringUtils;
l.ute.domain.TenantInfo;
slf4j.Slf4j;
import org.apache.fig.sharding.ShardingRuleConfiguration;
import org.apache.fig.sharding.TableRuleConfiguration;
import org.apache.fig.sharding.strategy.StandardShardingStrategyConfiguration;
import org.apache.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import javax.sql.DataSource;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;
/**
* （切换数据源必须在调⽤service之前进⾏，也就是开启事务之前）
* 动态数据源实现类
* @author  weiyongfu
* @date  2021/10/9 13:31
*/
@Slf4j
public class DynamicDataSource extends AbstractRoutingDataSource {
private Map<Object, Object> dynamicTargetDataSources;
/**
* 决定使⽤哪个数据源之前需要把多个数据源的信息以及默认数据源信息配置好
*
* @param defaultTargetDataSource 默认数据源
* @param targetDataSources      ⽬标数据源
*/
DynamicDataSource(DataSource defaultTargetDataSource, Map<Object, Object> targetDataSources) {
setDefaultTargetDataSource(defaultTargetDataSource);
setTargetDataSources(targetDataSources);
this.dynamicTargetDataSources = targetDataSources;
super.afterPropertiesSet();
}
public DynamicDataSource() {
}
/**
* 如果不希望数据源在启动配置时就加载好，可以定制这个⽅法，从任何你希望的地⽅读取并返回数据源
* ⽐如从数据库、⽂件、外部接⼝等读取数据源信息，并最终返回⼀个DataSource实现类对象即可
*/
@Override
protected DataSource determineTargetDataSource() {
return super.determineTargetDataSource();
}
/**
* 如果希望所有数据源在启动配置时就加载好，这⾥通过设置数据源Key值来切换数据，定制这个⽅法
*
* 实现数据源切换要扩展的⽅法，该⽅法的返回值就是项⽬中所要⽤的DataSource的key值，
* 拿到该key后就可以在resolvedDataSource中取出对应的DataSource，如果key不到对应的DataSource就使⽤默认的数据源。    */
@Override
protected Object determineCurrentLookupKey() {
String dataSourceName = DataSourceKey();
if (!StringUtils.isEmpty(dataSourceName)) {
Map<Object, Object> dynamicTargetDataSources2 = this.dynamicTargetDataSources;
if (ainsKey(dataSourceName)) {
log.info("当前数据源为：" + dataSourceName);
} else {
log.info("不存在的数据源："+ dataSourceName);
}
} else {
log.info("当前数据源为：默认数据源");
}
return dataSourceName;
}
/**
* 设置默认数据源
* @param defaultDataSource Object
*/
@Override
public void setDefaultTargetDataSource(Object defaultDataSource) {
super.setDefaultTargetDataSource(defaultDataSource);
}
/**
* 设置数据源
* @param dataSources Map<Object, Object>
*/
@Override
public void setTargetDataSources(Map<Object, Object> dataSources) {
super.setTargetDataSources(dataSources);
this.dynamicTargetDataSources = dataSources;
// 将数据源的 key 放到数据源上下⽂的 key 集合中，⽤于切换时判断数据源是否有效
DynamicDataSourceContextHolder.addDataSourceKeys(dataSources.keySet());
}
void setDataSources(TenantInfo tenantInfo) {
String tenantId = TenantId();
String dataSourceDriver = DataSourceDriver();
String url = DataSourceUrl();
String username = DataSourceUsername();
String password = DataSourcePassword();
try {
// 排除连接不上的错误
Class.forName(dataSourceDriver);
// 相当于连接数据库
DruidDataSource dataSource = new DruidDataSource();
dataSource.setDriverClassName(dataSourceDriver);
dataSource.setUrl(url);
dataSource.setUsername(username);
dataSource.setPassword(password);
this.dynamicTargetDataSources.put(tenantId, buildShardDatasources(dataSource,tenantId));
setTargetDataSources(this.dynamicTargetDataSources);
super.afterPropertiesSet();
log.info("数据源初始化成功!租户标识:" + tenantId);
} catch (Exception e) {
throw new IllegalStateException("[" + tenantId + "]" + "：数据源连接不上，可能是连接参数有误！");
}
}
public DataSource buildShardDatasources(DataSource dataSource,String type) {
// 配置多数据源
Map<String, DataSource> dsMap = new HashMap<>();
dsMap.put(type, dataSource);
//tableRuleConfiguration.setDatabaseShardingStrategyConfig()；分库规则，要分库的⾃⼰加进去
//act_test_data表分⽚规则添加
/*TableRuleConfiguration manuHisTableRuleConfig = new TableRuleConfiguration("act_test_data", type+".act_test_data_$->{2020..2023}");        StandardShardingStrategyConfiguration manu_workorder = new StandardShardingStrategyConfiguration("testtime", new TableShardingAlgorithm());
manuHisTableRuleConfig.setTableShardingStrategyConfig(manu_workorder);
ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
//manu_order表分⽚规则添加

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。