Get started with JPA Criteria Query API
JPA Criteria Query API is a pre-defined API, used to define queries for Entities. It is a different way to define a query than the usual way of using JPQL. JPA Criteria Query is safe, portable, and easily modified by changing the syntax.
The main advantage of the Criteria Query API is that errors can be detected earlier in compile time. JPQL is written in text form, while Criteria Query is built based on criteria, has a Java color and is more abstract than JPQL. However both are similar in terms of performance and efficiency.
- Get Started with JPA JPQL
No ADS
JPQL and Criteria Query are closely related, both use similar operators in their queries and both comply with the jakarta.persistence.criteria package.
Criteria Query API includes many features and syntaxes, and it is impossible to cover them all in one article. Therefore, this article only guides you through declaring libraries and starting with basic Criteria Query examples. You will find suggested related articles at the end of this article.
1. Library
If you use the JPA Criteria Query API in your Spring Boot project, add the following library:
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
2. Entities
Let's look at the sample data model we'll use in the examples.
Employee.java
package org.o7planning.sample_bank_db.entity;
// Imports
@Entity
@Table(name = "Employee")
public class Employee implements Serializable {
@Id
@GeneratedValue
@Column(name = "Emp_Id", nullable = false)
private Long empId;
@Column(name = "Start_Date", nullable = false)
private LocalDate startDate;
@Column(name = "End_Date", nullable = true)
private LocalDate endDate;
@Column(name = "First_Name", length = 32, nullable = false)
private String firstName;
@Column(name = "Last_Name", length = 32, nullable = false)
private String lastName;
@Column(name = "Title", length = 32, nullable = false)
private String title;
@ManyToOne
@JoinColumn(name = "Dept_Id", nullable = true, //
foreignKey = @ForeignKey(name = "Employee_Department_Fk"))
private Department department;
@ManyToOne
@JoinColumn(name = "Superior_Emp_Id", nullable = true, //
foreignKey = @ForeignKey(name = "Employee_Employee_Fk"))
private Employee superiorEmployee;
// Other Properties, Setters and Getters.
}
The data model used in this article is a sample database, simulating the data of a bank. If you are interested, you can download the project below, it is written on Spring Boot + JPA and supports all types of databases. When you run the project, tables and data are automatically created.
- JPA Sample Bank Database
3. Simple example
Let's start with a simple example, querying all records in the Employee table with the JPA Criteria API. We will then explain each line of code.
Note: This is a simple example using JPA in Spring. If you use JPA in a regular Java project there is not much difference, just create the EntityManager object manually.
SimpleExample.java
package org.o7planning.jpa_criteria_query.java_11215;
import java.util.List;
import org.o7planning.sample_bank_db.entity.Employee;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import jakarta.persistence.EntityManager;
import jakarta.persistence.TypedQuery;
import jakarta.persistence.criteria.CriteriaBuilder;
import jakarta.persistence.criteria.CriteriaQuery;
import jakarta.persistence.criteria.Root;
@Component
public class SimpleExample {
@Autowired
private EntityManager entityManager;
public void test() {
// (1)
CriteriaBuilder builder = entityManager.getCriteriaBuilder();
// (2)
CriteriaQuery<Employee> criteriaQuery = builder.createQuery(Employee.class);
// (3)
Root<Employee> root = criteriaQuery.from(Employee.class);
// (4) - (select, where, orderBy, groupBy, ...)
// --> Select * from EMPLOYEE
criteriaQuery = criteriaQuery.select(root);
// (5)
TypedQuery<Employee> query = entityManager.createQuery(criteriaQuery);
// (6)
List<Employee> allEmps = query.getResultList();
System.out.println("Emp Count: " + allEmps.size());
}
}
No ADS
No | Code/Description |
1 |
|
2 |
|
3 |
|
4 |
|
5 |
|
6 |
|
4. Use expressions
Find all employees named "Susan".
//
// Select e from Employee e where e.firstName = 'Susan'
//
criteriaQuery //
.select(root) // --> from Employee e
// where e.firstName ='Susan'
.where(builder.equal(root.get("firstName"), "Susan"));
Find all employees other than "Susan".
//
// Select e from Employee e where e.firstName != 'Susan'
//
criteriaQuery //
.select(root) // --> from Employee e
// where e.firstName != 'Susan'
.where(builder.notEqual(root.get("firstName"), "Susan"));
No ADS
Find employees whose names are in a specified list.
criteriaQuery //
.select(root) // --> from Employee e
// where e.firstName in ('Susan', 'Robert')
.where(root.get("firstName").in("Susan", "Robert"));
Find employees with "empId" less than 10.
criteriaQuery //
.select(root) // --> from Employee e
// where e.empId < 10
.where(builder.lt(root.get("empId"), 10));
Find employees who started working at the company after a specified date.
criteriaQuery //
.select(root) // --> from Employee e
// where e.startDate > LocalDate.of(2022, 1, 1)
.where(builder.greaterThan(root.get("startDate"), LocalDate.of(2002, 1, 1)));
Find employees who are still working at the company.
criteriaQuery //
.select(root) // --> from Employee e
// where e.endDate is null
.where(builder.isNull(root.get("endDate")));
Note: The examples mentioned in this article are basic, there is only one Entity involved in the query, if you want an example with multiple Entities, you can see the article below:
- JPA Criteria Query API Join
5. Combine conditions
The JPA Criteria API allows you to combine multiple conditions into one condition.
Predicate[] predicates = new Predicate[2];
predicates[0] = builder.greaterThan(root.get("startDate"), LocalDate.of(2002, 01, 01));
predicates[1] = builder.equal(root.get("firstName"), "Susan");
//
criteriaQuery //
.select(root) // --> from Employee e
// where e.startDate > LocalDate.of(2002, 01, 01) and e.firstName = 'Susan'
.where(predicates);
No ADS
Use the logical operator "AND":
Predicate predicate1 = builder.greaterThan(root.get("startDate"), LocalDate.of(2002, 01, 01));
Predicate predicate2 = builder.equal(root.get("firstName"), "Susan");
// (4)
criteriaQuery //
.select(root) // --> from Employee e
// where e.startDate > LocalDate.of(2002, 01, 01) and e.firstName = 'Susan'
.where(builder.and(predicate1, predicate2));
Use the logical operator "OR":
Predicate predicate1 = builder.greaterThan(root.get("startDate"), LocalDate.of(2002, 01, 01));
Predicate predicate2 = builder.equal(root.get("firstName"), "Susan");
// (4)
criteriaQuery = criteriaQuery //
.select(root) // --> from Employee e
// where e.startDate > LocalDate.of(2002, 01, 01) or e.firstName = 'Susan'
.where(builder.or(predicate1, predicate2));
No ADS