## 前言
一般在Spring Boot 專案中，若只需要連接一個資料庫，只需要依賴`spring-boot-start-jdbc`和配置好`application.yml`即可使用。

但若功能需要讀取複數個不同的資料庫時就需要特別配置DataSource。

## 作法

**DataBaseConfiguration.java**

在該檔案內定義了2個不同的資料庫DataSource，`env.getProperty("XXX")`取的是`application.yml`設定好的連線資訊、參數。

package com.company.qqq.domain.config;

import com.zaxxer.hikari.HikariDataSource;
import javax.sql.DataSource;
import java.util.Objects;
import java.util.Properties;
import java.util.concurrent.TimeUnit;
import oracle.jdbc.OracleConnection;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.AutoConfigureOrder;
import org.springframework.boot.autoconfigure.condition.ConditionalOnClass;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.Ordered;
import org.springframework.core.env.Environment;

@Configuration
@ConditionalOnClass(DataSource.class)
@AutoConfigureOrder(Ordered.HIGHEST_PRECEDENCE)
public class DomainConfiguration {

    @Autowired
    private Environment env;

    public DomainConfiguration(Environment env) {
        this.env = env;
    }

    @Primary
    @Bean(name = "oracleDataSource")
    public DataSource brddhsDataSource() {
        HikariDataSource hikariDataSource = new HikariDataSource();
        hikariDataSource.setJdbcUrl(env.getProperty("spring.datasource.oracle.hikari.jdbc-url"));
        hikariDataSource.setDriverClassName(Objects.requireNonNull(env.getProperty("spring.datasource.oracle.hikari.driver-class-name")));
        hikariDataSource.setUsername(Objects.requireNonNull(env.getProperty("spring.datasource.oracle.hikari.username")));
        hikariDataSource.setPassword(Objects.requireNonNull(env.getProperty("spring.datasource.oracle.hikari.pd")));
        hikariDataSource.setConnectionTimeout(Long.parseLong(Objects.requireNonNull(env.getProperty("spring.datasource.oracle.hikari.connection-timeout"))));
        hikariDataSource.setIdleTimeout(Long.parseLong(Objects.requireNonNull(env.getProperty("spring.datasource.oracle.hikari.idle-timeout"))));
        hikariDataSource.setMaximumPoolSize(Integer.parseInt(Objects.requireNonNull(env.getProperty("spring.datasource.oracle.hikari.maximum-pool-size"))));
        hikariDataSource.setMaxLifetime(Long.parseLong(Objects.requireNonNull(env.getProperty("spring.datasource.oracle.hikari.max-lifetime"))));
        final Properties properties = new Properties();
        properties.setProperty(OracleConnection.CONNECTION_PROPERTY_THIN_NET_CONNECT_TIMEOUT, String.valueOf(TimeUnit.SECONDS.toMillis(600)));
        properties.setProperty(OracleConnection.CONNECTION_PROPERTY_THIN_READ_TIMEOUT, String.valueOf(TimeUnit.SECONDS.toMillis(600)));
        hikariDataSource.setDataSourceProperties(properties);
        return hikariDataSource;
    }


    @Bean(name = "mssqlDataSource")
    public DataSource mssqlDataSource() {
        HikariDataSource hikariDataSource = new HikariDataSource();
        hikariDataSource.setJdbcUrl(env.getProperty("spring.datasource.mssql.hikari.jdbc-url"));
        hikariDataSource.setDriverClassName(Objects.requireNonNull(env.getProperty("spring.datasource.mssql.hikari.driver-class-name")));
        hikariDataSource.setUsername(Objects.requireNonNull(env.getProperty("spring.datasource.mssql.hikari.username")));
        hikariDataSource.setPassword(Objects.requireNonNull(env.getProperty("spring.datasource.mssql.hikari.pd")));
        hikariDataSource.setConnectionTimeout(Long.parseLong(Objects.requireNonNull(env.getProperty("spring.datasource.mssql.hikari.connection-timeout"))));
        hikariDataSource.setIdleTimeout(Long.parseLong(Objects.requireNonNull(env.getProperty("spring.datasource.mssql.hikari.idle-timeout"))));
        hikariDataSource.setMaximumPoolSize(Integer.parseInt(Objects.requireNonNull(env.getProperty("spring.datasource.mssql.hikari.maximum-pool-size"))));
        hikariDataSource.setMaxLifetime(Long.parseLong(Objects.requireNonNull(env.getProperty("spring.datasource.mssql.hikari.max-lifetime"))));
        return hikariDataSource;
    }
}

