在shardingsphere执行存储过程

news/2025/2/12 18:39:22

环境:

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


https://dhexx.cn/news/show-5466339.html

相关文章

使用Python编写Windows系统服务管理脚本,主要用于管理mysql、postgresql等服务

提示&#xff1a; 1、理论上该Python脚本可以关闭和开启所有服务 2、搜索服务采用的是模糊搜索 3、常用服务查询: 暂时只写了 mysql、postgresql 如需要其他的自行添加即可 使用方式&#xff08;使用管理员权限运行&#xff09;&#xff1a; 运行方式一&#xff1a;使用管理员…

【Rust 学习笔记】Rust 基础数据类型介绍(一)

博主未授权任何人或组织机构转载博主任何原创文章&#xff0c;感谢各位对原创的支持&#xff01; 博主链接 博客内容主要围绕&#xff1a; 5G/6G协议讲解 高级C语言讲解 Rust语言讲解 文章目录 Rust 基础数据类型介绍&#xff08;一&#xff09;一、固定宽…

RabbitMQ高级特性:TTL、死信队列与延迟队列

RabbitMQ高级特性&#xff1a;TTL、死信队列与延迟队列 RabbitMQ作为一款开源的消息代理软件&#xff0c;广泛应用于分布式系统中&#xff0c;用于实现消息的异步传递和系统的解耦。其强大的高级特性&#xff0c;包括TTL&#xff08;Time-To-Live&#xff09;、死信队列&#…

初始化hive元数据库报错org.apache.hadoop.hive.metastore.HiveMetaException: Failed to get schema version.

最近&#xff0c;我在学习hive的时候&#xff0c;用MySQL作为hive的元数据库&#xff0c;配置好hive-site.xml文件后初始化hive的元数据库失败&#xff0c;显示错误为&#xff1a; org.apache.hadoop.hive.metastore.HiveMetaException: Failed to get schema version. Unde…

论文笔记(五十七)Diffusion Model Predictive Control

Diffusion Model Predictive Control 文章概括摘要1. Introduction2. Related work3. 方法3.1 模型预测控制3.2. 模型学习3.3. 规划&#xff08;Planning&#xff09;3.4. 适应 4. 实验&#xff08;Experiments&#xff09;4.1. 对于固定奖励&#xff0c;D-MPC 可与其他离线 RL…

Scrapy管道设置和数据保存

1.1 介绍部分&#xff1a; 文字提到常用的Web框架有Django和Flask&#xff0c;接下来将学习一个全球范围内流行的爬虫框架Scrapy。 1.2 内容部分&#xff1a; Scrapy的概念、作用和工作流程 Scrapy的入门使用 Scrapy构造并发送请求 Scrapy模拟登陆 Scrapy管道的使用 Scrapy中…

使用 Vite 创建 Vue3+TS 项目并整合 ElementPlus、Axios、Pinia、Less、Vue-router 等组件或插件

前言 记录一下使用 Vite 创建 Vue3TS 项目并整合 ElementPlus、Axios、Pinia、Less、Vue-router 等组件或插件。 一、使用 Vite 创建 Vue3TS 项目 1.新建一个 temp 文件夹 &#xff08;1&#xff09;在桌面新建一个 temp 文件夹&#xff0c;然后在 VS Code 中打开此文件夹&…

飞塔防火墙只允许国内IP访问

飞塔防火墙只允许国内IP访问 方法1 新增地址对象&#xff0c;注意里面已经细分为中国内地、中国香港、中国澳门和中国台湾 方法2 手动新增国内IP的对象组&#xff0c;目前好像一共有8632个&#xff0c;每个对象最多支持600个IP段

【C++】LeetCode:LCR 026. 重排链表

题干 LCR 026. 重排链表 给定一个单链表 L 的头节点 head &#xff0c;单链表 L 表示为&#xff1a; L0 → L1 → … → Ln-1 → Ln 请将其重新排列后变为&#xff1a; L0 → Ln → L1 → Ln-1 → L2 → Ln-2 → … 不能只是单纯的改变节点内部的值&#xff0c;而是需要实…

IntelliJ+SpringBoot项目实战(十七)--在SpringBoot中整合SpringSecurity和JWT(下B)

八、SpringSecurity实现权限控制 在上节中介绍了SpringSecurity登录时从数据库中验证用户以及获取用户的权限集合。本文介绍如何进行权限控制。 在上节中&#xff0c;虽然实现了从数据库中获取用户并验证密码&#xff0c;但是还没有实现权限的控制&#xff0c;只是将用…