看到了当当开源的Sharding-JDBC组件，它可以在几乎不修改代码的情况下完成分库分表的实现。摘抄其中一段介绍：

      Sharding-JDBC直接封装JDBC API，可以理解为增强版的JDBC驱动，旧代码迁移成本几乎为零：

• 可适用于任何基于java的ORM框架，如：JPA, Hibernate, Mybatis, Spring JDBC Template或直接使用JDBC。
• 可基于任何第三方的数据库连接池，如：DBCP, C3P0, BoneCP, Druid等。
• 理论上可支持任意实现JDBC规范的数据库。虽然目前仅支持MySQL，但已有支持Oracle，SQLServer，DB2等数据库的计划。

先做一个最简单的试用，不做分库，仅做分表。选择数据表bead_information，首先复制成三个表：bead_information_0、bead_information_1、bead_information_2

测试实现过程

前提：已经实现srping+mybatis对单库单表做增删改查的项目。

 1、修改pom.xml增加dependency

        <dependency>
            <groupId>com.dangdang</groupId>
            <artifactId>sharding-jdbc-core</artifactId>
            <version>1.4.2</version>
        </dependency>
        <dependency>
            <groupId>com.dangdang</groupId>
            <artifactId>sharding-jdbc-config-spring</artifactId>
            <version>1.4.0</version>
        </dependency>

2、新建一个sharding-jdbc.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:context="http://www.springframework.org/schema/context" 
    xmlns:tx="http://www.springframework.org/schema/tx"
    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.springframework.org/schema/tx 
                        http://www.springframework.org/schema/tx/spring-tx.xsd
                        http://www.springframework.org/schema/context 
                        http://www.springframework.org/schema/context/spring-context.xsd
                        http://www.dangdang.com/schema/ddframe/rdb 
                        http://www.dangdang.com/schema/ddframe/rdb/rdb.xsd">
    
 
    
     <!-- 配置数据源 -->
    <bean name="dataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
        <property name="url" value="jdbc:mysql://localhost:3306/beadhouse" />
        <property name="username" value="root" />
        <property name="password" value="123456" />
    </bean>
                 
    <rdb:strategy id="tableShardingStrategy" sharding-columns="id" algorithm-class="com.springdemo.utill.MemberSingleKeyTableShardingAlgorithm"/>
    
    <rdb:data-source id="shardingDataSource">
        <rdb:sharding-rule data-sources="dataSource">
            <rdb:table-rules>
                <rdb:table-rule logic-table="bead_information" actual-tables="bead_information_${0..2}"  table-strategy="tableShardingStrategy"/>
            </rdb:table-rules>
        </rdb:sharding-rule>
    </rdb:data-source>
    
    <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="shardingDataSource" />
    </bean>
</beans>

3、将文件引入spring配置文件中。

需要修改几个地方，把sqlSessionFactory和transactionManager原来关联的dataSource统一修改为shardingDataSource（这一步作用就是把数据源全部托管给sharding去管理）

4、实现分表（分库）逻辑，我们的分表逻辑类需要实现SingleKeyTableShardingAlgorithm接口的三个方法doBetweenSharding、doEqualSharding、doInSharding

（取模除数需要按照自己需求改变，我这里分3个表，所以除以3）

import java.util.Collection;
import java.util.LinkedHashSet;
import com.dangdang.ddframe.rdb.sharding.api.ShardingValue;
import com.dangdang.ddframe.rdb.sharding.api.strategy.table.SingleKeyTableShardingAlgorithm;
import com.google.common.collect.Range;
public class MemberSingleKeyTableShardingAlgorithm implements SingleKeyTableShardingAlgorithm<Integer> {

    @Override
    public Collection<String> doBetweenSharding(Collection<String> tableNames, ShardingValue<Integer> shardingValue) {
        Collection<String> result = new LinkedHashSet<String>(tableNames.size());
        Range<Integer> range = (Range<Integer>) shardingValue.getValueRange();
        for (Integer i = range.lowerEndpoint(); i <= range.upperEndpoint(); i++) {
            Integer modValue = i % 3;
            String modStr = modValue < 3 ? "" + modValue : modValue.toString();
            for (String each : tableNames) {
                if (each.endsWith(modStr)) {
                    result.add(each);
                }
            }
        }
        return result;
    }
    @Override
    public String doEqualSharding(Collection<String> tableNames, ShardingValue<Integer> shardingValue) {
        Integer modValue = shardingValue.getValue() % 3;
        String modStr = modValue < 3 ? "" + modValue : modValue.toString();
        for (String each : tableNames) {
            if (each.endsWith(modStr)) {
                return each;
            }
        }
        throw new IllegalArgumentException();
    }
    @Override
    public Collection<String> doInSharding(Collection<String> tableNames, ShardingValue<Integer> shardingValue) {
        Collection<String> result = new LinkedHashSet<String>(tableNames.size());
        for (Integer value : shardingValue.getValues()) {
            Integer modValue = value % 3;
            String modStr = modValue < 3 ? "" + modValue : modValue.toString();
            for (String tableName : tableNames) {
                if (tableName.endsWith(modStr)) {
                    result.add(tableName);
                }
            }
        }
        return result;
    }
}

