How to execute SQL query in parallel using spring boot?

Question

I have multiple charts in a page which will be updated with values from database. I am making an ajax call when chart is initialized, now the request comes to controller class. From controller class I am making multiple call to database using repository class object. How can I make a single request to database with multiple queries and get array of response.

For e.g. Here I have made a 3 different calls to get 3 different value for a chart:

Controller Class and Repository class

How can I combine these request into single one.

There is a concept of Batching in JDBC where we can use addBatch and excuteBatch to do what I wanted, but I am not able to understand if I can achieve the same using Spring batch.

score 1 · Answer 1 · answered Dec 20 '17 at 04:02

1

Sounds like you want to implement these as async queries. This is covered in the Spring Data JPA documentation, I believe. You can use any of these method formats in the current version:

@Async
Future<User> findByFirstname(String firstname);               

@Async
CompletableFuture<User> findOneByFirstname(String firstname); 

@Async
ListenableFuture<User> findOneByLastname(String lastname);

https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#repositories.query-async

(You could also spin off separate Thread instances for each, but I wouldn't advise it)

answered Dec 20 '17 at 04:02

Catchwa

5,845
4
31
57

I believe @ Async is syntactic sugar to spawn a thread from a default thread pool (configured). That doesn't mean this answer is wrong. In fact it is ok. The issue the code will continue past all three of these. I assume the OP wants to wait for the 3 to complete before proceeding. Implications are also that @ Async is a 'fire and forget' Exceptions will be thrown from that 'new' thread and not on this 'launching' one. See this: https://stackoverflow.com/questions/29181057/how-to-check-that-async-call-completed-in-spring - particularly the CompletableFuture.allOf(results).join() . – Randy Jul 18 '23 at 21:27

shazin · Answer 2 · 2017-12-20T05:46:13.177

Statement.addBatch is not supposed to be used for SELECT but for batching INSERTs and UPDATEs

What you need is a bit of a Custom query with UNION to get all the data you need in one sql.

      SELECT COUNT(n.lastUpdatedOn)
      FROM TableEntity n 
      WHERE n.lastUpdatedOn BETWEEN :start1 AND :end1 
      UNION 
      SELECT COUNT(n.lastUpdatedOn)
      FROM TableEntity n 
      WHERE n.lastUpdatedOn BETWEEN :start2 AND :end2 
      UNION 
      SELECT COUNT(n.lastUpdatedOn)
      FROM TableEntity n 
      WHERE n.lastUpdatedOn BETWEEN :start3 AND :end3

And your Repository code.

@Query("SELECT COUNT(n.lastUpdatedOn) FROM TableEntity n WHERE n.lastUpdatedOn BETWEEN :start1 AND :end1 UNION SELECT COUNT(n.lastUpdatedOn) FROM TableEntity n WHERE n.lastUpdatedOn BETWEEN :start2 AND :end2 UNION SELECT COUNT(n.lastUpdatedOn) FROM TableEntity n WHERE n.lastUpdatedOn BETWEEN :start3 AND :end3")
List<Long> countModifiedTimeStamp(@Param("start1") Timestamp start1, @Param("end1") Timestamp end1, @Param("start2") Timestamp start2, @Param("end2") Timestamp end2, @Param("start3") Timestamp start3, @Param("end3") Timestamp end3);

And when you call

List<Long> counts = this.repo.countModifiedTimeStamp(todayStartDay, today, last7days, today, longBack, last7days);

In the returned list you will have today at first element, last7days in second and longBack in third.

How to execute SQL query in parallel using spring boot?

2 Answers2