接著為Oracle、MSSQL的DataSource分別建立自己的`EntityManagerFactory`和`TransactionManager`。

其中最重要的是`@EnableJpaRepositories`：
* `basePackages`：寫的包名就是當JPA存取到該包名底下的Entity時會使用該config的設定，也就是說不同資料庫的Entity需要分別放在不同package底下。
* `entityManagerFactoryRef` ：使用的是需要使用哪個entityManagerFactory實體。
* `transactionManagerRef` ：使用的是需要使用哪個transactionManager實體。
)

**OracleConfiguration.java**

package com.company.qqq.domain.config;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.PersistenceContext;
import javax.sql.DataSource;
import java.util.Properties;
import java.util.concurrent.TimeUnit;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.AutoConfigureOrder;
import org.springframework.boot.autoconfigure.condition.ConditionalOnClass;
import org.springframework.boot.autoconfigure.jdbc.JdbcProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.Ordered;
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.orm.jpa.vendor.HibernateJpaVendorAdapter;
import org.springframework.scheduling.annotation.EnableAsync;
import org.springframework.web.servlet.config.annotation.WebMvcConfigurer;

@Configuration
@ConditionalOnClass(DataSource.class)
@AutoConfigureOrder(Ordered.HIGHEST_PRECEDENCE)
@EnableJpaRepositories(
        basePackages = "com.company.qqq.domain.oracle",
        entityManagerFactoryRef = "oracleFactory",
        transactionManagerRef = "oracleManager"
)
@EnableAsync
public class OracleConfiguration {

    @Autowired
    @Qualifier("oracleDataSource")
    private DataSource dataSource;

    @Primary
    @Bean(name = "oracleFactory")
    public LocalContainerEntityManagerFactoryBean managerFactory() {
        final LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
        em.setDataSource(dataSource);
        em.setPackagesToScan("com.company.domain.entity.oracle");
        em.setJpaVendorAdapter(new HibernateJpaVendorAdapter());
        em.setPersistenceUnitName("oracle");
        final Properties properties = new Properties();
        properties.setProperty("javax.persistence.query.timeout", String.valueOf(TimeUnit.SECONDS.toMillis(600)));
        properties.setProperty("javax.persistence.lock.timeout", String.valueOf(TimeUnit.SECONDS.toMillis(600)));
        em.setJpaProperties(properties);
        return em;
    }

    @Primary
    @Bean(name = "oracleManager")
    JpaTransactionManager transactionManager(@Qualifier("oracleFactory") final EntityManagerFactory entityManagerFactory) {
        final JpaTransactionManager queryDataTransactionManager = new JpaTransactionManager();
        queryDataTransactionManager.setEntityManagerFactory(entityManagerFactory);
        return queryDataTransactionManager;
    }

    @Primary
    @Bean
    @PersistenceContext(unitName = "oracle")
    public JPAQueryFactory jpaQueryFactory(@Qualifier("oracleFactory") EntityManager entityManager) {
        return new JPAQueryFactory(entityManager);
    }

    @Primary
    @Bean
    public JdbcTemplate jdbcTemplate(@Qualifier("oracleDataSource") DataSource dataSource, JdbcProperties properties) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        JdbcProperties.Template template = properties.getTemplate();
        jdbcTemplate.setFetchSize(template.getFetchSize());
        jdbcTemplate.setMaxRows(template.getMaxRows());
        if (template.getQueryTimeout() != null) {
            jdbcTemplate.setQueryTimeout((int) template.getQueryTimeout().getSeconds());
        }
        return jdbcTemplate;
    }
}

**ＭssqlConfiguration.java**

package com.company.qqq.domain.config;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.PersistenceContext;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.AutoConfigureOrder;
import org.springframework.boot.autoconfigure.condition.ConditionalOnClass;
import org.springframework.boot.autoconfigure.jdbc.JdbcProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.Ordered;
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.orm.jpa.vendor.HibernateJpaVendorAdapter;

