Open in app

Sign in

Write

Sign in

Switch between multiple databases with a custom annotation in Spring Boot

Sushain Dilishan
Sysco LABS Sri Lanka
Published in
6 min readJun 10, 2022

Introduction

While doing back end development a very common scenario that we face is to enable multiple data sources to be connected to a single service. This may either be read replicas or completely different databases. While you go through the internet, you will find multiple ways to handle this problem. For example: creating multiple data source beans and EntityManagers etc. But all these methods require lot of maintenance and boilerplate code every time we need to introduce a new database.

Now that we understand the problem, let’s look at how we are going to tackle this with our annotation approach.

In order to make this happen we will be using two concepts. One is AbstractRoutingDataSource provided by Spring framework. By implementing this component you have to configure two key things.

  • A map which has all your datasources.
  • A ThreadLocal variable Lookup Key which will be used to identify the data source to be used in the runtime.

After we implement AbstractRoutingDataSource, then we need to create our custom annotation. After we implement our annotation, we will be using Aspect Oriented Programming (AOP) to switch between our databases dynamically by updating the Lookup Key of our implemented AbstractRoutingDataSource class.

Aspect Oriented Programming (AOP)

Before getting into implementation, it is important to understand AOP and how it enables us to do this magic of switching between multiple data-sources dynamically within a single request.

AOP is a programming paradigm that aims to increase modularity by allowing the separation of cross-cutting concerns. It does this by adding additional behavior to existing code without modifying the code itself.

As quoted above, in our implementation AOP allows us to get rid of all the boiler plate code and make our cross-cutting concern - in our case it is the switching between databases modularized into one component in the application.

AOP allows us to specify a Pointcut - in our case, the custom annotation we create. After we specify our Pointcut we have to implement our Advices.

Advice is the actual action we need to perform on this Pointcut, in our case this is updating our Threadlocal variable which is the lookup key for our databases. We will be implementing two types of advices they are:

Before - which will be executed before the method execution. This will get the value of the annotation passed, and update the lookup key for database switching.

After - which will be executed after the method execution. This will be setting the default lookup key after the method is executed.

Aspect Oriented Programming

As we got a glimpse of power in Aspect Oriented Programming (AOP) we can jump into the implementation!

Generate the project with Spring Initializr

First, we need to initialize a project with Spring Initializer.

This can be achieved by visiting http://start.spring.io/

Make sure to select the dependencies below:

  • Spring WEB: Product of the Spring community focused on creating document-driven Web services.
  • Lombok: Java library that makes the code cleaner and gets rid of the boilerplate code.
  • Spring Data JPA: Library used to store data in SQL stores with Java persistence api using Spring and Hibernate.

After initializing the project, we will be using MySQL-Connector dependency on the pom.xml file.

<dependencies>
   <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>org.projectlombok</groupId>
      <artifactId>lombok</artifactId>
      <optional>true</optional>
   </dependency>
   <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-test</artifactId>
      <scope>test</scope>
   </dependency>
   <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <scope>runtime</scope>
   </dependency>
</dependencies>

Setup databases using MySQL

Run the scripts below in MySQL. For this demo, we will be using a student database and a college database.

CREATE DATABASE  IF NOT EXISTS `student_database`;
USE `student_database`;--
-- Table structure for table `student`
--DROP TABLE IF EXISTS `student`;CREATE TABLE `student` (
  `studentId` int(11) NOT NULL AUTO_INCREMENT,
  `studentName` varchar(45) DEFAULT NULL,
  `studentAge` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`studentId`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;--
-- Data for table `student`
--INSERT INTO `student` VALUES 
    (1,'ranil','18'),
    (2,'anil','17'),
    (3,'sunil','16');CREATE DATABASE  IF NOT EXISTS `college_database`;
USE `college_database`;--
-- Table structure for table `student`
--DROP TABLE IF EXISTS `college`;CREATE TABLE `college` (
  `collegeId` int(11) NOT NULL AUTO_INCREMENT,
  `collegeName` varchar(45) DEFAULT NULL,
  `collegeLocation` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`collegeId`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;--
-- Data for table `student`
--INSERT INTO `college` VALUES 
    (1,'Central College','piliyandala'),
    (2,'mahanama college','colombo'),
    (3,'royal college','colombo');

1.) Let’s define our properties first in application.properties file

What you can see is that we are using a naming convention when we are declaring our database connections in the property file.

spring.database-names.list - contains our database names that will pass to our annotation.

then our database properties will follow spring.datasource.{name_of_database} convention.

You can configure any number of databases here using this convention and the data sources will be added in our application.

server.port=8080

spring.jpa.database=mysql
spring.jpa.hibernate.ddl-auto=update
spring.jpa.generate-ddl=true
spring.jpa.show-sql=true

#Database Name List
spring.database-names.list =student,college

#student database credentials
spring.datasource.student.url=jdbc:mysql://localhost:3306/student_database
spring.datasource.student.username=root
spring.datasource.student.password=test
spring.datasource.student.driver=com.mysql.jdbc.Driver

#college database credentials
spring.datasource.college.url=jdbc:mysql://localhost:3306/college_database
spring.datasource.college.username=root
spring.datasource.college.password=test
spring.datasource.college.driver=com.mysql.jdbc.Driver

2.) Next, let’s create our AbstractRoutingDataSource implementation

