Location via proxy:   [ UP ]  
[Report a bug]   [Manage cookies]                

DEV Community

Cover image for How To Set Up Multiple Datasources in Spring Boot 3
Writech profile image Antonello Zanini
Antonello Zanini for Writech

Posted on

How To Set Up Multiple Datasources in Spring Boot 3

#java #springboot #sql #mysql

Separating data into different databases is a typical approach in the microservices world. This means that each backend usually needs to access only its limited database. At the same time, a backend application may need to use more than one database. This is why you can set up multiple datasources in Spring Boot.

In this step-by-step tutorial, you will learn how to configure Spring Boot Data 3 to work with many databases.

Getting Started

Suppose your Spring Boot RESTful backend needs to connect to the following two databases:

  • account: A MySQL database containing user data
  • company: A PostgreSQL database storing company data

The databases rely on two different RDBMS technologies and might even be on two different servers.

The account and company databases

For the sake of simplicity, suppose that both databases has a single table. account has the user table and company has the product table. The goal here is to create a Spring Boot app that can use both databases. Let’s learn how to do it!

Keep in mind that this is just a sample scenario and the proposed solution can easily be extended to an indefinite number of databases, regardless of their DBMS technology.

Configuring Multiple Datasources in JPA 3

Follow this step-by-step tutorial and learn how to configure a Spring Boot 3 project to work with different databases in JPA.

Prerequisites

Before getting started, you need to add the following dependencies to your Spring Boot 3 project:

If you are a Maven user, add the lines below to your pom.xml file:

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
</dependency>
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
</dependency>
Enter fullscreen mode Exit fullscreen mode

Otherwise, if you are Gradle user, verify that build.gradle contains:

implementation 'org.springframework.boot:spring-boot-starter-data-jpa'
implementation 'com.mysql:mysql-connector-j'
implementation 'org.postgresql:postgresql'
Enter fullscreen mode Exit fullscreen mode

The last two dependencies are the PostgreSQL and MySQL drivers, respectively. These allow you to connect to MySQL and PostgreSQL servers. Make sure to add the right drivers to your project’s dependencies according to the database technologies your backend relies on.

You now have everything you need to set up multiple datasources in Spring Boot. Time to learn how to do it!

1. Mapping Your Entities

Create an entities package and split it into the following two subpackages:

  • account: Will contain all MySQL database entities
  • company: Will contain all PostgreSQL database entities

Define the User and Product JPA entities and place them in the right subpackage.

2. Defining the Datasource Connection Properties

Add the connection info for the two target datasources to your application.properties file:

spring.datasource.account.url=jdbc:mysql://<MYSQL_DB_URL>/account
spring.datasource.account.username=<MYSQL_USERNAME>
spring.datasource.account.password=<MYSQL_PASSWORD>
spring.datasource.account.driver-class-name=com.mysql.cj.jdbc.Driver

spring.datasource.company.url=jdbc:postgresql://<POSTGRESQL_DB_CONNECTION_URL>/company
spring.datasource.company.username=<POSTGRESQL_USERNAME>
spring.datasource.company.password=<POSTGRESQL_PASSWORD>
spring.datasource.company.driver-class-name=org.postgresql.Driver
Enter fullscreen mode Exit fullscreen mode

Replace all <___> parameters with the right strings. Note that account and company after spring.datasource are two custom options with the same name as the database they refer to.

3. Defining the Datasource Configuration Files

The info defined above can be used to create a database connection through a custom Spring Boot configuration file.

This is what the @Configuration file for the account database looks like:

package com.dbvis.demo.configs;

import com.dbvis.demo.entities.account.User;
import com.zaxxer.hikari.HikariDataSource;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import javax.sql.DataSource;
import java.util.Objects;

@Configuration
@EnableJpaRepositories(
        basePackages = "com.dbvis.demo.repositories.account",
        entityManagerFactoryRef = "accountEntityManagerFactory",
        transactionManagerRef = "accountTransactionManager"
)
@EnableTransactionManagement
public class AccountDataSourceConfig {

