JPA Criteria Api – A code alternate to raw SQL queries

Introduction

JPA is a hibernate specification that helps us in managing, accessing & persisting data between RDBMS and Java objects & one of its main features is Criteria API, which is a pre-defined API and was introduced in JPA 2.0. It defines platform-independent criteria queries written in Java and enables us to achieve the same result as a raw query programmatically. It provides us with some object-oriented control over the raw SQL queries. By writing a criteria you define the where clause of a query for your specified domain class.

Basic components

• CriteriaBuilder — It is a factory class obtained from the entity manager to create various query-related objects such as criteriaQuery, predicates, etc.
• CriteriaQuery — It represents the structure of the query specifying the “select” clause, “where” clause & ordering of the resultSet.
• Root — This represents the Entity on which the query will operate i.e. “from” clause of a query.
• Predicates — This represents the “where” clause of the query.

Steps to Create Criteria Query

• Create an EntityManagerFactory & EntityManager instance.
• Create a CriteriaBuilder instance using the session’s getCriteriaBuilder() method.
• Create a CriteriaQuery instance using criteriaBuilder’s createQuery() method.
• Once we have created the criteriaQuery instance we need to set the query’s root using its from() method.
• To get the final result, create a query instance using the session’s createQuery() method by passing criteria query as an argument and then use query.getResultList() or query.getResultSet() method.

Implementing JPA Criteria Query

Define an Entity

import lombok.*;
import javax.persistence.*;

@Entity
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@EqualsAndHashCode
public class DemoEmp {
@Id
@GeneratedValue(strategy= GenerationType.AUTO)
private int id;
private String name;
private int salary;
private int experience;
private String departmentName;
}

Define the Criteria Builder

EntityManagerFactory emf = Persistence.createEntityManagerFactory("my-persistence-unit");
EntityManager em = emf.createEntityManager();
CriteriaBuilder criteria = em.getCriteriaBuilder(); 
CriteriaQuery<DemoEmp> cq = criteria.createQuery(DemoEmp .class); 
Root<DemoEmp> root = cq.from(DemoEmp .class); 
cq.select(root); 
Query<DemoEmp> query = em.createQuery(cq);

The above code will get all the rows of the DemoEmp entity from the database.

We can also use CriteriaBuilder for restricting query results based on the conditions using expressions

• Fetch entities with salary greater than 50000

cq.select(root).where(criteria.gt(root.get("salary"), 50000));

• Fetch entities having experience between 2 and 5

cq.select(root).where(criteria.between(root.get("experience"), 2, 5));

There are various other methods as well like isNull(), isNotNull(), isEmpty(), isNotEmpty(), in(), like(), aggregate methods etc.

We can also chain these expressions to achieve our desired condition using Predicates.

Predicate greaterThanSalary = criteria.gt(root.get("salary"), 50000);
Predicate experience = criteria.between(root.get("experience"), 2, 5);

Now we can join these 2 conditions with either a Logical “AND” or a Logical “OR” based on our requirement.

cq.select(root).where(criteria.or(greaterThanSalary, experience));
cq.select(root).where(criteria.and(greaterThanSalary, experience));

GroupBy & Having Clause

The AbstractQuery interface of Criteria API has two methods groupBy() and having() which are used to filter the data by grouping them and to set the desired conditions on the grouped data respectively.

CriteriaQuery<DemoEmp> cq = criteria.createQuery(DemoEmp .class); 
Root<DemoEmp> root = cq.from(DemoEmp .class); 
cq.multiselect(root.get("experience"),criteria.count(root)).groupBy(root.get("experience")); 
List<Object[]> results = em.createQuery(cq).getResultList();

In the above code, we are grouping the number of DempEmp based on their experience.

CriteriaQuery<DemoEmp> cq = criteria.createQuery(DemoEmp .class); 
Root<DemoEmp> root = cq.from(DemoEmp .class); 
cq.multiselect(root.get("experience"),criteria.count(root)).groupBy(root.get("experience")).having(criteria.ge(root.get("experience"), 5));  
List<Object[]> results = em.createQuery(cq).getResultList();

In the above code, we are grouping the number of DempEmp having an experience either equal or greater than 5.

CriteriaUpdates

CriteriaUpdates feature allows us to update multiple records in the database using Criteria API. We can create a CriteriaUpdate instance using CriteriaBuilder’s createCriteriaUpdate() method.

This interface has a set() method that enables us to update the existing record with the desired value. We can update multiple attributes & multiple records simultaneously.

