How to Ignore Time Component of Date column when filtering on Date.

When you create a table that supports column filtering,  the default behavior for filtering on dates is to compare the date directly.  This means that if the date in the database has a time component the row will not match and no results will be found.    If you don't need to display the time along with the date in the table then you can just put a trunc(...) around the date in the original query.   If you do need to display the time then you can add another view attribute to your query that outputs the trunc'd version of the date.   The new column will not be displayed but will be used for sorting and filtering.   The af:column component has a sortProperty attribute that usually contains the column attribute name for the column displayed below it.   This does not have to be the case however.  The sortProperty can be set to any column in the view object so that when you put a value in the filter field it filters and sorts on a different column then the one displayed below it.  Set the sortProperty for the date/time column to the date only column and you will get the desired behavior.  When you filter on a date it will return all rows with that date regardless of the time portion of the date.

There is another way to accomplish this that will make every Date filter within your application time agnostic so that developers do not need to add this extra column every time they want to filter on the date portion of a date/time column.To do this you need to create a custom SQL builder class that extends OracleSQLBuilderImpl and override the getFormattedLHSCompareFragment method (Left Hand Side Compare Fragment)  This is called whenever filtering is performed.   In this method add the trunc(...) around the value.  Sample code is shown below.


This class is activated by adding a JVM parameter on startup (jbo.SQLBuilder)


  -Djbo.SQLBuilder=com.yourCompany.model.custom.MySQLBuilderImpl

/**
 * Override method in OracleSQLBuilderImpl in order to trunc (remove time component)
 * when filtering on Dates in tables (Query By Example) QBE
 * This class is activated by adding a JVM parameter
 *  -Djbo.SQLBuilder=com.yourCompany.model.custom.MySQLBuilderImpl
 */
public class MySQLBuilderImpl extends OracleSQLBuilderImpl {
    
    private static final Log log = LogFactory.getLog(MySQLBuilderImpl.class);
    private static SQLBuilder mSQLBuilderInterface=null;  
    
    public MySQLBuilderImpl() {
    }


  /**
   * Gets the singleton instance of this class.
   * @return a SQLBuilder object.
   */
  synchronized public static SQLBuilder getInterface()
  {
     if (mSQLBuilderInterface == null)
     {
        if (Diagnostic.isOn())
        {
           Diagnostic.println("OracleSQLBuilder reached getInterface");
        }

        mSQLBuilderInterface = (SQLBuilder)(new MySQLBuilderImpl());

        if (Diagnostic.isOn())
        {
           Diagnostic.println(mSQLBuilderInterface.getVersion());
        }
     }
     return mSQLBuilderInterface;
  }
 

  @Override
  public String getFormattedLHSCompareFragment(ViewObject vo, ViewCriteria vc,
                                               AttributeDef attrDef,
                                               int sqltype, String lhs,
                                               Object rhs) {
   String value =   super.getFormattedLHSCompareFragment(vo, vc, attrDef, sqltype, lhs, rhs);
   
    if (sqltype==Types.DATE || sqltype==Types.TIMESTAMP) {
      
      // only apply trunc to database columns, not view alias names used for in-memory filtering
      // ADF does not handle a trunc for a in-memory filter and will get an error
      Integer attr = vo.getAttributeIndexOf(value); // 
      if (attr == -1){  // not found, meaning it's a database column name, not an attribute name.
      
       value = "TRUNC("+value+")";
        if (log.isDebugEnabled()) {
            log.debug("MySQLBuilderImpl truncating date value:"+value);
        }
      }
    }
    return value;
  }
}

Commiting an ADF view that contains an outer join

Here is my problem

• I have a ADF writable View of table1 that that contains a left outer join to table2.  
• Table2 has a foreign key to table1. 
• Both Table1 and Table2 contain editable fields displayed in the UI.
• Table1 contains an existing row to be edited and will always be commited but I don't want to create a row in table2 unless the user enters data in one of table2's fields

My problem was that if I tried to commit, I would get an "attribute x is required" message because the foreign key was missing.  To solve this I opened the view in jdeveloper, clicked on the Java tab and selected the "Generate View Row Class" so that a ViewRowImpl.java class is created.

 I then opened this generated class and updated the setter for each attribute that can be edited in the UI. Below is an example. The red text was what I added.   KeyAttribute is the primary key of table1 KeyAttribute2 is the foreign key in table2.  They need to be set to the same value before table2 can be commited. If there is currently no matching row in table2, KeyAttribute2 will be null.  KeyAttribute1 is a DBSequence and KeyAttribute2 is a Number, hence the call to getSequenceNumber to convert it to a Number.  With this code, now I don't get the error any more and also it won't commit a row in table2 if no data for it has been entered.

    public void setMyDate(Date value) {
        if (value!=null && getKeyAttribute2() == null){

          setKeyAttribute2(getKeyAttribute().getSequenceNumber());

        }

        setAttributeInternal(MYDATE, value);
    }

This works great when the row you are editing in table1 already exists.  If you want to create a new row in table1 and commit it at the same time as a new row in table2 it is a bit more challenging as you will need to generate a DBSequence to be added to both the Primary and Foreign key fields but only to the foreign key field if other data exists for table2, otherwise you will create a empty row in table2 with only the foreign key.