Spring Data JPA Spring Boot DataSource

Spring Boot + Spring Data JPA 配置多個 DataSource

黃紹溥 Kim Huang 2021/12/05 23:13:38
18230

環境準備
1. 建立多個不同的資料庫儲存空間
 

此例使用 PostgreSQL，在同一個 database (testdb) 下，建立三個 schema：demo_a, demo_b, demo_c

分別在底下建立 table (table_a, table_b, table_c) 並寫入測試資料

準備好資料庫的連線資訊稍後設定：jdbc:postgresql://127.0.0.1:5432/testdb
2. 建立 Spring Boot 專案
 

使用 Spring Initializr 建立，並加入必要的依賴：

  • Spring Data JPA
  • PostgreSQL Driver

使用 Spring Initializr 建立專案

將產生的專案引入 IDE (Eclipse)
專案引入 Eclipse

開始撰寫
1. 在 application.properties 中設定三組連線資訊
 

key 可自訂，但同一組的 prefix 需相同。
此例以 "a"、"b"、"c" 分別對應 "demo_a"、"demo_b"、"demo_c" 三個 schema
properties設定

  • 注意：預設的 "url" 須改寫為 "jdbc-url"，留待後續說明。
2. 建立 packages
 

不同連線的 entity, repository 不可放在相同的 package 下，可如下圖配置：
package配置
3. 依照 Spring Data JPA 規範，建立 entity 及 repository 類別
 

建立 table_a 的 entity 及 repository
table_a的entity及repository

TableA.java 
package com.thinkpower.multids.a.entity;

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

@Entity
@Table(name = "table_a")
public class TableA {

	@Id
	@Column(name = "id")
	private Integer id;

	@Column(name = "name")
	private String name;

	public Integer getId() {
		return id;
	}

	public void setId(Integer id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	@Override
	public String toString() {
		return "TableA [id=" + id + ", name=" + name + "]";
	}

}
TableADao.java 
package com.thinkpower.multids.a.repository;

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

import com.thinkpower.multids.a.entity.TableA;

@Repository
public interface TableADao extends JpaRepository<TableA, Integer> {

}

建立 table_b 的 entity 及 repository
table_b的entity及repository

TableB.java 
package com.thinkpower.multids.b.entity;

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

@Entity
@Table(name = "table_b")
public class TableB {

	@Id
	@Column(name = "id")
	private Integer id;

	@Column(name = "name")
	private String name;

	public Integer getId() {
		return id;
	}

	public void setId(Integer id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	@Override
	public String toString() {
		return "TableB [id=" + id + ", name=" + name + "]";
	}

}
TableBDao.java 
package com.thinkpower.multids.b.repository;

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

import com.thinkpower.multids.b.entity.TableB;

@Repository
public interface TableBDao extends JpaRepository<TableB, Integer> {

}

建立 table_c 的 entity 及 repository
table_c的entity及repository

TableC.java 
package com.thinkpower.multids.c.entity;

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

@Entity
@Table(name = "table_c")
public class TableC {

	@Id
	@Column(name = "id")
	private Integer id;

	@Column(name = "name")
	private String name;

	public Integer getId() {
		return id;
	}

	public void setId(Integer id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	@Override
	public String toString() {
		return "TableC [id=" + id + ", name=" + name + "]";
	}

}
TableCDao.java 
package com.thinkpower.multids.c.repository;

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

import com.thinkpower.multids.c.entity.TableC;

@Repository
public interface TableCDao extends JpaRepository<TableC, Integer> {

}
4. 透過 Java Config 方式自訂 DataSource，並為每個 DataSource 分別建立各自的 EntityManagerFactory, TransactionManager。
 

AConfig.java
AConfig.java

package com.thinkpower.multids.a.config;

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.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import com.thinkpower.multids.a.entity.TableA;
import com.thinkpower.multids.a.repository.TableADao;

@Configuration
@EnableJpaRepositories( //
	basePackageClasses = TableADao.class, //
	entityManagerFactoryRef = "aEntityManagerFactory", //
	transactionManagerRef = "aTransactionManager"
)
@EnableTransactionManagement
public class AConfig {

	@Bean("aDataSource")
	@Primary
	@ConfigurationProperties("app.datasource.a")
	public DataSource aDataSource() {
		return DataSourceBuilder.create().build();
	}

	@Bean("aEntityManagerFactory")
	@Primary
    public LocalContainerEntityManagerFactoryBean aEntityManagerFactory(
    		@Qualifier("aDataSource") DataSource aDataSource,
    		EntityManagerFactoryBuilder builder) {
        return builder //
        .dataSource(aDataSource) //
        .packages(TableA.class) //
        .persistenceUnit("aDs") //
        .build();
    }

	@Bean("aTransactionManager")
	@Primary
	public PlatformTransactionManager aTransactionManager(
	        @Qualifier("aEntityManagerFactory") LocalContainerEntityManagerFactoryBean aEntityManagerFactory) {
	    return new JpaTransactionManager(aEntityManagerFactory.getObject());
	}

}
  • 在 "a" 組的每個 Bean 加上 @Primary，是為了讓之後其他的自動配置功能，可以找到預設的一組連線，其他組不可加此標註。
  • 使用 @ConfigurationProperties 可以自動幫 DataSource 注入屬性值，但由於 Spring Boot 預設的 DataSource 實作類別是 HikariDataSource，它沒有 url 屬性，只有 jdbcUrl，所以在 application.properties 中才需要將 "url" 置換成 "jdbc-url"。當然您也可以使用 DataSourceProperties 類別來避開這個問題。
  • 如果一個應用程式有多個 EntityManagerFactory，則其 persistenceUnit 應該要使用不同的名稱加以區別，此例為："aDs", "bDs", "cDs"。

BConfig.java
BConfig.java

package com.thinkpower.multids.b.config;

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.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import com.thinkpower.multids.b.entity.TableB;
import com.thinkpower.multids.b.repository.TableBDao;

@Configuration
@EnableJpaRepositories( //
	basePackageClasses = TableBDao.class, //
	entityManagerFactoryRef = "bEntityManagerFactory", //
	transactionManagerRef = "bTransactionManager"
)
@EnableTransactionManagement
public class BConfig {

	@Bean("bDataSource")
	@ConfigurationProperties("app.datasource.b")
	public DataSource bDataSource() {
		return DataSourceBuilder.create().build();
	}

	@Bean("bEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean bEntityManagerFactory(
    		@Qualifier("bDataSource") DataSource bDataSource,
    		EntityManagerFactoryBuilder builder) {
        return builder //
        .dataSource(bDataSource) //
        .packages(TableB.class) //
        .persistenceUnit("bDs") //
        .build();
    }

	@Bean("bTransactionManager")
	public PlatformTransactionManager bTransactionManager(
		@Qualifier("bEntityManagerFactory") LocalContainerEntityManagerFactoryBean bEntityManagerFactory
	) {
		return new JpaTransactionManager(bEntityManagerFactory.getObject());
	}

}

CConfig.java
CConfig.java

package com.thinkpower.multids.c.config;

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.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import com.thinkpower.multids.c.entity.TableC;
import com.thinkpower.multids.c.repository.TableCDao;

@Configuration
@EnableJpaRepositories( //
	basePackageClasses = TableCDao.class, //
	entityManagerFactoryRef = "cEntityManagerFactory", //
	transactionManagerRef = "cTransactionManager"
)
@EnableTransactionManagement
public class CConfig {

	@Bean("cDataSource")
	@ConfigurationProperties("app.datasource.c")
	public DataSource cDataSource() {
		return DataSourceBuilder.create().build();
	}

