为什么要分库分表
关系型数据库以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();
}
}
以上代码在本地是可以运行的,需要配置好环境,连上数据库。
本文暂时没有评论,来添加一个吧(●'◡'●)