springboot配置多数据源完整流程——直达成功之路

应用场景：同步一个用户管理系统的所有用户到一个业务系统中，但是这两个系统的数据库不是同一个数据库而且数据库类型不同，一个是mysql、一个是SqlServer。
目的：在一个spring boot项目中同时使用两种数据库进行数据操作。

一、maven坐标

导入数据库驱动maven坐标

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.47</version>
</dependency>

<dependency>
    <groupId>com.microsoft.sqlserver</groupId>
    <artifactId>sqljdbc4</artifactId>
    <version>4.0</version>
</dependency>

二、Mapper接口与xml文件目录调整

不同数据源放置的mapper接口和mapper文件放在不同位置。

三、application.yml

1. 注意数据源名称不能重复
2. SqlServer配置属性为jdbc-url而mysql为url

datasource:
  url: 127.0.0.1:3306
  username: ***
  password: ***
  database:
    chc-admin:   #mysql数据源名称1
      driverClassName: com.mysql.jdbc.Driver
      url: jdbc:mysql://${datasource.url}/CLabraries?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=GMT
      username: ${datasource.username}
      password: ${datasource.password}
      initialSize: 5
      minIdle: 1
      maxActive: 20
      maxWait: 60000
      timeBetweenEvictionRunsMillis: 60000
      minEvictableIdleTimeMillis: 300000
      validationQuery: SELECT 1 FROM DUAL
      testWhileIdle: true
      testOnBorrow: false
      testOnReturn: false
      poolPreparedStatements: true
      maxPoolPreparedStatementPerConnectionSize: 20
      #filters: stat,wall,slf4j
      connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
      useGlobalDataSourceStat: true
    ncdb:   #SqlServer数据源名称2
      driverClassName: com.microsoft.sqlserver.jdbc.SQLServerDriver
      jdbc-url: jdbc:sqlserver://127.0.0.1:12433;DatabaseName=NCDB
      username: ***
      password: ******
      type: com.alibaba.druid.pool.DruidDataSource

四、DataSource数据源注册

1. 主数据源应配置@Primary，但多数据源下只能存在一个。
2. 第一个主数据源配置类中有一些非必须设置如 @Bean(name = "mysqlDataSource", destroyMethod = "close", initMethod = "init")不需要设置destroyMethod 以及druid相关配置据自己需求以斟酌。
3. @EnableTransactionManagement默认是jdk动态代理，@EnableTransactionManagement(proxyTargetClass = true)可以启用aspectj的代理方式。

1. MySQL数据源注册文件1

package chc.dao.dbconfig;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

@Configuration
@EnableTransactionManagement
@MapperScan(
        basePackages = {"chc.dao.mapper.admin"},
        sqlSessionFactoryRef = "mysqlSessionFactory",
        sqlSessionTemplateRef = "mysqlSessionTemplate"
)
public class DataSourceConfig {
    
    @Bean(name = "mysqlDataSource", destroyMethod = "close", initMethod = "init")
    @Primary
    @ConfigurationProperties(prefix = "datasource.database.chc-admin")
    public DruidDataSource adminDataSource() {
        return new DruidDataSource();
    }

    /**
     * 事务管理,具体使用在service层加入@Transactional注解
     */
    @Bean(name = "mysqlTransactionManager")
    public PlatformTransactionManager AdminAnnotationDrivenTransactionManager() {
        return new DataSourceTransactionManager(adminDataSource());
    }

    @Bean(name = "mysqlSessionFactory")
    public SqlSessionFactory basicSqlSessionFactory(@Qualifier("mysqlDataSource") DataSource basicDataSource) throws Exception {
        SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
        factoryBean.setDataSource(basicDataSource);
        factoryBean.setMapperLocations(
                new PathMatchingResourcePatternResolver().getResources("classpath:mapper/admin/ehr/*.xml"));
        return factoryBean.getObject();
    }
    
     @Bean(name = "mysqlSessionTemplate")
    public SqlSessionTemplate testSqlSessionTemplate(
            @Qualifier("mysqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
    
    /**
     * druid监控
     *
     * @return
     */
    @Bean
    public ServletRegistrationBean druidServlet() {
        ServletRegistrationBean reg = new ServletRegistrationBean();
        reg.setServlet(new StatViewServlet());
        reg.addUrlMappings("/druid/*");
        reg.addInitParameter("loginUsername", "admin");
        reg.addInitParameter("loginPassword", "123456");
        return reg;
    }

    /**
     * druid监控过滤
     *
     * @return
     */
    @Bean
    public FilterRegistrationBean filterRegistrationBean() {
        FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean();
        filterRegistrationBean.setFilter(new WebStatFilter());
        filterRegistrationBean.addUrlPatterns("/*");
        filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
        return filterRegistrationBean;
    }
}

2. SqlServer数据源注册文件2

package chc.dao.dbconfig;

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 org.springframework.transaction.annotation.EnableTransactionManagement;

import javax.sql.DataSource;

@Configuration
@EnableTransactionManagement(proxyTargetClass = true)
@MapperScan(
        basePackages = {"chc.dao.mapper.admin.ehr"},
        sqlSessionFactoryRef = "sqlServerSessionFactory",
        sqlSessionTemplateRef = "sqlServerSessionTemplate"
)
public class SqlServerDataSourceConfig {

    @Bean(name = "sqlServerDataSource")
    @ConfigurationProperties(prefix = "datasource.database.ncdb")
    public DataSource dataSource() {
        return DataSourceBuilder.create().build();
    }


    @Bean(name = "sqlServerTransactionManager")
    public DataSourceTransactionManager transactionManager(@Qualifier("sqlServerDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }


    @Bean(name = "sqlServerSessionFactory")
    public SqlSessionFactory basicSqlSessionFactory(@Qualifier("sqlServerDataSource") DataSource basicDataSource) throws Exception {
        SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
        factoryBean.setDataSource(basicDataSource);
        factoryBean.setMapperLocations(
                new PathMatchingResourcePatternResolver().getResources("classpath:mapper/admin/ehr/*.xml"));
        return factoryBean.getObject();
    }


    @Bean(name = "sqlServerSessionTemplate")
    public SqlSessionTemplate testSqlSessionTemplate(
            @Qualifier("sqlServerSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}