Trying out Criteria Builder in a Java project

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 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<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