Start using Criteria Builder in your Java project

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

Notes mentioning this note

There are no notes linking to this note.