Spring Boot Multiple Database Configuration

Sometimes it’s our application requirement to deal with multiple databases. Spring Boot helps us to configure multiple databases in a single application. This blog will help to configure multiple datasources into a spring boot application.

To use multiple DataSources, let’s add the following details in the property file.

Here we configured ‘foo’ datasource and ‘bar’ datasource.

application.properties

spring.foo.datasource.url = jdbc:mysql://localhost:3306/foo

spring.foo.datasource.username = root

spring.foo.datasource.password = root

spring.foo.datasource.driver-class-name = com.mysql.cj.jdbc.Driver

spring.bar.datasource.url = jdbc:mysql://localhost:3306/bar

spring.bar.datasource.username = root

spring.bar.datasource.password = root

spring.bar.datasource.driver-class-name = com.mysql.cj.jdbc.Driver

logging.level.org.springframework = INFO

JPA Entities and JPA Repositories

 Let’s define the following 2 JPA entities and repositories.

Product Entity

@Entity

@Table(name = "products")

public class Product {

    @Id

    @GeneratedValue(strategy = GenerationType.IDENTITY)

    private Long id;

    @Column(nullable = false)

    private String name;

    @Column(nullable = false)

    private Double price;

    public Product() {

    }

    public Product(String name, Double price) {

        this.name = name;

        this.price = price;

    }

    public Long getId() {

        return id;

    }

    public void setId(Long id) {

        this.id = id;

    }

    public String getName() {

        return name;

    }

    public void setName(String name) {

        this.name = name;

    }

    public Double getPrice() {

        return price;

    }

    public void setPrice(Double price) {

        this.price = price;

    }

}

User Entity

@Entity

@Table(name = "users")

public class User {

    @Id

    @GeneratedValue(strategy = GenerationType.IDENTITY)

    private Long id;

    @Column(nullable = false)

    private String name;

    @Column(nullable = false)

    private String email;

    public User() {

    }

    public User(String name, String email) {

        this.name = name;

        this.email = email;

    }

    public Long getId() {

        return id;

    }

    public void setId(Long id) {

        this.id = id;

    }

    public String getName() {

        return name;

    }

    public void setName(String name) {

        this.name = name;

    }

    public String getEmail() {

        return email;

    }

    public void setEmail(String email) {

        this.email = email;

    }

}

Product Repository 

@Repository

public interface ProductRepository extends JpaRepository<Product, Long> {

}

User Repository 

@Repository

public interface UserRepository extends JpaRepository<User, Long> {

}

We added both entities and repositories in different packages.

Package Structure

We will have a different package structure for both database entities and repositories. This is important while creating configuration classes. While creating config classes, we need to specify the base packages which will be used by Spring to read different configurations and create multiple Datasource. For our example, this is how the package structure looks like:

DataSource Configuration

We will create two Spring configuration classes whose responsibilities are to read the configurations and make sure all necessary setup/classes are available to our project on the application startup.

FooDatabase Configuration 

@Configuration

@Primary

@EnableJpaRepositories(basePackages = "com.example.multipledb.repository.foo",

        entityManagerFactoryRef = "fooEntityManagerFactory", transactionManagerRef = "fooTransactionManager")

@EnableTransactionManagement

public class FooDatabaseConfiguration {

    @Bean(name = "fooDataSourceProperties")

    @ConfigurationProperties(prefix = "spring.foo.datasource")

    @Primary

    DataSourceProperties getDataSourceProperties() {

        return new DataSourceProperties();

    }

    @Bean(name = "fooDataSource")

    @Primary

    HikariDataSource getDatasource(DataSourceProperties properties) {

        return properties.initializeDataSourceBuilder().type(HikariDataSource.class).build();

    }


    @Bean(name = "fooEntityManagerFactory")

    @Primary

