Derived Queries with Spring Data JPA – The Ultimate Guide

---

Take your skills to the next level!

The Persistence Hub is the place to be for every Java developer. It gives you access to all my premium video courses, monthly Java Persistence News, monthly coding problems, and regular expert sessions.

Join the Persistence Hub!

---

All applications need to execute queries to retrieve data from their database. With JPA and Hibernate, you can write JPQL, native SQL queries or Criteria Queries and you can use all of them with Spring Data JPA. In most cases, Spring Data just makes it a little bit easier.

Spring Data JPA acts as a layer on top of JPA, and it offers you 2 ways to define your query:

1. You can let Spring Data JPA derive the query from the name of a method in your repository.
2. You can define your own JPQL or native query using a @Query annotation.

Both options work great, and you should be familiar with them. In this article, I will focus on derived queries, and I will dive deeper into defining a custom query in a future article.

Example model

But before we do that, let’s take a quick look at the domain model that we will use in all of the examples. It consists of an Author and a Book entity with a many-to-many association between them.

Derived queries

Spring Data often gets praised for its derived query feature. As long as your method name starts with find…By, read…By, query…By, count…By, or get…By and follows the right pattern, Spring Data generates the required JPQL query.

That might sound like you will never need to write your own queries again. But that’s not the case. It’s a great way to define simple queries. But as soon as you need to use more than 2 query parameters or your query gets at least a little bit complex, you should use a custom query. That’s either because the query name gets really complicated to write and read or because you exceed the capabilities of the method name parser.

That said, let’s now take a look Spring Data JPA’s derived query feature.

Simple derived query with parameters

Let’s start with a simple example of a query that loads Author entities with a given firstName.

public interface AuthorRepository extends JpaRepository<Author, Long> {

    List<Author> findByFirstName(String firstName);

}

As you can see, the definition of a derived query is pretty simple and self-explaining. I started the name of the method with findBy and then referenced the entity attributes for which I want to filter in the WHERE clause by its name. And then I define a method parameter with the same name as the referenced entity attribute.

You can then use this query by injecting an instance of the AuthorRepository and calling the findByFirstName method with the firstName you want to search for.

@RunWith(SpringRunner.class)
@SpringBootTest(classes = Application.class)
public class TestQueryMethod {

    @Autowired
    private AuthorRepository authorRepository;

    @Test
    @Transactional
    public void testQueryMethodAuthor() {
        List<Author> a = authorRepository.findByFirstName("Thorben");
    }
}

When you run this test case and activate the logging of SQL statements, you can see the generated SQL statement in your log file.

2019-04-16 10:38:22.523 DEBUG 24408 --- [           main] org.hibernate.SQL                        : 
    select
        author0_.id as id1_0_,
        author0_.first_name as first_na2_0_,
        author0_.last_name as last_nam3_0_,
        author0_.version as version4_0_ 
    from
        author author0_ 
    where
        author0_.first_name=?

Derived queries with multiple parameters

You can extend this method to search for Author entities with a given firstName and lastName by combining them with And. Spring Data JPA, of course, also allows you to concatenate multiple checks using an Or clause.

public interface AuthorRepository extends JpaRepository<Author, Long> {

    List<Author> findByFirstNameAndLastName(String firstName, String lastName);

}

As expected, when you call this repository method, Spring Data JPA and Hibernate generate an SQL query with a WHERE clause that filters the result based on the first_name and last_name columns.

2019-04-16 10:38:22.661 DEBUG 24408 --- [           main] org.hibernate.SQL                        : 
    select
        author0_.id as id1_0_,
        author0_.first_name as first_na2_0_,
        author0_.last_name as last_nam3_0_,
        author0_.version as version4_0_ 
    from
        author author0_ 
    where
        author0_.first_name=? 
        and author0_.last_name=?

Traverse associations in derived queries

If you want to filter for an attribute of an associated entity, you can traverse managed relationships by referencing the attribute that maps the association followed by the attribute of the related entity.

The following code snippet shows an example in which I reference the books attribute on the Author entity to traverse the mapped association and then reference the title attribute of the associated Book entity. That create a query that returns all authors who have written a book with a given title.

public interface AuthorRepository extends JpaRepository<Author, Long> {

    List<Author> findByBooksTitle(String title);

}

When you call this query method, Hibernate generates an SQL query that joins the author and the book table and compares the value in the title column with the provided bind parameter value in the WHERE clause.

2019-04-16 10:37:31.200 DEBUG 20024 --- [           main] org.hibernate.SQL                        : 
    select
        author0_.id as id1_0_,
        author0_.first_name as first_na2_0_,
        author0_.last_name as last_nam3_0_,
        author0_.version as version4_0_ 
    from
        author author0_ 
    left outer join
        book_author books1_ 
            on author0_.id=books1_.fk_author 
    left outer join
        book book2_ 
            on books1_.fk_book=book2_.id 
    where
        book2_.title=?

Other comparison operators

If you just reference an entity attribute in your method name, Spring Data JPA will generate a simple equals comparison. You can also specify different comparison operations by using one of the following keywords together with the name of your entity attribute:

• Like – to check if the value of an entity is like a provided String.
• Containing – to check if the value of an entity attribute contains the provided String.
• IgnoreCase – to ignore the case when comparing the value of an entity attribute with a provided String.
• Between – to check if the value of an entity attribute is between 2 provided values.
• LessThan / GreaterThan – to check if the value of an entity attribute is less or greater then a provided one.

