How to connect to Multiple databases with Spring Data JPA

Usually, when you build an application you intend to connect to a single database. Its often not required to connect to multiple data sources unless you working on an ETL kind of project. In this article, we will see how you can connect to Multiple databases with Spring Data JPA

Softwares used

  • Spring Boot 2.0.2.RELEASE
  • Spring Data JPA
  • Java 8
  • Oracle XE
  • MySQL
  • Maven
  • Eclipse

To simulate the real-life scenario, in our demonstration we will connect to two databases Oracle and MySQL.

Maven Dependencies

Below are some of the key maven dependencies that we have used.

Now, we need to make a note here. Oracle jars are not available through maven. So you need to download the jar and install in your local repository before you build your project.

You can read this article – How to add Oracle JDBC driver in your Maven local repository

We will define database details in our properties file. The file will look like below

Now when we define two data source beans, Spring needs to know which one is primary and which bean is treated as secondary. If you don’t define them Spring will fail to start your application as there will be two beans of the same type it tries to make ready for injection.

You can define any bean primary with annotation @Primary

We will configure our data source bean in the similar way we have done in one of the previous articles.

Read – Java Web Application Starter Template with Spring Boot

Primary Datasource Configuration

Please take a look at how we have added @Primary annotation on the data source, entity manager factory. Also, we have given references to these beans with the qualifier in the @EnableJpaRepositories annotation.

Secondary Datasource Configuration

The secondary dao config doesn’t have @Primary annotation but the bean qualifiers have to be uniquely referenced.

So far we have completed our database configuration now let us use them. I have defined two tables in each of the database TblOracle and TblMysql. Each of these tables has the same structure.  Below are their corresponding entities

TblOracle

TblMysql

Corresponding persistence.xml

For our demo, we will define a simple rest controller and will use the Spring Data JPA Repositories in them.

We will define repositories as

These will be referenced in an example as

Above is the usage we have seen using JPA repositories.  What if we want to use the traditional way by defining entity manager?

Define Entity Manager

See how we have autowired the factories, we need to provide the unique qualifier we have set in the config.

Usage

Check below screen to see them in actionMultiple databases with Spring Data JPA

Multiple databases with Spring Data JPA

Conclusion

In this article, we have seen how it is easy to connect to Multiple databases with Spring Data JPA. We have seen what precautions we need to take while doing the configuration for multiple databases.

The complete code is available at our GitHub repo. Please feel free to download and try.

Download Code

Add a Comment

Your email address will not be published. Required fields are marked *