apache shardingsphere 【实现oracle数据库按月分片】

mac2025-10-10 1

pom文件配置：

注意4.0.0-RC1版本最低使用ojdbc6版本。

<dependency> <groupId>org.apache.shardingsphere</groupId> <artifactId>sharding-jdbc-spring-namespace</artifactId> <version>4.0.0-RC1</version> </dependency> <dependency> <groupId>com.oracle</groupId> <artifactId>ojdbc6</artifactId> <version>11.2.0.2.0</version> </dependency>

数据源配置：

这里配置了两个数据源，一个是默认数据源dataSource主要用于非分片的查询，这里配置要注意，spring bean 配置多数据源，启动会提示冲突，要指定首选，用关键字 primary="true" 来配置。

下面是分片数据源

集成分片数据源及规则：

分片算法有以下四种

精确分片算法

对应PreciseShardingAlgorithm，用于处理使用单一键作为分片键的=与IN进行分片的场景。需要配合StandardShardingStrategy使用。

范围分片算法

对应RangeShardingAlgorithm，用于处理使用单一键作为分片键的BETWEEN AND、>、<、>=、<=进行分片的场景。需要配合StandardShardingStrategy使用。

复合分片算法

对应ComplexKeysShardingAlgorithm，用于处理使用多键作为分片键进行分片的场景，包含多个分片键的逻辑较复杂，需要应用开发者自行处理其中的复杂度。需要配合ComplexShardingStrategy使用。

Hint分片算法

对应HintShardingAlgorithm，用于处理使用Hint行分片的场景。需要配合HintShardingStrategy使用。

我这里主要实现精确分片算法和范围分片算法。见代码precise-algorithm-ref，range-algorithm-ref。

注意：同一个分片字段如果同时实现精确分片和范围分片 shardingsphere 会根据传入的sql自动匹配相应规则。

<bean id="rangeModuloTableShardingAlgorithm" class="com.demo.common.dal.shardingAlgorithm.RangeModuloShardingTableAlgorithm" /> <bean id="preciseModuloShardingTableAlgorithm" class="com.demo.common.dal.shardingAlgorithm.PreciseModuloShardingTableAlgorithm" />  <sharding:standard-strategy id="moduloShardingTableAlgorithm" sharding-column="SETT_DATE" precise-algorithm-ref="preciseModuloShardingTableAlgorithm" range-algorithm-ref="rangeModuloTableShardingAlgorithm" />  <sharding:data-source id="shardingDataSource"> <sharding:sharding-rule data-source-names="ds_0"> <sharding:table-rules> <sharding:table-rule logic-index="1" logic-table="RECONCILIATION_TRAN_INFO" actual-data-nodes="ds_0.RECONCILIATION_TRAN_INFO_$->{1901..1903}" table-strategy-ref="moduloShardingTableAlgorithm" /> </sharding:table-rules> <sharding:binding-table-rules> <sharding:binding-table-rule logic-tables="RECONCILIATION_TRAN_INFO" /> </sharding:binding-table-rules> </sharding:sharding-rule> <sharding:props> <prop key="sql.show">true</prop> </sharding:props> </sharding:data-source>

PreciseModuloShardingTableAlgorithm 自定义精确分片的规则。

package com.demo.common.dal.shardingAlgorithm; import java.util.Collection; import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm; import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue; public final class PreciseModuloShardingTableAlgorithm implements PreciseShardingAlgorithm<Long>{ public String doSharding(final Collection<String> tableNames, final PreciseShardingValue<Long> shardingValue) { for (String each : tableNames) { String s = String.valueOf(shardingValue.getValue()).substring(2, 6); if (each.endsWith(s)) { return each; } } throw new UnsupportedOperationException(); } }

RangeModuloShardingTableAlgorithm 自定义范围分片的规则，这里sql定义之between 会优先匹配范围分片。这里主要实现了按月自定义分片的时下。

package com.demo.common.dal.shardingAlgorithm; import com.google.common.collect.Lists; import com.google.common.collect.Range; import org.apache.commons.lang3.time.DateFormatUtils; import org.apache.commons.lang3.time.DateUtils; import org.apache.shardingsphere.api.sharding.standard.RangeShardingAlgorithm; import org.apache.shardingsphere.api.sharding.standard.RangeShardingValue; import java.util.ArrayList; import java.util.Collection; import java.util.Date; import java.util.List; public final class RangeModuloShardingTableAlgorithm implements RangeShardingAlgorithm<Long> { @Override public Collection<String> doSharding(Collection<String> availableTargetNames, RangeShardingValue<Long> shardingValue) { Collection<String> collect = new ArrayList<String>(); Range<Long> valueRange = shardingValue.getValueRange(); String s1 = String.valueOf(valueRange.lowerEndpoint()).substring(2, 6); String s2 = String.valueOf(valueRange.upperEndpoint()).substring(2, 6); List<String> betweenDate = getBetweenDate(DateUtils.parseDate(s1, "yyyyMM"), DateUtils.parseDate(s2, "yyyyMM")); for (String each : availableTargetNames) { for (int i = 0; i < betweenDate.size(); i++) { if (each.endsWith(s1)) { collect.add(each); } } } return collect; } private List<String> getBetweenDate(Date startDate, Date endDate) { List<String> dateList = Lists.newArrayList(); boolean flag = true; String endDate_string = DateFormatUtils.format(endDate, "yyyyMM"); String startDate_string = DateFormatUtils.format(startDate, "yyyyMM"); dateList.add(startDate_string); Date nowDate = getDateAddMouth(startDate); while (flag) { String newdate_string = DateFormatUtils.format(nowDate, "yyyyMM"); dateList.add(newdate_string); if (newdate_string.equals(endDate_string)) { flag = false; }else { nowDate = getDateAddMouth(nowDate); } }; return dateList; } private Date getDateAddMouth(Date date) { return DateUtils.addMonths(date, 1); } }

最新回复(0)