Spring Data JPA - Query Methods

Learn about writing Spring Data JPA Query Methods and how Spring resolves those query methods into actual SQL statements.

Tutorial Contents

Overview

In Spring Data JPA, a repository interface is a Java Interface that directly or indirectly extends from the Spring Data Repository interface, and it defines query methods. The Spring Data provides pre-defined interfaces like CrudRepository or PagingAndSortingRepository having pre-defined query methods.

The Spring Data JPA framework implements these query methods during an application startup. Based on the name, return type, and arguments of the query methods, Spring generates an SQL query and related JDBC code behind the scene. This feature frees the developer from dealing with low-level SQL queries and a lot of boilerplate code.

Learn the basics of JPA and Spring Data Jpa
Read this:

Spring Data JPA Repositories

CrudRepository

As the name denotes, the CrudRepository defines simple query methods to perform standard Create, Read, Update, and Delete operations.
Learn more: about using CrudReository from Scratch at Spring Boot with Spring Data JPA.

public interface CrudRepository<T, ID> 
    extends Repository<T, ID> {

  <S extends T> S save(S var1);
  <S extends T> Iterable<S> saveAll(Iterable<S> var1);
  Optional<T> findById(ID var1);
  boolean existsById(ID var1);
  Iterable<T> findAll();
  Iterable<T> findAllById(Iterable<ID> var1);
  long count();
  void deleteById(ID var1);
  void delete(T var1);
  void deleteAll(Iterable<? extends T> var1);
  void deleteAll();
}Code language: Java (java)

The snippet shows the CrudRepository interface definition. The T is the type of Entity, and the ID is the datatype of its primary key.

Want to learn using Java Persistence API (JPA) with Spring and Spring Boot ?
Read this:

PagingAndSortingRepository

The PagingAndSortingRepository interface is a sub-interface of CrudRepository and defines two additional methods for pagination and sorting-related queries.
Learn more: Pagination and Sorting with Spring Data JPA.

@NoRepositoryBean
public interface PagingAndSortingRepository<T, ID> 
    extends CrudRepository<T, ID> {
  
  Iterable<T> findAll(Sort var1);
  Page<T> findAll(Pageable var1);
}Code language: Java (java)

findAll(Sort): Returns all the Entity beans in the order specified by the Sort instance.
findAll(Pageable): As per the given Pageable criteria, it returns a sequential subset of Entity beans.

How to Create Spring Data Repository?

Using @Repository

A repository interface class must have @Repository annotation and extend one of the Spring Data Repository interfaces.

@Repository
public interface DogsRepository 
    extends CrudRepository<Dog, Long> {
  Dog findDistinctFirstByAge(int age);
}Code language: Java (java)

The repository interface here handles a Dog entity with a primary key of Long type. On top of the overridden methods, the interface additional query method, findDistinctFirstByAge(age). Spring Data JPA Framework, at runtime, provides an implementation for all the methods in this interface, including the overridden ones.

Using @RepositoryDefinition

The DogsRepository inherits all the methods from the CrudRepository interface. However, sometimes we may not want to expose or support a particular action on an Entity. For example, we may wish to disallow the deletion of the Dog entities in our database.

Instead of extending our Repository interface from one of the Spring Data Repository interfaces, we can annotate it with @RepositoryDefinition to define only a specific set of methods in the interface.

@RepositoryDefinition(domainClass = 
    Dog.class, idClass = Long.class)
public interface DogsRepository {

  Optional<Dog> findById(Long var1);
  boolean existsById(Long var1);
  Iterable<Dog> findAll();
  Iterable<Dog> findAllById(Iterable<Long> var1);
  Dog findDistinctFirstByAge(int age);
}Code language: Java (java)

The DogsRepository interface in this snippet supports only the methods we explicitly added to it. A user of this interface won’t be able to use it to execute any other action on the Entity.

For more details on How to Hide or Restrict specific methods in Spring Data Repositories, please visit: Hide Specific Spring Data Repository Query Methods.

Using Spring Data JPA Query Methods

This section outlines different types of query methods and their naming structure. Once we understand how Spring uses query method names to derive low-level SQL queries, we can define custom query methods easily.

Query Methods to Retrieve an Entity by Primary Key

Spring Data Repository allows us to begin our query method names with keywords findBy_, getBy_, readBy_, and queryBy_. All these keywords are aliases to each other, and the query methods having these keywords can find a single Entity by one or more fields.

All the query methods in the following snippet are similar. They find a single Entity based on the primary key.

Optional<Dog> findById(Long id);
Optional<Dog> readById(Long id);
Optional<Dog> getById(Long id);
Optional<Dog> queryById(Long id);Code language: Java (java)

Query Methods to Retrieve Multiple Entities

We can find entities by more than one field using the exact keywords and logical operators. Please note that the return type of the query methods is a collection, as there could be more than one entity matching the criteria.

