一、介绍
sharding-jdbc是轻量级的java框架,是增强版的JDBC驱动
sharding-jdbc并不是做分库分表,是操作多个库多个表
sharding-jdbc主要两个功能:数据分片和读写分离
sharding-jdbc目的是:简化对分库分表之后的数据相关操作
参考文档:
二、实操-使用Sharding-JDBC操作水平分库
1、按照水平分库的方式,创建数据库和表
1)创建数据库sharding_jdbc_user_db_1和sharding_jdbc_user_db_2
2)在两个数据库中创建两张表或多张表,这里分别是user_1、user_2
3)记录插入规则:根据routeId,如果为偶数,将数据添加到sharding_jdbc_user_db_1库中;如果routeId为奇数,将数据添加到sharding_jdbc_user_db_2库中、根据主键id,如果主键id为偶数,将数据添加如user_1中;如果主键id为奇数,将数据添加到user_2中。
2、环境
Spring Boot、Mybatis-Plus、Sharding-JDBC、Druid连接池、Mysql
3、依赖
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.4.2</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.23</version>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.0.0-RC1</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
</dependency>
4、配置sharding-jdbc分片策略
#sharding-jdbc分片策略
#配置数据源,给数据源起名称,水平分库,配置两个数据源
spring.shardingsphere.datasource.names = m1,m2
#配置数据源1具体内容,包含连接池,驱动,地址,用户名和密码
spring.shardingsphere.datasource.m1.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver = com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.url = jdbc:mysql://localhost:3306/sharding_jdbc_user_db_1?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m1.username = root
spring.shardingsphere.datasource.m1.password = root
#配置数据源2具体内容,包含连接池,驱动,地址,用户名和密码
spring.shardingsphere.datasource.m2.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m2.driver = com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m2.url = jdbc:mysql://localhost:3306/sharding_jdbc_user_db_2?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m2.username = root
spring.shardingsphere.datasource.m2.password = root
#指定数据库分布情况,数据库里面表分布情况
#m1,m2 -> user_1,user_2
spring.shardingsphere.sharding.tables.user.actual-data-nodes = m$->{1..2}.user_$->{1..2}
#指定user表里面主键生成策略
spring.shardingsphere.sharding.tables.user.key-generator.column = uid
spring.shardingsphere.sharding.tables.user.key-generator.type = SNOWFLAKE
# 指定数据库分片策略,约定routeId值偶数添加到m1,如果routeId是奇数添加到m2
#spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column = routeId
#spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression = m$->{routeId %2 + 1}
#上面方式可以,下面也可以
spring.shardingsphere.sharding.tables.user.database-strategy.inline.sharding-column = routeid
spring.shardingsphere.sharding.tables.user.database-strategy.inline.algorithm-expression = m$->{routeid %2 + 1}
# 指定表分片策略,约定uid值偶数添加到user_1表,如果cid是奇数添加到user_2
spring.shardingsphere.sharding.tables.user.table-strategy.inline.sharding-column = uid
spring.shardingsphere.sharding.tables.user.table-strategy.inline.algorithm-expression = user_$->{uid % 2 + 1}
#打开sql输出日志
spring.shardingsphere.props.sql.show = true
spring.main.allow-bean-definition-overriding = true
5、编写model、Mapper
@Data
@ToString
public class User {
private Long uid;
private String name;
private String tellphone;
private Integer routeid;
}
@Repository
public interface UserMapper extends BaseMapper<User> {
}
6、测试
@SpringBootTest
@RunWith(SpringRunner.class)
public class TestClass {
@Resource
private UserMapper userMapper;
@Test
public void addUserDb1(){
User user = new User();
user.setName("tom");
user.setTellphone("111");
user.setRouteid(2);
for(int i=0; i<6; i++){
userMapper.insert(user);
}
}
@Test
public void addUserDb2(){
User user = new User();
user.setName("tom");
user.setTellphone("111");
user.setRouteid(1);
for(int i=0; i<6; i++){
userMapper.insert(user);
}
}
}
7、结果
sharding-jdbc-user_1库中,两张表顺利插入数据:
sharding-jdbc-user_2库中,两张表顺利插入数据: