Spring Data JPA - Query Creation from Method Names


In this article, we will learn how we can create query methods by using the query generation from the method name strategy.
Spring Data JPA offers various ways to create a query. In this article, we will focus on how to generate a query using method name strategy.
The following simple example shows what a JPA query method translates into:
public interface UserRepository extends Repository<User, Long> {
  List<User> findByEmailAddressAndLastname(String emailAddress, String lastname);
}
Spring Data JPA creates a query using the JPA criteria API from this, but, essentially, this translates into the following query: select u from User u where u.emailAddress = ?1 and u.lastname = ?2.

Supported keywords inside method names

The following table describes the keywords supported for JPA and what a method containing that keyword translates to:
Let's create an example to demonstrate the usage of above all keywords. Before creating UserRepository, let's create User JPA entity on which we will perform database operations.

JPA Entity - User.java

package net.guides.springboot2.springboottestingexamples.model;

import java.util.Date;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "users")
public class User {
 private long id;
 private String firstname;
 private String lastname;
 private Date startDate;
 private int age;
 private int active;

 @Id
 @GeneratedValue(strategy = GenerationType.AUTO)
 public long getId() {
  return id;
 }

 public void setId(long id) {
  this.id = id;
 }

 public String getFirstname() {
  return firstname;
 }

 public void setFirstname(String firstname) {
  this.firstname = firstname;
 }

 public String getLastname() {
  return lastname;
 }

 public void setLastname(String lastname) {
  this.lastname = lastname;
 }

 public Date getStartDate() {
  return startDate;
 }

 public void setStartDate(Date startDate) {
  this.startDate = startDate;
 }

 public int getAge() {
  return age;
 }

 public void setAge(int age) {
  this.age = age;
 }

 public int getActive() {
  return active;
 }

 public void setActive(int active) {
  this.active = active;
 }
}

Spring Data JPA Repository - UserRepository.java

import java.util.Collection;
import java.util.Date;
import java.util.List;
import java.util.Optional;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

import net.guides.springboot2.springboottestingexamples.model.User;

/**
 * UserRepository demonstrates the method name query generation. 
 * @author Ramesh Fadatare
 *
 */
@Repository
public interface UserRepository extends JpaRepository<User, Long> {
 Optional<User> findByLastnameAndFirstname(String firstname, String lastname);

 Optional<User> findByLastnameOrFirstname(String firstname, String lastname);

 List<User> findByStartDateBetween(Date date1, Date date2);

 List<User> findByAgeLessThan(int age);

 List<User> findByAgeLessThanEqual(int age);

 List<User> findByAgeGreaterThan(int age);

 List<User> findByAgeGreaterThanEqual(int age);

 List<User> findByStartDateAfter(Date date);

 List<User> findByStartDateBefore(Date date);

 List<User> findByAgeIsNull();

 List<User> findByFirstnameLike(String firstname);

 List<User> findByFirstnameNotLike(String firstname);

 Optional<User> findByFirstnameStartingWith(String firstname);

 List<User> findByFirstnameEndingWith(String firstname);

 List<User> findByFirstnameContaining(String firstname);

 Optional<User> findByAgeOrderByLastnameDesc(int age);

 List<User> findByLastnameNot(String lastname);

 List<User> findByAgeIn(Collection<Integer> ages);

 List<User> findByAgeNotIn(Collection<Integer> ages);

 List<User> findByActiveTrue();

 List<User> findByActiveFalse();

 List<User> findByFirstnameIgnoreCase(String firstname);
}

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

Source code for Reference

JPA Entity - Todo.java

package net.guides.springboot2.springboottestingexamples.model;

import java.time.ZonedDateTime;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;
import javax.persistence.Version;

import org.hibernate.annotations.Type;
import org.springframework.data.annotation.CreatedBy;
import org.springframework.data.annotation.CreatedDate;
import org.springframework.data.annotation.LastModifiedBy;
import org.springframework.data.annotation.LastModifiedDate;

@Entity
@Table(name = "todos")
public class Todo {

    static final int MAX_LENGTH_DESCRIPTION = 500;
    static final int MAX_LENGTH_TITLE = 100;

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    @Column(name = "created_by_user", nullable = false)
    @CreatedBy
    private String createdByUser;

    @Column(name = "creation_time", nullable = false)
    @Type(type = "org.jadira.usertype.dateandtime.threeten.PersistentZonedDateTime")
    @CreatedDate
    private ZonedDateTime creationTime;

    @Column(name = "description", length = MAX_LENGTH_DESCRIPTION)
    private String description;

