动态数据源实现分表走shardingsphere,不分表走其他

warrah

已于 2024-04-17 21:56:22 修改

阅读量754

点赞数 16

分类专栏：岁月云——Web系统最佳实践文章标签：动态数据源分库

于 2024-04-15 16:50:20 首次发布

本文链接：https://blog.csdn.net/warrah/article/details/137782004

版权

岁月云——Web系统最佳实践专栏收录该内容

117 篇文章 0 订阅

订阅专栏

shardingsphere从4.1.1升级到5.2.1但是还没有完结，因为在执行存储过程的时候，系统提示错误如下。shardingsphere是不支持存储过程呢，但项目中不能避免使用存储过程，因为有大量的数据需要初始化，这种情况该如何应对？

### SQL: {call init_data(                 ?,                 ?,                 ?                 )                 }
Caused by: java.sql.SQLFeatureNotSupportedException: prepareCall
	at org.apache.shardingsphere.driver.jdbc.unsupported.AbstractUnsupportedOperationConnection.prepareCall(AbstractUnsupportedOperationConnection.java:43)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.apache.ibatis.logging.jdbc.ConnectionLogger.invoke(ConnectionLogger.java:55)
	at com.sun.proxy.$Proxy170.prepareCall(Unknown Source)
	at org.apache.ibatis.executor.statement.CallableStatementHandler.instantiateStatement(CallableStatementHandler.java:87)
	at org.apache.ibatis.executor.statement.BaseStatementHandler.prepare(BaseStatementHandler.java:88)
	at org.apache.ibatis.executor.statement.RoutingStatementHandler.prepare(RoutingStatementHandler.java:59)
	at sun.reflect.GeneratedMethodAccessor358.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.apache.ibatis.plugin.Invocation.proceed(Invocation.java:49)

既然存储过程并不涉及分片的表，为何shardingsphere还会干预呢？
使用Hikari可以看到下面的日志

JDBC Connection [HikariProxyConnection@289895964 wrapping com.mysql.cj.jdbc.ConnectionImpl@164ef602] will not be managed by Spring

如果被shardingsphere管理则可以看到下面的日志

JDBC Connection [org.apache.shardingsphere.driver.jdbc.core.connection.ShardingSphereConnection@6e64e072] will not be managed by Spring

因为shardingsphere不支持存储过程及一些语句，所以产生了动态数据源的需求，在网上找的例子配置,Mybatis-plus@DS实现动态切换数据源应用


import com.baomidou.dynamic.datasource.DynamicRoutingDataSource;
import com.baomidou.dynamic.datasource.provider.AbstractDataSourceProvider;
import com.baomidou.dynamic.datasource.provider.DynamicDataSourceProvider;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DataSourceProperty;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DynamicDataSourceAutoConfiguration;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DynamicDataSourceProperties;
import org.apache.shardingsphere.driver.jdbc.adapter.AbstractDataSourceAdapter;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.SpringBootConfiguration;
import org.springframework.boot.autoconfigure.AutoConfigureBefore;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;

import org.springframework.context.annotation.Lazy;
import javax.annotation.Resource;
import javax.sql.DataSource;
import java.util.Map;

@Configuration
@AutoConfigureBefore({DynamicDataSourceAutoConfiguration.class,
        SpringBootConfiguration.class})
public class DataSourceConfig {

    /**
     * 分表数据源名称
     */
    private static final String SHARDING_DATA_SOURCE_NAME = "acc_sharding";

    @Autowired
    private DynamicDataSourceProperties properties;

    @Lazy
    @Resource(name = "shardingDataSource")
    AbstractDataSourceAdapter shardingDataSource;


    @Bean
    public DynamicDataSourceProvider dynamicDataSourceProvider() {
        Map<String, DataSourceProperty> datasourceMap = properties.getDatasource();
        return new AbstractDataSourceProvider() {
            @Override
            public Map<String, DataSource> loadDataSources() {
                Map<String, DataSource> dataSourceMap = createDataSourceMap(datasourceMap);
                // 将 shardingjdbc 管理的数据源也交给动态数据源管理
                dataSourceMap.put(SHARDING_DATA_SOURCE_NAME, shardingDataSource);
                return dataSourceMap;
            }
        };
    }

