在shardingsphere执行存储过程

环境：

springboot：2.5.2

数据库：Kingbase金仓V8R6

依赖：

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

        <dependency>
            <groupId>com.kingbase8</groupId>
            <artifactId>kingbase8</artifactId>
            <version>8.6.0</version>
        </dependency>

思路一：获取Connection执行存储过程

折腾了多种方式（EntityManager、DataSource、JdbcTemplate等），发现在shardingsphere环境下，始终不支持存储过程。

原因未知，没有深究出来。

可能是shardingsphere本身就不支持存储过程分片；

也就可能跟shardingsphere版本有问题；

还有可能是跟jdbc驱动包有关系(由于shardingsphere不适配金仓，jdbc驱动依然用的是org.postgresql.Driver)；

... ...

shardingjdbc数据源配置

spring:
    shardingsphere:
        datasource:
            app100:
                driver-class-name: org.postgresql.Driver
                jdbc-url: jdbc:postgresql://IP:端口/数据库?serverTimezone=Asia/Shanghai&useSSL=false
                password: 
                type: com.zaxxer.hikari.HikariDataSource
                username: 
                connection-timeout: 30000
                minimum-idle: 10
                maximum-pool-size: 1000
                idle-timeout: 30000
                pool-name: hikari-100
                max-lifetime: 60000
                connection-test-query: SELECT 1
                leak-detection-threshold: 50000
            app101:
                driver-class-name: org.postgresql.Driver
                jdbc-url: jdbc:postgresql://IP:端口/数据库?serverTimezone=Asia/Shanghai&useSSL=false
                password: 
                type: com.zaxxer.hikari.HikariDataSource
                username: 
                connection-timeout: 30000
                minimum-idle: 10
                maximum-pool-size: 1000
                idle-timeout: 30000
                pool-name: hikari-101
                max-lifetime: 60000
                connection-test-query: SELECT 1
                leak-detection-threshold: 50000
            app102:
                driver-class-name: org.postgresql.Driver
                jdbc-url: jdbc:postgresql://IP:端口/数据库?serverTimezone=Asia/Shanghai&useSSL=false
                password: 
                type: com.zaxxer.hikari.HikariDataSource
                username: 
                connection-timeout: 30000
                minimum-idle: 10
                maximum-pool-size: 1000
                idle-timeout: 30000
                pool-name: hikari-102
                max-lifetime: 60000
                connection-test-query: SELECT 1
                leak-detection-threshold: 50000

换种思路：动态数据源

配置

spring:
    datasource:
        multiPrimary:
            pool-name: 100
            type: com.zaxxer.hikari.HikariDataSource
            connection-timeout: 30000
            minimum-idle: 50
            maximum-pool-size: 1000
            idle-timeout: 30000
            max-lifetime: 60000
            connection-test-query: SELECT 1
            username: 
            password: 
            jdbc-url: jdbc:kingbase8://IP:端口/数据库
            driver-class-name: com.kingbase8.Driver
        multiSecondarys:
            - secondary-101:
              pool-name: 101
              type: com.zaxxer.hikari.HikariDataSource
              connection-timeout: 30000
              minimum-idle: 50
              maximum-pool-size: 1000
              idle-timeout: 30000
              max-lifetime: 60000
              connection-test-query: SELECT 1
              username: 
              password: 
              jdbc-url: jdbc:kingbase8://IP:端口/数据库
              driver-class-name: com.kingbase8.Driver
            - secondary-102:
              pool-name: 102
              type: com.zaxxer.hikari.HikariDataSource
              connection-timeout: 30000
              minimum-idle: 50
              maximum-pool-size: 1000
              idle-timeout: 30000
              max-lifetime: 60000
              connection-test-query: SELECT 1
              username: 
              password: 
              jdbc-url: jdbc:kingbase8://IP:端口/数据库
              driver-class-name: com.kingbase8.Driver

相关配置类源码

public class MultDataSourceUtil {
    private static final ThreadLocal<String> DATASOURCE_KEY = new ThreadLocal<>();

    public static void setDataSourceRoutingKey(String key) {
        DATASOURCE_KEY.set(key);
    }

    public static String getDataSourceRoutingKey() {
        return DATASOURCE_KEY.get();
    }

