为什么要分库分表

关系型数据库以MySQL为例，单机的存储能力、连接数是有限的，它自身就很容易会成为系统的瓶颈。当单表数据量在百万以内时，我们还可以通过添加从库、优化索引提升性能。

一旦数据量朝着千万以上趋势增长，再怎么优化数据库，很多操作性能仍下降严重。为了减少数据库的负担，提升数据库响应速度，缩短查询时间，这时候就需要进行分库分表。

如何分库分表

分库分表就是要将大量数据分散到多个数据库中，使每个数据库中数据量小响应速度快，以此来提升数据库整体性能。

针对数据切分类型，大致可以分为：垂直（纵向）切分和水平（横向）切分两种。

以下是分库分表的demo

package com.lyz.sharding.algorithm; import java.util.Collection; import java.util.LinkedHashSet; import com.dangdang.ddframe.rdb.sharding.api.ShardingValue; import com.dangdang.ddframe.rdb.sharding.api.strategy.database.SingleKeyDatabaseShardingAlgorithm; import com.google.common.collect.Range; /** * user表分库的逻辑函数 * @author liuyazhuang * */ public class StudentSingleKeyDatabaseShardingAlgorithm implements SingleKeyDatabaseShardingAlgorithm<Integer>{ /** * sql 中关键字 匹配符为 =的时候，表的路由函数 */ public String doEqualSharding(Collection<String> availableTargetNames, ShardingValue<Integer> shardingValue) { for (String each : availableTargetNames) { if (each.endsWith(shardingValue.getValue() % 2 + "")) { return each; } } throw new IllegalArgumentException(); } /** * sql 中关键字 匹配符为 in 的时候，表的路由函数 */ public Collection<String> doInSharding(Collection<String> availableTargetNames, ShardingValue<Integer> shardingValue) { Collection<String> result = new LinkedHashSet<String>(availableTargetNames.size()); for (Integer value : shardingValue.getValues()) { for (String tableName : availableTargetNames) { if (tableName.endsWith(value % 2 + "")) { result.add(tableName); } } } return result; } /** * sql 中关键字 匹配符为 between的时候，表的路由函数 */ 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 (Integer i = range.lowerEndpoint(); i <= range.upperEndpoint(); i++) { for (String each : availableTargetNames) { if (each.endsWith(i % 2 + "")) { result.add(each); } } } return result; } } ``` ``` package com.lyz.sharding.algorithm; 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; /** * 因为t_student实际表在每个库中只有2个，所以 %2 * @author iuyazhuang * */ public class StudentSingleKeyTableShardingAlgorithm implements SingleKeyTableShardingAlgorithm<Integer>{ /** * sql 中 = 操作时，table的映射 */ public String doEqualSharding(Collection<String> tableNames, ShardingValue<Integer> shardingValue) { for (String each : tableNames) { if (each.endsWith("0".concat(String.valueOf(shardingValue.getValue() % 2)))) { return each; } } throw new IllegalArgumentException(); } /** * sql 中 in 操作时，table的映射 */ public Collection<String> doInSharding(Collection<String> tableNames, ShardingValue<Integer> shardingValue) { Collection<String> result = new LinkedHashSet<String>(tableNames.size()); for (Integer value : shardingValue.getValues()) { for (String tableName : tableNames) { if (tableName.endsWith("0".concat(String.valueOf(value % 2)))) { result.add(tableName); } } } return result; } /** * sql 中 between 操作时，table的映射 */ 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++) { for (String each : tableNames) { if (each.endsWith("0".concat(String.valueOf(i % 2)))) { result.add(each); } } } return result; } } ``` ``` package com.lyz.sharding.algorithm; import java.util.Collection; import java.util.LinkedHashSet; import com.dangdang.ddframe.rdb.sharding.api.ShardingValue; import com.dangdang.ddframe.rdb.sharding.api.strategy.database.SingleKeyDatabaseShardingAlgorithm; import com.google.common.collect.Range; /** * user表分库的逻辑函数 * @author liuyazhuang * */ public class UserSingleKeyDatabaseShardingAlgorithm implements SingleKeyDatabaseShardingAlgorithm<Integer>{ /** * sql 中关键字 匹配符为 =的时候，表的路由函数 */ public String doEqualSharding(Collection<String> availableTargetNames, ShardingValue<Integer> shardingValue) { for (String each : availableTargetNames) { if (each.endsWith(shardingValue.getValue() % 2 + "")) { return each; } } throw new IllegalArgumentException(); } /** * sql 中关键字 匹配符为 in 的时候，表的路由函数 */ public Collection<String> doInSharding(Collection<String> availableTargetNames, ShardingValue<Integer> shardingValue) { Collection<String> result = new LinkedHashSet<String>(availableTargetNames.size()); for (Integer value : shardingValue.getValues()) { for (String tableName : availableTargetNames) { if (tableName.endsWith(value % 2 + "")) { result.add(tableName); } } } return result; } /** * sql 中关键字 匹配符为 between的时候，表的路由函数 */ 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 (Integer i = range.lowerEndpoint(); i <= range.upperEndpoint(); i++) { for (String each : availableTargetNames) { if (each.endsWith(i % 2 + "")) { result.add(each); } } } return result; } } ``` ``` package com.lyz.sharding.algorithm; 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; /** * 因为t_student实际表在每个库中只有3个，所以 %3 * @author iuyazhuang * */ public class UserSingleKeyTableShardingAlgorithm implements SingleKeyTableShardingAlgorithm<Integer>{ /** * sql 中 = 操作时，table的映射 */ public String doEqualSharding(Collection<String> tableNames, ShardingValue<Integer> shardingValue) { for (String each : tableNames) { if (each.endsWith(("0".concat(String.valueOf(shardingValue.getValue() % 3))))) { return each; } } throw new IllegalArgumentException(); } /** * sql 中 in 操作时，table的映射 */ public Collection<String> doInSharding(Collection<String> tableNames, ShardingValue<Integer> shardingValue) { Collection<String> result = new LinkedHashSet<String>(tableNames.size()); for (Integer value : shardingValue.getValues()) { for (String tableName : tableNames) { if (tableName.endsWith(("0".concat(String.valueOf(value % 3))))) { result.add(tableName); } } } return result; } /** * sql 中 between 操作时，table的映射 */ 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++) { for (String each : tableNames) { if (each.endsWith(("0".concat(String.valueOf(i % 3))))) { result.add(each); } } } return result; } } ``` ``` package com.lyz.sharding.entity; import java.io.Serializable; /** * 学生类 * @author liuyazhuang * */ public class Student implements Serializable { private static final long serialVersionUID = 8920597824668331209L; private Integer id; private Integer studentId; private String name; private Integer age; public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public Integer getStudentId() { return studentId; } public void setStudentId(Integer studentId) { this.studentId = studentId; } public String getName() { return name; } public void setName(String name) { this.name = name; } public Integer getAge() { return age; } public void setAge(Integer age) { this.age = age; } @Override public String toString() { return "Student [id=" + id + ", studentId=" + studentId + ", name=" + name + ", age=" + age + "]"; } } ``` ``` package com.lyz.sharding.entity; import java.io.Serializable; /** * 用户类 * @author liuyazhuang * */ public class User implements Serializable { private static final long serialVersionUID = 1L; private Integer id; private Integer userId; private String name; private Integer age; public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public Integer getUserId() { return userId; } public void setUserId(Integer userId) { this.userId = userId; } public String getName() { return name; } public void setName(String name) { this.name = name; } public Integer getAge() { return age; } public void setAge(Integer age) { this.age = age; } @Override public String toString() { return "User [id=" + id + ", userId=" + userId + ", name=" + name + ", age=" + age + "]"; } } ``` ``` package com.lyz.sharding.mapper; import java.util.List; import com.lyz.sharding.entity.Student; /** * 处理学生的数据操作接口 * @author liuyazhuang * */ public interface StudentMapper { Integer insert(Student s); List<Student> findAll(); List<Student> findByStudentIds(List<Integer> studentIds); } ``` ``` package com.lyz.sharding.mapper; import java.util.List; import com.lyz.sharding.entity.User; /** * 处理用户的数据操作接口 * @author liuyazhuang * */ public interface UserMapper { Integer insert(User u); List<User> findAll(); List<User> findByUserIds(List<Integer> userIds); } ``` ``` package com.lyz.sharding.service.impl; import javax.annotation.Resource; import org.springframework.stereotype.Service; import com.lyz.sharding.entity.Student; import com.lyz.sharding.mapper.StudentMapper; import com.lyz.sharding.service.StudentService; @Service public class StudentServiceImpl implements StudentService{ @Resource public StudentMapper studentMapper; public boolean insert(Student student) { return studentMapper.insert(student) > 0 ? true : false; } } ``` ``` package com.lyz.sharding.service.impl; import java.util.List; import javax.annotation.Resource; import org.springframework.stereotype.Service; import org.springframework.transaction.annotation.Propagation; import org.springframework.transaction.annotation.Transactional; import com.lyz.sharding.entity.Student; import com.lyz.sharding.entity.User; import com.lyz.sharding.mapper.StudentMapper; import com.lyz.sharding.mapper.UserMapper; import com.lyz.sharding.service.UserService; @Service @Transactional public class UserServiceImpl implements UserService { @Resource public UserMapper userMapper; @Resource public StudentMapper studentMapper; public boolean insert(User u) { return userMapper.insert(u) > 0 ? true :false; } public List<User> findAll() { return userMapper.findAll(); } public List<User> findByUserIds(List<Integer> ids) { return userMapper.findByUserIds(ids); } @Transactional(propagation=Propagation.REQUIRED) public void transactionTestSucess() { User u = new User(); u.setUserId(13); u.setAge(25); u.setName("war3 1.27"); userMapper.insert(u); Student student = new Student(); student.setStudentId(21); student.setAge(21); student.setName("hehe"); studentMapper.insert(student); } @Transactional(propagation=Propagation.REQUIRED) public void transactionTestFailure() throws IllegalAccessException { User u = new User(); u.setUserId(13); u.setAge(25); u.setName("war3 1.27 good"); userMapper.insert(u); Student student = new Student(); student.setStudentId(21); student.setAge(21); student.setName("hehe1"); studentMapper.insert(student); throw new IllegalAccessException(); } } ``` ``` package com.lyz.sharding.service; import com.lyz.sharding.entity.Student; /** * 处理学生的service * @author liuyazhuang * */ public interface StudentService { boolean insert(Student student); } ``` ``` package com.lyz.sharding.service; import java.util.List; import com.lyz.sharding.entity.User; /** * 处理用户的Service * @author liuyazhuang * */ public interface UserService { public boolean insert(User u); public List<User> findAll(); public List<User> findByUserIds(List<Integer> ids); public void transactionTestSucess(); public void transactionTestFailure() throws IllegalAccessException; } ``` ``` <?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" > <mapper namespace="com.lyz.sharding.mapper.StudentMapper" > <resultMap id="resultMap" type="com.lyz.sharding.entity.Student" > <id column="id" property="id" jdbcType="INTEGER" /> <result column="student_id" property="studentId" jdbcType="INTEGER" /> <result column="name" property="name" jdbcType="VARCHAR" /> <result column="age" property="age" jdbcType="INTEGER" /> </resultMap> <insert id="insert"> insert into t_student (student_id,name,age) values (#{studentId},#{name},#{age}) </insert> <select id="findAll" resultMap="resultMap"> select <include refid="columnsName"/> from t_student </select> <select id="findByStudentIds" resultMap="resultMap"> select <include refid="columnsName"/> from t_student where student_id in ( <foreach collection="list" item="item" separator=","> #{item} </foreach> ) </select> <sql id="columnsName"> id,student_id,name,age </sql> </mapper> ``` ``` <?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" > <mapper namespace="com.lyz.sharding.mapper.UserMapper" > <resultMap id="resultMap" type="com.lyz.sharding.entity.User" > <id column="id" property="id" jdbcType="INTEGER" /> <result column="user_id" property="userId" jdbcType="INTEGER" /> <result column="name" property="name" jdbcType="VARCHAR" /> <result column="age" property="age" jdbcType="INTEGER" /> </resultMap> <insert id="insert"> insert into t_user (user_id,name,age) values (#{userId},#{name},#{age}) </insert> <select id="findAll" resultMap="resultMap"> select <include refid="columnsName"/> from t_user </select> <select id="findByUserIds" resultMap="resultMap"> select <include refid="columnsName"/> from t_user where user_id in ( <foreach collection="list" item="item" separator=","> #{item} </foreach> ) </select> <sql id="columnsName"> id,user_id,name,age </sql> </mapper> ``` ``` jdbc_driver0 = com.mysql.jdbc.Driver jdbc_url0 = jdbc:mysql://localhost:3306/sharding_0 jdbc_username0 = root jdbc_password0 = root jdbc_driver1 = com.mysql.jdbc.Driver jdbc_url1 = jdbc:mysql://localhost:3306/sharding_1 jdbc_username1 = root jdbc_password1 = root validationQuery=SELECT 1 ``` ``` <?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:mybatis-spring="http://mybatis.org/schema/mybatis-spring" xmlns:tx="http://www.springframework.org/schema/tx" 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://mybatis.org/schema/mybatis-spring http://mybatis.org/schema/mybatis-spring-1.2.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.0.xsd"> <bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer"> <property name="locations"> <list> <value>classpath:config/resource/jdbc_dev.properties</value> </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="driverClass" value="${jdbc_driver0}" /> --> <!-- 初始化连接大小 --> <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" /> <!-- 打开removeAbandoned功能 --> <property name="removeAbandoned" value="true" /> <!-- 1800秒，也就是30分钟 --> <property name="removeAbandonedTimeout" value="1800" /> <!-- 关闭abanded连接时输出错误日志 --> <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="driverClass" value="${jdbc_driver1}" /> --> <!-- 初始化连接大小 --> <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" /> <!-- 打开removeAbandoned功能 --> <property name="removeAbandoned" value="true" /> <!-- 1800秒，也就是30分钟 --> <property name="removeAbandonedTimeout" value="1800" /> <!-- 关闭abanded连接时输出错误日志 --> <property name="logAbandoned" value="true" /> <property name="filters" value="stat" /> </bean> </beans> ``` ``` <?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:mybatis-spring="http://mybatis.org/schema/mybatis-spring" xmlns:tx="http://www.springframework.org/schema/tx" 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://mybatis.org/schema/mybatis-spring http://mybatis.org/schema/mybatis-spring-1.2.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.0.xsd"> <context:component-scan base-package="com.lyz.sharding" /> <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer"> <property name="basePackage" value="com.lyz.sharding.mapper"/> <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/> </bean> <!-- 配置sqlSessionFactory --> <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"> <property name="dataSource" ref="shardingDataSource"/> <property name="mapperLocations" value="classpath*:config/mapper/*Mapper.xml"/> </bean> <!-- 配置好dataSourceRulue,即对数据源进行管理 --> <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> <!-- 对t_user表的配置，进行分库配置，逻辑表名为t_user，每个库有实际的三张表 --> <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_00</value> <value>t_user_01</value> <value>t_user_02</value> </list> </constructor-arg> <constructor-arg index="2" ref="dataSourceRule"/> <constructor-arg index="3" ref="userDatabaseShardingStrategy"/> <constructor-arg index="4" ref="userTableShardingStrategy"/> </bean> <!-- t_user分库策略 --> <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.lyz.sharding.algorithm.UserSingleKeyDatabaseShardingAlgorithm" /> </constructor-arg> </bean> <!-- t_user 分表策略 --> <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.lyz.sharding.algorithm.UserSingleKeyTableShardingAlgorithm" /> </constructor-arg> </bean> <!-- 对t_student表的配置，进行分库配置，逻辑表名为t_student，每个库有实际的三张表 --> <bean id="studentTableRule" class="com.dangdang.ddframe.rdb.sharding.api.rule.TableRule"> <constructor-arg value="t_student" index="0"/> <constructor-arg index="1"> <list> <value>t_student_00</value> <value>t_student_01</value> </list> </constructor-arg> <constructor-arg index="2" ref="dataSourceRule"/> <constructor-arg index="3" ref="studentDatabaseShardingStrategy"/> <constructor-arg index="4" ref="studentTableShardingStrategy"/> </bean> <!-- t_student分库策略 --> <bean id="studentDatabaseShardingStrategy" class="com.dangdang.ddframe.rdb.sharding.api.strategy.database.DatabaseShardingStrategy"> <constructor-arg index="0" value="student_id"/> <constructor-arg index="1"> <bean class="com.lyz.sharding.algorithm.StudentSingleKeyDatabaseShardingAlgorithm" /> </constructor-arg> </bean> <!-- t_student 分表策略 --> <bean id="studentTableShardingStrategy" class="com.dangdang.ddframe.rdb.sharding.api.strategy.table.TableShardingStrategy"> <constructor-arg index="0" value="student_id"/> <constructor-arg index="1"> <bean class="com.lyz.sharding.algorithm.StudentSingleKeyTableShardingAlgorithm" /> </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"/> <ref bean="studentTableRule"/> </list> </constructor-arg> </bean> <!-- 对datasource进行封装 --> <bean id="shardingDataSource" class="com.dangdang.ddframe.rdb.sharding.api.ShardingDataSource"> <constructor-arg ref="shardingRule"/> </bean> <!-- 事务 --> <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager"> <property name="dataSource" ref="shardingDataSource" /> </bean> <tx:annotation-driven transaction-manager="transactionManager" /> </beans> ``` ``` package com.lyz.sharding.test; import java.util.Arrays; import java.util.List; import javax.annotation.Resource; import org.junit.Assert; import org.junit.Test; import org.junit.runner.RunWith; import org.springframework.test.context.ContextConfiguration; import org.springframework.test.context.junit4.SpringJUnit4ClassRunner; import com.lyz.sharding.entity.Student; import com.lyz.sharding.entity.User; import com.lyz.sharding.service.StudentService; import com.lyz.sharding.service.UserService; /** * 测试分库分表规则 * @author liuyazhuang * */ @RunWith(SpringJUnit4ClassRunner.class) @ContextConfiguration(locations = { "classpath*:config/spring/spring-database.xml", "classpath*:config/spring/spring-sharding.xml" }) public class ShardingJdbcMybatisTest { @Resource public UserService userService; @Resource public StudentService studentService; @Test public void testUserInsert() { User u = new User(); u.setUserId(11); u.setAge(25); u.setName("github"); Assert.assertEquals(userService.insert(u), true); } @Test public void testStudentInsert() { Student student = new Student(); student.setStudentId(21); student.setAge(21); student.setName("hehe"); Assert.assertEquals(studentService.insert(student), true); } @Test public void testFindAll(){ List<User> users = userService.findAll(); if(null != users && !users.isEmpty()){ for(User u :users){ System.out.println(u); } } } @Test public void testSQLIN(){ List<User> users = userService.findByUserIds(Arrays.asList(1)); if(null != users && !users.isEmpty()){ for(User u :users){ System.out.println(u); } } } @Test public void testTransactionTestSucess(){ userService.transactionTestSucess(); } @Test(expected = IllegalAccessException.class) public void testTransactionTestFailure() throws IllegalAccessException{ userService.transactionTestFailure(); } } 

以上代码在本地是可以运行的，需要配置好环境，连上数据库。