Springboot 连接MySql数据库（多数据源）

个人笔记

1.pom引入依赖：

<dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.10</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis-spring</artifactId>
            <version>2.0.3</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.3</version>
        </dependency>
    </dependencies>

2.application.yml配置文件关于数据配置

spring:
  datasource:
    one:
      driver-class-name: com.mysql.cj.jdbc.Driver
      url: "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&useJDBCCompliantTimezoneShift=true
     &useLegacyDatetimeCode=false&serverTimezone=GMT%2B8&useSSL=true&allowMultiQueries=true&autoReconnect=true&useAffectRows=true"
      username: root
      password: root
      # 初始化时建立物理连接的个数。初始化发生在显示调用 init 方法，或者第一次 getConnection 时
      initialSize: 5
      # 最小连接池数量
      minIdle: 5
      # 最大连接池数量
      maxActive: 10
      # 获取连接时最大等待时间，单位毫秒。配置了 maxWait 之后，缺省启用公平锁，并发效率会有所下降，如果需要可以通过配置 useUnfairLock 属性为 true 使用非公平锁。
      maxWait: 60000
      # Destroy 线程会检测连接的间隔时间，如果连接空闲时间大于等于 minEvictableIdleTimeMillis 则关闭物理连接。
      timeBetweenEvictionRunsMillis: 60000
      # 连接保持空闲而不被驱逐的最小时间
      minEvictableIdleTimeMillis: 300000
      # 用来检测连接是否有效的 sql 
      validationQuery: SELECT 1
      # 建议配置为 true，不影响性能，并且保证安全性。申请连接的时候检测，如果空闲时间大于 timeBetweenEvictionRunsMillis，执行 validationQuery 检测连接是否有效。
      testWhileIdle: true
      # 申请连接时执行 validationQuery 检测连接是否有效
      testOnBorrow: false
      # 归还连接时执行 validationQuery 检测连接是否有效
      testOnReturn: false
      # 是否自动回收超时连接
      removeAbandoned: true
      # 超时时间 (以秒数为单位)
      remove-abandoned-timeout: 1800
    two:
    第二个连接库数据源

3.配置文件
第一个数据源配置

package com.jojo.practice.config;

import com.alibaba.druid.pool.DruidDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
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;


/**
 * @author java-development
 */

@Configuration
@MapperScan(basePackages = DataSourceOneConfig.PACKAGES, sqlSessionFactoryRef = "sqlSessionFactoryOne")
public class DataSourceOneConfig {

    /**
     * 扫描Mapper路径
     */
    protected static final String PACKAGES = "com.jojo.practice.mapper.one";
    /**
     * XML所在路径
     */
    protected static final String MAPPER_XML_LOCATION = "classpath*:com.jojo.practice.mapper.one/*.xml";

    @Primary
    @Bean(name = "dataSourceOne")
    //读取application.properties中的配置参数映射成为一个对象

    @ConfigurationProperties(prefix = "spring.datasource.one")
    public DataSource getDateSourceOne() {
        return DataSourceBuilder.create().type(DruidDataSource.class).build();
    }

    @Primary
    @Bean(name = "sqlSessionFactoryOne")
    public SqlSessionFactory sqlSessionFactoryOne(@Qualifier("dataSourceOne") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(
                // 设置mybatis的xml所在位置
                new PathMatchingResourcePatternResolver().getResources(MAPPER_XML_LOCATION));
        return bean.getObject();
    }

    @Primary
    @Bean(name = "sqlSessionTemplateOne")
    public SqlSessionTemplate sqlSessionTemplateOne(@Qualifier("sqlSessionFactoryOne") SqlSessionFactory sessionFactory) {
        return new SqlSessionTemplate(sessionFactory);
    }

    /**
     * 事务管理
     *
     * @param dataSource
     * @return
     */
    @Bean(name = "transactionManagerOne")
    public DataSourceTransactionManager transactionManagerOne(@Qualifier("dataSourceOne") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }
}

第二个数据源配置

package com.jojo.practice.config;

import com.alibaba.druid.pool.DruidDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
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;


