SpringMVC集成shardingsphere5.0

一、命名空间增加如下图标红所示

XML中增加相关配置

<!--分片策略配置-->

	<sharding:sharding-algorithm id="yearShardingAlgorithm" type="CLASS_BASED">
		<props>
			<prop key="strategy">standard</prop>
			<prop key="algorithmClassName">com.demo.algorithm.YearShardingAlgorithm</prop>
		</props>
	</sharding:sharding-algorithm>




	<sharding:sharding-algorithm id="monthShardingAlgorithm" 





	<sharding:standard-strategy id="yearShardingStrategy" sharding-column="rec_date" algorithm-ref="yearShardingAlgorithm"/>



	<!--分片规则配置-->
	<sharding:rule id="shardingRule">
		<sharding:table-rules>
			<!--表达式ds_${0..1}枚举的数据源名称为主从配置的逻辑数据源名称-->
			<sharding:table-rule logic-table="d_trade_rec" actual-data-nodes="dataSource.d_trade_rec$->{2020..2023}0$->{1..9},dataSource.d_trade_rec$->{2020..2023}1$->{0..2}" table-strategy-ref="yearShardingAlgorithm" />

		</sharding:table-rules>

		<sharding:binding-table-rules>
			<sharding:binding-table-rule logic-tables="s_station_daily"/>
		</sharding:binding-table-rules>
	</sharding:rule>

	<!-- 分库分表 *end* -->

算法相关代码

public class YearShardingAlgorithm implements StandardShardingAlgorithm<String> {
    private static final Logger LOGGER = LoggerFactory.getLogger(YearShardingAlgorithm.class);

    private static AtomicInteger count = new AtomicInteger();

    public YearShardingAlgorithm() {
        LOGGER.info("YearShardingAlgorithm 初始化,{}",count.getAndIncrement());
    }
    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<String> shardingValue) {
        LOGGER.info("YearShardingAlgorithm doSharding availableTargetNames:{}---shardingValue:{}", JSON.toJSONString(availableTargetNames),JSON.toJSONString(shardingValue));
        // 根据配置的分表规则生成目标表的后缀
        String tableExt = shardingValue.getValue().replaceAll(" ","").replaceAll("-","").replaceAll(":","").substring(0, 4);
        for (String availableTableName : availableTargetNames) {
            if (availableTableName.endsWith(tableExt)) {
                return availableTableName;
            }
        }
        return shardingValue.getLogicTableName();
    }

    @Override
    public Collection<String> doSharding(Collection<String> availableTargetNames, RangeShardingValue<String> shardingValue) {
        LOGGER.info("YearShardingAlgorithm doRangeSharding availableTargetNames:{}---shardingValue:{}", JSON.toJSONString(availableTargetNames),JSON.toJSONString(shardingValue));
        Collection<String> collect = new ArrayList<>();
        Range<String> valueRange = shardingValue.getValueRange();
        String start = valueRange.lowerEndpoint().replaceAll(" ","").replaceAll("-","").replaceAll(":","").substring(0,4);
        String end =  valueRange.upperEndpoint().replaceAll(" ","").replaceAll("-","").replaceAll(":","").substring(0,4);
        for (Long i = Long.valueOf(start); i <= Long.valueOf(end); i++) {
            for (String each : availableTargetNames) {
                if (each.endsWith(i + "")) {
                    collect.add(each);
                }
            }
        }
        return collect;
    }

    @Override
    public void init() {

    }

    @Override
    public String getType() {
        return "CLASS_BASED";
    }

    @Override
    public Properties getProps() {
        return null;
    }

    @Override
    public void setProps(Properties props) {
    }
}

需要注意两点:

1、其中的getType返回的值要与xml中sharding-algorithm的type一致

2、在resource目录下创建META-INF\services文件夹,然后创建一个org.apache.shardingsphere.sharding.spi.ShardingAlgorithm文件,内容是算法的全路径名称,如com.demo.algorithm.YearShardingAlgorithm

另外SQL有以下几个需要注意的地方:

1、相关条件需要包括分片字段,范围查询最好包括开始时间和结束时间

2、分片字段不要做计算 比如时间字段使用 date_format(REC_DATE, ‘%Y-%m-%d’)

3、关联查询时,分片表如果有别名,查询时分片字段前要加上别名,否则SQL不会报错,但是会遍历所有分表

暂无评论

发送评论 编辑评论


				
|´・ω・)ノ
ヾ(≧∇≦*)ゝ
(☆ω☆)
(╯‵□′)╯︵┴─┴
 ̄﹃ ̄
(/ω\)
∠( ᐛ 」∠)_
(๑•̀ㅁ•́ฅ)
→_→
୧(๑•̀⌄•́๑)૭
٩(ˊᗜˋ*)و
(ノ°ο°)ノ
(´இ皿இ`)
⌇●﹏●⌇
(ฅ´ω`ฅ)
(╯°A°)╯︵○○○
φ( ̄∇ ̄o)
ヾ(´・ ・`。)ノ"
( ง ᵒ̌皿ᵒ̌)ง⁼³₌₃
(ó﹏ò。)
Σ(っ °Д °;)っ
( ,,´・ω・)ノ"(´っω・`。)
╮(╯▽╰)╭
o(*////▽////*)q
>﹏<
( ๑´•ω•) "(ㆆᴗㆆ)
😂
😀
😅
😊
🙂
🙃
😌
😍
😘
😜
😝
😏
😒
🙄
😳
😡
😔
😫
😱
😭
💩
👻
🙌
🖕
👍
👫
👬
👭
🌚
🌝
🙈
💊
😶
🙏
🍦
🍉
😣
Source: github.com/k4yt3x/flowerhd
颜文字
Emoji
小恐龙
花!
上一篇