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 :)

score 43 · Accepted Answer · answered May 27 '14 at 18:17

43

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

answered May 27 '14 at 18:17

Timo Westkämper

21,824
5
78
111

but if we have joins ? can you help me out there http://stackoverflow.com/questions/24792778/dynamic-search-by-criteria – Hayi Jul 16 '14 at 23:59

score 10 · Answer 2 · answered Nov 02 '15 at 15:20

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

score 3 · Answer 3 · answered May 20 '14 at 07:03

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.

score 2 · Answer 4 · edited Mar 31 '17 at 03:30

2

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.

edited Mar 31 '17 at 03:30

ben3000

4,629
6
24
43

answered May 25 '14 at 20:15

ameen

101
1
3

score 2 · Answer 5 · answered Sep 19 '18 at 04:04

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

Thank you this really helped me out! I like how the code is written! — HasBert, Feb 10 '21 at 15:28

score 1 · Answer 6 · answered Aug 20 '20 at 13:14

For any one wants to build predicate based on dynamic request parameters map instead of specific ones can use the following simple format,


public List<User> searchUser(Map<String, Optional<String>> requestParams ){
        QUser qUser = Quser.qUser;
    
        BooleanBuilder builder = new BooleanBuilder();
        requestParams.forEach( (String key, String value) -> {
            if(!value.isEmpty()) {
                StringPath column = Expressions.stringPath(qUser, key);
               builder.and(column.eq(value));
            }
        });
  }

And here is my controller

@RequestMapping(value = "", method = RequestMethod.GET) 
    public ResponseEntity<List<User>> searchUser(
            @RequestParam() Map<String, Optional<String>> requestParams) {

        List<User> userList = userService.searchUser(requestParams);
        
         if(userList!=null)
            return new ResponseEntity<>(userList, HttpStatus.OK);
        else
            return new ResponseEntity<>(userList, HttpStatus.INTERNAL_SERVER_ERROR);
    }

EliuX · Answer 7 · 2015-09-25T16:00:24.143

Base on what you need i would do this

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 a List of EmployeeEntity. Second, its better to use optional than checking if its null, and you may pass Java 8's Optional values obtained from optional RequestParam ones like 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 a very important thing is to use the containsIgnoreCase function in the predicate: its better than a typical like cause its case insensitive.

In my opinion you should use some approach like this:

@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 it at here.

score 0 · Answer 8 · answered Apr 13 '18 at 10:22

This is a very simple way to deal 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();
}

score 0 · Answer 9 · answered Aug 29 '19 at 09:20

There is another way using Optional without BooleanBuilder although the resulting query might 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);
}

Taking that idea and adding a helper function improves readability though:

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

Handle optional parameters in QueryDSL

9 Answers9

Linked