Configuring Multiple Databases in Spring Boot

2

My application has a connection to a remote database (SQLServer), but I would like to perform a small duplication of certain less volatile data in a local and embedded (H2) database to gain in performance. My setup 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 just create my Repository and that's it. How can I make a Repository linked to a bank and a second to another bank?

    
asked by anonymous 03.04.2018 / 15:10

1 answer

2

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 in the nomenclature was added to differentiate the connections. By escaping the default naming of connection data, Spring Boot will not be able to start the connection to the databases, but this will be resolved in the next step.

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

  • br.com.tassioauad.myapp.model
    • h2
      • entity
      • repository
    • sqlserver
      • entity
      • repository

With this separation done, let's take some of this automatic side of Spring Boot to have more control of what is happening and, consequently, we can inform each package linked to each bank. To do this, we'll create a configuration class for each bank we're 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 DataSource creation method, we are informing annotation @ConfigurationProperties(prefix = "spring.sqlserver.datasource") of the base name of the connection to the database we set up in the application.properties file.

Notice that in annotation @EnableJpaRepositories , used to inform you about the location of the repositories and also the connection, we give you the name of our EntityManager and TransationManager created by the methods. a basePackage pointing the package of repositories that related to the case database.

In the EntityManagerFactory method, notice that invoking the .packages("br.com.tassioauad.myapp.model.h2.entity") method of EntityManagerFactoryBuilder is informing the package where our entities related to the specific database are located.

Finally, we need to point out the need for annotation @Primary in the methods of at least one of the configuration classes to report that it is the primary or primary bank of the application. Therefore, nothing else is necessary.

    
03.04.2018 / 15:10