Spring Data JPA – Query Methods

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

Overview

The Repository interfaces define query methods. A repository interface is a java interface directly or indirectly extended from the Spring Data Repository interface. Spring Data provides pre-defined interfaces like CrudRepository or PagingAndSortingRepository both of which are sub-interfaces of the Repository. There are few pre-defined methods in the Repository interfaces. The important thing is the method names have to follow a standard structure, and if they do, Spring will use it to derive low-level SQL queries at runtime.

Let’s look at both CrudRepository and the PagingAndSortingRepository repositories and the methods they define.

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

CrudRepository

As the name denotes, the CrudRepository defines simple CRUD methods. Let’s have a look at a few essential methods from CrudRepository.

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)

Look at the interface definition. It needs to know about the Entity Bean and Id field to provide specific methods.

  • save: Pass an Entity Bean or a subclass to Entity Bean and it will be persisted. The persisted entity will be returned. Any auto-generated field or default fields will be initialised and returned with the entity.
  • saveAll: Pass an iterable of the Entities and will be saved.
  • findById: Pass the ID field and the matching entity will be returned. It returns an Entity wrapped in an Optional object.
  • existsById: Checks if the given ID exists in the table and returns a boolean.
  • findAll: This method returns all the entities from the table.
  • findAllById: Returns iterable of all the entities that match the given ID.
  • count: The count of entities.
  • deleteById: Find an entity by given ID and delete.
  • delete: Find an entity matching the given entity and delete.
  • deleteAll: If given an iterable of entities all those entities are removed. If called without any argument, all the entities are deleted.

PagingAndSortingRepository

It is a sub-interface of CrudRepository. It has two additional Pagination and Sorting methods and inherited methods from the super-interface.

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 var1): Finds and returns an iterable of all Entities in an order defined by the given Sort object.
  • findAll(Pageable var1): It returns a sequential subset of the matching results which is called a Page.

Using Spring Data Repositories

The Spring Data Repositories need to know about the Entity and the ID fields they will deal with. This information is provided in the repository declaration. Usually, each Entity will have its dedicated Repository.

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

What happens here?

  • Spring knows the Dog is the entity and Long is the type of primary key.
  • DogsRepository inherits all the methods from CrudRepository.
  • Additionally, it defines a custom query method findDistinctFirstByAge.
  • @Repository annotates it as a Repository.
  • Spring provides a proxy implementation of DogsRepository and all of its methods including findDistinctFirstByAge.

How does Spring provide an implementation for the custom query method?
We will learn how Spring parses the query method names into SQL queries in Query Methods to SQL Queries.

Restrict Repository Methods

As per the Java inheritance policy, the sub-interface inherits all the methods from the super-interface. For example, when a DogsRepository extends CrudRepository, it gets all the methods we saw earlier. In some cases, we may not want to expose all of those methods in our Repository.

What if you don’t want to provide delete or save methods on your Repository?
Use @RepositoryDefinition. See the below examples

@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)

What happens here?

  • @RepositoryDefinition tells spring about this Repository.
  • The annotation attributes tell spring about the entity class and primary key.
  • Only the required methods are included in the DogsRepository.
  • With restricted methods, the repository is safer to use.

Alternatively, we can create an Intermediate Interface by specifying @NoRepositoryBean and make it a parent interface for our repositories. For more details on How to Hide or Restrict specific methods in Spring Data Repositories, please visit: Hide Specific Spring Data Repository Query Methods.

Query Methods to SQL Queries

This section will see how Spring derives SQL queries from the method names and how to write more complex queries in the form of query methods. To parse the query method names into the actual queries, Spring uses a particular strategy. If we follow these strategies, we can quickly write the query methods. Let’s see how.

Retrieve Entities

No wonder all of the query methods will be about retrieving or finding certain entities from the table. Spring lets us start the query method names with keywords like findBy, getBy, readBy, countBy, and queryBy.

All of the patterns except countBy are aliases of each other. All of the methods below will behave similarly

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

The countBy returns count of records.

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

Find By Multiple Fields

All the cases we have seen use a single field to find entities. What if we have a combination of more than one field or condition?
See the below examples.

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)

1: Find by matching age and height.
2: Find by age, name, and color.
3: Find by name or age.
4: Find By Name (ignoring case) and color.

Limiting Results

Sometimes we may want to limit the number of records we get back. There is a straightforward way of doing it into Spring Data JPA.

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

The first two lines are identical, and they will return the very first Dog with a matching name.
The third query returns the first 10 Dogs who have the given color.

Let’s find the youngest Dog from the database.

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

More Complex Queries

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

1: Finds Dogs whose name contains the given String.
2: Finds Dogs whose name starts with the given String.

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)

1: The height is less than the given height.
2: Either age is less than the given or height is greater than the given height.
3: The age between the given start age and end age.
4: Where age is greater than or equal to the given age.
5: The date of birth is between the given date range.

Nested Fields

Many times our Entities can have nested fields. In an Entity bean, nested fields can represent a Composite Primary Key or Foreign Keys. Like Employee Has Address. Let’s look at examples of query methods that use nested fields.

Learn More:
Represent Composite Primary Key in Spring Data JPA: Spring Data JPA Composite Key with @EmbeddedId.
Query by not all fields of Composite Primary Key: Spring Data JPA find by @EmbeddedId Partially.

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

Here, the expression ‘AddressZipCode’ is evaluated as address.zipCode. Spring will understand and prepare a Join Query on Employee and Address tables smartly. Spring scans the name of the query method and finds all possibilities of matches. On the other hand, if ‘Employee’ had a field named ‘addressZipCode,’ the search would happen on this field.

What if Employee has addressZipCode and we still want to search by Address.zipCode?
In such a case, Spring prefers the field ‘addressZipCode’ and not the ‘zipCode’ field of the Address class. We can use an underscore to search specifically on the ‘zipCode’ of the Address class.

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

Now Spring correctly replaces the underscore with a dot.

Named Queries (@Query)

Sometimes, the query methods do not fulfil the requirements of all types of queries. The query method may be impossible, or some complex queries may cause the methods to have ugly-looking and lengthy names. In these cases, too, we can still use Spring repositories and provide queries with @Query.

Consider we are querying an Event table and wish to find the largest event_id. Below is a way we can use @Query to provide a query.
The thing to remember: the method name doesn’t have any significance here.

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

Find events having given status from the last n seconds.

@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)

Things to notice:

  • Query starts from FROM. The ‘select‘ part is optional when all columns are expected.
  • :status, :now, and :duration: These are query parameters and are passed as arguments to the method. Notice the method arguments are marked @Param.
  • TIME_TO_SEC & TIMEDIFF: With the custom Queries, we can leverage SQL inbuilt functions.
  • Also, the method name doesn’t have any role.

Summary

In this tutorial, we learned Query Methods, Springs Repository interfaces, and Custom Interfaces. We also learned how Spring converts Query Method names into SQL queries and how to make simple and complex searches using query methods. We saw that we could provide the SQL query using @Query for some complex queries.

This article is part of the Spring Data JPA Learning Series. Please, refer to the below tutorials for further reading.