You forgot to mention that you can write out a full Sql stment with joins
and so on. I use it a lot because of the datastructure we have here.
To this one have to edit the orion-ejb.xml and insert a complet vaild sql
statment which only returns the fields you have in the ententity object.
In CMP you would do something like this :
findByCrit1Crit2( int Crit1, String Crit2)
the sql in orion-ejb.xml would loke like this;
select a.Col1 a.Col2 a.Col3 from tabel1 a, table2 b (where a.Col1 = $1) and
( b.Col2 = $2) and (a.Col3 = b.Col1)
Regards,
Torgeir Lerker�d
-----Original Message-----
From: Joe Walnes [mailto:[EMAIL PROTECTED]]
Sent: 13. september 2000 16:01
To: Orion-Interest
Subject: RE: Specifying finder-methods (full query)
Ok, here's some methods to allow slightly more flexible queries.
There is rarely more than a few fields to query, but the permutations of
different combinations can make this a bit of a headache.
If you have name, age and score, you may end up with finder methods like:
findByName(String name)
findByAge(int age)
findByScore(int score)
findByNameAndAge(String name, int age)
findByNameAndScore(String name, int score)
..... etc
This could be merged into one finder statement
findByCriteria(String name, int age, int score)
Where the SQL query supplied to the finder-method is:
($name LIKE $1 OR $1 IS NULL) AND ($age = $2 OR $2 = 0) AND ($score
= $3 OR
$3 = 0)
This way, if 0 (or null) is supplied as a parameter to the finder method the
result set will not be filtered with that criteria.
You can also pass across parameters that aren't necessarily fields, but
useful for the query:
findByAge(int age, int allowedRange)
($age >= $1 - $allowedRange) OR ($age <= $1 + $allowedRange)
findByAge(int age, boolean older) // if true, returns all people
older than
age,
// otherwise all people younger
($2 = 1 AND age >= $1) OR ($2 = 0 AND age <= $1)
Also (if your database supports it), sub-selects are your friend
(particularly when using OR mapping)
$blah IN (SELECT id FROM xxx WHERE yy = $1)
If you still can't achieve what you're attempting, the session bean to query
the database for the keys will work - it may not be clean but sometimes you
do have to resort to methods like this. If it's any consellation, I have
done this because the query that needed to be generated was way too
complicated for CMP, and it did work. The problem is that you then have
nasty SQL inside your beans - where it shouldn't be.
-Joe Walnes
> Thanks for the reaction (also Nick). I realize now the potential
> security problems by allowing full
> specification of SQL on the client.
>
> Unfortunately the "Bob" example does not solve my problem. I
> managed to implement that example.
> That is where I came from. From there I tried to continue to
> implement a Query By Example frame and
> that's where I got stuck.
>
> Something like this:
> Say you have a table with products and you would like to create a
> flexible query frame for those
> products to run queries like all products for which the stock is
> low and which are purchased from comp
> xyz, or all products from categorie abc with names like %nic%.
>
> With specifying the query in orion-ejb-jar I came as far as
> either specifying a fixed name / number or
> you can use another fixed operator, e.g. '>' or LIKE. Due to
> generated the quotes I could not let the client
> generate the selection part of the where clause. You get roughly
> the same error as when generating the
> whole statement.
>
> Probably you can write some ?Session? Bean that will query direct
> on the database returning a collection
> of primary keys, but for some reason that does not sound like music to me.
>
>
> Frank
>
> On Tuesday, September 12, 2000 6:16 PM, Joe Walnes
> [SMTP:[EMAIL PROTECTED]] wrote:
> > Frank,
> >
> > The actual SQL for query should be within the deployment
> descriptor and not
> > the client code.
> >
> > So, if you wanted to find all MyProducts by a particular name,
> > orion-ejb-jar.xml would contain:
> >
> > <finder-method query="$name = $1" partial="false">
> >
> > And the client code would contain:
> >
> > ...findByName("Bob")
> >
> > It is not possible to pass SQL statements from the client to the finder
> > method at runtime when using CMP as PreparedStatements are created in
> > advance. Allowing this would impact performance, breach
> security and allow
> > all havoc to break out.
> >
> > -Joe Walnes
> >
> > > -----Original Message-----
> > > From: [EMAIL PROTECTED]
> > > [mailto:[EMAIL PROTECTED]]On Behalf Of Frank Eggink
> > > Sent: 12 September 2000 16:06
> > > To: Orion-Interest
> > > Subject: Specifying finder-methods (full query)
> > >
> > >
> > > I'm running into trouble specifying finder queries in
> > > orion-ejb-jar. As far
> > > as I can make sense of it the cause is in the quotes. Is that
> correct and
> > > does anyone know how the get around that?
> > >
> > >
> > > I've changed the default finder method to:
> > >
> > > <finder-method query="$1" partial="false">
> > > <!-- Generated SQL: "?" -->
> > > <method>
> > > <ejb-name>MyProduct</ejb-name>
> > > <method-name>findByName</method-name>
> > > <method-params>
> > >
> > > <method-param>java.lang.String</method-param>
> > > </method-params>
> > > </method>
> > > </finder-method>
> > >
> > >
> > > I call the method in my client with:
> > >
> > > ...findByName("select id, name, description, price from MyProduct");
> > >
> > >
> > > The following is the resulting output:
> > >
> > > Printing a list of all products: System/communication error: Database
> > > error: Unexpected token: 'select id, name, description, price from
> > > MyProduct in statement ['select id, name, description, price from
> > > MyProduct']; nested exception is:
> > > java.sql.SQLException: Unexpected token: 'select id, name,
> > > description,
> > > price from MyProduct in statement ['select id, name,
> description, price
> > > from MyProduct'] Process Exit...
> > >
> > >
> > > The database is hsql.
> > >
> > >
> > > Thanks, Frank
> > >
> > >
> > >
> >
> >
>
