內容目錄
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; }
你必須 登入 才能發表評論。