sharding-jdbc实现原理——分库分表代码实战

small&snail

6921人浏览 · 2019-06-19 19:49:42

small&snail · 2019-06-19 19:49:42 发布

Sharding-JDBC简介

Sharding-JDBC是当当应用框架ddframe中，从关系型数据库模块dd-rdb中分离出来的数据库水平分片框架，实现透明化数据库分库分表访问。Sharding-JDBC是继dubbox和elastic-job之后，ddframe系列开源的第3个项目。

实现原理

前文已介绍了Sharding-JDBC是实现了JDBC协议的jar文件。基于JDBC协议的实现与基于MySQL等数据库协议实现的中间层略有差别。

无论使用哪种架构，核心逻辑均极为相似，除了协议实现层不同（JDBC或数据库协议），都会分为分片规则配置、SQL解析、SQL改写、SQL路由、SQL执行以及结果归并等模块。

Sharding-JDBC的整体架构图参见图1。

图片描述

图1 Sharding-JDBC的整体架构图

分库分表实战

接下来，我们就利用sharding-jdbc进行数据库的分库分表操作。

1、创建数据库

首先我们创建相应的数据库

create database sharding_0;
create database sharding_1;

这样我们就创建了两个数据库sharding_0和sharding_1;

接下来我们在两个库中创建相应的数据表，在两个库中分别进行如下SQL:

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for t_student_00
-- ----------------------------
DROP TABLE IF EXISTS `t_student_00`;
CREATE TABLE `t_student_00` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`student_id` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
`age` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for t_student_01
-- ----------------------------
DROP TABLE IF EXISTS `t_student_01`;
CREATE TABLE `t_student_01` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`student_id` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
`age` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for t_user_00
-- ----------------------------
DROP TABLE IF EXISTS `t_user_00`;
CREATE TABLE `t_user_00` (
`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;

-- ----------------------------
-- Table structure for t_user_01
-- ----------------------------
DROP TABLE IF EXISTS `t_user_01`;
CREATE TABLE `t_user_01` (
`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 AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for t_user_02
-- ----------------------------
DROP TABLE IF EXISTS `t_user_02`;
CREATE TABLE `t_user_02` (
`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 AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

这样，我们的数据库就准备好了。

2、创建项目

接下来，我们就创建一个Maven项目，项目结构如下：

3、配置pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>

<groupId>com.lyz</groupId>
<artifactId>sharding-jdbc-mybatis</artifactId>
<version>0.0.1-SNAPSHOT</version>
<packaging>jar</packaging>

<name>sharding-jdbc-mybatis</name>
<url>http://maven.apache.org</url>

<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<spring.version>4.1.0.RELEASE</spring.version>
<mybatis.version>3.2.4</mybatis.version>
</properties>

<dependencies>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.10</version>
</dependency>
<dependency>
<groupId>com.dangdang</groupId>
<artifactId>sharding-jdbc-core</artifactId>
<version>1.0.0</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-orm</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>commons-dbcp</groupId>
<artifactId>commons-dbcp</artifactId>
<version>1.4</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-spring</artifactId>
<version>1.2.2</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>${mybatis.version}</version>
</dependency>

<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-expression</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-aop</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-beans</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context-support</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-test</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.28</version>
</dependency>
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.16</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-log4j12</artifactId>
<version>1.7.5</version>
</dependency>
</dependencies>
</project>

4、创建数据库映射类

这里，我们创建两个数据库映射类：User类和Student类。

4-1、User类

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 + "]";
   }

}

4-2、Student类

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 + "]";
   }

}

5、创建Mapper类
5-1、UserMapper类

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);


}
5-2、StudentMapper类

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);

}
6、创建service类
6-1、UserService类

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;

}
6-2、StudentService类

package com.lyz.sharding.service;

import com.lyz.sharding.entity.Student;

/**
* 处理学生的service
* @author liuyazhuang
*
*/
public interface StudentService {

boolean insert(Student student);

}
7、创建service的实现类
7-1、UserServiceImpl类

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();
}

}
7-2、StudentServiceImpl类

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;
}

}
8、创建分库逻辑
8-1、User分库逻辑UserSingleKeyDatabaseShardingAlgorithm类

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;
}

}
9-2、创建Student分表逻辑StudentSingleKeyTableShardingAlgorithm
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;
}

}
10、创建Mapper.xml
10-1、创建UserMapper.xml

<?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>
11、创建jdbc_dev.properties
jdbc_driver0 = com.mysql.jdbc.Driver
jdbc_url0 = jdbc:mysql://localhost:3306/sharding_0?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true
jdbc_username0 = root
jdbc_password0 = root

jdbc_driver1 = com.mysql.jdbc.Driver
jdbc_url1 = jdbc:mysql://localhost:3306/sharding_1?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true
jdbc_username1 = root
jdbc_password1 = root


validationQuery=SELECT 1
12、创建spring配置文件
12-1、spring-database.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: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="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>


</beans>
12-2、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: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>


<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="shardingDataSource"/>
<property name="mapperLocations" value="classpath*:config/mapper/*Mapper.xml"/>
</bean>



<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_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>


<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>


<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>




<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>


<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>


<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>


<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>
13、创建log4j.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE log4j:configuration PUBLIC "-//APACHE//DTD LOG4J 1.2//EN" "log4j.dtd">
<log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/">

<appender name="console" class="org.apache.log4j.ConsoleAppender">
<param name="encoding" value="GBK" />
<param name="target" value="System.out" />
<layout class="org.apache.log4j.PatternLayout">
<param name="ConversionPattern" value="%-5p %c{2} - %m%n" />
</layout>
</appender>


<appender name="DEFAULT-APPENDER" class="org.apache.log4j.DailyRollingFileAppender">
<param name="File" value="logs/common-default.log" />
<param name="Append" value="true" />
<param name="encoding" value="GBK" />
<param name="DatePattern" value="'.'yyyy-MM-dd'.log'" />
<layout class="org.apache.log4j.PatternLayout">
<param name="ConversionPattern" value="%d %-5p %c{2} - %m%n" />
</layout>
</appender>


<appender name="ERROR-APPENDER" class="org.apache.log4j.DailyRollingFileAppender">
<param name="File" value="logs/common-error.log" />
<param name="Append" value="true" />
<param name="encoding" value="GBK" />
<param name="threshold" value="error" />
<param name="DatePattern" value="'.'yyyy-MM-dd'.log'" />
<layout class="org.apache.log4j.PatternLayout">
<param name="ConversionPattern" value="%d %-5p %c{2} - %m%n" />
</layout>
</appender>


<appender name="COMPONENT-APPENDER" class="org.apache.log4j.DailyRollingFileAppender">
<param name="File" value="logs/logistics-component.log" />
<param name="Append" value="true" />
<param name="encoding" value="GBK" />
<param name="DatePattern" value="'.'yyyy-MM-dd'.log'" />
<layout class="org.apache.log4j.PatternLayout">
<param name="ConversionPattern" value="%d %-5p %c{2} - %m%n" />
</layout>
</appender>


<logger name="LOGISTICS-COMPONENT">
<level value="${loggingLevel}" />
<appender-ref ref="COMPONENT-APPENDER" />
<appender-ref ref="ERROR-APPENDER" />
</logger>


<root>
<level value="${rootLevel}"></level>
<appender-ref ref="DEFAULT-APPENDER" />
<appender-ref ref="ERROR-APPENDER" />
<appender-ref ref="console" />
<appender-ref ref="COMPONENT-APPENDER" />
</root>
</log4j:configuration>
14、创建测试类ShardingJdbcMybatisTest
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();
}
}
三、测试
我们进行ShardingJdbcMybatisTest类，查看数据表数据，即可看到我们的程序利用sharding-jdbc实现了分库分表操作。

四、温馨提示
大家可以到链接http://download.csdn.net/download/l1028386804/10258290下载完整的sharding-jdbc分库分表实例源代码

GitCode 开源社区

旨在为数千万中国开发者提供一个无缝且高效的云端环境，以支持学习、使用和贡献开源项目。

更多推荐

[转载]在Windows环境下安装GNU Radio

转自：在Windows环境下安装GNURadio_恐弱智_新浪博客GNU Radio是用Python开发的，大部分开源的工程能够在Linux环境下运行良好，而Windows下却运行的很勉强，而且安装配置都很复杂。GNU Radio算是个例外了，不光提供了Windows的二进制安装，还有比较详细的说明。我是Python小白，所以折腾了好久才弄好，特意记录下来，免得以后再装还折腾。GNU Radio的

GitCode 开源社区

centOS 8 使用dnf安装Docker

DNF是什么？CentOS 8使用YUM软件包管理器版本v4.0.4。现在，该版本使用DNF(已删除YUM)。DNF是软件包管理器。它会在Linux发行版上安装，执行更新并删除软件包。使用DNF安装Docker跳过具有损坏依赖性的程序包一个有效的解决方案是使您的CentOS 8系统使用以下--nobest命令安装最符合条件的版本：sudo dnf install docker...

GitCode 开源社区

定时同步数据库表(mysql+linux+crontab)

sync.sh里面的参数需要改变，ip/username/password/database/tablesync.sh#!/bin/sh# Please change the IP and password of the data source db.# Then change the table name.filename=/home/nington/db/$(date +%Y-%m