Skip to main content

ADF 11g : Change a Viewobjects' Query on the Fly

One of the requirements at my current project is to have one ADF table display data from different database tables. That is, depending on criteria entered by the user, the query behind the view object needs to change. All in all 12 different database tables are involved in this story. This requirement is based on functionally in the original (oracle forms) application. This forms application used the set_block_property built-in:
 set_block_property('<blockName>, query_data_source_name, <datasource>);  

I was able to reproduce this behavior in an ADF application. In this post I explain how I did this.

Database and ADF Business Components.
Luckily these tables all have the same attributes so it is relatively easy to achieve this. I created two database views. One based on the employees table, and a second one based on the locations table. Mark that I defined identical column aliases for both views.
 create view EMP_ABSTRACT_VW as  
select
EMPLOYEE_ID IDENTIFIER,
FIRST_NAME DESCRIPTION
from employees

 create view LOC_ABSTRACT_VW as  
select
location_id IDENTIFIER,
city DESCRIPTION
from LOCATIONS

Next I created an ADF Business Components viewObject based on one of the database views. I called this viewObject 'dataprovider'.
 select  
IDENTIFIER objId
,DESCRIPTION objDesc
from emp_abstract_vw


ADF Faces Page.

To display the data on a page I just created a simple page and dropped the collection from the datacontrol pallet onto the page as an ADF Table component. When running the page, you see data from the emp_abstract_vw as expected.

Preparing the Query Change Method.

In order to change the query I have to create a method on the application module. This method gets a handle to the viewObject, sets some properties, changes the query and executes it. Ok, not that fast. In the codefragment below you see one line where I use setFullSqlMode. Lets explain what FULLSQL_MODE_AUGMENTATION does.

First I apply the new programmatic query by calling setQuery() and execute the query. If I need to call setWhereClause or if a user changes criteria the query, the ADF Business Components framework augments the whereClause on the programmatic query. If you don't use FULLSQL_MODE_AUGMENTATION any changes by setWhereClause or change of query criteria are applied to the original query (that is the query that was defined design time).
1:   static String baseQuery = "select IDENTIFIER objId, DESCRIPTION objDesc from ";  
2: public void changeBaseTable(String baseTable){
3: String query = baseQuery + baseTable;
4: ViewObjectImpl voi = getdataProvider();
5: voi.setFullSqlMode(voi.FULLSQL_MODE_AUGMENTATION);
6: voi.setQuery(query);
7: voi.executeQuery();
8: }

Next step is to publish this method so I can use it on my page. I drop the method from the datacontrol, resulting in a methodAction in my bindingcontainer.
1:   <methodAction id="changeBaseTable" RequiresUpdateModel="true"  
2: Action="invokeMethod" MethodName="changeBaseTable"
3: IsViewObjectMethod="false" DataControl="AppModuleDataControl"
4: InstanceName="AppModuleDataControl.dataProvider">
5: <NamedData NDName="baseTable" NDType="java.lang.String"/>
6: </methodAction>

Final step is the implementation of a selectOneChoice with a value change listener to change the basetable for the query. The selected value corresponds to the name of one of the database views, and will be directly applied to the viewObjects' query.
1:  <af:selectOneChoice label="Basetable...&gt;&gt;&gt;  " id="soc1"  
2: value="#{pageFlowScope.SwitchIterator.baseTable}"
3: autoSubmit="true"
4: valueChangeListener="#{pageFlowScope.SwitchIterator.switchBaseTable}">
5: <af:selectItem label="Employees" value="EMP_ABSTRACT_VW"
6: id="si1"/>
7: <af:selectItem label="Locations" value="LOC_ABSTRACT_VW"
8: id="si2"/>
9: </af:selectOneChoice>

The valueChangeListener executes the methodBinding, thus changing the query.
  public void switchBaseTable(ValueChangeEvent valueChangeEvent) {  
setSource(valueChangeEvent.getNewValue().toString());
}
public void setSource(String baseTable) {
// Add event code here...
DCBindingContainer bc = (DCBindingContainer)BindingContext.getCurrent().getCurrentBindingsEntry();
OperationBinding oper = bc.getOperationBinding("changeBaseTable");
oper.getParamsMap().put("baseTable", baseTable );
oper.execute();
}

Now when you start the application you will see the table displaying all employees.
This is the default query behind the viewobject.


By changing the value of the listbox, you will also change the viewobjects' query, resulting in locations data being displayed in the table.




This post was originally posted at the AMIS Technology Blog

Comments

Popular posts from this blog

ADF 12.1.3 : Implementing Default Table Filter Values

In one of my projects I ran into a requirement where the end user needs to be presented with default values in the table filters. This sounds like it is a common requirement, which is easy to implement. However it proved to be not so common, as it is not in the documentation nor are there any Blogpost to be found that talk about this feature. In this blogpost I describe how to implement this.

The Use Case Explained
Users of the application would typically enter today's date in a table filter in order to get all data that is valid for today. They do this each and every time. In order to facilitate them I want to have the table filter pre-filled with today's date (at the moment of writing July 31st 2015).


So whenever the page is displayed, it should display 'today' in the table filter and execute the query accordingly. The problem is to get the value in the filter without the user typing it. Lets first take a look at how the ADF Search and Filters are implemented by the f…

ADF 12.2.x : Conditional Showing Message Instead of List of Values Popup

Today I had to fix an issue in one of our ADF applications. For those of you working with ADF (as I do too occasionally) this might be valuable, so I decided to share this. The application is based on an old Oracle Forms applications and I had to implement the following functionality:

FRM-41830 : List of Values contains no entries.

In other words (more ADF like) : If you are about to render a List of Values and that List of Values contains no rows, just show a message instead of showing the List of Values.

In this post I will describe how I was able to implement this although it turned out to be a challenge.
Below are the steps that I took. Note that the Application can be downloaded from GIT.

1) I use a UI pattern where we use readonly table and do edits in a popup. That means that any List of Values will add an additional popup to the UI.



2) The List of Values that I use in this sample only shows entries where the minimum salary is less then the employees current salary. Not that …

ADF 11g Quicky 3 : Adding Error, Info and Warning messages

How can we add a message programatically ? Last week I got this question for the second time in a months time. I decided to write a short blogpost on how this works.

Adding messages is very easy, you just need to know how it works.
You can add a message to your faces context by creating a new FacesMessage. Set the severity (ERROR, WARNING, INFO or FATAL ), set the message text, and if nessecary a message detail. The fragment below shows the code for an ERROR message.

1: public void setMessagesErr(ActionEvent actionEvent) {
2: String msg = "This is a message";
3: AdfFacesContext adfFacesContext = null;
4: adfFacesContext = AdfFacesContext.getCurrentInstance();
5: FacesContext ctx = FacesContext.getCurrentInstance();
6: FacesMessage fm =
7: new FacesMessage(FacesMessage.SEVERITY_ERROR, msg, "");
8: ctx.addMessage(null, fm);
9: }


I created a simple page with a couple of buttons to show the result of setting the message. When the butto…