Spring Data JPA JdbcTemplate 多資料庫來源配置範例 multiple datasource configuration example

Spring Boot，Spring Data JPA，JdbcTemplate多資料庫來源(multiple database datasource)配置範例。

SpringBootApplication類如下。

package com.abc.demo;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class DemoApplication {

    public static void main(String[] args) {
        SpringApplication.run(DemoApplication.class, args);
    }
    
}

在Spring Boot的application.properties設定多個資料庫的連線位置及登入帳密。 下面設定了兩個資料源，皆為Oracle資料庫，分別為First與Second資料庫。

application.properties

# Oracle Database settings
spring.jpa.database-platform=org.hibernate.dialect.Oracle10gDialect
spring.datasource.driver-class-name=oracle.jdbc.OracleDriver

# First Oracle Database Settings - 第一個資料源
first.datasource.jdbc-url=jdbc:oracle:thin:@localhost:1521:<FIRST_SERVICE_NAME>
first.datasource.username=<username>
first.datasource.password=<password>

# Second Oracle Database Settings - 第二個資料源
second.datasource.jdbc-url=jdbc:oracle:thin:@localhost:1521:<SECOND_SERVICE_NAME>
second.datasource.username=<username>
second.datasource.password=<password>

# 查詢時在Console顯示SQL語法
spring.jpa.show-sql=false

在Spring Boot新增下面兩個datasource配置類別。
FirstDataSourceConfig對應First資料源 ；
SecondDataSourceConfig對應Second資料源。

在設定多個資料庫來源的配置時，一定要有一個datasource掛上@Primary。

注意@EnableJpaRepositories的屬性 transactionManagerRef與 basePackages的設定。

EntityManagerFactoryBuilder.Builder.packages()的參數為要掃描的Entity類所在的package名稱，所以把不同datasource的Entity類分開放在不同的package下。

FirstDataSourceConfig

package com.abc.demo.config;

import javax.persistence.EntityManagerFactory;
import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
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.jdbc.core.JdbcTemplate;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

@EnableTransactionManagement
@EnableJpaRepositories(entityManagerFactoryRef = "firstEntityManagerFactory",
        transactionManagerRef = "firstTransactionManager",
        basePackages = { "com.abc.demo.model.first.repository" })
@Configuration
public class FirstDataSourceConfig {
    @Primary
    @ConfigurationProperties(prefix = "first.datasource")
    @Bean(name = "firstDataSource")
    public DataSource firstDataSource () {
        return DataSourceBuilder.create().build();
    }

    @Primary
    @Bean(name = "firstEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean firstEntityManagerFactory (
            EntityManagerFactoryBuilder builder,
            @Qualifier("firstDataSource") DataSource dataSource) {
        return builder.dataSource(dataSource)
                .packages("com.abc.demo.model.first.entity")
                .persistenceUnit("first").build();
    }

    @Primary
    @Bean(name = "firstTransactionManager")
    public PlatformTransactionManager firstTransactionManager (
            @Qualifier("firstEntityManagerFactory") EntityManagerFactory entityManagerFactory) {
        return new JpaTransactionManager(entityManagerFactory);
    }

    // First Datasource JdbcTemplate
    @Bean(name = "firstJdbcTemplate")
    public JdbcTemplate firstTemplate(@Qualifier("firstDataSource") DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }
}

SecondDataSourceConfig

package com.abc.demo.config;

import javax.persistence.EntityManagerFactory;
import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

@EnableTransactionManagement
@EnableJpaRepositories(entityManagerFactoryRef = "secondEntityManagerFactory",
        transactionManagerRef = "secondTransactionManager",
        basePackages = { "com.abc.demo.model.second.repository" })
@Configuration
public class SecondDataSourceConfig {

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

    @Bean(name = "secondEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean secondEntityManagerFactory (
            EntityManagerFactoryBuilder builder,
            @Qualifier("secondDataSource") DataSource dataSource) {
        return builder.dataSource(dataSource)
                .packages("com.abc.demo.model.second.entity")
                .persistenceUnit("second").build();
    }

    @Bean(name = "secondTransactionManager")
    public PlatformTransactionManager secondTransactionManager (
            @Qualifier("secondEntityManagerFactory") EntityManagerFactory entityManagerFactory) {
        return new JpaTransactionManager(entityManagerFactory);
    }

    // Second Datasource JdbcTemplate
    @Bean(name = "secondJdbcTemplate")
    public JdbcTemplate secondTemplate(@Qualifier("secondDataSource") DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }

}

Entity類別First及Second。

注意Entity類的package的名稱與datasource類別中EntityManagerFactoryBuilder.Builder.packages()的參數對應的package名稱。

First

package com.abc.demo.model.first.entity;

import javax.persistence.Id;  
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Table;
import javax.persistence.GeneratedValue;

@Entity
@Table(name = "FIRST")
public class First {
    
    @Id
    @GeneratedValue
    @Column(name = "ID")
    private Long Id;
    
    @Column(name = "COLUMN_ONE")
    private String columnOne;

    public First() {}

    public First(String columnOne) {
        this.columnOne = columnOne;
    }

}

Second

package com.abc.demo.model.second.entity;

import javax.persistence.Id;  
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Table;
import javax.persistence.GeneratedValue;

@Entity
@Table(name = "SECOND")
public class Second {
    
    @Id
    @GeneratedValue
    @Column(name = "ID")
    private Long Id;
    
    @Column(name = "COLUMN_ONE")
    private String columnOne;

    public Second() {}

    public Second(String columnOne) {
        this.columnOne = columnOne;
    }

}

Repository類，分別為FirstRepository及SecondRepository。 注意package名稱與datasource類中@EnableJpaRepositories的屬性 basePackages的對應。

FirstRepository

package com.abc.demo.model.first.repository;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface FirstRepository extends JpaRepository<First, Long> {
    ...
}

SecondRepository

package com.abc.demo.model.second.repository;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface SecondRepository extends JpaRepository<Second, Long> {
    ...
}

到此就完成了Spring Data JPA多資料庫來源的設定。

如果文章對你有幫助的話還幫忙點個廣告支持，感恩。

在DAO層DemoDao取得第一及第二資料源的JdbcTemplate如下。

DemoDao

package com.abc.demo.model.dao;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

@Repository
public class DemoDao {
    
    @Autowired
    @Qualifier("firstJdbcTemplate")
    private JdbcTemplate firstJdbcTemplate;
    
    @Autowired
    @Qualifier("secondJdbcTemplate")
    private JdbcTemplate secondJdbcTemplate;
    
    ...

}

• Configure Two DataSources
• Using multiple datasources with Spring Boot and Spring Data
• Spring Boot 2 + Spring Data JPA + MySQL 8 簡單範例
• Eclipse Gradle + Spring Boot 2 + Spring Data JPA + Oracle 範例
• Spring Boot datasource config jdbcUrl is required with driverClassName錯誤