sharding

sharding

      项目初期版本使用当当sharding-jdbc进行数据库的分库分表操作，后续项目存储更换为自研弹性数据库。总结学习当当数据库中间件sharding-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文件 

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns=".0.0"xmlns:xsi=""xsi:schemaLocation=".0.0 .0.0.xsd"><modelVersion>4.0.0</modelVersion><groupId>com.chun</groupId><artifactId>spring-boot-mybatis-sharding-jdbc</artifactId><version>1.0-SNAPSHOT</version><parent><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-parent</artifactId><version>1.4.2.RELEASE</version><relativePath/> <!-- lookup parent from repository --></parent><properties><project.build.sourceEncoding>UTF-8</project.build.sourceEncoding><java.version>1.8</java.version></properties><dependencies><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter</artifactId></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-test</artifactId><scope>test</scope></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId></dependency><dependency><groupId&batis.spring.boot</groupId><artifactId>mybatis-spring-boot-starter</artifactId><version>1.1.1</version></dependency><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-devtools</artifactId><optional>true</optional></dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId></dependency><!--sharding-jdbc --><dependency><groupId>com.dangdang</groupId><artifactId>sharding-jdbc-core</artifactId><version>1.5.4</version></dependency><!--<dependency>--><!--<groupId>javax.persistence</groupId>--><!--<artifactId>persistence-api</artifactId>--><!--<version>1.0</version>--><!--</dependency>--></dependencies><build><plugins><plugin><groupId>org.springframework.boot</groupId><artifactId>spring-boot-maven-plugin</artifactId><configuration><fork>true</fork></configuration></plugin></plugins></build>
</project>

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

1. 有多少个数据源
2. 每张表的逻辑表名和所有物理表名
3. 用什么列进行分库以及分库算法
4. 用什么列进行分表以及分表算法
   分为两个库：test_msg1 , test_msg2，
   每个库都包含两个表: t_order_0 , t_order_1
   使用user_id作为分库列；
   使用order_id作为分表列；

配置文件：

application.properties

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

Application

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 ansaction.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);}
}

Entity 定义实体类

UserEntity.java

package ity;import ums.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) {der_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;}}

UserSexEnum.java

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

Service层

User1Service.java

package com.chun.service;import ity.UserEntity;
import com.chun.mapper.User1Mapper;
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<UserEntity> getUsers() {List<UserEntity> users&#All();return users;}//    @Transactional(value="test1TransactionManager",rollbackFor = Exception.class,timeout=36000)  //说明针对Exception异常也进行回滚，如果不标注，则Spring 默认只有抛出 RuntimeException才会回滚事务public void updateTransactional(UserEntity user) {try{user1Mapper.insert(user);(String.valueOf(user));}catch(Exception e){("find exception!");throw e;   // 事物方法中，如果使用trycatch捕获异常后，需要将异常抛出，否则事物不回滚。}}
}

Mapper层

User1Mapper.java

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

数据源配置及Mybatis配置：

