JavaSpringTechnology

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.

This tutorial assumes you already have an understanding on JPA, Spring Data JPA and Spring Boot and it doesn’t cover basic setup like datasource configuration and dependency management.
If you are looking for using Spring Data JPA in a Spring Boot project, please read: Spring Boot with Spring Data JPA.

1 Overview

Query methods are defined in Responsitory interfaces. A repository interface is a java interface directly or indirectly extended from Spring Data org.springframework.data.repository.Repository(note: this is a marker interface). Spring Data provides pre-defined interfaces like CrudRepository or PagingAndSortingRepository both of them are sub-interfaces of 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 query at runtime.

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

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

2 CrudRepository

As the name denotes the CrudRepository defines simple CRUD methods. Let’s have a look at few important 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();
}

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 into the table and returns boolean.
  • findAll: This method returns all the entities from 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 to 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.

3 PagingAndSortingRepository

It is a sub-interface of CrudRepository. It has two additional methods for Pagination and Sorting along with 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);
}
  • 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 as a Page.

4 Using Spring Data Repositories

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

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

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 proxy implementation of DogsRepository and all of its methods including findDistinctFirstByAge.

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

5 Restrict Repository Methods

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

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);
}

What happens here?

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

6 Query Methods to SQL Queries

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

6.1 Retrieve Entities

No wonder all of the query methods will be all about retrieving or finding certain entities from the table. Spring lets us starting the query method names by keywords like find..By, get...By, read..By, count..By, and query..By.

All of the patterns except count..By 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);

The count..By obviously returns count of records and it works like below

    Integer countByName(String name);

6.2 Find By Multiple Fields

All the cases we have seen are find by single field. 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);

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.

6.3 Limiting Results

Sometimes we may want to limit the number of records we get back. There is a really easy way of doing in to Spring Data JPA.

    Dog findFirstByName(String name);
    Dog findTopByName(String name);

    List<Dog> findTop10ByColor(String color);

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

Let’s find the youngest Dog from the database.

    Dog findTopByOrderByBirthDateDesc();

6.3 More Complex Queries

    List<Dog> findByNameContaining(String subName);
    List<Dog> findByNameStartingWith(String subName);

1: Finds Dogs whose name contains 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);

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

6.4 Nested Fields

Many a 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. Have a look at below examples to understand to do filter on the nested fields using query methods.

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

Here, the expression ‘AddressZipCode’ is evaluated as address.zipCode. Spring will smartly understand and prepares a Join Query on Employee and Address tables. Spring scans the name of query method and finds all possibilities of matches. Consider, ‘Employee’ had a field named as ‘addressZipCode’, the above search will be performed on this field.

What if Employee has addressZipCode and we still want to search by Address.zipCode?
In such a case, Spring gives preference to the field ‘addressZipCode’ and not to the ‘zipCode’ field of Address class. To make search specifically on the ‘zipCode’ of Address class an underscore can be used.

List<Employee> findByNameAndAddress_ZipCode(Integer zipCode);

Now Spring correctly replaces the underscore with a dot.

7 Named Queries (@Query)

Sometimes, the query methods do not fulfil the requirements of all types of queries. The query method may simply be impossible or some complex queries may cause the methods having really 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
Thing to remember: method name doesn’t have any significance here.

    @Query(" SELECT MAX(eventId) AS eventId FROM Event ")
    Long lastProcessedEvent();

Find events having given status from 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);

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 passed as argument to the method. Notice the method arguments are marked @Param.
  • TIME_TO_SEC & TIMEDIFF With query option we can leverage SQL inbuilt functions.
  • Again, method name doesn’t have any role.

8 Summary

In this tutorial we learnt Query Methods, Springs Repository interfaces, and Custom Interfaces. We have also learnt, how Spring converts Query Method names into SQL queries and also seen how to make simple as well as complex searches using query methods. We saw for some of the complex queries we can provide the SQL query using @Query.

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