在一個 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()) ); } }