    @Primary
    @Bean
    public DataSource dataSource(DynamicDataSourceProvider dynamicDataSourceProvider) {
        DynamicRoutingDataSource dataSource = new DynamicRoutingDataSource();
        dataSource.setPrimary(properties.getPrimary());
        dataSource.setStrict(properties.getStrict());
        dataSource.setStrategy(properties.getStrategy());
        dataSource.setProvider(dynamicDataSourceProvider);
        dataSource.setP6spy(properties.getP6spy());
        dataSource.setSeata(properties.getSeata());
        return dataSource;
    }


}

上面的java配置，还有一个问题没有解决，那就是shardingDataSource这个数据源是怎么初始化的。因为shardingsphere5.2.1没有默认数据源，因此只好先降到5.1.1，Shardingsphere5.1.1 整合druid动态数据源，
在5.1.1中需要去掉下面的配置,因为不支持

shardingsphere5.x整合springboot+dynamic-datasource多数据源实战
切到5.1.1上面的动态数据源代码是有问题的,因为5.1.1版本自动装载的shardingSphereDataSource beanName=“shardingSphereDataSource”
动态数据源是好的，但带来的问题却是数据库初始化连接过多，如下面的配置，每个系统就有两份数据库连接池配置，这个需要注意，在k8s扩展服务的时候应该考虑到这一点

 @Lazy
 @Autowired
 private DataSource  shardingDataSource;

如果按照这个结论，那么将版本再切到5.2.1会怎么样呢？猜想有些地方应该还是会继承把。这个猜想是对的。
配置如下

spring:
  datasource:
    dynamic:
      hikari: # 全局hikariCP参数
        pool-name: Retail_HikariCP #连接池名称
        minimum-idle: 5 #最小空闲连接数量
        idle-timeout: 120000 #空闲连接存活最大时间，默认600000（10分钟）
        maximum-pool-size: 10 #连接池最大连接数，默认是10
        auto-commit: true  #此属性控制从池返回的连接的默认自动提交行为,默认值：true
        max-lifetime: 1800000 #此属性控制池中连接的最长生命周期，值0表示无限生命周期，默认1800000即30分钟
        connection-timeout: 30000 #数据库连接超时时间,默认30秒，即30000
        connection-test-query: SELECT 1
      datasource:
        master:
          type: com.zaxxer.hikari.HikariDataSource
          driver-class-name: com.mysql.cj.jdbc.Driver
          username: root
          password: ENC(xxx)
          url: jdbc:mysql://127.0.0.1:3306/acc?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai&allowMultiQueries=true
  #  数据库配置
  shardingsphere: 
    mode:
      type: Standalone
      repository:
        type: JDBC
    datasource:
      names: acc1
      acc1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        username: root
        password: ENC(xxx)
        jdbcUrl: jdbc:mysql://127.0.0.1:3306/acc?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai&allowMultiQueries=true
        pool-name: Retail_HikariCP #连接池名称
        minimum-idle: 5 #最小空闲连接数量
        #idle-timeout: 600000 #空闲连接存活最大时间，默认600000（10分钟）
        maximum-pool-size: 10 #连接池最大连接数，默认是10
        auto-commit: true  #此属性控制从池返回的连接的默认自动提交行为,默认值：true
        #max-lifetime: 600000 #此属性控制池中连接的最长生命周期，值0表示无限生命周期，默认1800000即30分钟
        connection-timeout: 300000 #数据库连接超时时间,默认30秒，即30000
        connection-test-query: SELECT 1
    props:
      # 这个跟4.1.1配置有区别
      sql-show: true
    sharding:
      default-data-source-name: acc1
    rules:
      sharding:
        tables:
          # 科目
          acc_account_subject:
            actual-data-nodes: acc1.acc_account_subject_$->{0..49}
            table-strategy:
              standard:
                sharding-column: as_id
                sharding-algorithm-name: acc_account_subject-inline
          # 科目余额
          acc_account_balance:
            actual-data-nodes: acc1.acc_account_balance_$->{0..49}
            table-strategy:
              standard:
                sharding-column: as_id
                sharding-algorithm-name: acc_account_balance-inline
          # 期初
          acc_initial_balance:
            actual-data-nodes: acc1.acc_initial_balance_$->{0..49}
            table-strategy:
              standard:
                sharding-column: as_id
                sharding-algorithm-name: acc_initial_balance-inline
          # 凭证
          acc_voucher:
            actual-data-nodes: acc1.acc_voucher_$->{0..49}
            table-strategy:
              standard:
                sharding-column: as_id
                sharding-algorithm-name: acc_voucher-inline
          # 凭证分录
          acc_voucher_entry:
            actual-data-nodes: acc1.acc_voucher_entry_$->{0..49}
            table-strategy:
              standard:
                sharding-column: as_id
                sharding-algorithm-name: acc_voucher_entry-inline
        bindingTables:
          - acc_account_subject,acc_account_balance,acc_initial_balance,acc_voucher,acc_voucher_entry
        sharding-algorithms:
          acc_account_subject-inline:
            type: inline
            props:
              algorithm-expression: acc_account_subject_$->{as_id%50}
          acc_account_balance-inline:
            type: inline
            props:
              algorithm-expression: acc_account_balance_$->{as_id%50}
          acc_initial_balance-inline:
            type: inline
            props:
              algorithm-expression: acc_initial_balance_$->{as_id%50}
          acc_voucher-inline:
            type: inline
            props:
              algorithm-expression: acc_voucher_$->{as_id%50}
          acc_voucher_entry-inline:
            type: inline
            props:
              algorithm-expression: acc_voucher_entry_$->{as_id%50}