    public static void clearDataSourceRoutingKey() {
        DATASOURCE_KEY.remove();
    }
}

import com.zaxxer.hikari.HikariDataSource;
import lombok.extern.slf4j.Slf4j;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;

import java.util.HashMap;
import java.util.Map;

@Slf4j
@ConfigurationProperties("spring.datasource")
@Configuration
public class DataSourceConfig {
    private HikariDataSource multiPrimary;
    private Map<String, HikariDataSource> multiSecondarys = new HashMap<>();

    @Bean
    @Primary
    public DynamicDataSource falsDynamicDataSource() {
        Map<Object, Object> targetDataSources = new HashMap<>();
        multiSecondarys.forEach((key, secondary) -> {
            targetDataSources.put(secondary.getPoolName(), secondary);
        });
        targetDataSources.put(multiPrimary.getPoolName(), multiPrimary);
        DynamicDataSource dynamicDataSource = new DynamicDataSource(multiPrimary, targetDataSources);
        dynamicDataSource.afterPropertiesSet();
        return dynamicDataSource;
    }


    public HikariDataSource getMultiPrimary() {
        return multiPrimary;
    }


    public void setMultiPrimary(HikariDataSource multiPrimary) {
        this.multiPrimary = multiPrimary;
    }

    public Map<String, HikariDataSource> getMultiSecondarys() {
        return multiSecondarys;
    }

    public void setMultiSecondarys(Map<String, HikariDataSource> multiSecondarys) {
        this.multiSecondarys = multiSecondarys;
    }
}

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

import javax.sql.DataSource;
import java.util.Map;


public class DynamicDataSource extends AbstractRoutingDataSource {
    public DynamicDataSource() {
        super();
    }

    public DynamicDataSource(DataSource defaultTargetDataSource, Map<Object, Object> targetDataSources) {
        super.setDefaultTargetDataSource(defaultTargetDataSource);
        super.setTargetDataSources(targetDataSources);
        super.afterPropertiesSet();
    }


    @Override
    protected Object determineCurrentLookupKey() {
        return MultDataSourceUtil.getDataSourceRoutingKey();
    }
}

业务类代码

import com.alibaba.fastjson.JSONObject;
import com.zaxxer.hikari.HikariDataSource;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import javax.sql.DataSource;
import java.sql.*;
import java.util.Date;
import java.time.Instant;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.util.*;


@Slf4j
@Service
public class 业务ServiceImpl implements 业务Service {
    @Resource
    private DataSource dataSource;

    @PersistenceContext
    private EntityManager em;

    @Override
    public Map<String, String> zjjhpld(String paramStr) {

        Map<String, String> result = new HashMap<>();
        String msg = "";

        try {
            String orgId = "00320000000000000000";


            // region
            Query query = em.createNativeQuery(" SELECT 字段 FROM 表 where 字段=:orgId AND tenantId=:tenantId ");
            query.setParameter("orgId", orgId);
            query.setParameter("tenantId", tenantId);
            String treeinfoPath = (String) query.getSingleResult();
            System.out.println(treeinfoPath);
            // endregion



            Map<Object, DataSource> dataSourceMap = ((DynamicDataSource) dataSource).getResolvedDataSources();

            for (Map.Entry<Object, DataSource> entry : dataSourceMap.entrySet()) {
                String k = (String) entry.getKey();
                if ("100".equals(k)) {
                    continue;
                }
                DataSource v = entry.getValue();
                HikariDataSource hikariDataSource = (HikariDataSource) v;
                Connection conn = hikariDataSource.getConnection();

                CallableStatement callableStatement = conn.prepareCall("{ call 存储过程(?, ?) }");
                callableStatement.setString(1, orgId);
                callableStatement.registerOutParameter(2, Types.REF_CURSOR);

                callableStatement.execute();

                ResultSet resultSet = (ResultSet) callableStatement.getObject(2);

                while (resultSet.next()) {
                    System.out.println();
                }

                conn.close();
            }

        } catch (Exception e) {
            log.error("出现异常：", e);
        }
        return result;
    }
}

注意：

存储过程可以正常执行了，但是有个问题，第一个查询sql不走shardingjdbc的分库了。

