Advanced Query Techniques in Spring Data JPA

We have explored the basics of Spring Data JPA and how method naming conventions make querying simple. If not, I highly recommend you to follow that blog first. In this second part, we'll dive deeper into more advanced query techniques, allowing you to leverage powerful combinations, sorting, pagination, and string-specific operations for better control over your data retrieval.

We are taking the same example as of Book Entity, having the attribute of the bookName, authorName, isbn, price, publishedDate, published.

Combining Conditions with Keywords

Spring Data JPA allows combining multiple conditions using the And and Or keywords to create more complex queries.

• And: Combines conditions using the AND operator.

Book findByBookNameAndAuthorName(String bookName, String authorName);

This above method will execute a query that retrieves a book from the database where both the bookName and authorName match the values provided in the method's parameters.

If we search for the book title, i.e, bookName, "The Great Book" and the author is "A. qwerty". If such a book exists, it will return that record. Otherwise, it will return null.

The equivalent SQL query that the method would execute

SELECT * FROM Book
WHERE bookName = 'The Great Book' 
AND authorName = 'A. qwerty';

• Or: Combines conditions using the OR operator. The OR operator in method names allows us to retrieve results where at least one of the conditions is met.

List<Book> findByBookNameOrAuthorName(String bookName, String authorName);

This method returns a list of Book objects where either the bookName or authorName matches the specified parameters. It does not require both conditions to be true. If a book matches either the name or the author, it will be included in the result list.

The corresponding SQL query for this method would look like

SELECT * FROM Book
WHERE bookName = 'The Great Book'
OR authorName = 'Albert Hero';

This query will return all books where either the title is "The Great Gatsby" or the author is "George Orwell". If a book matches either condition, it will be part of the result set.

Sorting and Pagination

• Sorting: This allows us to control the order in which records are returned.

OrderBy: We can use OrderBy in our method name to specify sorting. For instance, if we want to get books sorted by their name in ascending order (A to Z), we can use:

List<Book> findByAuthorNameOrderByBookNameAsc(String authorName);

Here’s what each part means:

1. findByAuthorName: This retrieves books based on the author’s name.
2. OrderByBookNameAsc: This sorts the books by their name in ascending order (A to Z). If we want to sort in descending order (Z to A), we can replace Asc with Desc.

• Pagination: This helps us to break down large sets of data into smaller chunks or "pages" to load more efficiently. This is useful when dealing with long lists of records, such as products, search results, or blog posts.

Pageable: Spring JPA provides a Pageable interface that allows us to retrieve a specific page of data with a defined number of records. It supports both pagination and sorting.

Page<Book> findByAuthorName(String authorName, Pageable pageable);

This method will return a page of books, filtered by the author’s name.

How to Use Pageable:

To paginate and sort data, we are passing a Pageable object when calling the method. For instance, if we want to get the second page with 10 books per page, sorted by book name in ascending order:

Pageable pageable = PageRequest.of(1, 10, Sort.by("bookName").ascending());

Page<Book> books = bookRepository.findByAuthorName("The Lost Hero", pageable);

PageRequest.of(1, 10, Sort.by("The Lost Hero").ascending()): This creates a pageable object that requests the second page (page index starts from 0) with 10 books per page, sorted by the bookName in ascending order.

Comparison Keywords

These are used to compare numeric values, dates, or other comparable data types. They help us filter data based on specific comparison criteria.

• IsBefore / IsAfter: These are used for comparing date or time fields.

List<Book> findByPublishedDateAfter(LocalDate date);

The corresponding SQL query for this method would look like

SELECT * FROM Book WHERE publishedDate > '2024-01-01';

• LessThan / GreaterThan / Between:These are used for numeric comparisons.

List<Book> findByPriceBetween(Double startPrice, Double endPrice);

The corresponding SQL query for this method would look like

SELECT * FROM Book WHERE price BETWEEN 10 AND 50;

String-Specific Operations

These operations are used to perform pattern matching and comparisons on string fields, similar to SQL's LIKE clause.

