How to use Criteria Builder in your Java project
#Java, SpringBoot
If you are writing a REST API in Java and SpringBoot for your side projects or at work, I am guessing you have used JPA by now. You probably heard of CriteriaBuilder, but if not, don't worry. I personally have only heard of it a couple of years ago. Looking online on the timeline of this, it appears to be available since Java Persistence 2.0 (Source)
So it's about time I learn the power of writing complex queries in a way that, for example, in a refactoring situation I don't need to change strings in my queries. This is part one of using Criteria Builder (package javax.persistence).
So it's about time I learn the power of writing complex queries in a way that, for example, in a refactoring situation I don't need to change strings in my queries. This is part one of using Criteria Builder (package `javax.persistence`).
How I used to write queries (a long time ago). Do not do this now. We live in 2020 now.
String query = "Select usr.username from PlatformUser usr where usr.companyId = " + companyId;
How I am writing queries using positional parameters in queries:
@Repository
public interface PlatformUserRepository extends JpaRepository {
@Query("SELECT item FROM PlatformUser usr where usr.companyId = ?1")
List findAllCompanyUsernames (Long companyId);
}
What I could do with Criteria Builder:
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery criteriaQuery = criteriaBuilder.createQuery(String.class);
Root root = criteriaQuery.from(PlatformUser.class);
criteriaQuery.select(root);
Query query = entityManager.createQuery(criteriaQuery);
List results = query.getResultList();
In order to have access to the entity manager your need to inject it in your repository class.
@PersistenceContext
private EntityManager entityManager;
Documentation for Criteria Builder is lacking from my point of view, so I am trying my best to explain line by line.
**Example from the demo project:**
// Using Lombok plugin for getters and setters
@Getter
@Setter
public class CompanyHandyPersonDto {
private Long id;
private String fullName;
private Long companyId;
private Long tradeId;
private String comment;
}
A simple select from a table:
@Repository
public class CompanyHandyPersonRepository implements CompanyHandyPersonRepositoryInterface {
@PersistenceContext
private EntityManager entityManager;
@Override
public List findAllHandyPersons() {
// Get instance of criteria builder from an entity manager
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
// Create a query object
CriteriaQuery criteriaQuery = criteriaBuilder.createQuery(CompanyHandyPerson.class);
// Root of this query (I have no better idea of how to explain this)
Root root = criteriaQuery.from(CompanyHandyPerson.class);
// Choosing what data the query returs
criteriaQuery.select(root);
Query query = entityManager.createQuery(criteriaQuery);
// Run the query constructed above and extract the result
List results = query.getResultList();
return results;
}
}
Find a list of objects that satisfy a condition:
```
@Override
public List findCompanyHandyPeopleThatAreSoftwareDevelopers() {
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery criteriaQuery = criteriaBuilder.createQuery(CompanyHandyPerson.class);
Root root = criteriaQuery.from(CompanyHandyPerson.class);
// Check against the tradeId representing a specific one
criteriaQuery.select(root).where(criteriaBuilder.equal(root.get("tradeId"), 1L));
Query query = entityManager.createQuery(criteriaQuery);
List results = query.getResultList();
return results;
}
Find a list of objects with a LIKE condition:
@Override
public List findAllTradesWithNameSimilarTo(String tradeName) {
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery criteriaQuery = criteriaBuilder.createQuery(Trade.class);
Root root = criteriaQuery.from(Trade.class);
criteriaQuery.select(root).where(criteriaBuilder.like(root.get("name"), "%" + tradeName + "%"));
Query query = entityManager.createQuery(criteriaQuery);
List results = query.getResultList();
return results;
}
Find a list of objects that have an Id in a given array of Ids:
@Override
public List findCompanyHandyPeopleThatHaveOneOfTheGivenTradeId(Long[] tradeIds) {
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery criteriaQuery = criteriaBuilder.createQuery(CompanyHandyPerson.class);
Root root = criteriaQuery.from(CompanyHandyPerson.class);
// Check tradeId is part of the given tradeIds
criteriaQuery.select(root)
.where(root.get("tradeId")
.in(tradeIds));
Query query = entityManager.createQuery(criteriaQuery);
List results = query.getResultList();
return results;
}
Return a list of non-empty strings for *userFeedbackTitle* or *userFeedbackDescription* (coalesce expression)
> Note: a job has `userFeedbackTitle` and `userFeedbackDescription`. None of these are forced to have values. Both can be null, both or only one can have values.
@Override
public List findAllFeedbackThatExists() {
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery criteriaQuery = criteriaBuilder.createQuery(Job.class);
Root root = criteriaQuery.from(Job.class);
// Check title and description and return when one of them is not null
CriteriaBuilder.Coalesce coalesceExpression = criteriaBuilder.coalesce();
coalesceExpression.value(root.get("userFeedbackTitle"));
coalesceExpression.value(root.get("userFeedbackDescription"));
criteriaQuery.select(coalesceExpression);
Query query = entityManager.createQuery(criteriaQuery);
List results = query.getResultList();
return results;
}
If you want to remove all null values from the above example, you can do so by using a Predicate:
Predicate nullFilter = PojoPredicates.isValueNull();
results.removeIf(nullFilter);
Find all users that have jobs requests that have the job status- In Progress:
@Override
public List findUsersThatHaveJobsInProgress() {
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery userCriteriaQuery = criteriaBuilder.createQuery(PlatformUser.class);
Root userRoot = userCriteriaQuery.from(PlatformUser.class);
// Select user and job by user_id
Subquery jobSubQuery = userCriteriaQuery.subquery(Job.class);
Root jobRoot = jobSubQuery.from(Job.class);
jobSubQuery.select(jobRoot).where(criteriaBuilder.equal(jobRoot.get("userId"), userRoot.get("id")));
userCriteriaQuery.select(userRoot).where(criteriaBuilder.exists(jobSubQuery));
TypedQuery typedQuery = entityManager.createQuery(userCriteriaQuery);
List resultList = typedQuery.getResultList();
return resultList;
}
It's great so far. Next, I will think of more complex situations where CriteriaBuilder is a better choice.
Here is the GitHub project demo for this: Demo Project Source Code