Spring Boot Oracle SqlServer 多数据源连接配置

• 1、pom.xml配置文件，添加依赖
• 2、properties配置文件，添加多数据源配置
• 3、新建datasource配置类
• 4、新建各数据源配置类

1、pom.xml配置文件添加依赖

<!-- spring boot版本需要2.0以上，HikariCP 配置类需要-->
<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.0.3.RELEASE</version>
    <relativePath/>
</parent>

<!-- oracle 数据源驱动依赖（需要本地导包）-->
<dependency>
    <groupId>com.oracle</groupId>
    <artifactId>ojdbc6</artifactId>
    <version>11.2.0.4</version>
</dependency>

<!-- sqlserver 数据源驱动依赖-->
<dependency>
    <groupId>com.microsoft.sqlserver</groupId>
    <artifactId>mssql-jdbc</artifactId>
    <version>7.2.2.jre8</version>
</dependency>

<!-- HikariCP 连接池依赖-->
<dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP</artifactId>
    <version>3.3.1</version>
</dependency>

2、properties配置文件，添加多数据源配置

# Oracle数据源1
spring.datasource.primary.jdbc-url=jdbc:oracle:thin:@127.0.0.1:1521/dbName1
spring.datasource.primary.username=username
spring.datasource.primary.password=password
spring.datasource.primary.driver-class-name=oracle.jdbc.OracleDriver

# Oracle数据源2
spring.datasource.second.jdbc-url=jdbc:oracle:thin:@127.0.0.1:1521/dbName2
spring.datasource.second.username=username
spring.datasource.second.password=password
spring.datasource.second.driver-class-name=oracle.jdbc.OracleDriver

# SqlServer数据源3
spring.datasource.third.jdbc-url=jdbc:sqlserver://127.0.0.1:1433;DatabaseName=dbName3
spring.datasource.third.username=username
spring.datasource.third.password=password
spring.datasource.third.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver

#各数据源 配置
spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=none
spring.jpa.properties.hibernate.format_sql=true
spring.jpa.properties.hibernate.max_fetch_depth=1
spring.jpa.properties.hibernate.temp.use_jdbc_metadata_defaults=false
spring.jpa.open-in-view=false
##  Hikari 连接池配置
## 连接池名称
spring.datasource.primary.pool-name=HikariPool-1
## 最小空闲连接数量，默认是10
spring.datasource.primary.minimum-idle=5
## 连接池最大连接数，默认是10
spring.datasource.primary.maximum-pool-size=10
## 空闲连接存活最大时间，默认600000（10分钟）
spring.datasource.primary.idle-timeout=600000
## 此属性控制池中连接的最长生命周期，值0表示无限生命周期，默认1800000即30分钟
spring.datasource.primary.max-lifetime=1800000
## 数据库连接超时时间,默认30秒，即30000
spring.datasource.primary.connection-timeout=30000

spring.datasource.second.pool-name=HikariPool-2
spring.datasource.second.minimum-idle=5
spring.datasource.second.maximum-pool-size=10
spring.datasource.second.idle-timeout=600000
spring.datasource.second.max-lifetime=1800000
spring.datasource.second.connection-timeout=30000

spring.datasource.third.pool-name=HikariPool-3
spring.datasource.third.minimum-idle=5
spring.datasource.third.maximum-pool-size=10
spring.datasource.third.idle-timeout=600000
spring.datasource.third.max-lifetime=1800000
spring.datasource.third.connection-timeout=30000

## 日志级别
logging.level.web=DEBUG
logging.level.root=DEBUG

3、新建datasource配置类

数据源配置.png

如图所示，在config包下，新建datasource包，再在其下新建DataSourceConfig管理类，最后再添加各个数据源单独配置类

DataSourceConfig.java

import com.zaxxer.hikari.HikariDataSource;
import lombok.extern.slf4j.Slf4j;
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;

@Slf4j
@Configuration
public class DataSourceConfig {
    /**
     * 数据源1
     *
     * @return DataSource
     */
    @Primary
    @Bean(name = "primaryDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.primary")
    public DataSource primaryDataSource() {
        return DataSourceBuilder.create().build();
    }

    /**
     * 数据源2
     *
     * @return DataSource
     */
    @Bean(name = "secondDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.second")
    public DataSource secondDataSource() {
        return DataSourceBuilder.create().build();
    }

    /**
     * 数据源3
     *
     * @return DataSource
     */
    @Bean(name = "thirdDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.third")
    public DataSource thirdDataSource() {
        return DataSourceBuilder.create().build();
    }

    /**
     * 显示数据库连接池信息
     *
     * @param dataSource 连接池信息
     */
    static void logDS(DataSource dataSource) {
        HikariDataSource hds = (HikariDataSource) dataSource;
        String info = "\n\n\tHikariCP连接池配置\n\t连接池名称：" +
                hds.getPoolName() +
                "\n\t最小空闲连接数：" +
                hds.getMinimumIdle() +
                "\n\t最大连接数：" +
                hds.getMaximumPoolSize() +
                "\n\t连接超时时间：" +
                hds.getConnectionTimeout() +
                "ms\n\t空闲连接超时时间：" +
                hds.getIdleTimeout() +
                "ms\n\t连接最长生命周期：" +
                hds.getMaxLifetime() +
                "ms\n";
        log.info(info);
    }
}

4、新建各数据源配置类

PrimaryDataSourceConfig.java

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.orm.jpa.HibernateSettings;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.boot.autoconfigure.orm.jpa.JpaProperties;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import javax.persistence.EntityManager;
import javax.sql.DataSource;
import java.util.Map;

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef = "entityManagerFactoryPrimary",
        transactionManagerRef = "transactionManagerPrimary",
        basePackages = {"*************.repository.primary"})  // repository层位置
public class PrimaryDataSourceConfig {
    @Autowired
    @Qualifier("primaryDataSource")
    private DataSource primaryDataSource;


    @Autowired
    private JpaProperties jpaProperties;

    @Primary
    @Bean(name = "entityManagerPrimary")
    public EntityManager entityManager(EntityManagerFactoryBuilder builder) {
        return entityManagerFactoryPrimary(builder).getObject().createEntityManager();
    }

    @Primary
    @Bean(name = "entityManagerFactoryPrimary")
    public LocalContainerEntityManagerFactoryBean entityManagerFactoryPrimary(EntityManagerFactoryBuilder builder) {
        DataSourceConfig.logDS(primaryDataSource);
        return builder
                .dataSource(primaryDataSource)
                .properties(getVendorProperties())
                .packages("*************.entity.primary")  // 设置实体类所在位置
                .persistenceUnit("primaryPersistenceUnit")
                .build();
    }

    /**
     * hibernate配置
     *
     * @return
     */
    private Map<String, Object> getVendorProperties() {
        Map<String, Object> ret = jpaProperties.getHibernateProperties(new HibernateSettings());
        ret.put("hibernate.dialect", "org.hibernate.dialect.OracleDialect");
        return ret;
    }

    @Primary
    @Bean(name = "transactionManagerPrimary")
    public PlatformTransactionManager transactionManagerPrimary(EntityManagerFactoryBuilder builder) {
        return new JpaTransactionManager(entityManagerFactoryPrimary(builder).getObject());
    }
}

注意，以下信息按照项目填写

1. repository层位置
2. 实体类所在位置
3. hibernate配置，数据源SQL方言

Hibernate SQL方言 (hibernate.dialect)

其他数据源按照上述配置即可。