    /**
     *  Initialize a DataSourceProperties bean by reading the
     *  datasource info from "spring.datasource.account.*" configs
     *  in application.properties
     */
    @Primary
    @Bean
    @ConfigurationProperties("spring.datasource.account")
    public DataSourceProperties accountDataSourceProperties() {
        return new DataSourceProperties();
    }

    /**
     * Create the datasource for the "account" database
     * using accountDataSourceProperties
     */
    @Primary
    @Bean
    @ConfigurationProperties("spring.datasource.account.configuration")
    public DataSource accountDataSource() {
        return accountDataSourceProperties()
                .initializeDataSourceBuilder()
                .type(HikariDataSource.class)
                .build();
    }

    /**
     * Configure Hibernate's EntityManager object
     * to look for Entity classes inside the "com.dbvis.entities.account" package
     */
    @Primary
    @Bean(name = "accountEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean accountEntityManagerFactory(
            EntityManagerFactoryBuilder builder,
            DataSource dataSource
    ) {
        return builder
                .dataSource(accountDataSource())
                .packages(User.class)
                .build();
    }

    /**
     * Create a transaction manager for the EntityManager object created with
     * the "accountEntityManagerFactory" bean
     */
    @Primary
    @Bean
    public PlatformTransactionManager accountTransactionManager(
            @Qualifier("accountEntityManagerFactory")
            LocalContainerEntityManagerFactoryBean accountEntityManagerFactory
    ) {
        return new JpaTransactionManager(
                Objects.requireNonNull(
                        accountEntityManagerFactory.getObject()
                )
        );
    }

}
Enter fullscreen mode Exit fullscreen mode

This file reads the account connection info from application.properties to instantiate a DataSource bean. Then, it uses to create a valid EntityManager and TransactionManager. These two objects are what you need to handle a connection to a database. Check out the official documentation to learn more about how data access works in Spring Boot.

Note the use of the @Bean name property and @Qualifier annotation to tell Spring Boot what specific bean to use. Also, notice that the scope of the EntityManager gets restricted to the package containing the User entity, which is the account subpackage.

Similarly, you need to define a @Configuration file for company:

package com.dbvis.demo.configs;


import com.dbvis.demo.entities.company.Product;
import com.zaxxer.hikari.HikariDataSource;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
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.transaction.PlatformTransactionManager;
import javax.sql.DataSource;
import java.util.Objects;

@Configuration
@EnableJpaRepositories(
        basePackages = "com.dbvis.demo.repositories.company",
        entityManagerFactoryRef = "companyEntityManagerFactory",
        transactionManagerRef = "companyTransactionManager"
)
public class CompanyDataSourceConfig {

    /**
     *  Initialize a DataSourceProperties bean by reading the
     *  datasource info from "spring.datasource.company.*" configs
     *  in application.properties
     */
    @Bean
    @ConfigurationProperties("spring.datasource.company")
    public DataSourceProperties companyDataSourceProperties() {
        return new DataSourceProperties();
    }

    /**
     * Create the datasource for the "company" database
     * using companyDataSourceProperties
     */
    @Bean
    @ConfigurationProperties("spring.datasource.company.configuration")
    public DataSource companyDataSource() {
        return companyDataSourceProperties()
                .initializeDataSourceBuilder()
                .type(HikariDataSource.class)
                .build();
    }

    /**
     * Configure Hibernate's EntityManager object
     * to look for Entity classes inside the "com.dbvis.entities.company" package
     */
    @Bean(name = "companyEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean companyEntityManagerFactory(
            EntityManagerFactoryBuilder builder
    ) {
        return builder
                .dataSource(companyDataSource())
                .packages(Product.class)
                .build();
    }

    /**
     * Create a transaction manager for the EntityManager object created with
     * the "companyEntityManagerFactory" bean
     */
    @Bean
    public PlatformTransactionManager companyTransactionManager(
            @Qualifier("companyEntityManagerFactory")
            LocalContainerEntityManagerFactoryBean companyEntityManagerFactory
    ) {
        return new JpaTransactionManager(
                Objects.requireNonNull(
                        companyEntityManagerFactory.getObject()
                )
        );
    }

}
Enter fullscreen mode Exit fullscreen mode

