Handling Optional Parameters in QueryDSL

Question

I am using QueryDSL with SpringData. I have a table, Employee and I created an entity class, EmployeeEntity I wrote the following maintenance 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 the previous one, I commented on NPException . How to use QueryDSL for optional parameters in QueryDSL using Spring data?

Thanks:)

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 to avoid if conditions:

For example, for "and" you can write: (using the lambdas Java 8 kernel)

 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 use will 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)) ); } 

You can also use jdk. Optional class.

Answer 3

This is Java 101 in fact: check null and initialize the query instead of concatenating predicates. So a helper method like this can 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)); } … 

Please note that I use createOrAnd(…) even in the first sentence just for consistency and should not adapt this code if you decide to add a new sentence before it was for firstName .

Answer 4

I ran into the same problem and here is another version of Timo Westcamper accepted the answer using 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); } 

Answer 5

if you checked the implementation of QueryDSL 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, presumably, you want.

Answer 6

Based on what you need, I would do it

 public List<EmployeeEntity> getEmployees(Optional<String> firstName, Optional<String> lastName) { BooleanExpression queryPredicate = QEmployeeEntity.employeeEntity.firstName.containsIgnoreCase(firstName.orElse("")).and(QEmployeeEntity.employeeEntity.lastName.containsIgnoreCase(lastName.orElse(""))); return empployeeDAO.findAll(queryPredicate); } 

First of all, you should return List from EmployeeEntity . Secondly, it’s better to use an option than checking if it is null , and you can pass Java 8 Optional values ​​obtained from optional RequestParam such as this:

 @RequestMapping(value = "/query", method = RequestMethod.GET) public ModelAndView queryEmployee(@RequestParam(value = "firstName", required = false) Optional<String> firstName, @RequestParam(value = "lastName", required = false) Optional<String> lastName) { List<EmployeeEntity> result = getEmployees(firstName, lastName); .... } 

And it is very important to use the containsIgnoreCase function in the predicate: it is better than typical like , cause case insensitivity.

In my opinion, you should use this approach:

 @Controller class UserController { @Autowired UserRepository repository; @RequestMapping(value = "/", method = RequestMethod.GET) String index(Model model, @QuerydslPredicate(root = User.class) Predicate predicate, Pageable pageable, @RequestParam MultiValueMap<String, String> parameters) { model.addAttribute("users", repository.findAll(predicate, pageable)); return "index"; } } 

look here .

Answer 7

This is a very simple way to work with optional parameters, I use it in my project:

  public List<ResultEntity> findByOptionalsParams(String param1, Integer param2) { QResultEntity qResultEntity = QResultEntity.resultEntity; final JPQLQuery<ResultEntity> query = from(qResultEntity); if (!StringUtils.isEmpty(param1)) { query.where(qResultEntity.field1.like(Expressions.asString("%").concat(param1).concat("%"))); } if (param2 != null) { query.where(qResultEntity.field2.eq(param2)); } return query.fetch(); } 

Answer 8

There is another way to use Optional without a BooleanBuilder , although the resulting query may be a bit verbose:

 public EmployeeEntity getEmployees(String firstName, String lastName) { QEmployeeEntity employee = QEmployeeEntity.employeeEntity; BooleanExpression where = ofNullable(firstName).map(employee.firstName::eq).orElse(Expressions.TRUE) .and(ofNullable(lastName).map(employee.lastName::eq).orElse(Expressions.TRUE)); return empployeeDAO.findAll(where); } 

This idea and the addition of an auxiliary function improves readability:

 public EmployeeEntity getEmployees(String firstName, String lastName) { QEmployeeEntity employee = QEmployeeEntity.employeeEntity; BooleanExpression where = optionalExpression(firstName, employee.firstName::eq) .and(optionalExpression(lastName, employee.lastName::eq)); return empployeeDAO.findAll(where); } public static <T> BooleanExpression optionalExpression(T arg, Function<T, BooleanExpression> expressionFunction) { if (arg == null) { return Expressions.TRUE; } return expressionFunction.apply(arg); }