Query By Example in Spring data Jpa

1. Overview

In this article, we are going to discuss using the Query By Example (QBE) in Spring data JPA along with examples. The (QBE) Query by example is a query language used in relational databases that allow users to search for information in tables and fields by providing a simple user interface where the user inputs an example of the data that he or she wants to access.

2. Setup for Query By Example

First, let’s set up entity and repository class to use Query By Example (QBE).

2.1. Entity class

First, let’s create an entity class that represents the Customer table in the database. We specified the primary key of the entity class by annotating the field id with @Id. The newUser flag shows whether that customer is a new user.

@Entity
@AllArgsConstructor
@Data
public class Customer {

    @Id
    private String id;
    private String name;
    private int newUser;

}

Here, we have annotated the Entity class with annotations @AllArgsConstructor and @Data from the Lombok library to generate a constructor with all arguments and getters/setters. However, you can also manually create a constructor and getters/setters rather than using the Lombok library.

2.2. A JpaRepository instance

Now, let’s create a Repository class for the Spring JPA. The annotation @Repository is a specialization of @Component annotation which is used to show that the implementing class provides the mechanism for storage, retrieval, update, delete and search operation on DB objects.

You can also use the JpaRepository interface. The JpaRepository extends PagingAndSortingRepository which in turn extends CrudRepository. Because of this inheritance, JpaRepository will have all the functions of CrudRepository and PagingAndSortingRepository.

@Repository
public interface CustomerRepository extends JpaRepository<Customer, String> {

    List<Customer> findAllByName(String name, Pageable pageable);
}

3. Query by example

You can use QBE in Speing JPA as JpaRepository extends QueryByExampleExecutor interface. The Query by Example (QBE) is a method of query creation that allows us to execute queries based on an example entity instance. So you don’t even need to define any queries in your Repository interface.

The below code takes the customer instance as a query example and retrieves the count of new customers.

public Long getNewCustomerCount() {
    Customer customer = new Customer();
    customer.setNew_user(1);
    return customerRepository.count(Example.of(customer));
}

4. Conclusion

To sum up, we have learned the Query By Example in Spring data JPA with example. You can find the code samples of this project in our GitHub repository.