	@Bean("cEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean cEntityManagerFactory(
    		@Qualifier("cDataSource") DataSource cDataSource,
    		EntityManagerFactoryBuilder builder) {
        return builder //
        .dataSource(cDataSource) //
        .packages(TableC.class) //
        .persistenceUnit("cDs") //
        .build();
    }

	@Bean("cTransactionManager")
	public PlatformTransactionManager cTransactionManager(
		@Qualifier("cEntityManagerFactory") LocalContainerEntityManagerFactoryBean cEntityManagerFactory
	) {
		return new JpaTransactionManager(cEntityManagerFactory.getObject());
	}

}
5. 建立 Service 類別以利測試
 

AService.java
AService.java

package com.thinkpower.multids.a.service;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.thinkpower.multids.a.entity.TableA;
import com.thinkpower.multids.a.repository.TableADao;

@Service
public class AService {

	@Autowired
	private TableADao tableADao;

	public void findAll() {
		System.out.println("\n使用 Spring Data JPA 衍生方法查詢 table_a 的資料:");
		this.tableADao.findAll().forEach(System.out::println);
	}

	@Transactional("aTransactionManager")
	public TableA testTransactional(Integer id, String name) {
		// 刪除全部
		this.tableADao.deleteAll();
		
		// 寫入一筆
		TableA a = new TableA();
		a.setId(id);
		a.setName(name);
		a = this.tableADao.save(a);
		
		// 故意埋入 RuntimeException: ArrayIndexOutOfBoundsException
		System.out.println(new String[] {}[1]);
		
		return a;
	}

}
  • findAll()：用以測試一般查詢。
  • testTransactional()：用以測試交易失敗時是否會 rollback (TransactionManager 是否設定正確)。
  • @Transactional：
    • 注意要使用 org.springframework.transaction.annotation.Transactional。
    • 必須把此 Transactional method 所使用的 TransactionalManager Bean 名稱填入 transactionManager 屬性。在此方法中主要操作的是 TableADao，而在 AConfig 中有設定對應的 TransactionalManager Bean 名稱為 "aTransactionManager"。
    • 預設只有捕捉到 RuntimeException / Error 及其子類別才會觸發 rollback，所以此例拋出 ArrayIndexOutOfBoundsException 做測試。可利用 "rollbackFor" 及相關屬性另外調整。

BService.java
BService.java

package com.thinkpower.multids.b.service;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.thinkpower.multids.b.entity.TableB;
import com.thinkpower.multids.b.repository.TableBDao;

@Service
public class BService {

	@Autowired
	private TableBDao tableBDao;

	public void findAll() {
		System.out.println("\n使用 Spring Data JPA 衍生方法查詢 table_b 的資料:");
		this.tableBDao.findAll().forEach(System.out::println);
	}

	@Transactional("bTransactionManager")
	public TableB testTransactional(Integer id, String name) {
		// 刪除全部
		this.tableBDao.deleteAll();
		
		// 寫入一筆
		TableB b = new TableB();
		b.setId(id);
		b.setName(name);
		b = this.tableBDao.save(b);
		
		// 故意埋入 RuntimeException: ArrayIndexOutOfBoundsException
		System.out.println(new String[] {}[1]);
		
		return b;
	}

}

CService.java
CService.java

package com.thinkpower.multids.c.service;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.thinkpower.multids.c.entity.TableC;
import com.thinkpower.multids.c.repository.TableCDao;

@Service
public class CService {

	@Autowired
	private TableCDao tableCDao;

	public void findAll() {
		System.out.println("\n使用 Spring Data JPA 衍生方法查詢 table_c 的資料:");
		this.tableCDao.findAll().forEach(System.out::println);
	}

