# Derived Query Methods in Spring Data JPA

By Atta Ur Rehman Shah (https://attacomsian.com/about). Published Oct 4, 2019, updated Sep 25, 2022. Topics: Spring Boot, Spring Data JPA.
Canonical URL: https://attacomsian.com/blog/derived-query-methods-spring-data-jpa

> Learn how to create and use the derived query methods in Spring Data JPA to fetch, update, and delete data from a database.

[Spring Data JPA](https://attacomsian.com/blog/getting-started-spring-data-jpa) allows you to execute different queries to retrieve data from the database. You can either use the method name to derive a query directly or manually define your own JPQL/native query using the [@Query annotation](https://attacomsian.com/blog/spring-data-jpa-query-annotation).  

For simple use cases, you can write a **derive query method by looking at the corresponding method name** in the entity class. Just define the query methods in a repository interface that extends one of the Spring Data repositories, such as `CrudRepository`. Spring Data JPA will create queries automatically by parsing these method names.

In this article, you will learn how Spring Data JPA leverages the idea of method naming conventions for mapping JPA queries.

## Derived Query Methods Structure

A derived query **method name has two main components** separated by the first `By` keyword:

1. **The introducer** clause like `find`, `read`, `query`, `count`, or `get` tells Spring Data JPA what you want to do with the method. This clause can contain other expressions, such as `Distinct`, that set a distinct flag on the query to be created.
2. **The criteria** clause that starts after the first `By` keyword. The first `By` acts as a delimiter to indicate the start of the actual query criteria. The criteria clause is where you define conditions on entity properties and concatenate them with `And` and `Or` keywords.

Here are some examples:

```java
# find users by the last name
List<User> findByLastName(String lastName);

# find distinct users by email
List<User> findDistinctByEmail(String email);

# count users by profession
int countByProfession(String profession);
```

> **Note:** You can also use `readBy`, `getBy`, and `queryBy` in place of `findBy`, and Spring Data JPA would behave the same. For example, `readByName(String name)` is equivalent to `findByName(String name)`.

## Create an Application

Let us create a sample [Spring Boot application](https://attacomsian.com/blog/spring-data-jpa-h2-database) with Spring Data JPA and H2 database. If you prefer to use MySQL for the data store, follow this step-by-step [tutorial](https://attacomsian.com/blog/accessing-data-spring-data-jpa-mysql).

The sample application has just one `User` entity. Here is what it looks like:

<p class="file-desc"><span>User.java</span></p>

```java
package com.attacomsian.jpa.derived.domains;

import javax.persistence.*;
import java.util.Date;

@Entity
public class User {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;
    private String name;
    @Column(unique = true)
    private String email;
    private int age;
    private Date birthDate;
    private boolean active;

    public User() {
    }

    public User(String name, String email, int age, Date birthDate, boolean active) {
        this.name = name;
        this.email = email;
        this.age = age;
        this.birthDate = birthDate;
        this.active = active;
    }

    // getters and setters, equals(), toString(), ... (omitted for brevity)
}
```

Next, create the `UserRepository` interface that extends `CrudRepository`. We will use this repository interface for defining all our derived query methods. Here is what it looks like: 

<p class="file-desc"><span>UserRepository.java</span></p>

```java
package com.attacomsian.jpa.derived.repositories;

import com.attacomsian.jpa.derived.domains.User;
import org.springframework.data.repository.CrudRepository;

public interface UserRepository extends CrudRepository<User, Long> {
    
    // TODO: add derived methods 
}
```

## Simple Derived Queries

Let us start with a simple example of a derived query that fetches `User` entities with a given `name`:

```java
List<User> findByName(String name);
```

The above-derived query is pretty simple and self-explaining. The method name starts with the `findBy` keyword and uses the entity attribute `name` to specify the criteria. This method takes only one parameter that will be used to match the referenced entity attribute in the `WHERE` clause. The `findByName()` method returns a list of `User` entities as multiple users can have the same name.

Now, if you run the application, the above-derived method will be translated to the following JPQL query:

```sql
SELECT u from User u WHERE u.name = ?1
```

Similarly, we can create simple queries for other `User`'s entity attributes. The following query method returns a `User`  instance matched with an email address:

```java
Optional<User> findByEmail(String email);
```

## Derived Queries with Multiple Parameters

You can easily combine multiple condition expressions using `And` and `Or` keywords. Let us merge the above two query methods to search for `User` entities with a given name *or* an email address:

```java
List<User> findByNameOrEmail(String name, String email);
```

Another example of a derived query with multiple parameters that looks for `User` entities with a given name *and* age:

```java
List<User> findByNameAndAge(String name, int age);
```

You can **concatenate as many expressions as you want** with `And` and `Or` keywords:

```java
List<User> findByActiveAndBirthDateOrNameAndAge(boolean active,Date dob, String name, int age);
```

Spring Data JPA **does not explicitly limit** the number of expressions you can combine in a single derived query. However, you should not go insane. Too long derived queries are hard to read and maintain. For complex use cases, you should use [custom queries](https://attacomsian.com/blog/spring-data-jpa-query-annotation) instead.

## Equality Condition Keywords

For all derived queries, we have written above, Spring Data JPA will generate a simple equals comparison. You can also specify other comparison operators by using equality keywords.

For an exact match, we only need to reference the entity attribute as we did above:

```java
List<User> findByName(String name);
```

For more readability, you can add either `Is` or `Equals`:

```java
List<User> findByNameIs(String name);
// OR
List<User> findByNameEquals(String name);
```

To express the inequality, you can use the `IsNot` keyword:

```java
List<User> findByNameIsNot(String name);
// OR
List<User> findByNameNot(String name);
```

For the `null` comparison, we cannot directly use the equal (`=`) operator. If you pass a `null` parameter value, Spring Data JPA will not throw an exception. Instead, it will translate the query to an `IS NULL` SQL comparison.

To explicitly add the `IS NULL` or `IS NOT NULL` criteria to the query, you can use `IsNull` and `IsNotNull`:

```java
List<User> findByEmailIsNull();
List<User> findByEmailIsNotNull();
```

For boolean entity attributes, you can even go one step further and directly add `True` and `False` keywords for the equality conditions:

```java
List<User> findByActiveTrue();
List<User> findByActiveFalse();
```

## Matching Condition Keywords

To check if the value of an entity attribute contains the given substring, we can use the matching condition keywords.

For example, we can find users whose names **start with a specific value** by using the `StartingWith` keyword:

```java
List<User> findByNameStartingWith(String prefix);
// OR
List<User> findByNameIsStartingWith(String prefix);
// OR
List<User> findByNameStartsWith(String prefix);
```

Spring Data JPA will translate this to the `WHERE name LIKE 'prefix%'` query.

For users whose names **end with a specific value**, you should use the `endingWith` keyword that translates to `WHERE name LIKE '%suffix'`:

```java
List<User> findByNameEndingWith(String suffix);
```

Similarly, for users whose names **contain a specific value**, there exists a `Containing` keyword which is equivalent to `WHERE name LIKE '%infix%'`:

```java
List<User> findByNameContaining(String infix);
```

All the above three condition keywords automatically append the `%` operator to the parameter value. It is good enough for simple use cases. 

For complex matching operation which includes several `%` operators, you should use the `Like` keyword instead:

```java
List<User> findByEmailLike(String pattern);
```

The `Like` (or `NotLike`) keyword does not append the `%` operator to the argument. You have to explicitly define the matching pattern like below:

```java
String pattern = "%atta%@gmail%";
List<User> users = userRepository.findByEmailLike(pattern);
```

## Comparison Condition Keywords

Spring Data JPA also provides keywords for comparison operations. For example, we can use `LessThan` and `LessThanEqual` keywords to compare the value of a specific property with the given value:

```java
List<User> findByAgeLessThan(int age);
List<User> findByAgeLessThanEqual(int age);
```

For `>` and `>=` comparison operations, you should use `GreaterThan` and `GreaterThanEqual` keywords:

```java
List<User> findByAgeGreaterThan(int age);
List<User> findByAgeGreaterThanEqual(int age);
```

To check if the value of an entity attribute is between two given values, Spring Data JPA provides the `Between` keyword:

```java
List<User> findByAgeBetween(int start, int end);
```

Two more keywords, supported by the Spring Data JPA, are `Before` and `After`. We can use these keywords to find users who were born before or after a given date:

```java
List<User> findByBirthDateBefore(Date before);
List<User> findByBirthDateAfter(Date after);
```

## `Distinct` and `IgnoreCase` Keywords

The `Distinct` keyword is used to enable the distinct flag for the query:

```java
List<User> findDistinctByEmail(String email);

List<User> findDistinctPeopleByNameOrEmail(String name, String email);
```

The `IgnoreCase` keyword is used to enable a case-insensitive search for a specific property:

```java
List<User> findByNameIgnoreCase(String name);
```

To enable a case-insensitive search for all suitable properties, you should use the `AllIgnoreCase` keyword:

```java
List<User> findByNameOrEmailAllIgnoreCase(String name, String email);
```

## Sorting Derived Query Results

Spring Data JPA also enables **static ordering** by appending an `OrderBy` clause to the query method that references a property and by providing a sorting direction (`Asc` or `Desc`).

The following example uses static ordering to retrieve all `User` entities whose `name` contains a given value in the ascending order:

```java
List<User> findByNameContainingOrderByName(String name);
// OR
List<User> findByNameContainingOrderByNameAsc(String name);
```

By default, the `OrderBy` clause sorts the results in ascending order. But you can add `Desc` to reverse the sorting direction:

```java
List<User> findByNameContainingOrderByNameDesc(String name);
```

If you need **dynamic ordering**, you can add a `Sort` parameter to your query method. This is one of the special parameters supported by Spring Data JPA. `Sort` triggers the generation of an `ORDER BY` clause. Here is an example:

```java
List<User> findByNameContaining(String name, Sort sort);
```

To call the above method, you need to create a `Sort` object to specify the entity attributes and their ordering:

```java
// sort users in ascending order
List<User> users = userRepository.findByNameContaining("john", Sort.by("name").descending());

// sort users in descending order
List<User> users = userRepository.findByNameContaining("john", Sort.by("name").descending());

// multiple sort parameters
List<User> users = userRepository.findByNameContaining("john", Sort.by("name", "age").descending());
```

## Limiting Derived Query Results

The results of derived query methods can be limited by interchangeably using the `First` or `Top` keywords. An optional numeric value can be added to `Top` or `First` to specify the maximum number of results to be returned. If the number is not provided, a result size of 1 is used. 

The following examples show how to limit the derived query result size:

```java
User findFirstByOrderByName();

User findTopByOrderByAgeDesc();

List<User> findFirst5ByEmail(String email);

List<User> findDistinctTop3ByAgeLessThan(int age);
```

## Paginate Derived Query Results

Spring Data JPA provides another special `Pageable` parameter for applying pagination to query results. You just need to add this parameter to your query method definition and change the return type to `Page<User>`:

```java
Page<User> findByActive(boolean active, Pageable pageable);
```
 
The `Pageable` interface makes it easy to dynamically add paging to your statically defined query. A `Page` knows the total number of elements and pages available. 

You just define the page number you want to retrieve and how many records should be on a page. Spring Data JPA will automatically create an appropriate paging query:

```java
Pageable pageable = PageRequest.of(0, 10);
Page<User> userPage = userRepository.findByActive(true, pageable)
```

You can even add dynamic sorting to derived query through the `Pageable` instance:

```java
Pageable pageable = PageRequest.of(0, 10, Sort.by("name").descending());
Page<User> userPage = userRepository.findByActive(true, pageable);
```

## Derived Delete Queries

Spring Data JPA also supports derived delete queries:

```java
// delete all users by name
void deleteByName(String name);

// delete all active or inactive users
void deleteAllByActive(boolean active);
```


> **Source code:** Download the complete source code from [GitHub](https://github.com/attacomsian/code-examples/tree/master/spring-data-jpa/jpa-queries) available under MIT license.

## Conclusion

That's all for creating and using derived queries in Spring Data JPA. You have learned the structure of derived query methods, JPA-supported keywords, sorting, paginating, limiting the result size, and much more.

Spring Data JPA is a powerful tool that **provides an extra layer of abstraction** on top of an existing JPA provider like Hibernate. The derived query feature, which I just explained in this tutorial, is one of the most loved features of Spring Data JPA. 

You just need to follow conventions while writing derived methods. As long as your query method name starts with `find…By`, `read…By`, `query…By`, `count…By`, or `get…By` and follows the correct criteria pattern, Spring Data translates it to create the required JPQL query.

Check out the [official documentation](https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.query-methods) to learn more about all the available options.

**Continue reading** the next part of this tutorial, that describes how to [create and use custom JPQL and native queries](https://attacomsian.com/blog/spring-data-jpa-query-annotation) with the `@Query` annotation.

## Further Reading

To learn about Spring Data JPA, check out the following articles:

* [Getting Started with Spring Data JPA](https://attacomsian.com/blog/getting-started-spring-data-jpa)
* [Spring Data JPA with H2 DataBase and Spring Boot](https://attacomsian.com/blog/spring-data-jpa-h2-database)
* [Accessing Data with Spring Data JPA and MySQL](https://attacomsian.com/blog/accessing-data-spring-data-jpa-mysql)
* [Spring Data JPA Custom Queries with the @Query Annotation](https://attacomsian.com/blog/spring-data-jpa-query-annotation)
* [How to Use Spring Data JPA Named Queries](https://attacomsian.com/blog/spring-data-jpa-named-queries)
* [Sorting Query Results with Spring Data JPA](https://attacomsian.com/blog/spring-data-jpa-sorting)