當前位置：首頁 > 编程资源 > 编程问答 >内容正文

编程问答

Sharding-JDBC（三）3.1.0版本实践

發布時間：2025/3/21 编程问答 27 豆豆

生活随笔收集整理的這篇文章主要介紹了 Sharding-JDBC（三）3.1.0版本实践小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

一、Sharding-JDBC依賴

<dependency><groupId>io.shardingsphere</groupId><artifactId>sharding-jdbc-core</artifactId><version>3.1.0</version> </dependency>  <dependency><groupId>io.shardingsphere</groupId><artifactId>sharding-jdbc-spring-namespace</artifactId><version>3.1.0</version> </dependency>

?和2.0.3版本相比，依賴的名稱有所改變，不要搞錯了哦。

二、代碼實踐

業務背景就不再介紹了，不了解可移步至Sharding-JDBC（二）2.0.3版本實踐。

如下代碼配置了標準分片策略中的精確分片算法PreciseShardingAlgorithm和Hint分片算法HintShardingAlgorithm。

XML配置：

<?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xmlns:tx="http://www.springframework.org/schema/tx"xmlns:sharding="http://shardingsphere.io/schema/shardingsphere/sharding"xsi:schemaLocation="http://www.springframework.org/schema/beanshttp://www.springframework.org/schema/beans/spring-beans.xsdhttp://www.springframework.org/schema/txhttp://www.springframework.org/schema/tx/spring-tx.xsdhttp://shardingsphere.io/schema/shardingsphere/shardinghttp://shardingsphere.io/schema/shardingsphere/sharding/sharding.xsd"><sharding:standard-strategy id="settlementTableShardingStandardStrategy" sharding-column="pay_serial_number"precise-algorithm-ref="preciseTableShardingAlgorithm"/><sharding:hint-strategy id="settlementHintTableShardingStrategy" algorithm-ref="hintTableShardingAlgorithm"/><sharding:hint-strategy id="settlementHintDatabaseShardingStrategy" algorithm-ref="hintDatabaseShardingAlgorithm"/><sharding:data-source id="shardingDataSource"><sharding:sharding-rule data-source-names="dataSource"><sharding:table-rules><sharding:table-rule logic-table="settlement"table-strategy-ref="settlementTableShardingStandardStrategy"/><sharding:table-rule logic-table="settlement_hint"database-strategy-ref="settlementHintDatabaseShardingStrategy"table-strategy-ref="settlementHintTableShardingStrategy"/></sharding:table-rules></sharding:sharding-rule><sharding:props><prop key="sql.show">true</prop></sharding:props></sharding:data-source><bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager"><property name="dataSource" ref="shardingDataSource"/></bean><tx:annotation-driven/></beans>

精確分片算法：

