Dynamic DataSource Routing : Spring Boot

Dynamic DataSource Routing: Spring Boot

Multi data source routing(DB per satellite)

Sometimes we need to create a web application that can connect to multiple databases. We might need to connect multiple databases based on the client, customers so on.

Spring provides AbstractRoutingDataSource class which we can extend and achieve the data source routing dynamically and we can do it easily on Spring Boot.

Use cases

Sometimes for security reasons, performance, scalability, or isolation, we need data source routing such as MASTER and REPLICA DB where REPLICA DB can only be read-only.

Another use case is the real-time processing data of satellites. These are very huge volume data sets and created very frequent on an interval.

Let’s take the satellite SARAL(https://en.wikipedia.org/wiki/SARAL) and Aryabhata (https://en.wikipedia.org/wiki/Aryabhata_(satellite)) example which pushed the data on a very frequent interval of time. Suppose we have an application that can retrieve the data of the satellite and each satellite has its own database. To achieve this, we can take the help of Data source routing.

Satellite Data (ftp://scatsat1:%22%22@ftp.mosdac.gov.in/2020/JUL/L3BT/). Data copied from https://www.mosdac.gov.in/open-data

Each satellite creates a huge set of files on their FTP server (https://www.mosdac.gov.in/open-data) and based on the type, we want to retrieve the files.

In this article, we will provide the steps to configure multiple data sources dynamically using Spring Boot & JPA.

For the complete example find the code from GitHub.

Maven Dependencies

Here is the list of maven dependencies. We used MySQL Database and Spring MVC.

Find it from GitHub.

DataSource Router :  AbstractRoutingDataSource, Spring 2.0.1 introduced which acts as an intermediary. However, the real data source can be determined dynamically at run time based on a lookup key. In our example, we will use the notion of a DatabaseContext which has the following implementation. Find it from GitHub.Take a DataBaseContextHolder which is a utility class that can set the data source context dynamically which has the following implementation. Find it from GitHub.

Finally, we will define our MultiRoutingDataSource which will extend the spring provide AbstractRoutingDataSource and override the method. Find it from GitHub.

Configuring Datasources

Let’s configure the data source in the application.properties file

Data Source Configuration :

In this example, we created three (3) data source bean such as mainDataSource,arayabhatDataSource & saralDataSource which can be decided based on the type of data source requested dynamically. Find it from GitHub.

Testing :

In this example, when we will click on File Storage-Saral satellite, it will access the satellite SaralData and will navigate the satellite Saral data related file server of ftp://scatsat1:%22%22@ftp.mosdac.gov.in/ and similar to Aryabhata(But for Aryabhata satellite, we have used same FTP server with different data set for illustration purpose only).

Access files example :

Satellite Data (ftp://scatsat1:%22%22@ftp.mosdac.gov.in/2020/JUL/L3BT/). Data copied from https://www.mosdac.gov.in/open-data

Conclusion

Spring boot provides additional configuration support which we can do if we want to configure multi-data source connectivity dynamically.

For the complete example find the code from GitHub.

Spring Data JPA : Query by Example

Spring Data JPA : Query by Example(QBE)

If you are a java developer and if anyone asks to you to create an enterprise application, suddenly your head will move left & right around the JAVA orbit and Spring/Spring Boot framework will hit your brain. Of course others might be, but it is often. Am i right ?

Spring Boot  ecosystem is an evolution of Spring framework which helps to create stand-alone, production-grade Spring based applications with minimal effort.

Lucky-draw Coupons 

In a festival time, my manager asked me to implement the following use case .

1.  You have to find all the customers whose first name ends with some given character and to send some random lucky draw coupon code which they can redeem on a e-commerce site. 

Yes, i can use native DB query ; but he doesn't give green signal !!! for db query, because later he has to do some intermediate operations too and he ordered me to achieve it using spring boot . 

You might also be interested in the related tutorials:

• Cyclic barrier in Java concurrency
• Do you know Precision and Scale of a Number ?
• Monetary calculations in Java Using float or double !!!

Spring Data JPA

It facilitates the implementation of JPA based repositories. It makes the life easier to build spring based applications that uses data access technologies.

Spring Data JPA Query By Example

If you are using Spring Data JPA, you might have used JpaRepository of spring data JPA. If you check the JpaRepository.class you will find the below signature :

public interface JpaRepository<T, ID>   extends PagingAndSortingRepository<T, ID>, QueryByExampleExecutor<T> {}

See, it extends QueryByExampleExecutor .So what is this and why it has extended ?

Query By Example(QBE) is a developer-friendly querying technique which facilitates to write dynamic query without any specific query-language.

It allows us to execute queries based on an Example entity instance. The field of the Example instance needs to be populated with the desired criteria values. 

In Spring Data JPA we can use org.springframework.data.domain.Example instance which takes an entity instance (called 'probe' in this context). E.g

//get all the customers whose first name is ending with 'dra' 
var customers = Customers.builder().firstName(ending).build();

 var matcher = ExampleMatcher.matching()

    .withIgnoreNullValues()

    .withMatcher("firstName", match ->                  match.endsWith().ignoreCase(true));

 var example = Example.of(customers, matcher);

By default, fields having null values are ignored in the underlying query, so above Example will be equivalent to the following JPQL:

SELECT t from Customer c where c.firstName = '%dra';

Examples

I am going to implement the same above mentioned use case.

1. Get All customers



2. Get all customers whose first name ends with 'dra'



3. Get all customers whose last name ends with 'das'



4. Get all customers whose first name equals 'Rosalin'



5. Get all customers whose wallet balance is 2000

Let's start with a simple example:

Entity : Customer.java

@Entity

@Table(name = "customers")

@Data

@NoArgsConstructor

@AllArgsConstructor

@EqualsAndHashCode

@ToString

@Builder

public class Customers implements Serializable{

 private static final long serialVersionUID = 34131213L;

 @Id

    @GeneratedValue(strategy = GenerationType.AUTO)

    private Long id;

 @Column(name = "first_name", nullable = false)

    private String firstName;

 @Column(name = "last_name")

    private String lastName;

 @Column(name = "balance")

 private Long   walletBalance;

}

Service : CustomerService.java

public List<Customers> findByFirstNameEnding(String ending) {

  

  var customers = Customers.builder().firstName(ending).build();

  var matcher = ExampleMatcher.matching()

    .withIgnoreNullValues()

    .withMatcher("firstName", match -> match.endsWith().ignoreCase(true));



  var example = Example.of(customers, matcher);

  return (List<Customers>) customerRepository.findAll(example);

 }

Find the complete code on github.

In this tutorial, we have used Spring Data JPA Query By Example technique to generate queries. You might also be interested in the related tutorials:

• Cyclic barrier in Java concurrency
• Do you know Precision and Scale of a Number ?
• Monetary calculations in Java Using float or double !!!