CriteriaUpdate<DemoEmp> criteriaUpdate = criteria.createCriteriaUpdate(DemoEmp.class); 
Root<DemoEmp> root = criteriaUpdate.from(DemoEmp.class); 
criteriaUpdate.set("experience", 10); 
criteriaUpdate.set("salary", 1000000); 
criteriaUpdate.where(criteria.equal(root.get("id"), 5))
em.createQuery(criteriaUpdate).executeUpdate();

The above code will update the experience attribute value to 10 & salary attribute value to 100000 for the DemoEmp with id =5

CriteriaDelete

CriteriaDelete feature allows us to delete multiple records from the database using Criteria API. We can create a CriteriaDelete instance using CriteriaBuilder’s createCriteriaDelete() method. We can delete multiple records by providing a where clause for restrictions.

CriteriaDelete<DemoEmp> criteriaDelete = criteria.createCriteriaDelete(DemoEmp.class);
Root<DemoEmp> root = criteriaDelete.from(DemoEmp.class); 
criteriaDelete.where(criteria.equal(root.get("departmentName"), "Research")); 
em.createQuery(criteriaDelete).executeUpdate();

The above code will delete all the employees of the “Research” department.

Criteria Join

JPA provides a Join interface for performing joins for retrieving data from various interconnected database tables. We can perform multiple joins within a single query as well. To understand this in detail let’s change our entity and add some relationships to practice joins.

Updated Entity

import lombok.*;
import javax.persistence.*; 
import java.util.List;

@Entity 
@Getter 
@Setter 
@NoArgsConstructor 
@AllArgsConstructor 
@Builder 
@EqualsAndHashCode 
public class DemoEmp { 
@Id 
@GeneratedValue(strategy= GenerationType.AUTO) 
private int id; 
private String name; 
private int salary; 
private int experience; 
private String departmentName;

@ManyToMany(fetch = FetchType.LAZY)
@JoinTable(name = "demo_emp_project_mapping",
joinColumns = {@JoinColumn(name = "demo_emp_id")},
inverseJoinColumns = {@JoinColumn(name = "project_id")}
)
@JsonBackReference
private List<Project> projects;

@ElementCollection
private List<String> certifications;
}

import lombok.*;
import javax.persistence.*;

@Entity 
@Getter 
@Setter 
@NoArgsConstructor 
@AllArgsConstructor 
@EqualsAndHashCode 
public class Project {
@Id 
@GeneratedValue(strategy= GenerationType.AUTO) 
private int id; 
private String name;

@ManyToMany(mappedBy = "projects")
@JsonBackReference
private List<DemoEmp> employees;
}

CriteriaQuery<DemoEmp> cq = criteria.createQuery(DemoEmp .class); 
Root<DemoEmp> root = cq.from(DemoEmp .class); 
List<Predicate> conditions = new ArrayList();

ListJoin<DemoEmp, String> empCertificationJoin = root.joinList("certifications");
conditions.add(criteria.equal(empCertificationJoin, "XYZ_Certification"));

Join<DemoEmp, Project> empProjectJoin = root.join("projects");
conditions.add(criteria.equal(empProjectJoin.get("id"), 1));

cq.orderBy( criteria.asc(root.get("salary")), criteria.desc(root.get("experience")));
cq.select(root).where(conditions.toArray(new Predicate[]{})).distinct(true);

List<DempEmp> finalOutput = em.createQuery(cq).getResultList;

The above code will retrieve all the employees having “XYZ_Certification” and worked on the project with id “1“. CriteriaQuery’s “where(conditions.toArray(new Predicate[]{}))” combines all the predicates present in the list in a logical “and“.

Benefits of using Criteria Queries

• Dynamic Query Building — Nice, clean & object-oriented API dynamic code
• Type-Safety — It has the type-safety benefits of Java.
• Metamodel Integration — Compile time error detection.
• Refactoring Support — Easy to modify as it provides better support for dynamic queries as compared to HQL & JPQL.

Conclusion

JPA Criteria Query API is a powerful feature that allows you to write a clean, object-oriented, type-safe code. The CriteriaUpdate & CriteriaDelete introduced in JPA 2.1 make JPA Criteria API a better solution by providing the condition-specific bulk update & delete functionality that was missing in the earlier version. Criteria Joins provides us precision, flexibility, and efficiency as we can define complex conditions for merging data using multiple criteria and logical operators. Using criteria for joins helps us ignore the unnecessary matches and reduces the dataset size which helps to improve performance while dealing with large datasets.