Here is a simple example that selects an Author entity which firstName contains the String “thor” while ignoring its case.

public interface AuthorRepository extends JpaRepository<Author, Long> {

    List<Author> findByFirstNameContainingIgnoreCase(String firstName);

}

When you call this method on the AuthorRepository, Spring Data JPA and Hibernate generate an SQL query that converts the provided String and the value in the first_name column to upper case and creates a LIKE expression to check if the first_name contains the provided String.

2019-04-16 10:38:22.693 DEBUG 24408 --- [           main] org.hibernate.SQL                        : 
    select
        author0_.id as id1_0_,
        author0_.first_name as first_na2_0_,
        author0_.last_name as last_nam3_0_,
        author0_.version as version4_0_ 
    from
        author author0_ 
    where
        upper(author0_.first_name) like upper(?)
2019-04-16 10:38:22.695 TRACE 24408 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [VARCHAR] - [%tho%]

Order the results of a derived query

You can, of course, also order your query results. In JPQL, this would require an ORDER BY clause in your query. With Spring Data JPA, you just need to add the words OrderBy to your query followed by the name of the entity attribute and the abbreviations ASC or DESC for your preferred order.

The following example uses this feature to retrieve all Book entities whose title contains a provided String in the ascending order of their title.

public interface BookRepository extends JpaRepository<Book, Long> {

    List<Book> findByTitleContainsOrderByTitleAsc(String title);

}

When you call this method on the BookRepository, Spring Data JPA and Hibernate generate an SQL statement with the expected ORDER BY clause.

2019-04-16 15:34:44.517 DEBUG 17108 --- [           main] org.hibernate.SQL                        : 
    select
        book0_.id as id1_1_,
        book0_.title as title2_1_,
        book0_.version as version3_1_ 
    from
        book book0_ 
    where
        book0_.title like ? 
    order by
        book0_.title asc

If you require dynamic ordering, you can add a parameter of type Sort to your query method. This is one of the special parameters supported by Spring Data JPA, and it triggers the generation of an ORDER BY clause.

public interface BookRepository extends JpaRepository<Book, Long> {

    List<Book> findByTitleContains(String title, Sort sort);

}

You then need to instantiate a Sort object and specify the ordering the entity attributes that shall be used to generate the ORDER BY clause.

Sort sort = new Sort(Sort.Direction.ASC, "title");
List<Book> b = bookRepository.findByTitleContains("Hibernate", sort);

When you execute the test case, the findByTitleContains generates the same SQL statement as the previous method. But this time, you define the order dynamically,to only return the first 5 records. and you can adjust it at runtime.

2019-04-16 15:34:44.517 DEBUG 17108 --- [           main] org.hibernate.SQL                        : 
    select
        book0_.id as id1_1_,
        book0_.title as title2_1_,
        book0_.version as version3_1_ 
    from
        book book0_ 
    where
        book0_.title like ? 
    order by
        book0_.title asc

Limiting the number of results

Using Hibernate or any other JPA implementation, you can limit the number of returned records on the Query interface. With Spring Data JPA, you can do the same by adding the keywords Top or First followed by a number between the find and By keywords.

When you call the findFirst5ByTitleOrderByTitleAsc method on the BookRepository, Spring Data JPA and Hibernate generate a query that returns the first 5 Book entities whose title contains the given String.

public interface BookRepository extends JpaRepository<Book, Long> {

    List<Book> findFirst5ByTitleOrderByTitleAsc(String title);

}

As you might have expected, the generated SQL statement contains a LIMIT clause to return the first 5 records.

2019-04-16 16:10:42.977 DEBUG 24352 --- [           main] org.hibernate.SQL                        : 
    select
        book0_.id as id1_1_,
        book0_.title as title2_1_,
        book0_.version as version3_1_ 
    from
        book book0_ 
    where
        book0_.title like ? 
    order by
        book0_.title asc 
    limit ?

Paginate the results of a derived query

And after we had a look at ordering and limiting the number of returned records, we also need to talk about pagination. Spring Data JPA provides another special parameter for it. You just need to add a parameter of type Pageable to your query method definition and change the return type to Page<YourEntity>.

public interface BookRepository extends JpaRepository<Book, Long> {

    Page<Book> findAll(Pageable pageable);

}

The Pageable interface makes it very easy to step through the pages. You just define which page number you want to retrieve and how many records should be on a page. That’s it. Spring Data JPA takes care of the rest.

Pageable pageable = PageRequest.of(0, 10);
Page<Book> b = bookRepository.findAll(pageable);

As expected, the generated SQL query contains a LIMIT clause and it would also contain an OFFSET clause, if you don’t request the first page.

2019-04-16 16:43:49.221 DEBUG 17156 --- [           main] org.hibernate.SQL                        : 
    select
        book0_.id as id1_1_,
        book0_.title as title2_1_,
        book0_.version as version3_1_ 
    from
        book book0_ 
    limit ?

Conclusion

Spring Data JPA just provides a relatively small usability layer on top of JPA, but it offers several features that make working with JPA much easier. The derived query feature, which I showed you in this tutorial, is an excellent example of that.

Sure, you could write all these queries yourself, but you don’t have to. As long as your method name doesn’t get too long or complicated, I recommend to let Spring Data JPA generate the required JPQL statement and to take care of the query execution. As a rule of thumb, as long as your query doesn’t need more than 2 parameters, a derived query is the easiest approach.

If your query requires more than 2 parameters or you can’t express it in a short and simple method name, you should define the query yourself. I will show you how to do that in one of my next articles.