[ 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: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
