在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.Driverjdbc-url: jdbc:postgresql://IP:端口/数据库?serverTimezone=Asia/Shanghai&useSSL=falsepassword: type: com.zaxxer.hikari.HikariDataSourceusername: connection-timeout: 30000minimum-idle: 10maximum-pool-size: 1000idle-timeout: 30000pool-name: hikari-100max-lifetime: 60000connection-test-query: SELECT 1leak-detection-threshold: 50000app101:driver-class-name: org.postgresql.Driverjdbc-url: jdbc:postgresql://IP:端口/数据库?serverTimezone=Asia/Shanghai&useSSL=falsepassword: type: com.zaxxer.hikari.HikariDataSourceusername: connection-timeout: 30000minimum-idle: 10maximum-pool-size: 1000idle-timeout: 30000pool-name: hikari-101max-lifetime: 60000connection-test-query: SELECT 1leak-detection-threshold: 50000app102:driver-class-name: org.postgresql.Driverjdbc-url: jdbc:postgresql://IP:端口/数据库?serverTimezone=Asia/Shanghai&useSSL=falsepassword: type: com.zaxxer.hikari.HikariDataSourceusername: connection-timeout: 30000minimum-idle: 10maximum-pool-size: 1000idle-timeout: 30000pool-name: hikari-102max-lifetime: 60000connection-test-query: SELECT 1leak-detection-threshold: 50000

---

换种思路：动态数据源

配置

spring:datasource:multiPrimary:pool-name: 100type: com.zaxxer.hikari.HikariDataSourceconnection-timeout: 30000minimum-idle: 50maximum-pool-size: 1000idle-timeout: 30000max-lifetime: 60000connection-test-query: SELECT 1username: password: jdbc-url: jdbc:kingbase8://IP:端口/数据库driver-class-name: com.kingbase8.DrivermultiSecondarys:- secondary-101:pool-name: 101type: com.zaxxer.hikari.HikariDataSourceconnection-timeout: 30000minimum-idle: 50maximum-pool-size: 1000idle-timeout: 30000max-lifetime: 60000connection-test-query: SELECT 1username: password: jdbc-url: jdbc:kingbase8://IP:端口/数据库driver-class-name: com.kingbase8.Driver- secondary-102:pool-name: 102type: com.zaxxer.hikari.HikariDataSourceconnection-timeout: 30000minimum-idle: 50maximum-pool-size: 1000idle-timeout: 30000max-lifetime: 60000connection-test-query: SELECT 1username: 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@Primarypublic 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();}@Overrideprotected 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 {@Resourceprivate DataSource dataSource;@PersistenceContextprivate EntityManager em;@Overridepublic Map<String, String> zjjhpld(String paramStr) {Map<String, String> result = new HashMap<>();String msg = "";try {String orgId = "00320000000000000000";// regionQuery 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);// endregionMap<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 {@Resourceprivate DefaultListableBeanFactory beanFactory;@Resourceprivate DataSourceConfig dataSourceConfig;@Resourceprivate DataSource dataSource;@PersistenceContextprivate EntityManager em;@Overridepublic 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);*/// regionQuery query = em.createNativeQuery(" SELECT 字段 FROM 表 where 条件=:orgId AND tenantId=:tenantId ");query.setParameter("orgId", orgId);query.setParameter("tenantId", tenantId);String val = (String) query.getSingleResult();// endregionfor (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 {@Resourceprivate JpaProperties jpaProperties;@Resourceprivate DynamicDataSource dynamicDataSource;@Beanpublic 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@Beanpublic EntityManagerFactory entityManagerFactory(LocalContainerEntityManagerFactoryBean entityManagerFactoryBean) {return entityManagerFactoryBean.getObject();}@Primary@Beanpublic PlatformTransactionManager transactionManager(EntityManagerFactory entityManagerFactory) {return new JpaTransactionManager(entityManagerFactory);}@Primary@Beanpublic 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();}@Beanpublic 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: trueconfiguration:log-impl: org.apache.ibatis.logging.stdout.StdOutImpl