Handle optional parameters in QueryDSL
Solution 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);
}
Solution 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
Solution 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
.
Solution 4
if you check the QueryDSL implementation of null
:
public BooleanExpression and(@Nullable Predicate right) {
right = (Predicate) ExpressionUtils.extract(right);
if (right != null) {
return BooleanOperation.create(Ops.AND, mixin, right);
} else {
return this;
}
}
which is supposedly what you want.
Solution 5
I faced same problem and here comes another version of Timo Westkämper
's accepted answer using the Optional
.
default Optional<Correlation> findOne(
@Nonnull final String value, @Nullable final String environment,
@Nullable final String application, @Nullable final String service) {
final QSome Some = QSome.some;
final BooleanBuilder builder = new BooleanBuilder();
ofNullable(service).map(some.service::eq).map(builder::and);
ofNullable(application).map(some.application::eq).map(builder::and);
ofNullable(environment).map(some.environment::eq).map(builder::and);
builder.and(some.value.eq(value));
return findOne(builder);
}
Prashant Shilimkar
Updated on July 09, 2022Comments
-
Prashant Shilimkar almost 2 years
I am using QueryDSL with SpringData. I have Table say,
Employee
and I have created entity class say,EmployeeEntity
I have written following service methodpublic 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 :)
-
Hayi almost 10 yearsbut if we have joins ? can you help me out there stackoverflow.com/questions/24792778/dynamic-search-by-criteria
-
HasBert about 3 yearsThank you this really helped me out! I like how the code is written!