4

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?

Mr.Eddart
  • 10,050
  • 13
  • 49
  • 77

2 Answers2

6
select * from Ent    
    where (field1 = :parameter1 or :parameter1 is null)       
    and (field2 = :parameter2 or :parameter2 is null)
Abelevich
  • 293
  • 5
  • 9
4

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)

Community
  • 1
  • 1
NimChimpsky
  • 46,453
  • 60
  • 198
  • 311
  • Thank you! but sometimes I have to apply the filter (when it is not null) and sometimes not. So named queries doesn't solve the problem, native queries either... – Mr.Eddart Sep 08 '11 at 09:06
  • 1
    +1. The criteria API is the way to go for dynamically constructed queries. That's the whole point of this API. – JB Nizet Sep 08 '11 at 09:11
  • @edutesoy., I don't understand why the solutions would not work in your case. – NimChimpsky Sep 08 '11 at 09:11
  • 1
    @JB Nizet: **unless** Criteria API is not expressive enough and you **have not other option** than doing it in HQL (as I explicitly say in the question). I already know Criteria is for these cases, but i cannot use it this time because **it is not powerful enough**. See my previous question where I try to express something in Criteria that seems to be impossible: http://stackoverflow.com/questions/7346281/limitation-on-the-expressiveness-of-hibernate-and-jpa-criteriaan – Mr.Eddart Sep 08 '11 at 12:25
  • 1
    @JB Nizet it would work but exactly the same as without named queries. I still need to deal with a bunch of embedded IFs to decide if i need to put a WHERE, if i need to put an OR, if I need to put a comma.... i don't know if you see my point. Finally i solved it calling to submethods to construct my String step by step in isolated scopes. – Mr.Eddart Sep 08 '11 at 12:28