package org.cellphone.finance.repo.sharding;import com.google.common.collect.Lists; import io.shardingsphere.api.algorithm.sharding.PreciseShardingValue; import io.shardingsphere.api.algorithm.sharding.standard.PreciseShardingAlgorithm; import org.apache.commons.collections.CollectionUtils; import org.cellphone.common.constant.CommonConst; import org.springframework.stereotype.Component;import java.util.Collection;/*** 精確分片算法，屬于標準分片算法，用于處理=和IN的分片* * 使用精確分片算法的前提：分片字段必須存在與SQL中、數據庫表結構中，否則無法使用精確分片算法* * 此分片算法應用于SETTLEMENT數據表，這里是按天分表* * 特別說明：Sharding Jdbc版本：3.1.0* * Created by on 2018/4/9.*/ @Component("preciseTableShardingAlgorithm") public class PreciseTableShardingAlgorithm implements PreciseShardingAlgorithm<String> {/*** 精確分片算法** @param availableTargetNames 目標數據源名稱或數據表名稱，注意：是邏輯數據源名或邏輯數據表名，來自SQL* @param shardingValue 分片值，來自SQL中分片字段對應的值* @return 真實數據源名稱或數據表名稱*/@Overridepublic String doSharding(final Collection<String> availableTargetNames, final PreciseShardingValue<String> shardingValue) {// 默認數據表名稱，有可能數據庫中不存在這張表String tableName = "settlement";// 邏輯表名為空，返回默認表名if (CollectionUtils.isEmpty(availableTargetNames))return tableName;// availableTargetNames來自SQL，只有一個元素tableName = Lists.newArrayList(availableTargetNames).get(0);String paySerialNumber = shardingValue.getValue();String suffix = paySerialNumber.substring(5, 13);return tableName + CommonConst.UNDERLINE + suffix;} }

Hint數據源分片算法：

package org.cellphone.finance.repo.sharding;import io.shardingsphere.api.algorithm.sharding.ShardingValue; import io.shardingsphere.api.algorithm.sharding.hint.HintShardingAlgorithm; import org.springframework.stereotype.Component;import java.util.Collection;/*** Sharding Jdbc基于暗示(Hint)的數據分片算法** 使用Sharding Jdbc 3.x版本時，此數據源分片算法這個一定要有！！！* 否則無法正常使用org.cellphone.finance.repo.sharding.HintTableShardingAlgorithm算法* * Created by on 2019/4/25.*/ @Component("hintDatabaseShardingAlgorithm") public class HintDatabaseShardingAlgorithm implements HintShardingAlgorithm {@Overridepublic Collection<String> doSharding(Collection<String> availableTargetNames, ShardingValue shardingValue) {return availableTargetNames;} }

Hint數據表分片算法：

package org.cellphone.finance.repo.sharding;import com.google.common.collect.Lists; import io.shardingsphere.api.algorithm.sharding.ListShardingValue; import io.shardingsphere.api.algorithm.sharding.ShardingValue; import io.shardingsphere.api.algorithm.sharding.hint.HintShardingAlgorithm; import org.apache.commons.collections.CollectionUtils; import org.apache.commons.lang3.StringUtils; import org.apache.commons.lang3.time.DateFormatUtils; import org.apache.commons.lang3.time.DateUtils; import org.cellphone.common.constant.CommonConst; import org.cellphone.common.constant.DateConst; import org.springframework.stereotype.Component;import java.text.ParseException; import java.util.*;/*** Sharding Jdbc基于暗示(Hint)的數據分片算法* 版本：Sharding Jdbc 3.1.0** 官方介紹（2.x版本）：http://shardingsphere.apache.org/document/legacy/2.x/cn/02-guide/hint-sharding-value/* 官方介紹（4.x版本）：https://shardingsphere.apache.org/document/current/cn/manual/sharding-jdbc/usage/hint/* * * * * 使用此算法的背景如下：* 1. SETTLEMENT（支付表）是按時間維度進行分表，該時間取自PAY_SERIAL_NUMBER中的時間數據，非表中的時間字段；* * 2. 使用用戶手機號此類條件查詢數據時，請求參數除傳入業務參數外，* 還需傳入時間段（即分片字段，例：startTime和endTime）以確定分表范圍；* 但是！！！startTime和endTime（即分片字段）不存在SQL中、數據庫表結構中，而存在于外部業務邏輯* * * * 因此，第2點導致無法直接使用Sharding Jdbc的PreciseShardingAlgorithm（精確分片算法）或RangeShardingAlgorithm（范圍分片算法），* 只能使用HintShardingAlgorithm（基于暗示的數據分片算法），該算法的使用場景如下：* 1. 分片字段不存在SQL中、數據庫表結構中，而存在于外部業務邏輯；* * 2. 強制在主庫進行某些數據操作。* * * Created by on 2019/4/25.*/ @Component("hintTableShardingAlgorithm") public class HintTableShardingAlgorithm implements HintShardingAlgorithm {/*** 分片算法** @param availableTargetNames 邏輯數據庫名稱或邏輯數據表名稱* @param shardingValue 用來確定分表的參數* @return 實際數據表名稱列表，SQL實際操作的數據表*/@Overridepublic Collection<String> doSharding(Collection<String> availableTargetNames, ShardingValue shardingValue) {String realTableName = StringUtils.EMPTY;for (String each : availableTargetNames) {if (StringUtils.isNotBlank(each)) {// 基于hint的邏輯表名：settlement_hintrealTableName = each.replace("_hint", StringUtils.EMPTY);break;}}List<String> tables = new ArrayList<>();ListShardingValue<String> listShardingValue = (ListShardingValue<String>) shardingValue;List<String> list = Lists.newArrayList(listShardingValue.getValues());// 缺少確定分表的參數，無法確定具體分表，直接返回真實表名稱if (CollectionUtils.isEmpty(list)) {tables.add(realTableName);return tables;}// 拆分分表參數，此參數值來自：com.fcbox.manage.core.repo.FcBoxPostRepository.queryFcBoxPosts()String[] queryTime = list.get(0).split(CommonConst.UNDERLINE);Date startTime, endTime;try {startTime = DateUtils.parseDate(queryTime[0], DateConst.DATE_FORMAT_NORMAL);endTime = DateUtils.parseDate(queryTime[1], DateConst.DATE_FORMAT_NORMAL);} catch (ParseException e) {// 分表參數解析錯誤，無法確定具體分表，直接返回真實表名稱tables.add(realTableName);return tables;}Calendar calendar = Calendar.getInstance();// 組織startTime和endTime時段范圍內的分表while (startTime.getTime() <= endTime.getTime()) {tables.add(realTableName + CommonConst.UNDERLINE + DateFormatUtils.format(startTime, DateConst.DATE_FORMAT_YYYY_MM_DD));calendar.setTime(startTime);calendar.add(Calendar.DATE, 1);startTime = calendar.getTime();}return tables;} }

與Hint分片算法對應的Java查詢方法?settlementMapper.selectByExample(example)：

public List<Settlement> querySettlements(SettlementExample example, String startTime, String endTime) {// 組織查詢時間，傳入org.cellphone.finance.repo.sharding.HintTableShardingAlgorithm分片算法中以確認具體分表String queryTime = startTime + CommonConst.UNDERLINE + endTime;// 獲取HintManagerHintManager hintManager = HintManager.getInstance();/** 添加數據源分片鍵值，使用Sharding Jdbc 3.x版本一定要添加數據源分片鍵值，否則無法使用HintTableShardingAlgorithm分片算法* 若無分庫，addDatabaseShardingValue方法的value字段隨意填充* 若有分庫，addDatabaseShardingValue方法的value字段填充實際參數值*/hintManager.addDatabaseShardingValue("settlement_hint", StringUtils.EMPTY);// 添加數據表分片鍵值hintManager.addTableShardingValue("settlement_hint", queryTime);List<Settlement> settlements = settlementMapper.selectByExample(example);// 清除分片鍵值hintManager.close();return settlements;}

以及該查詢方法對應的SQL語句：

select * from settlement_hint t where t.pay_serial_number = ?

?單元測試代碼：

@Test public void test003QuerySettlements() throws ParseException {String startTime = "2018-04-03 00:00:00", endTime = "2018-04-05 00:00:00";SettlementExample example = new SettlementExample();SettlementExample.Criteria criteria = example.createCriteria();criteria.andPaySerialNumberEqualTo(paySerialNumber);List<Settlement> settlements = repository.querySettlements(example, startTime, endTime);Assert.assertEquals("136********", settlements.get(0).getUserMobile()); }

三、源碼分析

和2.0.3版本相比，3.1.0版本的路由入口變成了?io.shardingsphere.core.routing.type.standard.StandardRoutingEngine#route()?，但基本上區別不大，僅僅是多了一步需要確定路由的真實數據源，盡管數據源只有一個，也需要顯式配置數據源路由算法。代碼中標注了注釋的部分都是路由代碼比較核心的部分。

/** Copyright 2016-2018 shardingsphere.io.* * Licensed under the Apache License, Version 2.0 (the "License");* you may not use this file except in compliance with the License.* You may obtain a copy of the License at** http://www.apache.org/licenses/LICENSE-2.0** Unless required by applicable law or agreed to in writing, software* distributed under the License is distributed on an "AS IS" BASIS,* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.* See the License for the specific language governing permissions and* limitations under the License.* */package io.shardingsphere.core.routing.type.standard;import com.google.common.base.Optional; import com.google.common.base.Preconditions; import io.shardingsphere.api.algorithm.sharding.ShardingValue; import io.shardingsphere.core.hint.HintManagerHolder; import io.shardingsphere.core.optimizer.condition.ShardingCondition; import io.shardingsphere.core.optimizer.condition.ShardingConditions; import io.shardingsphere.core.optimizer.insert.InsertShardingCondition; import io.shardingsphere.core.routing.strategy.ShardingStrategy; import io.shardingsphere.core.routing.strategy.hint.HintShardingStrategy; import io.shardingsphere.core.routing.type.RoutingEngine; import io.shardingsphere.core.routing.type.RoutingResult; import io.shardingsphere.core.routing.type.RoutingTable; import io.shardingsphere.core.routing.type.TableUnit; import io.shardingsphere.core.rule.BindingTableRule; import io.shardingsphere.core.rule.DataNode; import io.shardingsphere.core.rule.ShardingRule; import io.shardingsphere.core.rule.TableRule; import lombok.RequiredArgsConstructor;import java.util.ArrayList; import java.util.Collection; import java.util.Collections; import java.util.LinkedHashSet; import java.util.LinkedList; import java.util.List;/*** Standard routing engine.** @author zhangliang* @author maxiaoguang* @author panjuan*/ @RequiredArgsConstructor public final class StandardRoutingEngine implements RoutingEngine {private final ShardingRule shardingRule;private final String logicTableName;private final ShardingConditions shardingConditions;/*** 相比2.0.3版本，精簡成了1行代碼** @return 路由結果*/@Overridepublic RoutingResult route() {return generateRoutingResult(getDataNodes(shardingRule.getTableRuleByLogicTableName(logicTableName)));}private RoutingResult generateRoutingResult(final Collection<DataNode> routedDataNodes) {RoutingResult result = new RoutingResult();for (DataNode each : routedDataNodes) {TableUnit tableUnit = new TableUnit(each.getDataSourceName());tableUnit.getRoutingTables().add(new RoutingTable(logicTableName, each.getTableName()));result.getTableUnits().getTableUnits().add(tableUnit);}return result;}/*** 獲取數據節點，即真實表** @param tableRule 從XML讀取的table rule* @return 數據節點列表*/private Collection<DataNode> getDataNodes(final TableRule tableRule) {// 判斷是否是通過Hint分片策略進行路由if (isRoutingByHint(tableRule)) {return routeByHint(tableRule);}if (isRoutingByShardingConditions(tableRule)) {return routeByShardingConditions(tableRule);}return routeByMixedConditions(tableRule);}/*** 判斷是否是通過Hint分片策略進行路由* * 數據源分片策略和數據表分片策略都必須是HintShardingStrategy，這意味者必須顯式配置數據源Hint分片策略和數據表Hint分片策略** @param tableRule 從XML讀取的table rule* @return*/private boolean isRoutingByHint(final TableRule tableRule) {return shardingRule.getDatabaseShardingStrategy(tableRule) instanceof HintShardingStrategy && shardingRule.getTableShardingStrategy(tableRule) instanceof HintShardingStrategy;}/*** 通過Hint分片策略進行路由** @param tableRule* @return*/private Collection<DataNode> routeByHint(final TableRule tableRule) {return route(tableRule, getDatabaseShardingValuesFromHint(), getTableShardingValuesFromHint());}private boolean isRoutingByShardingConditions(final TableRule tableRule) {return !(shardingRule.getDatabaseShardingStrategy(tableRule) instanceof HintShardingStrategy || shardingRule.getTableShardingStrategy(tableRule) instanceof HintShardingStrategy);}private Collection<DataNode> routeByShardingConditions(final TableRule tableRule) {return shardingConditions.getShardingConditions().isEmpty() ? route(tableRule, Collections.<ShardingValue>emptyList(), Collections.<ShardingValue>emptyList()): routeByShardingConditionsWithCondition(tableRule);}private Collection<DataNode> routeByShardingConditionsWithCondition(final TableRule tableRule) {Collection<DataNode> result = new LinkedList<>();for (ShardingCondition each : shardingConditions.getShardingConditions()) {Collection<DataNode> dataNodes = route(tableRule, getShardingValuesFromShardingConditions(shardingRule.getDatabaseShardingStrategy(tableRule).getShardingColumns(), each),getShardingValuesFromShardingConditions(shardingRule.getTableShardingStrategy(tableRule).getShardingColumns(), each));reviseShardingConditions(each, dataNodes);result.addAll(dataNodes);}return result;}private Collection<DataNode> routeByMixedConditions(final TableRule tableRule) {return shardingConditions.getShardingConditions().isEmpty() ? routeByMixedConditionsWithHint(tableRule) : routeByMixedConditionsWithCondition(tableRule);}private Collection<DataNode> routeByMixedConditionsWithCondition(final TableRule tableRule) {Collection<DataNode> result = new LinkedList<>();for (ShardingCondition each : shardingConditions.getShardingConditions()) {Collection<DataNode> dataNodes = route(tableRule, getDatabaseShardingValues(tableRule, each), getTableShardingValues(tableRule, each));reviseShardingConditions(each, dataNodes);result.addAll(dataNodes);}return result;}private Collection<DataNode> routeByMixedConditionsWithHint(final TableRule tableRule) {if (shardingRule.getDatabaseShardingStrategy(tableRule) instanceof HintShardingStrategy) {return route(tableRule, getDatabaseShardingValuesFromHint(), Collections.<ShardingValue>emptyList());}return route(tableRule, Collections.<ShardingValue>emptyList(), getTableShardingValuesFromHint());}private List<ShardingValue> getDatabaseShardingValues(final TableRule tableRule, final ShardingCondition shardingCondition) {ShardingStrategy dataBaseShardingStrategy = shardingRule.getDatabaseShardingStrategy(tableRule);return isGettingShardingValuesFromHint(dataBaseShardingStrategy)? getDatabaseShardingValuesFromHint() : getShardingValuesFromShardingConditions(dataBaseShardingStrategy.getShardingColumns(), shardingCondition);}private List<ShardingValue> getTableShardingValues(final TableRule tableRule, final ShardingCondition shardingCondition) {ShardingStrategy tableShardingStrategy = shardingRule.getTableShardingStrategy(tableRule);return isGettingShardingValuesFromHint(tableShardingStrategy)? getTableShardingValuesFromHint() : getShardingValuesFromShardingConditions(tableShardingStrategy.getShardingColumns(), shardingCondition);}private boolean isGettingShardingValuesFromHint(final ShardingStrategy shardingStrategy) {return shardingStrategy instanceof HintShardingStrategy;}/*** 從HintManagerHolder中獲取數據源分片值** @return 數據源分片值列表*/private List<ShardingValue> getDatabaseShardingValuesFromHint() {// getDatabaseShardingValue方法實現有點惡心，不兼容大小寫...Optional<ShardingValue> shardingValueOptional = HintManagerHolder.getDatabaseShardingValue(logicTableName);return shardingValueOptional.isPresent() ? Collections.singletonList(shardingValueOptional.get()) : Collections.<ShardingValue>emptyList();}private List<ShardingValue> getTableShardingValuesFromHint() {// getTableShardingValue方法實現有點惡心，不兼容大小寫...Optional<ShardingValue> shardingValueOptional = HintManagerHolder.getTableShardingValue(logicTableName);return shardingValueOptional.isPresent() ? Collections.singletonList(shardingValueOptional.get()) : Collections.<ShardingValue>emptyList();}private List<ShardingValue> getShardingValuesFromShardingConditions(final Collection<String> shardingColumns, final ShardingCondition shardingCondition) {List<ShardingValue> result = new ArrayList<>(shardingColumns.size());for (ShardingValue each : shardingCondition.getShardingValues()) {Optional<BindingTableRule> bindingTableRule = shardingRule.findBindingTableRule(logicTableName);if ((logicTableName.equals(each.getLogicTableName()) || bindingTableRule.isPresent() && bindingTableRule.get().hasLogicTable(logicTableName))&& shardingColumns.contains(each.getColumnName())) {result.add(each);}}return result;}/*** 路由，獲取真實表列表** @param tableRule 從XML讀取的table rule* @param databaseShardingValues 數據源分片值* @param tableShardingValues 數據表分片值* @return 真實表列表*/private Collection<DataNode> route(final TableRule tableRule, final List<ShardingValue> databaseShardingValues, final List<ShardingValue> tableShardingValues) {Collection<String> routedDataSources = routeDataSources(tableRule, databaseShardingValues);Collection<DataNode> result = new LinkedList<>();for (String each : routedDataSources) {result.addAll(routeTables(tableRule, each, tableShardingValues));}return result;}/*** 路由到真實數據源** @param tableRule 從XML讀取的table rule* @param databaseShardingValues 數據源分片值* @return 真實數據源列表*/private Collection<String> routeDataSources(final TableRule tableRule, final List<ShardingValue> databaseShardingValues) {Collection<String> availableTargetDatabases = tableRule.getActualDatasourceNames();if (databaseShardingValues.isEmpty()) {return availableTargetDatabases;}Collection<String> result = new LinkedHashSet<>(shardingRule.getDatabaseShardingStrategy(tableRule).doSharding(availableTargetDatabases, databaseShardingValues));Preconditions.checkState(!result.isEmpty(), "no database route info");return result;}/*** 路由到真實數據表，和2.0.3版本沒啥區別** @param tableRule 從XML讀取的table rule* @param routedDataSource 已確認好的數據源* @param tableShardingValues 數據表分片值* @return 真實表列表*/private Collection<DataNode> routeTables(final TableRule tableRule, final String routedDataSource, final List<ShardingValue> tableShardingValues) {Collection<String> availableTargetTables = tableRule.getActualTableNames(routedDataSource);Collection<String> routedTables = new LinkedHashSet<>(tableShardingValues.isEmpty() ? availableTargetTables: shardingRule.getTableShardingStrategy(tableRule).doSharding(availableTargetTables, tableShardingValues));Preconditions.checkState(!routedTables.isEmpty(), "no table route info");Collection<DataNode> result = new LinkedList<>();for (String each : routedTables) {result.add(new DataNode(routedDataSource, each));}return result;}private void reviseShardingConditions(final ShardingCondition each, final Collection<DataNode> dataNodes) {if (each instanceof InsertShardingCondition) {((InsertShardingCondition) each).getDataNodes().addAll(dataNodes);}} }

分析到此，Sharding-JDBC 3.1.0版本可以支持分片鍵不存在于SQL中和數據表結構中的使用場景。但3.1.0版本還有一個比較惡心的地方，Sharding-JDBC在初始化時，會連接數據庫獲取數據表的元數據，包括需要水平切分的表和不需水平切分的表。代碼如下：

總結

以上是生活随笔為你收集整理的Sharding-JDBC（三）3.1.0版本实践的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

上一篇： Sharding-JDBC（二）2.0.
下一篇： SCRUM敏捷开发官方权威指南