在java侧，针对分表的还需要加上注解@DS，一般用在mapper、service上

否则会提示

### Cause: java.sql.SQLSyntaxErrorException: Table 'acc.acc_account_subject' doesn't exist
; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: Table 'acc.acc_account_subject' doesn't exist
	at java.util.concurrent.CompletableFuture.encodeThrowable(CompletableFuture.java:273)
	at java.util.concurrent.CompletableFuture.completeThrowable(CompletableFuture.java:280)
	at java.util.concurrent.CompletableFuture$AsyncSupply.run(CompletableFuture.java:1592)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
	at java.lang.Thread.run(Thread.java:748)
Caused by: org.springframework.jdbc.BadSqlGrammarException: 
### Error querying database.  Cause: java.sql.SQLSyntaxErrorException: Table 'acc.acc_account_subject' doesn't exist
### The error may exist in com/whty/acc/setting/dao/AccStatementAsMapper.java (best guess)

分表的数据直接在service上加上注解

关联方，在具体的方法上加，这些第一感觉有些侵入性，但是也还好，提醒其他工程师，这个代码要注意分表，这样好像也没有错。
再怎么原生的mybatis，比shardingsphere再转义一道，还是要快些。

一般情况是没有啥问题，但是二班就一定了，看表面的sql中，为什么sql语句查询了50条呢，是因为acc_account_subject 做了50张分表，因此查询出来了50条，实际是有问题的

==>  Preparing: SELECT COUNT(*) AS total FROM acc_fund_account t LEFT JOIN acc_account_subject t1 ON (t1.id = t.sub_id AND t1.as_id = t.as_id AND t1.status = ?) LEFT JOIN acc_foreign_currency t2 ON (t2.id = t.fc_id AND t2.as_id = t.as_id AND t2.status = ?) WHERE (t.as_id = ? AND t.status = ? AND t.category = ?)
==> Parameters: 01(String), 01(String), 27024(Integer), 01(String), 01(String)
<==    Columns: total
<==        Row: 50
<==      Total: 1

再看原来生产库执行出的脚本,也就是说原来4.1.1版本的分表是正确的，那为何5.2.1就不行了呢，难道是退步了？

打印出sql可以看到,他干了件蠢事，遍历了所有表，难道是因为分表是left join导致的？

调整为inner join，可以看到他找他具体的分表了,这样问题就可以定位到left join 的问题,那么为什么会出现这个问题呢？

不可能版本越高越倒退，但可能是因为版本越高灵活性可能就没有以前那么好，因为要兼顾的东西太多，故而设置了不少规范，因此有些代码应该可以规范的方式去调整,既然acc_account_subject是分片的，那么它的关联查询就应该带上分表字段,因此下方对应位置，关联表时应传入对应的参数。而不能像之前一样了

接下来意味着所有与分片有关的leftJoin，您都需要检查一遍，确保没有问题.
接着实现另外一个问题，官方不支持下面的场景

但实际业务中需要批量复制数据，依然可以手工来实现分表

    public static  String  getActualTable(Integer asId,String logicTable){
        Integer suffix = asId % TABLE_SHARDING_COUNT;
        return logicTable + "_"+ suffix;

    }

