springMVC中多数据源切换的两种主流实现方法（二）

最新推荐文章于 2024-08-13 11:49:14 发布

志大洋

最新推荐文章于 2024-08-13 11:49:14 发布

阅读量1.1k

点赞数 1

分类专栏： #开发 Java生态圈

本文链接：https://blog.csdn.net/wzg725/article/details/81871743

版权

#开发 Java生态圈专栏收录该内容

8 篇文章 0 订阅

订阅专栏

说到多数据源，使用的场景一般有：1，主从数据库切换；2，多租户间数据逻辑隔离或数据库负载均衡；

本文后继的例子均以SSM架构为基础讲解，如你的框架不适合，请参考思路即可。

---------------------------------------------------------------------------------------------------------------------

针对场景2的方案，结合本公司电商相关的项目，简要指出关键点。

本文的解决方案，主要依靠第三方分布式数据库中间件Sharding-Sphere。

		<sharding-sphere.version>3.0.0.M1</sharding-sphere.version>
		... ...
		<dependency>
		    <groupId>io.shardingsphere</groupId>
		    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
		    <version>${sharding-sphere.version}</version>
		</dependency>
		<!-- for spring namespace -->
		<dependency>
		    <groupId>io.shardingsphere</groupId>
		    <artifactId>sharding-jdbc-spring-namespace</artifactId>
		    <version>${sharding-sphere.version}</version>
		</dependency>

数据源的配置文件：

<?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:p="http://www.springframework.org/schema/p"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:tx="http://www.springframework.org/schema/tx"
       xmlns:sharding="http://shardingsphere.io/schema/shardingsphere/sharding"
       xsi:schemaLocation="http://www.springframework.org/schema/beans 
                        http://www.springframework.org/schema/beans/spring-beans.xsd
                        http://shardingsphere.io/schema/shardingsphere/sharding 
                        http://shardingsphere.io/schema/shardingsphere/sharding/sharding.xsd
                        http://www.springframework.org/schema/context
                        http://www.springframework.org/schema/context/spring-context.xsd
                        http://www.springframework.org/schema/tx
                        http://www.springframework.org/schema/tx/spring-tx.xsd">

    <!-- 数据源公用配置 -->
    <bean id="parentDataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
		<property name= "testWhileIdle" value="true" />
		<property name= "testOnBorrow" value="false" />
		<property name= "testOnReturn" value="false" />
		<property name= "timeBetweenEvictionRunsMillis" value="30000" />
    </bean>

    <!-- MySQL数据源 -->
    <bean id="ds_00" parent="parentDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver" />
	<property name="url" value="${jdbc.xxx_retailorder_1-master.url}" />
	<property name="username" value="${jdbc.xxx_retailorder_1-master.username}" />
	<property name="password" value="${jdbc.xxx_retailorder_1-master.password}" />
	<property name="initialSize" value="${jdbc.xxx_retailorder_1-master.initialSize}" />
	<property name="maxActive" value="${jdbc.xxx_retailorder_1-master.maxActive}" />
	<property name="maxIdle" value="${jdbc.xxx_retailorder_1-master.maxIdle}" />
	<property name="minIdle" value="${jdbc.xxx_retailorder_1-master.minIdle}" />
	<property name="maxWait" value="${jdbc.xxx_retailorder_1-master.maxWait}" />
	<property name= "validationQuery" value="${jdbc.xxx_retailorder_1-master.validationQuery}" />
    </bean>

    <bean id="ds_01" parent="parentDataSource">
	... ...
    </bean>
    <bean id="ds_02" parent="parentDataSource">
	... ...
    </bean>
    <bean id="ds_03" parent="parentDataSource">
	... ...
    </bean>

    <bean id="preciseModuloDatabaseShardingAlgorithm" class="com.xxx.retailorder.sharding.algorithm.SingleKeyModuloDatabaseShardingAlgorithmByOrderId" />
    <sharding:standard-strategy id="databaseShardingStrategyByOrderId" sharding-column="RETAIL_ORDER_ID" precise-algorithm-ref="preciseModuloDatabaseShardingAlgorithm" />

    <sharding:data-source id="dynamicDataSource">
        <sharding:sharding-rule data-source-names="ds_00,ds_01,ds_02,ds_03">
            <sharding:table-rules>
            	 <sharding:table-rule logic-table="RETAIL_ORDER" actual-data-nodes="ds_0$->{0..3}.RETAIL_ORDER"
                                database-strategy-ref="databaseShardingStrategyByOrderId" generate-key-column-name="ID" />
                 <sharding:table-rule logic-table="ITEM_DETAILS" actual-data-nodes="ds_0$->{0..3}.ITEM_DETAILS" 
                                database-strategy-ref="databaseShardingStrategyByOrderId" generate-key-column-name="ID" />
                 ... ...
            </sharding:table-rules>
        </sharding:sharding-rule>
    </sharding:data-source>
