Configuring Multiple Datasources in Spring Boot Application

Akib Sayyad 0

Configuring two different data sources in a Spring Boot application, one using MySQL and the other using PostgreSQL, involves several steps. Below is a complete example of how you can achieve this:

1. Add Dependencies

In your pom.xml, add the dependencies for both MySQL and PostgreSQL drivers:

<dependencies>
    <!-- MySQL Driver -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <scope>runtime</scope>
    </dependency>

    <!-- PostgreSQL Driver -->
    <dependency>
        <groupId>org.postgresql</groupId>
        <artifactId>postgresql</artifactId>
        <scope>runtime</scope>
    </dependency>

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

Below is an outline of how your project structure would look:

spring-boot-multi-datasource/
├── src
│   ├── main
│   │   ├── java
│   │   │   └── com
│   │   │       └── example
│   │   │           ├── controller
│   │   │           │   ├── MysqlController.java
│   │   │           │   └── PostgresController.java
│   │   │           ├── model
│   │   │           │   ├── mysql
│   │   │           │   │   └── MysqlEntity.java
│   │   │           │   └── postgres
│   │   │           │       └── PostgresEntity.java
│   │   │           ├── repository
│   │   │           │   ├── mysql
│   │   │           │   │   └── MysqlRepository.java
│   │   │           │   └── postgres
│   │   │           │       └── PostgresRepository.java
│   │   │           ├── service
│   │   │           │   └── MyService.java
│   │   │           ├── config
│   │   │           │   ├── MysqlDataSourceConfig.java
│   │   │           │   └── PostgresDataSourceConfig.java
│   │   │           └── SpringBootMultiDatasourceApplication.java
│   │   ├── resources
│   │   │   ├── application.properties
│   │   │   └── application.yml
│   │   └── static
│   │   └── templates
│   └── test
│       ├── java
│       └── resources
├── pom.xml
└── README.md

2. Configure application.properties or application.yml

Define the properties for both MySQL and PostgreSQL data sources.

For application.properties:

# MySQL datasource
spring.datasource.mysql.url=jdbc:mysql://localhost:3306/mysql_db
spring.datasource.mysql.username=root
spring.datasource.mysql.password=root_password
spring.datasource.mysql.driver-class-name=com.mysql.cj.jdbc.Driver

# Postgres datasource
spring.datasource.postgres.url=jdbc:postgresql://localhost:5432/postgres_db
spring.datasource.postgres.username=postgres
spring.datasource.postgres.password=postgres_password
spring.datasource.postgres.driver-class-name=org.postgresql.Driver

For application.yml:

spring:
  datasource:
    mysql:
      url: jdbc:mysql://localhost:3306/mysql_db
      username: root
      password: root_password
      driver-class-name: com.mysql.cj.jdbc.Driver
    postgres:
      url: jdbc:postgresql://localhost:5432/postgres_db
      username: postgres
      password: postgres_password
      driver-class-name: org.postgresql.Driver

3. Create Configuration Classes

You will create separate configuration classes for each data source and mark one of them as @Primary to let Spring know which one to use when not explicitly specified.

MySQL Configuration:

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
    basePackages = "com.example.repository.mysql", // Adjust the package
    entityManagerFactoryRef = "mysqlEntityManagerFactory",
    transactionManagerRef = "mysqlTransactionManager"
)
public class MysqlDataSourceConfig {

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

    @Primary
    @Bean(name = "mysqlEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean mysqlEntityManagerFactory(
            EntityManagerFactoryBuilder builder, @Qualifier("mysqlDataSource") DataSource dataSource) {
        return builder
                .dataSource(dataSource)
                .packages("com.example.model.mysql") // Adjust the package
                .persistenceUnit("mysql")
                .build();
    }

    @Primary
    @Bean(name = "mysqlTransactionManager")
    public PlatformTransactionManager mysqlTransactionManager(
            @Qualifier("mysqlEntityManagerFactory") EntityManagerFactory entityManagerFactory) {
        return new JpaTransactionManager(entityManagerFactory);
    }
}

Purpose of @Primary in the Example:

  • MySQL as the Default Data Source: In this case, MySQL is designated as the primary data source. So, when Spring needs a DataSource and there are no specific qualifiers or other beans specified, it will default to using the MySQL data source.
  • MySQL as the Default EntityManager: When Spring needs an EntityManagerFactory or TransactionManager and there are no specific qualifiers, it will default to the MySQL versions, because they are marked with @Primary.

Without @Primary:

If you don’t use @Primary, Spring will throw an error like:

No qualifying bean of type 'javax.sql.DataSource' available: expected single matching bean but found 2: mysqlDataSource,postgresDataSource

