shardJDBC快速配置分表

林中见山海

于 2024-01-22 10:17:29 发布

阅读量413

点赞数 9

文章标签：数据库 java spring

本文链接：https://blog.csdn.net/weixin_44449775/article/details/135741370

版权

导入依赖：

       <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.1.1</version>
        </dependency>

配置分表策略


public class HashShardingStrategy implements PreciseShardingAlgorithm<Integer> {
    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Integer> shardingValue) {
        Integer inboxPlatform = shardingValue.getValue();
        // 根据业务调用平台方来分表
        String tb_index = getTbIndex(inboxPlatform,availableTargetNames.size());
        for (String each : availableTargetNames) {
            if (each.endsWith(tb_index)) {
                return each;
            }
        }
        throw new IllegalArgumentException(String.format("index为%s的表不存在",tb_index));
    }
    private String getTbIndex(Integer key,int count){
        return String.valueOf(key);
    }
}

配置主键生成算法
shardjdbc自带了两种主键生成算法，
UUIDShardingKeyGenerator：
UUID 虽然可以做到全局唯一性，但还是不推荐使用它作为主键，因为我们的实际业务中不管是 user_id 还是 order_id 主键多为整型，而 UUID 生成的是个 32 位的字符串
SnowflakeShardingKeyGenerator：
了解了雪花算法的主键 ID 组成后不难发现，这是一种严重依赖于服务器时间的算法，而依赖服务器时间的就会遇到一个棘手的问题：时钟回拨。
自定义主键生成算法：

注意：需要在资源文件目录下创建文件夹：META-INF/services


public class HashShardingStrategy implements PreciseShardingAlgorithm<Integer> {
    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Integer> shardingValue) {
        Integer inboxPlatform = shardingValue.getValue();
        // 根据业务调用平台方来分表
        String tb_index = getTbIndex(inboxPlatform,availableTargetNames.size());
        for (String each : availableTargetNames) {
            if (each.endsWith(tb_index)) {
                return each;
            }
        }
        throw new IllegalArgumentException(String.format("index为%s的表不存在",tb_index));
    }
    private String getTbIndex(Integer key,int count){
        return String.valueOf(key);
    }
}

4.xml配置



spring.shardingsphere.datasource.数据库名.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.数据库名.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.数据库名.url=jdbc:mysql://xxx
spring.shardingsphere.datasource.数据库名.username = xxx
spring.shardingsphere.datasource.数据库名.password = xxxx
spring.shardingsphere.datasource.数据库名.maxPoolPreparedStatementPerConnectionSize = 20
spring.shardingsphere.datasource.数据库名.maxWait = 60000
spring.shardingsphere.datasource.数据库名.initialSize = 5
spring.shardingsphere.datasource.数据库名.timeBetweenEvictionRunsMillis = 60000
spring.shardingsphere.datasource.数据库名.minEvictableIdleTimeMillis = 300000
spring.shardingsphere.datasource.数据库名.poolPreparedStatements = false
spring.shardingsphere.datasource.数据库名.minIdle = 5
spring.shardingsphere.datasource.数据库名.maxActive = 20
spring.shardingsphere.datasource.数据库名.testWhileIdle = true
spring.shardingsphere.datasource.数据库名.validationQuery = SELECT 1 FROM DUAL
spring.shardingsphere.datasource.数据库名.filters = stat,wall,log4j
spring.shardingsphere.datasource.数据库名.connectionProperties = servlets.stat.mergeSql=true;servlets.stat.slowSqlMillis=5000
spring.shardingsphere.datasource.数据库名.testOnReturn = true


spring.shardingsphere.sharding.default-data-source-name = 数据库名
#数据库表
#spring.shardingsphere.sharding.tables.数据库表.database-strategy.standard.sharding-column=id
#spring.shardingsphere.sharding.tables.数据库表.database-strategy.standard.precise-algorithm-class-name=com.xxx.config.DataBaseHashShardingStrategy
#spring.shardingsphere.sharding.tables.数据库表.actual-data-nodes=ds.数据库表,ds.image_info_cdn_test,ds.image_info_cdn_20$->{23..99}$->{(1..12).collect { it < 10 ? '0' + it : it }}
spring.shardingsphere.sharding.tables.数据库表.actual-data-nodes = 数据库名.数据库表_$->{(0..2)}
spring.shardingsphere.sharding.tables.数据库表.table-strategy.standard.sharding-column = inbox_platform
spring.shardingsphere.sharding.tables.数据库表.table-strategy.standard.precise-algorithm-class-name = com.xxx.config.HashShardingStrategy
spring.shardingsphere.sharding.tables.数据库表.key-generator.column = id
spring.shardingsphere.sharding.tables.数据库表.key-generator.type = GUID


spring.shardingsphere.sharding.binding-tables = 数据库表