Tuesday Jul 17, 2012

Interpret af:query's queryEvent and display popup to end user using QueryListener

Found an interesting question on OTN. Based on the question, wired a usecase to try out.

Usecase : Show a warning to user when they try to search the records (af:query component), without specifying a criteria / a wild card "%". I.e, when the user tries to query the entire table, show a warning that querying all the records would take some time.

There are three phases in implementing this usecase.

1. Interpret the query event and get the query criteria.
2. Show the popup.
3. Process the interpreted query based on the outcome of the popup.

Before proceeding with the implementation, we'll create a page for assumption.
a. Page contains a af:query component with a resultant table / read-only table.
b. Has a popup to be shown to the end user.
c. Bound to a bean.

We'll now implement it phase by phase.

First of all, we'll create couple of attributes in the bean and generate accessors to them.
    private RichTable empTable;
    private boolean warnUser=true;
   // Set the default queryListener property value of the af:query component
   // for mexpr.
   private String mexpr = "#{bindings.ImplicitViewCriteriaQuery.processQuery}"; 
    private QueryEvent qEvt;

    public void setEmpTable(RichTable empTable) {
        this.empTable = empTable;
    }

    public RichTable getEmpTable() {
        return empTable;
    }



Now, we need to trap the query event of the af:query component to perform the desired task. We can add a queryListener and bind it to the af:query component

  public void processQuery(QueryEvent queryEvent) {
        // store the queryEvent in a bean attribute, to be used in another method.
        qEvt = queryEvent;
        // Reset the flag. This flag would be used to check if the system has to 
        //raise the popup or not
        warnUser=false;
        DCBindingContainer bc =   
        (DCBindingContainer)BindingContext.getCurrent().getCurrentBindingsEntry();   
         // Get the view criteria that would be applied.
         // findExecutableBinding method takes two parameters.
         // id of the searchRegion executable binding
         // criteria for the searchRegion executable binding
         // Ex : <searchRegion Criteria="__ImplicitViewCriteria__"      
         // Customizer="oracle.jbo.uicli.binding.JUSearchBindingCustomizer"
         //         Binds="EmpView1Iterator" id="ImplicitViewCriteriaQuery"/>
       


 ViewCriteria vc = 
JUSearchBindingCustomizer.getViewCriteria((DCBindingContainer)bc.findExecutableBinding("ImplicitViewCriteriaQuery"),"__ImplicitViewCriteria__");


        ViewCriteriaRow vcr = (ViewCriteriaRow)vc.get(0);
        // Some logic to set the flag. Here, checking if the Ename attribute has
        // no value specified / used a wildcard expression ("%").
        for(int i=0;i<vcr.getAttributeNames().length;i++) {
           
 if(vcr.getAttributeNames()[i] == "Ename" &&  
("%".equals(vcr.getAttributeValues()[i]) || 
vcr.getAttributeValues()[i]==null)) 
                warnUser=true;
        }
 
        if(warnUser) 
            showPopup();
        else 
            executeQuery();
 
    }



showPopup and executeQuery are custom methods to show the popup and to process the query respectively.

       public void showPopup(){
        UIViewRoot root = FacesContext.getCurrentInstance().getViewRoot();
        RichPopup popup = (RichPopup) root.findComponent("p1");
        RichPopup.PopupHints hints = new RichPopup.PopupHints();
        popup.show(hints);
    }

  // This method invokes the method expression used by af:query component programatically
    public void executeQuery(){
        processMethodExpression(mexpr, new Object[] {qEvt}, new Class[] {QueryEvent.class}); 
        AdfFacesContext adfFacesContext = AdfFacesContext.getCurrentInstance(); 
        adfFacesContext.addPartialTarget(empTable); 
 
    }

    private Object processMethodExpression(String methodExpression, Object[] parameters, Class[] expectedParamTypes) { 
        FacesContext fctx = FacesContext.getCurrentInstance(); 
        ELContext elctx = fctx.getELContext(); 
        Application app = fctx.getApplication(); 
        ExpressionFactory exprFactory = app.getExpressionFactory(); 
       
 MethodExpression methodExpr = exprFactory.createMethodExpression(elctx,
 methodExpression, Object.class, expectedParamTypes); 
        return methodExpr.invoke(elctx, parameters); 
        }


Now, we need to bind the custom querListener created above to the af:query component

  <af:query id="qryId2" headerText="Search" disclosed="true"
      value="#{bindings.ImplicitViewCriteriaQuery.queryDescriptor}"
      model="#{bindings.ImplicitViewCriteriaQuery.queryModel}"
      queryListener="#{viewScope.QueryBean.processQuery}".....



We are almost there. Now, when we run the page and query for the records by keeping Ename as null (in the query panel), we would get the popup.

Final step is to handle the user action on the popup and then proceed executing the query / to stop it.

For this, we'll create a dialog listener and bind it to popup.

 public void onDialog(DialogEvent dialogEvent) {
        Outcome o = dialogEvent.getOutcome();
        if(o == Outcome.yes) 
            executeQuery();
 
    }

                <af:popup childCreation="deferred" autoCancel="disabled" id="p1">
                    <af:dialog id="d2" type="yesNo" title="Are you sure?"
                               dialogListener="#{viewScope.QueryBean.onDialog}">
                       
 <af:outputText value="It would be time consuming to query for all 
