Repost:
Comments inline, I hope this helps.
Jeff.
On Wed, 26 Sep 2001 11:26:10 -0400
"J.D. Bertron" <[EMAIL PROTECTED]> wrote:
> Hi,
> Can someone on this board summarize the answers to the
>
> "How do I set up a finder method with an underlying query like --- ?"
>
> 1- For "Like" queries.
> SELECT ACCOUNTS.ID, ACCOUNTS.DESCRIPTION
> FROM ACCOUNTS
> WHERE ACCOUNTS.DESCRIPTION LIKE "%blah%"
We do this by passing in the like string, ie findByFoo("%foo%"), and the
query is defined as:
<finder-method query="accounts.foo like $1">
>
> 2- For queries involving subqueries (JOINS) to a dependant table.
> SELECT ACCOUNTS.ID, ACCOUNTS.DESCRIPTION, ACCOUNTS.ZIPCODE
> FROM ACCOUNTS, REGIONS
> WHERE ACCOUNTS.ZIPCODE = REGIONS.ZIPCODE AND REGIONS.ID = ?
<finder-method query="select accounts.* from accounts,regions where
accounts.zipcode = regions.zipcode and regions.id = $1" partial="false">
>
> 3- For queries returning columns NOT originally in the bean:
> SELECT ACCOUNT.ID, ACCOUNT.DESCRIPTION, COUNT(*) AS ORDERS
> FROM ACCOUNTS, ACCT_ORDERS
> WHERE ACCOUNTS.ID = ACCT_ORDERS.ID AND
> GROUP BY ACCOUNT_ID, ACCOUNT.DESCRIPTION
>
> or queries NOT returning ALL columns in the bean:
> SELECT ACCOUNT.ID, ACCOUNT.DESCRIPTION
> FROM ACCOUNTS, ACCT_ORDERS
> WHERE ACCOUNTS.ID = ACCT_ORDERS.ID AND
> GROUP BY ACCOUNT_ID, ACCOUNT.DESCRIPTION
> HAVING COUNT(*) > ?
The finder queries populate a bean, and therefore must return all the
fields that bean needs. If you want just a subset of columns returned, or
a count value, or doing group by, then you need to use an explicit JDBC
query.
>
> 4- Summarize the answer to all these ORDER BY questions
>
Orion returns an ordered collection from finder methods, but this is not
portable and subject to change as the J2EE spec states that the collection
is unordered. Alot of containers probably implement it as ordered, but
just be aware that this could change. To order a collection, just add an
order by clause to the query:
<finder-method query="accounts.foo like $1 order by accountid">
>
> 5- Clarify solutions to the problem of degenerate finder signatures.
> Your bean has many attributes, and you want to give your client
> the ability to find beans based on a set of conditions (filter)
> of their choosing. Combinations of conditions could
> result in a degenerate number of finders and finder signatures.
> A more desirable result is to have Orion handle empty finder method
> parameters so that they are ignored in the final query.
>
> Example: findByFilters( String accountID, String zipCode, String
> description)
> When passed only the accountID and zipCode, the query filters only
> on these two columns in the table
> and ignores the description column. When passed in only a zipCode
> the query ignores both the accountID and description.
we just make the queries check for nulls. in your example above, it would
be something like:
<finder-method query="($1 is null or ($accountid = $1)) and ($2 is null or
($zipcode = $2)) and ($3 is null or ($description = $3))">
>
> I'm personally anxious to get answers to #3 and #5. I'm sure others
would
> be interested in
> a nice document with clear answers and examples.
>
> J.D. Bertron
>