• Containing: Used to perform a LIKE %hunted% operation, i.e., it look for partial matches.

List<Book> findByBookNameContaining(String keyword);

The corresponding SQL query for this method would look like, if the keyword value is hunted.

SELECT * FROM Book WHERE bookName LIKE '%hunted%';

• StartingWith: This is used to check if a string field starts with a specific prefix.

List<Book> findByBookNameStartingWith(String prefix);

The corresponding SQL query for this method would look like, if the prefix value is The Lost.

SELECT * FROM Book WHERE bookName LIKE 'The Lost%';

• EndingWith: This is used to check if a string field ends with a specific suffix.

List<Book> findByBookNameEndingWith(String suffix);

The corresponding SQL query for this method would look like, if the suffix value is Hero.

SELECT * FROM Book WHERE bookName LIKE '%Hero';

Boolean Fields

Spring JPA provides simple keywords for querying boolean fields directly.

• IsTrue / IsFalse: This is used to query boolean fields where the value is either true or false.

List<Book> findByPublishedIsTrue();
List<Book> findByPublishedIsFalse();

The corresponding SQL query for this method would look like

SELECT * FROM Book WHERE published = TRUE;

In and NotIn

These keywords are used to check if a field matches any value in a collection or does not match any value in a collection.

• In: This is used to find entities where the value of a field is included in a collection of values.

List<Book> findByBookNameIn(List<String> names);

The corresponding SQL query for this method would look like, if the list of the String names are these, 'The Lost Hero', 'Alice in Wonderland'.

SELECT * FROM Book WHERE bookName IN ('The Lost Hero', 'Alice in Wonderland');

• NotIn: This is used to find entities where the value of a field is not included in a collection of values. Just an opposite of the In operation.

List<Book> findByBookNameNotIn(List<String> names);

The corresponding SQL query for this method would look like, if the list of the String names are these, 'The Lost Hero', 'Alice in Wonderland'.

SELECT * FROM Book WHERE bookName NOT IN ('The Lost Hero', 'Alice in Wonderland');

Top and First

These keywords are used to limit the number of records returned. We can retrieve the Top N or First N results, typically in combination with sorting.

• Top:This is used to retrieve the top N records from a result set.

List<Book> findTop3ByOrderByPublishedDateDesc();

SELECT * FROM Book ORDER BY publishedDate DESC LIMIT 3;

• First: This is used to retrieve the first N records from a result set, often sorted by a specific field.

List<Book> findFirst5ByOrderByBookNameAsc();

SELECT * FROM Book ORDER BY bookName ASC LIMIT 5;

Lets take an example of this method findFirst5ByOrderByBookNameAsc() and break it down into different parts to make it easier to understand:

1. find: This is the core part of the method, which tells Spring JPA that we want to retrieve data from the database.

2. First5 / First<Number>: This part of the method specifies that we want to get the first 5 records. We can replace the 5 with any number depending upon the choices or requirement. This is useful when we don’t need all the results, just a small number.

3. By: This keyword help to connects the "find" action with the field we want to search by.

4. OrderByBookName: This tell Spring JPA that we want to sort the results based on the bookName field. So, it will organize the books alphabetically (by their names) before choosing the first 5.

5. Asc: This stands for "ascending" order, which means sorting from A to Z (alphabetically). Suppose we wanted to sort from Z to A, we would use Desc (for "descending" order) instead.

In Summary, this method will find books in the database, sort them by their names in ascending order (A to Z), and return the first 5 records that meet this sorting criteria.

Conclusion

In this second part, we explored how Spring Data JPA allows us to create more advanced and flexible queries through method naming conventions. Combining conditions, pagination, sorting, comparison operators, and other powerful features provide the ability to construct complex queries without writing SQL. These conventions follow the "convention over configuration" principle, saving our time while offering immense flexibility.

When working with strings, numbers, dates, booleans, or collections, Spring Data JPA makes querying our database simpler and more intuitive. When our queries become too complex for method conventions, we can always fall back on custom queries using the @Query annotation. But for most use cases, these conventions are all we need to handle queries efficiently.