Springboot 多数据源配置

Springboot 多数据源配置

       前几天工作中遇到个项目说出来比较恶心，对接老系统要求一个项目连接多个数据库，Oracle、Mysql、SqlServer，这个时候看了一些方式写了一套多数据源常用的方式，将代码分享出来。后续会将代码整体迁移到gitlub上，文章仅做快速搭建参考。

我们先用两个mysql进行操作测试，Oracle和SqlServer下篇文章来写如何应用如何连接。

项目选型 Springboot 2.6  gradle 4.6  java8  
项目下载 : https://download.csdn.net/download/qq_27535933/11435439
创建Springboot的项目就省略不写了直接粘贴一点干货。

1.gradle依赖  

plugins {
    id 'org.springframework.boot' version '2.1.6.RELEASE'
    id 'java'
}

apply plugin: 'io.spring.dependency-management'

group = 'com.example'
version = '0.0.1-SNAPSHOT'
sourceCompatibility = '1.8'

configurations {
    compileOnly {
        extendsFrom annotationProcessor
    }
}

repositories {
    mavenCentral()
}

dependencies {

    //启动
    compile ('org.springframework.boot:spring-boot-starter')
    //web
    compile ('org.springframework.boot:spring-boot-starter-web')
    //lombok
    compileOnly 'org.projectlombok:lombok'
    //mysql 连接
    compile 'mysql:mysql-connector-java'
    //mybatis
    compile("org.mybatis.spring.boot:mybatis-spring-boot-starter:1.3.1")

    annotationProcessor 'org.projectlombok:lombok'
    testImplementation 'org.springframework.boot:spring-boot-starter-test'
}

2.配置文件：现在本地的数据库创建两个datebase 一个命名test 一个命名test2， 具体配置的以后后续补上

#端口号
server.port=8090

# mysql  one
spring.datasource.mysql.jdbc-url=jdbc:mysql://localhost:3306/test
spring.datasource.mysql.username=root
spring.datasource.mysql.password=123456
spring.datasource.mysql.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.mysql.initialSize=5
spring.datasource.mysql.minIdle=5
spring.datasource.mysql.maxActive=20
spring.datasource.mysql.maxWait=60000
spring.datasource.mysql.timeBetweenEvictionRunsMillis=60000
spring.datasource.mysql.validationQuery=SELECT 1
spring.datasource.mysql.testWhileIdle=true
spring.datasource.mysql.testOnBorrow=false
spring.datasource.mysql.testOnReturn=false
spring.datasource.mysql.poolPreparedStatements=true
spring.datasource.mysql.maxPoolPreparedStatementPerConnectionSize=20
spring.datasource.mysql.filters=stat
spring.datasource.mysql.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000

# mysql  two
spring.datasource.mysql2.jdbc-url=jdbc:mysql://localhost:3306/test2
spring.datasource.mysql2.username=root
spring.datasource.mysql2.password=123456
spring.datasource.mysql2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.mysql2.initialSize=5
spring.datasource.mysql2.minIdle=5
spring.datasource.mysql2.maxActive=20
spring.datasource.mysql2.maxWait=60000
spring.datasource.mysql2.timeBetweenEvictionRunsMillis=60000
spring.datasource.mysql2.validationQuery=SELECT 1
spring.datasource.mysql2.testWhileIdle=true
spring.datasource.mysql2.testOnBorrow=false
spring.datasource.mysql2.testOnReturn=false
spring.datasource.mysql2.poolPreparedStatements=true
spring.datasource.mysql2.maxPoolPreparedStatementPerConnectionSize=20
spring.datasource.mysql2.filters=stat
spring.datasource.mysql2.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000

3.这个数据源匹配的上面的配置文件

package com.example.config;

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 javax.sql.DataSource;

/**
 * @author YanChengLong
 * @date 2019/7/24
 * @desc 数据源配置
 */
@Configuration
public class MySqlDateSourceConfig {

