How can I dynamically add where clauses with querydsl?

Question

I've been looking for some time now on the documentation and tried several things but I was not able to dynamically add where clauses with querydsl:

Pseudocode, I need something like the "if":

boolean addWhereClause = false; 
QAddress address = QAddress.address; 
JPQLQuery query = new JPAQuery(getEntityManager()); 
query.from(address)
     .if(addWhereClause).where(address.company.isNotNull())

or maybe better a whereIf:

boolean addWhereClause = false; 
QAddress address = QAddress.address; 
JPQLQuery query = new JPAQuery(getEntityManager()); 
query.from(address)
     .whereIf(addWhereClause, address.company.isNotNull())

The only thing I've found so far is using a BooleanBuilder, but I think there is a better way(like the pseudocode above).

kind regards, soilworker

if(addWhereClause) {query.from(address).where(address.company.isNotNull())} else {query.from(address);} — Héctor, Dec 09 '14 at 12:59
Is this the only way? I'm looking for a more "compact" form to achieve this. Let's asume I've to build queries with 50 such addWhereClause-Conditions. 50 such if/else constructs are not very nice. — soilworker, Dec 09 '14 at 13:13
Maybe: JPQLQuery query = new JPAQuery(getEntityManager()).from(); query = addWhereClause ? query.where(address.company.isNotNull()) : query; — Héctor, Dec 09 '14 at 15:02

Timo Westkämper · Accepted Answer · 2015-01-04T13:02:41.450

12

It should work like this

boolean addWhereClause; 
QAddress address = QAddress.address; 
JPQLQuery query = new JPAQuery(getEntityManager()); 
query.from(address);    
if (addWhereClause) {
    query.where(address.company.isNotNull());
}

or

boolean addWhereClause; 
QAddress address = QAddress.address; 
JPQLQuery query = new JPAQuery(getEntityManager()); 
query.from(address)
     .where(addWhereClause ? address.company.isNotNull() : null);

edited Jan 04 '15 at 13:02

answered Dec 09 '14 at 21:43

Timo Westkämper

21,824
5
78
111

Is there another way to achieve this? Let's asume I have 50 of such where clauses that I want to add dynamically. 50 if's are not very readable and a lot of complexity is added to the method where I build this query. – soilworker Dec 10 '14 at 08:41
A method query.whereIf(boolean, Predicate...) or something like this would be an awesome feature. In our project a lot of dynamic queries are built, if I have to use if constructs for this I don't get a real benefit when I introduce querydsl to our project. – soilworker Dec 10 '14 at 11:00
Am I allowed to create a querydsl ticket for this and maybe implement this? – soilworker Dec 10 '14 at 15:34
You can create an issue for it, but I am not sure if this should be implemented in Querydsl. – Timo Westkämper Dec 10 '14 at 21:50
Why not? I think this would be a good feature. If you say it won't be implemented, I won't create a ticket for it. – soilworker Dec 11 '14 at 09:04
2

The if variant has the benefit that the expression will never be evaluated if the condition is not met, the whereIf pattern doesn't have that guarantee, e.g. whereIf(arg != null, ...) might lead to an expression construction with a NPE – Timo Westkämper Dec 20 '14 at 21:26
Sry, I don't get what you exactly mean. I've tested my wanted behavior with an subclass of JPAQuery. There I've a test method that looks like the following: `public MyQuery whereIf(Boolean bool, Predicate... o) { if (bool) { return (MyQuery) this.where(o); } else { return this; } }` This is working fine in my opinion. – soilworker Dec 22 '14 at 08:27
What if the predicate can only be constructed when the condition is met? That will be the case in enough cases, that's one reason why such an approach is problematic in the API, but feel free to customize JPAQuery to your needs. – Timo Westkämper Dec 23 '14 at 17:19
1

I edited my answer to provide an example how to customize the query class properly. – Timo Westkämper Dec 23 '14 at 17:23
1

@soilworker I added now a variant that comes close to your original request. – Timo Westkämper Jan 04 '15 at 13:03

score 0 · Answer 2 · edited Sep 19 '18 at 02:41

i found another solution to get more compact code, you just have to code a new slightly modified BooleanBuilder. In fact you just have to make every method return a boolean instead of BooleaBuilder (you loose the chaining possibility), BUT here is what you get :

TworkBooleanBuilder tworkBuilder = new TworkBooleanBuilder();

boolean foo = isNotBlank(title) && tworkBuilder.and(QBook.book.title.containsIgnoreCase(title));
foo = isNotBlank(isbn) && tworkBuilder.and(QBook.book.isbn.containsIgnoreCase(isbn));
foo = before != null && tworkBuilder.and(QBook.book.date.before(before));
foo = after != null && tworkBuilder.and(QBook.book.date.after(after));

if StringUtils.isNotBlank(str) return true, java keeps evaluating the right part of the &&. And so only if "title" is not null does the "where" clause get added the "and" predicate : QBook.book.title.containsIgnoreCase(title)

Of course "foo" is useless, just there to make java compile and evaluate the expression

How can I dynamically add where clauses with querydsl?

2 Answers2