shardingsphere-jdbc实现分库分表
提供了单独只进行分库和分库分表的两种实现demo.其实两者的区别在于定义TableRuleConfiguration(表的路由规则)时是否设置分库或者分表规则
这里主要是针对的数据分片的分库分表的数据配置 , 若有主从库配置或者其他的场景的可参考官方文档 : https://shardingsphere.apache.org/document/5.1.0/en/user-manual/shardingsphere-jdbc/ . 这里面有api/starter/yaml/properties等各种配置支持.
1.POM配置
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<!--mybatis-->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
</dependency>
<!--mysql-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
</dependency>
<!-- shardingsphere -->
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-core-api</artifactId>
<version>4.1.1</version>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-core</artifactId>
<version>4.1.1</version>
</dependency>
2.变量配置.这里变量名可根据自己需求进行命名
# ************************** sharding sphere config start **************************** # 参与分库的表,用逗号分隔,不在此定义的,直接路由到默认库中 SHARDING-DATABASE-TABLE-NAMES=B_USER,T_SP # 参与分表的表,用逗号分隔 SHARDING-TABLE-NAMES=T_SP # 公共分库字段 database.sharding.column=TENANT_ID # 表b_user的路由节点 table.user.actual-data-nodes=cloudtravel_consumer$->{1..2}.b_user # 表t_sp的路由节点 table.sp.actual-data-nodes=cloudtravel_consumer$->{1..2}.t_sp_$->{0..1} # 表t_sp分表字段 table.sp.sharding.column=BIZ_ID # Configure the 1st data source sharding.datasource1.name=cloudtravel_consumer1 datasource.cloudtravel-consumer1.driver-class-name=com.mysql.cj.jdbc.Driver datasource.cloudtravel-consumer1.url=jdbc:mysql://localhost:3306/cloudtravel_consumer1?useAffectedRows=true&serverTimezone=UTC&characterEncoding=utf-8 datasource.cloudtravel-consumer1.username=root datasource.cloudtravel-consumer1.password=root # Configure the 2nd data source SHARDING.datasource2.name=cloudtravel_consumer2 datasource.cloudtravel-consumer2.driver-class-name=com.mysql.cj.jdbc.Driver datasource.cloudtravel-consumer2.url=jdbc:mysql://localhost:3306/cloudtravel_consumer2?useAffectedRows=true&serverTimezone=UTC&characterEncoding=utf-8 datasource.cloudtravel-consumer2.username=root datasource.cloudtravel-consumer2.password=root # 是否打印sql语句 mybatis.mapper-locations=classpath:mapper/*.xml # ************************** sharding sphere config end ****************************
3. 引入配置信息
package com.cloudtravel.shardingsphere.db; import org.springframework.beans.factory.annotation.Value; import org.springframework.context.annotation.Configuration; import org.springframework.core.annotation.Order; @Configuration @Order(10) public class DataSourceConfigBase { @Value("${sharding.datasource1.name}") private String datasourceName1; @Value("${SHARDING.datasource2.name}") private String datasourceName2; @Value("${mybatis.mapper-locations}") private String mapperLocations; @Value("${database.sharding.column}") private String databaseShardingColumnDefault; @Value("${SHARDING-DATABASE-TABLE-NAMES}") private String shardingDatabaseTableNames; @Value("${SHARDING-TABLE-NAMES}") private String shardingTableNames; @Value("${table.user.actual-data-nodes}") private String userActualDataNodes; @Value("${table.sp.actual-data-nodes}") private String spActualDataNodes; @Value("${table.sp.sharding.column}") private String tableSpShardingColumn; public String getDatasourceName1() { return datasourceName1; } public String getDatasourceName2() { return datasourceName2; } public String getMapperLocations() { return mapperLocations; } public String getDatabaseShardingColumnDefault() { return databaseShardingColumnDefault; } public String getShardingDatabaseTableNames() { return shardingDatabaseTableNames; } public String getShardingTableNames() { return shardingTableNames; } public String getUserActualDataNodes() { return userActualDataNodes; } public String getSpActualDataNodes() { return spActualDataNodes; } public String getTableSpShardingColumn() { return tableSpShardingColumn; } }
4.基础配置类
@Configuration
@MapperScan(basePackages = "com.cloudtravel.shardingsphere.dao" , sqlSessionTemplateRef = "testSqlSessionTemplate")
@Order(11)
public class DataSourceConfig {
@Autowired
private TableShardingAlgorithm tableShardingAlgorithmCom;
@Autowired
private DatabaseShardingAlgorithm databaseShardingAlgorithm;
@Autowired
DataSourceConfigBase dataSourceConfigBase;
@Autowired
Environment environment;
/**
* 设置数据源
* @return
* @throws SQLException
*/
@Primary
@Bean(name = "shardingDataSource")
DataSource getShardingDataSource() throws SQLException {
ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
// 设置默认的分库策略
shardingRuleConfig.setDefaultDatabaseShardingStrategyConfig(
new StandardShardingStrategyConfiguration(
dataSourceConfigBase.getDatabaseShardingColumnDefault(),
databaseShardingAlgorithm
)
);
// 配置表规则
shardingRuleConfig.getTableRuleConfigs().
addAll(Arrays.asList(
getBUserRuleConfiguration() ,
getTSpRuleConfiguration()
)
);
// 设置默认数据库
shardingRuleConfig.setDefaultDataSourceName(dataSourceConfigBase.getDatasourceName1());
return ShardingDataSourceFactory.createDataSource(createDataSourceMap(), shardingRuleConfig, new Properties());
}
/**
* 获取sqlSessionFactory实例
* @param shardingDataSource
* @return
* @throws Exception
*/
@Bean("SqlSessionFactory")
@Primary
public SqlSessionFactory sqlSessionFactory(@Qualifier("shardingDataSource") DataSource shardingDataSource) throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(shardingDataSource);
bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(dataSourceConfigBase.getMapperLocations()));
return bean.getObject();
}
@Bean
@Primary
public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("SqlSessionFactory")SqlSessionFactory sqlSessionFactory) {
return new SqlSessionTemplate(sqlSessionFactory);
}
/**
* 需要手动配置事务管理器
* @param shardingDataSource
* @return
*/
@Bean
public DataSourceTransactionManager transactitonManager(@Qualifier("shardingDataSource") DataSource shardingDataSource) {
return new DataSourceTransactionManager(shardingDataSource);
}
/**
* 具体表进行分库分表的规则配置
* @return
*/
private TableRuleConfiguration getBUserRuleConfiguration() {
TableRuleConfiguration orderTableRuleConfig=new TableRuleConfiguration("b_user",
dataSourceConfigBase.getUserActualDataNodes());
orderTableRuleConfig.setDatabaseShardingStrategyConfig(
new StandardShardingStrategyConfiguration(
dataSourceConfigBase.getDatabaseShardingColumnDefault(),
databaseShardingAlgorithm
)
);
return orderTableRuleConfig;
}
/**
* 具体表进行分库分表的规则配置
* @return
*/
private TableRuleConfiguration getTSpRuleConfiguration() {
TableRuleConfiguration orderTableRuleConfig=new TableRuleConfiguration("t_sp",
dataSourceConfigBase.getSpActualDataNodes());
orderTableRuleConfig.setDatabaseShardingStrategyConfig(
new StandardShardingStrategyConfiguration(
dataSourceConfigBase.getDatabaseShardingColumnDefault(),
databaseShardingAlgorithm
)
);
orderTableRuleConfig.setTableShardingStrategyConfig(
new ComplexShardingStrategyConfiguration(
dataSourceConfigBase.getTableSpShardingColumn(),
tableShardingAlgorithmCom
)
);
return orderTableRuleConfig;
}
public DataSource druidDataSource1() {
DruidDataSource dataSource = new DruidDataSource();
dataSource.setUrl(environment.getProperty("datasource.cloudtravel-consumer1.url"));
dataSource.setUsername(environment.getProperty("datasource.cloudtravel-consumer1.username"));
dataSource.setPassword(environment.getProperty("datasource.cloudtravel-consumer1.password"));
dataSource.setDriverClassName(environment.getProperty("datasource.cloudtravel-consumer1.driver-class-name"));
return dataSource;
}
public DataSource druidDataSource2() {
DruidDataSource dataSource = new DruidDataSource();
dataSource.setUrl(environment.getProperty("datasource.cloudtravel-consumer2.url"));
dataSource.setUsername(environment.getProperty("datasource.cloudtravel-consumer2.username"));
dataSource.setPassword(environment.getProperty("datasource.cloudtravel-consumer2.password"));
dataSource.setDriverClassName(environment.getProperty("datasource.cloudtravel-consumer2.driver-class-name"));
return dataSource;
}
/**
* 多库的连接配置
* @return
*/
private Map<String, DataSource> createDataSourceMap() {
Map<String, DataSource> result = new HashMap<>();
result.put(dataSourceConfigBase.getDatasourceName1(),druidDataSource1());
result.put(dataSourceConfigBase.getDatasourceName2(), druidDataSource2());
return result;
}
}
5.分表逻辑实现
/** *分表逻辑 */ @Service public class TableShardingAlgorithm implements ComplexKeysShardingAlgorithm { @Value("${SHARDING-TABLE-NAMES}") private String SHARDING_TABLE_NAMES; @Override public Collection<String> doSharding(Collection collection, ComplexKeysShardingValue complexKeysShardingValue) { List<String> tables = new ArrayList<>(); Long bizId = (Long) ((List) complexKeysShardingValue.getColumnNameAndShardingValuesMap().get("biz_id")).get(0); String tableName = complexKeysShardingValue.getLogicTableName(); String physicsTable = tableName + "_"; physicsTable += String.valueOf(bizId % 2); tables.add(physicsTable); System.out.println(tableName + "走分表,bizId = " + bizId + "路由到表" + physicsTable); return tables; } }
6.dataSource配置--分库逻辑实现
/**
* 分库逻辑
*/
@Service
public class DatabaseShardingAlgorithm implements PreciseShardingAlgorithm {
@Autowired
private DataSourceConfigBase dataSourceConfigBase;
@Override
public String doSharding(Collection collection, PreciseShardingValue preciseShardingValue) {
String dataSource = null;
String tableName = preciseShardingValue.getLogicTableName();
Integer value = StringUtils.isNotEmpty(preciseShardingValue.getValue().toString()) ?
Integer.parseInt(preciseShardingValue.getValue().toString()) : 0;
if (value % 2 == 0) {
dataSource = dataSourceConfigBase.getDatasourceName1();
} else {
dataSource = dataSourceConfigBase.getDatasourceName2();
}
System.out.println(tableName + "走分库,tenantId = " + value + "进入" + dataSource);
return dataSource;
}
}
7. seata配置
resources下创建seata.config配置注册到SeaTa管理服务的应用名+分布式事务的服务组名
client {
application.id = cloudtravel-shardingsphere
transaction.service.group = sp-user-seata-group
}
8.业务测试
package com.cloudtravel.shardingsphere.service.impl;
import com.cloudtravel.shardingsphere.common.service.ShardBaseUserService;
import com.cloudtravel.shardingsphere.dao.TUserModelMapper;
import com.cloudtravel.shardingsphere.model.TUserModel;
import org.apache.shardingsphere.transaction.annotation.ShardingTransactionType;
import org.apache.shardingsphere.transaction.core.TransactionType;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
@Service
public class ShardBaseUserServiceImpl implements ShardBaseUserService {
@Autowired
TUserModelMapper userModelMapper;
@Override
// @GlobalTransactional(name = "sp-user-seata-group" , rollbackFor = Exception.class)
@Transactional //引入事务支持
@ShardingTransactionType(TransactionType.BASE)//引入分布式柔性事务支持,不需分布式事务的话只用@Transactional即可
public Long addUser(Long tenantId) {
TUserModel model = new TUserModel();
model.setTenantId(tenantId.toString());
model.setBizId("1");
model.setUserType(1);
model.setUserName("test1");
model.setIdNumber("231");
model.setIdNumType(0);
userModelMapper.insertSelective(model);
// throw new RuntimeException("test");
return model.getId();
}
}
补充 : 莫了个大名 . 看人家都说引入seata-sharding-at包就可以实现分布式事务 . 这两天调试发现不行了