配置多个数据源，数据源的名称最好要有一定的规则，方便配置分库的计算规则；
配置数据源规则，即将多个数据源交给sharding-jdbc管理，并且可以设置默认的数据源，当表没有配置分库规则时会使用默认的数据源；
分库：
user_id % 2 = 0的数据存储到test_msg1 ，为1的数据存储到test_msg0
分表：
order_id % 2 = 0的数据存储到 t_order_0，为1的数据存储到t_order_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;
batis.spring.SqlSessionFactoryBean;
batis.spring.SqlSessionTemplate;
batis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
import org.t.properties.ConfigurationProperties;
import t.annotation.Bean;
import t.annotation.Configuration;
import t.annotation.Primary;
import 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 = "st1")public DataSource dataSource0(){ate().build();}/*** 配置数据源1，数据源的名称最好要有一定的规则，方便配置分库的计算规则* @return*/@Bean(name="dataSource1")@ConfigurationProperties(prefix = "st2")public DataSource dataSource1(){ate().build();}/*** 配置数据源规则，即将多个数据源交给sharding-jdbc管理，并且可以设置默认的数据源，* 当表没有配置分库规则时会使用默认的数据源* @param dataSource0* @param dataSource1* @return*/@Beanpublic DataSourceRule dataSourceRule(@Qualifier("dataSource0") DataSource dataSource0,@Qualifier("dataSource1") DataSource dataSource1){Map<String, DataSource> 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<BindingTableRule> bindingTableRules = new ArrayList<BindingTableRule>();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 {ateDataSource(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"));Object();}@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;
llect.Range;import java.util.Collection;
import java.util.LinkedHashSet;/*** 分库策略的简单实现* Created by Kane on 2018/1/22.*/
public class ModuloDatabaseShardingAlgorithm implements SingleKeyDatabaseShardingAlgorithm<Long> {@Overridepublic String doEqualSharding(Collection<String> databaseNames, ShardingValue<Long> shardingValue) {for (String each : databaseNames) {if (dsWith(Long.Value().toString()) % 2 + "")) {return each;}}throw new IllegalArgumentException();}@Overridepublic Collection<String> doInSharding(Collection<String> databaseNames, ShardingValue<Long> shardingValue) {Collection<String> result = new LinkedHashSet<>(databaseNames.size());for (Long value : Values()) {for (String tableName : databaseNames) {if (dsWith(value % 2 + "")) {result.add(tableName);}}}return result;}@Overridepublic Collection<String> doBetweenSharding(Collection<String> databaseNames, ShardingValue<Long> shardingValue) {Collection<String> result = new LinkedHashSet<>(databaseNames.size());Range<Long> range = (Range<Long>) ValueRange();for (Long i = range.lowerEndpoint(); i <= range.upperEndpoint(); i++) {for (String each : databaseNames) {if (dsWith(i % 2 + "")) {result.add(each);}}}return result;}
}

debug方法doEqualSharding会发现：

 

分库代码.png

doEqualSharding参数.png

根据user_id的值返回路由的库的名称。
分库：
user_id % 2 = 0的数据存储到test_msg1 ，为1的数据存储到test_msg0，

分表策略的基本实现

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

ModuloTableShardingAlgorithm.java

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

Controller层

UserController.java

package com.chun.web;import ity.UserEntity;
import ums.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<UserEntity> getUsers() {List<UserEntity> users&#Users();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";}
}

Mybatis 配置文件

resources/mybatis/l

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis//DTD Mapper 3.0//EN" ".dtd" >
<mapper namespace="com.chun.mapper.User1Mapper" ><resultMap id="BaseResultMap" type="ity.UserEntity" ><id column="id" property="id" jdbcType="BIGINT" /><result column="order_id" property="order_id" jdbcType="BIGINT" /><result column="user_id" property="user_id" jdbcType="BIGINT" /><result column="userName" property="userName" jdbcType="VARCHAR" /><result column="passWord" property="passWord" jdbcType="VARCHAR" /><result column="user_sex" property="userSex" javaType="ums.UserSexEnum"/><result column="nick_name" property="nickName" jdbcType="VARCHAR" /></resultMap><sql id="Base_Column_List" >id, userName, passWord, user_sex, nick_name</sql><select id="getAll" resultMap="BaseResultMap"  >SELECT<include refid="Base_Column_List" />FROM t_order</select><insert id="insert" parameterType="ity.UserEntity" >INSERT INTOt_order(order_id,user_id,userName,passWord,user_sex)VALUES(#{order_id},#{user_id},#{userName}, #{passWord}, #{userSex})</insert></mapper>

resources/l

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis//DTD Config 3.0//EN" ".dtd">
<configuration><typeAliases><typeAlias alias="Integer" type="java.lang.Integer" /><typeAlias alias="Long" type="java.lang.Long" /><typeAlias alias="HashMap" type="java.util.HashMap" /><typeAlias alias="LinkedHashMap" type="java.util.LinkedHashMap" /><typeAlias alias="ArrayList" type="java.util.ArrayList" /><typeAlias alias="LinkedList" type="java.util.LinkedList" /></typeAliases>
</configuration>

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

springboot2.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&#sql.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&#sql.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&#sql.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&#sql.cj.jdbc.Driver

 

spring.datasource.url 数据库的 JDBC URL。

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

官方文档的解释是：

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

原文地址：