    /**
     * @author YanChengLong
     * @date 2019/5/11
     * @desc 默认主数据源 mysql 数据库1
     */
    @Bean(name = "mysqlDataSource")
    @Qualifier("mysqlDataSource")
    @Primary // @Primary注解定义主数据源
    @ConfigurationProperties(prefix = "spring.datasource.mysql")
    public DataSource mysqlDataSource() {
        return DataSourceBuilder.create().build();
    }

    /**
     * @author YanChengLong
     * @date 2019/5/11
     * @desc mysql 数据库2
     */
    @Bean(name = "mysqlDataSource2")
    @Qualifier("mysqlDataSource2")
    @ConfigurationProperties(prefix = "spring.datasource.mysql2")
    public DataSource mysqlDataSource2() {
        return DataSourceBuilder.create().build();
    }

}

这个配置里面有一个问题需要我们注意：

这两个数据源的地方Springboot的版本写法是不一样的：

应用Springboot  v1 版本的 写法如下：
 

spring.datasource.mysql.jdbc.url=jdbc:mysql://localhost:3306/test

应用Springboot  v2 版本的 写法如下：

spring.datasource.mysql2.jdbc-url=jdbc:mysql://localhost:3306/test2

设置数据源的配置：

新建两个类 ：MysqlDataSourceOneConfig   MysqlDataSourceTwoConfig

MysqlDataSourceOneConfig：

package com.example.config;

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.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 javax.sql.DataSource;

/**
 * @author YanChengLong
 * @date 2019/7/24
 * @desc 数据源配置1
 */
@Configuration
@MapperScan(basePackages = "com.example.mapper", sqlSessionTemplateRef  = "mysqlSqlSessionTemplate")
public class MysqlDataSourceOneConfig {

    /**
     * @author YanChengLong
     * @date 2019/5/11
     * @desc 创建SqlSession对象
     */
    @Bean(name = "mysqlSqlSessionFactory")
    @Primary
    public SqlSessionFactory mysqlSqlSessionFactory(@Qualifier("mysqlDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        return bean.getObject();
    }

    /**
     * @author YanChengLong
     * @date 2019/5/11
     * @desc 配置声明式事务管理器
     */
    @Bean(name = "mysqlTransactionManager")
    @Primary
    public PlatformTransactionManager mysqlTransactionManager(@Qualifier("mysqlDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    /**
     * @author YanChengLong
     * @date 2019/5/11
     * @desc sql会话
     */
    @Bean(name = "mysqlSqlSessionTemplate")
    @Primary
    public SqlSessionTemplate mysqlSqlSessionTemplate(@Qualifier("mysqlSqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

MysqlDataSourceTwoConfig：

package com.example.config;

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.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 javax.sql.DataSource;

/**
 * @author YanChengLong
 * @date 2019/7/24
 * @desc 数据源配置2
 */
@Configuration
@MapperScan(basePackages = "com.example.mapper2", sqlSessionTemplateRef  = "mysqlSqlSessionTemplate2")
public class MysqlDataSourceTwoConfig {

    /**
     * @author YanChengLong
     * @date 2019/5/11
     * @desc 创建SqlSession对象
     */
    @Bean(name = "mysqlSqlSessionFactory2")
    @Primary
    public SqlSessionFactory mysqlSqlSessionFactory(@Qualifier("mysqlDataSource2") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        return bean.getObject();
    }

    /**
     * @author YanChengLong
     * @date 2019/5/11
     * @desc 配置声明式事务管理器
     */
    @Bean(name = "mysqlTransactionManager2")
    @Primary
    public PlatformTransactionManager mysqlTransactionManager(@Qualifier("mysqlDataSource2") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    /**
     * @author YanChengLong
     * @date 2019/5/11
     * @desc sql会话
     */
    @Bean(name = "mysqlSqlSessionTemplate2")
    @Primary
    public SqlSessionTemplate mysqlSqlSessionTemplate(@Qualifier("mysqlSqlSessionFactory2") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

注意这个地方： 这个地方是根据项目的目录地址创建的，主要目的是扫描下面sql，如果用XML进行编写的话也是可以的，但是建议分开来做。

整体项目的结构：

测试是没有问题的，有问题的小伙伴可以私信我，后续会补充讲解数据源的具体配置。