SpringBoot+JPA+MySql+SqlServer多數(shù)據(jù)源配置

前言：我們在單數(shù)據(jù)源的情況下，springboot配置是很簡單的，只需要導(dǎo)入相對應(yīng)的
包以及在配置文件配置連接參數(shù)即可。但是往往隨著業(yè)務(wù)的發(fā)展，我們通常會進(jìn)行數(shù)
據(jù)庫拆分以及比較老的系統(tǒng)需要集成時使用的數(shù)據(jù)庫不一樣，這個時候就需要配置多數(shù)據(jù)源的配置。參考網(wǎng)上各文章整理后配置好的多數(shù)據(jù)源。

一、項(xiàng)目依賴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配置文件配置

同時連接兩個數(shù)據(jù)庫，配置如下

  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數(shù)據(jù)源配置
      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數(shù)據(jù)源配置
    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

三、配置雙數(shù)據(jù)源主要代碼

1.創(chuàng)建主從數(shù)據(jù)源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 2019/11/12 14:43
* 配置主數(shù)據(jù)源
*/

@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.主數(shù)據(jù)源的配置

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 2019/11/12 14:46
 * 主數(shù)據(jù)源的配置
 */

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

    @Autowired
    @Qualifier("primaryDataSource")
    // 自動注入配置好的數(shù)據(jù)源
    private DataSource primaryDataSource;


    @Value("${spring.jpa.hibernate.main-dialect}")
    // 獲取對應(yīng)的數(shù)據(jù)庫方言
    private String primaryDialect;

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

        return builder
                //設(shè)置數(shù)據(jù)源
                .dataSource(primaryDataSource)
                //設(shè)置數(shù)據(jù)源屬性
                .properties(getVendorProperties(primaryDataSource))
                //設(shè)置實(shí)體類所在位置.掃描所有帶有 @Entity 注解的類
                .packages("com.eco.power.air.airquality.entityPrimary")
                // Spring會將EntityManagerFactory注入到Repository之中.有了 EntityManagerFactory之后,
                // Repository就能用它來創(chuàng)建 EntityManager 了,然后 EntityManager 就可以針對數(shù)據(jù)庫執(zhí)行操作
                .persistenceUnit("primaryPersistenceUnit")
                .build();

    }

    private Map<String, String> getVendorProperties(DataSource dataSource) {
        Map<String,String> map = new HashMap<>();
        // 設(shè)置對應(yīng)的數(shù)據(jù)庫方言
        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.從數(shù)據(jù)源的配置

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 2019/11/12 15:03
 * 從數(shù)據(jù)源的配置
 */

@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());
    }
}

完成了以上配置之后，
主數(shù)據(jù)源的實(shí)體位于:com.eco.power.air.airquality.entityPrimary
主數(shù)據(jù)源的數(shù)據(jù)訪問對象位于：com.eco.power.air.airquality.repositoryPrimary
第二數(shù)據(jù)源的實(shí)體位于： com.eco.power.air.airquality.entitySecondary
第二數(shù)據(jù)源的數(shù)據(jù)訪問接口位于：com.eco.power.air.airquality.repositoryPrimary
分別在這些package下創(chuàng)建各自的實(shí)體和數(shù)據(jù)訪問接口!
至此多數(shù)據(jù)源配置完成，此方法也適用于其他數(shù)據(jù)庫配置，只需修改application.yml中的數(shù)據(jù)源配置即可。

最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

友情鏈接更多精彩內(nèi)容