List<Dog> findByAgeAndHeight(Integer age, Double height);
List<Dog> findByAgeAndNameAndColor(Integer age, String name, String color);
List<Dog> findByNameOrAge(String name, Integer age);
List<Dog> findByNameIgnoreCaseAndColor(String name, String color);Code language: Java (java)

Query Methods to Count Entities

If a query method name begins with a keyword countBy_, the query returns the count of the Entity beans by one or more fields.

Integer countByName(String name);
Integer countByNameAndLastName(String name, String lastName);Code language: Java (java)

Query Methods to Find the First N Entities

The Spring Data Repository query methods allow us to retrieve the first N entities that match the criteria.

Find the first or the topmost Entity that matches the criteria. Both of the following query methods are similar.

Dog findFirstByName(String name);
Dog findTopByName(String name);Code language: Java (java)

Alternatively, we can specify the number of entities we want to retrieve. For example, find the first ten entities that match the criteria.

List<Dog> findTop10ByColor(String color);Code language: Java (java)

In the real world, we can use this feature and the proper criteria to get meaningful information from the database. For example, finding the youngest Dog from the table.

Dog findTopByOrderByBirthDateDesc();Code language: Java (java)

Complex Queries with Query Method – Comparison Queries

It is essential to learn that we can also find entities using more complex search criteria; for example, it begins with, contains, greater than, less than, etc.

Example of finding entities that contain or begin with the given search query string.

List<Dog> findByNameContaining(String subName);
List<Dog> findByNameStartingWith(String subName);Code language: Java (java)

The following snippet contains examples of query methods that use more complex logical search criteria.

List<Dog> findByHeightLessThan(double height);
List<Dog> findByAgeLessThanOrHeightGreaterThan(Integer age, double height);
List<Dog> findByAgeGreaterThanAndAgeLessThan(Integer ageStart, Integer ageEnd);
List<Dog> findByAgeGreaterThanEqual(Integer age);
List<Dog> findByDateOfBirthBetween(Date start, Date end);Code language: Java (java)

Query Methods to Find by Nested Fields

In an Entity bean, nested fields can represent a Composite Primary Key or Foreign Keys. For example, an Employee Has an Address.

Learn More:
Represent Composite Primary Key in Spring Data JPA: Spring Data JPA Composite Key with @EmbeddedId.

Query by only a few fields of the Composite Primary Key: Spring Data JPA find by @EmbeddedId Partially.

Let’s look at examples of query methods that use nested fields.

List<Employee> findByNameAndAddressZipCode(Integer zipCode);Code language: Java (java)

If The Employee entity contains an Address, Spring will evaluate the ‘AddressZipCode‘ expression as ‘address.zipCode‘. Alternatively, if the Employee entity has a column ‘addressZipCode‘, Spring will search on that field.

What if the Employee contains an Address and a field ‘addressZipCode‘?
In such a case, Spring prefers the field ‘addressZipCode‘ and not the ‘zipCode‘ field of the Address class. We can use the underscore expression to explicitly instruct Spring to use the nested field instead.

List<Employee> findByNameAndAddress_ZipCode(Integer zipCode);Code language: Java (java)

We should use the underscore to resolve ambiguity or to make the method names more explicit.

Using Spring Data Named Queries (@Query)

Spring Data Repository query methods are great in keeping the underlying databases and low-level SQL queries under the hood. However, sometimes expressing a complex SQL query in the form of a query method is impossible, or if possible, the query method name may become very ugly and lengthy. Also, we may want to use a database-specific native SQL function or feature.

Spring Data allows @Query annotation for all such requirements where we can write a generic (JPQL) or a database-specific native (SQL) query.

The following is an example of a Spring Data Repository @Query annotation.

@Query(" SELECT MAX(eventId) AS eventId FROM Event ")
Long lastProcessedEvent();Code language: Java (java)

We can also pass parameters to the Spring Data @Query annotation and use them in the SQL query.

@Query("""
       FROM Event 
       WHERE status = :status 
       AND TIME_TO_SEC(TIMEDIFF(:now, lastUpdateTs)) >= :duration 
       """)
List<PreviewEvent> findByStatusAndDuration(
    @Param("status") String status, 
    @Param("duration") Integer duration, 
    @Param("now") Timestamp now);Code language: Java (java)

Please note that the query doesn’t have the SELECT statement. Spring allows us to skip the SELECT part when we read the complete entity – all columns. Also, when we use the @Query annotation, the query method name doesn’t play any role.

When we want to write a database-specific native SQL query, we can instruct the @Query annotation by setting ‘nativeQuery = true‘.

Summary

This tutorial taught us how to use the Spring Data Repository interface, repository query methods, and wiring custom queries using the @Query annotation. We understood that when we use query methods, Spring Data takes care of the low-level SQL queries and JDBC connections and parses the resultsets into Java objects. We can execute different queries on the database by manipulating the query method names.

Lastly, we understood that Spring also allows us to provide JPQL or native SQL queries using @Query annotation, and we can use that for complex or database-specific queries.

Spring Data JPA – Query Methods