/**
 * @author java-development
 */

@Configuration
@MapperScan(basePackages = DataSourceTwoConfig.PACKAGES, sqlSessionFactoryRef = "sqlSessionFactoryTwo")
public class DataSourceTwoConfig {

    /**
     * 扫描Mapper路径
     */
    protected static final String PACKAGES = "com.jojo.practice.mapper.two";
    /**
     * XML所在路径
     */
    protected static final String MAPPER_XML_LOCATION = "classpath*:com.jojo.practice.mapper.two/*.xml";

    @Bean(name = "dataSourceTwo")
    @ConfigurationProperties(prefix = "spring.datasource.two")//读取application.properties中的配置参数映射成为一个对象
    public DataSource getDateSourceTwo() {
        return DataSourceBuilder.create().type(DruidDataSource.class).build();
    }

    @Primary
    @Bean(name = "sqlSessionFactoryTwo")
    public SqlSessionFactory sqlSessionFactoryTwo(@Qualifier("dataSourceTwo") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(
                // 设置mybatis的xml所在位置
                new PathMatchingResourcePatternResolver().getResources(MAPPER_XML_LOCATION));
        return bean.getObject();
    }

    @Primary
    @Bean(name = "sqlSessionTemplateTwo")
    public SqlSessionTemplate sqlSessionTemplateTwo(@Qualifier("sqlSessionFactoryTwo") SqlSessionFactory sessionFactory) {
        return new SqlSessionTemplate(sessionFactory);
    }

    /**
     * 事务管理
     *
     * @param dataSource
     * @return
     */
    @Bean(name = "transactionManagerTwo")
    public DataSourceTransactionManager transactionManagerTwo(@Qualifier("dataSourceTwo") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }
}

4.结果展示：

5.遇到的问题以及注意事项
在配置文件中主入DataSource对象，两个配置类都使用了@Primary

导致：

Caused by: org.springframework.beans.factory.NoUniqueBeanDefinitionException: No qualifying bean of type 'javax.sql.DataSource' available: more than one 'primary' bean found among candidates: [dataSourceOne, dataSourceTwo]
	at org.springframework.beans.factory.support.DefaultListableBeanFactory.determinePrimaryCandidate(DefaultListableBeanFactory.java:1664) ~[spring-beans-5.3.5.jar:5.3.5]
	at org.springframework.beans.factory.support.DefaultListableBeanFactory.determineAutowireCandidate(DefaultListableBeanFactory.java:1624) ~[spring-beans-5.3.5.jar:5.3.5]
	at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:1355) ~[spring-beans-5.3.5.jar:5.3.5]
	at org.springframework.beans.factory.support.DefaultListableBeanFactory$DependencyObjectProvider.getIfUnique(DefaultListableBeanFactory.java:2063) ~[spring-beans-5.3.5.jar:5.3.5]
	at org.springframework.boot.autoconfigure.jdbc.DataSourceInitializerInvoker.getDataSourceInitializer(DataSourceInitializerInvoker.java:98) ~[spring-boot-autoconfigure-2.4.4.jar:2.4.4]
	at org.springframework.boot.autoconfigure.jdbc.DataSourceInitializerInvoker.afterPropertiesSet(DataSourceInitializerInvoker.java:61) ~[spring-boot-autoconfigure-2.4.4.jar:2.4.4]
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1845) ~[spring-beans-5.3.5.jar:5.3.5]
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1782) ~[spring-beans-5.3.5.jar:5.3.5]
	... 85 common frames omitted

解决方法：
1.在启动类上加 exclude = DataSourceAutoConfiguration.class
2.配置类中只标注一个主要源
参考文章

3.sqlSessionFactoryBean调用的方法中setMapperLocations读取不到mapper资源的原因getResource 和 getResources不同。

getResource()：
	从类的根路径下获取文件
getResources():
	获取所有类路径下的指定文件
可以通过classpath前缀指定，从所有的类路径下获取指定文件，与classpath前缀的区别是classpath前缀只能获取当类路径下的资源文件，而classpath前缀可以获取所有类路径下的资源文件，包括jar包中的。