Spring & Multiple SQLs: How to easily connect your Spring application to multiple SQL databases

This post will show you how to connect your Spring application to multiple SQL Databases at the same time. But first, let’s see when multiple DB connections are useful.

text

For example you have an application and you have that application set up in different environments (let’s call it “prod app”), and you want to write another app, to read its DB. Let’s call this application the “view app”. You want to protect the view app with user authentication for sure, because your prod app’s DB contains sensitive data (like the code to detonate printers :P ), so you don’t want anyone to read it. On the other hand you don’t want your view app’s users to be stored in the prod app’s DB. What can you do? Your view application must connect to multiple (in this case SQL) Databases. This tutorial will show you how to do it.

First let’s setup the connection to both databases in the application.yml file

spring:
  datasource:
    view:
      type: com.zaxxer.hikari.HikariDataSource
      url: jdbc:postgresql://stanlin.wanari.com:5432/spring-multidb-view
      username: spring-multidb-view
      password: spring-multidb-view
    prod:
      type: com.zaxxer.hikari.HikariDataSource
      url: jdbc:postgresql://stanlin.wanari.com:5432/spring-multidb-prod
      username: spring-multidb-prod
      password: spring-multidb-prod

I made two example databases.

One is for the view app, and one is for the prod app. The view DB contains a user table with an e-mail address. I authenticate the user by checking the e-mail given in the header, you can use JWT or any other authentication method, but authentication is out of scope right now, so I implemented this simple and stupid method. If it’s stupid but it works it ain’t stupid right? :D

The other is for the prod DB, it contains a table (detonation_code) with some very sensitive data!

The next step is to tell Spring how to configure the dataSource, entityManagerFactory and transactionManager for the two connections.

I use the same thing for the two configs, the only difference is in the public static final String variables, they will tell Spring where to inject which provided bean.

package com.wanari.multidb.example.configuration;
 
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;
 
import javax.persistence.EntityManagerFactory;
import javax.sql.DataSource;
 
@Configuration
@EnableJpaRepositories(
    basePackages = ViewDatabaseConfiguration.REPOSITORY_PACKAGE,
    entityManagerFactoryRef = ViewDatabaseConfiguration.ENTITY_MANAGER_FACTORY,
    transactionManagerRef = ViewDatabaseConfiguration.TRANSACTION_MANAGER
)
public class ViewDatabaseConfiguration {
 
    private static final String SPECIFIER = "view";
 
    private static final String DOMAIN_PACKAGE = "com.wanari.multidb.example.domain." + SPECIFIER;
    static final String REPOSITORY_PACKAGE = "com.wanari.multidb.example.repository." + SPECIFIER;
    private static final String CONFIGURATION_PROPERTIES = "spring.datasource." + SPECIFIER;
 
    private static final String DATA_SOURCE = SPECIFIER + "DataSource";
    static final String ENTITY_MANAGER_FACTORY = SPECIFIER + "EntityManagerFactory";
    static final String TRANSACTION_MANAGER = SPECIFIER + "TransactionManager";
    private static final String PERSISTENCE_UNIT = SPECIFIER + "PersistenceUnit";
 
    @Primary
    @Bean(name = DATA_SOURCE)
    @ConfigurationProperties(prefix = CONFIGURATION_PROPERTIES)
    public DataSource dataSource() {
        return DataSourceBuilder.create().build();
    }
 
    @Primary
    @Bean(name = ENTITY_MANAGER_FACTORY)
    public LocalContainerEntityManagerFactoryBean entityManagerFactory(
        EntityManagerFactoryBuilder builder,
        @Qualifier(DATA_SOURCE) DataSource viewDataSource
    ) {
        return builder
            .dataSource(viewDataSource)
            .packages(DOMAIN_PACKAGE)
            .persistenceUnit(PERSISTENCE_UNIT)
            .build();
    }
 
    @Bean(name = TRANSACTION_MANAGER)
    public PlatformTransactionManager transactionManager(@Qualifier(ENTITY_MANAGER_FACTORY) EntityManagerFactory entityManagerFactory) {
        return new JpaTransactionManager(entityManagerFactory);
    }
}
DatabaseConfiguration.java
package com.wanari.multidb.example.configuration;
 
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;
 
