In a typical monolithic architecture, applications often interact with a single database. However, there are scenarios where you might need to integrate multiple databases, such as when handling legacy systems or optimizing for performance. With Spring Boot, this is not only possible but can be managed efficiently using DataSource, EntityManager, and TransactionManager.

In this blog, we’ll walk through how to configure multiple databases in a Spring Boot monolithic application using JPA, covering the complete setup and implementation. Let’s dive in!

Why Use Multiple Databases in a Monolithic Application?

There are several use cases where multiple databases might be necessary:
1. Data Segregation: Separate databases can be used to isolate certain parts of the application (e.g., reporting, user data).
2. Legacy Integration: You may need to integrate with a legacy system that uses a different database.
3. Performance Optimization: Splitting data between databases can sometimes help with performance scaling.

Step 1: Add Dependencies
To use multiple databases, you’ll need to add relevant dependencies to your pom.xml. We’ll
assume you’re using H2 and MySQL for this example.

<dependencies>
<!– Spring Boot Starter Data JPA –>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

<!– Spring Boot Starter Web (if you’re building a web application) –>
<dependency>

<!– Add other database drivers as needed, for example:
<!– PostgreSQL –>
<!– <dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
</dependency> –>

<!– H2 Database for In-Memory Testing (optional) –>
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<scope>test</scope>
</dependency>

<!– Spring Boot Starter Test (for unit testing) –>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>

</dependencies>
These dependencies will allow Spring Boot to work with H2 (an in-memory database) and
MySQL.

Step 2: Configure Database Properties
In application.yml (or application.properties), configure both data sources. You’ll
define connection properties for each database.
# Primary Data Source Configuration
spring.datasource.primary.url=jdbc:mysql://localhost:3306/primary_db
spring.datasource.primary.username=root
spring.datasource.primary.password=root_password
spring.datasource.primary.driver-class-name=com.mysql.cj.jdbc.Driver

# Secondary Data Source Configuration
spring.datasource.secondary.url=jdbc:mysql://localhost:3306/secondary_db
spring.datasource.secondary.username=root
spring.datasource.secondary.password=root_password
spring.datasource.secondary.driver-class-name=com.mysql.cj.jdbc.Driver

Here, we’ve defined db1 for H2 and db2 for MySQL. Each has its own URL, credentials, and
Hibernate dialect.

Step 2: Create Configuration for Each Data Source
For each data source, you need to define a configuration class. This will handle creating the DataSource, EntityManagerFactory, and TransactionManager beans for each
database.
Example Configuration for the Primary Database:
@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
basePackages = “com.example.repository.primary”,
entityManagerFactoryRef = “primaryEntityManagerFactory”,
transactionManagerRef = “primaryTransactionManager”

)
public class PrimaryDatabaseConfig {
@Primary
@Bean(name = “primaryDataSource”)
@ConfigurationProperties(prefix = “spring.datasource.primary”)
public DataSource primaryDataSource() {
return DataSourceBuilder.create().build();
}
@Primary
@Bean(name = “primaryEntityManagerFactory”)
public LocalContainerEntityManagerFactoryBean primaryEntityManagerFactory(
EntityManagerFactoryBuilder builder,
@Qualifier(“primaryDataSource”) DataSource dataSource) {
return builder
.dataSource(dataSource)
.packages(“com.example.model.primary”)
.persistenceUnit(“primary”)
.build();
}
@Primary
@Bean(name = “primaryTransactionManager”)

public PlatformTransactionManager primaryTransactionManager(
@Qualifier(“primaryEntityManagerFactory”) EntityManagerFactory
primaryEntityManagerFactory) {
return new JpaTransactionManager(primaryEntityManagerFactory);
}
}
Example Configuration for the Secondary Database:

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
basePackages = “com.example.repository.secondary”,
entityManagerFactoryRef = “secondaryEntityManagerFactory”,
transactionManagerRef = “secondaryTransactionManager”
)
public class SecondaryDatabaseConfig {
@Bean(name = “secondaryDataSource”)
@ConfigurationProperties(prefix = “spring.datasource.secondary”)
public DataSource secondaryDataSource() {
return DataSourceBuilder.create().build();
}

@Bean(name = “secondaryEntityManagerFactory”)
public LocalContainerEntityManagerFactoryBean secondaryEntityManagerFactory(
EntityManagerFactoryBuilder builder,
@Qualifier(“secondaryDataSource”) DataSource dataSource) {
return builder

.dataSource(dataSource)
.packages(“com.example.model.secondary”)
.persistenceUnit(“secondary”)
.build();
}

@Bean(name = “secondaryTransactionManager”)
public PlatformTransactionManager secondaryTransactionManager(
@Qualifier(“secondaryEntityManagerFactory”) EntityManagerFactory
secondaryEntityManagerFactory) {
return new JpaTransactionManager(secondaryEntityManagerFactory);
}
}
In these configurations:
● DataSource: We define a DataSource bean for each database.
● EntityManagerFactory: Specifies the package for the entities related to each database and configures the entity manager.
● TransactionManager: Handles transactions for each specific data source.

