Customizing af:query saved search

The below are some of the code pieces useful when customizing or overriding the af:query panel. In addition to that if you have MDS enabled for your ADF application, these saved searches CREATED or UPDATED programmatically will also be persisted to MDS.

Code to access the base query descriptor:
  private QueryDescriptor getBaseQueryDescriptor(){
    QueryDescriptor qdBase = null;
    List<QueryDescriptor> list = getQueryModel().getModel().getSystemQueries();
    for(QueryDescriptor qd : list){
      if(<VIEWCRITERIA_NAME_inVO>.equalsIgnoreCase(qd.getName())){
        System.out.println("qd name:: "+ qd.getName());
        qdBase= qd;
        break;
      }
    }
 
    return qdBase;
  }

Code to access the base viewcriteria from the criteria query:
      private ViewCriteria getViewCriteria(DCBindingContainer bc,
                QueryDescriptor qd) {
               
       Object execBinding =
         bc.findExecutableBinding("<VIEWCRITERIAQUERY_NAME_inPageDef>");   //VIEWCRITERIA_QUERY_NAME as present in the pagedef
      ViewCriteria vc =
       JUSearchBindingCustomizer.getViewCriteria((DCBindingContainer)execBinding, qd.getName());
      return vc;
     

      }

Code to create  a query descriptor from the query model programmatically:
This is same as creating a save search in UI from af:query panel. Of course one needs have MDS enabled for the application so that saved searches will be persisted per user.

private void methodCreateDescriptor(ActionEvent x){
 QueryModel qm = getQueryModel().getModel(); //create a managed bean binding for the af:query
 QueryDescriptor qdBase = getBaseQueryDescriptor(); //get the base query descriptor
  QueryDescriptor newQd =  qm.create(name, qdBase); //creates a new descriptor

      Map<String, Object> uiHints = new HashMap<String, Object>(10);
      uiHints.put(QueryDescriptor.UIHINT_MODE, QueryDescriptor.QueryMode.BASIC);
      uiHints.put((QueryDescriptor.UIHINT_NAME), name);
    //results table af:table                                                                                  
    uiHints.put(QueryDescriptor.UIHINT_RESULTS_COMPONENT_ID, "::pc1:t2");
      uiHints.put(QueryDescriptor.UIHINT_SAVE_RESULTS_LAYOUT, Boolean.TRUE);
      uiHints.put(QueryDescriptor.UIHINT_SHOW_IN_LIST, Boolean.TRUE);
//setDefault and autoExecute are check boxes in UI.
      uiHints.put(QueryDescriptor.UIHINT_DEFAULT, setDefault);
      uiHints.put(QueryDescriptor.UIHINT_AUTO_EXECUTE, autoExecute);

   
      newQd.getUIHints().clear(); //Just to make sure the MODE is set properly.
      newQd.getUIHints().putAll(uiHints);

      /**
       * As the new   QD is already created by calling qm.create(name, qdBase)
       * Now raising UPDATE event to properly update the query hints and mode.
       */
      QueryOperationEvent event_mode_change = new QueryOperationEvent(this.getQueryModel(),QueryOperationEvent.Operation.UPDATE, newQd, uiHints);
      invokeMethodExpression("#{bindings.AuditViewCriteriaQuery.processQueryOperation}", Object.class,
                             new Class[]{QueryOperationEvent.class}, new Object[]{event_mode_change});
      qm.setCurrentDescriptor(newQd); //If you want to set the new QD as current descriptor
//Do the above step only after invoking the above UPDATE event, otherwise the mode will not be set properly.
}

Override queryListener of af:query

public void processCustomQuery(QueryEvent queryEvent) {
//Do you code here
  invokeMethodExpression("#{bindings.AuditViewCriteriaQuery.processQuery}", Object.class,

                             new Class[]{QueryEvent.class}, new Object[]{queryEvent});
}

Override queryOperationListener of af:query
 public void processCustomQueryOperation(QueryOperationEvent queryOperationEvent) {
QueryDescriptor qd = queryOperationEvent.getDescriptor();
//Your code here
    invokeMethodExpression("#{bindings.AuditViewCriteriaQuery.processQueryOperation}", Object.class,
                           new Class[]{QueryOperationEvent.class}, new Object[]{queryOperationEvent});
}
 
invokeMethodExpression method
  public Object invokeMethodExpression(String expr, Class returnType, Class[]  argClasses, Object[] arguments){
        FacesContext fc = FacesContext.getCurrentInstance();
        ELContext elc = fc.getELContext();
        ExpressionFactory exprFactory = fc.getApplication().getExpressionFactory();
        MethodExpression methodExpr = exprFactory.createMethodExpression(elc,expr,returnType,argClasses);  
        return methodExpr.invoke(elc,arguments);
       }

Note: Code base is on 11.1.1.7

Displaying all the rows of a DB table, in two ADF tables side by side without scrolling of the tables