    @Column(name = "modified_by_user", nullable = false)
    @LastModifiedBy
    private String modifiedByUser;

    @Column(name = "modification_time")
    @Type(type = "org.jadira.usertype.dateandtime.threeten.PersistentZonedDateTime")
    @LastModifiedDate
    private ZonedDateTime modificationTime;

    @Column(name = "title", nullable = false, length = MAX_LENGTH_TITLE)
    private String title;

    @Version
    private long version;

    /**
     * Required by Hibernate.
     */
    private Todo() {}

    private Todo(Builder builder) {
        this.title = builder.title;
        this.description = builder.description;
    }

    static Builder getBuilder() {
        return new Builder();
    }

    Long getId() {
        return id;
    }

    String getCreatedByUser() {
        return createdByUser;
    }

    ZonedDateTime getCreationTime() {
        return creationTime;
    }

    String getDescription() {
        return description;
    }

    String getModifiedByUser() {
        return modifiedByUser;
    }

    ZonedDateTime getModificationTime() {
        return modificationTime;
    }

    String getTitle() {
        return title;
    }

    long getVersion() {
        return version;
    }

    /**
     * This entity is so simple that you don't really need to use the builder pattern
     * (use a constructor instead). I use the builder pattern here because it makes
     * the code a bit more easier to read.
     */
    static class Builder {
        private String description;
        private String title;

        private Builder() {}

        Builder description(String description) {
            this.description = description;
            return this;
        }

        Builder title(String title) {
            this.title = title;
            return this;
        }

        Todo build() {
            Todo build = new Todo(this);
            return build;
        }
    }
}

Spring Data JPA - TodoRepository.java

Refer below source code examples gives you more idea about how to create database queries using method names.
package net.guides.springboot2.springboottestingexamples.repository;

import java.util.List;
import java.util.Optional;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.Repository;
import org.springframework.data.repository.query.Param;

import net.guides.springboot2.springboottestingexamples.model.Todo;

/**
 * This repository provides CRUD operations for {@link net.petrikainulainen.springdata.jpa.todo.Todo}
 * objects.
 *
 */
interface TodoRepository extends Repository < Todo, Long > {

    void delete(Todo deleted);

    List < Todo > findAll();

    /**
     * This query method creates the invoked query method by parsing it from the method name of the query method.
     * @param descriptionPart   The part that must be found from the description of the todo entry.
     * @param titlePart         The part that must be found from the title of the todo entry.
     * @param pageRequest       The information of the requested page.
     * @return  A page of todo entries whose title or description contains with the given search term. The content of
     *          the returned page depends from the page request given as a method parameter.
     */
    Page < Todo > findByDescriptionContainsOrTitleContainsAllIgnoreCase(String descriptionPart,
        String titlePart,
        Pageable pageRequest);

    /**
     * This query method creates the invoked query method by parsing it from the method name of the query method.
     * @param descriptionPart   The part that must be found from the description of the todo entry.
     * @param titlePart         The part that must be found from the title of the todo entry.
     * @return  A list of todo entries whose title or description contains with the given search criteria. The returned
     *          todo entries are sorted in alphabetical order by using the title of the todo entry.
     */
    List < Todo > findByDescriptionContainsOrTitleContainsAllIgnoreCaseOrderByTitleAsc(String descriptionPart,
        String titlePart);

    /**
     * This query method invokes the named JPQL query that is configured in the {@code Todo} class by using the
     * {@code @NamedQuery} annotation. The name of the named query is: {@code Todo.findBySearchTermNamed}.
     * @param searchTerm    The given search term.
     * @param pageRequest   The information of the given page.
     * @return  A page of todo entries whose title or description contains with the given search term. The content of
     *          the returned page depends from the page request given as a method parameter.
     */
    Page < Todo > findBySearchTermNamed(@Param("searchTerm") String searchTerm, Pageable pageRequest);

    /**
     * This query method invokes the named SQL query that is configured in the {@code Todo} class by using
     * the {@code @NamedNativeQuery} annotation. The name of the named native query is: {@code Todo.findBySearchTermNamedNative}.
     * @param searchTerm    The given search term.
     * @return  A list of todo entries whose title or description contains with the given search term. The returned
     *          todo entries are sorted in alphabetical order by using the title of the todo entry.
     */
    List < Todo > findBySearchTermNamedNative(@Param("searchTerm") String searchTerm);

