阿飛Javaer躁愿，轉(zhuǎn)載請(qǐng)注明原創(chuàng)出處，謝謝来候！

本篇文章講解如何在ssm（spring营搅、springmvc剧防、mybatis）結(jié)構(gòu)的程序上集成sharding-jdbc（版本為1.5.4.1）進(jìn)行分庫(kù)分表峭拘；
假設(shè)分庫(kù)分表行為如下：

• 將auth_user表分到4個(gè)庫(kù)（user_0~user_3）中鸡挠；
• 其他表不進(jìn)行分庫(kù)分表拣展，保留在default_db庫(kù)中缔逛；

1. POM配置

以spring配置文件為例褐奴，新增如下POM配置：

<dependency>
    <groupId>com.dangdang</groupId>
    <artifactId>sharding-jdbc-core</artifactId>
    <version>1.5.4.1</version>
</dependency>

<dependency>
    <groupId>com.dangdang</groupId>
    <artifactId>sharding-jdbc-config-spring</artifactId>
    <version>1.5.4.1</version>
</dependency>

此次集成sharding-jdbc以1.5.4.1版本為例辅搬，如果是2.x版本的sharding-jdbc脖旱，那么需要將坐標(biāo)<groupId>com.dangdang</groupId>修改為<groupId>io.shardingjdbc</groupId>；另外币旧，如果是yaml配置竿滨，那么需要將坐標(biāo)<artifactId>sharding-jdbc-config-spring</artifactId>修改為<artifactId>sharding-jdbc-config-yaml</artifactId>于游；

2. 配置數(shù)據(jù)源

spring-datasource.xml配置所有需要的數(shù)據(jù)源如下--auth_user分庫(kù)分表后需要的4個(gè)庫(kù)user_0~user_3倾剿，以及不分庫(kù)分表的默認(rèn)庫(kù)default_db：

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-beans.xsd">

    <!-- 配置數(shù)據(jù)源 -->
    <bean id="sj_ds_0" class="com.alibaba.druid.pool.DruidDataSource"
          init-method="init" destroy-method="close">
        <property name="url" value="${sj_user_0.url}" />
        <property name="username" value="${sj_user_0.username}" />
        <property name="password" value="${sj_user_0.password}" />
        <!--druid配置優(yōu)化可以放在這里-->
    </bean>

    <!-- 配置數(shù)據(jù)源 -->
    <bean id="sj_ds_1" class="com.alibaba.druid.pool.DruidDataSource"
          init-method="init" destroy-method="close">
        <property name="url" value="${sj_user_1.url}" />
        <property name="username" value="${sj_user_1.username}" />
        <property name="password" value="${sj_user_1.password}" />
        <!--druid配置優(yōu)化可以放在這里-->
    </bean>

    <!-- 配置數(shù)據(jù)源 -->
    <bean id="sj_ds_2" class="com.alibaba.druid.pool.DruidDataSource"
          init-method="init" destroy-method="close">
        <property name="url" value="${sj_user_2.url}" />
        <property name="username" value="${sj_user_2.username}" />
        <property name="password" value="${sj_user_2.password}" />
        <!--druid配置優(yōu)化可以放在這里-->
    </bean>

    <!-- 配置數(shù)據(jù)源 -->
    <bean id="sj_ds_3" class="com.alibaba.druid.pool.DruidDataSource"
          init-method="init" destroy-method="close">
        <property name="url" value="${sj_user_3.url}" />
        <property name="username" value="${sj_user_3.username}" />
        <property name="password" value="${sj_user_3.password}" />
        <!--druid配置優(yōu)化可以放在這里-->
    </bean>

    <!-- 配置數(shù)據(jù)源 -->
    <bean id="sj_ds_default" class="com.alibaba.druid.pool.DruidDataSource"
          init-method="init" destroy-method="close">
        <property name="url" value="${sj_default.url}" />
        <property name="username" value="${sj_default.username}" />
        <property name="password" value="${sj_default.password}" />
        <!--druid配置優(yōu)化可以放在這里-->
    </bean>
</beans>

properties配置文件內(nèi)容如下：

sj_user_0.driver=com.mysql.jdbc.Driver
sj_user_0.url=jdbc:mysql://localhost:3306/user_0
sj_user_0.username=root
sj_user_0.password=RootAfei_1

sj_user_1.driver=com.mysql.jdbc.Driver
sj_user_1.url=jdbc:mysql://localhost:3306/user_1
sj_user_1.username=root
sj_user_1.password=RootAfei_1