Here is a requirement to display all the rows of a database table (around 50 rows) in an ADF table without the scrolling of the table in the UI. We can achieve this functionality by rendering the data in two different ADF tables side by side, the first table renders the first half of the rows and the second table renders the remaining half.

I take an example data to display 52 weeks of a sales table, the first table rendering the 26 weeks and second one rendering the remaining half.

As a best practice, fetch all the records at one go from database using a single ViewObject query and build two different RowSets holding the two sets of data 26 rows each.

Set the Fetch_Mode as all to fetch all the rows at once.

Create two View Criteria's to filter the ViewObject rows in to two sets with 26 rows each.

Create two public methods in ViewObjectImpl of the VO, returning the respective RowSets with 26 rows each. And also expose these public method as client interfaces.

With the above steps we are done with the model and we will start building UI for rendering these two RowSets as ADF tables.

Create two method iterators in the executable section of the ADF page definition as below.

Create two method actions from those exposed public interface methods of the ViewObjectImpl.

Now we are ready to create ADF tables in UI from these RowSets being returned from the above method actions but if we drop the 'return' of the public interface methods, we would not get the proper Tree binding created in PageDef as we would have got when we drop a ViewObject.

So for that reason, first the same ViewObject as ADF table twice in the UI (may be inside a ShowDetailHeader layout or what ever is your layout) for two tables side by side.

Once you get the Tree binding created with all the 'AttrNames' required, just have to change the IteratorBinding property to point to the respective Method Iterator we created earlier. Do the same for both the Tree bindings and we are done with UI development too.

Finally the UI page looks like this,

Hope it helps.

Teradata datasource creation in weblogic server

If you have a requirement to create a web application talking to Teradata DB and deploy the application to Weblogic server then you need a generic datasource pointing to the Teradata DB and jndi name for the DS.

I have faced similar requirement to create an ADF application talking to Teradata DB  and I created the DS created using the following steps. I am able to successful use the JNDI corresponding to the datasource in ADF model Applciation Module configurations.

The below are the instructions to create a datasource.

1. Make sure Teradata jdbc libraries terajdbc4.jar and tdgssconfig.jar are present in the weblogic server classpath. You can download these libraries from Teradata forums.
• Copy the jars terajdbc4.jar and tdgssconfig.jar to WLS installation directory <WLS_HOME>\server\lib

•  Add these two jars to the WEBLOGIC_CLASSPATH variable in commEnv.sh configuration file. 
• File location: in the WLS installation directory <WL_HOME>\common\bin\commEnv.sh
• Add classpath: ${CLASSPATHSEP}${WL_HOME}/server/lib/terajdbc4.jar${CLASSPATHSEP}${WL_HOME}/server/lib/tdgssconfig.jar
• Note: Here the CLASSPATHSEP is a delimiter
1. 
   
2.         Login to WLS console and navigate to Service à Data Sources
3. New à Generic Data Source
• Name: teradataDS
• JNDI Name:  jdbc/teradataDS
• Database Type: Other
4. Next à Next à Uncheck ‘Supports Global Transactions’ à Next 
• Database User Name:  tera_username 
• Password: *****
5.  Nextà
• Driver Class Name: com.teradata.jdbc.TeraDriver
• Connection URL: jdbc:teradata://<database host >
• Properties: user=tera_username
6. Next à
• Select Targets: managedserver_1
7. Save the changes and bounce the servers if required.

Hope it is useful.

Reference: http://developer.teradata.com/doc/connectivity/jdbc/reference/current/WebLogic103.html

Jdeveloper IDE Connection to the Teradata database

If you want to create an IDE connection in Jdeveloper IDE and pointing to Teradata database, the below are the steps.

1. Download the latest jdbc drivers from Teradata website https://downloads.teradata.com/download/connectivity/jdbc-driver
2. Open the menu option Application ---> Default Project Properties in Jdev
3. Libraries and Classpath tab ---> click on Add Library --> New
4. Give a name 'Teradatalibs' to the library and select Classpath, then click on Add Entry and choose both the jars terajdbc4.jar and tdgssconfig.jar from the earlier downloaded zip. So far we have created a new project library with jdbc drivers required for making a connection to the Teradata DB.
5. Navigate to IDE Database Navigator and right click IDE connection and + icon there.
6. Click New for Driver class, give the class name as com.teradata.jdbc.TeraDriver and browse for the library 'Teradatalibs' created in the step 4.
7. Give User name & password,  and need to enter the JDBC url something like jdbc:teradata://<DB HOST NAME>
8. Test for connection, it will be success if the details entered are correct.

Screen shot below.

Hope it helps.

af:SelectManyChoice component to display the child ViewObject rows as list of items

Here is a requirement to render the child viewobject rows as list of values in a multi selection component like af:selectManyChoice. This is not similar to the selectManyChoice what we usually build for af:query component. Here we are going to enable the multiselection for an LOV in an input form and that multi select component renders the associated child rows as selected values and also, selecting an item from the LOV will create a new row in the child table. The objectives of the experiment are,