</beans>

##Springboot项目的配置方法

package com.xxxx.retailorder.work.config;

import java.sql.SQLException;
import java.util.Collection;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.Map;
import java.util.Properties;

import javax.sql.DataSource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.context.annotation.PropertySource;
import org.springframework.core.env.Environment;

import com.google.common.collect.Maps;
import com.xxxx.retailorder.common.db.sharding.strategy.PaymentDatabaseShardingAlgorithm;
import com.xxxx.retailorder.common.db.sharding.strategy.RetailOrderIdDatabaseShardingAlgorithm;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

import io.shardingjdbc.core.api.ShardingDataSourceFactory;
import io.shardingjdbc.core.api.config.ShardingRuleConfiguration;
import io.shardingjdbc.core.api.config.TableRuleConfiguration;
import io.shardingjdbc.core.api.config.strategy.ComplexShardingStrategyConfiguration;
import io.shardingjdbc.core.api.config.strategy.StandardShardingStrategyConfiguration;

/**
 * 加载多数据源
 */
@Configuration
@ConfigurationProperties
@PropertySource(value = {"classpath:jdbc.properties"})
public class MultiDataSource {
    
    private static final Logger LOG = LoggerFactory.getLogger(MultiDataSource.class);
    
    @Autowired 
    private Environment env;

    /**
     * 多数据源别名列表
     */
    private static String[] DS_NAMES = {"ds_00","ds_01","ds_02","ds_03"} ;
    
    /**
     * 加载创建dataSource
     *
     * @return
     */
    private Map<String, DataSource> createDataSourceMap() {
        HashMap<String, DataSource> dataSourceMap = Maps.newHashMap();
        // 得到系统环境
        String activeEnv = env.getProperty("spring.profiles.active");
        // 未配置或者指定生产环境时，直接使用指定数据源配置
        if (activeEnv == null || "prd".equalsIgnoreCase(activeEnv)) {
            for (int i = 1; i <= DS_NAMES.length; i++) {
                dataSourceMap.put(DS_NAMES[i - 1], getDataSourceItem(i));
            }
        }
        // 测试环境下，数据源00，01对应1数据库，数据源02，03对应2数据库
        else {
            for (int i = 1; i <= DS_NAMES.length; i++) {
                if (i <=2 ) {
                    dataSourceMap.put(DS_NAMES[i - 1], getDataSourceItem(1));
                }
                else if (i <= 4) {
                    dataSourceMap.put(DS_NAMES[i - 1], getDataSourceItem(2));
                }
            }
        }
        return dataSourceMap;
    }
    