records. Are you sure you want to continue?" id="ot9"/>
                        <f:facet name="buttonBar"/>
                    </af:dialog>
                </af:popup>



Here is how the runtime would be.

step1.jpg



Enter % for Ename and hit the Search button

step2.jpg



Popup with a warning message displayed

step3.jpg


Clicking on Yes / No on the popup performs respective task (perform query / cancel query).

step4.jpg



Thursday Jul 12, 2012

Calculating time since last query in adf using java script

In some scenarios, we would need to calculate the time elapsed since the last query execution, without contacting the server (i.e without using af:poll component). This blog is to give an example for this scenario by using javascript.

OTN question : https://forums.oracle.com/forums/thread.jspa?forumID=83&threadID=2411997

Assuming that we have an application with a table in the page, and a commandButton to query the records, we will proceed further to build our usecase.

The logical flow of this implementation would be like,
1. Execute the query
2. When the button clicked, reset the previous time
3. Set the time to 0.
4. Trigger the timer to increment time.

Now, implementation comes.

Lets add an output text to display the time lapsed

<af:outputLabel value="Minutes since last query :" id="ol1"/>
        <af:outputText value="" id="ot9" clientComponent="true"/>

Since we would be accessing the output text from javascript, we would need to make it available to client by setting clientComponent to true.

We now need to add a javascript code to our page, to count the minutes and display in the output text.

        <af:resource type="javascript">

        function updateTimer(){
            var opTxt = AdfPage.PAGE.findComponentByAbsoluteId('ot9');
            var curMins = opTxt.getProperty('value');
            if(curMins == '' || curMins == null)
                opTxt.setProperty('value','0');               
            else
                opTxt.setProperty('value',parseInt(opTxt.getProperty('value')) + 1);
        }

        </af:resource>

In the above code, we find the outputText component (with id ot9), get its value. If the value is blank or null, then set it to 0. If it is a non-zero number, then increment it.

Now, we need to call this method every minute to update the output text. For this, we will add another JS function.

        function startTimer(){
            var opTxt = AdfPage.PAGE.findComponentByAbsoluteId('ot9');
            opTxt.setProperty('value','');
            updateTimer();
            var int=window.setInterval("updateTimer()",60000);
        }

This function finds the outputText (with id ot9), resets its value (i.e set it to blank) and then schedules the function we've created first (updateTimer()) to be called every minute (i.e 60000 milliseconds).

Assuming we have a button to execute the query, we'll add a client listener to the button to execute this function when pressed.

                <af:commandButton actionListener="#{bindings.Execute.execute}" text="Execute"                                       
                                        disabled="#{!bindings.Execute.enabled}"  id="cb1" partialSubmit="true">
                          <af:clientListener method="startTimer" type="action" />
                </af:commandButton>

Ouput would be like below.


Wednesday Feb 08, 2012

Passing comma separated string as bind variable for VO query's IN operator

Quite often, we want to pass a parameter to the bind variable in the VO's query with a comma separated value, for the where clause with an IN operator. However, normal SQL query that the VO contain interpret that whole comma separated value as a single String and our usecase fail to get fulfilled.

Ex. question in OTN thread : https://forums.oracle.com/forums/thread.jspa?messageID=10125366

To overcome this, Mohammad Jabr has written a blog entry with the help of  Steve Muench's example #126. This has been achieved by using a TYPE and CASTing it to get the list.

In this article, we'll see another option to alter the query using regexp_substr, without having to use a TYPE and CAST.

Let us take an example of a VO created using EMP table with the following query.

SELECT Emp.EMPNO, 
       Emp.ENAME, 
       Emp.JOB, 
       Emp.MGR, 
       Emp.HIREDATE, 
       Emp.SAL, 
       Emp.COMM, 
       Emp.DEPTNO
FROM EMP Emp

EmpViewDefault.jpg


We'll add a where clause to the VO's query with a bind variable that takes comma separated string as input. Here, we would be including regexp_substr function (Oracle DB >=10g), to split the comma separated string and return them as rows.

Now, our Where clause of the VO's query would be looking like

WHERE Emp.ENAME in 
  (select regexp_substr(:Bind_Ename_Comma_Sep_List,'[^,]+', 1, level) 
   from dual 
    connect by 
        regexp_substr(:Bind_Ename_Comma_Sep_List, '[^,]+', 1, level) 
            is not null)
Also, we'll add a Bind Variable Bind_Ename_Comma_Sep_List of String data type. Once after modifying the Where Cluase and after adding the bind variable, our VO would be looking like

EmpView.jpg


To validate our query, let us run the AM tester to check the result

AMTester.jpg


Validate the result by entering a comma separated ENAME list to the bind variable (SMITH,ALLEN,JONES).


AMTesterBindVar.jpg


Check out the query result

AMTesterQueryRes.jpg


As it can be seen, we've passed 3 comma separated Enames to the bind variable, which in turn fetched only those records with the matching 3 Enames.
About

Tips & Tricks from Arun on JDev ADF, Forms, SQL & PL/SQL.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today