I released five new sample lessons from my Test With Spring course: Introduction to Spock Framework

Spring Data JPA Tutorial: Introduction to Query Methods

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.

Additional Reading:

If you are not familiar with Spring Data JPA, you should read the following blog posts before you continue reading this blog post:

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 TodoRepository interface. After we have done this, our repository interface looks as follows:

import org.springframework.data.repository.Repository;

interface TodoRepository extends Repository<Todo, Long> { 

	//This is a query method.
	Todo findById(Long id);
}
Don’t worry if you don’t understand how this query method works. The next part of my Spring Data JPA tutorial describes how you can add query methods to your Spring Data JPA repositories.

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.

My "Test With Spring" course helps you to write unit, integration, and end-to-end tests for Spring and Spring Boot Web Apps:

CHECK IT OUT >>

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:

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:

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:

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.

My "Test With Spring" course helps you to write unit, integration, and end-to-end tests for Spring and Spring Boot Web Apps:

CHECK IT OUT >>

Here are some query methods that use the position based parameter binding:

import java.util.Optional
import 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.

The @Param annotation configures the name of the named parameter that is replaced with the value of the method parameter.

Here are some query methods that use named parameters:

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 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.

If you want to learn how to use Spring Data JPA, you should read my Spring Data JPA tutorial.

About the Author

Petri Kainulainen is passionate about software development and continuous improvement. He is specialized in software development with the Spring Framework and is the author of Spring Data book.

About Petri Kainulainen →