5、配置完成，可以实现增删改查测试。

相比于Spring基于AbstractRoutingDataSource实现的分库分表功能，Sharding jdbc在单库单表扩展到多库多表时，兼容性方面表现的更好一点。例如，spring实现的分库分表sql写法如下：

select id, name, price, publish, intro
from book${tableIndex}
where id = #{id,jdbcType=INTEGER}

sql中的表名book需要加一个分表的后缀tableIndex，也就是需要在sql注入的参数中指定插入哪个表。相比，Sharding jdbc在这一块封装的更好一点。其sql中，根本不需要指定tableIndex，而是根据分库分表策略自动路由。

select id, name, price, publish, intro
from book
where id = #{id,jdbcType=INTEGER}

Sharding jdbc的这种特性，在水平扩展的时候无疑更具有吸引力。试想一下，一个项目开发一段时间后，单库单表数据量急剧上升，需要分库分表解决数据库的访问压力。而现有sql配置都是基于单库单表实现的，如果基于spring的AbstractRoutingDataSource实现，需要修改每一个相关表的sql，修改涉及较多地方，出错概率较大。而基于Sharding jdbc实现时，sql无需修改，只需要在spring中添加Sharding jdbc的相关配置即可，减少了修改面，大大简化分库分表的实现难度。

那么，Sharding jdbc是如何实现这种分库分表的逻辑呢？下面我们用一段简单、易懂的代码描述Sharding jdbc的原理。

通常我们在写一段访问数据库的数据时，逻辑是这样的：

ClassPathXmlApplicationContext ctx = new ClassPathXmlApplicationContext(“application.xml”);
DataSource dataSource = ctx.getBean(“dataSource”, DataSource.class);
Connection connection = dataSource.getConnection();

String sql = “select id, name, price, publish, intro from book where id = 111″;
PreparedStatement ps = connection.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
// handle ResultSet…

Sharding jdbc是基于JDBC协议实现的，当我们获得dataSource时，这个dataSource是Sharding jdbc自己定义的一个SpringShardingDataSource类型的数据源，该数据源在返回getConnection()及prepareStatement()时，分别返回ShardingConnection和ShardingPreparedStatement的实例对象。然后在executeQuery()时，ShardingPreparedStatement做了这样的一件事：

根据逻辑sql，经过分库分表策略逻辑计算，获得分库分表的路由结果SQLRouteResult；
SQLRouteResult中包含真实的数据源以及转换后的真正sql，利用真实的数据源去执行获得ResultSet；
将ResultSet列表封装成一个可以顺序读的ResultSet对象IteratorReducerResultSet。

class ShardingPreparedStatement implements PreparedStatement {

@Override
public ResultSet executeQuery() throws SQLException {
List<SQLRouteResult> routeResults = routeSql(logicSql);

List<ResultSet> resultSets = new ArrayList<>(routeResults.size());
for (SQLRouteResult routeResult : routeResults) {
PreparedStatement ps = routeResult.getDataSource().getConnection.prepareStatement(routeResult.getParsedSql());
ResultSet rs = ps.executeQuery();
resultSets.add(rs);
}

return new IteratorReducerResultSet(resultSets);
}
…..

}

其中，分库分表策略的sql路由过程，我们将Sharding jdbc中的相关代码全部抽出来，放到一起来观看这个过程的实现：

// 环境准备
@SuppressWarnings(“resource”)
ClassPathXmlApplicationContext ctx = new ClassPathXmlApplicationContext(“application.xml”);
SpringShardingDataSource dataSource = ctx.getBean(SpringShardingDataSource.class);
Field field = SpringShardingDataSource.class.getSuperclass().getDeclaredField(“shardingContext”);
field.setAccessible(true);
ShardingContext sctx = (ShardingContext)field.get(dataSource);
ShardingRule shardingRule = sctx.getShardingRule();

String logicSql = “select id, name, price, publish, intro from book where id = ?”;
List<Object> parameters = new ArrayList<>();
parameters.add(2000);

// sql解析
MySqlStatementParser parser = new MySqlStatementParser(logicSql);
MySQLSelectVisitor visitor = new MySQLSelectVisitor();
SQLStatement statement = parser.parseStatement();
visitor.getParseContext().setShardingRule(shardingRule);
statement.accept(visitor);

