Configuring Multiple Databases in a Single Spring Boot Application

source : innovationm.co

In many enterprise applications, there is often a need to connect to multiple databases. This can be due to a variety of reasons such as different data storage requirements, integrating legacy systems, or microservices architecture. Spring Boot, with its powerful abstraction and configuration capabilities, makes it relatively straightforward to connect to multiple databases. This article will guide you through the steps to configure multiple databases in a single Spring Boot application, with ample code examples to illustrate the implementation.

Prerequisites

• Basic knowledge of Spring Boot
• JDK 8 or later installed
• Maven or Gradle build tool

Project Setup

To start with, we need to create a Spring Boot project. You can create a new project using Spring Initializr.

Include the following dependencies:

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>
    <dependency>
        <groupId>com.h2database</groupId>
        <artifactId>h2</artifactId>
        <scope>runtime</scope>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <!-- Add other database dependencies here, e.g., MySQL, PostgreSQL -->
</dependencies>

Application Properties

Configure the application properties to include settings for multiple data sources. Here is an example for two databases: H2 (in-memory) and MySQL.

# H2 Database Configuration
spring.datasource.primary.url=jdbc:h2:mem:primarydb
spring.datasource.primary.driverClassName=org.h2.Driver
spring.datasource.primary.username=sa
spring.datasource.primary.password=password
spring.datasource.primary.hikari.maximum-pool-size=5

# MySQL Database Configuration
spring.datasource.secondary.url=jdbc:mysql://localhost:3306/secondarydb
spring.datasource.secondary.username=root
spring.datasource.secondary.password=rootpassword
spring.datasource.secondary.driverClassName=com.mysql.cj.jdbc.Driver
spring.datasource.secondary.hikari.maximum-pool-size=5

# Hibernate properties for both data sources
spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true

Data Source Configuration

Create configuration classes for each data source.

Primary Data Source Configuration

package com.example.config;

import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.jdbc.datasource.lookup.JndiDataSourceLookup;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;
import org.springframework.transaction.PlatformTransactionManager;

@Configuration
@EnableJpaRepositories(
    basePackages = "com.example.primary",
    entityManagerFactoryRef = "primaryEntityManagerFactory",
    transactionManagerRef = "primaryTransactionManager"
)
public class PrimaryDataSourceConfig {

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

    @Bean(name = "primaryEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean primaryEntityManagerFactory(
            @Qualifier("primaryDataSource") DataSource dataSource) {
        LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
        em.setDataSource(dataSource);
        em.setPackagesToScan("com.example.primary");
        em.setJpaVendorAdapter(new HibernateJpaVendorAdapter());

        return em;
    }

    @Bean(name = "primaryTransactionManager")
    public PlatformTransactionManager primaryTransactionManager(
            @Qualifier("primaryEntityManagerFactory") LocalContainerEntityManagerFactoryBean primaryEntityManagerFactory) {
        return new JpaTransactionManager(primaryEntityManagerFactory.getObject());
    }
}

Secondary Data Source Configuration

package com.example.config;

import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
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.orm.jpa.vendor.HibernateJpaVendorAdapter;
import org.springframework.transaction.PlatformTransactionManager;

@Configuration
@EnableJpaRepositories(
    basePackages = "com.example.secondary",
    entityManagerFactoryRef = "secondaryEntityManagerFactory",
    transactionManagerRef = "secondaryTransactionManager"
)
public class SecondaryDataSourceConfig {

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

    @Bean(name = "secondaryEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean secondaryEntityManagerFactory(
            @Qualifier("secondaryDataSource") DataSource dataSource) {
        LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
        em.setDataSource(dataSource);
        em.setPackagesToScan("com.example.secondary");
        em.setJpaVendorAdapter(new HibernateJpaVendorAdapter());

        return em;
    }

    @Bean(name = "secondaryTransactionManager")
    public PlatformTransactionManager secondaryTransactionManager(
            @Qualifier("secondaryEntityManagerFactory") LocalContainerEntityManagerFactoryBean secondaryEntityManagerFactory) {
        return new JpaTransactionManager(secondaryEntityManagerFactory.getObject());
    }
}

