【Java】ruoyi框架，添加ShardingJdbc支持分库分表

最新推荐文章于 2024-03-30 15:10:28 发布

还算善良_

最新推荐文章于 2024-03-30 15:10:28 发布

阅读量1.1k

点赞数 13

文章标签： java oracle 开发语言

本文链接：https://blog.csdn.net/weixin_46522411/article/details/136560324

版权

1、调研，目前主流的有Mycat、Sharding Jdbc ，准备选用后者；

分库分表，一种是按照时间范围进行，另外一种按照某个字段为分片键；

我这里，选用表数据中的DeptId字段作为分片键，这样能够保证同一个部门下的数据能够分布到同一个数据库下的同一张表中，避免跨库操作带来性能损失以及跨库操作带来的事物问题；

2、项目实战

2.1 配置项目环境

（1）添加项目pom

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

2.2 配置添加

（1）添加配置 application-sharding.yml（这部分主要是为了区分开分库分表的配置文件和其他，当然也可以直接在原有的配置文件基础上添加）

我这边默认按照两个数据库进行操作，其中处理了开票记录表和明细表如有需要自己添加；

我这边分片键按照部门ID进行对2取余数，这样能够保证同一个部门下的数据能够到相同的数据库下相同的表中，避免了跨库操作；分片键可以自己进行调整。

为什么是对2取余数？因为目前总共分出来2个数据库，dev_0、dev_1 如果是三个那就是对3取余，总之能够保证取余之后的范围在 0~n-1 ，n对应的是分出来的库数量。表也是一样的，我这边默认按照和库一样的规则。

spring:
  shardingsphere:
    enabled: true # 是否开启分库分表
    datasource:
      names: ds0,ds1
      ds0:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/xxx_dev_0?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8&nullCatalogMeansCurrent=true
        username: root
        password: root
      ds1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/xxx_dev_1?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8&nullCatalogMeansCurrent=true
        username: root
        password: root
    sharding:
      tables:
        output_base_invoice_history:
          name: tableName1
          database-strategy:  #分库策略
            inline:
              sharding-column: dept_id #分片列
              algorithm-expression: ds$->{dept_id % 2} # 计算数据分到哪个数据库
          actualDataNodes: ds$->{0..1}.tableName1_$->{0..1} # 映射到 ds0 和 ds1 数据源
          table-strategy:  #分表策略
            inline:
              algorithm-expression: tableName1_$->{dept_id % 2}
              sharding-column: dept_id

        output_base_history_invoice_items:
          name: tableName2
          database-strategy: #分库策略
            inline:
              sharding-column: dept_id #分片列
              algorithm-expression: ds$->{dept_id % 2} # 计算数据分到哪个数据库
          actualDataNodes: ds$->{0..1}.tableName2_$->{0..1} # 映射到 ds0 和 ds1 数据源
          table-strategy: #分表策略
            inline:
              algorithm-expression: tableName2_$->{dept_id % 2}
              sharding-column: dept_id
      props:
        sql:
          show: true

  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    druid:
      ds0:
        enabled: true
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/xxx_dev_0?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8&nullCatalogMeansCurrent=true
        username: root
        password: root
      ds1:
        enabled: true
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/xxx_dev_1?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8&nullCatalogMeansCurrent=true
        username: root
        password: root

（2）在ruoyi-framework工程下修改DruidConfig类，添加Sharding的动态数据源支持：

首先，添加SHARDING的枚举

/**
 * 数据源
 *
 * @author ruoyi
 */
public enum DataSourceType {
    /**
     * 主库
     */
    MASTER,

    /**
     * 从库
     */
    SLAVE,
    /**
     * 分库分表
     */
    SHARDING
}

其次，添加sharding规则到动态数据源

   @Bean(name = "dynamicDataSource")
    @Primary
    public DynamicDataSource dataSource(DataSource masterDataSource)
    {
        Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put(DataSourceType.MASTER.name(), masterDataSource);
        setDataSource(targetDataSources, DataSourceType.SLAVE.name(), "slaveDataSource");
        setDataSource(targetDataSources, DataSourceType.SHARDING.name(), "shardingDataSource");
        return new DynamicDataSource(masterDataSource, targetDataSources);
    }

之后，新建ShardingJdbc的配置类

/**
 * sharding 配置信息
 *
 * @author yanjun.hou
 * <p>
 * 用法：@DataSource(DataSourceType.SHARDING)
 * 在对应需要分表操作的service方法上加上注解
 * 如整个service都需要分表操作, 可直接将该注解加载类上
 */
@Configuration
public class ShardingDataSourceConfig {


    @Value("${spring.shardingsphere.sharding.tables.tableName1.name}")
    private String historyTable;
    @Value("${spring.shardingsphere.sharding.tables.tableName1.actualDataNodes}")
    private String historyTableActualDataNodes;
    @Value("${spring.shardingsphere.sharding.tables.tableName1.database-strategy.inline.sharding-column}")
    private String historyDbShardingColumn;
    @Value("${spring.shardingsphere.sharding.tables.tableName1.database-strategy.inline.algorithm-expression}")
    private String historyDbAlgorithmExpression;

