Friday, April 3, 2015

How to Add QBE Operators to Date column of table

The QBE “Query By Example” (as they are called by Oracle) table filters support the following operators.

Greater than
Less than
Greater than or equal to
Less than or equal to

So you can do things like this
>2000 and < 6000
L or A or F

This works for number and string fields only not for date fields.
This is how I implemented support for date fields.  The result looks like this

This example presumes you already have a table with filterable columns.
You should have a search region in your bindings executable list. Mine is called resolutionTargetQuery.  You will need to replace that value in the code below with whatever the name of your query is.
The first thing you need to do is add a calculated column to your view object that returns the numeric value of the date.   You need to get the java Date.getTime()  numeric value.   This will be the column used to filter the date on.   There are several ways to create this column.   If your date column is of type oracle.jbo.domain.Date then you can use an expression like shown below for your calculated column

After you have the new column, edit the table tree binding and shuttle the the new column  to the right to make it available for usage on the page.

Now we add a queryListener to the backing bean.  This will get called anytime you filter on the table.   In the queryListener we detect if the date field is being filtered and if so we switch it to filter on the numeric date column instead of the actual date column, pre-pending the value with the operator selected from the dropdown.  Remember, numeric columns support the operator but date columns do not.
Then we execute the query
After executing the query we need to put back the original date values so they don't  get removed from the table header bar.

In the jspx, on the af:table add queryListener property

This gets called anytime you filter the table

In the Table Column definition add a toolbar to the filter facet containing the dropdown and date input field
<af:column align="center" filterable="true" headertext="Effective Start Date" sortable="true" sortproperty="targetEffectiveDate" width="130">
    <f:facet name="filter">
        <af:toolbar id="t1">
            <af:selectonechoice rendered="true" value="#{pageFlowScope.backing_bean.startDateOperator}">
                <af:selectitem label="=" value="">
                <af:selectitem label="&gt;" value="&gt;">
                <af:selectitem label="&gt;=" value="&gt;=">
                <af:selectitem label="&lt;" value="&lt;">
                <af:selectitem label="&lt;=" value="&lt;=">
            <af:inputdate columns="8" value="#{vs.filterCriteria.targetEffectiveDate}">
    <af:inputdate label=" " required="true" value="#{row.bindings.targetEffectiveDate.inputValue}">
        <af:convertdatetime pattern="#{bindings.resolutionTargets.hints.targetEffectiveDate.format}">