sj_user_2.driver=com.mysql.jdbc.Driver
sj_user_2.url=jdbc:mysql://localhost:3306/user_2
sj_user_2.username=root
sj_user_2.password=RootAfei_1

sj_user_3.driver=com.mysql.jdbc.Driver
sj_user_3.url=jdbc:mysql://localhost:3306/user_3
sj_user_3.username=root
sj_user_3.password=RootAfei_1

sj_default.driver=com.mysql.jdbc.Driver
sj_default.url=jdbc:mysql://localhost:3306/default_db
sj_default.username=root
sj_default.password=RootAfei_1

3. 集成sharding數(shù)據(jù)源

spring-sharding.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:rdb="http://www.dangdang.com/schema/ddframe/rdb"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
                        http://www.springframework.org/schema/beans/spring-beans.xsd
                        http://www.dangdang.com/schema/ddframe/rdb
                        http://www.dangdang.com/schema/ddframe/rdb/rdb.xsd">

    <!--數(shù)據(jù)庫(kù)sharding策略：以id列進(jìn)行sharding芹缔，sharding邏輯在AuthUserDatabaseShardingAlgorithm中-->
    <rdb:strategy id="databaseStrategy" sharding-columns="id"
                  algorithm-class="com.crt.fin.ospsso.service.shardingjdbc.AuthUserDatabaseShardingAlgorithm" />
    <!--auth_user表sharding策略:無(wú) -->

    <!--定義sharding數(shù)據(jù)源-->
    <rdb:data-source id="shardingDataSource">
        <!--default-data-source指定默認(rèn)數(shù)據(jù)源, 即沒有在<rdb:table-rules>申明的logic-table表,
        即不需要分庫(kù)分表的表, 全部走默認(rèn)數(shù)據(jù)源-->
        <rdb:sharding-rule data-sources="sj_ds_0,sj_ds_1,sj_ds_2,sj_ds_3,sj_ds_default"
                           default-data-source="sj_ds_default">
            <rdb:table-rules>
                <!--auth_user只分庫(kù)不分表, actual-tables的值一定要加上:sj_ds_${0..3}.,
                否則會(huì)遍歷data-sources, 而sj_ds_default中并沒有auth_user表 -->
                <rdb:table-rule logic-table="auth_user" actual-tables="sj_ds_${0..3}.auth_user"
                                database-strategy="databaseStrategy"/>
            </rdb:table-rules>
            <rdb:default-database-strategy sharding-columns="none" algorithm-class="com.dangdang.ddframe.rdb.sharding.api.strategy.database.NoneDatabaseShardingAlgorithm"/>
            <rdb:default-table-strategy sharding-columns="none" algorithm-class="com.dangdang.ddframe.rdb.sharding.api.strategy.table.NoneTableShardingAlgorithm"/>
        </rdb:sharding-rule>
        <rdb:props>
            <prop key="sql.show">true</prop>
            <prop key="executor.size">2</prop>
        </rdb:props>
    </rdb:data-source>

    <!-- 配置sqlSessionFactory -->
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <!---datasource交給sharding-jdbc托管-->
        <property name="dataSource" ref="shardingDataSource"/>
        <property name="mapperLocations" value="classpath*:mybatis/*Mapper.xml"/>
    </bean>

    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <property name="basePackage" value="com.crt.fin.ospsso.dal.mapper"/>
        <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/>
    </bean>

</beans>

說(shuō)明：spring-sharding.xml配置的分庫(kù)分表規(guī)則：auth_user表分到id為sj_ds_${0..3}的四個(gè)庫(kù)中，表名保持不變芝硬；其他表在id為sj_ds_default庫(kù)中轧房，不分庫(kù)也不分表；集成sharding-jdbc的核心就是將SqlSessionFactoryBean需要的dataSource屬性修改為shardingDataSource迟赃，把數(shù)據(jù)源交給sharding-jdbc處理捺氢；

分庫(kù)邏輯AuthUserDatabaseShardingAlgorithm的代碼很簡(jiǎn)單剪撬，源碼如下：

/**
 * @author wangzhenfei9
 * @version 1.0.0
 * @since 2018年02月08日
 */
public class AuthUserDatabaseShardingAlgorithm implements SingleKeyDatabaseShardingAlgorithm<Long> {

    private final Logger logger = LoggerFactory.getLogger(this.getClass());

    private static final int SHARDING_NUMBER = 4;

