• ruoyi框架中添加sharding sphere5.0.0分表（通过spi添加自定义分表策略）

    sharding shphere作为一个分库分表组件，在处理分表业务时，比起自己实现分表功能，还是有很多明显优势的。

    对于一个从0开始的springboot项目，添加sharding sphere分库分表是比较简单的，有时需要在已有的程序框架中添加sharding sphere的分库分表功能，这就需要根据框架本身的特点进行特定的配置了。

    1. 添加依赖

    在ruoyi-framework\pom.xml模块添加sharding-jdbc整合依赖:

    1. <!-- sharding-jdbc分库分表 -->
    2. <dependency>
    3.     <groupId>org.apache.shardingsphere</groupId>
    4.     <artifactId>shardingsphere-jdbc-core</artifactId>
    5.     <version>5.0.0</version>
    6. </dependency>

    2. 创建测试库

    这里使用和主程序相同的数据库，所以不需要单独的创建数据库。

    3. 创建两个测试订单表

    1. create table sys_order
    2. (
    3.   order_id      bigint(20)      not null                   comment '订单ID',
    4.   user_id       bigint(64)      not null                   comment '用户编号',
    5.   status        char(1)         not null                   comment '状态（0交易成功 1交易失败）',
    6.   order_no      varchar(64)     default null               comment '订单流水',
    7.   primary key (order_id)
    8. ) engine=innodb comment = '订单信息表';
    9.  
    10. create table sys_order_0
    11. (
    12.   order_id      bigint(20)      not null                   comment '订单ID',
    13.   user_id       bigint(64)      not null                   comment '用户编号',
    14.   status        char(1)         not null                   comment '状态（0交易成功 1交易失败）',
    15.   order_no      varchar(64)     default null               comment '订单流水',
    16.   primary key (order_id)
    17. ) engine=innodb comment = '订单信息表';
    18.  
    19.  
    20. create table sys_order_1
    21. (
    22.   order_id      bigint(20)      not null                   comment '订单ID',
    23.   user_id       bigint(64)      not null                   comment '用户编号',
    24.   status        char(1)         not null                   comment '状态（0交易成功 1交易失败）',
    25.   order_no      varchar(64)     default null               comment '订单流水',
    26.   primary key (order_id)
    27. ) engine=innodb comment = '订单信息表';

    4. 配置文件添加数据源

    在application-druid.yml添加测试数据源，位置和主数据源同级位置：

    1. # 数据源配置
    2. spring:
    3.     datasource:
    4.         type: com.alibaba.druid.pool.DruidDataSource
    5.         driverClassName: com.mysql.cj.jdbc.Driver
    6.         druid:
    7.             # 主库数据源
    8.             master:
    9.                 url: jdbc:mysql://localhost:3306/ry473?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
    10.                 username: root
    11.                 password: password
    12.             # 订单库1
    13.             shardsource:
    14.                 enabled: true
    15.                 url: jdbc:mysql://localhost:3306/ry473?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
    16.                 username: root
    17.                 password: password

    5. 代码生成工具生成代码组件

    通过代码生成功能生成sys_order相关组件并添加到项目中，包括页面、控制器、服务层、mapper以及mapper.xml文件等。

    6. 添加shardingsphere配置类

    1. package com.ruoyi.framework.config;
    2.  
    3. import java.sql.SQLException;
    4. import java.util.Collections;
    5. import java.util.HashMap;
    6. import java.util.Map;
    7. import java.util.Properties;
    8. import javax.sql.DataSource;
    9. import org.apache.shardingsphere.driver.api.ShardingSphereDataSourceFactory;
    10. import org.apache.shardingsphere.infra.config.algorithm.ShardingSphereAlgorithmConfiguration;
    11. import org.apache.shardingsphere.sharding.api.config.ShardingRuleConfiguration;
    12. import org.apache.shardingsphere.sharding.api.config.rule.ShardingTableRuleConfiguration;
    13. import org.apache.shardingsphere.sharding.api.config.strategy.keygen.KeyGenerateStrategyConfiguration;
    14. import org.apache.shardingsphere.sharding.api.config.strategy.sharding.StandardShardingStrategyConfiguration;
    15.  
    16. import org.springframework.beans.factory.annotation.Qualifier;
    17. import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
    18. import org.springframework.boot.context.properties.ConfigurationProperties;
    19. import org.springframework.context.annotation.Bean;
    20. import org.springframework.context.annotation.Configuration;
    21. import com.alibaba.druid.pool.DruidDataSource;
    22. import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
    23. import com.ruoyi.framework.config.properties.DruidProperties;
    24.  
    25. /**
    26.  * sharding 配置信息
    27.  *
    28.  * @author ruoyi
    29.  */
    30. @Configuration
    31. public class ShardingDataSourceConfig
    32. {
    33.     @Bean
    34.     @ConfigurationProperties("spring.datasource.druid.shardsource")
    35.     @ConditionalOnProperty(prefix = "spring.datasource.druid.shardsource", name = "enabled", havingValue = "true")
    36.     public DataSource shardDataSource(DruidProperties druidProperties)
    37.     {
    38.         DruidDataSource dataSource = DruidDataSourceBuilder.create().build();
    39.         return druidProperties.dataSource(dataSource);
    40.     }
    41.  
    42. //    @Bean
    43. //    @ConfigurationProperties("spring.datasource.druid.order2")
    44. //    @ConditionalOnProperty(prefix = "spring.datasource.druid.order2", name = "enabled", havingValue = "true")
    45. //    public DataSource order2DataSource(DruidProperties druidProperties)
    46. //    {
    47. //        DruidDataSource dataSource = DruidDataSourceBuilder.create().build();
    48. //        return druidProperties.dataSource(dataSource);
    49. //    }
    50.  
    51.     @Bean(name = "shardingDataSource")
    52.     public DataSource shardingDataSource(@Qualifier("shardDataSource") DataSource shardDataSource) throws SQLException
    53.     {
    54.         Map<String, DataSource> dataSourceMap = new HashMap<>();
    55.         dataSourceMap.put("order", shardDataSource);
    56.         // dataSourceMap.put("order2", order2DataSource);
    57.  
    58.         // sys_order 表规则配置
    59.         // TableRuleConfiguration orderTableRuleConfig = new TableRuleConfiguration("sys_order", "order$->{1..2}.sys_order_$->{0..1}");
    60.  
    61.         ShardingTableRuleConfiguration orderTableRuleConfig = new ShardingTableRuleConfiguration("sys_order", "order.sys_order_$->{0..1}");
    62.  
    63.         orderTableRuleConfig.setTableShardingStrategy(
    64.                 new StandardShardingStrategyConfiguration("order_id", "tableShardingAlgorithm"));
    65.  
    66.         // 配置分库策略
    67.         // orderTableRuleConfig.setDatabaseShardingStrategyConfig(new InlineShardingStrategyConfiguration("user_id", "order$->{user_id % 2 + 1}"));
    68.         // 配置分表策略
    69.         // orderTableRuleConfig.setTableShardingStrategyConfig(new InlineShardingStrategyConfiguration("order_id", "sys_order_$->{order_id % 2}"));
    70.  
    71.         // 分布式主键
    72.         orderTableRuleConfig.setKeyGenerateStrategy(new KeyGenerateStrategyConfiguration("order_id", "snowflake"));
    73.  
    74.         // 配置分片规则
    75.         ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
    76.         shardingRuleConfig.getTables().add(orderTableRuleConfig);
    77.  
    78.         Properties pDatabase = new Properties();
    79.         pDatabase.setProperty("shardCount", "2");
    80.  
    81.         // 设置分表策略
    82.         ShardingSphereAlgorithmConfiguration ssactable = new ShardingSphereAlgorithmConfiguration(
    83.                 "ORDER_ID_SHARD", pDatabase);
    84.         shardingRuleConfig.getShardingAlgorithms().put("tableShardingAlgorithm", ssactable);
    85.  
    86.         // 内置Snowflake分布式序列算法配置
    87.         Properties snowflakeProp = new Properties();
    88.         snowflakeProp.setProperty("worker-id", "1");
    89.         shardingRuleConfig.getKeyGenerators().put("snowflake",
    90.                 new ShardingSphereAlgorithmConfiguration("SNOWFLAKE", snowflakeProp));
    91.  
    92.         // 获取数据源对象
    93.         DataSource dataSource = ShardingSphereDataSourceFactory.createDataSource(dataSourceMap,
    94.                 Collections.singleton(shardingRuleConfig),
    95.                 getProperties());
    96.         return dataSource;
    97.     }
    98.  
    99.     /**
    100.      * 系统参数配置
    101.      */
    102.     private Properties getProperties()
    103.     {
    104.         Properties shardingProperties = new Properties();
    105.         shardingProperties.put("sql.show", true);
    106.         return shardingProperties;
    107.     }
    108. }

    7. druid配置类配置

    1. @Bean(name = "dynamicDataSource")
    2.     @Primary
    3.     public DynamicDataSource dataSource(DataSource masterDataSource)
    4.     {
    5.         Map<Object, Object> targetDataSources = new HashMap<>();
    6.         targetDataSources.put(DataSourceType.MASTER.name(), masterDataSource);
    7.         setDataSource(targetDataSources, DataSourceType.SLAVE.name(), "slaveDataSource");
    8.         setDataSource(targetDataSources, DataSourceType.SHARDING.name(), "shardingDataSource");
    9.         return new DynamicDataSource(masterDataSource, targetDataSources);
    10.     }

    8. 添加分表策略

    1. package com.ruoyi.framework.config;
    2.  
    3. import java.util.Collection;
    4. import java.util.Properties;
    5. import java.util.regex.Matcher;
    6. import java.util.regex.Pattern;
    7.  
    8. import org.apache.shardingsphere.sharding.api.sharding.standard.PreciseShardingValue;
    9. import org.apache.shardingsphere.sharding.api.sharding.standard.RangeShardingValue;
    10. import org.apache.shardingsphere.sharding.api.sharding.standard.StandardShardingAlgorithm;
    11. import org.slf4j.Logger;
    12. import org.slf4j.LoggerFactory;
    13. import org.springframework.beans.factory.annotation.Value;
    14. import org.springframework.stereotype.Component;
    15.  
    16. @Component
    17. public class TableShardingAlgorithm implements StandardShardingAlgorithm<Long>{
    18.  
    19.     private static Logger log = LoggerFactory.getLogger(TableShardingAlgorithm.class);
    20.  
    21.     private Properties props;
    22.  
    23.     // @Value("${sharding.shardcount}")
    24.     private Long shardCount = 2l;
    25.  
    26.  
    27.     public Properties getProps() {
    28.         return props;
    29.     }
    30.  
    31.     public void setProps(Properties props) {
    32.         this.props = props;
    33.     }
    34.  
    35.     @Override
    36.     public String doSharding(Collection<String> tableNames, PreciseShardingValue<Long> shardingValue) {
    37.  
    38.         Long lShard = 0l;
    39.         lShard = (Long)shardingValue.getValue();
    40.  
    41.         // 读取设置的参数
    42.         if(props != null)
    43.         {
    44.             String shardCountStr = props.getProperty("shardCount", "2");
    45.             shardCount = Long.parseLong(shardCountStr);
    46.         }
    47.         
    48.         Long lowMod = lShard % 2;
    49.  
    50.  
    51.         Long shardTableMod = lowMod;
    52.  
    53.         // 转换为带格式的字符串
    54.         String shardModStr = "_" + shardTableMod;
    55.  
    56.         for (String table : tableNames) {
    57.             if (table.endsWith(shardModStr)) {
    58.                 return table;
    59.             }
    60.         }
    61.  
    62.         return "";
    63.     }
    64.  
    65.     @Override
    66.     public String getType() {
    67.         // TODO Auto-generated method stub
    68.         return "ORDER_ID_SHARD";
    69.  
    70.     }
    71.  
    72.     @Override
    73.     public void init() {
    74.         // TODO Auto-generated method stub
    75.  
    76.     }
    77.  
    78.     @Override
    79.     public Collection<String> doSharding(Collection<String> tableNames,
    80.                                          RangeShardingValue<Long> shardingValue) {
    81.  
    82.         return null;
    83.  
    84.     }    
    85.  
    86. }

    9. 通过spi设置分表策略

    在resource目录下创建META-INF\services目录，创建文件org.apache.shardingsphere.sharding.spi.ShardingAlgorithm，并在文件中添加如下内容：

    com.ruoyi.framework.config.TableShardingAlgorithm

    自定义的策略，需要通过spi机制进行配置。 

    10. 在服务的实现文件中添加注解

    通过注解@DataSource(DataSourceType.SHARDING)，框架可以判断对应的操作需要使用分表的数据库连接。

    1. package com.ruoyi.system.service.impl;
    2.  
    3. import java.util.List;
    4.  
    5. import com.ruoyi.common.annotation.DataSource;
    6. import com.ruoyi.common.enums.DataSourceType;
    7. import org.springframework.beans.factory.annotation.Autowired;
    8. import org.springframework.stereotype.Service;
    9. import com.ruoyi.system.mapper.SysOrderMapper;
    10. import com.ruoyi.system.domain.SysOrder;
    11. import com.ruoyi.system.service.ISysOrderService;
    12. import com.ruoyi.common.core.text.Convert;
    13.  
    14. /**
    15.  * 订单信息Service业务层处理
    16.  * 
    17.  * @author ruoyi
    18.  * @date 2022-06-27
    19.  */
    20. @Service
    21. public class SysOrderServiceImpl implements ISysOrderService 
    22. {
    23.     @Autowired
    24.     private SysOrderMapper sysOrderMapper;
    25.  
    26.     /**
    27.      * 查询订单信息
    28.      * 
    29.      * @param orderId 订单信息主键
    30.      * @return 订单信息
    31.      */
    32.     @Override
    33.     @DataSource(DataSourceType.SHARDING)
    34.     public SysOrder selectSysOrderByOrderId(Long orderId)
    35.     {
    36.         return sysOrderMapper.selectSysOrderByOrderId(orderId);
    37.     }
    38.  
    39.     /**
    40.      * 查询订单信息列表
    41.      * 
    42.      * @param sysOrder 订单信息
    43.      * @return 订单信息
    44.      */
    45.     @Override
    46.     @DataSource(DataSourceType.SHARDING)
    47.     public List<SysOrder> selectSysOrderList(SysOrder sysOrder)
    48.     {
    49.         return sysOrderMapper.selectSysOrderList(sysOrder);
    50.     }
    51.  
    52.     /**
    53.      * 新增订单信息
    54.      * 
    55.      * @param sysOrder 订单信息
    56.      * @return 结果
    57.      */
    58.     @Override
    59.     @DataSource(DataSourceType.SHARDING)
    60.     public int insertSysOrder(SysOrder sysOrder)
    61.     {
    62.         return sysOrderMapper.insertSysOrder(sysOrder);
    63.     }
    64.  
    65.     /**
    66.      * 修改订单信息
    67.      * 
    68.      * @param sysOrder 订单信息
    69.      * @return 结果
    70.      */
    71.     @Override
    72.     @DataSource(DataSourceType.SHARDING)
    73.     public int updateSysOrder(SysOrder sysOrder)
    74.     {
    75.         return sysOrderMapper.updateSysOrder(sysOrder);
    76.     }
    77.  
    78.     /**
    79.      * 批量删除订单信息
    80.      * 
    81.      * @param orderIds 需要删除的订单信息主键
    82.      * @return 结果
    83.      */
    84.     @Override
    85.     @DataSource(DataSourceType.SHARDING)
    86.     public int deleteSysOrderByOrderIds(String orderIds)
    87.     {
    88.         return sysOrderMapper.deleteSysOrderByOrderIds(Convert.toStrArray(orderIds));
    89.     }
    90.  
    91.     /**
    92.      * 删除订单信息信息
    93.      * 
    94.      * @param orderId 订单信息主键
    95.      * @return 结果
    96.      */
    97.     @Override
    98.     @DataSource(DataSourceType.SHARDING)
    99.     public int deleteSysOrderByOrderId(Long orderId)
    100.     {
    101.         return sysOrderMapper.deleteSysOrderByOrderId(orderId);
    102.     }
    103. }

    11. 执行测试

    通过页面，可以添加和删除数据，在数据库表中，可以看到数据被保存到不同的表中了。

    表sys_order_0中的数据：

    表sys_order_1中的数据： 

     

     可以看出尾数为奇数的数据到了sys_order_1，尾数为偶数的数据到了sys_order_0。

  • 相关阅读:
    7.3 服务端漏洞：密码找回逻辑漏洞检测和重现
    three.js中直线的创建，以及new THREE.LineBasicMaterial不能设置宽度问题。
    某云负载均衡获取客户端真实IP的问题
    三家前装出货超2万台，激光雷达明年「放量」是大概率事件
    ros2原来本是一个通信协议
    C语言中获得结构体成员的相对偏移量(Linux内核源码解读)
    疯了！全网居然有人一次性把Java虚拟机HotSpot 给讲透彻了
    Eigen中三维位姿表示方式以及相互转换
    目标检测中生成锚框函数详解
    SpringCloudAlibaba系列微服务搭建笔记二-RestTemplate+Ribbon
  • 原文地址：https://blog.csdn.net/liaomingwu/article/details/125482377