若依前后端分离项目整合shardingjdbc分表(详细，分片字段订单id)

文章目录

- - 1. 引入Maven依赖
  - 2.引入配置文件
  - 3.兼容之前的数据库源,使用现在的sharding数据库源（shardingjdbc默认的数据源），但是配置好文件之后是没有生效的，需要加配置文件覆盖
  - 4. 检测是否成功
  - 5. 如何使用，在需要使用的类或者方法上面加上如下注解 @DataSource(DataSourceType.SHARDING)

1. 引入Maven依赖

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

注意：是否和你的springboot是兼容的。

2.引入配置文件

# 项目相关配置
ruoyi:
  # 名称
  name: RuoYi
  # 版本
  version: 3.8.7
  # 版权年份
  copyrightYear: 2023
  # 文件路径 示例（ Windows配置D:/ruoyi/uploadPath，Linux配置 /home/ruoyi/uploadPath）
  profile: D:/ruoyi/uploadPath
  # 获取ip地址开关
  addressEnabled: false
  # 验证码类型 math 数字计算 char 字符验证
  captchaType: math
# 开发环境配置
server:
  # 服务器的HTTP端口，默认为8080
  port: 28001
  servlet:
    # 应用的访问路径
    context-path: /
  tomcat:
    # tomcat的URI编码
    uri-encoding: UTF-8
    # 连接数满后的排队数，默认为100
    accept-count: 1000
    threads:
      # tomcat最大线程数，默认为200
      max: 800
      # Tomcat启动初始化的线程数，默认值10
      min-spare: 100

# 日志配置
logging:
  level:
    com.ruoyi: debug
    org.springframework: warn
    io.swagger.models.parameters.AbstractSerializableParameter: error
#    config: ./conf/logback-spring-admin.xml
  log:
    level: info
    path: ./logs


#logging:
#  level:
#    io.swagger.models.parameters.AbstractSerializableParameter: error
#  config:  conf/logback-spring-admin.xml
#log:
#  level: info
#  path: ./logs


# 用户配置
user:
  password:
    # 密码最大错误次数
    maxRetryCount: 10
    # 密码锁定时间（默认10分钟）
    lockTime: 5

# Spring配置
spring:
  main:
    allow-bean-definition-overriding: true
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    druid:
      # 主库数据源
#      master:
#        url: jdbc:mysql://localhost:3306/antcash?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
#        username: root
#        password: xxxxxx
    # 实际开发库
      master:
        url: jdbc:mysql://xxxxxxx:3306/antcash_dev_db?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
        username: antcash_dev
        password: xxxxxx

#        localhost
#        url: jdbc:mysql://localhost:3306/antcash?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
#        username: root
#        password: roothouzhicong