    @Override
    public String doEqualSharding(final Collection<String> availableTargetNames, final ShardingValue<Long> shardingValue) {
        for (String each : availableTargetNames) {
            if (each.endsWith(shardingValue.getValue() % SHARDING_NUMBER + "")) {
                logger.debug("the target database name: {}", each);
                return each;
            }
        }
        throw new UnsupportedOperationException();
    }

    @Override
    public Collection<String> doInSharding(final Collection<String> availableTargetNames, final ShardingValue<Long> shardingValue) {
        Collection<String> result = new LinkedHashSet<>(availableTargetNames.size());
        Collection<Long> values = shardingValue.getValues();
        for (Long value : values) {
            for (String each : availableTargetNames) {
                if (each.endsWith(value % SHARDING_NUMBER + "")) {
                    result.add(each);
                }
            }
        }
        return result;
    }

    @Override
    public Collection<String> doBetweenSharding(final Collection<String> availableTargetNames, final ShardingValue<Long> shardingValue) {
        Collection<String> result = new LinkedHashSet<>(availableTargetNames.size());
        Range<Long> range = shardingValue.getValueRange();
        for (Long value = range.lowerEndpoint(); value <= range.upperEndpoint(); value++) {
            for (String each : availableTargetNames) {
                if (each.endsWith(value % SHARDING_NUMBER + "")) {
                    result.add(each);
                }
            }
        }
        return result;
    }
}

這段代碼參考sharding-jdbc源碼中DatabaseShardingAlgorithm.java接口的實(shí)現(xiàn)即可，例如ModuloDatabaseShardingAlgorithm.java梨水；

4. 注意事項(xiàng)

無(wú)法識(shí)別sharding-jdbc分庫(kù)分表規(guī)則inline-expression問題舅世，例如：
<rdb:table-rule logic-table="auth_user" actual-tables="sj_ds_${0..3}.auth_user" database-strategy="databaseStrategy"/>

1. 根本原因：
   根本原因是spring把${}當(dāng)做占位符雏亚，${0..3}這種表達(dá)式摩钙，spring會(huì)嘗試去properties文件中找key為0..3的屬性胖笛。但是這里是sharding-jdbc分庫(kù)分表規(guī)則的inline表達(dá)式长踊，需要spring忽略這種行為。否則會(huì)拋出異常:
   java.lang.IllegalArgumentException: Could not resolve placeholder '0..3' in value "sj_ds_${0..3}.auth_user"

2. 解決辦法：
   配置: <property name="ignoreUnresolvablePlaceholders" value="true"/>
   或者: <context:property-placeholder ****** ignore-unresolvable="true" />

5. Main測(cè)試

Main.java用來(lái)測(cè)試分庫(kù)分表是否OK，其源碼如下：

/**
 * @author wangzhenfei9
 * @version 1.0.0
 * @since 2018年02月08日
 */
public class Main {

    public static void main(String[] args) {
        ApplicationContext context = new ClassPathXmlApplicationContext(
                "/META-INF/spring/spring-*.xml");

        // auth_user有進(jìn)行分庫(kù)莉擒，
        AuthUserMapper authUserMapper = context.getBean(AuthUserMapper.class);
        AuthUser authUser = authUserMapper.selectByPrimaryKey(7L);
        System.out.println("-----> The auth user: "+JSON.toJSONString(authUser));

        // user_permission沒有分庫(kù)分表
        UserPermissionMapper userPermissionMapper = context.getBean(UserPermissionMapper.class);
        UserPermission userPermission = userPermissionMapper.selectPermissionByUsername("wangzhenfei", "FINANCE_WALLET");
        System.out.println("-----< The user permission: "+JSON.toJSONString(userPermission));
    }

}

AuthUserMapper.selectByPrimaryKey()和UserPermissionMapper.selectPermissionByUsername()的代碼和沒有分庫(kù)分表的代碼完全一樣涨冀；

6. 遺留問題

Main方法測(cè)試鹿鳖，或者啟動(dòng)服務(wù)后的調(diào)用測(cè)試都沒有問題翅帜，但是通過(guò)junit測(cè)試用例訪問就會(huì)拋出異常命满，作為一個(gè)待解決的遺留問題：

org.springframework.beans.factory.NoUniqueBeanDefinitionException: No qualifying bean of type 'javax.sql.DataSource' available: expected single matching bean but found 6: sj_ds_0,sj_ds_1,sj_ds_2,sj_ds_3,sj_ds_default,shardingDataSource