Maxkit: Spring Boot 3 JPA multiple datasource

在一個 Spring Boot 3 project 同時連接到兩個 database，需要用兩個設定檔指定兩個 datasource，分別設定不同的 entityManager, transactonManager, jdbcTemplate。

application.yml

spring:
  application:
    name: project
  jpa:
    properties:
      hibernate:
        # dialect: org.hibernate.dialect.MySQLDialect
        dialect: org.hibernate.community.dialect.MySQLLegacyDialect

project:
  kokods:
    url: jdbc:mariadb://localhost:3306/koko
    username: root
    password: password
    schema: koko
    type: com.zaxxer.hikari.HikariDataSource
    hikari:
      connection-timeout: 30000 # milliseconds that a client will wait for a new connection from the pool  30 seconds
      minimum-idle: 1           # minimum number of idle connections
      maximum-pool-size: 100    # maximum number of connections
      idle-timeout: 600000      # maximum amount of time that a connection may sit idle in the pool of connections  10 mins
      max-lifetime: 1800000     # a connection can be pooled for before being destroyed  30 mins
      auto-commit: true
      connection-test-query: SELECT CURRENT_TIMESTAMP
  db2ds:
    url: jdbc:mariadb://localhost:3306/db2
    username: root
    password: password
    schema: db2
    type: com.zaxxer.hikari.HikariDataSource
    hikari:
      connection-timeout: 30000 # milliseconds that a client will wait for a new connection from the pool  30 seconds
      minimum-idle: 1           # minimum number of idle connections
      maximum-pool-size: 100    # maximum number of connections
      idle-timeout: 600000      # maximum amount of time that a connection may sit idle in the pool of connections  10 mins
      max-lifetime: 1800000     # a connection can be pooled for before being destroyed  30 mins
      auto-commit: true
      connection-test-query: SELECT CURRENT_TIMESTAMP

logging:
  level:
    com.zaxxer.hikari: TRACE
    com.zaxxer.hikari.HikariConfig: DEBUG

@Configuration設定

KoKoConfig.java

package tw.com.maxkit.koko.config;

import com.zaxxer.hikari.HikariDataSource;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.core.env.Environment;
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;

import javax.sql.DataSource;

@Configuration
//@PropertySource({"classpath:persistence-multiple-db.properties"})
@EnableJpaRepositories(
        basePackages = "tw.com.maxkit.koko.dao.jpa",
        entityManagerFactoryRef = "kokoEntityManagerFactory",
        transactionManagerRef = "kokoTransactionManager"
)
@EnableTransactionManagement
public class KoKoConfig {
    @Autowired
    private Environment env;

    @Primary
    @Bean("kokoDataSourceProperties")
    @ConfigurationProperties("project.kokods")
    public DataSourceProperties kokoDataSourceProperties() {
        return new DataSourceProperties();
    }

    @Primary
    @Bean("kokoDataSource")
    @Qualifier(value="kokoDataSourceProperties")
    @ConfigurationProperties(prefix = "project.kokods.hikari")
    public HikariDataSource kokoDataSource() {
        return kokoDataSourceProperties().initializeDataSourceBuilder().type(HikariDataSource.class).build();
    }

    // 如果不修改 Hikari 的參數，可直接使用這個 datasource，但要注意設定檔 url 要改為 jdbc-url
//    @Bean("lartelDataSource")
//    @ConfigurationProperties("lartel.kokods")
//    public DataSource lartelDataSource() {
//        return DataSourceBuilder.create().build();
//    }

    @Primary
    @Bean("kokoEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean kokoEntityManagerFactory(
            @Qualifier("kokoDataSource") DataSource kokoDataSource,
            EntityManagerFactoryBuilder builder) {
        return builder //
                .dataSource(kokoDataSource) //
                .packages("tw.com.maxkit.koko.data.entity") //
                .persistenceUnit("kokoDs") //
                .build();
    }

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

    @Primary
    @Bean("kokoJdbcTemplate")
    public JdbcTemplate kokoJdbcTemplate(
            @Qualifier("kokoDataSource") DataSource kokoDataSource) {
        return new JdbcTemplate(kokoDataSource);
    }
}

Db2Config.java

package tw.com.maxkit.db2.config;

import com.zaxxer.hikari.HikariDataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
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.core.env.Environment;
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;

import javax.sql.DataSource;