This error happens because Spring finds two beans of type DataSource (one for MySQL and one for PostgreSQL), and it doesn’t know which one to inject.

PostgreSQL Configuration:

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
    basePackages = "com.example.repository.postgres", // Adjust the package
    entityManagerFactoryRef = "postgresEntityManagerFactory",
    transactionManagerRef = "postgresTransactionManager"
)
public class PostgresDataSourceConfig {

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

    @Bean(name = "postgresEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean postgresEntityManagerFactory(
            EntityManagerFactoryBuilder builder, @Qualifier("postgresDataSource") DataSource dataSource) {
        return builder
                .dataSource(dataSource)
                .packages("com.example.model.postgres") // Adjust the package
                .persistenceUnit("postgres")
                .build();
    }

    @Bean(name = "postgresTransactionManager")
    public PlatformTransactionManager postgresTransactionManager(
            @Qualifier("postgresEntityManagerFactory") EntityManagerFactory entityManagerFactory) {
        return new JpaTransactionManager(entityManagerFactory);
    }
}

4. Define Entity Classes and Repositories

For each data source, you’ll define separate entity classes and repositories.

MySQL Entity:

@Entity
@Table(name = "mysql_table")
public class MysqlEntity {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;

    // Getters and setters
}

MySQL Repository:

public interface MysqlRepository extends JpaRepository<MysqlEntity, Long> {
}

MySQL Controller:

@RestController
@RequestMapping("/mysql")
public class MysqlController {

    private final MyService myService;

    @Autowired
    public MysqlController(MyService myService) {
        this.myService = myService;
    }

    @PostMapping("/save")
    public ResponseEntity<MysqlEntity> saveMysqlEntity(@RequestBody MysqlEntity mysqlEntity) {
        MysqlEntity savedEntity = myService.saveDataToMysql(mysqlEntity);
        return ResponseEntity.ok(savedEntity);
    }
    
    @GetMapping("/all")
    public ResponseEntity<List<MysqlEntity>> getAllMysqlEntities() {
        List<MysqlEntity> entities = myService.getAllMysqlEntities();
        return ResponseEntity.ok(entities);
    }
}

PostgreSQL Entity:

@Entity
@Table(name = "postgres_table")
public class PostgresEntity {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;

    // Getters and setters
}

PostgreSQL Repository:

public interface PostgresRepository extends JpaRepository<PostgresEntity, Long> {
}

PostgreSQL Controller:

@RestController
@RequestMapping("/postgres")
public class PostgresController {

    private final MyService myService;

    @Autowired
    public PostgresController(MyService myService) {
        this.myService = myService;
    }

    @PostMapping("/save")
    public ResponseEntity<PostgresEntity> savePostgresEntity(@RequestBody PostgresEntity postgresEntity) {
        PostgresEntity savedEntity = myService.saveDataToPostgres(postgresEntity);
        return ResponseEntity.ok(savedEntity);
    }

    @GetMapping("/all")
    public ResponseEntity<List<PostgresEntity>> getAllPostgresEntities() {
        List<PostgresEntity> entities = myService.getAllPostgresEntities();
        return ResponseEntity.ok(entities);
    }
}

5. In your MyService, add methods to retrieve all entities:

@Service
public class MyService {

    private final MysqlRepository mysqlRepository;
    private final PostgresRepository postgresRepository;

    @Autowired
    public MyService(MysqlRepository mysqlRepository, PostgresRepository postgresRepository) {
        this.mysqlRepository = mysqlRepository;
        this.postgresRepository = postgresRepository;
    }

    public MysqlEntity saveDataToMysql(MysqlEntity entity) {
        return mysqlRepository.save(entity);
    }

    public PostgresEntity saveDataToPostgres(PostgresEntity entity) {
        return postgresRepository.save(entity);
    }

    public List<MysqlEntity> getAllMysqlEntities() {
        return mysqlRepository.findAll();
    }

    public List<PostgresEntity> getAllPostgresEntities() {
        return postgresRepository.findAll();
    }
}

6. Test with Postman

Save Data (POST)

To save data into MySQL and PostgreSQL, use the following steps in Postman:

  1. URL for MySQL:
POST http://localhost:8080/mysql/save

Sample Body (JSON):

{
    "name": "Sample MySQL Data"
}

2. URL for PostgreSQL:

POST http://localhost:8080/postgres/save

Sample Body (JSON):

{
    "name": "Sample PostgreSQL Data"
}
  • Select POST as the method.
  • Go to the “Body” tab, select “raw” and set the format to JSON.
  • Paste the JSON sample data.
  • Hit the “Send” button.

Retrieve Data (GET)

To retrieve all data from MySQL and PostgreSQL:

  1. URL for MySQL:
GET http://localhost:8080/mysql/all

