BC4J Helper by Sung Im
Thoughts on how BC4J works, code samples, and other helpful techniques
        

Entity Object over Table Whose Primary Key May Be NULL

Some people have tried building an Entity Object over a database table where:

  1. The table has multi-part unique columns.
  2. For some rows, some of these columns are NULL.

In general, if you mark a column as a primary key column, the DBMS will not allow NULL into such a column.  This is true even when multiple columns are used for the primary key.  However, in some applications, the user can map a set of attributes whose values can uniquely identify the row as the primary key attributes for the Entity (without relying on the DBMS's primary key constraint).  Further for some rows, these unique columns may have NULL in them.

If you so build a BC4J Entity Object with multi-part EO primary key where some Entity rows may have null for primary key attributes, you are likely to run into problems.  Symptoms of these problems may be:

  1. You apply DML operation on a row whose PK parts are NULL and find that the updates didn't make to the database.
  2. You get oracle.jbo.RowAlreadyDeletedException on a row whose PK parts are NULL, although the row is still in the table.

The base BC4J classes have the underlying assumption that all PK attributes are not null.  The main reason behind this assumption is for the Entity Cache to reuse one statement.  Let me elaborate on that.

Suppose we have the following EO (Number is oracle.jbo.domain.Number):

   entity Emp
   {
      attribute Number     EmpNum      mapped to EMPNO;  /*PK1*/
      attribute String     EmpName     mapped to ENAME;
      attribute String     EmpJob      mapped to JOB;
      attribute Number     EmpMgr      mapped to MGR;
      attribute String     EmpHireDate mapped to HIREDATE;
      attribute Number     EmpSal      mapped to SAL;
      attribute Number     EmpComm     mapped to COMM;   /*PK2*/
      attribute Integer    EmpDeptNum  mapped to DEPTNO;
   }

EmpNum and EmpComm are marked as primary keys.

When BC4J needs to query for a database row or issue a UPDATE/DELETE statement, it roughly uses the following where-clause:

   WHERE EMPNO = :1 AND COMM = :2

Then, we bind attribute values for EmpNum and EmpComm into this where-clause.

However, if EmpComm happens to be null, the SQL statement will silently fail.  I.e., it will fail to find a row whose EMPNO is EmpNum and COMM is null.  This is due to the good old NULL handling idiosyncracy of SQL where NULL is not considered a value.  Hence, when COMM is NULL, the correct where-clause is:

   WHERE EMPNO = :1 AND COMM IS NULL

Of course, BC4J can check the PK attribute values for null and generate a statement with appropriate "IS NULL."  However, doing this will mean that one statement could not be used for all Entity rows.  That is, one row may need the first flavor of where-clause and another row the second flavor.

Since JDBC statements are a precious resource (not to be wasted) and since we feel NULLable PKs are an exception rather than the rule, the base BC4J classes assumes that the PK are NOT NULL.  This allows us to use one statement for all rows in that Entity cache.

For the NULLable PK cases, you would need to write custom code.  Here is a sample on how to make this work.  (This sample is based on BC4J 9.0.3.  Future versions may make this easier--requiring less custom code.)  Let's continue with the above example where COMM is a NULLable PK.

Here are three methods that you need to override in your custom EntityImpl (in my example, it would be EmpImpl.java):

  protected void doSelect(boolean lock); 
  protected StringBuffer buildDMLStatement(int operation,
                                            AttributeDefImpl[] allAttrs,
                                            AttributeDefImpl[] retCols,
                                            AttributeDefImpl[] retKeys,
                                            boolean batchMode);
  protected int bindDMLStatement(int operation,
                                  PreparedStatement stmt,
                                  AttributeDefImpl[] allAttrs,
                                  AttributeDefImpl[] retCols,
                                  AttributeDefImpl[] retKeys,
                                  HashMap retrList,
                                  boolean batchMode)
      throws SQLException;

Here are the actual bodies of these methods:

imports

You need the following imports:

import com.sun.java.util.collections.HashMap; 
import java.sql.PreparedStatement; 
import java.sql.ResultSet;
import java.sql.SQLException;
import oracle.jbo.AlreadyLockedException; 
import oracle.jbo.AttributeDef;
import oracle.jbo.CSMessageBundle;
import oracle.jbo.DMLException;
import oracle.jbo.JboException;
import oracle.jbo.RowAlreadyDeletedException;
import oracle.jbo.RowInconsistentException;
import oracle.jbo.TooManyObjectsException;
import oracle.jbo.common.Diagnostic;
import oracle.jbo.server.DBTransactionImpl;


doSelect OVERRIDE

   protected void doSelect(boolean lock)
   {
      switch (getPostState())
      {
         case STATUS_INITIALIZED:
            /* do nothing */
            break;
         case STATUS_DEAD:
            if (Diagnostic.isOn())
            {
               Diagnostic.println("Warning: Ignoring fault-in for STATUS_DEAD entity");
            }
            break;
         default:
            faultInEntity(lock);
      }
   }

   void faultInEntity(boolean lock)
   {
      PreparedStatement stmt = null;
      ResultSet result = null;
      String stmtStr;
     
      /* Build the statement */
      stmtStr = "SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO FROM EMP empTab WHERE EMPNO=:1 AND ";
     
      Number commVal = (Number) getAttributeInternal(EMPCOMM);
     
      /* Check to see if COMM is NULL.  If so, use "IS NULL"
      * in the fault-in statement */
      if (commVal == null)
      {
         stmtStr += "COMM IS NULL";
      }
      else
      {
         stmtStr += "COMM=:2";
      }
      /* Add for-update for lock */
      if (lock)
      {
         stmtStr += " FOR UPDATE NOWAIT";
      }
     
      if (Diagnostic.isOn())
      {
         Diagnostic.println("## Statement: " + stmtStr);
      }
     
      /* Create a prepared statement */
      stmt = getDBTransaction().createPreparedStatement(stmtStr, 1 /*noRowsPrefetch*/);
     
      try
      {
         /* Use Oracle style binding */
         ((oracle.jdbc.OracleStatement) stmt).setEscapeProcessing(false);
         /* Bind EMPNO */
         stmt.setObject(1, getEmpNum());
         /* Bind COMM only if it's not NULL */
         if (commVal != null)
         {
            stmt.setObject(2, commVal);
         }
         /* Find the matching row */
         result = stmt.executeQuery();
         if (!result.next())
         {
            /* Cannot find the row.  It must've been deleted by
            * another user */
            throw new RowAlreadyDeletedException(true /*entity row*/,
                                                 getEntityDef().getName(),
                                                 getPrimaryKey());
         }
         if (lock)
         {
            /* Mark the entity row for locked */
            setLocked(true);
         }
         /* Resolve attrs coming from the table */
         AttributeDef attrs[] = getEntityDef().getAttributeDefs();
         Object[] newAttrVals = new Object[attrs.length];
         Object[] origAttrVals = new Object[attrs.length];
         int[] changedAttrIndices = new int[attrs.length];
         boolean isConsistent = true;
         int noChangedAttrs = 0;
        
         for (int j = 0; j < attrs.length; j++)
         {
            /* Get the new val and compare against old */
            newAttrVals[j] = ((AttributeDefImpl) attrs[j]).loadFromResultSet(result, j + 1, (DBTransactionImpl) getDBTransaction());
            Object oldAttrVal = getAttributeValue((AttributeDefImpl) attrs[j]);
            boolean needsToSet = false;
           
            /* Figure out whether we need to populate the
            * row with the new value.  Also, check to see if
            * the value coming from table differs from the value
            * in cache.  If so and if we're locking the row,
            * prepare for RowInconsistentException */
            if (newAttrVals[j] == null)
            {
               if (oldAttrVal != null)
               {
                  needsToSet = true;
                 
                  if (lock)
                  {
                     isConsistent = false;
                  }
               }
            }
            else  if (oldAttrVal == null && newAttrVals[j] != null)
            {
               needsToSet = true;
            }
            else  if (newAttrVals[j].equals(oldAttrVal) == false)
            {
               needsToSet = true;
               if (lock)
               {
                  isConsistent = false;
               }
            }
            if (attrs[j].isPrimaryKey() == false)
            {
               if (isAttributeChanged(j) == false)
               {
                  if (needsToSet)
                  {
                     /* Populate the row */
                     origAttrVals[noChangedAttrs] = oldAttrVal;
                 
                     populateAttribute(j, newAttrVals[j]);
                     changedAttrIndices[noChangedAttrs] = j;
                     noChangedAttrs++;
                  }
               }
            }
         }
         /* If there are more than one row of matching PK,
         * throw a TooManyObjectsException */
         if (result.next())
         {
            throw new TooManyObjectsException(getPrimaryKey());
         }
         /* We are locking the row and found inconsistent attr
         * values.  Throw a RowInconsistentException */
         if (!isConsistent)
         {
            int[] tmpAttrs = new int[noChangedAttrs];
            Object[] tmpVals = new Object[noChangedAttrs];
           
            System.arraycopy(changedAttrIndices, 0, tmpAttrs, 0, noChangedAttrs);
            System.arraycopy(origAttrVals, 0, tmpVals, 0, noChangedAttrs);
           
            notifyAttributesChanged(tmpAttrs, tmpVals);
            throw new RowInconsistentException(getPrimaryKey());
         }
      }
      catch(SQLException ex)
      {
         if (lock && (ex.getErrorCode() == 54))
         {
            throw new AlreadyLockedException(ex);
         }
         else
         {
            if (Diagnostic.isOn())
            {
               Diagnostic.println("doSelect failed...");
               Diagnostic.printStackTrace(ex);
            }
            throw new DMLException(CSMessageBundle.class,
                                   CSMessageBundle.EXC_ENTITY_SELECT,
                                   new Object[] { getEntityDef().getName()},
                                   ex);
         }
      }
      catch (Exception ex)
      {
         if (ex instanceof JboException)
         {
            throw (JboException) ex;
         }
         else
         {
            if (Diagnostic.isOn())
            {
               Diagnostic.println("doSelect failed...");
               Diagnostic.printStackTrace(ex);
            }
            throw new DMLException(CSMessageBundle.class,
                                   CSMessageBundle.EXC_ENTITY_SELECT,
                                   new Object[] { getEntityDef().getName()},
                                   ex);
         }
      }
      finally
      {
         try
         {
            if (result != null)
            {
               result.close();
            }
            if (stmt != null)
            {
               stmt.close();
            }
         }
         catch (SQLException e)
         {
            if (Diagnostic.isOn())
            {
               Diagnostic.println("doSelect cleanup failed");
               Diagnostic.printStackTrace(e);
            }
         }
      }
   }


buildDMLStatement OVERRIDE

   /* Override DML statement building for IS NULL handling */
   protected StringBuffer buildDMLStatement(int operation,
                                            AttributeDefImpl[] allAttrs,
                                            AttributeDefImpl[] retCols,
                                            AttributeDefImpl[] retKeys,
                                            boolean batchMode)
   {
      Number commVal = (Number) getAttributeInternal(EMPCOMM);
     
      if (commVal != null)
      {
         return super.buildDMLStatement(operation, allAttrs, retCols, retKeys, batchMode);
      }
      switch (operation)
      {
         case DML_INSERT:
            return super.buildDMLStatement(operation, allAttrs, retCols, retKeys, batchMode);
         case DML_UPDATE:
         {
            StringBuffer strBuf = new StringBuffer("UPDATE EMP empTab SET ");
            int bindIndex = 1;
            boolean isFirst = true;
           
            for (int i = 0; i < allAttrs.length; i++)
            {
               AttributeDefImpl ad = allAttrs[i];
               if (isAttributeChanged(ad.getIndex()))
               {
                  if (isFirst == false)
                  {
                     strBuf.append(",");
                  }
                  strBuf.append(allAttrs[i].getColumnName()).append("=:").append(bindIndex++);
                  isFirst = false;
               }
            }
            strBuf.append(" WHERE EMPNO=:").append(bindIndex++).append(" AND COMM IS NULL");
            return strBuf;
         }
         case DML_DELETE:
            return new StringBuffer("DELETE FROM EMP empTab WHERE EMPNO=:2 AND COMM IS NULL");
      }
      throw new IllegalArgumentException("");
   }


bindDMLStatement OVERRIDE

   /* Override DML statement binding for IS NULL handling */
   protected int bindDMLStatement(int operation,
                                  PreparedStatement stmt,
                                  AttributeDefImpl[] allAttrs,
                                  AttributeDefImpl[] retCols,
                                  AttributeDefImpl[] retKeys,
                                  HashMap retrList,
                                  boolean batchMode)
      throws SQLException
   {
      int bindIndex = 1;
      Number commVal = (Number) getAttributeInternal(EMPCOMM);
     
      if (commVal != null)
      {
         return super.bindDMLStatement(operation, stmt, allAttrs, retCols, retKeys, retrList, batchMode);
      }
      switch (operation)
      {
         case DML_INSERT:
            return super.bindDMLStatement(operation, stmt, allAttrs, retCols, retKeys, retrList, batchMode);
         case DML_UPDATE:
         {
            for (int i = 0; i < allAttrs.length; i++)
            {
               AttributeDefImpl ad = allAttrs[i];
               if (isAttributeChanged(ad.getIndex()))
               {
                  Object o = getAttributeInternal(ad.getIndex());
                  o = (o != null) ? ad.convertToStorage(o, (DBTransactionImpl) getDBTransaction()) : null;
                  if (o == null)
                  {
                     stmt.setNull(bindIndex++, ad.getSQLType());
                  }
                  else
                  {
                     stmt.setObject(bindIndex++, o);
                  }
               }
            }
            /* Where-clause */
            stmt.setObject(bindIndex++, getEmpNum());
            break;
         }
         case DML_DELETE:
         {
            /* Where-clause */
            stmt.setObject(bindIndex++, getEmpNum());
            break;
         }
      }
      return bindIndex;
   }



© Copyright 2003 Sung Im. Click here to send an email to the editor of this weblog.
Last update: 10/1/03; 11:54:04 AM.