[JBoss-dev] Code: Stable RowID For Fast Updates

K.V. Vinay Menon Sun, 10 Jun 2001 03:45:48 -0700

Hello Dan, Georg and all.

Here is the code update for the Fast RowID based updates and deletes.

1. I have NOT modified the insert for insert into as I am trying to avoid Oracle specified code [at least until JAWS becomes completetly pluggable and all].

2. The only issue with this is that if the user specified using the rowid and the rowid is null [say in ejbPostCreate, and when else????]. Given appropriate usage guidelines I think this can be avoided.

3. I could have added code [in fact I did but thought otherwise after that for defaulting back to the primary key IF the rowid is null but that means we'd have to change the sql on the fly [ and during setParameters since that is where the field values are actually read] which made the code look rather clumsy.

To use this the user needs to specify the stable-rowid-column value in the jaws.xml and a matching cmp field in the bean.

Regards

Vinay

1. To the JawsEntityMetaData file added

//Get row id for fast stable row id based updates/deletes and inserts

stableRowIdColumn = getElementContent(getOptionalChild(element, "stable-rowid-column"));

2. JDBCCreateEntityCommand.java

a) Constructor

         /**
          * Don't include the rowid column in the insert. That is something the
          * database manages for you.
          */
         if(!cmpField.getColumnName().equalsIgnoreCase(rowIDColumn))
         {
             fieldSql += (first ? "" : ",") +
                         cmpField.getColumnName();
             valueSql += first ? "?" : ",?";
             first = false;
         }
b) setParameters

         /**
          * Don't use the rowid column as that is something the dbms
          * manages for you.
          */
         if(!cmpField.getColumnName().equalsIgnoreCase(rowIDColumn))
         {
             Object value = getCMPFieldValue(ctx.getInstance(), cmpField);
             setParameter(stmt, idx++, cmpField.getJDBCType(), value);
         }
3.JDBCStoreEntityCommand

a) setParameters

String stableRowIdColumn = jawsEntity.getStableRowIdColumn();

      /**
       * Will store the actual value for the stable row id column
       * and the JDBC type for this column
       */
      Object stableRowIdColumnValue=null;
      int stableRowIdColumnType=0;

      while (iter.hasNext())
      {
         CMPFieldMetaData cmpField = (CMPFieldMetaData)iter.next();

         /**
          * If the stable row id column was specified in jaws.xml and the current
          * column being processed is the stable rowid coulmn, then save the value
          * and datatype as we will need it to set the parameters in the WHERE
          * clause
          */
         if(stableRowIdColumn!=null && cmpField.getColumnName().equalsIgnoreCase(stableRowIdColumn))
         {
            stableRowIdColumnValue = es.currentState[i];
            stableRowIdColumnType = cmpField.getJDBCType();
         }
         else
         {
             if (!tuned || es.dirtyField[i])
             {
                setParameter(stmt, idx++, cmpField.getJDBCType(), es.currentState[i]);
             }
         }

i++;
}

      /**
       * Alright, we've set all the parameters now and lets set the parameters for
       * the WHERE clause. If the stable row id was specified in the jaws.xml
       * then set the parameter for that alone, else set the usual primary key
       * parameters to update based on that.
       */
      if(stableRowIdColumn!=null)
      {
          setParameter(stmt,idx,stableRowIdColumnType,stableRowIdColumnValue);
      }
      else
      {
          setPrimaryKeyParameters(stmt, idx, es.ctx.getId());
      }
b) makeSQL

      String stableRowIdColumn = jawsEntity.getStableRowIdColumn();
      if(stableRowIdColumn==null)
      {
        /**
         * If it isn't there then just set it to an empty string to avoid checking
         * for null in a loop.
         */
        stableRowIdColumn = "";
      }

      String sql = "UPDATE "+jawsEntity.getTableName()+" SET ";
      Iterator iter = jawsEntity.getCMPFields();
      int i = 0;
      boolean first = true;
      while (iter.hasNext())
      {
         CMPFieldMetaData cmpField = (CMPFieldMetaData)iter.next();

         if(!(cmpField.getColumnName().equalsIgnoreCase(stableRowIdColumn) || cmpField.getName().equalsIgnoreCase(stableRowIdColumn)))
         {
             if (!tuned || es.dirtyField[i++])
             {
                sql += (first?"":",") +
                   cmpField.getColumnName() + "=?";
                first = false;
             }
        }
      }

sql += " WHERE ";

      //Construct the WHERE clause - either based on the stable row id or the PK
      if(stableRowIdColumn.trim().length()>1 && (!overrideRowId))
      {
          sql += stableRowIdColumn+" =?";
      }
      else
      {
        sql += getPkColumnWhereList();
      }

4. JDBCRemoveEntityCommand

a) Constructor

String stableRowIdColumn = jawsEntity.getStableRowIdColumn();

String sql;

      /**
       * If the stable rowid column was specified in the jaws.xml then use it
       * for delete operations. Else use the standard PK based delete.
       */
      if(stableRowIdColumn!=null)
      {
          // Remove SQL
          sql = "DELETE FROM " + jawsEntity.getTableName() +" WHERE "+stableRowIdColumn+"=?";
      }
      else
      {
          // Remove SQL
          sql = "DELETE FROM " + jawsEntity.getTableName() +
                       " WHERE "+getPkColumnWhereList();
      }
b) setParameters

String stableRowIdColumn = jawsEntity.getStableRowIdColumn();

int i=0;

      //If the stable rowid coulumn was specified get its value and set param
      if(stableRowIdColumn!=null )
      {
          Iterator iter = jawsEntity.getCMPFields();
          while (iter.hasNext())
          {
             CMPFieldMetaData cmpField = (CMPFieldMetaData)iter.next();

             if(cmpField.getColumnName().equalsIgnoreCase(stableRowIdColumn))
             {
                setParameter(stmt, 1, cmpField.getJDBCType(), es.currentState[i]);
             }

             i++;
          }
      }
      //Else just use the standard PK values
      else
      {
        setPrimaryKeyParameters(stmt, 1, ctx.getId());
      }

Vinay

[JBoss-dev] Code: Stable RowID For Fast Updates

Reply via email to