Conditional CriteriaBuilder for Optional Params
Tags: java, spring, specification, database
Published: 2020-03-30 01:43:53

---
title: Conditional CriteriaBuilder for Optional Params
published: true
description: Conditional Database Queries with JPA Specification
tags: java, spring, specification, database
---

Recently I came across an interesting challenge at work where I needed to conditionally query data based on a series of Optional fields — that is, I needed to query what was asked for and nothing else.

This task was easily (once I figured it out) solved with JPA Specifications.

Look at the following example where I will use data fields on let’s say…birthday attendees:

<img src="https://miro.medium.com/max/1400/0*oEbGtW9YBb-gU-4Q" alt="Two doors, one yellow and one red, give the viewer a choice."/>

```java
public Specification<Attendee> filterAttendees(Optional<String> firstNameFilter, Optional<String> lastNameFilter) {

    return (root, criteriaQuery, criteriaBuilder) -> {
        List<Predicate> predicates = new ArrayList<>();
        if (firstNameFilter.isPresent() && (firstNameFilter.get().length() > 0)) {
            predicates.add(criteriaBuilder.and(criteriaBuilder.like(criteriaBuilder.lower(root.get("firstName")), "%" + firstNameFilter.get().toLowerCase() + "%")));
        }
        if (lastNameFilter.isPresent() &&(lastNameFilter.get().length() > 0)) {
            predicates.add(criteriaBuilder.and(criteriaBuilder.like(criteriaBuilder.lower(root.get("lastName")), "%" + lastNameFilter.get().toLowerCase() + "%")));
        }

        return criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()]));
    };
}
```

The example above will fetch any Attendee whose name is LIKE the String provided. Here’s how:

- The method takes two Optional parameters, which means that I can provide both, one, or neither. I will talk about this more towards the end.

- The line return (root, criteriaQuery, criteriaBuilder) -> passes in the root Attendee, a database query, and the criteria that you build to perform said query. I have written this as a lamba for brevity’s sake, but the older form of writing Specification() will work as well.

- I create a List of Predicates, which will be combined into my criteriaBuilder to make a comprehensive set of criteria. As with English, Predicate here is the basis or the what of the full expression. You will see in the last line that I add these all together into a singular Predicate and use that to create my CriteriaBuilder, which I return.

- The bulk of this whole code segment, and what makes it truly conditional are the two similar statements that check that a filter .isPresent() and not empty (that the length is greater than 0). If these conditions are met, I know that my method has been passed parameters that need to be included in my search. Once I have those, I am able to tell the criteriaBuilder to find instances LIKE them in the database and to include them in the CriteriaBuilder.

- Having those Optional parameters is what really lets this method be dynamic. Because I am handling the chance that a parameter comes in null, I can use this method to look up attendees by full name, just first, or just last. I can even set this up to return all attendees if the parameters were to come back fully blank. The method would be robust and cut out a lot of user error and some form validation on the front end.

Thanks for your time and I hope you find a use for this!