#  Brazil
#        url: jdbc:mysql://52.3.3.7:3306/runscore?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
#        username: runscore
#        password: gz_rs2023!
      # 从库数据源
      slave:
        # 从数据源开关/默认关闭
        enabled: false
        url:
        username:
        password:
      # 初始连接数
      initialSize: 5
      # 最小连接池数量
      minIdle: 10
      # 最大连接池数量
      maxActive: 20
      # 配置获取连接等待超时的时间
      maxWait: 60000
      # 配置连接超时时间
      connectTimeout: 30000
      # 配置网络超时时间
      socketTimeout: 60000
      # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
      timeBetweenEvictionRunsMillis: 60000
      # 配置一个连接在池中最小生存的时间，单位是毫秒
      minEvictableIdleTimeMillis: 300000
      # 配置一个连接在池中最大生存的时间，单位是毫秒
      maxEvictableIdleTimeMillis: 900000
      # 配置检测连接是否有效
      validationQuery: SELECT 1 FROM DUAL
      testWhileIdle: true
      testOnBorrow: false
      testOnReturn: false
      webStatFilter:
        enabled: true
      statViewServlet:
        enabled: true
        # 设置白名单，不填则允许所有访问
        allow:
        url-pattern: /druid/*
        # 控制台管理用户名和密码
        login-username: ruoyi
        login-password: 123456
      filter:
        stat:
          enabled: true
          # 慢SQL记录
          log-slow-sql: true
          slow-sql-millis: 2000
          merge-sql: true
        wall:
          config:
            multi-statement-allow: true

  shardingsphere:
    props:
      max:
        connections:
          size:
            per:
              query: 5
    datasource:
      names: sharding
      sharding:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://xxxxxxx:3306/antcash_dev_db?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
        username: xxx
        password: xxxx
#        url: jdbc:mysql://xxxxxxx:3306/runscore?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
#        username: runscore
#        password: xxxxxx!
    sharding:
      tables:
        merchant_order:
          actual-data-nodes: sharding.merchant_order_$->{1..50}
          table-strategy:
            standard:
              sharding-column: id
              precise-algorithm-class-name: com.ruoyi.common.config.TableShardingAlgorithm
        merchant_order_pay_info:
          actual-data-nodes: sharding.merchant_order_pay_info_$->{1..50}
          table-strategy:
            standard:
              sharding-column: id
              precise-algorithm-class-name: com.ruoyi.common.config.TableShardingAlgorithm
        account_change_log:
          actual-data-nodes: sharding.account_change_log_$->{1..50}
          table-strategy:
            standard:
              sharding-column: id
              precise-algorithm-class-name: com.ruoyi.common.config.TableShardingAlgorithm
#    props:
#      sql-show: true
#      executor-size: 16

  # 资源信息
  messages:
    # 国际化资源文件路径
    basename: i18n/messages
#  profiles:
#    active: druid
  # 文件上传
  servlet:
    multipart:
      # 单个文件大小
      max-file-size: 10MB
      # 设置总上传的文件大小
      max-request-size: 20MB
  # 服务模块
  devtools:
    restart:
      # 热部署开关
      enabled: true
  # redis 配置
  redis:
    # 地址
    host: xxxxxx
#    host: localhost
    # 端口，默认为6379
    port: 6378
    # 数据库索引
    database: 0
    # 密码
    password:
    # 连接超时时间
    timeout: 10s
    lettuce:
      pool:
        # 连接池中的最小空闲连接
        min-idle: 0
        # 连接池中的最大空闲连接
        max-idle: 8
        # 连接池的最大数据库连接数
        max-active: 8
        # #连接池最大阻塞等待时间（使用负值表示没有限制）
        max-wait: -1ms

# token配置
token:
  # 令牌自定义标识
  header: Authorization
  # 令牌密钥
  secret: abcdefghijklmnopqrstuvwxyz
  # 令牌有效期（默认30分钟）
  expireTime: 300

# MyBatis配置
#mybatis:
#  # 搜索指定包别名
#  typeAliasesPackage: com.ruoyi.**.domain
#  # 配置mapper的扫描，找到所有的mapper.xml映射文件
#  mapperLocations: classpath*:mapper/**/*.xml
#  # 加载全局的配置文件
#  configLocation: classpath:mybatis/mybatis-config.xml
mybatis-plus:
  # 搜索指定包别名
  typeAliasesPackage: com.ruoyi.**.domain
  # 配置mapper的扫描，找到所有的mapper.xml映射文件
  mapperLocations: classpath*:mapper/**/*Mapper.xml
  # 加载全局的配置文件
  configLocation: classpath:mybatis/mybatis-config.xml

# PageHelper分页插件
pagehelper:
  helperDialect: mysql
  supportMethodsArguments: true
  params: count=countSql

# Swagger配置
swagger:
  # 是否开启swagger
  enabled: false
  # 请求前缀
  pathMapping: /dev-api

# 防止XSS攻击
xss:
  # 过滤开关
  enabled: true
  # 排除链接（多个用逗号分隔）
  excludes: /system/notice
  # 匹配链接
  urlPatterns: /system/*,/monitor/*,/tool/*

  configuration:
    map-underscore-to-camel-case: true

3.兼容之前的数据库源,使用现在的sharding数据库源（shardingjdbc默认的数据源），但是配置好文件之后是没有生效的，需要加配置文件覆盖

在注解DataSourceType里面加上sharding

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

    /**
     * 从库
     */
    SLAVE,

    SHARDING,
}

DruidConfig加上shardingjdbcsource数据源：

package com.ruoyi.framework.config;

import java.io.IOException;
import java.util.HashMap;
import java.util.Map;
import javax.annotation.PostConstruct;
import javax.servlet.Filter;
import javax.servlet.FilterChain;
import javax.servlet.ServletException;
import javax.servlet.ServletRequest;
import javax.servlet.ServletResponse;
import javax.sql.DataSource;
import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import com.alibaba.druid.spring.boot.autoconfigure.properties.DruidStatProperties;
import com.alibaba.druid.util.Utils;
import com.ruoyi.common.enums.DataSourceType;
import com.ruoyi.common.utils.spring.SpringUtils;
import com.ruoyi.framework.config.properties.DruidProperties;
import com.ruoyi.framework.datasource.DynamicDataSource;

