Sharding-JDBC简单实现水平分库初体验

一、介绍

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、依赖

<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库中，两张表顺利插入数据：