    @Value("${spring.shardingsphere.sharding.tables.tableName1.table-strategy.inline.sharding-column}")
    private String historyTableShardingColumn;
    @Value("${spring.shardingsphere.sharding.tables.tableName1.table-strategy.inline.algorithm-expression}")
    private String historyTableAlgorithmExpression;


    @Value("${spring.shardingsphere.sharding.tables.tableName2.name}")
    private String historyItemsTable;
    @Value("${spring.shardingsphere.sharding.tables.tableName2.actualDataNodes}")
    private String historyItemsTableActualDataNodes;
    @Value("${spring.shardingsphere.sharding.tables.tableName2.database-strategy.inline.sharding-column}")
    private String historyItemsDbShardingColumn;
    @Value("${spring.shardingsphere.sharding.tables.tableName2.database-strategy.inline.algorithm-expression}")
    private String historyItemsDbAlgorithmExpression;

    @Value("${spring.shardingsphere.sharding.tables.tableName2.table-strategy.inline.sharding-column}")
    private String historyItemsTableShardingColumn;
    @Value("${spring.shardingsphere.sharding.tables.tableName2.table-strategy.inline.algorithm-expression}")
    private String historyItemsTableAlgorithmExpression;

    @Bean
    @ConfigurationProperties("spring.datasource.druid.ds0")
    @ConditionalOnProperty(prefix = "spring.datasource.druid.ds0", name = "enabled", havingValue = "true")
    public DataSource order1DataSource(DruidProperties druidProperties) {
        DruidDataSource dataSource = DruidDataSourceBuilder.create().build();
        return druidProperties.dataSource(dataSource);
    }

    @Bean
    @ConfigurationProperties("spring.datasource.druid.ds1")
    @ConditionalOnProperty(prefix = "spring.datasource.druid.ds1", name = "enabled", havingValue = "true")
    public DataSource order2DataSource(DruidProperties druidProperties) {
        DruidDataSource dataSource = DruidDataSourceBuilder.create().build();
        return druidProperties.dataSource(dataSource);
    }

    @Bean(name = "shardingDataSource")
    @ConditionalOnProperty(prefix = "spring.shardingsphere", name = "enabled", havingValue = "true")
    public DataSource shardingDataSource(@Qualifier("order1DataSource") DataSource order1DataSource, @Qualifier("order2DataSource") DataSource order2DataSource) throws SQLException {
        Map<String, DataSource> dataSourceMap = new HashMap<>();
        dataSourceMap.put("ds0", order1DataSource);
        dataSourceMap.put("ds1", order2DataSource);
 
        // 表规则配置
        TableRuleConfiguration invoiceHistory = new TableRuleConfiguration(historyTable, historyTableActualDataNodes);
        // 分库策略
        invoiceHistory.setDatabaseShardingStrategyConfig(new InlineShardingStrategyConfiguration(historyDbShardingColumn, historyDbAlgorithmExpression));
        // 分表策略
        invoiceHistory.setTableShardingStrategyConfig(new InlineShardingStrategyConfiguration(historyTableShardingColumn, historyTableAlgorithmExpression));

 
        // 表规则配置
        TableRuleConfiguration invoiceHistoryItems = new TableRuleConfiguration(historyItemsTable, historyItemsTableActualDataNodes);
        // 分库策略
        invoiceHistoryItems.setDatabaseShardingStrategyConfig(new InlineShardingStrategyConfiguration(historyItemsDbShardingColumn, historyItemsDbAlgorithmExpression));
        // 分表策略
        invoiceHistoryItems.setTableShardingStrategyConfig(new InlineShardingStrategyConfiguration(historyItemsTableShardingColumn, historyItemsTableAlgorithmExpression));

        // 分片规则
        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
        Collection<TableRuleConfiguration> tableRuleConfigs = shardingRuleConfig.getTableRuleConfigs();
        tableRuleConfigs.add(invoiceHistory);
        tableRuleConfigs.add(invoiceHistoryItems);
        // 获取数据源对象
        DataSource dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, getProperties());
        return dataSource;
    }

    /**
     * 系统参数配置
     */
    private Properties getProperties() {
        Properties shardingProperties = new Properties();
        shardingProperties.put("sql.show", true);
        return shardingProperties;
    }
}

最后，用法

首先，需要在application.yml中添加激活配置

active: dev,sharding

其次，在对应业务Mapper或者ServiceImpl 或者对应的方法上添加 @DataSource(DataSourceType.SHARDING) 即可。

第一种
@DataSource(DataSourceType.SHARDING)
public interface xxxMapper {}

第二种
@DataSource(DataSourceType.SHARDING)
public class xxxServiceImpl {}

第三种
@Override
@DataSource(DataSourceType.SHARDING)
 public XXX xxx() {
     return xxx;
 }