Earlier we created our first Spring Data JPA repository that provides CRUD operations for todo entries.
Although that is a good start, that doesn’t help us to write real life applications because we have no idea how we can query information from the database by using custom search criteria.
One way to find information from the database is to use query methods. However, before we can create custom database queries with query methods, we have to find the answers to the following questions:
- What are query methods?
- What kind of return values can we use?
- How can we pass parameters to our query methods?
This blog post answers to all of these questions. Let’s start by finding out the answer to the first question.
If you are not familiar with Spring Data JPA, you should read the following blog posts before you continue reading this blog post:
- Spring Data JPA Tutorial: Introduction provides a quick introduction to Spring Data JPA and gives an overview of the Spring Data repository interfaces.
- Spring Data JPA Tutorial: Getting the Required Dependencies describes how you can get the required dependencies.
- Spring Data JPA Tutorial: Configuration describes how you can configure the persistence layer of a Spring application that uses Spring Data JPA.
A Very Short Introduction to Query Methods
Query methods are methods that find information from the database and are declared on the repository interface. For example, if we want to create a database query that finds the Todo object that has a specific id, we can create the query method by adding the findById() method to the TodoRepositoryinterface. After we have done this, our repository interface looks as follows:
|
1
2
3
4
5
6
7
|
import org.springframework.data.repository.Repository;interface TodoRepository extends Repository<Todo, Long> { //This is a query method. Todo findById(Long id);} |
Let’s move on and find out what kind of values we can return from our query methods.
Returning Values From Query Methods
A query method can return only one result or more than one result. Also, we can create a query method that is invoked asynchronously. This section addresses each of these situations and describes what kind of return values we can use in each situation.
First, if we are writing a query that should return only one result, we can return the following types:
- Basic type. Our query method will return the found basic type or null.
- Entity. Our query method will return an entity object or null.
- Guava / Java 8 Optional<T>. Our query method will return an Optional that contains the found object or an empty Optional.
Here are some examples of query methods that return only one result:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
import java.util.Optional;import org.springframework.data.jpa.repository.Query;import org.springframework.data.repository.Repository;import org.springframework.data.repository.query.Param;interface TodoRepository extends Repository<Todo, Long> { @Query("SELECT t.title FROM Todo t where t.id = :id") String findTitleById(@Param("id") Long id); @Query("SELECT t.title FROM Todo t where t.id = :id") Optional<String> findTitleById(@Param("id") Long id); Todo findById(Long id); Optional<Todo> findById(Long id);} |
Second, if we are writing a query method that should return more than one result, we can return the following types:
- List<T>. Our query method will return a list that contains the query results or an empty list.
- Stream<T>. Our query method will return a Stream that can be used to access the query results or an empty Stream.
Here are some examples of query methods that return more than one result:
|
1
2
3
4
5
6
7
8
9
|
import java.util.stream.Stream;import org.springframework.data.repository.Repository;interface TodoRepository extends Repository<Todo, Long> { List<Todo> findByTitle(String title); Stream<Todo> findByTitle(String title);} |
Third, if we want that our query method is executed asynchronously, we have to annotate it with the @Async annotation and return a Future<T> object. Here are some examples of query methods that are executed asynchronously:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
|
import java.util.concurrent.Future;import java.util.stream.Stream;import org.springframework.data.jpa.repository.Query;import org.springframework.data.repository.Repository;import org.springframework.data.repository.query.Param;import org.springframework.scheduling.annotation.Async;interface TodoRepository extends Repository<Todo, Long> { @Async @Query("SELECT t.title FROM Todo t where t.id = :id") Future<String> findTitleById(@Param("id") Long id); @Async @Query("SELECT t.title FROM Todo t where t.id = :id") Future<Optional<String>> findTitleById(@Param("id") Long id); @Async Future<Todo> findById(Long id); @Async Future<Optional<Todo>> findById(Long id); @Async Future<List<Todo>> findByTitle(String title); @Async Future<Stream<Todo>> findByTitle(String title);} |
Let’s move on and find out how we can pass method parameters to our query methods.
Passing Method Parameters to Query Methods
We can pass parameters to our database queries by passing method parameters to our query methods. Spring Data JPA supports both position based parameter binding and named parameters. Both of these options are described in the following.
The position based parameter binding means that the order of our method parameters decides which placeholders are replaced with them. In other words, the first placeholder is replaced with the first method parameter, the second placeholder is replaced with the second method parameter, and so on.
Here are some query methods that use the position based parameter binding:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
import java.util.Optionalimport org.springframework.data.jpa.repository.Query;import org.springframework.data.repository.Repository;interface TodoRepository extends Repository<Todo, Long> { public Optional<Todo> findByTitleAndDescription(String title, String description); @Query("SELECT t FROM Todo t where t.title = ?1 AND t.description = ?2") public Optional<Todo> findByTitleAndDescription(String title, String description); @Query(value = "SELECT * FROM todos t where t.title = ?0 AND t.description = ?1", nativeQuery=true ) public Optional<Todo> findByTitleAndDescription(String title, String description);} |
Using position based parameter binding is a bit error prone because we cannot change the order of the method parameters or the order of the placeholders without breaking our database query. We can solve this problem by using named parameters.
We can use named parameters by replacing the numeric placeholders found from our database queries with concrete parameter names, and annotating our method parameters with the @Param annotation.
Here are some query methods that use named parameters:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
import java.util.Optionalimport org.springframework.data.jpa.repository.Query;import org.springframework.data.repository.Repository;import org.springframework.data.repository.query.Param;interface TodoRepository extends Repository<Todo, Long> { @Query("SELECT t FROM Todo t where t.title = :title AND t.description = :description") public Optional<Todo> findByTitleAndDescription(@Param("title") String title, @Param("description") String description); @Query( value = "SELECT * FROM todos t where t.title = :title AND t.description = :description", nativeQuery=true ) public Optional<Todo> findByTitleAndDescription(@Param("title") String title, @Param("description") String description);} |
Let’s move on and summarize what we learned from this blog post.
Summary
This blog post has taught us three things:
- Query methods are methods that find information from the database and are declared on the repository interface.
- Spring Data has pretty versatile support for different return values that we can leverage when we are adding query methods to our Spring Data JPA repositories.
- We can pass parameters to our database queries by using either position based parameter binding or named parameters.
The next part of my Spring Data JPA tutorial describes how we can create database queries from the method names of our query methods.
P.S. You can get the example application of this blog post from Github.
