Multiple Data Sources

1. To have multiple data source, you need to write it in code. You cannot do it alone in application.properties (at least up to now (2021/02/08)).
2. Add multiple @Configuration with @EnableJpaRepositories. During config, add the package location of the entities related to this data source.
3. We need to add three beans in each of the configuration file to setup the EntityManager, DataSource, and TransactionManager.
4. Add @Primary for your primary data source's methods/beans.
5. Put your Entities files on correct package (see below).
6. Add spring-boot-starter-data-jpa dependency in your pom.xml

Here is how to config the primary data source. We use Environment here so that we can get the properties settings in application.properties files. You can set them there directly here if you want. Note that we do not store user/password directly in plain text format, so we need to decrypt them with our own Tools class.

Note that the EnableJpaRepositories refs name MUST match with our method/bean names.

@Configuration
@PropertySource({"classpath:application.properties"})
@EnableJpaRepositories(
        basePackages = "com.exmaple.myapp.repository.primary",
        entityManagerFactoryRef = "primaryEntityManager",
        transactionManagerRef = "primaryTransactionManager"
)
public class PersistencePrimaryDataConfiguration {
    @Autowired
    private Environment env;

    @Bean
    @Primary
    public LocalContainerEntityManagerFactoryBean primaryEntityManager() {
        LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
        em.setDataSource(primaryDataSource());
        em.setPackagesToScan("hk.org.ha.mipo.sopHamisDataExtractor.model.primary");
        HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
        em.setJpaVendorAdapter(vendorAdapter);
        HashMap<String, Object> properties = new HashMap<>();
        properties.put("hibernate.hbm2ddl.auto", env.getProperty("primary.hibernate.ddl-auto"));
        properties.put("hibernate.dialect", env.getProperty("primary.hibernate.dialect"));
        properties.put("hibernate.jdbc.batch_size", env.getProperty("primary.hibernate.jdbc.batch_size"));
        properties.put("hibernate.order_inserts", env.getProperty("primary.hibernate.order_inserts"));
        properties.put("hibernate.order_updates", env.getProperty("primary.hibernate.order_updates"));
        properties.put("hibernate.generate_statistics", env.getProperty("primary.hibernate.generate_statistics"));
        properties.put("hibernate.naming.implicit-strategy", env.getProperty("primary.hibernate.naming.implicit-strategy"));
        properties.put("hibernate.naming.physical-strategy", env.getProperty("primary.hibernate.naming.physical-strategy"));

        em.setJpaPropertyMap(properties);
        return em;
    }

    @Primary
    @Bean
    public DataSource primaryDataSource() {
        String encryptedUser = env.getProperty("primary.username");
        String encryptedPass = env.getProperty("primary.password");
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName(env.getProperty("primary.driverClassName"));
        dataSource.setUrl(env.getProperty("primary.jdbc.url"));
        try {
            dataSource.setUsername(Tools.decrypt(encryptedUser));
            dataSource.setPassword(Tools.decrypt(encryptedPass));
        } catch (UnsupportedEncodingException e) {
            //should not happened
            e.printStackTrace();
        }
        return dataSource;
    }

    @Primary
    @Bean
    public PlatformTransactionManager primaryTransactionManager() {
        JpaTransactionManager transactionManager = new JpaTransactionManager();
        transactionManager.setEntityManagerFactory(primaryEntityManager().getObject());
        return transactionManager;
    }
}

A usual setting in the application.properties would be

primary.username=%^%*&^&^*&*&ENCODED USERNAME*&*&^(*(*&^&*
primary.password=@#$%^&*$%&&&ENCODED PASSWORD^%&*^&!@#$%^&*(*
primary.jdbc.url=jdbc:PRIMARY_SERVER_LOCATION:PORT;databaseName=YOUR_DATABASE_NAME
primary.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver
primary.hibernate.ddl-auto=none
primary.hibernate.dialect=org.hibernate.dialect.SQLServer2012Dialect
primary.hibernate.jdbc.batch_size=30
primary.hibernate.order_inserts=true
primary.hibernate.order_updates=true
primary.hibernate.generate_statistics=false
primary.hibernate.naming.implicit-strategy=org.hibernate.boot.model.naming.ImplicitNamingStrategyLegacyJpaImpl
primary.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl

They are basically the same as your primary data source configuration file, except you do not add @Primary at the methods/beans.

If you pay attention on the code, you will see

...basePackages = "com.example.myapp.primary",..
...
//and 
...em.setPackagesToScan("com.example.myapp.primary");

Here you can actually pass in an array of string to tell Spring which package(s) of your entities is related to this data source. Yes, we use the package to separate the relation to the data sources. Meaning if you have two data sources, you need to have at least two packages for your entities.