The main difference is that this config file refers to the company values in application.properties. Also, its Spring Boot beans are not marked with @Primary. This is because only one DataSource connection must be specified as primary.

If you forget to mark one of the two connections as primary, Spring Boot will fail with the following error:

Parameter 0 of method companyEntityManagerFactory in com.dbvis.demo.configs.AAProductDataSourceConfig required a bean of type 'org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder' that could not be found.
Enter fullscreen mode Exit fullscreen mode

4. Creating the JPA Repositories

It only remains to define some Spring Data repositories. This will help you perform CRUD operations on your tables.

Create a repositories packages. Then, consider splitting the package into as many subpackage as the number of databases you need to connect to.

You can define a UserRepository as below:

package com.dbvis.demo.repositories.account;

import com.dbvis.demo.entities.account.User;
import org.springframework.data.jpa.repository.JpaRepository;

public interface UserRepository extends JpaRepository<User, Long> {}
Enter fullscreen mode Exit fullscreen mode

Note that the JpaRepository does not require any extra configuration. This is because the AccountDataSourceConfig define earlier takes care of setting up everything required for the repository to work.

Similarly, define ProductRepository:

package com.dbvis.demo.repositories.company;

import com.dbvis.demo.entities.company.Product;
import org.springframework.data.jpa.repository.JpaRepository;

public interface ProductRepository extends JpaRepository<Product, Long> {}
Enter fullscreen mode Exit fullscreen mode

You can now use the repository to perform operations on the account and company databases.

5. Putting It All Together

This is the file structure your project should now have:

The file structure of your Spring Boot project

ProductController uses the ProductRepository autowired object to retrieve data from the company database, while the UserController relies on a UserRepository instance.

Et voilà! Your Spring Boot 3 backend can now retrieve or write data to both the account and company databases, respectively.

Exploring Multiple Datasources in a Single Tool With DbVisualizer

If your project grows in complexity and requires multiple database connections, you will need an advanced tool to manage many datasources. This is what DbVisualizer is all about!

DbVisualizer is a complete database client that supports more than 50 different database technologies. This powerful tool enables you to take your database management to the next level. In particular, it comes with advanced data exploration capabilities, in-record data editing support, query optimization functionality, JSON and BLOB data type built-in support, and much more.

DbVisualizer

What are you waiting for? Try DbVisualizer for free today!

Conclusion

In this tutorial, you learn how to configure Spring Boot 3 to work with several databases. In detail, you saw how to use Spring Boot Data 3 to connect to multiple datasources. As shown here, it is not difficult and only requires some special configuration.

Knowing how to connect to multiple databases in Spring Boot is useful, but without the right data exploration tool everything becomes more complex. DbVisualizer is a MySQL, PostgreSQL, Oracle, and Microsoft SQL Server client that support more than other 50 database technologies. Thanks to its advanced features, managing numerous database connections simultaneously has never been easier. Download DbVisualizer for free!

FAQ

Let’s answer some popular questions on multiple database connections with Spring Boot.

How many databases can you connect to in Spring Boot?

You can connect to as many databases as the backend server can handle. There is no real limit to the number of database connections you can create with Spring Boot. At the same time, keep in mind that each connection to a data source takes resources. So, you should try to limit them to the bare minimum possible.

Can the same Spring Boot app use different database technologies?

Yes, Spring Boot Data supports the connections to several DBMS technologies, including both SQL and NoSQL technologies. You can connect to any database, as long as there is a supported Java JDBC driver.

How many database technologies can you use in a Spring Boot app?

Spring Boot Data does not have limits on the number of database technologies you can connect to. You can have a single DBMS, several connections to it, or a heterogeneous scenario with connections to different database technologies.

Can you simultaneously connect to both SQL and NoSQL databases with Spring Boot Data?

Yes, Spring Boot Data supports both SQL and NoSQL connections. Thus, you can set up both a MySQL and a MongoDB connection. For example, you can configure the Spring Boot application to work with SQL and MongoDB repositories.

Can two datasources have the same name in a Spring Boot app?

Yes, two datasources can have the same name. As long as they are on two different database servers and are configured properly at the application layer, Spring Boot can work with two or more datasources with the same name.

Top comments (0)