Blog

Understanding Criteria Query with Use Case

Introduction

• JPA provides multiple ways to query data from the database such as JPQL, and Native SQL Queries, Using Repository methods.
• One of the ways to interact with the database is by using Criteria API.
• In this article, we will learn about Criteria API and how to use them to query databases.

Entity 

• We will use the BOOK_REVIEWS table for this example.

@Entity
@Table(name="BOOK_REVIEWS")
public class BookReview {

    @Id
    @GeneratedValue(strategy= GenerationType.SEQUENCE, generator = "book_reviews_reviews_id_seq")
    @SequenceGenerator(name = "book_reviews_reviews_id_seq", sequenceName = "book_reviews_reviews_id_seq", allocationSize = 1)
    private Long reviewsId;
    private String userId;
    private String isbn;
    private String bookRating;

  // setters and getters
}

Criteria API

• Criteria API provides a set of interfaces and classes that helps build & execute queries.
• This interface allows us to write queries in Object oriented way so that we can skip the knowledge of writing raw SQL queries.

CriteriaBuilder & CriteriaQuery

• The first step is to build the CriteriaBuilder object.

// creating criteria builder and query
 CriteriaBuilder builder = entityManager.getCriteriaBuilder();

• Once we have a builder object we can construct a criteria query, which will be required to get the Root object and execution of the query.

CriteriaQuery<Long> criteriaQuery = builder.createQuery(Long.class);
Root<Account> accountRoot = criteriaQuery.from(Account.class);

Simple Query Execution

• Once we have CriteriaQuery and Root, we can define our query such as Select.

// select query
     criteriaQuery
                .select(builder.count(accountRoot));

• The last step is to execute the query and get the result.

// execute and get the result
 return entityManager.createQuery(criteriaQuery).getSingleResult();

Result count

Conditional Query

// condition
Predicate[] conditions = new Predicate[1];
conditions[0] = builder.greaterThan(root.get("bookRating"), "3.0");

// select query
criteriaQuery
        .select(builder.count(root))
        .where(conditions);

Result count

Conclusion

• CriteriaQuery help us write SQL query in Object oriented way, which as a Java developer allow us to write more flexible queries without relying on RAW SQL query.
• Apart from select queries, we can also perform delete, and update queries.

Before You Leave

• If you like this content consider becoming medium member to support.
• Upgrade your Java skills with Grokking the Java Interview
• If you want to upskill your Java skills, you should definitely check out
  [NEW] Master Spring Boot 3 & Spring Framework 6 with Java
  [ 38 hrs content, 4.7/5 stars, 6+ students already enrolled]