当mysql单表数据量比较大时往往需要分库分表，Sharding-JDBC是当当网开源的数据库分库分表中间件。Sharding-JDBC定位为轻量级java框架，使用客户端直连数据库，以jar包形式提供服务，无proxy代理层，无需额外部署，无其他依赖，DBA也无需改变原有的运维方式。本文主要讲述该框架与spring+mybatis的整合使用。
1.准备工作#
由于是分库分表，所以需要在不同的数据库建立相同的表。分别在sharding_0,sharding_1两个数据库中建立t_user0,t_user1,t_user2三张表，需要用到的SQL语句如下:
Copy
SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS `t_user_0`;
CREATE TABLE `t_user_0` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
`age` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `t_user_1`;
CREATE TABLE `t_user_1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
`age` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `t_user_2`;
CREATE TABLE `t_user_2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
`age` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;项目的整体结构如下图所示：
需要用到的sharding-jdbc的依赖如下所示：
Copy
<dependency> <groupId>io.shardingjdbcgroupId> <artifactId>sharding-jdbc-coreartifactId> <version>${latest.release.version}version> dependency>
2.代码详解#
代码结构如上图所示，Mapper,Service层和普通的SSM项目一样，无需做改变，主要是增加了algorithm下面的两个文件，一个是分库策略，一个是分表策略。然后spring的配置文件稍稍做了修改，spring-database.xml设置的是数据库的连接信息。spring-sharding.xml设置的是具体的分库分表信息。
spring-database.xml配置如下所示：
Copy
<bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer"> <property name="locations"> <list> <value>classpath:config/resource/jdbc_dev.propertiesvalue> list> property> bean>
<bean name="sharding_0" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close"> <property name="url" value="${jdbc_url0}" /> <property name="username" value="${jdbc_username0}" /> <property name="password" value="${jdbc_password0}" />
<property name="initialSize" value="0" />
<property name="maxActive" value="20" />
<property name="minIdle" value="0" />
<property name="maxWait" value="60000" /> <property name="validationQuery" value="${validationQuery}" /> <property name="testOnBorrow" value="false" /> <property name="testOnReturn" value="false" /> <property name="testWhileIdle" value="true" />
<property name="timeBetweenEvictionRunsMillis" value="60000" />
<property name="minEvictableIdleTimeMillis" value="25200000" />
<property name="removeAbandoned" value="true" />
<property name="removeAbandonedTimeout" value="1800" />
<property name="logAbandoned" value="true" /> <property name="filters" value="stat" /> bean>
<bean name="sharding_1" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close"> <property name="url" value="${jdbc_url1}" /> <property name="username" value="${jdbc_username1}" /> <property name="password" value="${jdbc_password1}" />
<property name="initialSize" value="0" />
<property name="maxActive" value="20" />
<property name="minIdle" value="0" />
<property name="maxWait" value="60000" /> <property name="validationQuery" value="${validationQuery}" /> <property name="testOnBorrow" value="false" /> <property name="testOnReturn" value="false" /> <property name="testWhileIdle" value="true" />
<property name="timeBetweenEvictionRunsMillis" value="60000" />
<property name="minEvictableIdleTimeMillis" value="25200000" />
<property name="removeAbandoned" value="true" />
<property name="removeAbandonedTimeout" value="1800" />
<property name="logAbandoned" value="true" /> <property name="filters" value="stat" /> bean>spring-sharding.xml配置如下所示，从下图中可以看到主要是指定了需要分表的策略和分库的策略，然后进行了封装，具体的分库分表策略需要自己编写。
Copy
<bean id="dataSourceRule" class="com.dangdang.ddframe.rdb.sharding.api.rule.DataSourceRule"> <constructor-arg> <map> <entry key="sharding_0" value-ref="sharding_0"/> <entry key="sharding_1" value-ref="sharding_1"/> map> constructor-arg> bean>
<bean id="userTableRule" class="com.dangdang.ddframe.rdb.sharding.api.rule.TableRule"> <constructor-arg value="t_user" index="0"/> <constructor-arg index="1"> <list> <value>t_user_0value> <value>t_user_1value> <value>t_user_2value> list> constructor-arg> <constructor-arg index="2" ref="dataSourceRule"/> <constructor-arg index="3" ref="userDatabaseShardingStrategy"/> <constructor-arg index="4" ref="userTableShardingStrategy"/> bean>
<bean id="userDatabaseShardingStrategy" class="com.dangdang.ddframe.rdb.sharding.api.strategy.database.DatabaseShardingStrategy"> <constructor-arg index="0" value="user_id"/> <constructor-arg index="1"> <bean class="com.study.dangdang.sharding.jdbc.algorithm.UserSingleKeyDatabaseShardingAlgorithm" /> constructor-arg> bean>
<bean id="userTableShardingStrategy" class="com.dangdang.ddframe.rdb.sharding.api.strategy.table.TableShardingStrategy"> <constructor-arg index="0" value="user_id"/> <constructor-arg index="1"> <bean class="com.study.dangdang.sharding.jdbc.algorithm.UserSingleKeyTableShardingAlgorithm" /> constructor-arg> bean>
<bean id="shardingRule" class="com.dangdang.ddframe.rdb.sharding.api.rule.ShardingRule"> <constructor-arg index="0" ref="dataSourceRule"/> <constructor-arg index="1"> <list> <ref bean="userTableRule"/> list> constructor-arg> bean>
<bean id="shardingDataSource" class="com.dangdang.ddframe.rdb.sharding.api.ShardingDataSource"> <constructor-arg ref="shardingRule"/> bean>