发现获取的数据源已经变成了动态数据源，而不是shardingjdbc的数据源，这样不符合我们的需求。

再换个思路，去掉动态，只留下组装好的数据源

配置类代码：

import com.zaxxer.hikari.HikariDataSource;
import lombok.extern.slf4j.Slf4j;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Configuration;
import java.util.HashMap;
import java.util.Map;

@Slf4j
@ConfigurationProperties("spring.datasource")
@Configuration
public class DataSourceConfig {
    private HikariDataSource multiPrimary;
    private Map<String, HikariDataSource> multiSecondarys = new HashMap<>();

    public HikariDataSource getMultiPrimary() {
        return multiPrimary;
    }


    public void setMultiPrimary(HikariDataSource multiPrimary) {
        this.multiPrimary = multiPrimary;
    }

    public Map<String, HikariDataSource> getMultiSecondarys() {
        return multiSecondarys;
    }

    public void setMultiSecondarys(Map<String, HikariDataSource> multiSecondarys) {
        this.multiSecondarys = multiSecondarys;
    }
}

业务代码：

import com.alibaba.fastjson.JSONObject;
import com.zaxxer.hikari.HikariDataSource;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.support.DefaultListableBeanFactory;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import javax.sql.DataSource;
import java.sql.*;
import java.util.Date;
import java.time.Instant;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.util.*;


@Slf4j
@Service
public class 业务ServiceImpl implements 业务Service {
    @Resource
    private DefaultListableBeanFactory beanFactory;
    @Resource
    private DataSourceConfig dataSourceConfig;

    @Resource
    private DataSource dataSource;

    @PersistenceContext
    private EntityManager em;

    @Override
    public Map<String, String> 业务(String paramStr) {

        Map<String, String> result = new HashMap<>();
        String msg = "";

        try {
            String orgId = "";
            String jhqj = "";


            HikariDataSource multiPrimary = dataSourceConfig.getMultiPrimary();
            Map<String, HikariDataSource> multiSecondarys = dataSourceConfig.getMultiSecondarys();


            /*HikariDataSource dataSource = new HikariDataSource();
            dataSource.setDriverClassName("com.mysql.jdbc.Driver");
            dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/db3?characterEncoding=utf8");
            dataSource.setUsername("root");
            dataSource.setPassword("123456");
            dataSource.getConnection();
            System.out.println("db3 创建完成！");
            beanFactory.registerSingleton("db3", dataSource);*/





            // region
            Query query = em.createNativeQuery(" SELECT 字段 FROM 表 where 条件=:orgId AND tenantId=:tenantId ");
            query.setParameter("orgId", orgId);
            query.setParameter("tenantId", tenantId);
            String val = (String) query.getSingleResult();
            // endregion


            for (Map.Entry<String, HikariDataSource> entry : multiSecondarys.entrySet()) {
                String k = entry.getKey();
                if ("100".equals(k)) {
                    continue;
                }
                DataSource v = entry.getValue();
                HikariDataSource hikariDataSource = (HikariDataSource) v;
                Connection conn = hikariDataSource.getConnection();

                CallableStatement callableStatement = conn.prepareCall("{ call 存储过程(?, ?) }");
                callableStatement.setString(1, orgId);
                callableStatement.registerOutParameter(2, Types.REF_CURSOR);

                callableStatement.execute();

                ResultSet resultSet = (ResultSet) callableStatement.getObject(2);

                while (resultSet.next()) {
                    System.out.println();
                }

                conn.close();
            }
        } catch (Exception e) {
            log.error("出现异常：", e);
        }

        return result;
    }
}

结果：

第一个查询依然走shardingjdbc，而且存储过程也可以正常执行，满足需求。

其他分享

动态数据源：集成JPA + MP，具体参考本人的另一篇博客《动态数据源》尚待整理

JPA配置类

import org.springframework.boot.autoconfigure.domain.EntityScan;
import org.springframework.boot.autoconfigure.orm.jpa.JpaProperties;
import org.springframework.boot.context.properties.EnableConfigurationProperties;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.SharedEntityManagerCreator;
import org.springframework.transaction.PlatformTransactionManager;

import javax.annotation.Resource;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import java.util.Map;


