网站用什么做,seo关键词优化推广哪家好,一级a做爰片免费网站 小说,长沙有实力seo优化公司Springboot中sharding-jdbc的API模式并使用自定义算法
可配合AbstractRoutingData使用切换数据源 程序用到了AbstractRoutingData来切换数据源#xff08;数据源是自定义的格式编写并没有用springboot的自动装配的格式写#xff09;#xff0c;但是又用到sharding-jdbc进行…Springboot中sharding-jdbc的API模式并使用自定义算法
可配合AbstractRoutingData使用切换数据源 程序用到了AbstractRoutingData来切换数据源数据源是自定义的格式编写并没有用springboot的自动装配的格式写但是又用到sharding-jdbc进行分库分页如果直接引用sharding-jdbc-spring-boot-starter会自动装配它自己默认的数据源dataSource导致我们自己写的数据源失效。所以我们需要用API的模式把sharding-jdbc的数据源dataSource放入我们自己的写的AbstractRoutingData里面来。 POM文件添加 dependencygroupIdorg.apache.shardingsphere/groupIdartifactIdshardingsphere-jdbc-core/artifactIdversion5.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 MapObject, Object targetDataSources new HashMap();private static final ThreadLocalString 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(MapString, DataSource targetDataSources) {//设置默认数据源//super.setDefaultTargetDataSource(targetDataSources.get(default));this.targetDataSources.putAll(targetDataSources);//设置数据源super.setTargetDataSources(this.targetDataSources);super.afterPropertiesSet();}public MapObject, 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);PrimaryBeanpublic DataSource shardingdataSource() throws SQLException, IOException {//获取AbstractRoutingData对象DynamicDataSource chooseDataSource DynamicDataSource.getInstance();//获取自己配置文件上的普通数据源该方法忽略展示key为数据库的名字value为数据源MapString, DataSource targetDataSources this.getTargetDataSources();/*生成数据源的样式使用DruidDataSourcePOM文件记得加入也可以使用其他数据源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 MapString,String propertyMap;Overrideprotected void processProperties(ConfigurableListableBeanFactory beanFactoryToProcess, Properties props) throws BeansException {super.processProperties(beanFactoryToProcess, props);propertyMap new HashMapString, 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 MapString, DataSource createDataSourceMap(ListString datasourceNames){MapString, DataSource dataSourceMapnew HashMap();for (int i 0; i datasourceNames.size(); i) {MapString, 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_0my_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 tableRulenew 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 tableRulenew 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(ListString 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 MapString, DataSource createDataSourceMap(ListString datasourceNames){MapString, DataSource dataSourceMapnew HashMap();for (int i 0; i datasourceNames.size(); i) {MapString, 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 tableRulenew 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 tableRulenew 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 ArrayListString(){{add(datasourceNames);}}),Collections.singleton(createShardingRuleConfigurationOnlyTable()),properties);}}自定义分库分片算法 标准分片算法
public final class HashModShardingAlgorithm implements StandardShardingAlgorithmString {Overridepublic String doSharding(CollectionString collection, PreciseShardingValueString 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 CollectionString doSharding(CollectionString collection, RangeShardingValueString 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 CollectionString doSharding(Collection collection, ComplexKeysShardingValue complexKeysShardingValue) {// 返回真实表名集合ListString tableNameList new ArrayList();// 逻辑表名String logicTableName complexKeysShardingValue.getLogicTableName();// 获取分片键的值算法自己定义CollectionString factoryCodes (CollectionString) complexKeysShardingValue.getColumnNameAndShardingValuesMap().get(code);CollectionString workshopCodes (CollectionString) complexKeysShardingValue.getColumnNameAndShardingValuesMap().get(name);CollectionString storehouseCodes (CollectionString) complexKeysShardingValue.getColumnNameAndShardingValuesMap().get(sex);CollectionString materialNos (CollectionString) complexKeysShardingValue.getColumnNameAndShardingValuesMap().get(age);if (ListUtil.isEmpty(factoryCodes)|| ListUtil.isEmpty(workshopCodes)|| ListUtil.isEmpty(storehouseCodes)|| ListUtil.isEmpty(materialNos)) {//分片键缺任何一个字段均返回全部表for (String tableName : (CollectionString) 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 : (CollectionString) collection) {if (tableName.endsWith(_ tableSuffix)) {tableNameList.add(tableName);}}}return tableNameList;}/*** 获取表名** param codes* return*/private String getTabel(CollectionString names) {OptionalString 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 StandardShardingAlgorithmLocalDateTime {Overridepublic String doSharding(CollectionString collection, PreciseShardingValueLocalDateTime 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 CollectionString doSharding(CollectionString collection, RangeShardingValueLocalDateTime 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写出来仅仅为了自己后面能重新看到如果有好的方法也可以告诉我