接着使用mybatis来实现

 public boolean copyByAsId(Integer newAsId, Integer copyAsId) {
        String newTableName = ShardingUtils.getActualTable(newAsId, "acc_account_subject");
        String copyTableName = ShardingUtils.getActualTable(copyAsId, "acc_account_subject");
        return extAccAccountSubjectMapper.copyByAsId(newAsId, copyAsId, newTableName, copyTableName);
    }

还有一种情况没有考虑，因为shardingsphere不支持存储过程，如果同一个service中出现，一部分代码需要执行存储过程，另外一部分代码需要执行分表，遇到这种情况如果外层增加注解就会报错，之前的工程师通过代码来手工切换数据源，那么使用@DS是否能完成呢？

Caused by: java.sql.SQLFeatureNotSupportedException: prepareCall
	at org.apache.shardingsphere.driver.jdbc.unsupported.AbstractUnsupportedOperationConnection.prepareCall(AbstractUnsupportedOperationConnection.java:43)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.apache.ibatis.logging.jdbc.ConnectionLogger.invoke(ConnectionLogger.java:55)
	at com.sun.proxy.$Proxy380.prepareCall(Unknown Source)
	at org.apache.ibatis.executor.statement.CallableStatementHandler.instantiateStatement(CallableStatementHandler.java:87)
	at org.apache.ibatis.executor.statement.BaseStatementHandler.prepare(BaseStatementHandler.java:88)
	at org.apache.ibatis.executor.statement.RoutingStatementHandler.prepare(RoutingStatementHandler.java:59)
	at sun.reflect.GeneratedMethodAccessor300.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.apache.ibatis.plugin.Invocation.proceed(Invocation.java:49)

如下种情况该如何应对？

SpringBoot+MyBatis-Plus多数据源@DS注解失效的解决方法，如果他说的是对的,那为何我这么操作却没有生效呢？

有方案说使用在最外层添加@DSTransactional，然后再不同数据源添加@Transactional(propagation = Propagation.REQUIRES_NEW, rollbackFor = Exception.class),实际应用会有问题，会提示NullPointerException错误

org.springframework.transaction.CannotCreateTransactionException: Could not open JDBC Connection for transaction; nested exception is java.lang.NullPointerException
	at org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin(DataSourceTransactionManager.java:309)
	at org.springframework.transaction.support.AbstractPlatformTransactionManager.startTransaction(AbstractPlatformTransactionManager.java:400)
	at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:373)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:595)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:382)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)

解决方案是加所用到的service上增加@DS("master"),否则就会出错，我的天哪，这样的话，那改造的工作量还真是大，那为何自身不能默认的数据源呢？
不过调研并分析问题，也是一件有趣的事情，例如mybatis-plus实战—多数据源@DS和@Transactional冲突 ,这个人在controller上添加@Transactional，虽然并没有不能这么写的限制，但是却是少见，这种在我的团队估计我会训。
通过上面的结论@DSTransactional这条路是行不通的。
有一种方式肯定是是能解决的

   @Override
    public void excelImportNew(MultipartFile accountBalanceFile, MultipartFile ledgerFile, MultipartFile voucherFile, ImportAccountBalanceReq req) throws IOException {
        //校验表
        checkTable(accountBalanceFile, ledgerFile, voucherFile, req.getSoftwareId());
//        //校验请求参数
        this.checkParam(req);
        Integer asId = getAsId(req);
        try {
            importData(asId, accountBalanceFile, ledgerFile, voucherFile, req);
        } catch (Exception e) {
            removeAccountSet(asId);
            throw new RuntimeException(e);
        }

    }

这种方式实际使用了两个事务。
在这里插入图片描述

warrah

关注

16
点赞
踩
17

收藏

觉得还不错? 一键收藏
打赏
1
评论
动态数据源实现分表走shardingsphere,不分表走其他

但是还没有完结，因为在执行存储过程的时候，系统提示错误如下。shardingsphere是不支持存储过程呢，但项目中不能避免使用存储过程，因为有大量的数据需要初始化，这种情况该如何应对？动态数据源是好的，但带来的问题却是数据库初始化连接过多，如下面的配置，每个系统就有两份数据库连接池配置，这个需要注意，在k8s扩展服务的时候应该考虑到这一点。因为shardingsphere不支持存储过程及一些语句，所以产生了动态数据源的需求，在网上找的例子配置,上面的java配置，还有一个问题没有解决，那就是。
复制链接

扫一扫