Spring Boot – multi-database setup

To be able to access two databases in SpringBoot you must define two data sources. In my case I had one database for identity details of users, like username, name, email, address etc. and second database for everything else.

You have to define one @Configuration class for each database and each class will wired its own datasource to services (like JpaRepository or CrudRepository) to defined packages.

package hello;
 
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.annotation.EnableTransactionManagement;
 
import javax.persistence.EntityManagerFactory;
import javax.sql.DataSource;
 
@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef = "authEntityManagerFactory",
        transactionManagerRef = "authTransactionManager",
        basePackages = "hello.service.auth")
public class AuthConfig {
 
    @Bean
    @ConfigurationProperties(prefix = "datasource.auth")
    @Primary
    public DataSource authDataSource() {
        return DataSourceBuilder.create().build();
    }
 
    @Bean
    @Primary
    public LocalContainerEntityManagerFactoryBean authEntityManagerFactory(
            final EntityManagerFactoryBuilder builder) {
        return builder
                .dataSource(authDataSource())
                .packages("hello.model.auth")
                .persistenceUnit("authPersistenceUnit")
                .build();
    }
 
    @Bean
    @Primary
    public JpaTransactionManager authTransactionManager(
            @Qualifier("authEntityManagerFactory") final EntityManagerFactory factory) {
        return new JpaTransactionManager(factory);
    }
}
package hello;
 
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 org.springframework.transaction.annotation.EnableTransactionManagement;
 
import javax.persistence.EntityManagerFactory;
import javax.sql.DataSource;
 
@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef = "modelEntityManagerFactory",
        transactionManagerRef = "modelTransactionManager",
        basePackages = { "hello.service.model" })
public class ModelDatabaseConfig {
 
    @Bean(name = "modelDataSource")
    @ConfigurationProperties(prefix = "datasource.model")
    public DataSource modelDataSource() {
        return DataSourceBuilder.create().build();
    }
 
    @Bean(name = "modelEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean modelEntityManagerFactory(
            EntityManagerFactoryBuilder builder,
            @Qualifier("modelDataSource") DataSource barDataSource) {
        return builder
                .dataSource(barDataSource)
                .packages("hello.model.marshall")
                .persistenceUnit("model")
                .build();
    }
 
    @Bean(name = "modelTransactionManager")
    public PlatformTransactionManager modelTransactionManager(
            @Qualifier("modelEntityManagerFactory") EntityManagerFactory factory) {
        return new JpaTransactionManager(factory);
    }
 
}

Every datasource must be now described in src/resources/application.properties file, that will provide database credentials, driver, transaction management…

datasource.model.url=jdbc:mysql://localhost/model?autoReconnect=true
datasource.model.username=model_user
datasource.model.password=password
datasource.model.driver-class-name=com.mysql.cj.jdbc.Driver
 
datasource.auth.url==jdbc:mysql://192.168.1.100/auth?autoReconnect=true
datasource.auth.username=auth_user
datasource.auth.password=password
datasource.auth.driver-class-name=com.mysql.cj.jdbc.Driver