Jonathan Weedon wrote:
>
> Douglas,
>
> I think the easiest way to do this is using a finder method that can take
> null parameters. So, if I want to query on a string and an integer, I would
> have following finder:
>
> public interface FooHome ... {
> public Collection findByStuff(String s, Integer i) ...;
> }
>
> Then I would define the where clause as:
>
> ((:s is null) or (:s = s)) and ((:i is null) or (:i = i))
>
> That is, return all values for which either :s is null, or :s has the same
> value as the column s, and where :i is null, or :i has the same value as
> the column i.
If you use prepared statement caching, you may find that this results
in miserable performance on large data sets, as the query plan might be
optimised for your first query (let's say where s is not null) and that
plan may be inappropriate for your next query (let's say where s is null
and i is not null).
What would be best is to be able to omit an entire clause, e.g.
omit "((:s is null) or (:s = s))" when s is null. In that way
the database server's query optimizer will be able to do its job
properly.
> If you don't like using Integer, but prefer int, then you need to select a
> default numeric value.
>
> <vendor>
> See the example:
>
> <appserver>/examples/ejb/instructors
>
> which illustrates this technique for the method:
>
> InstructorHome.findByAddress(Address a)
>
> </vendor>
>
> -jkw
>
> "Potts, Douglas" wrote:
> >
> > Hi,
> > We're doing some entity beans using CMP. One of the tables as about twenty
> > fields. The requirement is to be able to search on any combination of those
> > fields. Does this mean we have to do a finder for each of the possibilities
> > or s there another way of doing it.
> > We're using Inprise Application Server on Win NT4 with EJB 1.1
> > Regards and thanks,
> > Doug
>
> ===========================================================================
> To unsubscribe, send email to [EMAIL PROTECTED] and include in the body
> of the message "signoff EJB-INTEREST". For general help, send email to
> [EMAIL PROTECTED] and include in the body of the message "help".
--
________________________________________________________________________________
Evan Ireland Sybase EAServer Engineering [EMAIL PROTECTED]
Wellington, New Zealand +64 4 934-5856
===========================================================================
To unsubscribe, send email to [EMAIL PROTECTED] and include in the body
of the message "signoff EJB-INTEREST". For general help, send email to
[EMAIL PROTECTED] and include in the body of the message "help".