1. Enabling multiselection for LOV attribute of a view object.
2. Render the child viewobject rows as selected items in a selectManyChoice component
3. Selecting an item from selectManyChoice LOV, will create a new row in the child table. 

Here I am taking HR schema for building this sample application. The use case is that user may want to mark the employee as a manager to one or more departments which are selected from a departments multi select LOV, in an employee create/update ADF input form. The component 'Mgr To Department' in UI is a selectManyChoice listing all the departments from the Department table (in fact the LOV list can be from any base table or a static LOV, in real world scenarios). The below is an example screen shot and it says that the employee Id 1102 is a manager to two departments with id's 130 and 140.

User can select any number of departments from LOV and save it. You can follow the below steps for building this,

Enabling multi selection for an LOV attribute: 

Declaratively we can not build a multi selection for an LOV attribute, here we need a tweak a bit in source of a VO. The idea is first to create an ViewObject (for LOV) and expose it as a viewaccessor in the parent viewobject (with which you are going to build input form). Now create a EO based transient attribute(create it in EO and then add it to VO), and build LOV for it using the viewaccessor just exposed. As I mentioned, we need to convert this LOV to multi selection enabled, so please go the source of the viewobject do as below

• change the value attribute of CONTROLTYPE to 'delimited_ids_choice'. 
• Add Delimiter="," to the list binding of that LOV.

Transient attribute 'MgrToDepartment' at EO level:

 <Attribute
    Name="MgrToDepartment"
    Precision="255"
    ColumnName="MGR_TO_DEPARTMENT"
    SQLType="VARCHAR"
    Type="java.lang.String"
    ColumnType="VARCHAR2"
    IsQueriable="false"
    IsPersistent="false"/>

Transient attribute 'MgrToDepartment' at VO level: 

<ViewAttribute
    Name="MgrToDepartment"
    IsQueriable="false"
    PrecisionRule="true"
    EntityAttrName="MgrToDepartment"
    EntityUsage="Employees"
    LOVName="LOV_MgrToDepartment"    
    AliasName="MGR_TO_DEPARTMENT"
    Expression="MGR_TO_DEPARTMENT">
    <Properties>
      <SchemaBasedProperties>
        <DISPLAYWIDTH
          Value="40"/>
        <CONTROLTYPE
          Value="delimited_ids_choice"/>
      </SchemaBasedProperties>
    </Properties>
  </ViewAttribute>

List Binding of the LOV: 
<ListBinding
    Name="LOV_MgrToDepartment"
    ListVOName="DepartmentsLOV"
    ListRangeSize="-1"
    Delimiter=","
    NullValueFlag="none"
    NullValueId="LOV_MgrToDepartment_LOVUIHints_NullValueId"
    MRUCount="0">

Also make sure that 'IsSelected=false' is not present for the transient attribute at the VO level.
Now the LOV is ready in the model layer and need to drop it as a multi select component in the UI. You can drag and drop the view object instance as an input form in UI page and now you need to change the default LOV component generated by IDE to a selectManyChoice component. The code looks as below,  

Now you may have noticed that I used backingbean logic for 'value' expression. Actually I tried with different expressions on pagedef list binding 'MgrToDepartment' but I couldn't get the desired of result of passing comma separated values (eg: 130,140) of the selected departments to the model layer. And the below logic in managed bean worked form me,   

With this, you should be able to pass the selected departments as comma separated values to model layer, and I will discuss about accessing these values in the model layer for saving the selected department records to the Departments table with the manager id as the current employee id from the Employee table, later in this post.

Render the child viewobject rows as selected items in a selectManyChoice component:

As you know selectManyChoice here, sends/reads the selected values as comma separated values to/from model layer, so for rendering the list of departments as selected values (i.e the deparment id's for which the current employee is a manager) in the LOV we need to change the Employee view object query so that it reads the list of departments as comma separated values. The below is the snap shot of employee VO query after change,

Here I am reading the list of departments for which an employee is a manager, to the transient attribute 'Mgr_To_Department' and whenever the query is execute on VO, then the transient attribute will have the departments as comma separated values. Now the UI has this transient attribute as select many LOV and we should see these departments as, selected values in the selectManyChoice LOV when the UI renders.

Selecting an item in selectManyChoice list items, will create a new row in the child viewobject:

Now lets go back to the case when an user select a department id from the LOV and clicks on commit, then the selected department (in the Department table) should get their manager id set to the current employee id. So to make that happen we need to write the logic accordingly, making use of entity associations between Department and Employee table.

As we know we are going to get the comma separated values for the transient attribute 'Mgr_To_Department', need to manipulate that list in the doDML() method of the entity, so first enable the Entity Impl class for Employees entity and override the doDML() method as below. 

In the doDML(), for the respective state of the entity, we can write our own logic of creating child records using the entity accessors. In this example I didn't complete the whole flow of setting the manager id of the selected departments as the current row employee id.

You can download the example application from the following link. SelectManyChoice.zip

References:
Thanks to Jobinesh blog where I picked the initial stuff of enabling the multi selection for an LOV.
Declaratively-enabling-multiple-value.html