    LocalContainerEntityManagerFactoryBean getEntityManagerFactory(DataSource dataSource) {

        LocalContainerEntityManagerFactoryBean entityManagerFactoryBean = new

                LocalContainerEntityManagerFactoryBean();

        entityManagerFactoryBean.setDataSource(dataSource);

        entityManagerFactoryBean.setPackagesToScan("com.example.multipledb.entity.foo");

        entityManagerFactoryBean.setJpaVendorAdapter(new HibernateJpaVendorAdapter());

        Properties properties = new Properties();

        properties.put("hibernate.dialect", "org.hibernate.dialect.MySQL5Dialect");

        entityManagerFactoryBean.setJpaProperties(properties);

        return entityManagerFactoryBean;

    }

    @Bean(name = "fooTransactionManager")

    @Primary

    JpaTransactionManager getTransactionManager(LocalContainerEntityManagerFactoryBean

                                                        entityManagerFactoryBean) {

        JpaTransactionManager transactionManager = new JpaTransactionManager(

                Objects.requireNonNull(entityManagerFactoryBean.getObject()));

        return transactionManager;

    }

}

In the above example class:

@Configuration: indicate that our class declares @Bean methods that will be processed by the Spring container to be used at runtime.

@EnableTransactionManagement: used to allow the usage of annotation-driven transaction management capability.

@EnableJpaRepositories: since we are using spring data JPA, this annotation is required to tell Spring to enable JPA repositories. We specified the entity manager factory and the transaction manager beans to be used in the JPA repositories.

@ConfigurationProperties: This annotation tells spring to pick up the data source properties that are prefixed with “spring.foo.datasource” from the application.properties file and build a data source using DataSourceBuilder.

@Primary: Basically tell the spring that the configured data source is primary.

BarDatabase Configuration 

@Configuration

@EnableJpaRepositories(basePackages = "com.example.multipledb.repository.bar",

        entityManagerFactoryRef = "barEntityManagerFactory", transactionManagerRef = "barTransactionManager")

@EnableTransactionManagement

public class BarDatabaseConfiguration {

    @Bean(name = "barDataSourceProperties")

    @ConfigurationProperties(prefix = "spring.bar.datasource")

    DataSourceProperties getDataSourceProperties() {

        return new DataSourceProperties();

    }

    @Bean(name = "barDataSource")

    HikariDataSource getDatasource(@Qualifier("barDataSourceProperties") DataSourceProperties properties) {

        return properties.initializeDataSourceBuilder().type(HikariDataSource.class).build();

    }

    @Bean(name = "barEntityManagerFactory")

    LocalContainerEntityManagerFactoryBean getEntityManagerFactory(

            @Qualifier("barDataSource") DataSource dataSource) {

        LocalContainerEntityManagerFactoryBean entityManagerFactoryBean =

                new LocalContainerEntityManagerFactoryBean();

        entityManagerFactoryBean.setDataSource(dataSource);

        entityManagerFactoryBean.setPackagesToScan("com.example.multipledb.entity.bar");

        entityManagerFactoryBean.setJpaVendorAdapter(new HibernateJpaVendorAdapter());

        Properties properties = new Properties();

        properties.put("hibernate.dialect", "org.hibernate.dialect.MySQL5Dialect");

        entityManagerFactoryBean.setJpaProperties(properties);

        return entityManagerFactoryBean;

    }

    @Bean(name = "barTransactionManager")

    JpaTransactionManager getTransactionManager(

            @Qualifier("barEntityManagerFactory") LocalContainerEntityManagerFactoryBean

                    entityManagerFactoryBean) {

        JpaTransactionManager transactionManager = new JpaTransactionManager(

                Objects.requireNonNull(entityManagerFactoryBean.getObject()));

        return transactionManager;

    }

}

When we are dealing with just one datasource and Spring Boot, data source configuration is simple. Spring Boot provides a lot of autoconfiguration.

However, if you need to connect to multiple data sources with Spring Boot, additional configuration is needed.

Leave a Reply