import static com.ruoyi.framework.datasource.DynamicDataSourceContextHolder.log;

/**
 * druid 配置多数据源
 *
 * @author ruoyi
 */
@Configuration
public class DruidConfig
{
    @Bean
    @ConfigurationProperties("spring.datasource.druid.master")
    public DataSource masterDataSource(DruidProperties druidProperties)
    {
        DruidDataSource dataSource = DruidDataSourceBuilder.create().build();
        return druidProperties.dataSource(dataSource);
    }

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

        return druidProperties.dataSource(dataSource);
    }



    @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);
    }

    /**
     * 设置数据源
     *
     * @param targetDataSources 备选数据源集合
     * @param sourceName 数据源名称
     * @param beanName bean名称
     */
    public void setDataSource(Map<Object, Object> targetDataSources, String sourceName, String beanName)
    {
        try
        {
            DataSource dataSource = SpringUtils.getBean(beanName);
            targetDataSources.put(sourceName, dataSource);
        }
        catch (Exception e)
        {
            log.error(e.getMessage());

        }
    }

    /** 解决druid 日志报错：discard long time none received connection:xxx */
    @PostConstruct
    public void setProperties() {
        System.setProperty("druid.mysql.usePingMethod", "false");
    }

    /**
     * 去除监控页面底部的广告
     */
    @SuppressWarnings({ "rawtypes", "unchecked" })
    @Bean
    @ConditionalOnProperty(name = "spring.datasource.druid.statViewServlet.enabled", havingValue = "true")
    public FilterRegistrationBean removeDruidFilterRegistrationBean(DruidStatProperties properties)
    {
        // 获取web监控页面的参数
        DruidStatProperties.StatViewServlet config = properties.getStatViewServlet();
        // 提取common.js的配置路径
        String pattern = config.getUrlPattern() != null ? config.getUrlPattern() : "/druid/*";
        String commonJsPattern = pattern.replaceAll("\\*", "js/common.js");
        final String filePath = "support/http/resources/js/common.js";
        // 创建filter进行过滤
        Filter filter = new Filter()
        {
            @Override
            public void init(javax.servlet.FilterConfig filterConfig) throws ServletException
            {
            }
            @Override
            public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain)
                    throws IOException, ServletException
            {
                chain.doFilter(request, response);
                // 重置缓冲区，响应头不会被重置
                response.resetBuffer();
                // 获取common.js
                String text = Utils.readFromResource(filePath);
                // 正则替换banner, 除去底部的广告信息
                text = text.replaceAll("<a.*?banner\"></a><br/>", "");
                text = text.replaceAll("powered.*?shrek.wang</a>", "");
                response.getWriter().write(text);
            }
            @Override
            public void destroy()
            {
            }
        };
        FilterRegistrationBean registrationBean = new FilterRegistrationBean();
        registrationBean.setFilter(filter);
        registrationBean.addUrlPatterns(commonJsPattern);
        return registrationBean;
    }
}

加上shardingjdbc自己的数据源配置：

package com.ruoyi.framework.config;


import org.apache.shardingsphere.api.config.sharding.KeyGeneratorConfiguration;
import org.apache.shardingsphere.api.config.sharding.ShardingRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.TableRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.strategy.StandardShardingStrategyConfiguration;
import org.apache.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;

/** @Classname ShardingDataSourceConfig @Description @Date 2023-5-23 15:38 @Author 无朽 */
@Configuration
public class ShardingDataSourceConfig {

    @Bean(name = "shardingDataSource")
    public DataSource shardingDataSource(
            @Qualifier("masterDataSource") DataSource masterDataSource
          )
            throws SQLException {
        Map<String, DataSource> dataSourceMap = new HashMap<>(1);
        dataSourceMap.put("masterDataSource", masterDataSource);

        // 配置分片规则
  /*      ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
        shardingRuleConfig.getTableRuleConfigs().add(orderTableRuleConfiguration);
        shardingRuleConfig.getTableRuleConfigs().add(fileTableRuleConfiguration);*/
        // 获取数据源对象
        DataSource dataSource =
                ShardingDataSourceFactory.createDataSource(
                        dataSourceMap, null, getProperties());
        return dataSource;
    }


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