ShardingSphere分库分表（SpringBoot+mybatis+mysql）配置
⼀、什么是ShardingSphere
定位为轻量级 Java 框架，在 Java 的 JDBC 层提供的额外服务。 它使⽤客户端直连数据库，以 jar 包形式提供服务，⽆需额外部署和依赖，可理解为增强版的 JDBC 驱动，完全兼容 JDBC 和各种 ORM 框架。它由Sharding-JDBC、Sharding-Proxy和Sharding-
Sidecar（计划中）这3款相互独⽴的产品组成。 他们均提供标准化的数据分⽚、分布式事务和数据库治理功能。
⼆、应⽤场景
单数据库系统往往会随着业务的增长⽽导致⽆法满⾜互联⽹海量数据的应⽤场景，在性能，可⽤性以及运维等⽅⾯遇到瓶颈。为了解决这种问题，数据库横向以及纵向扩展（分库分表）的中间件也伴随满⾜类似的需求⽽产⽣。常见的分库分表的中间件有
MyCat，ShardingSphere以及阿⾥巴巴分布式数据库服务DRDS。ShardingSphere是⼀套开源的分布式数据库中间件，当前就⼀个基于springBoot+mybatis+mysql的实例对ShardingSpher的分库分表配置操作进⾏详细的介绍。
nice什么意思
三、实现过程
⼀、准备数据库环境
1、⾸先安装两个MySql数据库
（它们的端⼝分别为3306和3307），创建⽅式请参考。
2、在两个数据库中分别创建数据库
CREATE DATABASE shardingsphere;
3、在两个数据库中分别创建两张表
DROP TABLE IF EXISTS`t_ur_0`;
CREATE TABLE`t_ur_0`(
`ur_id`bigint(32)NOT NULL COMMENT'主键',
`id_number`varchar(18)CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT'⾝份证号码',
bushido
`name`varchar(20)CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT'姓名',
`age`int(4)NULL DEFAULT NULL COMMENT'年龄',
`gender`int(2)NULL DEFAULT1COMMENT'性别：1-男；2-⼥',
`birth_date`date NULL DEFAULT NULL COMMENT'出⽣⽇期',
PRIMARY KEY(`ur_id`)USING BTREE
)ENGINE=InnoDB CHARACTER SET= utf8mb4 COLLATE= utf8mb4_general_ci COMMENT='⽤户表' ROW_FORMAT = Compact;
DROP TABLE IF EXISTS`t_ur_1`;
CREATE TABLE`t_ur_1`(
`ur_id`bigint(32)NOT NULL COMMENT'主键',
`id_number`varchar(18)CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COM
MENT'⾝份证号码',
`name`varchar(20)CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT'姓名',
`age`int(4)NULL DEFAULT NULL COMMENT'年龄',
`gender`int(2)NULL DEFAULT1COMMENT'性别：1-男；2-⼥',
`birth_date`date NULL DEFAULT NULL COMMENT'出⽣⽇期',
PRIMARY KEY(`ur_id`)USING BTREE
)ENGINE=InnoDB CHARACTER SET= utf8mb4 COLLATE= utf8mb4_general_ci COMMENT='⽤户表' ROW_FORMAT = Compact;
⼆、代码实现
1、创建⼀个springBoot系统
（本⽂使⽤的IDE⼯具是IntelliJ IDEA 2019.2 x64，具体过程请⾃⾏⽹上搜索创建），系统名称为shar
dingsphere。⾸先编写maven的l⽂件。此处shardingSphere使⽤的版本是3.1.0。
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="/POM/4.0.0"xsi="/2001/XMLSchema-instance"
schemaLocation="/POM/4.0.0 /xsd/maven-4.0.0.xsd">
名人访谈<modelVersion>4.0.0</modelVersion>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.2.6.RELEASE</version>
<relativePath/><!-- lookup parent from repository --> </parent>
<groupId&amo</groupId>
<artifactId>shardingsphere</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>shardingsphere</name>
<description>Demo project for Spring Boot</description> <properties>
<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-web</artifactId>
</dependency>
<dependency>
<groupId>io.shardingsphere</groupId>
<artifactId>sharding-jdbc-core</artifactId>
<version>3.1.0</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.60</version>
</dependency>
<dependency>郁郁寡欢是什么意思
<groupId>cn.hutool</groupId>
与领导相处的艺术<artifactId>hutool-all</artifactId>
<version>4.5.13</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
<version>5.1.32</version>
</dependency>
<dependency>
美国voa之音
<groupId>com.alibaba</groupId>
奥巴马 上海
<artifactId>druid</artifactId>
<version>1.0.9</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId&batis</groupId>
<artifactId>mapper-spring-boot-starter</artifactId>
<version>2.0.2</version>
</dependency>
</dependencies>
<build>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
2、编写配置⽂件l⽂件
此处主要配置两个数据源ds0和ds1，他们分别对应⼀开始创建的两个数据库。
rver:
port:8090
spring:
datasource:
type-alias-package: amo.shardingsphere.mapper
mapper-locations: classpath:/mapper/*.xml
ds0:
url: jdbc:mysql://localhost:3306/shardingsphere?uUnicode=true&characterEncoding=utf-8&uSSL=true urname: root
password:
driver-class-name: sql.jdbc.Driver
initialSize:2 #初始化⼤⼩
maxWait:6000 #获取连接时最⼤等待时间，单位毫秒。
min-idle:5 # 数据库连接池的最⼩维持连接数
maxActive:20 # 最⼤的连接数
initial-size:5 # 初始化提供的连接数
max-wait-millis:200 # 等待连接获取的最⼤超时时间
type: com.alibaba.druid.pool.DruidDataSource
ds1:
url: jdbc:mysql://localhost:3307/shardingsphere?uUnicode=true&characterEncoding=utf-8&uSSL=true urname: root
password:
driver-class-name: sql.jdbc.Driver
initialSize:2 #初始化⼤⼩
maxWait:6000 #获取连接时最⼤等待时间，单位毫秒。
min-idle:5 # 数据库连接池的最⼩维持连接数
maxActive:20 # 最⼤的连接数
initial-size:5 # 初始化提供的连接数
max-wait-millis:200 # 等待连接获取的最⼤超时时间
type: com.alibaba.druid.pool.DruidDataSource
mybatis:
configuration:
map-underscore-to-camel-ca:true
type-alias-package: del
mapper-locations: classpath:mapper/*.xml
3、创建shardingSphere的数据源配置类DataSourceConfig.java
特别需要关注的是⽅法urRuleConfig，该⽅法是对t_ur表设置分库分表规则。
3.1、t_ur表共创建了四张表分别是ds0_t_ur0,ds0_t_ur1,ds1_t_ur0,ds1_t_ur1。
tableRuleConfig.tActualDataNodes(“ds0.t_ur_0,ds0.t_ur_1,ds1.t_ur_0,ds1.t_ur_1”);
3.2、t_ur表以主键ur_id为分库键和分表键
//设置t_ur分库规则
tableRuleConfig.tDatabaShardingStrategyConfig(
new StandardShardingStrategyConfiguration(“ur_id”, new DatabaShardingAlgorithm()));
//设置t_ur分表规则
tableRuleConfig.tTableShardingStrategyConfig(
new StandardShardingStrategyConfiguration(“ur_id”, new IdCommonShardingAlgorithm()));
其中类DatabaShardingAlgorithm是分库类， 类IdCommonShardingAlgorithm是分表类，它们都实现PreciShardingAlgorithm 接⼝，并在接⼝⽅法中实现对应的分库分表逻辑，具体内容请看查看这两个类具体内容。
fig;
import com.alibaba.druid.filter.Filter;
import com.alibaba.druid.filter.stat.StatFilter;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;
amo.shardingsphere.dbstrategy.DatabaShardingAlgorithm;
amo.shardingsphere.dbstrategy.GenderTableShardingAlgorithm;
amo.shardingsphere.dbstrategy.IdCommonShardingAlgorithm;
import io.fig.rule.ShardingRuleConfiguration;
import io.fig.rule.TableRuleConfiguration;
import io.fig.strategy.InlineShardingStrategyConfiguration;
import io.fig.strategy.StandardShardingStrategyConfiguration;
import io.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;
import lombok.Data;
import org.apache.ibatis.ssion.SqlSessionFactory;
batis.spring.SqlSessionFactoryBean;
batis.spring.SqlSessionTemplate;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.web.rvlet.ServletRegistrationBean;
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.HashMap;
import java.util.Map;
import java.util.Properties;
import urrent.ConcurrentHashMap;
/
***
* @author teamo
* @date 2022-03-18
*/
@Configuration
@Data
public class DataSourceConfig {
@Value("${pe-alias-package}")
private String typeAliasPackage;
stagecoach@Value("${spring.datasource.mapper-locations}")
private String mapperLocation;
@Value("${spring.datasource.ds0.url}")
private String url0;
@Value("${spring.datasource.ds0.urname}")
private String urName0;
@Value("${spring.datasource.ds0.password}")
private String password0;
@Value("${spring.datasource.ds0.driver-class-name}")
private String driverClassName0;
@Value("${spring.pe}")
private String dsType0;
@Value("${spring.datasource.ds1.url}")
private String url1;
@Value("${spring.datasource.ds1.urname}")
private String urName1;
@Value("${spring.datasource.ds1.password}")
private String password1;
@Value("${spring.datasource.ds1.driver-class-name}")
private String driverClassName1;
@Value("${spring.pe}")
private String dsType1;
/**
* 获取数据源Map
*/
private Map<String, DataSource>getDatasourceMap(){
// 真实数据源map
Map<String, DataSource> dataSourceMap =new HashMap<String, DataSource>(2);
// 配置第⼀个数据源
DruidDataSource dataSource0 =new DruidDataSource();
dataSource0.tDriverClassName(driverClassName0);
dataSource0.tUrl(url0);
dataSource0.tUrname(urName0);
dataSource0.tPassword(password0);
dataSource0.tMinIdle(5);
dataSource0.tMaxActive(20);
dataSource0.tInitialSize(5);
博物馆的英语dataSource0.tMaxWait(6000);
dataSourceMap.put("ds0", dataSource0);
// 配置第⼆个数据源
DruidDataSource dataSource1 =new DruidDataSource();
dataSource1.tDriverClassName(driverClassName1);
dataSource1.tUrl(url1);
dataSource1.tUrname(urName1);
dataSource1.tPassword(password1);
dataSource1.tMinIdle(5);
dataSource1.tMaxActive(20);
dataSource1.tInitialSize(5);
dataSource1.tMaxWait(6000);
dataSourceMap.put("ds1", dataSource1);
return dataSourceMap;
}
@Bean
public Filter statFilter(){
StatFilter filter =new StatFilter();
filter.tSlowSqlMillis(5000);
filter.tLogSlowSql(true);
filter.tMergeSql(true);
return filter;
}
@Bean
public ServletRegistrationBean statViewServlet(){
//创建rvlet注册实体
ServletRegistrationBean rvletRegistrationBean =new ServletRegistrationBean(new StatViewServlet(),"/druid/*");
//设置ip⽩名单
rvletRegistrationBean.addInitParameter("allow","127.0.0.1");
