using a named query but not require all where params

npujol · **Joined:** Fri Mar 17, 2006 3:54 pm **Posts:** 6

Hi I was wondering if it is possible to use a named query but not have to specifiy all where params:

example:

SELECT count(*) as n FROM ProcessVO WHERE
completionTime is not null AND
processId=:processId AND
version=:version

I would like for the processId = :processId to not be required and be omitted from the where clause if not specified via a setParameter(). Any way to do this?

Thanks

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

You can't change the resulting SQL, but you can write the SQL to handle it. Make the requirement be that :processId is set to null if you don't want to search on it, and rewrite the query as

Code:

SELECT count(*) as n FROM ProcessVO WHERE
completionTime is not null AND
(:processId is null or processId=:processId) AND
version=:version

npujol · **Joined:** Fri Mar 17, 2006 3:54 pm **Posts:** 6

Right that is one option. I guess I was hoping that if I
did not call query.setParameter("processId",....) then the query would still be issued but the processId=:processId part not included. Thoughts?

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

There's nothing like that, as far as I'm aware.

scarface · **Joined:** Sun Jun 04, 2006 1:58 am **Posts:** 136

why not use filters ( assuming you are on hibernate 3 or greater)

http://www.hibernate.org/hib_docs/v3/reference/en/html/filters.html

damnhandy · **Posted:** Sun Jun 25, 2006 10:17 pm

This sort of thing is easier to achieve using the Criteria API. Is there any reason you have to use HQL?

Ryan-

batmat · **Posted:** Mon Jun 26, 2006 4:18 am

damnhandy wrote:

This sort of thing is easier to achieve using the Criteria API. Is there any reason you have to use HQL?

Ryan-

Yes, +1. In fact, that's the thing to learn : use HQL for static queries, optionally parametrized, and use Criteria API for variable queries.