This sample uses JDev 11g and ORCL DB11g
In this simple scenario I will detail how to auto refresh a pie chart based on the following query. We will then amend the query where clause to exclude a particular department.
--------------------------------------------------------
SELECT sum(Emp.sal) SalaryTotal,
Dept.deptno,
Dept.dname
FROM Emp, Dept
WHERE Emp.deptno = Dept.deptno
GROUP BY Dept.Dname, Dept.Deptno
--------------------------------------------------------
Pre-requisite on the DB Side
in SQLPLUS --> conn system/manager
grant change notification to scott;
Step 1 - Creating the Model
Create a new Fusion Web Application (ADF) in JDev 11g
In the Model Project - Create new ADF Business Components --> Business Components from Tables
Create a connection to SCOTT's schema on your 11g DB and create Entity Objects based on the tables - Dept & Emp
Create the ViewObjects -
Accept the defaults on the following pages.
Create a new ViewObject for the Chart
Enter the following query on the next page-
SELECT sum(Emp.sal) SalaryTotal,
Dept.deptno,
Dept.dname
FROM Emp, Dept
WHERE Emp.deptno = Dept.deptno
GROUP BY Dept.Dname, Dept.Deptno
Accept defaults on the following steps.
At step 7 - select checkbox - Generate View Object Class: SalByDeptViewImpl
At Step 8 - select checkbox to add the new view to the Application Module
For the new view - Set auto-refresh property = true
Copy and paste the following code into the ...impl class you generated earlier
(after the constructor)
long lastRequery = 0;
public long getLastRequery() {
return lastRequery;
}
@Override
protected void bindParametersForCollection(QueryCollection queryCollection,
Object[] object,
PreparedStatement preparedStatement) throws SQLException {
super.bindParametersForCollection(queryCollection, object, preparedStatement);
if (queryCollection != null) {
lastRequery = System.currentTimeMillis();
}
System.out.println("Re-execute "+(queryCollection == null?"count ":"")+"query for VO "+getName());
}
@Override
protected void processDatabaseChangeNotification(QueryCollection queryCollection) {
lastRequery = System.currentTimeMillis();
super.processDatabaseChangeNotification(queryCollection);
}
}
We have just done the following -
We have created a long value lastRequery
We have overridden the following methods –
-bindParametersForCollection
-processDatabaseChangeNotification
The long value represents the last time the query was executed or it has handled a database change notification event. The value of this long has been exposed so the client can reference it as a custom method, see the getLastRequery() method
bindParametersForCollection() and processDatabaseChangeNotification() update this flag.
Expose the getLastRequery() method via the client interface
Step 2 - Creating the UI
Create a new JSF page via the faces-config.xml (graphical mode)
Double-click on the image to create the page, selecting auto-creation of backing bean.
You are now in the visual editor.
-Drag and Drop a Poll control onto the page.
-Drag and Drop SalByDeptView as a Graph --> Pie Chart onto the page
-Drag and Drop getLastRequery()as a method somewhere on the page
-Edit the jspx source and comment out the button you just dropped as we only need it's binding.
-Go to the pagedef file's overview.
-Click on the SalByDeptView iterator binding.
- In the Property Inspector, change the rangeSize to 100
- Edit the page by selecting the graph component in the structure pane
In the Property Inspector set the ContentDelivery flag to "immediate".
Now we need to poll on lastRequery()
So add the following method to the end of the page's backing bean.
public void onPollTimerExpired(PollEvent pollEvent) {
Long lastRequery = (Long)AdfFacesContext.getCurrentInstance().getViewScope().get("lastRequery");
Long lastRequeryFromVO = (Long)BindingContext.getCurrent().getCurrentBindingsEntry().getOperationBinding("getLastRequery").execute();
System.out.println("Timer expired: lastRequery = "+lastRequery+", lastRequeryFromVO = "+lastRequeryFromVO);
if (lastRequery == null (!lastRequery.equals(lastRequeryFromVO))) {
AdfFacesContext.getCurrentInstance().getViewScope().put("lastRequery",lastRequeryFromVO);
AdfFacesContext.getCurrentInstance().addPartialTarget(getPieGraph1());
System.out.println("Data requeried in VO. PPR'ing the pie chart");
}
}
-Select the Poll operation in the structure window
-- Set the Id to pollTimer
-- Select onPollTimerExpired() in the PollListener propety
-- Set the Interval property to 2000
The onPollTimerExpired poll listener event handler invokes a method action binding to get the lastRequery() long value. It stores this value in the view scope. If it notices that the value has changed, then it PPR's the pie chart.
- In the DataBindings.cpx file, for the "AppModuleDataControl" data control usage, set the configuration to use the "AppModuleShared" configuration. This configuration is automatically created for you by ADF design time, along with the "AppModuleLocal" configuration. Auto-refresh view objects only work when they are in a shared AM.
Step 3 - Test
Run the page from the faces-config.xml diagrammer
Open a cmd window and start sqlplus as scott
Insert a new emp with a large salary e.g.
insert into emp values (
8881,
'Jim',
null,
null,
sysdate,
9999,
100,
30);
commit;
The pie chart will be updated automatically.
Now we will amend the WHERE clause to ignore Department 10.
Step 4 - Dynamically Changing the WHERE clause
To avoid getting an ORA-29983
ORA-29983: Unsupported query for Continuous Query Notification
Cause: The query cannot be registered for Continuous Query Notification.
Action: The query has some constructs that make it incompatible with Continous Query Notification like synonyms or views. Please check the documentation for complete list.
we must deactivate the default use of inline views by calling setNestedSelectForFullSql()
in the create() method of the ...viewImpl class.
Open SalByDeptViewImpl.
We must override the create method so Menu --> Source --> Override Methods.
Select the create() method
Add the following after the line super.create();
this.setNestedSelectForFullSql(false);
We will now add a method to amend the WHERE clause to ignore Department 10.
public void excludeDept10(){
ViewObject vo = this.getViewObject();
// get the original query
String query = vo.getQuery();
System.out.println("VO query is = " + query);
// let's find the WHERE clause
int startOfWhere = query.indexOf("WHERE");
int startOfGroupBy = query.indexOf("GROUP BY");
String whereClause = query.substring(startOfWhere, startOfGroupBy);
System.out.println("Original WHERE Clause = " + whereClause);
// amend the WHERE clause to exclude Dept 10
whereClause = whereClause + " and Dept.deptno > 10 ";
String newQuery = query.substring(0, startOfWhere-1);
newQuery = newQuery + whereClause;
System.out.println("New Query with WHERE = "+ newQuery);
newQuery = newQuery + query.substring(startOfGroupBy, query.length());
System.out.println("New Query with Group BY = "+ newQuery);
this.setQuery(newQuery);
vo.executeQuery();
}
I'm sure there's a much more elegant way on doing this --)
- Expose this method (as we did getLastRequery()) via the client interface
- Refresh the DataControl
- Drop the method as an ADF button on the page
- re-test
Press the button to exclude Dept 10
- Enter a new Emp with a large salary
