Sharding-Sphere-JDBC水平分表，分库例子

水平分表

• 创建springboot项目
• 引入依赖

链文接原: https://tomoya92.github.io/2020/06/03/sharding-sphere-horizontal-split

    
        org.springframework.boot
        spring-boot-starter
    

    
        org.springframework.boot
        spring-boot-devtools
        runtime
        true
    
    
        mysql
        mysql-connector-java
        runtime
    
    
        org.springframework.boot
        spring-boot-configuration-processor
        true
    
    
        org.projectlombok
        lombok
        true
    
    
        org.springframework.boot
        spring-boot-starter-test
        test
        
            
                org.junit.vintage
                junit-vintage-engine
            
        
    
    
        com.baomidou
        mybatis-plus-boot-starter
        3.3.2
    
    
    
        org.apache.shardingsphere
        sharding-jdbc-spring-boot-starter
        4.1.0
    

• 添加配置文件

# 指定数据源名，如果有两个，用逗号隔开 如：ds0,ds1，相应的下面也要配置上ds1的连接地址
spring.shardingsphere.datasource.names=ds0
# 配置数据源
spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://localhost:3306/test?useSSL=false&characterEncoding=utf8&serverTimezone=Asia/Shanghai
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=123123
# 指定表名规则
spring.shardingsphere.sharding.tables.user.actual-data-nodes=ds0.user_$->{0..1}
spring.shardingsphere.sharding.tables.user.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.user.table-strategy.inline.algorithm-expression=user_$->{id % 2}
# 指定user表主键名以及主键生成策略SNOWFLAKE(雪花算法)
spring.shardingsphere.sharding.tables.user.key-generator.column=id
spring.shardingsphere.sharding.tables.user.key-generator.type=SNOWFLAKE
# 打印sql
spring.shardingsphere.props.sql.show=true

• 创建表， user_0,user_1 表结构一样

CREATE TABLE `user_0` (
  `id` bigint(20) NOT NULL,
  `username` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

• 配置mybatisplus

链文接原: https://tomoya92.github.io/2020/06/03/sharding-sphere-horizontal-split

import lombok.Data;

import java.io.Serializable;

@Data
public class User implements Serializable {

    private Long id;
    private String username;
    private int age;
}

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.shardingspheredemo.entity.User;

public interface UserMapper extends BaseMapper {
}

@SpringBootApplication
@MapperScan("com.example.shardingspheredemo.mapper")
public class ShardingSphereDemoApplication {

    public static void main(String[] args) {
        SpringApplication.run(ShardingSphereDemoApplication.class, args);
    }

}

• 测试方法

@SpringBootTest
class ShardingSphereDemoApplicationTests {

    @Autowired
    private UserMapper userMapper;

    Random random = new Random();

    @Test
    void addUser() {
        for (int i = 0; i < 10; i++) {
            User user = new User();
            user.setUsername("user_" + i);
            user.setAge(random.nextInt(100));
            userMapper.insert(user);
        }
    }

    @Test
    void listUser() {
        QueryWrapper wrapper = new QueryWrapper<>();
        wrapper.lambda().gt(User::getAge, 20).orderByAsc(User::getUsername);
        System.out.println(userMapper.selectList(wrapper));
    }

}

水平分库

分库配置，两个库分别是：test, test1，每个库中都有user_0,user_1两张表，结构都一样
配置文件

# 指定数据源名，如果有两个，用逗号隔开 如：ds0,ds1，相应的下面也要配置上ds1的连接地址
spring.shardingsphere.datasource.names=ds0,ds1
# 配置数据源
spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://localhost:3306/test?useSSL=false&characterEncoding=utf8&serverTimezone=Asia/Shanghai
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=123123
# ds1
spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds1.jdbc-url=jdbc:mysql://localhost:3306/test1?useSSL=false&characterEncoding=utf8&serverTimezone=Asia/Shanghai
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=123123
# 指定表名规则
spring.shardingsphere.sharding.tables.user.actual-data-nodes=ds$->{0..1}.user_$->{0..1}
spring.shardingsphere.sharding.tables.user.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.user.table-strategy.inline.algorithm-expression=user_$->{id % 2}
# 指定user表主键名以及主键生成策略SNOWFLAKE(雪花算法)
spring.shardingsphere.sharding.tables.user.key-generator.column=id
spring.shardingsphere.sharding.tables.user.key-generator.type=SNOWFLAKE
# 指定分库条件字段为age
spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=age
spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=ds$->{age>20?0:1}
# 打印sql
spring.shardingsphere.props.sql.show=true

我这里定的策略是，根据age判断，age>20的数据存到ds0里，其它数据存到ds1里，在数据入user表的时候，再根据id判断，偶数的进user_0表，奇数的进user_1表，测试方法跟上面一样
原文链接: