springboot-多数据源配置-sql-MySQL

承灿 2023/9/4

# springboot-多数据源配置-sql和MySQL

整个目录结构如图：

# 依赖

<dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <!-- Spring Boot Test 依赖 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <!-- Spring Boot Mybatis 依赖 -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.2.0</version>
        </dependency> 
        <!-- MySQL 连接驱动依赖 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>${mysql-connector}</version>
        </dependency> <!-- Druid 数据连接池依赖 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.0.18</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>3.8.1</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>com.microsoft.sqlserver</groupId>
            <artifactId>mssql-jdbc</artifactId>
            <version>7.0.0.jre8</version>
        </dependency>
    </dependencies>

# yml

spring:
  datasource:
    master:
      url: jdbc:mysql://localhost:3306/xxxx?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
      driver-class-name: com.mysql.cj.jdbc.Driver
      username: root
      password: root
      type: com.alibaba.druid.pool.DruidDataSource
      druid:
        # 初始化大小，最小，最大
        initial-size: 5
        min-idle: 5
        max-active: 20
        # 配置获取连接等待超时的时间
        max-wait: 60000
        # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
        time-between-eviction-runs-millis: 60000
        # 配置一个连接在池中最小生存的时间，单位是毫秒
        min-evictable-idle-time-millis: 300000
        # 测试连接
        test-while-idle: true
        test-on-borrow: false
        test-on-return: false
    # sql server 2012配置信息
    cluster:
      url: jdbc:sqlserver://192.168.10.11;DatabaseName=isims
      driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
      username: root
      password: root

# 配置类

# MasterDataSourceConfig.java

package com.dataojo.huhehaotebasic.config.datasource;

import com.alibaba.druid.pool.DruidDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = {"com.dataojo.huhehaotebasic.mapper.master"}, sqlSessionFactoryRef = "masterSqlSessionFactory")
public class MasterDataSourceConfig {
    static final String MAPPER_LOCATION = "classpath*:mybatis/master/*.xml";

    @Value("${spring.datasource.master.url}")
    private String url;

    @Value("${spring.datasource.master.username}")
    private String user;

    @Value("${spring.datasource.master.password}")
    private String password;

    @Value("${spring.datasource.master.driver-class-name}")
    private String driverClass;

    @Primary
    @Bean(name = "masterDataSource")
    public DataSource masterDataSource() {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName(driverClass);
        dataSource.setUrl(url);
        dataSource.setUsername(user);
        dataSource.setPassword(password);
        return dataSource;
    }


    @Bean(name = "masterSqlSessionFactory")
    @Primary
    public SqlSessionFactory masterSqlSessionFactory(@Qualifier("masterDataSource") DataSource masterDataSource) throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(masterDataSource);
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources(MAPPER_LOCATION));
        org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
        configuration.setMapUnderscoreToCamelCase(true);
        sessionFactory.setConfiguration(configuration);
        return sessionFactory.getObject();
    }

    @Bean(name = "masterTransactionManager")
    @Primary
    public DataSourceTransactionManager masterTransactionManager() {
        return new DataSourceTransactionManager(masterDataSource());
    }


}

# ClusterDataSourceConfig.java

package com.dataojo.huhehaotebasic.config.datasource;

import com.alibaba.druid.pool.DruidDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = {"com.dataojo.huhehaotebasic.mapper.cluster"}, sqlSessionFactoryRef = "clusterSqlSessionFactory")
public class ClusterDataSourceConfig {

    static final String MAPPER_LOCATION = "classpath*:mybatis/cluster/*.xml";

    @Value("${spring.datasource.cluster.url}")
    private String url;

    @Value("${spring.datasource.cluster.username}")
    private String user;

    @Value("${spring.datasource.cluster.password}")
    private String password;

    @Value("${spring.datasource.cluster.driver-class-name}")
    private String driverClass;

    @Bean(name = "clusterDataSource")
    public DataSource clusterDataSource() {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName(driverClass);
        dataSource.setUrl(url);
        dataSource.setUsername(user);
        dataSource.setPassword(password);
        return dataSource;
    }


    @Bean(name = "clusterSqlSessionFactory")
    public SqlSessionFactory clusterSqlSessionFactory(@Qualifier("clusterDataSource") DataSource clusterDataSource)
            throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(clusterDataSource);
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources(MAPPER_LOCATION));
        return sessionFactory.getObject();
    }

    @Bean(name = "clusterTransactionManager")
    public DataSourceTransactionManager clusterTransactionManager() {
        return new DataSourceTransactionManager(clusterDataSource());
    }


}

# 请根据实体类自行创建表，mapper层、Service、Controller省略

# 问题记录1

No qualifying bean of type 'javax.sql.DataSource' available: more than one 'primary' bean found amon

多数据源报错:No qualifying bean of type 'javax.sql.DataSource' available: more than one 'primary' bean found among candidates: [test2DataSource, test1DataSource]由于之前引入mybatis的时候引入了pom

<dependency>
   <groupId>org.springframework.boot</groupId>
   <artifactId>spring-boot-autoconfigure</artifactId>
</dependency>

spring会依赖spring-boot-autoconfigure这个jar包这个jar包中有个DataSourceAutoConfiguration 会初始化DataSourceInitializer 这个类，这个类有一个init方法会去获取DataSource(数据源)

@Configuration
@ConditionalOnClass({ DataSource.class, EmbeddedDatabaseType.class })
@EnableConfigurationProperties(DataSourceProperties.class)
@Import({ Registrar.class, DataSourcePoolMetadataProvidersConfiguration.class })
public class DataSourceAutoConfiguration {
 
@Configuration
@ConditionalOnMissingBean(DataSourceInitializer.class)
protected static class DataSourceInitializerConfiguration {
 
@Bean
public DataSourceInitializer dataSourceInitializer() {
return new DataSourceInitializer();
}

初始化方法中会获取数据源需要初始化一些ddl操作也是就runSchemaScripts()方法检查初始化时是否需要执行sql script ,当你有两个数据源的时候，程序不知道取哪一个，所以报错

@PostConstruct
public void init() {
if (!this.properties.isInitialize()) {
logger.debug("Initialization disabled (not running DDL scripts)");
      return;
}
if (this.applicationContext.getBeanNamesForType(DataSource.class, false, false).length > 0) {
this.dataSource = this.applicationContext.getBean(DataSource.class);
}
if (this.dataSource == null) {
logger.debug("No DataSource found so not initializing");
      return;
}
   runSchemaScripts();
}
 
private void runSchemaScripts() {
   List<Resource> scripts = getScripts(this.properties.getSchema(), "schema");
   if (!scripts.isEmpty()) {
      runScripts(scripts);
      try {
this.applicationContext.publishEvent(new DataSourceInitializedEvent(
this.dataSource));
// The listener might not be registered yet, so don't rely on it.
if (!this.initialized) {
            runDataScripts();
            this.initialized = true;
}
      }
catch (IllegalStateException ex) {
logger.warn("Could not send event to complete DataSource initialization ("
+ ex.getMessage() + ")");
}
   }
}

解决办法：如果是xml配置的话定义数据源的地方加个primary="true" 记得只给其中的一个加，当多数据源时标示这个数据源是主要的

<bean id="payment-dataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close" primary="true">

如果是spring boot项目 spring boot 启动类加上 exclude = DataSourceAutoConfiguration.class 代表启动项目的时候不加载这个类例子：

@ComponentScan(basePackages = "xxx")
@MapperScan("xxx")
@SpringBootApplication(exclude={
        DataSourceAutoConfiguration.class})
public class Application {}

# 问题记录2

启动项目，报错：The server selected protocol version TLS10 is not accepted by client preferences [TLS12]解决办法

使用idea加载刚down下来的项目启动时，报错：The server selected protocol version TLS10 is not accepted by client preferences [TLS12] 报错信息如下：在网上找了很多解决办法，最终找到一个可行的，整理教程如下

1、找到jre的安装目录，例如我自己的：D:\Dev_Util\Java\jdk8.0.312\jre\lib\security。
使用编辑器打开java.security。

2、在文档中搜索 TLSv1, TLSv1.1

3、将jdk.tls.disabledAlgorithms配置中的TLSv1、TLSv1.1、3DES_EDE_CBC删除

或者直接注释掉，使用修改好的也可以。

jdk.tls.disabledAlgorithms=SSLv3, RC4, DES, MD5withRSA, \
    DH keySize < 1024, EC keySize < 224, anon, NULL, \
    include jdk.disabled.namedCurves

4、修改完成后，保存，重启项目即可。完美解决

参考：

https://blog.csdn.net/dangerous317/article/details/122172334 https://blog.csdn.net/qq_38080370/article/details/95022573 https://blog.csdn.net/ypp91zr/article/details/84298861

SpringBoot 缓存之@Cacheable SpringPrimary 注解

CCNAIVE

Choose mode