Handle optional parameters in QueryDSL

Question

I am using QueryDSL with SpringData. I have Table say, Employee and I have created entity class say, EmployeeEntity I have written following service method

public EmployeeEntity getEmployees(String firstName, String lastName)
{
    QEmployeeEntity employee = QEmployeeEntity.employeeEntity;
    BooleanExpression query = null;
    if(firstName != null)
    {
        query = employee.firstName.eq(firstName);
    }
    if(lastName != null)
    {
        query = query.and(employee.lastName.eq(lastName)); // NPException if firstName is null as query will be NULL
    }
    return empployeeDAO.findAll(query);
}

As in above I commented the NPException. How to use QueryDSL for optional Parameters in QueryDSL using Spring Data?

Thank you :)

Answer 1

BooleanBuilder can be used as a dynamic builder for boolean expressions:

public EmployeeEntity getEmployees(String firstName, String lastName) {
    QEmployeeEntity employee = QEmployeeEntity.employeeEntity;
    BooleanBuilder where = new BooleanBuilder();
    if (firstName != null) {
        where.and(employee.firstName.eq(firstName));
    }
    if (lastName != null) {
        where.and(employee.lastName.eq(lastName));
    }
    return empployeeDAO.findAll(where);
}

Answer 2

BooleanBuilder is good. You can also wrap it and add "optional" methods in order to avoid the if conditions:

For example, for "and" you can write: (Java 8 lambdas are used)

public class WhereClauseBuilder implements Predicate, Cloneable
{
    private BooleanBuilder delegate;

    public WhereClauseBuilder()
    {
        this.delegate = new BooleanBuilder();
    }

    public WhereClauseBuilder(Predicate pPredicate)
    {
        this.delegate = new BooleanBuilder(pPredicate);
    }

    public WhereClauseBuilder and(Predicate right)
    {
        return new WhereClauseBuilder(delegate.and(right));
    }

    public <V> WhereClauseBuilder optionalAnd(@Nullable V pValue, LazyBooleanExpression pBooleanExpression)
    {
        return applyIfNotNull(pValue, this::and, pBooleanExpression);
    }

    private <V> WhereClauseBuilder applyIfNotNull(@Nullable V pValue, Function<Predicate, WhereClauseBuilder> pFunction, LazyBooleanExpression pBooleanExpression)
    {
        if (pValue != null)
        {
            return new WhereClauseBuilder(pFunction.apply(pBooleanExpression.get()));
        }

        return this;
    }
   }

    @FunctionalInterface
    public interface LazyBooleanExpression
    {
        BooleanExpression get();
    }

And then the usage would be much cleaner:

public EmployeeEntity getEmployees(String firstName, String lastName) {
    QEmployeeEntity employee = QEmployeeEntity.employeeEntity;

    return empployeeDAO.findAll
    (
       new WhereClauseBuilder()
           .optionalAnd(firstName, () -> employee.firstName.eq(firstName))
           .optionalAnd(lastName, () -> employee.lastName.eq(lastName))
    );
}

It is possible also to use jdk's Optional class

Answer 3

This is Java 101 actually: check for null and initialize the query instead of concatenating predicates. So a helper method like this could do the trick:

private BooleanExpression createOrAnd(BooleanExpression left, BooleanExpression right) {
  return left == null ? right : left.and(right);
}

Then you can simply do:

BooleanExpression query = null;

if (firstName != null) {
  query = createOrAnd(query, employee.firstName.eq(firstName));
}

if (lastName != null) {
  query = createOrAnd(query, employee.lastName.eq(lastName));
}

…

Note, that I use createOrAnd(…) even in the first clause simply for consistency and to not have to adapt that code in case you decide to add a new clause even before the one for firstName.