Hi,

Can a named named query be defined, but with optional parameters; i.e. if the value is specified, then include the parameter in the WHERE clause, and just don't include it in the other case (where there is no value for the parameter)?

I've heard about dynamic filters, but those are applied to the way collections of objects are retrieved, and don't know how could they be used to achieve my issue.

Regards,
Enrique Medina.

Filters are going to be applied every time you issue a query associated to an entity on which you have defined and filter, when the filter is up.

What you can do is: Setup multiple filter for you entity, and only enable the filter when you have a value for it.

IMHO, that is not efficient... Think about more than 10 criterion...

I would expect something similar to what iBatis offers:

<statement id="dynamicGetAccountList"
resultMap="account-result" >
select * from ACCOUNT
<dynamic prepend="WHERE">
<isNotNull prepend="AND" property="firstName">
(ACC_FIRST_NAME = #firstName#
<isNotNull prepend="OR" property="lastName">
ACC_LAST_NAME = #lastName#
</isNotNull>
)
</isNotNull>
<isNotNull prepend="AND" property="emailAddress">
ACC_EMAIL like #emailAddress#
</isNotNull>
<isGreaterThan prepend="AND" property="id" compareValue="0">
ACC_ID = #id#
</isGreaterThan>
</dynamic>
order by ACC_LAST_NAME
</statement>

See what I mean?

Query by example will do this implicitly. Simply pass an object with the fields you want to include in the search populated, and the others null.

But, if I set parameters with NULL values, I would get:

SELECT * FROM Users WHERE Name LIKE '%rique%' AND startDate = NULL AND lastAccessed = NULL ...

That select will definitively not work!

ibatis will go through all your defined parameters and set up a where clause, where with hibernate, you would go though you parameters and set up the filters.

I am not sure why you say one is more efficient to the other. Looks pretty much the same to me.

Also, efficiency is relative. You are talking about executing 5-10 java method calls prior to an IO operation. In my opinion, executing a dozen java method calls will not compare to an IO database call over a JDBCDriver over a network;

But that's just my opinion.

Good luck :)