shardingsphere 实现 springboot集成 多数据源
分库分表实现
首先
引入依赖
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.0.0-RC1</version>
</dependency>
进行配置
package com.epay.dtc.ms.common.configuration;
import com.alibaba.druid.pool.DruidDataSource;
import com.github.houbb.heaven.util.lang.StringUtil;
import com.google.common.collect.Lists;
import lombok.Data;
import org.apache.shardingsphere.api.config.sharding.ShardingRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.TableRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.strategy.InlineShardingStrategyConfiguration;
import org.apache.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;
/**
* @author zhangsy
* @date 2021/8/2 9:20
* @description 分库分表数据库配置
*/
@Data
public class ShardingDataSourceConfig {
private final static Logger log = LoggerFactory.getLogger(ShardingDataSourceConfig.class);
public String driverClassName;
public String url;
public String username;
public String password;
public int initialSize;
public int minIdle;
public int maxActive;
public long maxWait;
public long timeBetweenEvictionRunsMillis;
public long minEvictableIdleTimeMillis;
public String validationQuery;
public Boolean testOnBorrow;
public Boolean testOnReturn;
public Boolean testWhileIdle;
public int validationQueryTimeout;
public Boolean keepAlive;
public Boolean removeAbandoned;
public int removeAbandonedTimeout;
public Boolean logAbandoned;
public Boolean poolPreparedStatements;
public int maxPoolPreparedStatementPerConnectionSize;
public String filters;
public String connectionProperties;
private String dbNames;
private Map<String, DataSource> dataSourceMap = new HashMap<>();
public DataSource buildDataSource(){
initDataSourceMap();
String dbName = "detail";
// 具体分库分表策略，按什么规则来分
ShardingRuleConfiguration conf = new ShardingRuleConfiguration();
// table rule
TableRuleConfiguration tableRule = new TableRuleConfiguration("m_user_group_detail", "detail.m_user_group_detail_$->{1..12}");
/* // 分表策略
ShardingStrategyConfiguration tableShardingStrategyConfig = new StandardShardingStrategyConfiguration("user_group_filter_time", new MyTablePreciseShardingAlgorithm());
tableRule.setTableShardingStrategyConfig(tableShardingStrategyConfig);*/
InlineShardingStrategyConfiguration inlineShardingStrategyConfiguration = new InlineShardingStrategyConfiguration("user_group_filter_time","m_user_group_detail_$->{user_group_filter_time}");
tableRule.setTableShardingStrategyConfig(inlineShardingStrategyConfiguration);
conf.setTableRuleConfigs(Lists.newArrayList(tableRule));
Properties props = new Properties();
props.put("sql.show", false);
DataSource dataSource = null;
try {
dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap, conf, props);
} catch (SQLException e) {
log.error("数据源初始化失败", e);
}
return dataSource;
}
// @Bean
public void initDataSourceMap() {
dbNames = "detail";
for (String name : dbNames.split(",")) {
DruidDataSource druidDataSource = new DruidDataSource();
druidDataSource.setDriverClassName(driverClassName);
druidDataSource.setUrl(url);
druidDataSource.setUsername(username);
druidDataSource.setPassword(password);
if(initialSize > 0){
druidDataSource.setInitialSize(initialSize);
}
if(minIdle > 0){
druidDataSource.setMinIdle(minIdle);
}
if(maxActive > 0){
druidDataSource.setMaxActive(maxActive);
}
if(maxWait > 0){
druidDataSource.setMaxWait(maxWait);
}
if(timeBetweenEvictionRunsMillis > 0){
druidDataSource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
}
if(minEvictableIdleTimeMillis > 0){
druidDataSource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
}
if(validationQueryTimeout > 0){
druidDataSource.setValidationQueryTimeout(validationQueryTimeout);
}
if(removeAbandonedTimeout > 0){
druidDataSource.setRemoveAbandonedTimeout(removeAbandonedTimeout);
}
if(maxPoolPreparedStatementPerConnectionSize > 0){
druidDataSource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
}
if(StringUtil.isNotBlank(validationQuery)){
druidDataSource.setValidationQuery(validationQuery);
}
if(StringUtil.isNotBlank(connectionProperties)){
druidDataSource.setConnectionProperties(connectionProperties);
}
if(testOnReturn != null){
druidDataSource.setTestOnReturn(testOnReturn);
}
if(testWhileIdle != null){
druidDataSource.setTestWhileIdle(testWhileIdle);
}
if(keepAlive != null){
druidDataSource.setKeepAlive(keepAlive);
}
if(removeAbandoned != null){
druidDataSource.setRemoveAbandoned(removeAbandoned);
}
if(logAbandoned != null){
druidDataSource.setLogAbandoned(logAbandoned);
}
if(poolPreparedStatements != null){
druidDataSource.setPoolPreparedStatements(poolPreparedStatements);
}
try {
if(StringUtil.isNotBlank(filters)){
druidDataSource.setFilters(filters);
}
dataSourceMap.put(name, druidDataSource);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
ymal配置
spring.shardingsphere.datasource.names = detail
spring.shardingsphere.datasource.detail.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.detail.driverClassName = com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.detail.url = jdbc:mysql://localhost:3306/****?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai
spring.shardingsphere.datasource.detail.username = root
spring.shardingsphere.datasource.detail.password = root
spring.shardingsphere.datasource.detail.filters = stat,slf4j,wall
spring.shardingsphere.datasource.detail.initialSize = 5
spring.shardingsphere.datasource.detail.keepAlive = true
spring.shardingsphere.datasource.detail.maxActive = 10
spring.shardingsphere.datasource.detail.maxWait = 10000
spring.shardingsphere.datasource.detail.minEvictableIdleTimeMillis = 300000
spring.shardingsphere.datasource.detail.minIdle = 5
spring.shardingsphere.datasource.detail.removeAbandoned = true
spring.shardingsphere.datasource.detail.removeAbandonedTimeout = 80
spring.shardingsphere.datasource.detail.testOnBorrow = true
spring.shardingsphere.datasource.detail.testOnReturn = false
spring.shardingsphere.datasource.detail.testWhileIdle = true
spring.shardingsphere.datasource.detail.timeBetweenEvictionRunsMillis = 60000
spring.shardingsphere.datasource.detail.validationQuery = SELECT 1
spring.shardingsphere.datasource.detail.validationQueryTimeout = 1
spring.shardingsphere.sharding.tables.m_user_group_detail.logic-table = detail.m_user_group_detail
spring.shardingsphere.sharding.tables.m_user_group_detail.actual-data-nodes = detail.m_user_group_detail_0$->{1..9},detail.m_user_group_detail_$->{10..12}
spring.shardingsphere.sharding.tables.m_user_group_detail.table-strategy.inline.sharding-column = user_group_filter_time
spring.shardingsphere.sharding.tables.m_user_group_detail.table-strategy.inline.algorithm-expression = m_user_group_detail_$->{user_group_filter_time}
spring.shardingsphere.sharding.default-data-source-name = detail
spring.shardingsphere.sharding.binding-tables = m_user_group_detail
spring.shardingsphere.props.sql.show = true
config配置
package com.epay.dtc.ms.cust.provider.configuration.mysql.detail;
import com.epay.dtc.ms.common.configuration.ShardingDataSourceConfig;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.stereotype.Component;
import javax.sql.DataSource;
/**
* @author zh
* @date 2021/07/06 09:48
* @description
*/
@Component
@MapperScan(basePackages = "com.epay.dtc.ms.common.mapper.mysql.detail", sqlSessionFactoryRef = "detailSqlSessionFactory")
@ConfigurationProperties(prefix = "spring.shardingsphere.datasource.detail")
public class DetailDataSourceConfig extends ShardingDataSourceConfig {
private String dbNames;
@Bean("detailDataSource")
public DataSource getDetailDataSource(){
return buildDataSource();
}
@Bean("detailSqlSessionFactory")
public SqlSessionFactory detailSqlSessionFactory(@Qualifier("detailDataSource") DataSource dataSource) throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(dataSource);
bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/mysql/detail/*.xml"));
return bean.getObject();
}
@Bean("detailSqlSessionTemplate")
public SqlSessionTemplate detailSqlSessionTemplate(@Qualifier("detailSqlSessionFactory") SqlSessionFactory sqlSessionFactory){
return new SqlSessionTemplate(sqlSessionFactory);
}
}
