目录 需求场景 需求逻辑： 难点： 说明： 代码 pom.xml依赖只贴sqlserver的 文件目录 yml配置文件 DataSource自定义注解 DataSourceAspect类文件 DruidConfig类 DruidProp
目录
DynamicDataSourceContextHolder
在学校或者自己练习的demo，基本都是配置一个数据源即可，基本都是使用Mysql，可是在工作中经常会出现很多不一样的场景和需求。
这里说一下我的需求：我需要从mysql数据库中通过一个字段去sqlserver数据库中读取数据，然后封装数据再录入到mysql中库中对应的表
尝试了很多方法，发现都不行，各种报错，在文章的后面会把我部署过程中的报错全部展现一下，方便大家排错，最后用的若依框架中的aop动态切换数据源的方式
后台手册 | RuoYi使用若依快速构建WEB应用程序
com.microsoft.sqlserver mssql-jdbc 9.2.1.jre15 
这里的数据源分为master 和 slave 如果你有三个四个五个数据源在这里都可以配置，只要在后面的config类配置中进行配置即可
#dev环境 mysql 7.0spring: application: name: data-spider datasource: type: com.alibaba.druid.pool.DruidDataSource # 配置多数据源 master: Url: jdbc:mysql://localhost:3306/库名?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&useSSL=false username: root passWord: root #driver-class-name: com.mysql.cj.jdbc.Driver slave: enabled: true Url: jdbc:sqlserver://localhost:1433;DatabaseName=test username: sa password: 123456 driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver druid: inital-size: 10 #最大连接数 max-active: 50 #最小连接数 min-idle: 10 #获取链接等待超时时间 max-wait: 5000 pool-prepared-statements: true #是否缓存preparedStatement，也就是PSCache。PSCache对支持游标的数据库性能提升巨大，比如说oracle。在mysql下建议关闭。 max-pool-prepared-statement-per-connection-size: 20 validation-query: SELECT 1 validation-query-timeout: 20000 test-on-borrow: false #申请连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能。 test-on-return: false #归还连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能。 test-while-idle: true #建议配置为true，不影响性能，并且保证安全性。申请连接的时候检测，如果空闲时间大于timeBetweenEvictionRunsMillis，执行validationQuery检测连接是否有效。 time-between-eviction-runs-millis: 60000 #配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒 min-evictable-idle-time-millis: 300000 #一个连接在池中最小生存的时间，单位是毫秒 max-evictable-idle-time-millis: 900000 # 配置一个连接在池中最大生存的时间，单位是毫秒 #StatViewServlet配置。（因为暴露的监控信息比较敏感，支持密码加密和访问ip限定） web-stat-filter: enableed: true stat-view-servlet: enabled: true url-pattern: /druid@Target({ ElementType.METHOD, ElementType.TYPE })@Retention(RetentionPolicy.RUNTIME)@Documented@Inheritedpublic @interface DataSource{ public DataSourceType value() default DataSourceType.MASTER;}aop切面思想
package com.hhubrain.common.aop;import java.util.Objects;import com.hhubrain.common.annotation.DataSource;import com.hhubrain.datasource.DynamicDataSourceContextHolder;import com.hhubrain.utils.StringUtils;import org.aspectj.lang.ProceedingJoinPoint;import org.aspectj.lang.annotation.Around;import org.aspectj.lang.annotation.Aspect;import org.aspectj.lang.annotation.Pointcut;import org.aspectj.lang.reflect.MethodSignature;import org.slf4j.Logger;import org.slf4j.LoggerFactory;import org.springframework.core.annotation.AnnotationUtils;import org.springframework.core.annotation.Order;import org.springframework.stereotype.Component;@Aspect@Order(1)@Componentpublic class DataSourceAspect{ protected Logger logger = LoggerFactory.getLogger(getClass()); //这里的地址就是上面的DataSource注解的位置 @Pointcut("@annotation(com.hhubrain.common.annotation.DataSource)" + "|| @within(com.hhubrain.common.annotation.DataSource)") public void dsPointCut() { } @Around("dsPointCut()") public Object around(ProceedingJoinPoint point) throws Throwable { DataSource dataSource = getDataSource(point); if (StringUtils.isNotNull(dataSource)) { DynamicDataSourceContextHolder.setDataSourceType(dataSource.value().name()); } try { return point.proceed(); } finally { // 销毁数据源 在执行方法之后 DynamicDataSourceContextHolder.clearDataSourceType(); } } public DataSource getDataSource(ProceedingJoinPoint point) { MethodSignature signature = (MethodSignature) point.getSignature(); DataSource dataSource = AnnotationUtils.findAnnotation(signature.getMethod(), DataSource.class); if (Objects.nonNull(dataSource)) { return dataSource; } return AnnotationUtils.findAnnotation(signature.getDeclaringType(), DataSource.class); }}驱动自定义配置类，去获取不同的数据源做不同的事情
package com.hhubrain.common.config;import java.io.IOException;import java.util.HashMap;import java.util.Map;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 com.hhubrain.datasource.DynamicDataSource;import com.hhubrain.model.enums.DataSourceType;import com.hhubrain.utils.SpringUtils;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;@Configurationpublic class DruidConfig{ //ConfigurationProperties 配置master数据源的前缀 看你的yml文件中的配置是否一致 @Bean @ConfigurationProperties("spring.datasource.master") public DataSource masterDataSource(DruidProperties druidProperties) { DruidDataSource dataSource = DruidDataSourceBuilder.create().build(); return druidProperties.dataSource(dataSource); } //ConditionalOnProperty这个注解的意思是 当配置文件中enabled这个值为true时才执行这个方法 @Bean @ConfigurationProperties("spring.datasource.slave") @ConditionalOnProperty(prefix = "spring.datasource.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 targetDataSources = new HashMap<>(); targetDataSources.put(DataSourceType.MASTER.name(), masterDataSource); setDataSource(targetDataSources, DataSourceType.SLAVE.name(), "slaveDataSource"); return new DynamicDataSource(masterDataSource, targetDataSources); } public void setDataSource(Map targetDataSources, String sourceName, String beanName) { try { DataSource dataSource = SpringUtils.getBean(beanName); targetDataSources.put(sourceName, dataSource); } catch (Exception e) { } } @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@Configurationpublic class DruidProperties{ @Value("${spring.datasource.druid.inital-size}") private int initialSize; @Value("${spring.datasource.druid.min-idle}") private int minIdle; @Value("${spring.datasource.druid.max-active}") private int maxActive; @Value("${spring.datasource.druid.max-wait}") private int maxWait; @Value("${spring.datasource.druid.time-between-eviction-runs-millis}") private int timeBetweenEvictionRunsMillis; @Value("${spring.datasource.druid.min-evictable-idle-time-millis}") private int minEvictableIdleTimeMillis; @Value("${spring.datasource.druid.max-evictable-idle-time-millis}") private int maxEvictableIdleTimeMillis; @Value("${spring.datasource.druid.validation-query}") private String validationQuery; @Value("${spring.datasource.druid.test-while-idle}") private boolean testWhileIdle; @Value("${spring.datasource.druid.test-on-borrow}") private boolean testOnBorrow; @Value("${spring.datasource.druid.test-on-return}") private boolean testOnReturn; public DruidDataSource dataSource(DruidDataSource datasource) { datasource.setInitialSize(initialSize); datasource.setMaxActive(maxActive); datasource.setMinIdle(minIdle); datasource.setMaxWait(maxWait); datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis); datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis); datasource.setMaxEvictableIdleTimeMillis(maxEvictableIdleTimeMillis); datasource.setValidationQuery(validationQuery); datasource.setTestWhileIdle(testWhileIdle); datasource.setTestOnBorrow(testOnBorrow); datasource.setTestOnReturn(testOnReturn); return datasource; }} 动态数据源
package com.hhubrain.datasource;import java.util.Map;import javax.sql.DataSource;import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;public class DynamicDataSource extends AbstractRoutingDataSource{ public DynamicDataSource(DataSource defaultTargetDataSource, Map targetDataSources) { super.setDefaultTargetDataSource(defaultTargetDataSource); super.setTargetDataSources(targetDataSources); super.afterPropertiesSet(); } @Override protected Object determineCurrentLookupKey() { return DynamicDataSourceContextHolder.getDataSourceType(); }} 数据源切换处理
package com.hhubrain.datasource;import org.slf4j.Logger;import org.slf4j.LoggerFactory;public class DynamicDataSourceContextHolder{ public static final Logger log = LoggerFactory.getLogger(DynamicDataSourceContextHolder.class); private static final ThreadLocal CONTEXT_HOLDER = new ThreadLocal<>(); public static void setDataSourceType(String dsType) { log.info("切换到{}数据源", dsType); CONTEXT_HOLDER.set(dsType); } public static String getDataSourceType() { return CONTEXT_HOLDER.get(); } public static void clearDataSourceType() { CONTEXT_HOLDER.remove(); }} 方便在非spring管理环境中获取bean
package com.hhubrain.utils;import org.springframework.aop.framework.AopContext;import org.springframework.beans.BeansException;import org.springframework.beans.factory.NoSuchBeanDefinitionException;import org.springframework.beans.factory.config.BeanFactoryPostProcessor;import org.springframework.beans.factory.config.ConfigurableListableBeanFactory;import org.springframework.context.ApplicationContext;import org.springframework.context.ApplicationContextAware;import org.springframework.stereotype.Component;@Componentpublic final class SpringUtils implements BeanFactoryPostProcessor, ApplicationContextAware { private static ConfigurableListableBeanFactory beanFactory; private static ApplicationContext applicationContext; @Override public void postProcessBeanFactory(ConfigurableListableBeanFactory beanFactory) throws BeansException { SpringUtils.beanFactory = beanFactory; } @Override public void setApplicationContext(ApplicationContext applicationContext) throws BeansException { SpringUtils.applicationContext = applicationContext; } @SuppressWarnings("unchecked") public static T getBean(String name) throws BeansException { return (T) beanFactory.getBean(name); } public static T getBean(Class clz) throws BeansException { T result = (T) beanFactory.getBean(clz); return result; } public static boolean containsBean(String name) { return beanFactory.containsBean(name); } public static boolean isSingleton(String name) throws NoSuchBeanDefinitionException { return beanFactory.isSingleton(name); } public static Class getType(String name) throws NoSuchBeanDefinitionException { return beanFactory.getType(name); } public static String[] getAliases(String name) throws NoSuchBeanDefinitionException { return beanFactory.getAliases(name); } @SuppressWarnings("unchecked") public static T getAopProxy(T invoker) { return (T) AopContext.currentProxy(); } public static String[] getActiveProfiles() { return applicationContext.getEnvironment().getActiveProfiles(); } public static String getActiveProfile() { final String[] activeProfiles = getActiveProfiles(); return StringUtils.isNotEmpty(activeProfiles) ? activeProfiles[0] : null; }} type的枚举类，差点忘了
package com.hhubrain.model.enums;public enum DataSourceType{ MASTER, SLAVE, TDENGINE}在对应的service层或者mapper层类上面或者方法上添加@DataSource(value=type)注解即可
我这里是在定时器里写的，就直接在mapper的接口方法上加的
sqlserver 对应yml文件中salve中的数据源
mysql 对应yml文件中master中的数据源
Failed to configure a Datasource: 'url' attribute is not specified and no embedded datasource could be configured.
解决方法：
把jdbcUrl 改成url 不管是什么 都改成url即可
Failed to bind properties under 'spring.datasource.slave' to javax.sql.Datasource:
解决方法：
Cavse: org.springframework.jdbc.CannotbetJdbcConnectionException: Failed to obtain JDBcConnection; nested exception is com.microsoft,sglserverjdbcSQLServerException: 通过端口 1433 连接到主机 Localhost的tcp/IP连接失败。
解决方法：
右键点击"计算机"-->选择"管理"-->选择sqlserver配置管理-->重启服务
Failed to obtain JDBC Connection; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: 对象名 'DUAL' 无效。
解决方法：
造成这个原因是配置mysql的时候需要validation-query：SELECT 1 FROM DUAL
但是sqlserver没有DUAL 造成报错，改成以下即可
问题千千万，唯有花时间
来源地址：https://blog.csdn.net/qq_44050737/article/details/130227327
--结束END--
本文标题: SpingBoot使用Mybatis-Plus操作多数据源,同时操作sqlserver和mysql
本文链接: https://lsjlt.com/news/396190.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
2024-10-23
2024-10-22
2024-10-22
2024-10-22
2024-10-22
2024-10-22
2024-10-22
2024-10-22
2024-10-22
2024-10-22
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
官方手机版
微信公众号
商务合作
0