Configuring Multiple Databases in Spring Boot

Question

My application has a remote database connection (Sqlserver), but I would like to perform a small duplication of certain less volatile data in a local and embedded database (H2) to gain in performance. My configuration today is that of a single bank, reporting on . properties:

spring.jpa.show-sql=true
spring.datasource.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.datasource.url=jdbc:sqlserver://****:1433;databaseName=****
spring.datasource.username=****
spring.datasource.password=****

Being in charge of the Spring Boot itself the creation of Entitymanager, Transactionmanager and etc. So after that I only create mine Repository and ready. How can I make a Repository is linked to a bank and a second to another bank?

Answer 1

First, let’s configure the connection data with the two databases in the file application.properties:

#----DATABASE SQLSERVER
spring.jpa.show-sql=true
spring.sqlserver.datasource.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.sqlserver.datasource.url=jdbc:sqlserver://****:1433;databaseName=****
spring.sqlserver.datasource.username=****
spring.sqlserver.datasource.password=****

#----DATABASE H2
spring.h2.datasource.url=jdbc:h2:file:~/myh2
spring.h2.datasource.username=sa
spring.h2.datasource.password=
spring.h2.datasource.driver-class-name=org.h2.Driver

Note that, unlike the original configuration, the name of the database was added in the nomenclature to differentiate the connections. By escaping from the default naming of the connection data, Spring Boot will not be able to start connection to the databases, but this will be solved in the next step.

After that, we’ll need in our package model create a subpackage to contain entities and repositories from each bank. It would look something like ...model.h2.entity and ...model.sqlserver.entity and the same for the repository. Obviously, you can create in the way you think best, here would be just one possible example of separation.

• br.com.tassioauad.myapp.model
  • H2
    • Entity
    • Repository
  • sqlserver
    • Entity
    • Repository

With this separation done, let’s take a little bit of this automatic side of Spring Boot to have more control of what is happening and, consequently, we can inform each packet connected to each bank. To do this, we will create a configuration class for each bank we are dealing with:

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef = "h2EntityManagerFactory",
        transactionManagerRef = "h2TransactionManager",
        basePackages = { "br.com.tassioauad.myapp.model.h2.repository" }
)
public class H2Configuration {

    @Bean(name = "h2DataSource")
    @ConfigurationProperties(prefix = "spring.h2.datasource")
    public DataSource dataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "h2EntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean
    entityManagerFactory(EntityManagerFactoryBuilder builder, @Qualifier("h2DataSource") DataSource dataSource) {
        Map<String, String> properties = new HashMap<>();
        properties.put("hibernate.hbm2ddl.auto", "update");
        return builder
                .dataSource(dataSource)
                .packages("br.com.tassioauad.myapp.model.h2.entity")
                .persistenceUnit("h2PU")
                .properties(properties)
                .build();
    }

    @Bean(name = "h2TransactionManager")
    public PlatformTransactionManager transactionManager(@Qualifier("h2EntityManagerFactory") EntityManagerFactory entityManagerFactory) {
        return new JpaTransactionManager(entityManagerFactory);
    }

}

And now one for Sqlserver:

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef = "sqlServerEntityManagerFactory",
        transactionManagerRef = "sqlServerTransactionManager",
        basePackages = { "br.com.tassioauad.myapp.model.sqlserver.repository" }
)
public class SqlServerConfiguration {

    @Primary
    @Bean(name = "sqlServerDataSource")
    @ConfigurationProperties(prefix = "spring.sqlserver.datasource")
    public DataSource dataSource() {
        return DataSourceBuilder.create().build();
    }

    @Primary
    @Bean(name = "sqlServerEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean
    entityManagerFactory(EntityManagerFactoryBuilder builder, @Qualifier("sqlServerDataSource") DataSource dataSource) {
        return builder
                .dataSource(dataSource)
                .packages("br.com.tassioauad.myapp.model.sqlserver.entity")
                .persistenceUnit("sqlServerPU")
                .build();
    }

    @Primary
    @Bean(name = "sqlServerTransactionManager")
    public PlatformTransactionManager transactionManager(@Qualifier("sqlServerEntityManagerFactory") EntityManagerFactory entityManagerFactory) {
        return new JpaTransactionManager(entityManagerFactory);
    }
}

In the creation method of DataSource, we are informing through the Annotation @ConfigurationProperties(prefix = "spring.sqlserver.datasource") the base name of the connection to the database we set up in the file application.properties.

Note that in the Annotation @EnableJpaRepositories, used to inform details about the location of the repositories and also the connection, we inform the name of our EntityManager and TransationManager established by the methods and which we also inform a basePackage pointing the repositories package that related to the case database.

In the creation method of EntityManagerFactory, note that in the method invocation .packages("br.com.tassioauad.myapp.model.h2.entity") of EntityManagerFactoryBuilder we are informing the package in which is located our entities related to the specific bank.

Finally, the need to have Annotation @Primary in the methods of at least one of the configuration classes to inform that it is the primary or primary database of the application. So nothing else is needed.