In the backing bean add your queryListener
// class properties with getters and setters 
 String startDateOperator="";  
 String endDateOperator=""; 
 private void processQuery(QueryEvent queryEvent) {
    RichTable table = (RichTable)queryEvent.getComponent();
    FilterableQueryDescriptor filterQD = (FilterableQueryDescriptor)table.getFilterModel();
    Map filterCriteria = filterQD.getFilterCriteria();
    Date start = (Date)filterCriteria.get("targetEffectiveDate");
    Date end = (Date)filterCriteria.get("targetEndDate");
    // get time from Date object and filter on different column
    // containing long value of date instead of Date
    if (start != null){
        long tm = start.getTime();
        filterCriteria.put("targetEffectiveDateLong",startDateOperator+tm);  // sorting on this column instead
        filterCriteria.put("targetEffectiveDate", null); // origianl column sort value must be null or will fail
    } else{
        filterCriteria.put("targetEffectiveDateLong", null);
        filterCriteria.put("targetEffectiveDate", null);
    if (end != null){
        long tm = end.getTime();
        filterCriteria.put("targetEndDateLong", endDateOperator+tm); // sorting on this column instead
        filterCriteria.put("targetEndDate", null); // this must be null or will fail
        filterCriteria.put("targetEndDateLong", null);
        filterCriteria.put("targetEndDate", null);
    DCBindingContainer bc =  (DCBindingContainer)BindingContext.getCurrent().getCurrentBindingsEntry();
    Object execBinding =  bc.findExecutableBinding("resolutionTargetsQuery");
 // if table editingMode mode propery is set to clickToEdit and
 // if you have a selected row when you filter the table and the filter removes the selected row
// you will get an exception when you select a new row because it can't find the previous selected row and will get
// JBO-25006: Value oracle.adf.model.bean.DCDataRow passed as parameter row to method ViewRowSetIteratorImpl.scrollRangeTo is invalid: not in the row set. 
// calling this function will unselect all selected rows prior to filtering to fix that problem.
     ADFUtils.resetRichTableSelection(table); // de-select any selected rows.

    // not suppose to cast to internal oracle class but other technique commented out below
    // doesn't work with POJO data control.  It may work work normal view object
    // execute filter
    ((FacesCtrlSearchBinding) execBinding).processQuery(queryEvent);
    // this will fail
    //           invokeQueryEventMethodExpression("#{bindings.allEmployeeesQuery.processQuery}",queryEvent);
    // restore original filter date values so they continues to display in the QBE bar
        filterCriteria.put("targetEffectiveDate", start); // restore original
        filterCriteria.put("targetEffectiveDateLong", null); // 
        filterCriteria.put("targetEndDate", end); // restore original
        filterCriteria.put("targetEndDateLong", null);
    } catch (Exception e){

  With a little more work you could support a between operator.  This expression works on numbers
<100000 and >25000.  So add two date input fields and show the second one with a partial trigger when you select "between" from the dropdown.  Then in your query listener set the appropriate filter statement using the value of the two dates.

Here is a link to another implementation

Wednesday, February 11, 2015

How to Handle Http Session Expiration on Login Page

We had a problem where if you sat on the login page for longer than the http session timeout period and then entered your username/password and attempted to login, you would get redirected back to the login page and have to enter it a 2nd time.  This got annoying so to fix this we added code to our custom exception handler to catch the ViewExpiredException.  A custom exception handler will catch all uncaught exceptions and is useful for displaying a generic "Something went wrong" page.    For information on how to set up a custom Exception handler see this blog by Frank Nimphius

The relevant part of the code I added to this exception handler is shown below.  It performs these steps
  • Retrieve username & password from request
  • Log user in
  • redirect to home page
 If the user entered an incorrect username/password then redirecting to home page will actually redirect back to the login page because the home page requires authentication and a valid login to access.
if (throwable instanceof ViewExpiredException) {
  ExternalContext extContext = FacesContext.getCurrentInstance().getExternalContext();
  String pathInfo = extContext.getRequestPathInfo();
  if (pathInfo.equals("/login")) {
      String username = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap().get("usernameInput");
      String password = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap().get("passwordInput");
      Login login = new Login();
      String action = login.login(username, password); // login the user even though the http session was expired.


The Login class is our own managed bean that is used by the login page.  It performs a bunch of thing, including creating a login history record among others, but the pertinent thing it does to actually login the user is this line 
                 authSuccess =  ServletAuthentication.login(user, pw, request, response);

 Our login page has a number of hidden fields that also need to be accessed during login and if you need to retrieve extra fields you can retrieve them the same way as shown above for the username and password fields.

Wednesday, October 15, 2014

What is the Entity Cache?

The ADF model layer uses an Entity Cache to manage updates to the database. This feature is one of the more misunderstood areas of the ADF framework because it’s not something you need a real good understanding of to create an ADF application. Here is a simple way to think about how the entity cache works.

If you are familiar with how the database command line works you know that you can insert or update rows in the database and until you commit, no one else accessing the system will see your changes until you commit them.   You can update a row, insert a row, delete a row and then re-query the tables you’ve changed and you will see your changes but no one else will.   This relates to the isolation transactional property of the ACID  (Atomicity, Consistency, Isolation, Durability)    features of modern databases.    You should not use the feature in a web application because you should never post data without immediately following up with a commit in the same request.   Since the request is coming from a web page if you were to post without committing you run the risk of never completing the commit and leaving the table with locked rows.   

In ADF this problem is solved using the Entity Cache.   We can think of the Entity cache as a work area containing pending changes but the changes do not hold a lock in the database.   The cache adds an additional layer between the database session and the pending changes.  These pending changes never get posted to the database until a commit is performed.   In addition when you run a entity based query, after executing the query,  the framework will look in the Entity cache to see if any of the rows returned have pending changes and will merge the pending changes with the results just retrieved.   This simulates the behavior of a post without a commit.   Only the specific user sees their own pending changes but it does this without ever posting the changes so no lock is created on the table (Assuming jbo.locking.mode is set to the default optimistic setting not pessimistic).

The entity cache allows one view object to display uncommitted rows that were updated in another view object (within the same application module) that is using one or more of the same entities.  It also allows changes to be propagated across these view objects without ever communicating with the database.  For example, if the user updates a UI field linked to one view object and somewhere else on the page, a different view object using that same entity is displaying that value,  All you need to do is refresh that other part of the page and you will see the pending change.

Every entity has its own entity cache.    The cache will contain rows to update, rows to add and rows to delete for a single table.    When it is time to commit, the framework simply has to look in the entity caches, post each entity change and then perform a single commit.

Because the rows were not locked, it is possible that another user or process could have updated the same row. The framework will compare the original values that are also stored in the entity cache with the values currently in the database and if they differ you will get an error "JBO-25014: Another user has changed the row with primary key oracle.jbo.Key". Also see yet-another-reason-for-jbo-25014.

There is a lot more to the entity cache but hopeful this simple explanation will help.  For more in-depth information on the Entity Cache See

What Happens at Runtime: When View Objects and Entity Objects Cooperate


Friday, August 29, 2014

Adding a regex QBE validator for numeric columns

I had a table containing a column that was a Number type.  If you tried to filter on a non numeric value a ConverterException would be thrown.   That was fixed by putting a Number converter on the filter input text to allow only numbers.   That worked except now you couldn't use the Query by Example (QBE) Search criteria Operators.  

These are the supported operators

Operator Description
> Greater than
< Less than
>= Greater than or equal to
<= Less than or equal to

You can write a custom QBE adapter to fix this but that seemed like overkill.

To fix this I changed the filter to be an input text and added a  regular expression validator to allow these operators.  The pattern I used is below.   I didn't find a regex example for QBE Numeric filtering and I'm not a regex expert (is anyone?)  so it took a while to get the regex string just right.  Hopefully this will save someone some time.
Here's the pattern
^[ ]*[<>]?([<=]|[>=])?[ ]*[0-9]+[ ]?(?i)([ ]+AND[ ]+[<>]?[ ]*[0-9]*|[ ]+OR[ ]+[<>]?[ ]*[0-9]*)?[ ]*$

<af:column sortProperty="Count" filterable="true"     headerText="Count"  id="c6">
<f:facet name="filter">
<af:inputText  value="#{vs.filterCriteria.Count}" columns="8" id="Count">                                                 
<af:validateRegExp pattern="^[ ]*[<>]?([<=]|[>=])?[ ]*[0-9]+[ ]?(?i)([ ]+AND[ ]+[<>]?[ ]*[0-9]*|[ ]+OR[ ]+[<>]?[ ]*[0-9]*)?[ ]*$" 
                  messageDetailNoMatch="Please Enter Number Only"/>
<af:outputText value="#{row.Count}"    id="ot7" />      

Examples of Things you can put in the filter
> 10
>10  AND <  50
> 10 and < 50
10 or 50

 If the column is a String type (even through it might contain numbers) you don't have this problem and wouldn't need to add a regex Validator. 

Wednesday, December 18, 2013

How to View Service Proxy Request/Response message in Jdeveloper

In order to view the XML SOAP envelope of an ADF service Proxy, add this line to your server startup and the XML will be dumped the jdeveloper console output. 
  In Jdeveloper, the server startup command line is accessed from

Run Menu
Choose Active Run Configuration
Manage Run Configurations 
Edit Default

Add the line to the end of the Java Options.

 Also if  you see a large work:WorkContext section in the SOAP header, you can remove that by adding this line to the startup.