sharding-jdbc是轻量级的java框架,是增强版的JDBC驱动
sharding-jdbc并不是做分库分表,是操作多个库多个表
sharding-jdbc主要两个功能:数据分片和读写分离
sharding-jdbc目的是:简化对分库分表之后的数据相关操作
参考文档:
ShardingSphere-JDBC :: ShardingSpherehttps://shardingsphere.apache.org/document/current/cn/user-manual/shardingsphere-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、依赖
org.springframework.boot spring-boot-starter-webcom.baomidou mybatis-plus-boot-starter3.4.2 mysql mysql-connector-javacom.alibaba druid-spring-boot-starter1.1.23 org.apache.shardingsphere sharding-jdbc-spring-boot-starter4.0.0-RC1 org.projectlombok lombokorg.springframework.boot spring-boot-starter-test
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 {
}
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库中,两张表顺利插入数据:



