Spring Data JPA Tutorial: Creating Database Queries From Method Names

The previous part of this tutorial taught us to pass method parameters to our query methods and described what kind of values we can return from them.

This blog post describes how we can create query methods by using the query generation from the method name strategy.

We will also implement a simple search function that has two requirements:

  1. It must return todo entries whose title or description contains the given search term.
  2. The search must be case insensitive.

Let's get started.

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:

Creating Query Methods

The query generation from the method name is a query generation strategy where the invoked query is derived from the name of the query method.

We can create query methods that use this strategy by following these rules:

  • The name of our query method must start with one of the following prefixes: find…By, read…By, query…By, count…By, and get…By.
  • If we want to limit the number of returned query results, we can add the First or the Top keyword before the first By word. If we want to get more than one result, we have to append the optional numeric value to the First and the Top keywords. For example, findTopBy, findTop1By, findFirstBy, and findFirst1By all return the first entity that matches with the specified search criteria.
  • If we want to select unique results, we have to add the Distinct keyword before the first By word. For example, findTitleDistinctBy or findDistinctTitleBy means that we want to select all unique titles that are found from the database.
  • We must add the search criteria of our query method after the first By word. We can specify the search criteria by combining property expressions with the supported keywords.
  • If our query method specifies x search conditions, we must add x method parameters to it. In other words, the number of method parameters must be equal than the number of search conditions. Also, the method parameters must be given in the same order than the search conditions.
  • We must set the return type of the query method by following the rules that were described in the previous part of this tutorial.
The following examples demonstrate how we can create simple query methods by using the query generation from the method name strategy:

Example 1: If we want to create a query method that returns the todo entry whose id is given as a method parameter, we have to add one of the following query methods to our repository interface:

import org.springframework.data.repository.Repository;

import java.util.Optional;

interface TodoRepository extends Repository<Todo, Long> {

	/**
	 * Returns the found todo entry by using its id as search 
	 * criteria. If no todo entry is found, this method
	 * returns null. 
	 */
	public Todo findById(Long id);

	/**
	 * Returns an Optional which contains the found todo 
	 * entry by using its id as search criteria. If no to entry
	 * is found, this method returns an empty Optional.
	 */
	public Optional<Todo> findById(Long id);
}

Example 2: If we want to create a query method that returns todo entries whose title or description is given as a method parameter, we have to add the following query method to our repository interface:

import org.springframework.data.repository.Repository;

import java.util.List;

interface TodoRepository extends Repository<Todo, Long> {

	/**
	 * Returns the found todo entry whose title or description is given
	 * as a method parameter. If no todo entry is found, this method
	 * returns an empty list.
	 */
	public List<Todo> findByTitleOrDescription(String title, String description);
}

Example 3: If we want to create a query method that returns the number of todo entries whose title is given as a method parameter, we have to add the following query method to our repository interface:

import org.springframework.data.repository.Repository;

interface TodoRepository extends Repository<Todo, Long> {

	/**
	 * Returns the number of todo entry whose title is given
	 * as a method parameter.
	 */
	public long countByTitle(String title);
}

Example 4: If we want to return the distinct todo entries whose title is given as a method parameter, we have to add the following query method to our repository interface:

import org.springframework.data.repository.Repository;

import java.util.List;

interface TodoRepository extends Repository<Todo, Long> {

	/**
	 * Returns the distinct todo entries whose title is given
	 * as a method parameter. If no todo entries is found, this
	 * method returns an empty list.
	 */
	public List<Todo> findDistinctByTitle(String title);
}

Example 5: If we want to to return the first 3 todo entries whose title is given as a method parameter, we have to add one of the following query methods to our repository interface:

import org.springframework.data.repository.Repository;

import java.util.List;

interface TodoRepository extends Repository<Todo, Long> {

	/**
	 * Returns the first three todo entries whose title is given
	 * as a method parameter. If no todo entries is found, this
	 * method returns an empty list.
	 */
	public List<Todo> findFirst3ByTitleOrderByTitleAsc(String title);

	/**
	 * Returns the first three todo entries whose title is given
	 * as a method parameter. If no todo entries is found, this
	 * method returns an empty list.
	 */
	public List<Todo> findTop3ByTitleOrderByTitleAsc(String title);
}

Let's move on and create the query method that fulfils the requirements of our search function.

Implementing the Search Function

We can implement the search function by following these steps:

  1. Create a query method whose name starts with the prefix findBy.
  2. Ensure that the query method returns todo entries whose description contains the given search term. We can do this by appending the property expression: Description and the keyword: Contains to the method name.
  3. Configure the query method to return the information of a todo entry if the previous (2) or the next (4) search condition is true. We can do this by appending the keyword: Or to the method name.
  4. Ensure that the query method returns todo entries whose title contains the given search term. We can do this by appending the property expression: Title and the keyword: Contains to the method name.
  5. Ensure that the search is case insensitive. We can do this by appending the keyword: AllIgnoreCase to the method name.
  6. Add two method parameters to the query method:
    1. Spring Data JPA uses the descriptionPart method parameter when it ensures that the description of the returned todo entry contains the given search term.
    2. Spring Data JPA uses the titlePart method parameter when it ensures that the title of the returned todo entry contains the given search term.
  7. Set the type of the returned object to List<Todo>.

The source code of our repository interface looks as follows:

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.Repository;

import java.util.List;

interface TodoRepository extends Repository<Todo, Long> {

	List<Todo> findByDescriptionContainsOrTitleContainsAllIgnoreCase(String descriptionPart,
                                                                     String titlePart);
}

Let's move on and find out when we should create our query methods by using the query generation from the method name strategy.

When Should We Use the Query Generation From the Method Name Strategy?

This query generation strategy has the following benefits:

  • Creating simple queries is fast.
  • The method name of our query method describes the selected value(s) and the used search condition(s).

This query generation strategy has the following weaknesses:

  • The features of the method name parser determine what kind of queries we can create. If the method name parser doesn’t support the required keyword, we cannot use this strategy.
  • The method names of complex query methods are long and ugly.
  • There is no support for dynamic queries.

When we think about the pros and cons of this strategy and take a second look at our examples, it becomes clear that the length of our query method name determines whether or not we should use this strategy.

I am not a big fan of "super long" method names because they become unreadable very fast. If we compare the method name: findById with the method name: findByDescriptionContainsOrTitleContainsAllIgnoreCase, we notice that first one is very easy to read. The second method name is not nearly as easy to read as the first one, but it is not impossible to read either (yet). It is a borderline case.

Because I want to write code that is easy to read, I think that we should use this strategy only when we are creating simple queries that have only one or two search conditions.

Let’s move on and summarize what we learned from this blog post.

Summary

This blog post has taught us the following things:

  • If we want to use the query generation by method name strategy, the name of our query method must start with a special prefix.
  • We can select unique results by using the Distinct keyword.
  • We can limit the number of returned query results by using either the Top or the First keyword.
  • We can create search conditions by using property expressions and the keywords supported by Spring Data JPA.
  • We should use the query generation from the method name strategy only when our query is simple and has only one or two search conditions.

The next part of this tutorial describes how we can create query methods by using the @Query annotation.

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.
26 comments… add one
  • Aleksandar Jan 12, 2016 @ 18:02

    Hi Petri,

    Can I create a method that will return field instead the whole Todo entity by only using approach that you described above?

  • Mayank Feb 10, 2016 @ 17:52

    Hi Petri,

    How can we write query for situation like below:

    Select * from USER where '2016-02-15' between VALID_FROM and VALID_TO;

    Regards,
    Mayank.

    • Petri Feb 10, 2016 @ 21:20

      Before I can answer to your question, I need a bit more information about your use case:

      • Is the date: '2016-02-15' stored into a field of the User entity? If so, what is the name of this field?
      • Do you want to create this query by using the query generation from the method name strategy or do you want to use JPQL?
  • Gabriel Lourenco Feb 22, 2016 @ 18:30

    Hi Petri,

    Your post helped me a lot. But I'm still having some problem on this one :

    I need to retrieve all users from the table User where the name contains the letters on the input box, without taking in count Case Sensitive and Accent Sensitive.

    So far I got this: findByNameIgnoreCaseContainsOrderByNameAsc() . But still not working. Can you give me some advice?

    • Petri Feb 22, 2016 @ 22:09

      Hi Gabriel,

      I assume that the generated query ignores case, but it isn't accent sensitive. Am I right? Which database are you using?

      I think that it is not possible to create accent sensitive query if you use the query generation from the method name strategy or JPQL, but it might be possible to configure your database to ignore the accent of the name column. Also, you can always use a native SQL query for this purpose.

      • Gabriel Lourenco Feb 23, 2016 @ 21:34

        Hi Petri.

        I'm using postgre (9.3). That is the point where I got stuck. My new approach is it to use native query indeed. I guess it will be easier. I'm thinking of enabling the unaccent extention from Postgre and use it in a native query. Do you think it is the best idea?

        • Petri Feb 23, 2016 @ 22:10

          Hi Gabriel,

          It is probably the fastest (and the best) way to solve your problem. Go for it! :)

          • Gabriel Lourenco Feb 26, 2016 @ 16:55

            Hi Petri.

            I want to thank you. It worked perfectly. Thanks a lot.

          • Petri Feb 26, 2016 @ 19:01

            You are welcome.

  • Rafael Fuchs Apr 17, 2016 @ 17:44

    Hi

    I would like to do the following, but the named method doesn't work the way I want.
    This way I'm getting the following error:

    SEVERE: Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessApiUsageException: Parameter with that position [1] did not exist; nested exception is java.lang.IllegalArgumentException: Parameter with that position [1] did not exist] with root cause
    java.lang.IllegalArgumentException: Parameter with that position [1] did not exist

    @Query(value = "select j from Journal j where (j.title like '%?1%' or j.description like '%?2%') and j.category=?3")
    Page findByTitleContainingOrDescriptionContainingAndCategoryEqualsAllIgnoreCase(
    String title, String description, String category, Pageable pageable);

    • Rafael Fuchs Apr 17, 2016 @ 17:51

      I've tried with named paramenters, but it said that the parameter doesn't exist like below.

      SEVERE: Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessApiUsageException: Parameter with that name [partitle] did not exist; nested exception is java.lang.IllegalArgumentException: Parameter with that name [partitle] did not exist] with root cause
      java.lang.IllegalArgumentException: Parameter with that name [partitle] did not exist

      • Petri Apr 18, 2016 @ 18:23

        That is indeed a weird problem (I have not experienced this myself). What Spring Data JPA version are you using? Also, have you tried using the CONCAT function (see this blog post)?

  • Mandar Jun 23, 2017 @ 10:23

    Hi,

    I want to generate a method for joining three table and also pass one conditional value in On( ) like
    "menu a left join rolemenu b on (a.menuid=b.menuid and b.roleid=?)"..
    Cal u suggest me how to write findByMethod for this

    • Petri Jun 27, 2017 @ 21:14

      Hi,

      As far as I know, the query generation from the method name strategy doesn't support joins. If you want to create a query method that uses joins, you have to use the @Query annotation.

  • Sudhir Aug 28, 2017 @ 14:55

    Hi,
    How can same method signature co exist in same class as in your example first line 12 and 19?
    regards, Sudhir

    • Petri Aug 29, 2017 @ 11:43

      Hi,

      I decided to add these methods to the same code listing (class) because this means that the reader compare these methods side by side. Of course, this means that the code doesn't compile, but I think that the benefits of this solution outweigh its drawbacks.

  • Nilesh Sep 20, 2017 @ 18:55

    HI Petri,

    I am looking for getting max of a column between two tables.
    E.g. We have 2 tables.
    1.Item table
    2.Audit table
    Data in item table
    Item.Item_id - 1
    Data in Audit table
    Audit.Aud_id Audit.item_id
    1 1
    2 1
    3 1

    Now,i want to write a jpa method name which would return me Audit.Aud_id = 3 record
    Please help me to sort out this issue.

    • Petri Sep 21, 2017 @ 21:57

      Hi,

      As far as I know, it's not possible to create this query by using the query generation from the method name strategy. I think that your best option is to create a SQL query by using the @Query annotation.

  • Shiny May 17, 2018 @ 16:00

    Can we use criteria in spring data jpa

  • Anonymous Apr 8, 2020 @ 10:36

    hi,
    i want something that would let the user to generate his own queries with any entities i have and get the result.
    dynamic query generator.
    and i am using Springboot, i have also tried Specification, is there something else ?

    • Petri Apr 8, 2020 @ 22:03

      Hi,

      If you want that your queries return JPA entities, you have basically these three options:

      • Spring Data JPA specifiications
      • The Hibernate Criteria API
      • QueryDSL

Leave a Reply