How to do optional wildcard select in JPA named queries?

[King Lung Chiu]

Hi everyone,

Say I have a Record entity with 3 fields a, b and c, and given a Record instance rec, I use r's fields as criteria for a predefined (named) SELECT query (with AND on all fields).

Does JPQL allow us to write a query that can do wildcard matches on fields that are null?

eg. if rec has all 3 of a, b and c set, then the query would simply be:

select * from Record
where a = rec.a
  AND b = rec.b
  AND c = rec.c   

But if say field c is null, I'd like to do something like this:

select * from Record
where a = rec.a
  AND b = rec.b
  AND c = *   

Which should work effectively as this JPQL query:

select * from Record
where a = rec.a
  AND b = rec.b 

I want to do wildcard matches because there are quite a number of fields in my entity, and I don't know in advance how many of them (and in which combination) will be supplied in the search criteria. And I'd like to do the above as a named query if possible to avoid having to dynamically construct SQL by hand.

Any idea how to achieve the above in JPQL?

thanks a lot, regards.

[Sridhar Santhanakrishnan]

How about using something similar to this?

select * from Record  
where a = rec.a  
AND b = rec.b  
AND (c = rec.c or c is null)    

[King Lung Chiu]

Hi,

Thanks for the reply.

When rec.c == null, I want to do a wild-card match, ie return Records with any c value, rather than return records where c == null.

So unfortunately that suggestion doesn't work :-(

Any other thoughts?