    /**
     * This query method reads the named JPQL query from the {@code META-INF/jpa-named-queries.properties} file.
     * The name of the invoked query is: {@code Todo.findBySearchTermNamedFile}.
     * @param searchTerm    The given search term.
     * @param pageRequest   The information of the given page.
     * @return  A page of todo entries whose title or description contains with the given search term. The content of
     *          the returned page depends from the page request given as a method parameter.
     */
    Page < Todo > findBySearchTermNamedFile(@Param("searchTerm") String searchTerm, Pageable pageRequest);

    /**
     * This query method reads the named native query from the {@code META-INF/jpa-named-queries.properties} file.
     * The name of the invoked query is: {@code Todo.findBySearchTermNamedNativeFile}.
     * @param searchTerm    The given search term.
     * @return  A list of todo entries whose title or description contains with the given search term. The returned
     *          todo entries are sorted in alphabetical order by using the title of the todo entry.
     */
    @Query(nativeQuery = true)
    List < Todo > findBySearchTermNamedNativeFile(@Param("searchTerm") String searchTerm);

    /**
     * This query method reads the named from the {@code META-INF/orm.xml} file. The name of the invoked query
     * is: {@code Todo.findBySearchTermNamedOrmXml}.
     * @param searchTerm    The given search term.
     * @param pageRequest   The information of the given page.
     * @return  A page of todo entries whose title or description contains with the given search term. The content of
     *          the returned page depends from the page request given as a method parameter.
     */
    Page < Todo > findBySearchTermNamedOrmXml(@Param("searchTerm") String searchTerm, Pageable pageRequest);

    /**
     * This query method reads the named from the {@code META-INF/orm.xml} file. The name of the invoked query
     * is: {@code Todo.findBySearchTermNamedNativeOrmXml}.
     * @param searchTerm    The given search term.
     * @return  A list of todo entries whose title or description contains the given search term. The returned
     *          todo entries are sorted in alphabetical order by using the title of the todo entry.
     */
    @Query(nativeQuery = true)
    List < Todo > findBySearchTermNamedNativeOrmXml(@Param("searchTerm") String searchTerm);

    /**
     * This query method invokes the JPQL query that is configured by using the {@code @Query} annotation.
     * @param searchTerm    The given search term.
     * @param pageRequest   The information of the requested page.
     * @return  A page of todo entries whose title or description contains with the given search term. The content of
     *          the returned page depends from the page request given as a method parameter.
     */
    @Query("SELECT t FROM Todo t WHERE " +
        "LOWER(t.title) LIKE LOWER(CONCAT('%',:searchTerm, '%')) OR " +
        "LOWER(t.description) LIKE LOWER(CONCAT('%',:searchTerm, '%'))")
    Page < Todo > findBySearchTerm(@Param("searchTerm") String searchTerm, Pageable pageRequest);

    /**
     * This query method invokes the JPQL query that is configured by using the {@code @Query} annotation.
     * @param searchTerm    The given search term.
     * @return  A list of todo entries whose title or description contains with the given search term. The
     *          returned todo entries are sorted in alphabetical order by using the title of a todo entry.
     */
    @Query("SELECT t FROM Todo t WHERE " +
        "LOWER(t.title) LIKE LOWER(CONCAT('%',:searchTerm, '%')) OR " +
        "LOWER(t.description) LIKE LOWER(CONCAT('%',:searchTerm, '%')) " +
        "ORDER BY t.title ASC")
    List < Todo > findBySearchTermSortedInQuery(@Param("searchTerm") String searchTerm);

    /**
     * This query method invokes the SQL query that is configured by using the {@code @Query} annotation.
     * @param searchTerm    The given search term.
     * @return  A list of todo entries whose title or description contains with the given search term. The
     *          returned todo entries are sorted in alphabetical order by using the title of a todo entry.
     */
    @Query(value = "SELECT * FROM todos t WHERE " +
        "LOWER(t.title) LIKE LOWER(CONCAT('%',:searchTerm, '%')) OR " +
        "LOWER(t.description) LIKE LOWER(CONCAT('%',:searchTerm, '%')) " +
        "ORDER BY t.title ASC",
        nativeQuery = true
    )
    List < Todo > findBySearchTermNative(@Param("searchTerm") String searchTerm);

    Optional < Todo > findOne(Long id);

    void flush();

    Todo save(Todo persisted);
}

Related Posts

Reference


Comments

  1. How to write this query by method
    @Query(value = "SELECT user FROM User server WHERE user.firstName LIKE :s% or user.lastName LIKE :s% ")

    ReplyDelete
    Replies
    1. This one actually
      @Query(value = "SELECT user FROM User user WHERE user.firstName LIKE :s% or user.lastName LIKE :s% ")

      REPLY

      Delete

Post a Comment