[ http://issues.apache.org/jira/browse/OJB-16?page=comments#action_62501 ]
Vadim Gritsenko commented on OJB-16:
------------------------------------
>> But ATM, I'd like to go with 2 procedures approach -
>> as it allows for simplier and more efficient select-by-pk
>> procedure.
>
> What's more efficient, the OJB impl or the database
> procedure impl ?
I meant more efficient SP impl, from DB POV. For example, compare the two:
public class MyBean {
/** ojb.field primarykey="true" */
private String a;
/** ojb.field primarykey="true" */
private String b;
/** ojb.field */
private String c;
...
/** ojb.field */
private String z;
}
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.
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).
Thanks,
Vadim
> 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: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
