Springboot中sharding-jdbc的API模式并使用自定义算法
可配合AbstractRoutingData使用切换数据源
程序用到了AbstractRoutingData来切换数据源（数据源是自定义的格式编写并没有用springboot的自动装配的格式写），但是又用到sharding-jdbc进行分库分页，如果直接引用sharding-jdbc-spring-boot-starter会自动装配它自己默认的数据源dataSource，导致我们自己写的数据源失效。所以我们需要用API的模式把sharding-jdbc的数据源dataSource放入我们自己的写的AbstractRoutingData里面来。
POM文件添加
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>shardingsphere-jdbc-core</artifactId>
<version>5.1.2</version>
</dependency>
自定义的数据库信息格式
使用AbstractRoutingData切换数据源
public class DynamicDataSource extends AbstractRoutingDataSource {
private static final Logger logger = Logger.getLogger(DynamicDataSource.class);
private static DynamicDataSource dynamicDataSource;
private final Map<Object, Object> targetDataSources = new HashMap();
private static final ThreadLocal<String> dataSourceName = new ThreadLocal();
public DynamicDataSource() {
}
public static DynamicDataSource getInstance() {
if (dynamicDataSource == null) {
synchronized(DynamicDataSource.class) {
if (dynamicDataSource == null) {
dynamicDataSource = new DynamicDataSource();
}
}
}
return dynamicDataSource;
}
/**
*determineCurrentLookupKey() 方法决定使用哪个数据源
*/
protected Object determineCurrentLookupKey() {
return (String)dataSourceName.get();
}
public void setTargetDataSources(Map<String, DataSource> targetDataSources) {
//设置默认数据源
//super.setDefaultTargetDataSource(targetDataSources.get("default"));
this.targetDataSources.putAll(targetDataSources);
//设置数据源
super.setTargetDataSources(this.targetDataSources);
super.afterPropertiesSet();
}
public Map<Object, Object> getTargetDataSources() {
return this.targetDataSources;
}
public void removeDataSource(String code) {
if (this.targetDataSources.get(code) != null) {
this.targetDataSources.remove(code);
}
//重新设置数据源
super.setTargetDataSources(this.targetDataSources);
super.afterPropertiesSet();
}
public static void setDataSource(String datasource) {
logger.info("切换数据源为："+datasource);
dataSourceName.set(datasource);
}
public static void clear() {
dataSourceName.remove();
}
}
- 数据源是自定义的，要禁用springboot的数据源自动装配配置,启动类上加上
@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class})
写入自己的自定义数据源
@Configuration
public class ShardingDataSourceConfig{
Logger logger = Logger.getLogger(ShardingDataSourceConfig.class);
@Primary
@Bean
public DataSource shardingdataSource() throws SQLException, IOException {
//获取AbstractRoutingData对象
DynamicDataSource chooseDataSource = DynamicDataSource.getInstance();
//获取自己配置文件上的普通数据源，该方法忽略展示，key为数据库的名字，value为数据源
Map<String, DataSource> targetDataSources = this.getTargetDataSources();
/*生成数据源的样式，使用DruidDataSource，POM文件记得加入，也可以使用其他数据源
DruidDataSource dataSource = new DruidDataSource();
dataSource.setDriverClassName(stringStringMap.get("driverClassName"));
dataSource.setUrl(stringStringMap.get("url"));
dataSource.setUsername(stringStringMap.get("username"));
dataSource.setPassword(stringStringMap.get("password"));
*/
//设置默认的数据源，必须保证Map里面有该值,可以放在DynamicDataSource里面再设置
chooseDataSource.setDefaultTargetDataSource(targetDataSources.get("default"));
//添加自己的sharding-jdbc数据源
//分库分表数据源
DataSource shardingDataSource = ShardingDataBaseConfiguration.getDataSource(shardingPrefixss);
targetDataSources.put("shardingDT",shardingDataSource);
//只分表数据源
DataSource dataSource = ShardingTableConfiguration.getDataSource(sourceDataBase);
targetDataSources.put("shardingT",dataSource);
}
chooseDataSource.setTargetDataSources(targetDataSources);
return chooseDataSource;
}
}
获取配置文件辅助类，网上很多方法，这里使用的是继承PropertyPlaceholderConfigurer类
public class PropertyPlaceholder extends PropertyPlaceholderConfigurer {
private static Map<String,String> propertyMap;
@Override
protected void processProperties(ConfigurableListableBeanFactory beanFactoryToProcess, Properties props) throws BeansException {
super.processProperties(beanFactoryToProcess, props);
propertyMap = new HashMap<String, String>();
for (Object key : props.keySet()) {
String keyStr = key.toString();
String value = props.getProperty(keyStr);
propertyMap.put(keyStr, value);
}
}
//自定义一个方法，即根据key拿属性值,方便java代码中取属性值
public static String getProperty(String name) {
return propertyMap.get(name);
}
}
定义自己的sharding分片规则，并返回sharding的数据源.
分库分表配置
public class ShardingDataBaseConfiguration {
/**
* 创建数据源
*/
private static Map<String, DataSource> createDataSourceMap(List<String> datasourceNames){
Map<String, DataSource> dataSourceMap=new HashMap<>();
for (int i = 0; i < datasourceNames.size(); i++) {
Map<String, String> stringStringMap = DataBaseInfoUtil.getDataBaseInformation().get(datasourceNames.get(i));
if (ObjectUtil.isNull(stringStringMap)){
return null;
}
DruidDataSource dataSource = new DruidDataSource();
dataSource.setDriverClassName(stringStringMap.get("driverClassName"));
dataSource.setUrl(stringStringMap.get("url"));
dataSource.setUsername(stringStringMap.get("username"));
dataSource.setPassword(stringStringMap.get("password"));
dataSourceMap.put("ds_"+datasourceNames.get(i), dataSource);
}
return dataSourceMap;
}
/**
* 分库分表设置
* create_time为分库的字段，按create_time字段的值来进行分库计算
* HashModShardingAlgorithm.class.getName()是算法名字，可随便写，需要和分表配置的算法名字对应上就行
* MY_HASH_MOD、MY_COMPLEX_INLINE、DATABASE_INLINE自定义算法的名字，最重要的地方，必须和自定义算法类中返回的名字一致，就是getType()返回的值，名字可以随意取
* */
private static ShardingRuleConfiguration createShardingRuleConfiguration() {
ShardingRuleConfiguration configuration = new ShardingRuleConfiguration();
configuration.getTables().add(getWlbTableRuleConfiguration());
configuration.getTables().add(getWiorpTableRuleConfiguration());
//设置分库的规则，按年份分库
configuration.setDefaultDatabaseShardingStrategy(new StandardShardingStrategyConfiguration("create_time", PreciseDatabaseShardingAlgorithm.class.getName()));
configuration.getShardingAlgorithms().put(HashModShardingAlgorithm.class.getName(),new ShardingSphereAlgorithmConfiguration("MY_HASH_MOD",new Properties()));
configuration.getShardingAlgorithms().put(ComplexShardingAlgorithm.class.getName(),new ShardingSphereAlgorithmConfiguration("MY_COMPLEX_INLINE",new Properties()));
configuration.getShardingAlgorithms().put(PreciseDatabaseShardingAlgorithm.class.getName(),new ShardingSphereAlgorithmConfiguration("DATABASE_INLINE",new Properties()));
return configuration;
}
/**
* 制定my_test表分片规则
* my_test为逻辑表名，my_test_0，my_test_1....my_test_9为实际数据库的表名，就是把数据分到这0-9的表中
* ds_${2020..2022} 为实际数据源的名字：ds_2020,ds_2021,ds_2022,写法${..},{}里面可以进行运算，例如ds_${id % 2}
* sub_base为my_test表的分表字段，就是my_test表的分表规则按sub_base来区分
* HashModShardingAlgorithm.class.getName()，这个是算法的名字可以随意起，对应configuration.getShardingAlgorithms().put()中key的值，写上自己自定义的类名好容易确认区分，sharding-jdbc也有自己默认定义好的分片算法
* 如果使用ds_${id % 2}这种在{}进行运算的，可以不写setTableShardingStrategy
* */
private static ShardingTableRuleConfiguration getWlbTableRuleConfiguration(){
ShardingTableRuleConfiguration tableRule=new ShardingTableRuleConfiguration("my_test","ds_${2020..2022}.my_test_${0..9}");
tableRule.setKeyGenerateStrategy(new KeyGenerateStrategyConfiguration("id","snowflake"));
tableRule.setTableShardingStrategy(new StandardShardingStrategyConfiguration("sub_base",HashModShardingAlgorithm.class.getName()));
return tableRule;
}
/**
* 制定my_test2表分库分片规则
* */
private static ShardingTableRuleConfiguration getWiorpTableRuleConfiguration(){
ShardingTableRuleConfiguration tableRule=new ShardingTableRuleConfiguration("my_test2","ds_${2020..2022}.my_test2_${0..9}");
tableRule.setKeyGenerateStrategy(new KeyGenerateStrategyConfiguration("id","snowflake"));
tableRule.setTableShardingStrategy(new ComplexShardingStrategyConfiguration("code,name,sex,age", ComplexShardingAlgorithm.class.getName()));
return tableRule;
}
public static DataSource getDataSource(List<String> datasourceNames) throws SQLException {
// 其他配置
Properties properties = new Properties();
//控制台日志展示sharding-jdbc的sql
properties.put("sql-show","true");
return ShardingSphereDataSourceFactory.createDataSource(createDataSourceMap(datasourceNames),
Collections.singleton(createShardingRuleConfiguration()),properties);
}
}
仅分表配置
public class ShardingTableConfiguration {
/**
* 创建数据源
*/
private static Map<String, DataSource> createDataSourceMap(List<String> datasourceNames){
Map<String, DataSource> dataSourceMap=new HashMap<>();
for (int i = 0; i < datasourceNames.size(); i++) {
Map<String, String> stringStringMap = DataBaseInfoUtil.getDataBaseInformation().get(datasourceNames.get(i));
if (ObjectUtil.isNull(stringStringMap)){
return null;
}
DruidDataSource dataSource = new DruidDataSource();
dataSource.setDriverClassName(stringStringMap.get("driverClassName"));
dataSource.setUrl(stringStringMap.get("url"));
dataSource.setUsername(stringStringMap.get("username"));
dataSource.setPassword(stringStringMap.get("password"));
dataSourceMap.put("ds0", dataSource);
}
return dataSourceMap;
}
/**
* 分表设置
*/
private static ShardingRuleConfiguration createShardingRuleConfigurationOnlyTable() {
ShardingRuleConfiguration configuration = new ShardingRuleConfiguration();
configuration.getTables().add(getWlbTableRuleConfiguration());
configuration.getTables().add(getWiorpTableRuleConfiguration());
configuration.getShardingAlgorithms().put(HashModShardingAlgorithm.class.getName(),new ShardingSphereAlgorithmConfiguration("MY_HASH_MOD",new Properties()));
configuration.getShardingAlgorithms().put(ComplexShardingAlgorithm.class.getName(),new ShardingSphereAlgorithmConfiguration("MY_COMPLEX_INLINE",new Properties()));
return configuration;
}
/**
* 制定my_test3表分片规则
*/
private static ShardingTableRuleConfiguration getWlbTableRuleConfiguration(){
ShardingTableRuleConfiguration tableRule=new ShardingTableRuleConfiguration("my_test3","ds0.my_test3_${0..9}");
tableRule.setKeyGenerateStrategy(new KeyGenerateStrategyConfiguration("id","snowflake"));
tableRule.setTableShardingStrategy(new StandardShardingStrategyConfiguration("box_batch",HashModShardingAlgorithm.class.getName()));
return tableRule;
}
/**
* 制定my_test4表分库分片规则
*/
private static ShardingTableRuleConfiguration getWiorpTableRuleConfiguration(){
ShardingTableRuleConfiguration tableRule=new ShardingTableRuleConfiguration("my_test4","ds0.my_test4_${0..9}");
tableRule.setKeyGenerateStrategy(new KeyGenerateStrategyConfiguration("id","snowflake"));
tableRule.setTableShardingStrategy(new ComplexShardingStrategyConfiguration("code,name,sex,age", ComplexShardingAlgorithm.class.getName()));
return tableRule;
}
public static DataSource getDataSource(String datasourceNames) throws SQLException {
// 其他配置
Properties properties = new Properties();
properties.put("sql-show","true");
return ShardingSphereDataSourceFactory.createDataSource(createDataSourceMap(new ArrayList<String>(){{add(datasourceNames);}}),
Collections.singleton(createShardingRuleConfigurationOnlyTable()),properties);
}
}
自定义分库分片算法
标准分片算法
public final class HashModShardingAlgorithm implements StandardShardingAlgorithm<String> {
@Override
public String doSharding(Collection<String> collection, PreciseShardingValue<String> shardingValue) {
if(StringUtil.isEmpty(shardingValue.getValue())){
throw new CommonException("precise sharding value is null");
}
String suffix = String.valueOf(Math.abs((long) shardingValue.hashCode())) % collection.size());
for (String tableName : collection) {
if (tableName.endsWith(suffix)) {
return tableName;
}
}
throw new UnsupportedOperationException();
}
@Override
public Collection<String> doSharding(Collection<String> collection, RangeShardingValue<String> rangeShardingValue) {
return collection;
}
@Override
public Properties getProps() {
return null;
}
@Override
public void init(Properties properties) {
}
//返回的算法名字
public String getType() {
return "MY_HASH_MOD";
}
}
复合字段算法
public class ComplexShardingAlgorithm implements ComplexKeysShardingAlgorithm {
@Override
public Collection<String> doSharding(Collection collection, ComplexKeysShardingValue complexKeysShardingValue) {
// 返回真实表名集合
List<String> tableNameList = new ArrayList<>();
// 逻辑表名
String logicTableName = complexKeysShardingValue.getLogicTableName();
// 获取分片键的值，算法自己定义
Collection<String> factoryCodes = (Collection<String>) complexKeysShardingValue.getColumnNameAndShardingValuesMap().get("code");
Collection<String> workshopCodes = (Collection<String>) complexKeysShardingValue.getColumnNameAndShardingValuesMap().get("name");
Collection<String> storehouseCodes = (Collection<String>) complexKeysShardingValue.getColumnNameAndShardingValuesMap().get("sex");
Collection<String> materialNos = (Collection<String>) complexKeysShardingValue.getColumnNameAndShardingValuesMap().get("age");
if (ListUtil.isEmpty(factoryCodes)
|| ListUtil.isEmpty(workshopCodes)
|| ListUtil.isEmpty(storehouseCodes)
|| ListUtil.isEmpty(materialNos)) {//分片键缺任何一个字段均返回全部表
for (String tableName : (Collection<String>) collection) {
tableNameList.add(tableName);
}
return tableNameList;//返回全部
}
// 获取真实表名
String realName = getTabel(factoryCodes) + getTabel(workshopCodes)+ getTabel(storehouseCodes);
for (String materialNo : materialNos) {
long abs = Math.abs((long) (realName + materialNo).hashCode());
String tableSuffix = String.valueOf(abs % 10);
for (String tableName : (Collection<String>) collection) {
if (tableName.endsWith("_" + tableSuffix)) {
tableNameList.add(tableName);
}
}
}
return tableNameList;
}
/**
* 获取表名
*
* @param codes
* @return
*/
private String getTabel(Collection<String> names) {
Optional<String> name = names.stream().findFirst();
if (name.isPresent()) {
return name.get();
}
return "";
}
@Override
public Properties getProps() {
return null;
}
@Override
public void init(Properties properties) {
}
//返回的算法名字
public String getType() {
return "MY_COMPLEX_INLINE";
}
}
分库算法
public class PreciseDatabaseShardingAlgorithm implements StandardShardingAlgorithm<LocalDateTime> {
@Override
public String doSharding(Collection<String> collection, PreciseShardingValue<LocalDateTime> preciseShardingValue) {
//对于库的分片collection存放的是所有的库的列表，这里代表ds_2020~dataSource_2022
//配置的分片的sharding-column对应的值
LocalDateTime year = preciseShardingValue.getValue();
if(ObjectUtil.isNull(year)){
throw new UnsupportedOperationException("preciseShardingValue is null");
}
DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy");
//按年路由
for (String each : collection) {
String value = formatter.format(year);//获取到年份
if(each.endsWith(value)){
// //这里返回回去的就是最终需要查询的库名
return each;
}
}
throw new UnsupportedOperationException();
}
@Override
public Collection<String> doSharding(Collection<String> collection, RangeShardingValue<LocalDateTime> rangeShardingValue) {
return collection;
}
@Override
public Properties getProps() {
return null;
}
@Override
public void init(Properties properties) {
}
//返回算法的名字
public String getType() {
return "DATABASE_INLINE";
}
}
自定义算法重点
SPI机制
需要在resources下面写上
META-INF.services.org.apache.shardingsphere.sharding.spi.ShardingAlgorithm
里面的内容写上算法的路径
使用
在需要切换数据源的地方设置数据源DynamicDataSource.setDataSource(自定义的数据源名字)，使用完后记得remove,切换为默认数据源避免出问题
事务
必须在切换数据源后才开启事务，单事务，在事务中切换数据源是不生效的
PS：写出来仅仅为了自己后面能重新看到，如果有好的方法也可以告诉我
![[JavaWeb]【十】web后端开发-SpringBootWeb案例（配置文件）](https://img-blog.csdnimg.cn/9b1d10ea95b44918aa745ef549aeecef.png)
