Multiple Database Configuration in Spring Boot

 Databases: 

• MySQL Database
• Oracle

In this demo, we would only use two datasources: MySQL and Oracle. 

Before you start: Create a database in MySQL called "mydb".

How it works:

It would be similar to your normal application but with a few changes

1. The application.properties file would now contain multiple setting each for a different datasource.
2. You need configuration classes, one for each of the data sources. One of the configuration classes must be set as Primary using the @Primary annotation.
3. The configuration classes would contain mandatory methods:
   • DataSource method
   • LocalContainerEntityManagerFactoryBean method
   • PlatformTransactionManager method
4.  We need a model/Entity class and a repository interface.

Step1: Create a SpringBoot project using SpringInitializer and add required dependencies like web, devtools etc.

<dependency>

    <groupId>org.springframework.boot</groupId>

    <artifactId>spring-boot-starter-data-jpa</artifactId>

</dependency>

<dependency>

    <groupId>org.springframework.boot</groupId>

    <artifactId>spring-boot-starter-web</artifactId>

</dependency>

<dependency>

    <groupId>mysql</groupId>

    <artifactId>mysql-connector-java</artifactId>

    <scope>runtime</scope>

</dependency>

<dependency>

    <groupId>oracle</groupId>

    <artifactId>ojdbc6</artifactId>

    <version>11.2.0</version>

    <scope>system</scope>

    <systemPath>C:/oraclexe/app/oracle/product/11.2.0/server/jdbc/lib/ojdbc6.jar</systemPath>

</dependency>

Step 2: Create Entity class as Employee.java and Student.java under Entity package.

Make sure to include All args- constructors as well as no-args constructors as well as getters and setter methods. You can also include toString method as well.

Step 3: Create the Controller class to call the APIs from the mentioned endpoints.

You can create a separate controller class or a combined controlled class. Here I have created a separate Controller class as EmployeeController.java and StudentController.java.

Step 4: Create a Repository interface each for the different Entity class that we have created. We need to create StudentRepository.java and EmployeeRepository.java that will ultimately extends JPARepository.

Step 5: Create a service layer to act as a mediator between Controller and Repository layer to fetch data from the database. 

Step 6: In the application.properties files, we have to give different datasource properties as shown below:

You can see the setting for the two datasources. Take note that the datasources are differentiated based on the prefixes. Just be sure to provide valid name.

Step 7: Now the most important part. We have to create the Database configuration file to connect with the required database.

EmployeeConfiguration.java and StudentConfiguration.java.

i) DataSource method

Take note that in this listing the function is annotated with the @Primary annotation.

ii) LocalContainerEntityManagerFactory

iii) PlatformTransactionManager

Step 8: Similarly we have to do for StudentConfiguration.java file. Just don't forget to remove the @Primary annotation from there as we can't have two primary datasource.

The complete contents of both the file is available at the github location.

Step 9: Run the application and call the API based on the Employee or Student data and you will see the data insertion/retrieval will happen from both the databases.

Code link: SpringBoot Multiple DB