db-ojb-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From t...@apache.org
Subject cvs commit: db-ojb/xdocs how-to-work-with-stored-procedures.xml project.xml
Date Thu, 08 May 2003 06:08:09 GMT
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>
  
  
  
  

Mime
View raw message