[JBoss-dev] Fast Updates Based on Row ID

K.V. Vinay Menon Wed, 06 Jun 2001 05:36:33 -0700

Hi Marc,

When you were here in London we'd discussed adding functionality to use things like ROWID for fast updates and deletes [as opposed to using primary keys]. I have been able to implement this by

1. Adding a field in jaws.xml called rowid-column name. This is ROWID for Oracle and can be something else for other databases. If you do not want to use this feature just don't specify the tag and it will use the default mechansm based on the primary key.

2. Updated JDBCStoreEntityCommand as follows

   protected String makeSQL(Object argOrArgs)
   {
      ExecutionState es = (ExecutionState)argOrArgs; // NB: null if tuned
      boolean tuned = jawsEntity.hasTunedUpdates();

      //Added by Vinay Menon - Start
      String rowIDColumn = jawsEntity.getRowIDColumnName();
      //Added by Vinay Menon - End

      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(rowIDColumn))
         {
             if (!tuned || es.dirtyField[i++])
             {
                sql += (first?"":",") +
                   cmpField.getColumnName() + "=?";
                first = false;
             }
        }
      }

//Modified by Vinay Menon - Start
sql += " WHERE ";

      if(rowIDColumn!=null && rowIDColumn.trim().length()>1)
      {
          sql += rowIDColumn+" =?";
      }
      else
      {
        sql += getPkColumnWhereList();
      }
      //Modified by Vinay Menon - End

return sql;
}

and

   protected void setParameters(PreparedStatement stmt, Object argOrArgs)
      throws Exception
   {
      ExecutionState es = (ExecutionState)argOrArgs;
      boolean tuned = jawsEntity.hasTunedUpdates();

      //Added by Vinay Menon - Start
      String rowIDColumn = jawsEntity.getRowIDColumnName();
      //Added by Vinay Menon - End

      int idx = 1;
      Iterator iter = jawsEntity.getCMPFields();
      int i = 0;

Object rowIDValue=null;
int rowIDJDBCType=0;

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

    f((!cmpField.getColumnName().equalsIgnoreCase(rowIDColumn)) && (!cmpField.getName().equalsIgnoreCase(rowIDColumn)))
         {
            rowIDValue = es.currentState[i];
            rowIDJDBCType = cmpField.getJDBCType();
         } else {
             if (!tuned || es.dirtyField[i])
             {
                setParameter(stmt, idx++, cmpField.getJDBCType(), es.currentState[i]);
             }
         }

i++;
}

      if(rowIDColumn!=null)
      {
          setParameter(stmt,idx,rowIDJDBCType,rowIDValue);
      }
      else
      {
          setPrimaryKeyParameters(stmt, idx, es.ctx.getId());
      }
      //Modified by Vinay Menon - End
   }

3. Similarly updated JDBCRemoveEntityCommand as

protected void setParameters(PreparedStatement stmt, Object argOrArgs)
      throws Exception
   {
      EntityEnterpriseContext ctx = (EntityEnterpriseContext)argOrArgs;

ExecutionState es = (ExecutionState)argOrArgs;

      //Modified by Vinay Menon - Start
      String rowIDColumn = jawsEntity.getRowIDColumnName();
      int i=0;

      if(rowIDColumn!=null)
      {
          Iterator iter = jawsEntity.getCMPFields();
          while (iter.hasNext())
          {
             CMPFieldMetaData cmpField = (CMPFieldMetaData)iter.next();

             if(cmpField.getColumnName().equalsIgnoreCase(rowIDColumn))
             {
                System.out.println("Delete for "+rowIDColumn+"="+es.currentState[i]);
                setParameter(stmt, 1, cmpField.getJDBCType(), es.currentState[i]);
             }

             i++;
          }
      }
      else
      {
        setPrimaryKeyParameters(stmt, 1, ctx.getId());
      }
      //Modified by Vinay Menon - Start
   }

and

   public JDBCRemoveEntityCommand(JDBCCommandFactory factory)
   {
      super(factory, "Remove");

//Modified by Vinay Menon - Start
String rowIDColumn = jawsEntity.getRowIDColumnName();

String sql;

      if(rowIDColumn!=null)
      {
          // Remove SQL
          sql = "DELETE FROM " + jawsEntity.getTableName() +" WHERE "+rowIDColumn+"=?";
      }
      else
      {
          // Remove SQL
          sql = "DELETE FROM " + jawsEntity.getTableName() +
                       " WHERE "+getPkColumnWhereList();
      }
      //Modified by Vinay Menon - End

setSQL(sql);
}

3. Updated JawsEntityMetaData with

public String getRowIDColumnName() { return rowIDColumnName; }

        //Added by Vinay Menon - Start
        //If a rowid [as in Oracle is present use it to optimize updates and deleted
        rowIDColumnName = getElementContent(getOptionalChild(element, "rowid-column"));
        //Added by Vinay Menon - End

This way all you need in your ejb is a cmp field with the corresponding name [or mapped to the corresponding column in jaws.xml]. Updates and Deletes will use this column for all their sqls. Note that updates will NOT update the rowid-column column. If nothing is specified in jaws.xml defaults are used.

Sample SQLs generated would look like

DELETE FROM itemizedcall WHERE ROWID=?

DELETE FROM itemizedcall WHERE accountnumber=? AND mobilenumber=? AND invoicenumber=? AND itemizedcallnumber=?

and

UPDATE itemizedcall SET itemizedcallnumber=?,callcost=?,callduration=?,mobilenumber=?,accountnumber=?,invoicenumber=?,calltype=? WHERE ROWID =?

UPDATE itemizedcall SET itemizedcallnumber=?,callcost=?,callduration=?,mobilenumber=?,accountnumber=?,invoicenumber=?,calltype=? WHERE account
number=? AND mobilenumber=? AND invoicenumber=? AND itemizedcallnumber=?

Please let me know what you think.

Regards

Vinay

Vinay Menon

ISe-net Solutions
Carphone Warehouse plc
www.carphonewarehouse.com

+44-2088968038 (W)
+44-7801054259 (M)
+44-7808470016 (F)
[EMAIL PROTECTED]

[JBoss-dev] Fast Updates Based on Row ID

Reply via email to