	@Transactional("cTransactionManager")
	public TableC testTransactional(Integer id, String name) {
		// 刪除全部
		this.tableCDao.deleteAll();
		
		// 寫入一筆
		TableC c = new TableC();
		c.setId(id);
		c.setName(name);
		c = this.tableCDao.save(c);
		
		// 故意埋入 RuntimeException: ArrayIndexOutOfBoundsException
		System.out.println(new String[] {}[1]);
		
		return c;
	}

}
6. 進行測試
 

利用 CommandLineRunner (覆寫 run() 方法) 在應用程式啟動後，立即執行測試

MultidsApplication.java 
package com.thinkpower.multids;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

import com.thinkpower.multids.a.service.AService;
import com.thinkpower.multids.b.service.BService;
import com.thinkpower.multids.c.service.CService;

@SpringBootApplication
public class MultidsApplication implements CommandLineRunner {

	@Autowired
	private AService aService;

	@Autowired
	private BService bService;

	@Autowired
	private CService cService;

	@Override
	public void run(String... args) throws Exception {
		this.aService.findAll();
		try {
			this.aService.testTransactional(4, "name_4");
		} catch (RuntimeException e) {
			System.err.println(e);
		} finally {
			this.aService.findAll();
		}

		// =====================================================

		this.bService.findAll();
		try {
			this.bService.testTransactional(5, "name_5");
		} catch (RuntimeException e) {
			System.err.println(e);
		} finally {
			this.bService.findAll();
		}

		// =====================================================

		this.cService.findAll();
		try {
			this.cService.testTransactional(6, "name_6");
		} catch (RuntimeException e) {
			System.err.println(e);
		} finally {
			this.cService.findAll();
		}
	}

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

}

執行結果：
測試結果
從結果可以看出，findAll() 查詢成功，且 testTransaction() 執行後拋出 ArrayIndexOutOfBoundsException，再次查詢的資料並未異動，表示 rollback 成功。

額外加碼

以下說明當客製實作 Spring JPA Repositories 時，如何使用多個連線設定。
1. 依照 Spring Data JPA 的規範，擴增 TableADao.java，並建立客製實作類別 TableADaoImpl.java
 

改寫 TableADao.java，增加 queryAll() 方法

package com.thinkpower.multids.a.repository;

import java.util.List;

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

import com.thinkpower.multids.a.entity.TableA;

@Repository
public interface TableADao extends JpaRepository<TableA, Integer> {

	public List<TableA> queryAll();

}

新增 TableADaoImpl.java，實作 queryAll()
TableADaoImpl.java

package com.thinkpower.multids.a.repository;

import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.TypedQuery;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.jpa.repository.JpaContext;

import com.thinkpower.multids.a.entity.TableA;

public class TableADaoImpl {

	private final EntityManager em;

	@Autowired
	public TableADaoImpl(JpaContext context) {
		this.em = context.getEntityManagerByManagedType(TableA.class);
	}

	public List<TableA> queryAll() {
		String jpql = "SELECT a FROM TableA a";
		TypedQuery<TableA> query = this.em.createQuery(jpql, TableA.class);
		return query.getResultList();
	}

}
  • Spring JPA 會自動掃描 interface 所在的 package，且 Class 名稱與其相應、並以 "Impl" 結尾，作為實作類別。因此 TableADaoImpl.java 才不用真的 implements TableADao.java，但還是要實作所有 interface 的方法。
  • 呼叫 JpaContext.getEntityManagerByManagedType() 時，需傳入此 Repository 對應的 Entity 類別，以取得正確的 EntityManager。

TableBDao.java

package com.thinkpower.multids.b.repository;

import java.util.List;

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

import com.thinkpower.multids.b.entity.TableB;

@Repository
public interface TableBDao extends JpaRepository<TableB, Integer> {

	public List<TableB> queryAll();

}

TableBDaoImpl.java
TableBDaoImpl.java

package com.thinkpower.multids.b.repository;

import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.TypedQuery;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.jpa.repository.JpaContext;

import com.thinkpower.multids.b.entity.TableB;

public class TableBDaoImpl {

	private final EntityManager em;