Here, we implement the AbstractRoutingDataSource and override the determinCurrentLookupKey().

You will see that we have created a thread local variable to store the database key. This is what we will be updating with our annotation to determine which data source to use.

We will be initializing this class and creating a bean in our Configuration class.

AbstractRoutingDataSource has provided us with some key methods we use here in setting the default DataSource, and to set the target DataSources as well.

3.) Creating the Configuration class

In our configuration class, we have initialized our previously created AbstractRoutingDataSourceImpl by passing the generated DataSource map by GetTargetDataSources() method and our default database preference.

Additionally, have created our EntityManagerFactory bean here as well.

We will be using Scope as prototype for our beans, since we will be switching between databases dynamically and updating properties.Singleton won’t help us here!

To understand more on what is EntityManagerFactory- Click here

4.) Creating the Custom Annotation

Here, we use target element type as method since this annotation will be used in method level to switch between databases.

Also, this annotation has one argument which is the database name which we want to switch to.

5.) Creating an Aspect

We are using AOP here because we will be applying Pointcuts to our annotation. Through our Aspect, we will be changing the lookupkey of AbstractRoutingDataSource and after the Pointcut, we will be restoring the database to default.

Now that everything is set, what remains it to create the Entity classes, Repository, Service and Controller layer to extract data from the data-sources.

6.) Creating Entity classes and Repository Interfaces

Student.java

package com.dynamicdatasource.demo.model;

import lombok.Data;

import javax.persistence.*;

@Data 
@Entity 
@Table(name = "student")
public class Student {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "employeeId")
    private int id;
    @Column(name = "studentName")
    private String name;
    @Column(name = "studentAge")
    private String age;
}

College.java

package com.dynamicdatasource.demo.model;

import lombok.Data;

import javax.persistence.*;

@Data
@Entity
@Table(name = "college")
public class College {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "collegeId")
    int id;
    @Column(name = "collegeName")
    String name;
    @Column(name = "collegeLocation")
    String location;
}

Response.java

package com.dynamicdatasource.demo.model;

import lombok.Data;

import java.util.List;

@Data
public class Response {

    List<Student> students;
    List<College> colleges;
}

CollegeRepository.java

package com.dynamicdatasource.demo.repository;

import com.dynamicdatasource.demo.model.College;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface CollegeRepository extends JpaRepository<College,Integer> {

    List<College> findAll();
}

StudentRepository.java

package com.dynamicdatasource.demo.repository;

import com.dynamicdatasource.demo.model.Student;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface StudentRepository extends JpaRepository<Student,Integer> {

    List<Student> findAll();
}

7.) Creating the Service

Key things to notice here is that we have used our custom built annotation — “@SwitchDataSource” to switch between student and college databases.

6.) Creating the Controller

Nothing fancy here, simply for the controller to get our response to test our database switching.

7.) Setup and testing the application

Now that everything is setup, I will be using Postman to send a request to my application.

Final Response

And just like that, you can see that we have generated a response by switching between 2 data-sources!!.

End Notes

In this article we covered multiple concepts such as:

  • Aspect Oriented Programming (AOP)
  • Creating a custom annotation
  • How to utilise AbstractRoutingDataSource for Dynamic switching of data-sources

Utilizing the above mentioned points, we created a cool annotation to dynamically switch between data-sources easily. As mentioned in the introduction as well this will remove large amount of boilerplate code and makes plugging new data sources easier as well. You can configure any number of databases and switch between them using this implementation.

Source Code is available here - click here

Thanks for reading! You can find me on LinkedIn as well!

References

https://www.baeldung.com/spring-abstract-routing-data-source

https://docs.spring.io/spring-framework/docs/4.3.15.RELEASE/spring-framework-reference/html/aop.html

Java
Spring Boot
Multiple Datasources
Hibernate
Aop

--

--

Sushain Dilishan
Sysco LABS Sri Lanka

Written by Sushain Dilishan

58 Followers
Writer for

Senior Software Engineer at Sysco

Help

Status

About

Careers

Press

Blog

Privacy

Terms

Text to speech

Teams