db-ojb-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Thomas Dudziak (JIRA)" <j...@apache.org>
Subject [jira] Commented: (OJB-16) Support stored procedures in select by pk statement
Date Sat, 09 Apr 2005 16:00:17 GMT
     [ http://issues.apache.org/jira/browse/OJB-16?page=comments#action_62502 ]
     
Thomas Dudziak commented on OJB-16:
-----------------------------------

> PK Procedure:
>
> CREATE FUNCTION FIND_MYBEAN_BYPK (AA IN VARCHAR, BB IN VARCHAR)
> RETURN TYPES.CURSORTYPE AS
> RESULT TYPES.CURSORTYPE;
> BEGIN
>   OPEN RESULT FOR SELECT * FROM MYBEAN WHERE A = AA AND B = BB;
>   RETURN RESULT;
> END;
> /
> 
> FK Procedure:
> 
> CREATE FUNCTION FIND_MYBEAN (AA IN VARCHAR, ..., ZZ IN VARCHAR)
> RETURN TYPES.CURSORTYPE AS
> RESULT TYPES.CURSORTYPE;
> BEGIN
>   OPEN RESULT FOR SELECT * FROM MYBEAN WHERE
>   (AA IS NULL OR A = AA) AND ... AND (ZZ IS NULL OR Z = ZZ)
>   RETURN RESULT;
> END;
> /
> 
> I'm no expert on Oracle or PL/SQL, but seems to me latter is less performant then
> former, and I don't see a better, more performant way for implementing the latter.

Yes, but for OJB they are virtually identical: they both want a list of (field) values of
the current object as input and return a resultset. That's why I'd rather use one descriptor
for these, where per default the pks are used unless the user specifies the fields (as arguments)
himself. This emphasizes the fact that all OJB does is to give the database procedure some
values from the current object (or constants). Whether the database procedure then uses them
as pk values or something different, doesn't matter to OJB as it is in the application domain.

> SP per relation would fix this issue (at the expense of having to create multiple
> procedures), but ATM I don't know from what side to approach this in order to
> implement this in OJB. And still, this two-procedures approach does not prevent one
> to implement procedure-per-relation approach. So it would give ability to specify
> generic function for all relations, and some customized, tailored to the relation,
> function for specific relation(s).

Mhmm, yes, it might be useful to also be able to specify the procedure to use for retrieving
a reference/collection at the reference/collection rather than at the target class.

> Support stored procedures in select by pk statement
> ---------------------------------------------------
>
>          Key: OJB-16
>          URL: http://issues.apache.org/jira/browse/OJB-16
>      Project: OJB
>         Type: New Feature
>   Components: PB-API
>     Versions: 1.0.x CVS
>     Reporter: Vadim Gritsenko
>     Assignee: Thomas Dudziak
>  Attachments: SelectByPKProcedureDescriptor.java, db-ojb-selectbypk.diff, xdoclet.diff
>
> This patch adds support for retrieving objects by primary keys through call to stored
procedure instead of using select statement.
> To activate the feature, add xdoclet tag to the class:
> /**
>  * @ojb.class table="MYBEAN"
>  * @ojb.selectbypk-procedure name="FIND_MYBEAN_BYID"
>  */
> public class MyBean {
>     /**
>      * @ojb.field primarykey="true"
>      */
>     Integer id;
> }
> And then, create stored procedure:
> CREATE OR REPLACE PACKAGE TYPES AS
>   TYPE CURSORTYPE IS REF CURSOR;
> END TYPES;
> /
> CREATE OR REPLACE FUNCTION FIND_MYBEAN_BYID (ANID IN MYBEAN.ID%TYPE)
> RETURN TYPES.CURSORTYPE AS
> RESULT TYPES.CURSORTYPE;
> BEGIN
>   OPEN RESULT FOR SELECT * FROM MYBEAN WHERE ID = ANID;
>   RETURN RESULT;
> END;
> /
> Patch is made against OJB_1_0_RELEASE branch.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
If you want more information on JIRA, or have a bug to report see:
   http://www.atlassian.com/software/jira


---------------------------------------------------------------------
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