sharding-jdbc实现分库分表

项目初期版本使用当当-jdbc进行数据库的分库分表操作，后续项目存储更换为自研弹性数据库。总结学习当当数据库中间件-jdbc，并完成demo，可运行，下面列出demo代码说明：

建表sql

/*
Navicat MySQL Data Transfer 在Navicat中直接运行本sql文件创建表
* 两个库：test_msg1   包含两个表： t_order_0   t_order_1
*         test_msg2  包含两个表： t_order_0   t_order_1
*  建表sql如下
*/SET FOREIGN_KEY_CHECKS=0;-- ----------------------------
-- Table structure for `users`
-- ----------------------------
DROP TABLE IF EXISTS `t_order_0`;
CREATE TABLE `t_order_0` (`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',`order_id` varchar(32) DEFAULT NULL COMMENT '顺序编号',`user_id` varchar(32) DEFAULT NULL COMMENT '用户编号',`userName` varchar(32) DEFAULT NULL COMMENT '用户名',`passWord` varchar(32) DEFAULT NULL COMMENT '密码',`user_sex` varchar(32) DEFAULT NULL,`nick_name` varchar(32) DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8;

pom文件

4.0.0com.chunspring-boot-mybatis-sharding-jdbc1.0-SNAPSHOTorg.springframework.bootspring-boot-starter-parent1.4.2.RELEASE UTF-81.8org.springframework.bootspring-boot-starterorg.springframework.bootspring-boot-starter-testtestorg.springframework.bootspring-boot-starter-weborg.mybatis.spring.bootmybatis-spring-boot-starter1.1.1mysqlmysql-connector-javaorg.springframework.bootspring-boot-devtoolstrueorg.projectlomboklombokcom.dangdangsharding-jdbc-core1.5.4org.springframework.bootspring-boot-maven-plugintrue

分库分表最主要有几个配置：

有多少个数据源每张表的逻辑表名和所有物理表名用什么列进行分库以及分库算法用什么列进行分表以及分表算法

分为两个库： , ，

每个库都包含两个表: ,

使用作为分库列；

使用作为分表列；

配置文件：

application.properties

配置数据源相关数据属性。

mybatis.config-locations=classpath:mybatis/mybatis-config.xml#datasource
spring.devtools.remote.restart.enabled=false#data source1
spring.datasource.test1.driverClassName=com.mysql.jdbc.Driver
spring.datasource.test1.url=jdbc:mysql://127.0.0.1:3306/test_msg1
spring.datasource.test1.username=root
spring.datasource.test1.password=root#data source2
spring.datasource.test2.driverClassName=com.mysql.jdbc.Driver
spring.datasource.test2.url=jdbc:mysql://127.0.0.1:3306/test_msg2
spring.datasource.test2.username=root
spring.datasource.test2.password=root

Application.java

package com.chun;import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
import org.springframework.transaction.annotation.EnableTransactionManagement;/*** Created by Kane on 2018/1/17.*/
@SpringBootApplication
@EnableAutoConfiguration(exclude={DataSourceAutoConfiguration.class}) //排除DataSourceConfiguratrion
@EnableTransactionManagement(proxyTargetClass = true)   //开启事物管理功能
public class Application {public static void main(String[] args) {SpringApplication.run(Application.class, args);}
}

定义实体类

UserEntity.java

package com.chun.entity;import com.chun.enums.UserSexEnum;
import java.io.Serializable;/*** Created by Kane on 2018/1/17.*/
public class UserEntity implements Serializable {private static final long serialVersionUID = 1L;private Long id;private Long order_id;private Long user_id;private String userName;private String passWord;private UserSexEnum userSex;private String nickName;public Long getId() {return id;}public void setId(Long id) {this.id = id;}public Long getOrder_id() {return order_id;}public void setOrder_id(Long order_id) {this.order_id = order_id;}public Long getUser_id() {return user_id;}public void setUser_id(Long user_id) {this.user_id = user_id;}public String getUserName() {return userName;}public void setUserName(String userName) {this.userName = userName;}public String getPassWord() {return passWord;}public void setPassWord(String passWord) {this.passWord = passWord;}public UserSexEnum getUserSex() {return userSex;}public void setUserSex(UserSexEnum userSex) {this.userSex = userSex;}public String getNickName() {return nickName;}public void setNickName(String nickName) {this.nickName = nickName;}}

.java

package com.chun.enums;/*** Created by Kane on 2018/1/17.*/
public enum UserSexEnum {MAN, WOMAN
}

层

User1Service.java

package com.chun.service;import com.chun.entity.UserEntity;
import com.chun.mapper.User1Mapper;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;import java.util.List;/*** Created by Kane on 2018/1/17.*/
@Slf4j
@Service
public class User1Service {@Autowiredprivate User1Mapper user1Mapper;public List getUsers() {List users=user1Mapper.getAll();return users;}//    @Transactional(value="test1TransactionManager",rollbackFor = Exception.class,timeout=36000)  //说明针对Exception异常也进行回滚，如果不标注，则Spring 默认只有抛出 RuntimeException才会回滚事务public void updateTransactional(UserEntity user) {try{user1Mapper.insert(user);log.error(String.valueOf(user));}catch(Exception e){log.error("find exception!");throw e;   // 事物方法中，如果使用trycatch捕获异常后，需要将异常抛出，否则事物不回滚。}}
}

层

User1Mapper.java

package com.chun.mapper;import com.chun.entity.UserEntity;import java.util.List;/*** Created by Kane on 2018/1/17.*/
public interface User1Mapper {List getAll();void update(UserEntity user);}

数据源配置及配置：

配置多个数据源，数据源的名称最好要有一定的规则，方便配置分库的计算规则；

配置数据源规则，即将多个数据源交给-jdbc管理，并且可以设置默认的数据源，当表没有配置分库规则时会使用默认的数据源；

分库：

% 2 = 0的数据存储到，为1的数据存储到

分表：

% 2 = 0的数据存储到，为1的数据存储到

DataSourceConfig.java

package com.chun;import com.chun.strategy.ModuloDatabaseShardingAlgorithm;
import com.chun.strategy.ModuloTableShardingAlgorithm;
import com.dangdang.ddframe.rdb.sharding.api.ShardingDataSourceFactory;
import com.dangdang.ddframe.rdb.sharding.api.rule.BindingTableRule;
import com.dangdang.ddframe.rdb.sharding.api.rule.DataSourceRule;
import com.dangdang.ddframe.rdb.sharding.api.rule.ShardingRule;
import com.dangdang.ddframe.rdb.sharding.api.rule.TableRule;
import com.dangdang.ddframe.rdb.sharding.api.strategy.database.DatabaseShardingStrategy;
import com.dangdang.ddframe.rdb.sharding.api.strategy.table.TableShardingStrategy;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.*;/***  数据源及分表配置* Created by Kane on 2018/1/17.*/
@Configuration
@MapperScan(basePackages = "com.chun.mapper", sqlSessionTemplateRef  = "test1SqlSessionTemplate")
public class DataSourceConfig {/*** 配置数据源0，数据源的名称最好要有一定的规则，方便配置分库的计算规则* @return*/@Bean(name="dataSource0")@ConfigurationProperties(prefix = "spring.datasource.test1")public DataSource dataSource0(){return DataSourceBuilder.create().build();}/*** 配置数据源1，数据源的名称最好要有一定的规则，方便配置分库的计算规则* @return*/@Bean(name="dataSource1")@ConfigurationProperties(prefix = "spring.datasource.test2")public DataSource dataSource1(){return DataSourceBuilder.create().build();}/*** 配置数据源规则，即将多个数据源交给sharding-jdbc管理，并且可以设置默认的数据源，* 当表没有配置分库规则时会使用默认的数据源* @param dataSource0* @param dataSource1* @return*/@Beanpublic DataSourceRule dataSourceRule(@Qualifier("dataSource0") DataSource dataSource0,@Qualifier("dataSource1") DataSource dataSource1){Map dataSourceMap = new HashMap<>(); //设置分库映射dataSourceMap.put("dataSource0", dataSource0);dataSourceMap.put("dataSource1", dataSource1);return new DataSourceRule(dataSourceMap, "dataSource0"); //设置默认库，两个库以上时必须设置默认库。默认库的数据源名称必须是dataSourceMap的key之一}/*** 配置数据源策略和表策略，具体策略需要自己实现* @param dataSourceRule* @return*/@Beanpublic ShardingRule shardingRule(DataSourceRule dataSourceRule){//具体分库分表策略TableRule orderTableRule = TableRule.builder("t_order").actualTables(Arrays.asList("t_order_0", "t_order_1")).tableShardingStrategy(new TableShardingStrategy("order_id", new ModuloTableShardingAlgorithm())).dataSourceRule(dataSourceRule).build();//绑定表策略，在查询时会使用主表策略计算路由的数据源，因此需要约定绑定表策略的表的规则需要一致，可以一定程度提高效率List bindingTableRules = new ArrayList();bindingTableRules.add(new BindingTableRule(Arrays.asList(orderTableRule)));return ShardingRule.builder().dataSourceRule(dataSourceRule).tableRules(Arrays.asList(orderTableRule)).bindingTableRules(bindingTableRules).databaseShardingStrategy(new DatabaseShardingStrategy("user_id", new ModuloDatabaseShardingAlgorithm())).tableShardingStrategy(new TableShardingStrategy("order_id", new ModuloTableShardingAlgorithm())).build();}/*** 创建sharding-jdbc的数据源DataSource，MybatisAutoConfiguration会使用此数据源* @param shardingRule* @return* @throws SQLException*/@Bean(name="dataSource")public DataSource shardingDataSource(ShardingRule shardingRule) throws SQLException {return ShardingDataSourceFactory.createDataSource(shardingRule);}/*** 需要手动配置事务管理器* @param dataSource* @return*/@Beanpublic DataSourceTransactionManager transactitonManager(@Qualifier("dataSource") DataSource dataSource){return new DataSourceTransactionManager(dataSource);}@Bean(name = "test1SqlSessionFactory")@Primarypublic SqlSessionFactory testSqlSessionFactory(@Qualifier("dataSource") DataSource dataSource) throws Exception {SqlSessionFactoryBean bean = new SqlSessionFactoryBean();bean.setDataSource(dataSource);bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mybatis/mapper/*.xml"));return bean.getObject();}@Bean(name = "test1SqlSessionTemplate")@Primarypublic SqlSessionTemplate testSqlSessionTemplate(@Qualifier("test1SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {return new SqlSessionTemplate(sqlSessionFactory);}
}

分库策略实现：

ModuloDatabaseShardingAlgorithm.java

package com.chun.strategy;import com.dangdang.ddframe.rdb.sharding.api.ShardingValue;
import com.dangdang.ddframe.rdb.sharding.api.strategy.database.SingleKeyDatabaseShardingAlgorithm;
import com.google.common.collect.Range;import java.util.Collection;
import java.util.LinkedHashSet;/*** 分库策略的简单实现* Created by Kane on 2018/1/22.*/
public class ModuloDatabaseShardingAlgorithm implements SingleKeyDatabaseShardingAlgorithm {@Overridepublic String doEqualSharding(Collection databaseNames, ShardingValue shardingValue) {for (String each : databaseNames) {if (each.endsWith(Long.parseLong(shardingValue.getValue().toString()) % 2 + "")) {return each;}}throw new IllegalArgumentException();}@Overridepublic Collection doInSharding(Collection databaseNames, ShardingValue shardingValue) {Collection result = new LinkedHashSet<>(databaseNames.size());for (Long value : shardingValue.getValues()) {for (String tableName : databaseNames) {if (tableName.endsWith(value % 2 + "")) {result.add(tableName);}}}return result;}@Overridepublic Collection doBetweenSharding(Collection databaseNames, ShardingValue shardingValue) {Collection result = new LinkedHashSet<>(databaseNames.size());Range range = (Range) shardingValue.getValueRange();for (Long i = range.lowerEndpoint(); i <= range.upperEndpoint(); i++) {for (String each : databaseNames) {if (each.endsWith(i % 2 + "")) {result.add(each);}}}return result;}
}

debug方法会发现：

分库代码.png

参数.png

根据的值返回路由的库的名称。

分库：

% 2 = 0的数据存储到 ，为1的数据存储到，

分表策略的基本实现

分表逻辑类需要实现接口的三个方法、、

ModuloTableShardingAlgorithm.java

package com.chun.strategy;import com.dangdang.ddframe.rdb.sharding.api.ShardingValue;
import com.dangdang.ddframe.rdb.sharding.api.strategy.table.SingleKeyTableShardingAlgorithm;
import com.google.common.collect.Range;import java.util.Collection;
import java.util.LinkedHashSet;/*** 分表策略的基本实现* Created by Kane on 2018/1/22.*/
public class ModuloTableShardingAlgorithm implements SingleKeyTableShardingAlgorithm {@Overridepublic String doEqualSharding(Collection tableNames, ShardingValue shardingValue) {for (String each : tableNames) {if (each.endsWith(shardingValue.getValue() % 2 + "")) {return each;}}throw new IllegalArgumentException();}@Overridepublic Collection doInSharding(Collection tableNames, ShardingValue shardingValue) {Collection result = new LinkedHashSet<>(tableNames.size());for (Long value : shardingValue.getValues()) {for (String tableName : tableNames) {if (tableName.endsWith(value % 2 + "")) {result.add(tableName);}}}return result;}@Overridepublic Collection doBetweenSharding(Collection tableNames, ShardingValue shardingValue) {Collection result = new LinkedHashSet<>(tableNames.size());Range range = (Range) shardingValue.getValueRange();for (Long i = range.lowerEndpoint(); i <= range.upperEndpoint(); i++) {for (String each : tableNames) {if (each.endsWith(i % 2 + "")) {result.add(each);}}}return result;}
}

层

UserController.java

package com.chun.web;import com.chun.entity.UserEntity;
import com.chun.enums.UserSexEnum;
import com.chun.service.User1Service;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;import java.util.List;/*** Created by Kane on 2018/1/17.*/
@Service
@RestController
public class UserController {@Autowiredprivate User1Service user1Service;@RequestMapping("/getUsers")public List getUsers() {List users=user1Service.getUsers();return users;}//测试@RequestMapping(value="insert1")public String updateTransactional(@RequestParam(value = "id") Long id,@RequestParam(value = "user_id") Long user_id,@RequestParam(value = "order_id") Long order_id,@RequestParam(value = "nickName") String nickName,@RequestParam(value = "passWord") String passWord,@RequestParam(value = "userName") String userName) {UserEntity user2 = new UserEntity();user2.setId(id);user2.setUser_id(user_id);user2.setOrder_id(order_id);user2.setNickName(nickName);user2.setPassWord(passWord);user2.setUserName(userName);user2.setUserSex(UserSexEnum.WOMAN);user1Service.updateTransactional(user2);return "test";}
}

配置文件

resources/mybatis/mapper/UserMapper.xml

id, userName, passWord, user_sex, nick_nameSELECTFROM t_orderINSERT INTOt_order(order_id,user_id,userName,passWord,user_sex)VALUES(#{order_id},#{user_id},#{userName}, #{passWord}, #{userSex})

resources/mybatis/mybatis-config.xml

如果报错： is with ，按照下面解决：

.0配置多数据源：

spring.datasource.primary.url=jdbc:mysql://localhost:3306/study?useUnicode=true&characterEncoding=utf-8&useSSL=true
spring.datasource.primary.username=root
spring.datasource.primary.password=root
spring.datasource.primary.driver-class=com.mysql.cj.jdbc.Driverspring.datasource.secondary.url=jdbc:mysql://localhost:3306/study?useUnicode=true&characterEncoding=utf-8&useSSL=true
spring.datasource.secondary.username=root
spring.datasource.secondary.password=root
spring.datasource.secondary.driver-class=com.mysql.cj.jdbc.Driver

改为：

spring.datasource.primary.jdbc-url=jdbc:mysql://localhost:3306/study?useUnicode=true&characterEncoding=utf-8&useSSL=true
spring.datasource.primary.username=root
spring.datasource.primary.password=root
spring.datasource.primary.driver-class-name=com.mysql.cj.jdbc.Driverspring.datasource.secondary.jdbc-url=jdbc:mysql://localhost:3306/study?useUnicode=true&characterEncoding=utf-8&useSSL=true
spring.datasource.secondary.username=root
spring.datasource.secondary.password=root
spring.datasource.secondary.driver-class-name=com.mysql.cj.jdbc.Driver

..url 数据库的 JDBC URL。

..jdbc-url 用来重写自定义连接池

官方文档的解释是：

因为连接池的实际类型没有被公开，所以在您的自定义数据源的元数据中没有生成密钥，而且在IDE中没有完成(因为接口没有暴露属性)。另外，如果您碰巧在类路径上有，那么这个基本设置就不起作用了，因为没有url属性(但是确实有一个属性)。在这种情况下，您必须重写您的配置如下: