springboot2.4 + mybatis-plus3.4 + sharding-jdbc 5.0.0分库分表+读写分离实现案例

置顶 wpml_java

已于 2022-03-17 20:20:43 修改

阅读量1.3k

点赞数 1

文章标签： java spring boot mysql

于 2022-03-17 17:01:01 首次发布

wpml_java

本文链接：https://blog.csdn.net/qq_24931785/article/details/123553596

版权

记录一下 shardingsphere-jdbc 5.0.0版本分库分表 + 读写分离实现

本示例采用 Java bean 配置方式

代码已上传gitee仓库，感兴趣的自行下载查看，能力有限仅供参考
仓库地址：https://gitee.com/wpml/sharding-jdbc-demo.git

1、创建数据库表，数据库脚本放在sql文件夹下了

2、创建springboot项目，本示例版本采用 2.4.2
3、pom.xml

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <optional>true</optional>
</dependency>
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
    <version>5.0.0</version>
</dependency>
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.4.3</version>
</dependency>
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.2.3</version>
</dependency>

4、application.yml配置
在这里插入图片描述

5、核心配置讲解 DBConfig.java

package com.wpml.shardingjdbc.config;

import com.alibaba.druid.pool.DruidDataSource;
import com.wpml.shardingjdbc.constant.Constant;
import com.wpml.shardingjdbc.props.DatasourceProps;
import com.wpml.shardingjdbc.props.YamlDatasourceProp;
import lombok.extern.slf4j.Slf4j;
import org.apache.shardingsphere.driver.api.ShardingSphereDataSourceFactory;
import org.apache.shardingsphere.infra.config.algorithm.ShardingSphereAlgorithmConfiguration;
import org.apache.shardingsphere.readwritesplitting.api.ReadwriteSplittingRuleConfiguration;
import org.apache.shardingsphere.readwritesplitting.api.rule.ReadwriteSplittingDataSourceRuleConfiguration;
import org.apache.shardingsphere.sharding.api.config.ShardingRuleConfiguration;
import org.apache.shardingsphere.sharding.api.config.rule.ShardingTableRuleConfiguration;
import org.apache.shardingsphere.sharding.api.config.strategy.keygen.KeyGenerateStrategyConfiguration;
import org.apache.shardingsphere.sharding.api.config.strategy.sharding.StandardShardingStrategyConfiguration;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.*;

@Configuration
@Slf4j
public class DBConfig {

    private Map<String, DataSource> dataSourceMap = new HashMap<>();

    public DBConfig(DatasourceProps datasourceProps) throws SQLException {
        log.info(">>>>>> 初始化数据源信息 >>>>>>");
        Map<String, YamlDatasourceProp> datasource = datasourceProps.getDatasource();
        if (datasource != null) {
            for (Map.Entry<String, YamlDatasourceProp> dm : datasource.entrySet()) {
                YamlDatasourceProp ydp = dm.getValue();
                DruidDataSource ds = new DruidDataSource();
                ds.setUrl(ydp.getUrl());
                ds.setUsername(ydp.getUsername());
                ds.setPassword(ydp.getPassword());
                ds.setDriverClassName(ydp.getDriverClassName());
                ds.setMaxActive(ydp.getMaxActive());
                ds.setMaxWait(ydp.getMaxWait());
                ds.setInitialSize(ydp.getInitialSize());
                ds.setMinIdle(ydp.getMinIdle());
                ds.setFilters(ydp.getFilters());
                ds.setValidationQuery(ydp.getValidationQuery());
                ds.setTimeBetweenEvictionRunsMillis(ydp.getTimeBetweenEvictionRunsMillis());

                dataSourceMap.put(dm.getKey(), ds);
            }
        } else {
            throw new RuntimeException("请配置数据源信息");
        }
    }


    @Bean
    public DataSource dataSource() throws SQLException {
        // 分片规则配置
        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
        // 具体表分片规则配置信息
        Collection<ShardingTableRuleConfiguration> tableConfigs = shardingRuleConfig.getTables();
        // 分片算法配置
        Map<String, ShardingSphereAlgorithmConfiguration> shardingAlgorithms = shardingRuleConfig.getShardingAlgorithms();

        // t_order表分表配置
        orderTableConfig(tableConfigs, shardingAlgorithms);

        // t_brand表配置
        brandTableConfig(tableConfigs, shardingAlgorithms);

        // 配置主键生成策略
        shardingRuleConfig.setDefaultKeyGenerateStrategy(new KeyGenerateStrategyConfiguration("id", "snowflake"));

        Properties props = new Properties();
        // 配置sql打印信息（生产环境应屏蔽该配置）
        props.setProperty("sql-show", "true");

        ReadwriteSplittingRuleConfiguration readwriteConfig = readwriteConfig();
        return ShardingSphereDataSourceFactory.createDataSource(dataSourceMap, Arrays.asList(shardingRuleConfig,readwriteConfig), props);
    }

    private ReadwriteSplittingRuleConfiguration readwriteConfig() {

        // 配置读写分离（此处只有一组读写分离配置）
        ReadwriteSplittingDataSourceRuleConfiguration dataSourceConfig =
                new ReadwriteSplittingDataSourceRuleConfiguration("ds0",
                        null,
                        "ds_write",
                        Collections.singletonList("ds_read_01"),
                        "roundRobin");
//        ReadwriteSplittingDataSourceRuleConfiguration dataSourceConfiguration2 =
//                new ReadwriteSplittingDataSourceRuleConfiguration("ds_read_01", "static",  "ds_write", Collections.singletonList("ds_read_01"), "roundRobin");

        // 负载均衡
        Map<String, ShardingSphereAlgorithmConfiguration> loadBalanceMaps = new HashMap<>(1);
        loadBalanceMaps.put("roundRobin", new ShardingSphereAlgorithmConfiguration("ROUND_ROBIN", new Properties()));

        return new ReadwriteSplittingRuleConfiguration(Arrays.asList(dataSourceConfig), loadBalanceMaps);
    }

    /**
     * brand 分库分表策略配置
     *
     * @param tableConfigs
     * @param shardingAlgorithms
     */
    private void brandTableConfig(Collection<ShardingTableRuleConfiguration> tableConfigs, Map<String, ShardingSphereAlgorithmConfiguration> shardingAlgorithms) {
        log.info(">>>>>> 配置 brand 分库分表策略 >>>>>>");
        // 分表规则配置
        ShardingTableRuleConfiguration brandTableRuleConfig = new ShardingTableRuleConfiguration(Constant.BRAND_TABLE, "ds_0$->{1..2}.t_brand_0$->{1..2}");
        // 添加分片策略配置
        brandTableRuleConfig.setTableShardingStrategy(new StandardShardingStrategyConfiguration(
                Constant.BRAND_TABLE_SHARDING_COLUMN, Constant.BRAND_TABLE_SHARDING_ALGORITHM_NAME
        ));
        // 分库策略配置
        brandTableRuleConfig.setDatabaseShardingStrategy(new StandardShardingStrategyConfiguration(
                Constant.BRAND_DB_SHARDING_COLUMN, Constant.BRAND_DB_SHARDING_ALGORITHM_NAME
        ));
        tableConfigs.add(brandTableRuleConfig);
        // 分表算法
        Properties props = new Properties();
        props.setProperty("algorithm-expression", "t_brand_0$->{brand_id % 2 + 1}");
        shardingAlgorithms.put(Constant.BRAND_TABLE_SHARDING_ALGORITHM_NAME, new ShardingSphereAlgorithmConfiguration("INLINE", props));

        // 分库算法
        props = new Properties();
        props.setProperty("algorithm-expression", "ds_0$->{shard_key % 2 + 1}");
        shardingAlgorithms.put(Constant.BRAND_DB_SHARDING_ALGORITHM_NAME, new ShardingSphereAlgorithmConfiguration("INLINE", props));

    }

    /**
     * order 分表策略配置
     *
     * @param tableConfigs
     * @param shardingAlgorithms
     */
    private void orderTableConfig(Collection<ShardingTableRuleConfiguration> tableConfigs, Map<String, ShardingSphereAlgorithmConfiguration> shardingAlgorithms) {
        log.info(">>>>>> 配置 order 分表策略 >>>>>>");
        // 分表规则配置
        ShardingTableRuleConfiguration orderTableRuleConfig = new ShardingTableRuleConfiguration(
                "t_order",
                "ds_01.t_order_$->{2022..2023}0$->{1..9},ds_01.t_order_$->{2022..2023}$->{11..12}"
        );
        // 添加分片策略配置
        orderTableRuleConfig.setTableShardingStrategy(new StandardShardingStrategyConfiguration(
                Constant.ORDER_TABLE_SHARDING_COLUMN, Constant.ORDER_TABLE_SHARDING_ALGORITHM_NAME
        ));

        tableConfigs.add(orderTableRuleConfig);
        // 实现自定义分片策略
    shardingAlgorithms.put(Constant.ORDER_TABLE_SHARDING_ALGORITHM_NAME, new ShardingSphereAlgorithmConfiguration("YEAR-MONTH", new Properties()));
    }
}

6、自定义分片策略实现 DateShardingAlgorithm.java

package com.wpml.shardingjdbc.sharding;

import org.apache.shardingsphere.sharding.api.sharding.standard.PreciseShardingValue;
import org.apache.shardingsphere.sharding.api.sharding.standard.RangeShardingValue;
import org.apache.shardingsphere.sharding.api.sharding.standard.StandardShardingAlgorithm;
import org.springframework.stereotype.Component;

import java.util.Collection;

@Component
public class DateShardingAlgorithm implements StandardShardingAlgorithm<String> {
    @Override
    public String doSharding(Collection<String> tableNames, PreciseShardingValue<String> shardingValue) {
        System.out.println("=================== PreciseShardingValue ===================" + shardingValue.getValue());
        System.out.println("=================== PreciseShardingValue ===================" + shardingValue.getColumnName());
        for (String tableName : tableNames) {
            if (tableName.endsWith(shardingValue.getValue())) {
                return tableName;
            }
        }
        throw new IllegalArgumentException("未找到匹配的数据表");
    }
    @Override
    public Collection<String> doSharding(Collection<String> tableNames, RangeShardingValue<String> shardingValue) {
        System.out.println("=================== RangeShardingValue ===================" + shardingValue.getValueRange());
        System.out.println("=================== RangeShardingValue ===================" + shardingValue.getColumnName());
        // TODO
        return null;
    }
    @Override
    public void init() {}
    @Override
    public String getType() {
        return "YEAR-MONTH";
    }
}

8、SPI方式引入自定义策略，resources目录下创建META-INF/services目录，
此目录下新建 org.apache.shardingsphere.sharding.spi.ShardingAlgorithm 文件，文件中配置自定义策略类的全限定名。

com.wpml.shardingjdbc.sharding.DateShardingAlgorithm

如何使用自定义策略呢？
DateShardingAlgorithm.java配置类中定义了type类型 “YEAR-MONTH” 在这里插入图片描述

在DBConfig.java配置类中使用方式如下
在这里插入图片描述
启动项目进行测试
读写分离测试

分库分表测试：
一切正常将看到以上测试结果，如果有问题可以留言一起探讨

最后希望对大家有帮助，谢谢查阅

wpml_java

关注

1
点赞
踩
2

收藏

觉得还不错? 一键收藏
2
评论
springboot2.4 + mybatis-plus3.4 + sharding-jdbc 5.0.0分库分表+读写分离实现案例

记录一下 shardingsphere-jdbc 5.0.0版本分库分表 + 读写分离实现本示例采用 Java bean 配置方式代码已上传gitee仓库，感兴趣的自行下载查看，能力有限仅供参考1、创建数据库表，数据库脚本放在sql文件夹下了2、创建springboot项目，本示例版本采用 2.4.23、pom.xml<dependency> <groupId>org.springframework.boot</groupId> <
复制链接

扫一扫