    // 根据指定数据源配置标识生成数据源对象
    private DataSource getDataSourceItem(int i) {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl(env.getProperty("jdbc.xxxx_retailorder_" + i + "-master.url"));
        config.setUsername(env.getProperty("jdbc.xxxx_retailorder_" + i + "-master.username"));
        config.setPassword(env.getProperty("jdbc.xxxx_retailorder_" + i + "-master.password"));
        config.setMinimumIdle(Integer.parseInt(env.getProperty("jdbc.xxxx_retailorder_" + i + "-master.minIdle")));
        config.setMaximumPoolSize(Integer.parseInt(env.getProperty("jdbc.xxxx_retailorder_" + i + "-master.maxIdle")));
        config.setConnectionTimeout(Long.valueOf(env.getProperty("jdbc.xxxx_retailorder_" + i + "-master.maxWait")));
        config.setConnectionTestQuery(env.getProperty("jdbc.xxxx_retailorder_" + i + "-master.validationQuery"));
        config.setDriverClassName("com.mysql.jdbc.Driver");

        HikariDataSource ds = new HikariDataSource(config);
        return ds;
    }

    /**
     * 与订单号相关的需要分库的表名
     */
    private final static String[] ORDER_ID_RELEVANT_TABLES = { "ORDER_TABLE", "ORDER_PRODUCT_TABLE"};

    /**
     * 取得数据源对象
     * 
     * @return 数据源对象
     * @throws SQLException 数据库操作异常时
     */
    @Bean
    @Primary
    public DataSource getShardingDataSource() throws SQLException {
        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
        Collection<TableRuleConfiguration> tableRuleConfigs = shardingRuleConfig.getTableRuleConfigs();

        tableRuleConfigs.addAll(getOrderTableRuleConfiguration());
        tableRuleConfigs.addAll(getPaymentTableRuleConfiguration());
        shardingRuleConfig.setDefaultDatabaseShardingStrategyConfig(new StandardShardingStrategyConfiguration(
                "ORDER_ID", RetailOrderIdDatabaseShardingAlgorithm.class.getName()));
        return ShardingDataSourceFactory.createDataSource(createDataSourceMap(), shardingRuleConfig,
                new HashMap<String, Object>(), new Properties());
    }

    /**
     * 跟订单号相关的表的分库规则配置
     * 
     * @param logicTable 订单相关的逻辑表名
     * @return 分库配置规则
     */
    private Collection<TableRuleConfiguration> getOrderTableRuleConfiguration() {
        Collection<TableRuleConfiguration> tableRuleConfigs = new LinkedList<>();
        for (String logicTable : ORDER_ID_RELEVANT_TABLES) {
            TableRuleConfiguration orderTableRuleConfig = new TableRuleConfiguration();
            orderTableRuleConfig.setLogicTable(logicTable);
            orderTableRuleConfig.setKeyGeneratorColumnName("ID");
            orderTableRuleConfig.setKeyGeneratorClass("io.shardingjdbc.core.keygen.DefaultKeyGenerator");
            tableRuleConfigs.add(orderTableRuleConfig);
        }
        return tableRuleConfigs;
    }

