Initially published on my blog: https://documentingiscool.netlify.app/blog/2020/08/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).
How I used to write queries (long time ago). Do not do this now. It's 2020:
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<PlatformUser, Long> {
@Query("SELECT item FROM PlatformUser usr where usr.companyId = ?1")
List<String> findAllCompanyUsernames (Long companyId);
}
What I could do with Criteria Builder:
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<String> criteriaQuery = criteriaBuilder.createQuery(String.class);
Root<PlatformUser> root = criteriaQuery.from(PlatformUser.class);
criteriaQuery.select(root);
Query query = entityManager.createQuery(criteriaQuery);
List<String> 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<CompanyHandyPerson> findAllHandyPersons() {
// Get instance of criteria builder from an entity manager
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
// Create a query object
CriteriaQuery<CompanyHandyPerson> criteriaQuery = criteriaBuilder.createQuery(CompanyHandyPerson.class);
// Root of this query (I have no better idea of how to explain this)
Root<CompanyHandyPerson> root = criteriaQuery.from(CompanyHandyPerson.class);
// Choosing what data the query returns
criteriaQuery.select(root);
Query query = entityManager.createQuery(criteriaQuery);
// Run the query constructed above and extract the result
List<CompanyHandyPerson> results = query.getResultList();
return results;
}
}
Find a list of objects that satisfy a condition:
@Override
public List<CompanyHandyPerson> findCompanyHandyPeopleThatAreSoftwareDevelopers() {
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<CompanyHandyPerson> criteriaQuery = criteriaBuilder.createQuery(CompanyHandyPerson.class);
Root<CompanyHandyPerson> 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<CompanyHandyPerson> results = query.getResultList();
return results;
}
Find a list of objects with a LIKE condition:
@Override
public List<Trade> findAllTradesWithNameSimilarTo(String tradeName) {
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<Trade> criteriaQuery = criteriaBuilder.createQuery(Trade.class);
Root<Trade> root = criteriaQuery.from(Trade.class);
criteriaQuery.select(root).where(criteriaBuilder.like(root.get("name"), "%" + tradeName + "%"));
// Or without taking into consideration lower or upper cases
/**
* Predicate likePredicate = *criteriaBuilder.like(criteriaBuilder.lower(pantryItemRoot.get("name")), "%" + input.toLowerCase() + "%");
*pantryItemCriteriaQuery.select(pantryItemRoot).where(likePredicat*e);
*/
Query query = entityManager.createQuery(criteriaQuery);
List<Trade> results = query.getResultList();
return results;
}
Find a list of objects that have an Id in a given array of Ids:
@Override
public List<CompanyHandyPerson> findCompanyHandyPeopleThatHaveOneOfTheGivenTradeId(Long[] tradeIds) {
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<CompanyHandyPerson> criteriaQuery = criteriaBuilder.createQuery(CompanyHandyPerson.class);
Root<CompanyHandyPerson> 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<CompanyHandyPerson> results = query.getResultList();
return results;
}
Return a list of non-empty strings for userFeedbackTitle or userFeedbackDescription (coalesce expression)
Note: a job has
userFeedbackTitleanduserFeedbackDescription. None of these are forced to have values. Both can be null, both or only one can have values.
@Override
public List<String> findAllFeedbackThatExists() {
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<Job> criteriaQuery = criteriaBuilder.createQuery(Job.class);
Root<Job> root = criteriaQuery.from(Job.class);
// Check title and description and return when one of them is not null
CriteriaBuilder.Coalesce<Job> coalesceExpression = criteriaBuilder.coalesce();
coalesceExpression.value(root.get("userFeedbackTitle"));
coalesceExpression.value(root.get("userFeedbackDescription"));
criteriaQuery.select(coalesceExpression);
Query query = entityManager.createQuery(criteriaQuery);
List<String> 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<String> nullFilter = PojoPredicates.isValueNull();
results.removeIf(nullFilter);
Find all users that have jobs requests that have the job status- In Progress:
@Override
public List<PlatformUser> findUsersThatHaveJobsInProgress() {
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<PlatformUser> userCriteriaQuery = criteriaBuilder.createQuery(PlatformUser.class);
Root<PlatformUser> userRoot = userCriteriaQuery.from(PlatformUser.class);
// Select user and job by user_id
Subquery<Job> jobSubQuery = userCriteriaQuery.subquery(Job.class);
Root<Job> 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<PlatformUser> typedQuery = entityManager.createQuery(userCriteriaQuery);
List<PlatformUser> 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
Top comments (0)