Understanding VO's method getQueryHitCount

Recently I was asked about getQueryHitCount method of the ViewObjectImpl class. The method is commonly used in various implementations of programmatically populated view object. JDeveloper generates this method in VO's implementation class and it's supposed to be overridden with some custom implementation returning an estimated row count of the VO's rowset. The developer was playing with  getQueryHitCount method returning different fixed values. And sometimes it worked for him, but sometimes it didn't. So, sometimes a table component rendered exact count of rows that he needed, but sometimes the returning value of getQueryHitCount didn't matter at all. The table, no matter what, rendered wrong number of rows and it didn't take into account the value of getQueryHitCount. So, I was asked whether I could explain this behavior.
The secret is that the framework, in some cases, doesn't execute getQueryHitCount method at all. A table component renders its rows by portions in a set of subsequent requests. The size of a portion depends on the RangeSize attribute of an iterator bindings. The default value is 25. When a table is rendering a portion of rows it is asking a view object about estimated row count. And if the view object has fetched all its rows it's going just to return the number of rows in its query collection and  getQueryHitCount is not going to be invoked. In other words if iterator's range size is greater than total number of rows of the VO, then getQueryHitCount will not be taken into account and this total set of rows will be rendered by the table.
But if the fetching is not complete and the VO is being asked about an estimated row count, the VO is really trying to estimate the row count by invoking getQueryHitCount method. The default implementation of the method generates a SQL query like "select count(*) ..." in order to force the database to evaluate the exact value. For programmatically populated view objects we have to create our custom implementation of the method depending on the data source we use. And sometimes this issue is getting quite complicated. On the other hand we can just return -1 in getQueryHitCount. The question is - what is better, to evaluate a real number of rows or just return -1? In most cases the difference is only in table rendering, actually in scroller rendering. When a table knows a real number of rows in the collection it renders its scroller at the very beginning matching to this number by size and position. Otherwise size and position of the scroller are going to be evaluated according to the number of rows fetched by the table and they're going to be reevaluated each time whenever number of fetched rows is growing up. So the scroller is going to get smaller while a user is scrolling the table and fetching more and more rows.
That's it!

Working with PL/SQL procedures in ADF BC Model

I believe this is very common use case when we need to invoke some PL/SQL procedure in our Java code in ADF BC model. The standard approach for that is to use JDBC callable statement. For example, we have a PL/SQL procedure:

create or replace procedure somePLSQLProcedure
(aStringParam Varchar2,
 aIntegerParam Number,
 aDateParam Date,
 aOutParam out Varchar2
 )

And Java method invoking this procedure using JDBC callable statement is going to look like this:

public String execPLSQLProcedure(String aStringParam, Integer aIntParam,
                                 Date aDateParam) {
    DBTransactionImpl dbti = (DBTransactionImpl)getDBTransaction();
    CallableStatement statement =
      dbti.createCallableStatement(("BEGIN "+"somePLSQLProcedure(?,?,?,?);" +
                                    "END;"), 0);
    try {
        statement.setString(1, aStringParam);
        statement.setInt(2, aIntParam);
        statement.setDate(3, aDateParam);
        statement.registerOutParameter(4, Types.VARCHAR);
        statement.execute();
        return statement.getString(4);
    } catch (SQLException sqlerr) {
        throw new JboException(sqlerr);
    } finally {
        try {
            if (statement != null) {
                statement.close();
            }
        } catch (SQLException closeerr) {
            throw new JboException(closeerr);
        }
    }
}

Don't you think this piece of code is a little bit ugly? It is doing a prosaic thing - invoking an extremely simple PL/SQL procedure and it is so complicated. Moreover, there is no any control as for number and types of the procedure's parameters. So, writing this code each time whenever I need to invoke a PL/SQL procedure gets me a bit annoyed, and I developed some set of helper classes and interfaces in an effort to make it much easier. For example, a method invoking the same PL/SQL procedure should look like this:

public String execSmartPLSQLProcedure(String aStringParam,
                                      Integer aIntParam,
                                      Date aDateParam) {
    IStoredProc proc =
      StoredProc.getStoredProc(getDBTransaction(), "somePLSQLProcedure");
    proc.setStringValue("aStringParam", aStringParam);
    proc.setIntegerValue("aIntegerParam", aIntParam);
    proc.setDateValue("aDateParam", aDateParam);
    proc.execute();
    return proc.getStringValue("aOutParam");
}

Much better, isn't it?
Let's consider an example of invoking of a PL/SQL function. We have a PL/SQL function:

create or replace function somePLSQLFunction
(aStringParam Varchar2,
 aIntegerParam Number,
 aDateParam Date
 ) return Varchar2

And a Java method invoking this function is very simple:

public String execSmartPLSQLFunction (String aStringParam,
                                      Integer aIntParam,
                                      Date aDateParam) {
    IStoredProc proc =
      StoredProc.getStoredProc(getDBTransaction(), "somePLSQLFunction");
    proc.setStringValue("aStringParam", aStringParam);
    proc.setIntegerValue("aIntegerParam", aIntParam);
    proc.setDateValue("aDateParam", aDateParam);
    proc.execute();
    return proc.getStringValue("RESULT");
}

And what about working with complex SQL types? For example we have some SQL type representing an array of Varchar2:

CREATE OR REPLACE TYPE varchar2_array_type AS TABLE OF VARCHAR2(2000)

And our PL/SQL procedure accepts a parameter of this type:

create or replace procedure somePLSQLProcedure
(aStringParam Varchar2,
 aIntegerParam Number,
 aDateParam Date,
 aArrayParam varchar2_array_type,
 aOutParam out Varchar2
 )

So our Java code should look like this:

public String execSmartPLSQLProcedure(String aStringParam,
                                      Integer aIntParam,                                          
                                      oracle.jbo.domain.Date aDateParam,
                                      ArrayList<String> aArrayParam) {
    IStoredProc proc =
      StoredProc.getStoredProc(getDBTransaction(), "somePLSQLProcedure");
    proc.setStringValue("aStringParam", aStringParam);
    proc.setIntegerValue("aIntegerParam", aIntParam);
    proc.setDateValue("aDateParam", aDateParam);
    proc.setArrayValue("aArrayParam", aArrayParam, "VARCHAR2_ARRAY_TYPE");
    proc.execute();
    return proc.getStringValue("aOutParam");
}

One of the principal features of this approach is that you know all necessary information about the procedure's parameters right after the execution of StoredProc.getStoredProc(...) . So you have the full list of parameters containing their names and data types. In order to retrieve this information from the database  StoredProc class uses DatabaseMetaData method getProcedureColumns. This technique is described in details in this post.
The list of parameters allows us to control their names and types actually before execution of the procedure. Furthermore, we can assume that names of attributes of our Entity and names of parameters of PL/SQL procedures (inserting, updating and deleting the data) are equal and we can implement some method in an EntityImpl class performing DML operations:

private void executeDMLprocedure(String procedureName) {
    IStoredProc storedProc =
        StoredProc.getStoredProc(getDBTransaction(), procedureName);
    for (IStoredProcParam param : storedProc.getParams()) {
        if (param.isIn() || param.isInOut()) {
            param.setParamValue(getAttribute(param.getParamName()));
        }
    }
    storedProc.execute();
    for (IStoredProcParam param : storedProc.getParams()) {
        if (param.isOut())
            populateAttribute(getEntityDef().getAttributeIndexOf(
                              param.getParamName()),
                              param.getParamValue());
    }
}

The source code of this tool is available here. It can be deployed into ADF library and, perhaps, it can make your life a bit easier.

That's it!