Spring Data JPA: Replace multiple queries with a single query
Understanding query improvement tricks as a software engineer
Introduction
As software developers, we usually tend to lack database knowledge and how to be efficient while executing database queries. ORM frameworks were built so that they can bring that abstraction where developers do not need to understand databases in detail.
But in reality, we are often interrupted by a database administrator where feedback is to optimize the query.
In this article, we will see such an opportunity where we will get a quick win on our naive query.
Use Case
Our marketing users perform ad-hoc data analysis and have created a list of target users to whom they want to send an email about the upcoming campaigns. They have uploaded this file to a remote server using UI.
There is a scheduler that runs sendEmail job, this job is a spring boot rest endpoint that is invoked by cron.
Before sending an email to a customer, this job checks if the user_email exists in the database ( to avoid sending emails to the non_customer or customer who is not in the OLTP database or not subscribed to get an email marketing campaign).
Then we finally send an email to the customer who exists in the database and a file uploaded by the marketing team both.
📢 Consider becoming a paid subscriber for as low as $2.5/mo (with an annual subscription) and support the work :)
Not convinced? Check out the details of the past work
Setup
We will set up a simple spring boot app with postgres as a database. Please follow this article to set up one.
Entity
Basically, we have one table account in the database, our entity looks like the one below.
Controller
The controller is simple to get an endpoint that invokes the email sender logic.
Here the response is void but in reality, we can return a boolean response about success or failure.
Repository
In the repository interface, we have added one method to check if the given email exists in the table or not.
All we have to do is to define the method that’s it, no implementation is needed, spring data JPA takes care of the implementation internally.