    /**
     * 跟预支付相关的表的分库规则配置。</br>
     * 关连操作中如果有订单号的值，优先采用订单号的分库规则，否则使用支付请求流水号的分库规则。</br>
     * 
     * @param logicTable 预支付相关的逻辑表名
     * @return 分库配置规则
     */
    private Collection<TableRuleConfiguration> getPaymentTableRuleConfiguration() {
        Collection<TableRuleConfiguration> tableRuleConfigs = new LinkedList<>();
        TableRuleConfiguration paymentReqConf = new TableRuleConfiguration();
        paymentReqConf.setLogicTable("PAYMENT_REQUEST");
        paymentReqConf.setKeyGeneratorColumnName("ID");
        paymentReqConf.setKeyGeneratorClass("io.shardingjdbc.core.keygen.DefaultKeyGenerator");
        paymentReqConf.setDatabaseShardingStrategyConfig(new ComplexShardingStrategyConfiguration("PAY_REQ_ID",
                PaymentDatabaseShardingAlgorithm.class.getName()));
        tableRuleConfigs.add(paymentReqConf);

        TableRuleConfiguration paymentConf = new TableRuleConfiguration();
        paymentConf.setLogicTable("PAYMENT");
        paymentConf.setKeyGeneratorColumnName("ID");
        paymentConf.setKeyGeneratorClass("io.shardingjdbc.core.keygen.DefaultKeyGenerator");
        paymentConf.setDatabaseShardingStrategyConfig(new ComplexShardingStrategyConfiguration(
                "ORDER_ID,PAY_REQ_ID", PaymentDatabaseShardingAlgorithm.class.getName()));
        tableRuleConfigs.add(paymentConf);

        TableRuleConfiguration paymnetReqDetailsConf = new TableRuleConfiguration();
        paymnetReqDetailsConf.setLogicTable("PAYMENT_REQUEST_DETAILS");
        paymnetReqDetailsConf.setKeyGeneratorColumnName("ID");
        paymnetReqDetailsConf.setKeyGeneratorClass("io.shardingjdbc.core.keygen.DefaultKeyGenerator");
        paymnetReqDetailsConf.setDatabaseShardingStrategyConfig(new ComplexShardingStrategyConfiguration(
                "ORDER_ID,PAY_REQ_ID", PaymentDatabaseShardingAlgorithm.class.getName()));
        tableRuleConfigs.add(paymnetReqDetailsConf);

        return tableRuleConfigs;
    }
}

分库算法

package com.xxx.retailorder.sharding.algorithm;

import java.util.Collection;

import com.xxx.retailorder.bo.RetailOrderNoOnRedisWorker;

import io.shardingsphere.core.api.algorithm.sharding.PreciseShardingValue;
import io.shardingsphere.core.api.algorithm.sharding.standard.PreciseShardingAlgorithm;

/**
 * 依赖零售订单号的精确分库算法
 * 
 * @author vollen
 */
public class SingleKeyModuloDatabaseShardingAlgorithmByOrderId implements PreciseShardingAlgorithm<String> {

    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<String> shardingValue) {
        for (String each : availableTargetNames) {
            if(each.endsWith(RetailOrderNoOnRedisWorker.getDataSourceID(shardingValue.getValue()))) {
                return each;
            }
        }
        throw new UnsupportedOperationException("分库失败（订单号不符合规范）");
    }

}

注：由于RetailOrderNoOnRedisWorker是订单号生成代码，不方便公布。这里只能解释一下该getDataSourceID方法。该方法就是根据订单号取得数据源信息。入参：订单号，订单号本身包含数据源信息。假如订单号的第10，11位表示数据源，如201808209901，该方法就返回01。上述分库算法就会得到ds_01的数据源。

至于DAO等方法，这里就不贴出。

该方案有两个缺点：

一、无法做到动态生成数据源对象。（如果有哪位大大知道，请留言交流吧）

二、要求业务的持久层中，无论增删改查，都必须含有订单号这样的特征字段。如果没有，结果往往不正确。

对于缺陷二，Sharding-Sphere已经有解决之道。该中间件提供一种强制路由的入口。由持久层外部指定分片数据，其实也就是本空间（一）的思路一样，使用ThreadLocal管理分片键值。

例如，突然一个需求在下单前要求查询用户下当天购买某商品的数量，来做购买限制。此时还没有订单号，但数据库已经有多个，该去哪个库查询呢？因为下单系统已经完全依赖订单号来进行数据分片的，已经没有办法再根据用户去分片了吧。此时就轮到“强制路由”（Hint）粉墨登场了。

        // 强制路由开始
        HintManager hintManager = HintManager.getInstance();
        hintManager.addDatabaseShardingValue("ORDER_TABLE", "ORDER_ID", RetailOrderNoOnRedisWorker.buildVirtualID(userOnlyId));
        Integer queryLimitNum = retailOrderMapper.queryLimitForOneDay(params);
        // 强制路由结束
        hintManager.close();

我的思路就是利用用户ID生成一个虚拟的订单号，该订单号与正式的订单号的形式一致，只不过不是正规的流水号罢了。