@Configuration
@EnableConfigurationProperties(JpaProperties.class)
@EntityScan("com.**.entity")
@EnableJpaRepositories("com.**.repository")
public class JpaExtConfiguration {
    @Resource
    private JpaProperties jpaProperties;

    @Resource
    private DynamicDataSource dynamicDataSource;

    @Bean
    public LocalContainerEntityManagerFactoryBean entityManagerFactoryBean(EntityManagerFactoryBuilder builder) {
        Map<String, String> properties = jpaProperties.getProperties();
        properties.put("hibernate.physical_naming_strategy", "org.springframework.boot.orm.jpa.hibernate.SpringPhysicalNamingStrategy");

        return builder.dataSource(dynamicDataSource).properties(properties).packages("com.**.entity").build();
    }

    @Primary
    @Bean
    public EntityManagerFactory entityManagerFactory(LocalContainerEntityManagerFactoryBean entityManagerFactoryBean) {
        return entityManagerFactoryBean.getObject();
    }

    @Primary
    @Bean
    public PlatformTransactionManager transactionManager(EntityManagerFactory entityManagerFactory) {
        return new JpaTransactionManager(entityManagerFactory);
    }

    @Primary
    @Bean
    public EntityManager entityManager(EntityManagerFactory entityManagerFactory) {
        return SharedEntityManagerCreator.createSharedEntityManager(entityManagerFactory);
    }
}

MP配置类

import com.baomidou.mybatisplus.core.MybatisConfiguration;
import com.baomidou.mybatisplus.core.config.GlobalConfig;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;


@MapperScan(sqlSessionFactoryRef = "dynamicDataSourceSqlSessionFactory")
@Configuration
@Slf4j
public class MpExtConfiguration {
    @Bean
    @ConfigurationProperties(prefix = "mybatis-plus.global-config")
    public GlobalConfig globalConfig() {
        return new GlobalConfig();
    }

    @Bean
    @ConfigurationProperties(prefix = "mybatis-plus.configuration")
    public MybatisConfiguration mybatisConfiguration() {
        return new MybatisConfiguration();
    }

    @Bean
    public SqlSessionFactory dynamicDataSourceSqlSessionFactory(@Qualifier("dynamicDataSource") DataSource dynamicDataSource, GlobalConfig globalConfig, MybatisConfiguration mybatisConfiguration) throws Exception {
        MybatisSqlSessionFactoryBean sqlSessionFactoryBean = new MybatisSqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dynamicDataSource);
        sqlSessionFactoryBean.setGlobalConfig(globalConfig);
        sqlSessionFactoryBean.setConfiguration(mybatisConfiguration);
        return (SqlSessionFactory) sqlSessionFactoryBean.getObject();
    }
}

MP配置

mybatis-plus:
  global-config:
    enable-sql-runner: true
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

在shardingsphere执行存储过程

环境：

思路一：获取Connection执行存储过程

换种思路：动态数据源

再换个思路，去掉动态，只留下组装好的数据源

相关文章

基于PoE交换机的智慧停车场监控组网应用

直接抄作业！Air780E模组LuatOS开发：位运算(bit)示例

多点DMALL启动招股：将在港交所上市，聚焦数字零售服务

WRF-Chem模式安装、环境配置、原理、调试、运行方法；数据准备及相关参数设置方法

离线安装 Docker-IO：详细步骤指南

拥抱 OpenTelemetry：阿里云 Java Agent 演进实践

【信息系统项目管理师】第3章：信息系统治理考点梳理

DRM(数字权限管理技术)防截屏录屏----ffmpeg安装

github webhooks 实现网站自动更新

全桥LLC变换器原理及MATLAB仿真模型

使用R语言进行美国失业率时空分析（包括绘图）

PostgreSQL在Linux环境下的常用命令总结

React Native学习笔记（三）

TsingtaoAI具身智能高校实训方案通过华为昇腾技术认证

基于matlab程序实现人脸识别

SQL Server管理员sa登录失败原因

15分钟做完一个小程序，腾讯这个工具有点东西

探秘多源异构数据：开启数据融合新时代

R语言结构方程模型（SEM）在生态学领域中的应用

springboot中使用mongodb完成评论功能