You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ojb-dev@db.apache.org by th...@apache.org on 2003/05/08 08:08:09 UTC
cvs commit: db-ojb/xdocs how-to-work-with-stored-procedures.xml project.xml
thma 2003/05/07 23:08:08
Modified: xdocs project.xml
Added: xdocs how-to-work-with-stored-procedures.xml
Log:
Add Ron's stored procedures howto. Thanks Ron!
Revision Changes Path
1.16 +3 -1 db-ojb/xdocs/project.xml
Index: project.xml
===================================================================
RCS file: /home/cvs/db-ojb/xdocs/project.xml,v
retrieving revision 1.15
retrieving revision 1.16
diff -u -r1.15 -r1.16
--- project.xml 4 May 2003 12:07:45 -0000 1.15
+++ project.xml 8 May 2003 06:08:08 -0000 1.16
@@ -12,7 +12,7 @@
<item name="Features" href="/features.html"/>
<item name="Quickstart" href="/quickstart.html"/>
<item name="FAQs" href="/faq.html"/>
- <item name="Mailing lists" href="/mail-lists.html"/>
+ <item name="Mailing lists" href="/mail-lists.html"/>
</menu>
<menu name="Downloads">
@@ -43,6 +43,7 @@
<item name="get started" href="/howto-get-started.html"/>
<item name="use DB Sequences" href="/howto-use-db-sequences.html"/>
<item name="use Oracle lobs" href="/howto-use-lobs.html"/>
+ <item name="use Stored Procedures" href="/how-to-work-with-stored-procedures.html"/>
</menu>
@@ -64,3 +65,4 @@
</menu>
</body>
</project>
+
1.1 db-ojb/xdocs/how-to-work-with-stored-procedures.xml
Index: how-to-work-with-stored-procedures.xml
===================================================================
<?xml version="1.0"?>
<document>
<properties>
<author email="rongallagher@bellsouth.net">Ron Gallagher</author>
<title>ObJectRelationalBridge - HOWTO Work With Stored Procedures</title>
</properties>
<body>
<section name="Introduction">
<p>
Out of the box, OJB generates generic SQL syntax to handle the basic
DML operations (INSERT, UPDATE, and DELETE). However, there may be
situations where it is necessary to utilize stored procedures to handle
these DML operations.
</p>
<p>
This document describes the requirements that were presented to me
on one development project and the process that I went through to
get OJB to utilize stored procedures to handle all INSERT, UPDATE
and DELETE operations.
</p>
</section>
<section name="The basic requirements">
<p>
Our use of OJB had to adhere to the following technical and functional
requirements:
</p>
<ol>
<li>
<p>
All data was to be stored in an Oracle 8i database. This was
a company standard, so database portability was not a concern.
</p>
</li>
<li>
<p>
All primary key values were to be assigned via Oracle
sequences. Previous applications at this company had used
client-side algorithms to assign primary key values. However,
these implementations suffered performance and scalability
problems and were considered inadequate for the application that
we were developing. OJB's built-in support for using
<a href="howto-use-db-sequences.html">database generated
sequences</a> was considered as an option. However, in light of
all of the other technical and functional requirements that
had to be supported, another solution had to be found.
</p>
</li>
<li>
<p>
For performance reasons, all insert, update and delete
operations were to be handled by stored procedures. These
procedures could be structured as 'stand-alone' stored procedures
or as procedures that were contained within an Oracle package. The
exact implementation was not mandated by any sort of standard.
</p>
</li>
<li>
<p>
All tables in the application were required to include the
following set of 'audit' columns:
</p>
<ul>
<li> <code>USER_CREATED</code> -
The id of the user who created the record</li>
<li> <code>DATE_CREATED</code> -
The date on which the record was created</li>
<li> <code>USER_UPDATED</code> -
The id of the user who last modified the record</li>
<li> <code>USER_UPDATED</code> -
The date on which the record was last modified</li>
</ul>
</li>
<p>
In addition to the inclusion of these columns on each table,
the following requirements related to these columns had to be
supported:
</p>
<ol>
<li>
<p>
The values of the two date-related audit columns were to be
maintained at the database level via insert and update
triggers.
<ul>
<li>
The insert trigger will set both
<code>DATE_CREATED</code> and <code>DATE_UPDATED</code>
to the current system date (<code>SYSDATE</code>).
</li>
<li>
The update trigger will set <code>DATE_UPDATED</code>
to the current system date (<code>SYSDATE</code>).
The update trigger will also ensure that the original
value of <code>DATE_CREATED</code> is never modified.
</li>
</ul>
</p>
</li>
<li>
<p>
The values of the two date-related audit columns were to be
maintained at the database level via insert and update
triggers.
<ul>
<li>
The insert and update triggers will ensure that
<code>USER_CREATED</code> and <code>USER_UPDATED</code>
are appropriately populated.
</li>
<li>
The update trigger will ensure that the original
value of <code>USER_CREATED</code> is never modified.
</li>
</ul>
</p>
</li>
<li>
<p>
Any changes that were made to these 'audit' columns by the
insert or update triggers had to be reflected in the
corresponding java object.
</p>
</li>
</ol>
</ol>
</section>
<section name="Problems to solve">
<p>
The implementation of the basic OJB API presented several problems that
had to be overcome in order to implement the functional and technical
requirements described above:
</p>
<ol>
<li>
<p>
Column values that were assigned or updated at the database
level had to be returned to our java code (ie. the 'caller')
in order for those values to be reflected in the corresponding
java object.
</p>
<p>
To accomplish this, we had to enhance OJB to utilize the JDBC
<code>CallableStatement</code> instead of the JDBC
<code>PreparedStatement</code>.
</p>
</li>
<li>
<p>
The SQL syntax that is generated by the OJB components does not
allow for the execution of stored procedures. These components
had to be enhanced to generate SQL syntax that called
stored procedures.
</p>
</li>
</ol>
</section>
<section name="The sample database">
<p>
To help illustrate the problem we faced as well as the solution we
implemented, we will utilize a very simple database.
</p>
<subsection name="The CUSTOMER table">
<source><![CDATA[
CREATE TABLE CUSTOMER
( CUSTOMER_ID NUMBER(18) NOT NULL
, CUSTOMER_NAME VARCHAR2(50) NOT NULL
, USER_CREATED VARCHAR2(30)
, DATE_CREATED DATE
, USER_UPDATED VARCHAR2(30)
, DATE_UPDATED DATE
, CONSTRAINT PK_CUSTOMER PRIMARY KEY (CUSTOMER_ID)
);]]></source>
</subsection>
<subsection name="The sequence">
<p>
This sequence will be used to assign unique values to
<code>CUSTOMER.CUSTOMER_ID</code>.
</p>
<source><![CDATA[
CREATE SEQUENCE CUSTOMER_SEQ;]]></source>
</subsection>
<subsection name="The insert and update triggers">
<p>
These two triggers ensure the following:
</p>
<ul>
<li>
All four audit columns are always populated when a record
is first created.
</li>
<li>
DATE_CREATED and USER_CREATED are never changed once a record
is created.
</li>
<li>
DATE_UPDATED and USER_UPDATED are always populated when a
record is updated.
</li>
</ul>
<source><![CDATA[
CREATE OR REPLACE TRIGGER CUSTOMER_ITR
BEFORE INSERT ON CUSTOMER
FOR EACH ROW
BEGIN
--
-- Populate the audit dates
--
SELECT SYSDATE
, SYSDATE
INTO :NEW.DATE_CREATED
, :NEW.DATE_UPDATED
FROM DUAL;
--
-- Make sure the user created column is populated.
--
IF :NEW.USER_CREATED IS NULL
THEN
SELECT SYS_CONTEXT('USERENV','TERMINAL')
INTO :NEW.USER_CREATED
FROM DUAL;
END IF;
--
-- Make sure the user updated column is populated.
--
IF :NEW.USER_UPDATED IS NULL
THEN
SELECT SYS_CONTEXT('USERENV','TERMINAL')
INTO :NEW.USER_UPDATED
FROM DUAL;
END IF;
END;
/
CREATE OR REPLACE TRIGGER CUSTOMER_UTR
BEFORE UPDATE ON CUSTOMER
FOR EACH ROW
BEGIN
--
-- Populate the date updated
--
SELECT SYSDATE
INTO :NEW.DATE_UPDATED
FROM DUAL;
--
-- Make sure the user updated column is populated.
--
IF :NEW.USER_UPDATED IS NULL
THEN
SELECT SYS_CONTEXT('USERENV','TERMINAL')
INTO :NEW.USER_UPDATED
FROM DUAL;
END IF;
--
-- Make sure the date/user created are never changed
--
SELECT :OLD.DATE_CREATED
, :OLD.USER_CREATED
INTO :NEW.DATE_CREATED
, :NEW.USER_CREATED
FROM DUAL;
END;
/]]></source>
</subsection>
<subsection name="The package that we'll use">
<p>
This Oracle package will handle all INSERT, UPDATE and
DELETE operations involving the <code>CUSTOMER</code> table.
</p>
<source><![CDATA[
CREATE OR REPLACE PACKAGE CUSTOMER_PKG AS
--
-- This procedure should be used to add a record to the CUSTOMER table.
--
PROCEDURE ADD ( ACUSTOMER_ID IN OUT CUSTOMER.CUSTOMER_ID%TYPE
, ACUSTOMER_NAME IN CUSTOMER.CUSTOMER_NAME%TYPE
, AUSER_CREATED IN OUT CUSTOMER.USER_CREATED%TYPE
, ADATE_CREATED IN OUT CUSTOMER.DATE_CREATED%TYPE
, AUSER_UPDATED IN OUT CUSTOMER.USER_UPDATED%TYPE
, ADATE_UPDATED IN OUT CUSTOMER.DATE_UPDATED%TYPE );
--
-- This procedure should be used to change a record on the CUSTOMER table.
--
PROCEDURE CHANGE ( ACUSTOMER_ID IN CUSTOMER.CUSTOMER_ID%TYPE
, ACUSTOMER_NAME IN CUSTOMER.CUSTOMER_NAME%TYPE
, AUSER_CREATED IN OUT CUSTOMER.USER_CREATED%TYPE
, ADATE_CREATED IN OUT CUSTOMER.DATE_CREATED%TYPE
, AUSER_UPDATED IN OUT CUSTOMER.USER_UPDATED%TYPE
, ADATE_UPDATED IN OUT CUSTOMER.DATE_UPDATED%TYPE );
--
-- This procedure should be used to delete a record from the CUSTOMER table.
--
PROCEDURE DELETE ( ACUSTOMER_ID IN CUSTOMER.CUSTOMER_ID%TYPE );
END CUSTOMER_PKG;
/
CREATE OR REPLACE PACKAGE BODY CUSTOMER_PKG AS
--
-- This procedure should be used to add a record to the CUSTOMER table.
--
PROCEDURE ADD ( ACUSTOMER_ID IN OUT CUSTOMER.CUSTOMER_ID%TYPE
, ACUSTOMER_NAME IN CUSTOMER.CUSTOMER_NAME%TYPE
, AUSER_CREATED IN OUT CUSTOMER.USER_CREATED%TYPE
, ADATE_CREATED IN OUT CUSTOMER.DATE_CREATED%TYPE
, AUSER_UPDATED IN OUT CUSTOMER.USER_UPDATED%TYPE
, ADATE_UPDATED IN OUT CUSTOMER.DATE_UPDATED%TYPE )
IS
NEW_SEQUENCE_1 CUSTOMER.CUSTOMER_ID%TYPE;
BEGIN
SELECT CUSTOMER_SEQ.NEXTVAL
INTO NEW_SEQUENCE_1
FROM DUAL;
INSERT INTO CUSTOMER
( CUSTOMER_ID
, CUSTOMER_NAME
, USER_CREATED
, USER_UPDATED )
VALUES ( NEW_SEQUENCE_1
, ACUSTOMER_NAME
, AUSER_CREATED
, AUSER_UPDATED )
RETURNING CUSTOMER_ID
, USER_CREATED
, DATE_CREATED
, USER_UPDATED
, DATE_UPDATED
INTO ACUSTOMER_ID
, AUSER_CREATED
, ADATE_CREATED
, AUSER_UPDATED
, ADATE_UPDATED;
END ADD;
--
-- This procedure should be used to change a record on the CUSTOMER table.
--
PROCEDURE CHANGE ( ACUSTOMER_ID IN CUSTOMER.CUSTOMER_ID%TYPE
, ACUSTOMER_NAME IN CUSTOMER.CUSTOMER_NAME%TYPE
, AUSER_CREATED IN OUT CUSTOMER.USER_CREATED%TYPE
, ADATE_CREATED IN OUT CUSTOMER.DATE_CREATED%TYPE
, AUSER_UPDATED IN OUT CUSTOMER.USER_UPDATED%TYPE
, ADATE_UPDATED IN OUT CUSTOMER.DATE_UPDATED%TYPE )
IS
BEGIN
UPDATE CUSTOMER
SET CUSTOMER_NAME = ACUSTOMER_NAME
, USER_CREATED = USER_CREATED
, USER_UPDATED = AUSER_UPDATED
WHERE CUSTOMER_ID = ACUSTOMER_ID
RETURNING USER_CREATED
, DATE_CREATED
, USER_UPDATED
, DATE_UPDATED
INTO AUSER_CREATED
, ADATE_CREATED
, AUSER_UPDATED
, ADATE_UPDATED;
END CHANGE;
--
-- This procedure should be used to delete a record from the CUSTOMER table.
--
PROCEDURE DELETE ( ACUSTOMER_ID IN CUSTOMER.CUSTOMER_ID%TYPE )
IS
BEGIN
DELETE
FROM CUSTOMER
WHERE CUSTOMER_ID = ACUSTOMER_ID;
END DELETE;
END CUSTOMER_PKG;
/]]></source>
<p>
Please note the following about the structure of the
<code>CUSTOMER_PKG</code> package:
</p>
<ul>
<li>
The <code>ACUSTOMER_ID</code> argument that is passed
to the the <code>ADD</code> procedure is defined as
<code>IN OUT</code>. This allows the procedure to return the
newly assigned <code>CUSTOMER_ID</code> to the caller.
</li>
<li>
In the <code>ADD</code> and <code>CHANGE</code> procedures,
the arguments that correspond to the four 'audit' columns
are defined as <code>IN OUT</code>. This allows the procedure
to return the current value of these columns to the 'caller'.
</li>
</ul>
</subsection>
</section>
<section name="The repository">
<p>
Here is the class descriptor related to the <code>CUSTOMER</code> table.
This class-descriptor includes all settings that are required for the
solution that we implemented.
</p>
<source><![CDATA[
<class-descriptor class="com.xxx.Customer" table="CUSTOMER">
<field-descriptor column="CUSTOMER_ID"
conversion="com.xxx.ojb.BigDecimal2LongConversion"
id="1" jdbc-type="DECIMAL"
name="customerId" nullable="false" primarykey="true">
<attribute attribute-name="return-on-insert" attribute-value="true"/>
</field-descriptor>
<field-descriptor column="CUSTOMER_NAME" id="2" jdbc-type="VARCHAR"
length="50" name="customerName" nullable="false"primarykey="false"/>
<field-descriptor column="USER_CREATED" id="3" jdbc-type="VARCHAR"
length="30" name="userCreated" nullable="true" primarykey="false">
<attribute attribute-name="return-on-insert" attribute-value="true"/>
<attribute attribute-name="return-on-update" attribute-value="true"/>
</field-descriptor>
<field-descriptor column="DATE_CREATED" id="4" jdbc-type="TIMESTAMP"
length="7" name="dateCreated" nullable="true" primarykey="false">
<attribute attribute-name="return-on-insert" attribute-value="true"/>
<attribute attribute-name="return-on-update" attribute-value="true"/>
</field-descriptor>
<field-descriptor column="USER_UPDATED" id="5" jdbc-type="VARCHAR"
length="30" name="userUpdated" nullable="true" primarykey="false">
<attribute attribute-name="return-on-insert" attribute-value="true"/>
<attribute attribute-name="return-on-update" attribute-value="true"/>
</field-descriptor>
<field-descriptor column="DATE_UPDATED" id="6" jdbc-type="TIMESTAMP"
length="7" name="dateUpdated" nullable="true" primarykey="false">
<attribute attribute-name="return-on-insert" attribute-value="true"/>
<attribute attribute-name="return-on-update" attribute-value="true"/>
</field-descriptor>
<attribute attribute-name="insert-proc" attribute-value="CUSTOMER_PKG.ADD"/>
<attribute attribute-name="update-proc" attribute-value="CUSTOMER_PKG.CHANGE"/>
<attribute attribute-name="delete-proc" attribute-value="CUSTOMER_PKG.DELETE"/>
</class-descriptor>
]]></source>
<p>
Some things to note about this class-descriptor:
</p>
<ol>
<li>
<p>
There are three attributes at the class-descriptor level that
identify the procedure to use when performing an INSERT
(<code>insert-proc</code>),
UPDATE (<code>update-proc</code>)
or DELETE (<code>delete-proc</code>)
operation. These attributes will be used when constructing
the SQL syntax for these various operations.
</p>
</li>
<li>
<p>
Various field-descriptors include the attributes
<code>return-on-insert</code> and
<code>return-on-update</code>. Our customizations use these
attributes to determine which values are returned during an
insert or update operation. By setting the appropriate attribute
to <code>true</code>, our customizations are able to 'harvest'
the values that are returned by the stored procedure and
update the java object that was persisted with the new value.
</p>
</li>
</ol>
</section>
<section name="Extensions to OJB">
<p>
In order to get OJB to support the invocation of stored procedures
rather than 'generic' SQL when inserting, updating or deleting a
record, we had to customize a few components used by the OJB kernel.
Becuase the configuration of OJB is very pluggable, it was simple
enough to get OJB to use our customizations by simply updating the
<a href="#OJB.properties">OJB.properties</a> file.
</p>
<p>
Here, in no particular order, are the components that we had to
customize.
</p>
<ul>
<li><a href="#StatementManager">
org.apache.ojb.broker.accesslayer.StatementManager</a>
</li>
<li><a href="#SqlGeneratorDefaultImpl">
org.apache.ojb.broker.sql.SqlGeneratorDefaultImpl</a>
</li>
<li><a href="#JdbcAccessImpl">
org.apache.ojb.broker.accesslayer.JdbcAccessImpl</a>
</li>
<li><a href="#StatementsForClassImpl">
org.apache.ojb.broker.accesslayer.StatementsForClassImpl</a>
</li>
<li><a href="#SqlInsertStatement">
org.apache.ojb.broker.sql.SqlInsertStatement</a>
</li>
<li><a href="#SqlUpdateStatement">
org.apache.ojb.broker.sql.SqlUpdateStatement</a>
</li>
<li><a href="#SqlDeleteByPkStatement">
\org.apache.ojb.broker.sql.SqlDeleteByPkStatement</a>
</li>
</ul>
<p>
We also created a single helper class to support our customizations.
</p>
<ul>
<li><a href="#SqlStatementHelper">SqlStatementHelper</a></li>
</ul>
<subsection name="org.apache.ojb.broker.accesslayer.StatementManager"
anchor="StatementManager">
<p>
Our extension is called
<code>com.xxx.ojb.StatementManager</code>, and it
changed the following behavior(s) of the base class:
</p>
<ol>
<li>
<p>
We overrode the method
<code>getStatementsForClass(ClassDescriptor)</code>
so that it utilzed our extension to
<a href="#StatementsForClassImpl">StatementsForClassImpl</a>
</p>
</li>
<li>
<p>
We overrode the method
<code>bindInsert(PreparedStatement,ClassDescriptor,
Object)</code> to register output parameters for any
field-descriptors where the attribute
<code>return-on-insert</code> is set to <code>true</code>.
</p>
</li>
<li>
<p>
We overrode the method
<code>bindUpdate(PreparedStatement,ClassDescriptor,
Object)</code> to register output parameters for any
field-descriptors where the attribute
<code>return-on-update</code> is set to <code>true</code>.
</p>
</li>
</ol>
</subsection>
<subsection name="org.apache.ojb.broker.sql.SqlGeneratorDefaultImpl"
anchor="SqlGeneratorDefaultImpl">
<p>
Our extension is called
<code>com.xxx.ojb.SqlGenerator</code>, and it
changed the following behavior(s) of the base class:
</p>
<ol>
<li>
<p>
In <code>getPreparedInsertStatement(ClassDescriptor)</code>,
we utlize our extension to
<a href="#SqlInsertStatement">SqlInsertStatement</a>.
</p>
</li>
<li>
<p>
In <code>getPreparedUpdateStatement(ClassDescriptor)</code>,
we utlize our extension to
<a href="#SqlUpdateStatement">SqlUpdateStatement</a>.
</p>
</li>
<li>
<p>
In <code>getPreparedDeleteStatement(ClassDescriptor)</code>,
we utlize our extension to
<a href="#SqlDeleteByPkStatement">SqlDeleteByPkStatement</a>.
</p>
</li>
</ol>
</subsection>
<subsection name="org.apache.ojb.broker.accesslayer.JdbcAccessImpl"
anchor="JdbcAccessImpl">
<p>
Our extension is called
<code>com.xxx.ojb.JdbcAccess</code>, and it
changed the following behavior(s) of the base class:
</p>
<ol>
<li>
<p>
In the <code>executeInsert(ClassDescriptor, Object)</code>
method, our extension 'harvests' any values that were returned
by the stored procedure that was called. This 'harvesting'
of return values is handled by the
<code>captureOutputValues</code>
method, and it occurs immediately after the statement is
executed.
</p>
<p>
Here is a portion of the code where the 'harvesting' of
the output values occurs.
</p>
<source><![CDATA[
synchronized (stmt)
{
broker.serviceStatementManager()
.bindInsert(stmt, cld, obj);
stmt.executeUpdate();
this.captureOutputValues(stmt,
cld,
obj,
SqlStatementHelper.INSERT_STATEMENT);
}]]></source>
</li>
<li>
<p></p>
<p>
In the <code>executeUpdate(ClassDescriptor, Object)</code>
method, our extension calls the
<code>captureOutputValues</code> immediately after the
statement is executed in order to harvest any values
that were returned by the stored procedure.
</p>
</li>
</ol>
<p>
Here is the implementation of the
<code>captureOutputValues(PreparedStatement,
ClassDescriptor, Object, int)</code>
method.
</p>
<source><![CDATA[
private void captureOutputValues(PreparedStatement stmt,
ClassDescriptor cld,
Object obj,
int statementType){
FieldDescriptor[] fields = null;
if ( !(stmt instanceof CallableStatement) ) {
return;
}
// Get the correct statement type
CallableStatement callable = (CallableStatement)stmt;
// Get the fields that are relevant to the statement that we executed.
switch ( statementType ) {
case SqlStatementHelper.INSERT_STATEMENT:
fields = cld.getFieldDescriptions();
break;
case SqlStatementHelper.UPDATE_STATEMENT:
fields = cld.getFieldDescriptions();
break;
default:
break;
}
// If we have no fields, then we're done.
if ( (fields==null) ||
(fields.length==0) ) {
return;
}
try {
// Loop through the fields
for ( int i = 0; i < fields.length; i++ ) {
// Get the field that we'll process
FieldDescriptor fmd = fields[i];
// Is this an 'output' field?
if ( this.fieldIsOutput(fmd,statementType) ) {
// Get the field conversion
FieldConversion conversion = fmd.getFieldConversion();
// Get the jdbc type
int jdbcType = fmd.getColumnJdbcType();
// Get the value that was returned
Object val = this.getObjectFromStatement(callable,
jdbcType,
(i+1));
// Convert the value
val = conversion.sqlToJava(val);
// Store the value
fmd.getPersistentField().set(obj,val);
}
}
} catch ( SQLException t ) {
throw new PersistenceBrokerException("Error reading from result set",t);
}
}]]></source>
<p>
Here is the implementation of the
<code>fieldIsOutput(FieldDescriptor, int)</code>
method.
</p>
<source><![CDATA[
private boolean fieldIsOutput(FieldDescriptor fmd,
int statementType){
switch ( statementType ) {
case SqlStatementHelper.INSERT_STATEMENT:
return ("true".equals(fmd.getAttribute("return-on-insert","false")));
case SqlStatementHelper.UPDATE_STATEMENT:
return ("true".equals(fmd.getAttribute("return-on-update","false")));
default:
return false;
}
}]]></source>
</subsection>
<subsection name="org.apache.ojb.broker.accesslayer.StatementsForClassImpl"
anchor="StatementsForClassImpl">
<p>
Our extension is called
<code>com.xxx.ojb.StatementsForClass</code>, and it
changed the following behavior(s) of the base class:
</p>
<ol>
<li>
<p>
In the <code>prepareStatement(Connection, String,
boolean)</code> method, our extension creates a JDBC
<code>CallableStatment</code> rather than a JDBC
<code>PreparedStatemnt</code>. By doing so, we can 'harvest'
return values after executing a stored procedure.
</p>
<p>
To implement this change, we simply copied the existing
implementation of the <code>prepareStatement</code> method
to our extension and changed all calls to
<code>conn.prepareStatement(...)</code> to
<code>con.prepareCall(...)</code>.
</p>
</li>
</ol>
</subsection>
<subsection name="org.apache.ojb.broker.sql.SqlInsertStatement"
anchor="SqlInsertStatement">
<p>
Our extension is called
<code>com.xxx.ojb.SqlInsertStatement</code>, and it
changed the following behavior(s) of the base class:
</p>
<ol>
<li>
<p>
In the <code>getStatement</code> method, we interrogate the
class descriptor to determine the name of the procedure to
use when inserting a new record. The name of the procedure
to invoke is contained in the attribute
<code>insert-proc</code>. If no procedure is identified,
then the SQL statement is constructed using logic that is
implemented in the base class. If a procedure is
identified, then a SQL statement is constructed that calls
that procedure with the appropriate number of arguments.
</p>
<p>
Here's the basic logic of our version of the
<code>getStatement</code> method:
</p>
<source><![CDATA[
public String getStatement() {
// Get the name of the procedure to use.
String procName = cld.getAttribute("insert-proc",null);
String statement = null;
// If we have no proc, then defer to logic in the base class.
// Otherwise, format a statement that invokes the proc.
if (procName==null) {
return super.getStatement();
} else {
return SqlStatementHelper.buildStatement(
procName,
this.cld.getFieldDescriptions().length);
}
}]]></source>
<p>
Details about the
<code>SqlStatementHelper.buildStatement(...)</code>
method can be found <a href="#SqlStatementHelper">here</a>.
</p>
</li>
</ol>
</subsection>
<subsection name="org.apache.ojb.broker.sql.SqlUpdateStatement"
anchor="SqlUpdateStatement">
<p>
Our extension is called
<code>com.xxx.ojb.SqlUpdateStatement</code>, and it
changed the same behaviors as did our extension to
<code>org.apache.ojb.broker.sql.SqlInsertStatement</code>
except for the following:
<ol>
<li>
Instead of checking the value of the
<code>insert-proc</code> attribute,
<code>com.xxx.ojb.SqlUpdateStatement</code>
checks the value of the
<code>update-proc</code> attribute.
</li>
</ol>
</p>
</subsection>
<subsection name="org.apache.ojb.broker.sql.SqlDeleteByPkStatement"
anchor="SqlDeleteByPkStatement">
<p>
Our extension is called
<code>com.xxx.ojb.SqlDeleteByPkStatement</code>, and it
changed the same behaviors as did our extension to
<code>org.apache.ojb.broker.sql.SqlInsertStatement</code>
except for the following:
</p>
<ol>
<li>
<p>
Instead of checking the value of the <code>insert-proc</code>
attribute, <code>com.xxx.ojb.SqlDeleteByPkStatement</code>
checks the value of the <code>delete-proc</code> attribute.
</p>
</li>
<li>
<p>
When calling
<code>SqlStatementHelper.buildStatement(...)</code>
the length of the <code>this.cld.getPkFields()</code>
array is used, instead of the
<code>this.cld.getFieldDescriptions()</code> array.
</p>
</li>
</ol>
</subsection>
<subsection name="SqlStatementHelper"
anchor="SqlStatementHelper">
<p>
This class provides a single static method,
<code>buildStatement(String, int)</code> that is used to generate
the SQL syntax that is required to invoke a stored
procedure.
</p>
<source><![CDATA[
public static String buildStatement(String procName,
int fieldCount) {
// The result
StringBuffer stmt = new StringBuffer(256);
// Begin the statement
stmt.append("{ CALL ");
stmt.append(procName);
stmt.append(" ( ");
// Add placeholders for the fields.
for ( int i = 0; i < fieldCount; i++ ) {
stmt.append("? ");
if ( i < fieldCount - 1 ) {
stmt.append(", ");
}
}
// Complete the statement
stmt.append(") }");
// Return the results.
return stmt.toString();
}]]></source>
</subsection>
</section>
<section name="OJB.properties" anchor="OJB.properties">
<p>
In order to get OJB to utilize our customizations, we had to change
three settings in the OJB.properties file.
</p>
<source><![CDATA[
#------------------------------------------------------------------------------
# SqlGenerator
#------------------------------------------------------------------------------
# The SqlGeneratorClass entry defines the SqlGenerator implemementation to be
# used
SqlGeneratorClass=com.xxx.ojb.SqlGenerator
#------------------------------------------------------------------------------
# StatementManager
#------------------------------------------------------------------------------
# The StatementManagerClass entry defines the StatementManager implemementation
# to be used
StatementManagerClass=com.xxx.ojb.StatementManager
#------------------------------------------------------------------------------
# JdbcAccess
#------------------------------------------------------------------------------
# The JdbcAccessClass entry defines the JdbcAccess implemementation to be used
JdbcAccessClass=com.xxx.ojb.JdbcAccess]]></source>
</section>
</body>
</document>