@Configuration
@EnableJpaRepositories(
        basePackages = "tw.com.maxkit.db2.dao.jpa",
        entityManagerFactoryRef = "lartelEntityManagerFactory",
        transactionManagerRef = "lartelTransactionManager"
)
@EnableTransactionManagement
public class LartelConfig {
    @Autowired
    private Environment env;

    // 這兩個 method 會套用修改 Hikari 的參數
    @Bean("db2DataSourceProperties")
    @ConfigurationProperties("project.db2ds")
    public DataSourceProperties db2DataSourceProperties() {
        return new DataSourceProperties();
    }

    @Bean("db2DataSource")
    @Qualifier(value="db2DataSourceProperties")
    @ConfigurationProperties(prefix = "project.db2ds.hikari")
    public HikariDataSource db2DataSource() {
        return db2DataSourceProperties().initializeDataSourceBuilder().type(HikariDataSource.class).build();
    }

    // 如果不修改 Hikari 的參數，可直接使用這個 datasource，但要注意設定檔 url 要改為 jdbc-url
//    @Bean("lartelDataSource")
//    @ConfigurationProperties("lartel.lartelds")
//    public DataSource lartelDataSource() {
//        return DataSourceBuilder.create().build();
//    }

    @Bean("db2EntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean db2EntityManagerFactory(
            @Qualifier("db2DataSource") DataSource db2DataSource,
            EntityManagerFactoryBuilder builder) {
        return builder //
                .dataSource(db2DataSource) //
                .packages("tw.com.maxkit.db2.data.entity") //
                .persistenceUnit("db2Ds") //
                .build();
    }

    @Bean("db2TransactionManager")
    public PlatformTransactionManager db2TransactionManager(
            @Qualifier("db2EntityManagerFactory") LocalContainerEntityManagerFactoryBean lartelEntityManagerFactory) {
        return new JpaTransactionManager(db2EntityManagerFactory.getObject());
    }

    @Bean("db2JdbcTemplate")
    public JdbcTemplate db2JdbcTemplate(
            @Qualifier("db2DataSource") DataSource db2DataSource) {
        return new JdbcTemplate(db2DataSource);
    }
}

第一個 datasource 部分的 DAO，另一個是類似的作法

package tw.com.maxkit.koko.dao.jpa;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;
import tw.com.maxkit.koko.data.entity.Uservo;

import java.util.List;

@Repository
public interface UservoDAO extends JpaRepository<Uservo, Long> {
    public List<Uservo> queryAll();
}

DAO 的 implementation

package tw.com.maxkit.koko.dao.jpa;

import jakarta.persistence.EntityManager;
import jakarta.persistence.TypedQuery;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.jpa.repository.JpaContext;
import org.springframework.stereotype.Component;
import tw.com.maxkit.koko.data.entity.Uservo;

import java.util.List;

@Component
public class UservoDAOImpl {
    private final EntityManager em;

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

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

Service

package tw.com.maxkit.koko.service;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import tw.com.maxkit.koko.dao.jpa.UservoDAO;
import tw.com.maxkit.koko.data.entity.Uservo;

@Service
public class UservoService {

    @Autowired
    private UservoDAO uservoDAO;

    public void findAll() {
        System.out.println("\n使用 Spring Data JPA 衍生方法查詢 uservo 的資料:");
//        this.uservoRepository.findAll().forEach(System.out::println);
        this.uservoDAO.findAll().forEach(
                uservo -> System.out.println("uservoseq="+uservo.getUservoseq()+", userid="+uservo.getUserid())
        );
    }

    @Transactional("kokoTransactionManager")
    public Uservo testTransactional(String userid, String username) {
        // 刪除全部
//        this.uservoRepository.deleteAll();

        // 寫入一筆
        Uservo a = new Uservo();
        a.setUserid(userid);
        a.setUsername(username);
        a = this.uservoDAO.save(a);

        // 故意埋入 RuntimeException: ArrayIndexOutOfBoundsException
        System.out.println(new String[] {}[1]);

        return a;
    }

    public void queryAll() {
        System.out.println("\n使用 queryAll 查詢 uservo 的資料:");
//        this.uservoRepository.findAll().forEach(System.out::println);
        this.uservoDAO.queryAll().forEach(
                uservo -> System.out.println("uservoseq="+uservo.getUservoseq()+", userid="+uservo.getUserid())
        );
    }
}

References

Spring Boot + Spring Data JPA 配置多個 DataSource

2025/08/25

Spring Boot 3 JPA multiple datasource

References

沒有留言:

張貼留言