網頁

2019/9/10

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的屬性 transactionManagerRefbasePackages的設定。

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類別FirstSecond

注意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類,分別為FirstRepositorySecondRepository。 注意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;
    
    ...

}


2 則留言:

  1. 請問您是如何知道多個db連線設定的?
    最近在學習一直卡住,然後要上網google其他人的提供的範例,想知道有甚麼訣竅或是如何看懂spring提供的文件Spring doc延伸到使用多個資料庫連線之類的。

    回覆刪除
  2. @Slang 我是先從網路上別人的教學文章知道如何配置多個db連線設定,然後再去Spring官方文件查找配置多個db連線的說明。

    想看懂Spring Docs文件的訣竅在於:
    第一,官方文件都很長一篇,因此要從中找到你要的資訊要善用Ctril + F的內容搜索以關鍵字去查詢。
    第二,要找對官方文件。這有時不容易從Google搜尋中找到,你必須直接去官方文件搜索。又Spring官方文件分為Spring、Spring Boot、Spring Data JPA及其他,本篇的多db連線設定是在Spring Boot文件找到的,而非Spring Data JPA文件。若你不確定的話可能相關文件都要搜一下,這件事確實不太容易。本篇文章的官方文件我放在文章最下方的第一個參考連結。

    回覆刪除