import javax.persistence.EntityManagerFactory;
import javax.sql.DataSource;
 
@Configuration
@EnableJpaRepositories(
    basePackages = ProdDatabaseConfiguration.REPOSITORY_PACKAGE,
    entityManagerFactoryRef = ProdDatabaseConfiguration.ENTITY_MANAGER_FACTORY,
    transactionManagerRef = ProdDatabaseConfiguration.TRANSACTION_MANAGER
)
public class ProdDatabaseConfiguration {
 
    private static final String SPECIFIER = "prod";
 
    private static final String DOMAIN_PACKAGE = "com.wanari.multidb.example.domain." + SPECIFIER;
    static final String REPOSITORY_PACKAGE = "com.wanari.multidb.example.repository." + SPECIFIER;
    private static final String CONFIGURATION_PROPERTIES = "spring.datasource." + SPECIFIER;
 
    private static final String DATA_SOURCE = SPECIFIER + "DataSource";
    static final String ENTITY_MANAGER_FACTORY = SPECIFIER + "EntityManagerFactory";
    static final String TRANSACTION_MANAGER = SPECIFIER + "TransactionManager";
    private static final String PERSISTENCE_UNIT = SPECIFIER + "PersistenceUnit";
 
    @Bean(name = DATA_SOURCE)
    @ConfigurationProperties(prefix = CONFIGURATION_PROPERTIES)
    public DataSource dataSource() {
        return DataSourceBuilder.create().build();
    }
 
    @Bean(name = ENTITY_MANAGER_FACTORY)
    public LocalContainerEntityManagerFactoryBean entityManagerFactory(
        EntityManagerFactoryBuilder builder,
        @Qualifier(DATA_SOURCE) DataSource prodDataSource
    ) {
        return builder
            .dataSource(prodDataSource)
            .packages(DOMAIN_PACKAGE)
            .persistenceUnit(PERSISTENCE_UNIT)
            .build();
    }
 
    @Bean(name = TRANSACTION_MANAGER)
    public PlatformTransactionManager transactionManager(
        @Qualifier(ENTITY_MANAGER_FACTORY) EntityManagerFactory entityManagerFactory
    ) {
        return new JpaTransactionManager(entityManagerFactory);
    }
}

As you see the two files are almost exactly the same. Let’s talk about the differences.

First, there is a SPECIFIER variable, with which the other String constants will give you the specific bean names for the specific configuration. If you want to make a third DB connection don’t forget to override that :)

Second, there are some functions annotated with the @Primary keyword. This will tell Spring which bean to prefer from the two instances with the same type (like the two dataSource, or entityManagerFactory, etc.). Our primary beans are the view beans, so that’s why those are annotated with @Primary.

**IMPORTANT! **

In Spring 1.5.7 you MUST separate your repositories into packeges (like I did in the example), and those folders should not contain each other. For example if I moved the repositories from .repository.view package to .repository package, they would use the wrong dataSource, entityManagerFactory etc.

By separating the repositories into packages you have nothing to do with them, but creating it like you do with only one DB connection. Spring will know which implementations to inject into the repositories by the EnableJpaRepositories annotation on the config :)

Here is an example for the DetonationCodeRepository

package com.wanari.multidb.example.repository.prod;
 
import com.wanari.multidb.example.domain.prod.DetonationCode;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;
 
@Repository
public interface DetonationCodeRepository extends JpaRepository<DetonationCode, Long> {
}

You can use the repository interface like you would use it with only one DB connection, add your specific queries, add a Specification, etc.

That’s all, I told you it’s easy :)

You can take a look at the full source code here!

Thanks for reading my post, and have a nice day!

PS.: Stay up-to-date! Follow us on Facebook or LLLLinkedIn 😉

member photo

His favorite technologies are AngularJS and Java 8. He's been at Wanari as a full stack developer for almost 3 years.

Latest post by Alex Sükein

Solutions for a filterable sortable pageable list in Spring