Step 3: Define Entity Classes for Each Database
The entity classes are annotated with @Entity, and they must be placed in the corresponding packages as specified in the configuration (com.example.model.primary and com.example.model.secondary).

Example Entity for the Primary Database:
@Entity
@Table(name = “primary_table”)
public class PrimaryEntity {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)

private Long id;
private String name;
// getters and setters
}
Example Entity for the Secondary Database:
@Entity
@Table(name = “secondary_table”)
public class SecondaryEntity {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String description;
// getters and setters
}

Step 4: Create Repositories for Each Data Source
In Spring Data JPA, repositories are interfaces that extend JpaRepository. You need to create separate repositories for each data source and place them in the appropriate packages.

Example Primary Repository:
@Repository
public interface PrimaryRepository extends JpaRepository<PrimaryEntity, Long> {
} Example Secondary Repository:
@Repository
public interface SecondaryRepository extends JpaRepository<SecondaryEntity, Long> {
}
These repositories are scanned based on the base packages defined in your configuration
(com.example.repository.primary and com.example.repository.secondary).

Step 5: Use Repositories in the Service Layer
Once your repositories are set up, you can inject them into your service layer to interact with the respective databases.
@Service
public class MyService {
@Autowired
private PrimaryRepository primaryRepository;
@Autowired
private SecondaryRepository secondaryRepository;

public void saveToPrimaryDb(PrimaryEntity primaryEntity) {
primaryRepository.save(primaryEntity);
} public void saveToSecondaryDb(SecondaryEntity secondaryEntity) {
secondaryRepository.save(secondaryEntity);
}
}

Step 6: Run and Test
At this point, your application is ready to interact with multiple databases. You can create services that save, update, or query data from both the primary and secondary databases.

Pros:
1. Separation of Concerns: Enables clear separation of different data types or modules, which can improve organizational structure and data management.

2. Legacy Integration: Facilitates integration with existing legacy systems by connecting to different databases without requiring a complete overhaul.

3. Scalability: Allows for scaling parts of your application independently by distributing data across multiple databases.

4. Improved Data Management: Different databases can be optimized for specific use cases, improving performance for particular data operations.

5. Enhanced Security: Sensitive data can be stored in separate databases with stricter access controls, reducing the risk of unauthorized access.

6. Backup and Recovery: Separate databases allow for more flexible backup and recovery strategies tailored to different types of data.

7. Data Redundancy: Reduces the risk of data loss by maintaining copies of critical information in different databases.

8. Specialized Database Features: Utilizes the unique features of different databases (e.g., NoSQL for document storage, relational databases for structured data).

9. Reduced Load on Single Database: Distributes the load, potentially improving performance by avoiding bottlenecks in a single database.

10. Flexibility in Technology Choices: Allows using different database technologies suited to specific needs within the same application.

Cons:
1. Increased Complexity: More configurations to manage can complicate your codebase.
2. Performance Overhead: Extra database connections and management may impact performance.
3. Transaction Management Challenges: Coordinating transactions across multiple databases is complex.
4. Testing Complexity: More complex test environments are required.
5. Deployment Overhead: Increased operational burden for deployment and maintenance.
6. Consistency Issues: Keeping data synchronized across databases can be difficult.
7. Complex Configuration: Configuration errors can lead to runtime issues.
8. Resource Consumption: More database connections consume additional system
resources.
9. Dependency Management: Changes in one database may impact others.
10. Learning Curve: Higher complexity adds to the learning curve for developers.

Conclusion
Using multiple databases in a monolithic Spring Boot application with JPA is a powerful approach that can help you handle different data sources while maintaining clean architecture. By configuring multiple DataSource, EntityManagerFactory, and TransactionManager beans, you can manage several databases seamlessly in your application. This approach, while simple, is scalable and can be easily extended if more databases are required in the future.