CODEWITHSUNDEEP
javahibernatejpaejbjpql
Mr.Eddart
Mr.Eddart

Reputation: 10273

How to build JPQL queries when parameters are dynamic?

I wonder if there is a good solution to build a JPQL query (my query is too "expressive" and i cannot use Criteria) based on a filter.

Something like:

query = "Select from Ent"
if(parameter!=null){
   query += "WHERE field=:parameter"
}
if(parameter2!=null) {
   query += "WHERE field2=:parameter2"
}

But i would write WHERE twice!! and the casuistic explodes as the number of parameter increases. Because none or all could be null eventually.

Any hint to build these queries based on filters on a proper way?

Upvotes: 4

Views: 11359

Answers (2)

Abelevich
Abelevich

Reputation: 293

select * from Ent    
    where (field1 = :parameter1 or :parameter1 is null)       
    and (field2 = :parameter2 or :parameter2 is null)

Upvotes: 6

NimChimpsky
NimChimpsky

Reputation: 47280

Why can't you use a criteria, like this.

Other options (less good imho):

Create two named queries one for each condition, then call the respective query.

Or build up a string and use a native query.

Oh, do you just mean the string formation(?) :

query = "Select from Ent where 1=1 "
if(parameter!=null){
   query += " and field=:parameter"
}
if(parameter2!=null) {
   query += " and field2=:parameter2"
}

(I think that string formation is ugly, but it seemed to be what was asked for)

Upvotes: 4

Related Questions