Entity Classes

Create entity classes for each data source.

Primary Entity

package com.example.primary.entity;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;

@Entity
public class PrimaryEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;

    // Getters and setters
}

Secondary Entity

package com.example.secondary.entity;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;

@Entity
public class SecondaryEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String description;

    // Getters and setters
}

Repositories

Create repository interfaces for each data source.

Primary Repository

package com.example.primary.repository;

import org.springframework.data.jpa.repository.JpaRepository;
import com.example.primary.entity.PrimaryEntity;

public interface PrimaryRepository extends JpaRepository<PrimaryEntity, Long> {
}

Secondary Repository

package com.example.secondary.repository;

import org.springframework.data.jpa.repository.JpaRepository;
import com.example.secondary.entity.SecondaryEntity;

public interface SecondaryRepository extends JpaRepository<SecondaryEntity, Long> {
}

Service Layer

Create service classes to handle the business logic.

Primary Service

package com.example.primary.service;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.example.primary.entity.PrimaryEntity;
import com.example.primary.repository.PrimaryRepository;
import java.util.List;

@Service
public class PrimaryService {

    @Autowired
    private PrimaryRepository primaryRepository;

    public List<PrimaryEntity> findAll() {
        return primaryRepository.findAll();
    }

    public PrimaryEntity save(PrimaryEntity entity) {
        return primaryRepository.save(entity);
    }
}

Secondary Service

package com.example.secondary.service;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.example.secondary.entity.SecondaryEntity;
import com.example.secondary.repository.SecondaryRepository;
import java.util.List;

@Service
public class SecondaryService {

    @Autowired
    private SecondaryRepository secondaryRepository;

    public List<SecondaryEntity> findAll() {
        return secondaryRepository.findAll();
    }

    public SecondaryEntity save(SecondaryEntity entity) {
        return secondaryRepository.save(entity);
    }
}

Controller Layer

Create REST controllers to expose the services.

Primary Controller

package com.example.primary.controller;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import com.example.primary.entity.PrimaryEntity;
import com.example.primary.service.PrimaryService;
import java.util.List;

@RestController
@RequestMapping("/api/primary")
public class PrimaryController {

    @Autowired
    private PrimaryService primaryService;

    @GetMapping("/entities")
    public List<PrimaryEntity> getAll() {
        return primaryService.findAll();
    }

    @PostMapping("/entity")
    public PrimaryEntity create(@RequestBody PrimaryEntity entity) {
        return primaryService.save(entity);
    }
}

Secondary Controller

package com.example.secondary.controller;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import com.example.secondary.entity.SecondaryEntity;
import com.example.secondary.service.SecondaryService;
import java.util.List;

@RestController
@RequestMapping("/api/secondary")
public class SecondaryController {

    @Autowired
    private SecondaryService secondaryService;

    @GetMapping("/entities")
    public List<SecondaryEntity> getAll() {
        return secondaryService.findAll();
    }

    @PostMapping("/entity")
    public SecondaryEntity create(@RequestBody SecondaryEntity entity) {
        return secondaryService.save(entity);
    }
}

Running the Application

Start your Spring Boot application and you should be able to access the endpoints for both primary and secondary databases.

• Primary DB Endpoints:
• GET /api/primary/entities - Retrieve all primary entities
• POST /api/primary/entity - Create a new primary entity
• Secondary DB Endpoints:
• GET /api/secondary/entities - Retrieve all secondary entities
• POST /api/secondary/entity - Create a new secondary entity

Conclusion

Configuring multiple databases in a Spring Boot application requires setting up multiple data source configurations, repositories, services, and controllers. By following the steps outlined in this article, you can easily connect to and manage multiple databases within your Spring Boot application. This setup allows you to leverage the power of Spring Boot and Spring Data JPA to handle complex data requirements in enterprise applications.

By encapsulating different data sources within their respective configurations, you ensure a clean separation of concerns and maintainable codebase. This guide provides a solid foundation, and you can extend it further based on specific application needs, such as adding more data sources or custom query methods.