	@Autowired
	public TableBDaoImpl(JpaContext context) {
		this.em = context.getEntityManagerByManagedType(TableB.class);
	}

	public List queryAll() {
		String jpql = "SELECT b FROM TableB b";
		TypedQuery query = this.em.createQuery(jpql, TableB.class);
		return query.getResultList();
	}

}

TableCDao.java

package com.thinkpower.multids.c.repository;

import java.util.List;

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

import com.thinkpower.multids.c.entity.TableC;

@Repository
public interface TableCDao extends JpaRepository<TableC, Integer> {

	public List<TableC> queryAll();

}

TableCDaoImpl.java
TableCDaoImpl.java

package com.thinkpower.multids.c.repository;

import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.TypedQuery;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.jpa.repository.JpaContext;

import com.thinkpower.multids.c.entity.TableC;

public class TableCDaoImpl {

	private final EntityManager em;

	@Autowired
	public TableCDaoImpl(JpaContext context) {
		this.em = context.getEntityManagerByManagedType(TableC.class);
	}

	public List queryAll() {
		String jpql = "SELECT c FROM TableC c";
		TypedQuery query = this.em.createQuery(jpql, TableC.class);
		return query.getResultList();
	}

}
2. 改寫 Service 類別以利測試
 

在 AService.java, BService.java 以及 CService.java 各自增加 queryAll() 方法，並呼叫客製自訂的 repository 方法：queryAll()
AService_mod.java

  • BService.java, CService.java 依此類推，省略程式碼範例。
3. 改寫 MultidsApplication.java，修改測試流程
 

在呼叫 testTransactional() 後的 finally 區塊，改呼叫 queryAll() 來驗證資料

package com.thinkpower.multids;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

import com.thinkpower.multids.a.service.AService;
import com.thinkpower.multids.b.service.BService;
import com.thinkpower.multids.c.service.CService;

@SpringBootApplication
public class MultidsApplication implements CommandLineRunner {

	@Autowired
	private AService aService;

	@Autowired
	private BService bService;

	@Autowired
	private CService cService;

	@Override
	public void run(String... args) throws Exception {
		this.aService.findAll();
		try {
			this.aService.testTransactional(4, "name_4");
		} catch (RuntimeException e) {
			System.err.println(e);
		} finally {
			// ※ 改呼叫客製自訂的方法
			this.aService.queryAll();
		}

		// =====================================================

		this.bService.findAll();
		try {
			this.bService.testTransactional(5, "name_5");
		} catch (RuntimeException e) {
			System.err.println(e);
		} finally {
			// ※ 改呼叫客製自訂的方法
			this.bService.queryAll();
		}

		// =====================================================

		this.cService.findAll();
		try {
			this.cService.testTransactional(6, "name_6");
		} catch (RuntimeException e) {
			System.err.println(e);
		} finally {
			// ※ 改呼叫客製自訂的方法
			this.cService.queryAll();
		}
	}

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

}
4. 執行並查看測試結果
 

測試結果_2
拋出 ArrayIndexOutOfBoundsException 之後確實能執行 queryAll() 查出所有資料。

參考文章
黃紹溥 Kim Huang
相關文章
在 Spring Boot 啟用 mutual TLS(mTLS)
Thymeleaf & 資料傳遞
Spring Cloud -- Hystrix圖形化Dashboard搭建及實作
Spring Cloud -- Hystrix服務熔斷
詳細瞭解 Spring Transaction 的 Propagation
Spring Boot + Spring Data JPA 配置多個 DataSource
最新文章
5步驟教你自動產出WORD文章目錄及圖表目錄
Unit testing LLM models - Lessons from Vertex AI
IntelliJ IDEA 配置 Maven 內網開發環境
Mermaid 語法匯出圖檔格式操作說明 - Mermaid Live Editor, Notion, Draw IO
Project IDX 線上IDE開發程式
使用 Bootify 快速生成 Spring Boot 程式架構