SpringBoot+JPA+MySql+SqlServer多数据源配置

前言：我们在单数据源的情况下，springboot配置是很简单的，只需要导入相对应的
包以及在配置文件配置连接参数即可。但是往往随着业务的发展，我们通常会进行数
据库拆分以及比较老的系统需要集成时使用的数据库不一样，这个时候就需要配置多数据源的配置。参考网上各文章整理后配置好的多数据源。

 

一、项目依赖pom.xml配置

  springboot版本为2.2.0,以下maven依赖包括sqlserver、mysql、jpa（按需求添加）

   <!--sqlserver配置-->
   <dependency>
        <groupId>com.microsoft.sqlserver</groupId>
        <artifactId>mssql-jdbc</artifactId>
        <scope>runtime</scope>
    </dependency>
    <!--mysql配置-->
     <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
    </dependency>
    <!--jpa配置-->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>

二、application.yml配置文件配置

同时连接两个数据库，配置如下

server:
  port: 8888
  tomcat:
    uri-encoding: utf-8
  servlet:
    context-path: /airQuality
    session:
      timeout: 30m

spring:
  jpa:
    database: MYSQL
    show-sql: true
    hibernate:
      ddl-auto: update
      second-dialect: org.hibernate.dialect.MySQL5Dialect
      main-dialect: org.hibernate.dialect.SQLServer2008Dialect


  datasource:
    second:
      #  mysql数据源配置
      driver-class-name: com.mysql.cj.jdbc.Driver
      jdbc-url: jdbc:mysql://localhost:3006/bigdata_ecology_integrated_management?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC
      username: root
      password: root
      database: mysql
      configuration:
        maximum-pool-size: 30
    dbcp2:
      max-idle: 10
      max-wait-millis: 10000
      min-idle: 5
      initial-size: 5

    #sqlserver数据源配置
    main:
      url: jdbc:sqlserver://localhost:11433;DatabaseName=AQI
      username: U_AQI
      password: powerdata@2019
      driverClassName: com.microsoft.sqlserver.jdbc.SQLServerDriver
      database: sql_server
      configuration:
      maximum-pool-size: 30

三、配置双数据源主要代码

1.创建主从数据源DataSourceConfig配置类

package com.eco.power.air.airquality.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 Wu Qilong
* @version 1.0
* @date 2020/06/05 14:43
* 配置主数据源
*/

@Configuration
public class DataSourceConfig {
 @Bean(name = "primaryDataSource")
 @Primary
 @Qualifier("primaryDataSource")
 @ConfigurationProperties(prefix = "datasource.main")
 public DataSource primaryDatasource() {
     return DataSourceBuilder.create().build();
 }

 @Bean(name = "secondaryDataSource")
 @Qualifier("secondaryDataSource")
 @ConfigurationProperties(prefix = "datasource.second")
 public DataSource secondaryDataSource() {
     return DataSourceBuilder.create().build();
 }
}

2.主数据源的配置

package com.eco.power.air.airquality.config;

import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.autoconfigure.orm.jpa.JpaProperties;
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.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;

/**
 * @author Wu Qilong
 * @version 1.0
 * @date 2020/06/05 14:46
 * 主数据源的配置
 */

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef = "entityManagerFactoryPrimary",//配置连接工厂 entityManagerFactory
        transactionManagerRef = "transactionManagerPrimary", //配置 事物管理器  transactionManager
        basePackages = {"com.eco.power.air.airquality.repositoryPrimary"}//设置持久层所在位置
)
public class PrimaryConfig {
    @Autowired
    private JpaProperties jpaProperties;

    @Autowired
    @Qualifier("primaryDataSource")
    // 自动注入配置好的数据源
    private DataSource primaryDataSource;


    @Value("${spring.jpa.hibernate.main-dialect}")
    // 获取对应的数据库方言
    private String primaryDialect;

    /**
     *
     * @param builder
     * @return
     */
    @Bean(name = "entityManagerFactoryPrimary")
    @Primary
    public LocalContainerEntityManagerFactoryBean entityManagerFactoryPrimary(EntityManagerFactoryBuilder builder) {

        return builder
                //设置数据源
                .dataSource(primaryDataSource)
                //设置数据源属性
                .properties(getVendorProperties(primaryDataSource))
                //设置实体类所在位置.扫描所有带有 @Entity 注解的类
                .packages("com.eco.power.air.airquality.entityPrimary")
                // Spring会将EntityManagerFactory注入到Repository之中.有了 EntityManagerFactory之后,
                // Repository就能用它来创建 EntityManager 了,然后 EntityManager 就可以针对数据库执行操作
                .persistenceUnit("primaryPersistenceUnit")
                .build();

    }

    private Map<String, String> getVendorProperties(DataSource dataSource) {
        Map<String,String> map = new HashMap<>();
        // 设置对应的数据库方言
        map.put("hibernate.dialect",primaryDialect);
        jpaProperties.setProperties(map);
        return jpaProperties.getProperties();
    }

    /**
     * 配置事物管理器
     *
     * @param builder
     * @return
     */
    @Bean(name = "transactionManagerPrimary")
    @Primary
    PlatformTransactionManager transactionManagerPrimary(EntityManagerFactoryBuilder builder) {
        return new JpaTransactionManager(entityManagerFactoryPrimary(builder).getObject());
    }
}

3.从数据源的配置

package com.eco.power.air.airquality.config;

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


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

/**
 * @author Wu Qilong
 * @version 1.0
 * @date 2020/05/ 15:03
 * 从数据源的配置
 */

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef="entityManagerFactorySecondary",
        transactionManagerRef="transactionManagerSecondary",
        basePackages= { "com.eco.power.air.airquality.repositorySecondary" })
public class SecondaryConfig {

    @Autowired
    private JpaProperties jpaProperties;

    @Autowired
    @Qualifier("secondaryDataSource")
    private DataSource secondaryDataSource;

    @Value("${spring.jpa.hibernate.second-dialect}")
    private String secondaryDialect;


    @Bean(name = "entityManagerSecondary")
    public EntityManager entityManager(EntityManagerFactoryBuilder builder) {
        return entityManagerFactorySecondary(builder).getObject().createEntityManager();
    }

    @Bean(name = "entityManagerFactorySecondary")
    public LocalContainerEntityManagerFactoryBean entityManagerFactorySecondary (EntityManagerFactoryBuilder builder) {
        return builder
                .dataSource(secondaryDataSource)
                .properties(getVendorProperties(secondaryDataSource))
                .packages("com.eco.power.air.airquality.entitySecondary")
                .persistenceUnit("secondaryPersistenceUnit")
                .build();
    }

    private Map<String, String> getVendorProperties(DataSource dataSource) {
        Map<String,String> map = new HashMap<>();
        map.put("hibernate.dialect",secondaryDialect);
        jpaProperties.setProperties(map);
        return jpaProperties.getProperties();
    }

    @Bean(name = "transactionManagerSecondary")
    PlatformTransactionManager transactionManagerSecondary(EntityManagerFactoryBuilder builder) {
        return new JpaTransactionManager(entityManagerFactorySecondary(builder).getObject());
    }
}

完成了以上配置之后，
主数据源的实体位于:com.eco.power.air.airquality.entityPrimary
主数据源的数据访问对象位于：com.eco.power.air.airquality.repositoryPrimary
第二数据源的实体位于： com.eco.power.air.airquality.entitySecondary
第二数据源的数据访问接口位于：com.eco.power.air.airquality.repositoryPrimary
分别在这些package下创建各自的实体和数据访问接口!
至此多数据源配置完成，此方法也适用于其他数据库配置，只需修改application.yml中的数据源配置即可。