Thanks, I'll be looking into this next week. I found also a link to a solution on the
www.orionsupport.com
site. If you look for Simple Object-relational mapping, you'll find to wath might be a
non-portable solution to
the problem.
Anyways I'll drop a note about what my adventures were in QBE country.
Cheers,
Frank
On Wednesday, September 13, 2000 4:01 PM, Joe Walnes [SMTP:[EMAIL PROTECTED]]
wrote:
> 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
> > > >
> > > >
> > > >
> > >
> > >
> >
>
>
