In ADF applications, people often come across use cases
where row counts are to be shown/used. For example,
- Before or after the table, show the count of number of rows displayed
- Use counts for writing simple UI logic
- Show the page header or footer with aggregated summary information.
In this blog, I show you the different options to achieve
these requirements. Also, I will explain pros and cons of each of these
options. Being an ADF developer, you should understand the concepts before
implementing them to avoid unexpected behavior.
ViewObjectImpl.java has few methods to work with row counts.
getRowCount()
getEstimatedRowCount()
getFetchedRowCount()
getRowCount()
retrieves all the records from View Object by executing View Object’s Query.
The count is calculated by traversing the View Object using next() method until
the last record is retrieved. It does this every time you invoke this method
so, it involves DB trip and consumes memory. If you are working with a large
number of rows, or if your application demands fast response, use
getEstimatedRowCount() to obtain a quicker count as this method hinders the
applications performance.
getEstimatedRowCount()
retrieves the count by hitting getQueryHitCount() method which runs the select
count(*) on the VO Query. For the first time, it queries the DB table using a
select * from table query and store the count in Middle-Tier. After that, it
keeps on adjusting the count within the Middle-Tier whenever row insertions or deletions
are performed on the View Object. This way, it prevents the db trip and is much
faster.
The downside of
this method is it may not be accurate sometimes. Specially, in the scenarios
where there are possibilities of data insertions or deletions from backend by
some other application concurrently.
getFechedRowCount()
provides you the number of rows fetched from Result Set at a given point of
time.
Hopefully this helps to understand the differences between
these methods for row count.
Sometimes, you tend to use above count methods to write
simple UI logic. These operations are expensive and have a negative impact on
performance especially when used for simple existence checks (like count >
0, count == 1 et.c.,). These can be performed by simply looking at the View
Object . For example, to test if the VO has at least one row, we can simply
look at the current row, previous and next rows to see if any of these are not
null. There is no need to execute a potentially expensive SQL query for these
simple checks.
You can write a utility class once and use them in all the
places of your project to verify this. Write them as static methods so you can
use them without instantiating the class. For example,
- to verify if View Object contains no rows
public static boolean containsNoRows(ViewObject
vo) {
if
(vo == null) return true;
RowSet
rs = vo.getRowSet();
if
(rs == null) return true;
return
(rs.getCurrentRow() == null && (!rs.hasNext() &&
!rs.hasPrevious());
}
- to verify if View Object contains at least one row
public static boolean
containsAtleastOneRow(ViewObject vo){
if
(vo == null) return false;
RowSet
rs = vo.getRowSet();
if (rs == null)
return false;
return
(rs.getCurrentRow() != null || rs.hasNext() || rs.hasPrevious());
}
- to verify if View Object contains more than one row
public static boolean containsMoreThanOneRow(ViewObject
vo) {
if
(vo == null) return false;
RowSet
rs = vo.getRowSet();
if(rs
== null) return false;
if(containsNoRows(vo))
return false;
Boolean
containsMoreThanOneRow = false;
RowSetIterator
rowIter = rs.createRowSetIterator(null);
if
(rowIter.hasNext()) {
rowIter.next();
if(rowIter.hasNext())
containsMoreThanOneRow = true;
}
rowIter.closeRowSetIterator();
return
containsMoreThanOneRow;
}
- to verify if View Object contains exactly one row
public static boolean containsExactlyOneRow(ViewObject
vo) {
if(vo
== null) return false;
RowSet
rs = vo.getRowSet();
if(rs
== null) return false;
if(containsNoRows(vo))
return false;
if(containsMoreThanOneRow(vo))
return false;
return
true;
}
Above methods would only work for basic checks. However, if
you have to show the summary information either in the page header or footer,
this wouldn’t work. Again, invoking getRowCount(), getEstimatedRowCount() would
yield performance issues.
For example, for an admin user, you want to show the number
of employees in each department in the page footer. Executing the VO query by
passing different department id and getting the row count is not something that
is suggestible. This will even worse if the VO has large volume of data.
Instead, you can go for SQL based read-only view object just
for this summary information. In-order to demo this, I had built a sample
workspace in JDeveloper 11.1.7.1.0 using the default HR schema in Oracle DB.
Click Create New View Object. Once the Create View Object popup
is displayed, provide Name, chose ‘Read-only access through SQL query’ option
at the bottom and click Next.
Provide the SQL in ‘Query Statement’ box, and click Test
button to validate the SQL. Ensure Query is valid and click Next.
Click Next to skip the Bind Variables section as this doesn’t
require any bind variables. Click Next in Attribute Mappings section to accept
defaults. Click Next in Attributes section to accept defaults. You can modify
the attributes settings as shown below, but I would accept the defaults and
click Next.
Keep clicking Next to
accept defaults and click Finish. Now, add this VO to EmployeeAM as shown
below.
Test this through AM tester to verify the results. Please
refer my other blog to know how to test the Business Logic through AMTester in Jdeveloper.
You can add this data to the UI. This is pretty quick as
this doesn’t fetch all rows from Employee/Departments tables. It just fetches
the count. I have seen a lot of instances where this approach has worked
tremendously. You can indeed follow the same approach to show the rows count
before or after the table.
Thank you for reading through!
No comments:
Post a Comment