46 comments… add one
  • Nice to given sharing about java example it is very easy to understand

    Reply
    • Thank you. I am happy to hear that this blog post was useful to you.

      Reply
  • You’re the man!
    Your articles are so well written and easily understandable. It’s amazing!

    Keep up the good work Petri.

    Reply
    • Thank you for your kind words. I really appreciate them!

      Reply
    • +1

      Reply
  • Thanks for your clean and simple explaination. Sharing is caring and it is your kindness to share and care :)

    Reply
    • You are welcome!

      Reply
  • When I executed my “String findTitleById(Long id)” I got ToDo object instead of String.Also I had to change “interface TodoRepository extends Repository { ” to “interface TodoRepository extends JpaRepository { ” as the former did not work. Can you please help me resolve this.

    Reply
    • Hi,

      When I executed my “String findTitleById(Long id)” I got ToDo object instead of String

      This was my mistake. I thought that Spring Data JPA would support this, but it doesn’t. You can still do this, but you have to specify your own query by using the @Query annotation. By the way, you might want to check out this Jira ticket: Support Projections as Query results.

      Thank you for pointing this out!

      Also I had to change “interface TodoRepository extends Repository { ” to “interface TodoRepository extends JpaRepository { ” as the former did not work.

      Did your code throw an exception or how did you figure out that it didn’t work?

      Reply
      • Oh that was a mistake by me. I had 2 Repository classes imported and had to use one directly(fully qualified class name). One is for the annotation @Repository and the other one you mentioned.That is why i changed it to JpaRepository.

        Anyways thanks for letting me know that there is an open ticket for this feature.

        Reply
  • Thanks for the post! :) It was very useful to me but I had one problem with it:
    what JPA returns when findBy_ sentence? I supposed it would be null, but I had an exception
    Thanks for the help!

    Reply
    • Hi,

      It shouldn’t throw an exception. What exception does it throw?

      Reply
  • Hi, thanks for your very clear tutorials. However, I have a question.

    Are you sure these examples will work? There are several interfaces here with attempts to overload methods with the same signature. Probably won’t compile?

    Reply
    • You are right. They don’t compile. The reason why I decided to put these query methods to the same interface is that this way it is easy to compare different query methods that do the same thing.

      Reply
  • Hello! Thank you very much for such a good tutorials, but I have one question.

    How to make some params optional (varying number of params), something like this:
    List findByOptionalLastnameAndOptionalFirstNameAnd…(String lastname, String firstname,…); ?
    Is there a way to do it?

    Best regards!

    Reply
    • Hi Luc,

      Thank you for your kind words. I really appreciate them.

      How to make some params optional (varying number of params). Is there a way to do it?

      If you want to create dynamic queries with Spring Data JPA, you have to use either JPA Criteria API or Querydsl.

      Reply
      • Thank you very much! Both tutorials are very useful! I used JPA Criteria API and it works perfect for me!

        Reply
        • You are welcome!

          Reply
  • Hello! Thank you for that tutorial.
    But I have a question, is there a way how to return Stream of rows from database using org.springframework.data.jpa.domain.Specification interface. In other words I need to filter rows from database and I can’t just do within query annotation.

    Thanks in advance!

    Reply
  • Nice introduction. one question about JPA. what difference between Spring JPA and Spring Hibernate?

    Reply
    • Hi,

      The Spring Hibernate module provides support for Hibernate. In other words, it ensures that you can use Hibernate in a Spring application. However, you still have to use either the Hibernate API or the “pure” JPA API.

      Spring Data JPA introduces an additional layer that helps you to remove the boilerplate code that is required when you write queries by using the “pure” JPA API. However, you still have to use a JPA provider such as Hibernate.

      Reply
  • how to find unique name using spring data jpa and also display all columns data

    Reply
    • Hi,

      I need a bit more input before I can answer to your question. For example, I need to see the source code of the entity that contains the queried name.

      Reply
  • Is valid in JPA distinct…?
    List findDistinctByName(String name);

    Reply
    • Yes. It should work. If you need more information, you should read this blog post.

      Reply
    • @Query(“SELECT DISTINCT r.Id,r.Desc,r.isActive,r.createdBy FROM categories r where r.isActive=?1”)

      Reply
      • That query doesn’t work because you are not selecting an entity. If you want to select only a few fields of an entity, you need to return a DTO.

        Reply
        • thank u

          Reply
          • You are welcome.

  • Hello Petri, excellent tutorials.
    I have some issue.
    I want the query method to generate query like “FROM ABC WHERE (NAME=? OR LASTNAME=?) AND IN(?,?,?)”
    I wrote method “findByNameOrLastnameAndCityIn()” but it generating query “FROM ABC WHERE NAME=? OR LASTNAME=? AND IN(?,?,?)”.
    What can I do to get required result??

    Reply
    • Hi,

      you need to use the @Query annotation and specify the used JPQL query manually.

      Reply
      • Actually, the no. of parameters in IN operator are not fixed, it can be one or more. So I am passing List to it, but then it is giving me error;
        Query is like :
        @Query(“FROM ABC WHERE (NAME=:name OR LASTNAME=:lastName) AND IN(:cities)”);
        ‘cities’ is of type List

        Reply
        • That is strange. Does your code throw an exception when you run it? If so, could you add the stack trace here? Also, just to clarify, does your repository method look like this:

          
          @Query("FROM ABC WHERE (NAME=:name OR LASTNAME=:lastName) AND IN(:cities)")
          List search(@Param("name" String name, 
          			   	 @Param("lastName") String lastName, 
          			   	 @Param("cities") List cities)
          
          
          Reply
  • Hello there!
    Spring newbie here. When do i have to use the ” nativeQuery=true”?
    Thanks.

    Reply
    • Hi,

      You need to use it when you want to create a query method that uses SQL instead of JPQL.

      Reply
  • Hey there,
    I am new to Spring (and databases and lots of other stuff I’m currently learning on the job). One of the problems I am currently dealing with is a database update via a web Application – you upload a csv, the database is deleted and the csv is read to the db. I used spring batch and it works well thanks to one of your tutorials. Unfortunately I found out that it only works well on my system (with H2), the next testing step uses mySQL. I’ve adapted everything and it runs, but REALLY slowly.
    I created a profile local where it runs fine and a profile !local, where i’ve tried different approaches, but nothing seems to improve the speed. Now i implemented a listener (the profile loads an empty Reader and Writer) that uses @Query with the sql statement LOAD DATA INFILE. I’ve already used some sql queries, so I know who this is working basically.

    My question: I only find the passing of method parameters in select statements. can i use them anywhere? I need to pass the url of the loaded csv-file to the sql statement (for i only have the file after the user loaded it via the html-mask).
    Any help or links or advise of any kind will be highly appreciated!
    I have seen that the last comment was written quite some time ago, but i am hoping, you’re still keeping track of this.

    The snippet that currently troubles me:
    @Modifying
    @Transactional
    @Query(value = “LOAD DATA INFILE ‘sqlCsv’ INTO TABLE MYTABLE FIELDS TERMINATED BY ‘;’ LINES TERMINATED BY ‘\r\n’ IGNORE 1 LINES;”, nativeQuery = true)
    public void loadData(String sqlCsv) throws InvalidDataAccessResourceUsageException;

    Reply
    • Hi,

      I am sorry that it took me a few days to answer to your question (I am currently on summer holiday).

      Unfortunately I found out that it only works well on my system (with H2), the next testing step uses mySQL. I’ve adapted everything and it runs, but REALLY slowly.

      Did you implement your writer ItemWriter by using JPA? If so, this might be one reason why the batch job is so slow when you use a real database. I recommend that you should create an ItemWriter that uses JDBC. I promise that it is a lot faster than the ItemWriter that uses JPA.

      My question: I only find the passing of method parameters in select statements. can i use them anywhere?

      Yes.

      I need to pass the url of the loaded csv-file to the sql statement (for i only have the file after the user loaded it via the html-mask). Any help or links or advise of any kind will be highly appreciated!

      If you are implementing a Spring Batch ItemWriter, you shouldn’t use this approach because this will break the API contract of the write() method. On the other hand, if you are writing custom code that simply invokes your repository method, you can pass the required information by using named parameters.

      Reply
  • Hi Petri,

    I am facing an issue when i am trying to fetch only few selected columns from a table using JPA..
    @Query (“select h.abrHeaderId, h.attLegalEntityCode, h.billCycle, h.typeOfSegmentCodeId, h.countryCode, h.countryLegalName, h.hierCreationStatusCodeId, h.nodeId from AbrBillAccountNode h where h.nodeId = (?1)”)
    public AbrBillAccountNode findOneByNodeId (@Param(“nodeId”) String nodeId);

    I want to fetch only these specific field values from DB but it is erroring out saying
    nested exception is org.springframework.core.convert.ConverterNotFoundException: No converter found capable of converting from type [java.lang.Long] to type [com.att.ssdf.abr.model.domain.AbrBillAccountNode]

    Please help me resolving this…

    Reply
    • Hi,

      The problem is that Spring Data JPA doesn’t know how it can convert your result set into an entity because the result set doesn’t have all the required columns. If you want to select only a few columns, your query method should return a DTO instead of an entity.

      If you have any other questions, let me know!

      Reply
  • nice example.

    Reply
    • Thank you!

      Reply
  • Hi Petri,

    Firstly great article, I was having some issue which got resolved with the help of this https://stackoverflow.com/questions/20120619/optimize-spring-data-jpa-queries/20121813#20121813 . There was this 2.7.2. JPQL Constructor Expressions which I am using for the current project which has some aggregate function columns( 2 avg columns). I wanted to do Pageable sort on these aggregate function at runtime. But every time I am trying to do pageable on my DTO projection fields I am not getting the result.

    @Query(
    “SELECT new StudentSummary(student.id AS Student_ID,”
    + ” student.name AS NAME,”
    + ” student.email AS EMAIL,”
    + ” student.phone AS PHONE,”
    + ” student.currentCity AS CITY,”
    + ” student.country AS COUNTRY,”
    + ” studentEducation.education AS EDUCATION,”
    + ” AVG(CASE WHEN student_education_result_history.examType = :theory THEN student_education_result_history.totalScore ELSE 0 END) AS THEORY_SCORE, ”
    + ” AVG(CASE WHEN student_education_result_history.examType = :practical THEN student_education_result_history.totalScore ELSE 0 END) AS PRACTICAL_SCORE) ”
    + ” FROM Student student LEFT OUTER JOIN student.studentEducation studentEducation”
    + ” LEFT OUTER JOIN studentEducation.studentResultHistory student_education_result_history
    + ” WHERE studentEducation.result = :allOverresult”)
    Page getStudentSummaryByAllOverResult(@Param(“allOverresult”) STUDENT_STATE state, @Param(“theory”) String theory,
    @Param(“practical”) String practical, Pageable pageable);

    Now model looks good but as per my client’s request I might need to add sort by THEORY_SCORE or PRACTICAL_SCORE plus pagination. I thought of adding fields of DTO for this but pageable is binding to Student Entity fields how can I do order by StudentSummary.theoryScore or StudentSummary.practicalScore ? Let me know if its not clear. Thank You

    Reply
    • Hi,

      Unfortunately there is no “clean” solution to your problem. At first I thought that the only way you can do this is to add a custom method to your repository and implement the pagination logic yourself. However, I found this StackOverflow question that might help you to save some time because it seems that you don’t need a custom method after all.

      Reply

Leave a Comment