README.md

Spring Data JDBC Multiple Datasources

This project demonstrates how to configure Spring Data JDBC with multiple independent datasources, each with its own repositories, entities, and database migrations.

Overview

The application connects to two separate MySQL databases:

• db1: Contains Account entities
• db2: Contains Report entities

A single endpoint (GET /) demonstrates fetching and merging data from both datasources into a unified response.

Architecture

Datasource Configuration

Each datasource requires its own set of beans to maintain complete isolation:

DB1 Configuration (Db1DataSourceConfiguration)

• db1CciDataSource - HikariCP datasource
• db1TxManager - Transaction manager
• db1JdbcOps - NamedParameterJdbcOperations
• db1MappingContext - JdbcMappingContext for entity mapping
• db1Converter - JDBC converter
• db1Dialect - MySQL dialect
• db1DataAccessStrategy - Data access strategy
• db1NamedParameterJdbcOps - JdbcAggregateOperations template

DB2 Configuration (Db2DataSourceConfiguration)

Same structure as DB1, with all beans marked @Primary since Spring Data JDBC auto-configuration expects primary beans.

Repository Configuration

Each datasource has its own repository configuration:

DB1 Repositories (Db1RepositoryConfiguration)

@EnableJdbcRepositories(
    basePackages = "com.sparrowlogic.springdatajdbcmultipledatasources.db1.repository",
    jdbcAggregateOperationsRef = "db1NamedParameterJdbcOps",
    transactionManagerRef = "db1TxManager"
)

DB2 Repositories (Db2RepositoryConfiguration)

@EnableJdbcRepositories(
    basePackages = "com.sparrowlogic.springdatajdbcmultipledatasources.db2.repository",
    jdbcAggregateOperationsRef = "db2NamedParameterJdbcOps",
    transactionManagerRef = "db2TxManager"
)

Database Migrations

Flyway is configured separately for each datasource:

DB1 Migrations (Db1MigrationConfiguration)

• Location: classpath:db/migration/db1
• Creates account table

DB2 Migrations (Db2MigrationConfiguration)

• Location: classpath:db/migration/db2
• Creates report table

Both run automatically on application startup via initMethod = "migrate".

Service Layer

ServiceThatDependsOnTwoDataSources demonstrates cross-datasource operations:

• Injects both AccountRepository and ReportRepository
• Saves entities to both databases
• Returns merged response containing data from both sources

Controller

IndexController exposes a single endpoint:

• GET / - Creates and returns an Account from db1 and a Report from db2

Running the Application

Prerequisites

• Java 17+
• Docker (for MySQL containers)
• Maven

Start MySQL Containers

docker run -d --name mysql-db1 -p 33060:3306 \
  -e MYSQL_ROOT_PASSWORD=password \
  -e MYSQL_DATABASE=first \
  -e MYSQL_USER=db1user \
  -e MYSQL_PASSWORD=password \
  mysql:latest

docker run -d --name mysql-db2 -p 33061:3306 \
  -e MYSQL_ROOT_PASSWORD=password \
  -e MYSQL_DATABASE=second \
  -e MYSQL_USER=db2user \
  -e MYSQL_PASSWORD=password \
  mysql:latest

Run Application

mvn spring-boot:run

Test the Endpoint

Visit http://localhost:8080 or:

curl http://localhost:8080

Response:

{
  "account": {
    "id": 1,
    "name": "test"
  },
  "report": {
    "id": 1,
    "reportName": "test"
  }
}

The response demonstrates successful integration of entities from two independent datasources.

Testing

Repository Tests

• AccountRepositoryTest - Tests db1 repository with @Transactional("db1TxManager")
• ReportRepositoryTest - Tests db2 repository with @Transactional("db2TxManager")

Each test uses its own transaction manager for proper isolation.

Service Test

• ServiceThatDependsOnTwoDataSourcesTest - Unit test with mocked repositories

Controller Test

• IndexControllerTest - Web layer test using MockMvc

Test Containers

TestcontainersConfiguration creates two independent MySQL containers for integration tests:

• db1Container - For first datasource
• db2Container - For second datasource

Properties are dynamically injected via @DynamicPropertySource.

Configuration

application.yml

spring:
  flyway:
    enabled: false  # Disabled in favor of manual configuration
  data:
    jdbc:
      repositories:
        enabled: false  # Disabled in favor of manual @EnableJdbcRepositories
  datasource:
    db1:
      url: jdbc:mysql://localhost:33060/first
      username: db1user
      password: password
    db2:
      url: jdbc:mysql://localhost:33061/second
      username: db2user
      password: password

Key Takeaways

1. Complete Isolation: Each datasource needs its own DataSource, TransactionManager, JdbcOperations, MappingContext, Converter, Dialect, and DataAccessStrategy.

2. Repository Separation: Use @EnableJdbcRepositories with distinct basePackages, jdbcAggregateOperationsRef, and transactionManagerRef for each datasource.

3. Primary Beans: Mark one set of beans as @Primary to satisfy Spring Data JDBC's auto-configuration expectations.

4. Transaction Management: Specify transaction manager explicitly in tests using @Transactional("txManagerName").

5. Flyway Isolation: Configure separate Flyway instances with distinct migration locations for each datasource.