db-ojb-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Brian McCallister <bri...@apache.org>
Subject Named Queries, Stored Procedures, etc
Date Thu, 09 Dec 2004 21:03:28 GMT
Vadim Gritsenko and I have exchanged a couple emails on providing 
mapping when *all* queries need to go through stored procedures. Some 
good thoughts (and a patch from Vadim, in followup email) have come of 
it.

-Brian

Part of thread below (more to follow)

On Dec 9, 2004, at 10:09 AM, Vadim Gritsenko wrote:

> Brian McCallister wrote:
>> Is QueryBySql at the PersistenceBroker level sufficient support? 
>> Could switch on sql.toUpperCase().startsWith("CALL").
>
> So, after adding modifications to that class, you could run stored 
> procedures using QueryFactory.newQuery(classToSearchFrom, String 
> anSqlStatement)? Cool.
>
> It's a step forward, but I think this won't be enough: how OJB will 
> traverse relations? Will have to come up with a way to tell OJB which 
> stored procedure to use to select related objects..

I am becoming better at pl/sql than I want to be in the last few weeks, 
myself =/

I think a more general approach would be named queries, as you could 
have umpteen different stored procedure queries for a given thing...

Okay, I think the general solution to this might be a strong mapping 
system for named queries. Consider having named queries with named 
parameters on a class, which can be either sql, stored procedure, or 
OQL (when we get a general OQL -> Criteria compiler, which is being 
worked on), or single-string JDOQL (a JDO2 thing that I don't think has 
been released to the public yet).

You could map a relation to a parameterized named procedure, something 
like:

     <query name="articles-in-group" 
type="org.apache.ojb.broker.Article" >
         <parameter name="groupId" type="java.lang.Long" />
         <procedure>CALL QUERIES.ARTICLES_IN_GROUP(:groupId)</procedure>
     </query>

<!-- non-procedure named queries --

     <query name="articles-in-group-sql" 
type="org.apache.ojb.broker.Article" >
         <parameter name="groupId" type="java.lang.Long" />
         <sql>select article_id, name, group_id from articles where 
group_id = (:groupId)</sql>
     </query>

     <query name="articles-in-group-oql" 
type="org.apache.ojb.broker.Article" >
         <parameter name="group" type="org.apache.ojb.broker.Group" />
         <oql>select a from Articles a from where a.group = :group</oql>
     </query>

     <query name="articles-in-group-jdoql" 
type="org.apache.ojb.broker.Article" >
         <parameter name="group" type="org.apache.ojb.broker.Group" />
         <jdoql>select Articles where group = :group</jdoql>
     </query>

<!-- collection mapping by referencing named query -->

     <collection-descriptor
        name="allArticlesInGroup"
        element-class-ref="org.apache.ojb.broker.Article" >
         <fetch-by-query>
             <named-query query="articles-in-group">
                 <param name="groupId">${this.groupId}</param>
             </named-query>
     </collection-descriptor>

<!-- or inline the query --->

     <collection-descriptor
        name="allArticlesInGroup"
        element-class-ref="org.apache.ojb.broker.Article" >
         <query name="articles-in-group" 
type="org.apache.ojb.broker.Article" >
             <parameter name="groupId" type="java.lang.Long" />
             <procedure>CALL 
QUERIES.ARTICLES_IN_GROUP(${this.groupId})</procedure>
         </query>
     </collection-descriptor>

Thoughts?

This would be hefty to implement, but is certainly feasible. Mind if I 
CC ojb-dev on this thread?

-Brian


---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org


Mime
View raw message