Derived Query or Query By method name with JPA Repository

Overview

Derived Query is JPA query methods declared in Repository interface and as par the name of that method JPA will automatically derive sql query by using keywords used in method name It’s easy to derive what the query should be by just looking at the method name

Derived Query Methods in Spring JPA

Derived method names have two main parts separated by the By keyword:

List findByTitle(String title);

If you separate this query by By keyword then first part represents the instruction, there are plenty of instruction out there, and some of those are as follow:

And second part contain criteria which act as SQL where condition.

In this case, it will generate SQL query as follow:

select * from books where title = :title

There are other keywords that we can use with find query like Distinct to remove duplicate and First or Top to limit our result set:

List findTop3ByPages(Integer pages);

List findFirstByCategory(String category);

The criteria can have the entity field specific condition expressions for a query. We can use the condition operator identifier (Like, LessThanEqual, GreaterThan, EndingWith, etc.) along with the entity’s property names.

To joint multiple condition expressions we can use And and Or logical operators

Examples

Let's take an example of books table

@Table(name = "books")
@Entity
class Book {
    @Id
    @GeneratedValue
    private Integer id;
    private String title;
    private Boolean softCopy;
    private Date published;
    private Integer pages;
    private String category;

    //getters and setters or lombok annotation
}

We also need to define a repository for that entity.

interface BookRepository extends JpaRepository {
    //All the example methods fall here
}

Equal criteria

We can use ``, Is or Equals for Equal check i.e., SQL = operator

//all three methods will produce a same result
List findByTitle(String title);

List findByTitleIs(String title);

List findByTitleEquals(String title);

Is and Equals are useful in for readability purpose, Specially in case of negative equal check i.e., SQL != or `` operator

//More readable
List findByTitleIsNot(String title);

//then
List findByTitleNot(String title);

//But both are valid method

Note: Equals keyword is not supported with negative criteria.

Null Equal criteria

We can use Null operator keyword for IS NULL criteria optionally prefixed by Is keyword.

List findByTitleIsNull();

List findByTitleNotNull();

We don't need to pass any argument while looking for null or non-null records as we SQL don't need any value for comparison operation

True/False Equal criteria

Criteria for boolean fields can be created with True and False keywords optionally prefixed by Is keyword.

Same as Null criteria this also don't require any value as a argument.

List findBySoftCopyTrue();

List findBySoftCopyIsFalse();

Similarity criteria

There are multiple keywords for Similarity check for a String type of field

List findByTitleStartingWith(String prefix);

List findByTitleEndingWith(String suffix);

List findByTitleContaining(String subString);

List findByTitleLike(String sqlPattern);

Grater/Less criteria

We can also retrieve records with < and <= operator behavior. There are couple of keywords to provide such functionality.

Mainly, these operations are performed on numeric filed and date fields

Examples:

List findByPagesLessThan(Integer numberOfPage);

List findByPagesLessThanEqual(Integer numberOfPage);

List findByPagesGreaterThan(Integer numberOfPage);

List findByPagesGreaterThanEqual(Integer numberOfPage);

List findByPagesBetween(Integer start, Integer end);

List findByPublishedAfter(ZonedDateTime date);

List findByPublishedBefore(ZonedDateTime date);

In criteria

We can also filter record with a set of values with In keywords similar to in operator of SQL

List findByCategoryIn(List categories);

List findByCategoryNotIn(List categories);

Combination of criteria with And and Or

We can combine a multiple combination with And and Or logical operator, Only limit is readability.

List findByTitleAndPagesLessThan(String title, Integer pages);

List findByCategoryInOrSoftCopyIsTrue(List categories);

Sorting

There are two ways to sort the data in Derived Query Methods we can do it with Keywords, or we can do it with Parameter

With Keywords

There are some keywords which used to get sorted result like: OrderBy, Asc, Desc

Let's take an example

List findByTitleOrderByTitle(String title);

List findByTitleOrderByTitleAsc(String title);

Above both will give you the same result as ascending is default order. To get descending order we need to use Desc keyword.

List findByTitleOrderByTitleDesc(String title);

With Parameter

We need to use Sort object as parameter to provide dynamic sorting function.

List findByTitle(String title, Sort sort);

You can create Sort object by initializer method like:

Sort.by(Sort.Direction.ASC, "title");

Same as Sort you can also explore Pageable, it will work similarly but for Pagination