Springboot中sharding-jdbc的API模式并使用自定义算法

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@Beanpublic 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;@Overrideprotected 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的sqlproperties.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> {@Overridepublic 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();}@Overridepublic Collection<String> doSharding(Collection<String> collection, RangeShardingValue<String> rangeShardingValue) {return collection;}@Overridepublic Properties getProps() {return null;}@Overridepublic void init(Properties properties) {}//返回的算法名字public String getType() {return "MY_HASH_MOD";}
}

复合字段算法

public class ComplexShardingAlgorithm implements ComplexKeysShardingAlgorithm {@Overridepublic 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 "";}@Overridepublic Properties getProps() {return null;}@Overridepublic void init(Properties properties) {}//返回的算法名字public String getType() {return "MY_COMPLEX_INLINE";}
}

分库算法

public class PreciseDatabaseShardingAlgorithm implements StandardShardingAlgorithm<LocalDateTime> {@Overridepublic 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();}@Overridepublic Collection<String> doSharding(Collection<String> collection, RangeShardingValue<LocalDateTime> rangeShardingValue) {return collection;}@Overridepublic Properties getProps() {return null;}@Overridepublic 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：写出来仅仅为了自己后面能重新看到，如果有好的方法也可以告诉我