SQLParsedResult parsedResult = visitor.getParseContext().getParsedResult();
if (visitor.getParseContext().isHasOrCondition()) {
new OrParser(statement, visitor).fillConditionContext(parsedResult);
}
visitor.getParseContext().mergeCurrentConditionContext();
System.out.println(“Parsed SQL result: ” + parsedResult);
System.out.println(“Parsed SQL: ” + visitor.getSQLBuilder());
parsedResult.getRouteContext().setSqlBuilder(visitor.getSQLBuilder());
parsedResult.getRouteContext().setSqlStatementType(SQLStatementType.SELECT);

// 分库分表路由
SQLRouteResult result = new SQLRouteResult(parsedResult.getRouteContext().getSqlStatementType(), parsedResult.getMergeContext(), parsedResult.getGeneratedKeyContext());
for (ConditionContext each : parsedResult.getConditionContexts()) {
Collection<Table> tables = parsedResult.getRouteContext().getTables();
final Set<String> logicTables = new HashSet<>();
tables.forEach(a -> logicTables.add(a.getName()));

SingleTableRouter router = new SingleTableRouter(shardingRule,
logicTables.iterator().next(),
each,
parsedResult.getRouteContext().getSqlStatementType());

RoutingResult routingResult = router.route();

// sql改写 –> routingResult.getSQLExecutionUnits()
// —> SingleRoutingTableFactor.replaceSQL(sqlBuilder).buildSQL()
// 结果合并
result.getExecutionUnits().addAll(routingResult.getSQLExecutionUnits(parsedResult.getRouteContext().getSqlBuilder()));
}
// amendSQLAccordingToRouteResult(parsedResult, parameters, result);
for (SQLExecutionUnit each : result.getExecutionUnits()) {
System.out.println(each.getDataSource() + ” ” + each.getSql() + ” ” + parameters);
}

• 准备环境。由于Sharding jdbc分库分表中ShardingRule这个类是贯穿整个路由过程，我们在Spring中写好Sharding jdbc的配置，利用反射获取一个这个对象。（Sharding jdbc版本以及配置，在文章最后列出，方便debug这个过程）
• sql解析。Sharding jdbc使用阿里的Druid库解析sql。在这个过程中，Sharding jdbc实现了一个自己的sql解析内容缓存容器SqlBuilder。当语法分析中解析到一个表名的时候，在SqlBuilder中缓存一个sql相关的逻辑表名的token。并且，Sharding jdbc会将sql按照语义解析为多个segment。例如，”select id, name, price, publish, intro from book where id = ？”将解析为，”select id, name, price, publish, intro | from | book | where | id = ?”。
• 分库分表路由。根据ShardingRule中指定的分库分表列的参数值，以及分库分表策略，实行分库分表，得到一个RoutingResult 。RoutingResult 中包含一个真实数据源，以及逻辑表名和实际表名。
• sql改写。在SqlBuilder中，查找sql中解析的segment，将和逻辑表名一致的segment替换成实际表名。（segment中可以标注该地方是不是表名）
  以上代码执行结果如下：

Parsed SQL result: SQLParsedResult(routeContext=RouteContext(tables=[Table(name=book, alias=Optional.absent())], sqlStatementType=null, sqlBuilder=null), generatedKeyContext=GeneratedKeyContext(columns=[], columnNameToIndexMap={}, valueTable={}, rowIndex=0, columnIndex=0, autoGeneratedKeys=0, columnIndexes=null, columnNames=null), conditionContexts=[ConditionContext(conditions={})], mergeContext=MergeContext(orderByColumns=[], groupByColumns=[], aggregationColumns=[], limit=null))
Parsed SQL: SELECT id, name, price, publish, intro FROM [Token(book)] WHERE id = ?
dataSource1 SELECT id, name, price, publish, intro FROM book_00 WHERE id = ? [2000]
dataSource2 SELECT id, name, price, publish, intro FROM book_02 WHERE id = ? [2000]
dataSource1 SELECT id, name, price, publish, intro FROM book_02 WHERE id = ? [2000]
dataSource2 SELECT id, name, price, publish, intro FROM book_01 WHERE id = ? [2000]
dataSource0 SELECT id, name, price, publish, intro FROM book_00 WHERE id = ? [2000]
dataSource0 SELECT id, name, price, publish, intro FROM book_01 WHERE id = ? [2000]
dataSource2 SELECT id, name, price, publish, intro FROM book_00 WHERE id = ? [2000]
dataSource1 SELECT id, name, price, publish, intro FROM book_01 WHERE id = ? [2000]
dataSource0 SELECT id, name, price, publish, intro FROM book_02 WHERE id = ? [2000]

实际上，我们可以用更通俗易懂的代码表示sql改写的这个过程：

String logicSql = “select id, name, price, publish, intro from book where id = 111″;
MySqlStatementParser parser = new MySqlStatementParser(logicSql);
SQLStatement statement = parser.parseStatement();
MySQLSimpleVisitor visitor = new MySQLSimpleVisitor();
statement.accept(visitor);