@Configuration
@ConditionalOnClass(DataSource.class)
@AutoConfigureOrder(Ordered.HIGHEST_PRECEDENCE)
@EnableJpaRepositories(
        basePackages = "com.company.qqq.domain.mssql",
        entityManagerFactoryRef = "mssqlFactory",
        transactionManagerRef = "mssqlManager"
)
public class MssqlConfiguration {

    @Autowired
    @Qualifier("mssqlDataSource")
    private DataSource dataSource;

    public MssqlConfiguration(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    @Bean(name = "mssqlFactory")
    public LocalContainerEntityManagerFactoryBean managerFactory() {
        final LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
        em.setDataSource(dataSource);
        em.setPackagesToScan("com.company.domain.entity.mssql");
        em.setJpaVendorAdapter(new HibernateJpaVendorAdapter());
        em.setPersistenceUnitName("mssql");
        return em;
    }

    @Bean(name = "mssqlManager")
    JpaTransactionManager transactionManager(@Qualifier("mssqlFactory") final EntityManagerFactory entityManagerFactory) {
        final JpaTransactionManager queryDataTransactionManager = new JpaTransactionManager();
        queryDataTransactionManager.setEntityManagerFactory(entityManagerFactory);
        return queryDataTransactionManager;
    }

    @Bean
    public JdbcTemplate mssqlJdbcTemplate(@Qualifier("mssqlDataSource") DataSource dataSource, JdbcProperties properties) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        JdbcProperties.Template template = properties.getTemplate();
        jdbcTemplate.setFetchSize(template.getFetchSize());
        jdbcTemplate.setMaxRows(template.getMaxRows());
        if (template.getQueryTimeout() != null) {
            jdbcTemplate.setQueryTimeout((int) template.getQueryTimeout().getSeconds());
        }
        return jdbcTemplate;
    }
}

設定好之後，使用JPA的查詢就會自動配對好對應的資料庫。
但是若要客製特殊SQL語法的查詢實作時，就需要另外的作法。

**TableARepository.java (原本)**
這是原本的Repository類別，只要繼承JpaRepository就可以使用JPA預設的查詢。

package com.company.qqq.oracle.repository;

import java.util.List;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;
import com.company.qqq.entity.oracle;

@Repository
public interface TableARepository extends JpaRepository<TableA, Integer> {
    
	public List<TableA> queryAll();

} 

**TableARepository.java (客製)**
多繼承一個`TableARepositoryCustom`介面，該介面定義了客製的方法。
並由`TableARepositoryImpl`來實作裡面的方法，該類別內指定使用哪個`EntityManager`去存取哪個資料庫。並定義了需要的SQL語句就可以使用JPA客製的查詢。

package com.company.qqq.oracle.repository;

import java.util.List;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;
import com.company.qqq.entity.oracle;

@Repository
public interface TableARepository extends JpaRepository<TableA, Integer>, TableARepositoryCustom {
    
	public List<TableA> queryAll();

} 

**TableARepositoryCustom.java**

package com.company.qqq.oracle.repository.coustom;

import com.company.qqq.oracle.entity.tableA;
import com.company.qqq.oracle.repository.custom.TableARepositoryCustom;
import java.util.Optional;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

@Repository
public interface TableARepositoryCustom {
    List<TableADTO> getMethod1(String idn, String kind);
    List<TableADTO> getMethod2(String idn, String seq);
    List<TableADTO> getMethod3(String account);

**TableARepositoryImpl.java**

package com.company.qqq.oracle.repository.impl;

import com.company.qqq.oracle.repository.custom.TableARepositoryCustom;
import com.company.qqq.oracle.dto.TableADTO;
import lombok.AllArgsConstructor;
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;

public class TableARepositoryImpl implements TableARepositoryCustom {
    
    private final EntityManager em;

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


    @Override
    public List<TableADTO> getMethod1(String idn, String kind) {
        String jpql = " SELECT a FROM TableA where idn = " + idn + " " and kind = " " + kind ;
		TypedQuery<TableA> query = this.em.createQuery(jpql, TableA.class);
		return query.getResultList();
    }
        

    @Override
    public List<TableADTO> getMethod2(String idn, String seq) {
       //TODO
    }

    @Override
    public List<TableADTO> getMethod3(String account) {
       //TODO
    }

}