Spring Boot JPA – Multiple Databases and Oracle Database Link

在 Spring Boot 在 Eclipse 的安裝與設定 好後，第一個會要求作的設定是：設定一個資料庫，在 Spring Boot 配置 Maria Database 與交易控制 ( Transaction ) 有說明該如何建立。但在 MariaDB 是使用 MySQL 的套件。若要使用 MariaDB 原生套件，可以參考下方設定。

# File: build.gradle 
implementation 'org.mariadb.jdbc:mariadb-java-client:2.1.2'

# File: application.properties 
spring.datasource.url=jdbc:mariadb://localhost:3306/springbootdb
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driver-class-name=org.mariadb.jdbc.Driver

這裡示範第二個資料庫若為 Oracle Database 時，應該要注意什麼事。

Oracle Database 設定

#File: build.gradle
implementation("com.oracle.database.jdbc:ojdbc8-production:19.7.0.0")


#File: application.properties

# =================================
# Multi Database
# =================================
# MariaDB
db-apps.datasource.jdbcurl=jdbc:mysql://${MYSQL_HOST:localhost}:3306/db_example?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC
db-apps.datasource.username=springuser
db-apps.datasource.password=ThePassword
db-apps.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

# OracleDB
db-oracle.datasource.url=jdbc:oracle:thin:@//localhost:1521/sid
db-oracle.datasource.jdbcUrl=jdbc:oracle:thin:@//localhost:1521/sid
db-oracle.datasource.username=oracleUser
db-oracle.datasource.password=userPassword
db-oracle.datasource.driver-class-name=oracle.jdbc.OracleDriver

在 DbMariaConfig.java

要加入 @Primary

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef = "emfApps",
        basePackages = {"com.apps.db.maria.repository","com.apps.service"}
)
public class DbMariaConfig {
 private final Logger logger = LoggerFactory.getLogger(this.getClass());

 @Primary
 @Bean(name = "dsApps")
    @ConfigurationProperties(prefix = "db-apps.datasource")
    public DataSource dataSource() {
        return DataSourceBuilder.create().build();
    }
 
 @Primary
    @Bean(name = "emfApps")
    public LocalContainerEntityManagerFactoryBean entityManagerFactory() {

        HibernateJpaVendorAdapter jpaVendorAdapter = new HibernateJpaVendorAdapter();

        LocalContainerEntityManagerFactoryBean factoryBean = new LocalContainerEntityManagerFactoryBean();
        factoryBean.setDataSource(dataSource());
        factoryBean.setJpaVendorAdapter(jpaVendorAdapter);
        factoryBean.setJpaProperties(hibernateProperties());

        factoryBean.setPackagesToScan("com.apps.db.maria.model");
        factoryBean.setPersistenceUnitName("puApps");

        return factoryBean;
    }
 
 @Primary
    @Bean(name = "transactionManager")
    public PlatformTransactionManager transactionManager( @Qualifier("emfApps") EntityManagerFactory entityManagerFactory) {
        return new JpaTransactionManager(entityManagerFactory);
    }
    
    private Properties hibernateProperties() {
        final Properties hibernateProperties = new Properties();
        hibernateProperties.setProperty("hibernate.dialect", "org.hibernate.dialect.MySQL5Dialect");
        return hibernateProperties;
    }
}

在 DbOracleConfig.java

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef = "emfOracle",
        transactionManagerRef = "tmOracle",
        basePackages = {"com.apps.oracle.repository","com.apps.oracle.service"}
)
public class DbOracleConfig {
 private final Logger logger = LoggerFactory.getLogger(this.getClass());

 @Bean(name = "dsOracle")
    @ConfigurationProperties(prefix = "db-oracle-uat.datasource")
    public DataSource dataSource() {
        return DataSourceBuilder.create().build();
    }
 
    @Bean(name = "emfOracle")
    public LocalContainerEntityManagerFactoryBean entityManagerFactory() {

        HibernateJpaVendorAdapter jpaVendorAdapter = new HibernateJpaVendorAdapter();

        LocalContainerEntityManagerFactoryBean factoryBean = new LocalContainerEntityManagerFactoryBean();
        factoryBean.setDataSource(dataSource());
        factoryBean.setJpaVendorAdapter(jpaVendorAdapter);
        factoryBean.setJpaProperties(hibernateProperties());

        factoryBean.setPackagesToScan("com.apps.db.oracle.model");
        factoryBean.setPersistenceUnitName("puOracle");

        return factoryBean;
    }
 
    @Bean(name = "tmOracle")
    public PlatformTransactionManager transactionManager( @Qualifier("emfOracle") EntityManagerFactory entityManagerFactory) {
        return new JpaTransactionManager(entityManagerFactory);
    }
    
    private Properties hibernateProperties() {
        final Properties hibernateProperties = new Properties();
        hibernateProperties.setProperty("hibernate.dialect", "org.hibernate.dialect.Oracle10gDialect");        
        return hibernateProperties;
    }

}

以上的設定再搭配 Spring Boot 配置 Maria Database 與交易控制 ( Transaction ) 的說明，原則上就沒什麼問題了。

Oracle DB Link

若在作業上還有需要連接第三個 Oracle 資料庫，除了可以再加入一個類似 DbOracleConfig.java 的方式以外。遦可以運用 Oracle DB Link 的方式來連接，

Login and run below SQL to create a database link

CREATE <PUBLIC> DATABASE LINK <DB_LINK> CONNECT TO <DATABASE_USER> IDENTIFIED BY <DATABASE_PASSWORD> USING '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = <DATABASE HOSTNAME>)(PORT = <PORT>)))(CONNECT_DATA = (SERVICE_NAME = <SERVICE NAME>)))';

Attached Oracle document:   https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_5005.htm

範例:

建立一個名為 remote 的 DB link , 並連到 service name 為 remote 的 Oracle Database.

#The following statement defines a shared public database link named remote that refers to the database specified by the service name remote:

CREATE PUBLIC DATABASE LINK remote 
   USING 'remote';

在 model 裡的設定如下，記得在 class 中 annotation 的 @Table 中要標記 schema 及加入 <REMOTE TABLE>@<DB_LINK>, 如: @Table(name = “[email protected]“, schema = “APPS“)

@Entity
@Data
@Table(name = "[email protected]", schema = "APPS")
public class OdpForecastOutboundExt {
 @Id
 private String rowid;  
 
 private String username;
 
 private Date birthDay;
 
 @Column(name = "HOME_ADDR")
 private String homeAddr;
 
 @Column(name = "HOME_TEL")
 private String homeTel;
}