String logicTable = “book”;
String realTable = “book_00″;
String token = “\\$\\{” + logicTable + “\\}”;

String sqlBuilder = visitor.getAppender().toString();
String sql = sqlBuilder.replaceAll(token, realTable);

System.out.println(sqlBuilder);
System.out.println(sql);

 

结果如下：

SELECT id, name, price, publish, intro
FROM ${book}
WHERE id = 111
SELECT id, name, price, publish, intro
FROM book_00
WHERE id = 111

以上，大致将Sharding jdbc的原理及实现过程介绍了一下，如果想要了解正真的实现过程和细节，还需要对照代码仔细推敲。

本文的实现环境：

<dependency>
<groupId>com.dangdang</groupId>
<artifactId>sharding-jdbc-core</artifactId>
<version>1.4.2</version>
</dependency>
<dependency>
<groupId>com.dangdang</groupId>
<artifactId>sharding-jdbc-config-spring</artifactId>
<version>1.4.0</version>
</dependency>

application.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:context=”http://www.springframework.org/schema/context”
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-4.0.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-4.0.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx-4.0.xsd
http://www.dangdang.com/schema/ddframe/rdb
http://www.dangdang.com/schema/ddframe/rdb/rdb.xsd”>

<context:property-placeholder location=”classpath:jdbc.properties” ignore-unresolvable=”true” />

<bean id=”dataSource0″ class=”org.springframework.jdbc.datasource.DriverManagerDataSource”>
<property name=”driverClassName” value=”com.mysql.jdbc.Driver” />
<property name=”url” value=”${jdbc.mysql.url0}” />
<property name=”username” value=”${jdbc.mysql.username0}” />
<property name=”password” value=”${jdbc.mysql.password0}” />
</bean>

<bean id=”dataSource1″ class=”org.springframework.jdbc.datasource.DriverManagerDataSource”>
<property name=”driverClassName” value=”${driver}” />
<property name=”url” value=”${jdbc.mysql.url1}” />
<property name=”username” value=”${jdbc.mysql.username1}” />
<property name=”password” value=”${jdbc.mysql.password1}” />
</bean>

<bean id=”dataSource2″ class=”org.springframework.jdbc.datasource.DriverManagerDataSource”>
<property name=”driverClassName” value=”${driver}” />
<property name=”url” value=”${jdbc.mysql.url2}” />
<property name=”username” value=”${jdbc.mysql.username2}” />
<property name=”password” value=”${jdbc.mysql.password2}” />
</bean>

<!– sharding jdbc –>
<rdb:strategy id=”tableShardingStrategy” sharding-columns=”id”
algorithm-class=”com.wy.sharding.MemberSingleKeyTableShardingAlgorithm” />

<rdb:data-source id=”shardingDataSource”>
<rdb:sharding-rule data-sources=”dataSource0,dataSource1,dataSource2″>
<rdb:table-rules>
<rdb:table-rule logic-table=”book”
actual-tables=”book_0${0..2}”
table-strategy=”tableShardingStrategy”/>
</rdb:table-rules>
</rdb:sharding-rule>
</rdb:data-source>
</beans>

MemberSingleKeyTableShardingAlgorithm.java:

public class MemberSingleKeyTableShardingAlgorithm implements SingleKeyTableShardingAlgorithm<Integer> {

public String doEqualSharding(Collection<String> availableTargetNames, ShardingValue<Integer> shardingValue) {
String routeDBSuffix = getRouteDBSuffix(shardingValue.getValue());
for (String each : availableTargetNames) {
if (each.endsWith(routeDBSuffix)) {
return each;
}
}
throw new IllegalArgumentException();
}

public Collection<String> doInSharding(Collection<String> availableTargetNames, ShardingValue<Integer> shardingValue) {
Collection<String> result = new LinkedHashSet<String>(availableTargetNames.size());
for (int value : shardingValue.getValues()) {
String routeDBSuffix = getRouteDBSuffix(value);
for (String tableName : availableTargetNames) {
if (tableName.endsWith(routeDBSuffix)) {
result.add(tableName);
}
}
}
return result;
}

public Collection<String> doBetweenSharding(Collection<String> availableTargetNames,
ShardingValue<Integer> shardingValue) {
Collection<String> result = new LinkedHashSet<String>(availableTargetNames.size());
Range<Integer> range = (Range<Integer>) shardingValue.getValueRange();
for (int i = range.lowerEndpoint(); i <= range.upperEndpoint(); i++) {
String routeDBSuffix = getRouteDBSuffix(i);
for (String each : availableTargetNames) {
if (each.endsWith(routeDBSuffix)) {
result.add(each);
}
}
}
return result;
}

public String getRouteDBSuffix(Integer shardingCode) {
int modValue = shardingCode % 3;
return “0” + modValue;
}

}