2. URL for PostgreSQL:

GET http://localhost:8080/postgres/all
  • Select GET as the method.
  • Hit “Send” to retrieve the data.

3. Verify Data

After performing POST requests, you can verify the inserted data by querying your MySQL and PostgreSQL databases directly using tools like:

  • MySQL Workbench for MySQL
  • pgAdmin for PostgreSQL

This completes the testing setup using Postman.

********************************** Completed *****************************

Code Explanation :

In your Spring Boot application with multiple datasources, the annotations @EnableTransactionManagement and @EnableJpaRepositories are used to enable transaction management and to configure JPA repositories, respectively. Here's a detailed explanation of both:

1. @EnableTransactionManagement

  • Purpose: This annotation is used to enable declarative transaction management in Spring. It allows you to annotate methods or classes with @Transactional, so that Spring can automatically manage transactions for those methods.
@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
    basePackages = "com.example.repository.mysql",
    entityManagerFactoryRef = "mysqlEntityManagerFactory",
    transactionManagerRef = "mysqlTransactionManager"
)
public class MysqlDataSourceConfig {
    // Configuration code
}

When @EnableTransactionManagement is added, Spring Boot will manage the creation, committing, and rolling back of transactions for the data sources configured in the application.

  • Declarative Transaction Management: Once you enable transaction management, you can use the @Transactional annotation on methods to indicate that the method should run within a transactional context.

For example:

@Service
public class MyService {
    
    @Transactional
    public void saveDataToMysql(MysqlEntity entity) {
        mysqlRepository.save(entity);
    }
    
    @Transactional
    public void saveDataToPostgres(PostgresEntity entity) {
        postgresRepository.save(entity);
    }
}

In this case, when the saveDataToMysql or saveDataToPostgres methods are called, Spring will automatically manage transactions, so you don't need to manually open or close them.

2. @EnableJpaRepositories

  • Purpose: This annotation is used to scan and enable JPA repositories in the specified packages. It tells Spring where to find the JpaRepository interfaces for a particular data source and also defines the associated EntityManagerFactory and TransactionManager.

Key Attributes:

  • basePackages: Specifies the packages where Spring should scan for repository interfaces.
  • entityManagerFactoryRef: Tells Spring which EntityManagerFactory to use for that particular set of repositories. The EntityManagerFactory is responsible for managing JPA entity managers.
  • transactionManagerRef: Specifies which TransactionManager to use for the repositories, enabling transactional operations.

MySQL Datasource Configuration:

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
    basePackages = "com.example.repository.mysql", // Scans for MySQL JPA repositories
    entityManagerFactoryRef = "mysqlEntityManagerFactory",
    transactionManagerRef = "mysqlTransactionManager"
)
public class MysqlDataSourceConfig {
    // Configuration for MySQL datasource
}
  • basePackages = "com.example.repository.mysql": This tells Spring to scan the com.example.repository.mysql package for JPA repositories that will use MySQL.
  • entityManagerFactoryRef = "mysqlEntityManagerFactory": This points to the MySQL-specific EntityManagerFactory bean defined in the configuration class.
  • transactionManagerRef = "mysqlTransactionManager": This specifies that the transaction manager for these repositories will be the MySQL-specific transaction manager.

PostgreSQL Datasource Configuration:

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
    basePackages = "com.example.repository.postgres", // Scans for PostgreSQL JPA repositories
    entityManagerFactoryRef = "postgresEntityManagerFactory",
    transactionManagerRef = "postgresTransactionManager"
)
public class PostgresDataSourceConfig {
    // Configuration for PostgreSQL datasource
}

Similarly, for PostgreSQL:

  • basePackages = "com.example.repository.postgres": This tells Spring to scan the com.example.repository.postgres package for JPA repositories that will use PostgreSQL.
  • entityManagerFactoryRef = "postgresEntityManagerFactory": Refers to the PostgreSQL-specific EntityManagerFactory.
  • transactionManagerRef = "postgresTransactionManager": Specifies the transaction manager for PostgreSQL-related repositories.

Summary of Both Annotations:

  1. @EnableTransactionManagement:
  • Enables declarative transaction management.
  • You can use @Transactional on methods to manage transactions without writing explicit transaction-handling code.

2. @EnableJpaRepositories:

  • Tells Spring to scan the specified packages for JPA repository interfaces.
  • Associates the repositories with the correct EntityManagerFactory and TransactionManager based on the data source.

In this case, you have two data sources (MySQL and PostgreSQL), so you need to define separate configurations, entity managers, and transaction managers, and @EnableJpaRepositories makes sure that Spring associates each repository with the correct data source configuration.

Tags

Post a Comment

Post a Comment

Previous Post Next Post