DOWNLOAD
Oracle JDeveloper
   TAGS
adf, java, All
product logo Taking an Oracle ADF Application from Design to Reality

By Chris Muir ACE Director and Penny Cookson ACE

Chapter 4 - The "Could-Have" Requirements: Logging the Search Criteria

Published May 2009

Click here to see "Taking an Oracle ADF Application from Design to Reality" description and Table of Contents

In this section, our COULD-Have main requirement is to log all search criteria entered by the user, as well as the number of records returned.  This will enable us in the future to see how useful our search page is, or if the users are having difficulty in using it.

Again, we need to break down these requirements into discrete solvable units:

  1. Create an additional logging table to store query results.
  2. Identify a framework query-event chokepoint method to capture query results.
  3. Write the query results to the database query logging table via a JDBC routine.
  4. Create an additional logging table to store the inquiry search fields.
  5. Write the search parameters to the database search parameter logging table via a JDBC routine.

Create an additional logging table to store query results.

For each session there may be one or more queries/searches, each returning zero or one or more records.  Accordingly, we want to create an additional logging table in the database that records these facts.  Once again we'll beg our DBA to create a new table in the database:

CREATE TABLE log_query
(log_query_id     NUMBER(10,0)   NOT NULL
,log_session_id   NUMBER(10,0)   NOT NULL 
,sql_statement    VARCHAR2(2000) NOT NULL
,records_returned NUMBER(3)      
,datetime         DATE           NOT NULL 
,CONSTRAINT log_query_pk PRIMARY KEY (log_query_id)
,CONSTRAINT log_query_fk1 FOREIGN KEY (log_session_id)
 REFERENCES log_session(log_session_id));

Plus the following sequence:

CREATE SEQUENCE log_query_seq;
Note how the table has a foreign key to our log_session table's primary key enabling us to track the queries against the user session.

Identify a framework query-event chokepoint method to capture query results.

Given that we have our log_query table to store information about the query event, we now need to identify a chokepoint method in the Oracle ADF framework to capture the event, so we can write to the log_query table.

In Oracle ADF Business Components, in the ViewObjectImpl the method executeQuery() is responsible for executing the View Object's query against the database. We can override this method in the ParcelViewImpl as follows:

@Override
public void executeQuery() {
  super.executeQuery();
}

Write the query results to the database query logging table via a JDBC routine.

Now that we have the executeQuery() method we augment the executeQuery() method to write the audit information as follows:

@Override
public void executeQuery() {
  if (firstQuery) {
    super.executeQuery();
  } else {

    AppModuleImpl am = (AppModuleImpl)getApplicationModule();
     
                              
am.insertLogQuery(getQuery(),null);

    super.executeQuery();
   
     
                              
am.updateLogQuery(new Number(super.getQueryHitCount(getDefaultRowSetInternal())));
  }
}
As you can see we want to log the query to the log_query table before doing the query, and after the query in the same record we'll update the number of records fetched. Within the AppModuleImpl we'll define the insertLogQuery() and updateLogQuery() methods as follows: 
public void insertLogQuery(String sqlStatement, Number recordsReturned) {
  DBTransaction trans = getDBTransaction();

  SequenceImpl seq = new SequenceImpl("LOG_QUERY_SEQ", trans);
   
                              
setLogQueryId(seq.getSequenceNumber());

  CallableStatement statement = null;

  String plsql =
    "DECLARE "
   +  "PRAGMA AUTONOMOUS_TRANSACTION; "
   +"BEGIN "
   +  "INSERT INTO log_query "
   +  "(log_query_id, log_session_id, sql_statement, records_returned, datetime) "
   +  "VALUES (?,?,?,?,sysdate); "
   +  "COMMIT; "
   +"END;";

  statement = trans.createCallableStatement(plsql, 4);
  try {
    statement.setInt(1, getLogQueryId().intValue());
    statement.setInt(2, getLogSessionId().intValue());
    statement.setString(3, sqlStatement);
    if (recordsReturned == null)
      statement.setNull(4, Types.INTEGER);
    else
      statement.setInt(4, recordsReturned.intValue());

    int rows = statement.executeUpdate();

  } catch (SQLException s) {
    throw new JboException(s);
  } finally {
    try {
      if (statement != null)
        statement.close();
    } catch (SQLException s) { /* ignore */
    }
  }
}

public void updateLogQuery(Number recordsReturned) {
  DBTransaction trans = getDBTransaction();

  CallableStatement statement = null;

  String plsql =
    "DECLARE "
   +  "PRAGMA AUTONOMOUS_TRANSACTION; "
   +"BEGIN "
   +  "UPDATE log_query "
   +  "SET records_returned = ? "
   +  "WHERE log_query_id = ?; "
   +  "COMMIT; "
   +"END;";

  statement = trans.createCallableStatement(plsql, 2);
  try {
    if (recordsReturned == null)
      statement.setNull(1, Types.INTEGER);
    else
      statement.setInt(1, recordsReturned.intValue());           
       
                              
statement.setInt(2, getLogQueryId().intValue());

    int rows = statement.executeUpdate();

  } catch (SQLException s) {
    throw new JboException(s);
  } finally {
    try {
      if (statement != null)
        statement.close();
    } catch (SQLException s) { /* ignore */
    }
  }
}
You'll note that we have the setLogQueryId() and getLogQueryId() methods to write and read the next log_query_seq value from the database to an instance variable in the AppModuleImpl, similar to what we did for the LogSessionId. We'll add the following code to the AppModuleImpl: 
private Number logQueryId;

public void setLogQueryId(Number logQueryId) {
  this.logQueryId = logQueryId;
}

public Number getLogQueryId() {
  return logQueryId;
}
Unlike with the LogSessionId however we don't have to worry about ensuring that the LogQueryId is correctly passivated and activated, because the LogQueryId is used in a single user request.

You can now test this functionality. The following is an example of a logging entry in our LOG_QUERY table:

Figure1

Notice that the SQL Statement contains the bind variables and does not actually show us the query criteria that the user entered. The next step will log these to a new table.

Create an additional logging table to store the inquiry search fields.

For each query, we want to save a copy of the search parameters and their values. Later on, we can see what sorts of searches our users are doing and find out if, in combination with the log_query table record, they are successfully returning any records. So for the last time, we'll beg our DBAs to create another logging table in the database:

CREATE TABLE log_param
(log_param_id     NUMBER(10,0)   NOT NULL
,log_query_id     NUMBER(10,0)   NOT NULL
,param_name       VARCHAR2(100)  NOT NULL
,param_value      VARCHAR2(100)
,datetime         DATE           NOT NULL 
,CONSTRAINT log_param_pk PRIMARY KEY (log_param_id)
,CONSTRAINT log_param_fk1 FOREIGN KEY (log_query_id)
 REFERENCES log_query(log_query_id));
And the following sequence: 
CREATE SEQUENCE log_param_seq;
Note how the table has a foreign key to our log_query table's primary key enabling us to track the parameters against the queries for the user session.

Identify a framework chokepoint method to capture the enquiry search parameters and write them to our logging tables.

Thanks to our earlier work we know that the ViewObjectImpl has a chokepoint method executeQueryForCollection() that is called before the View Object's query is executed. That in turn allows us to inspect the bind variables entered by the user. Currently our executeQueryForCollection() method looks like this:

@Override
protected void executeQueryForCollection(Object queryCollection, Object[] bindParams, int noUserParams) {
  if (firstQuery)
    firstQuery = false;
  else 
    enforceQueryCriteria();
  super.executeQueryForCollection(queryCollection, bindParams, noUserParams);
}

Write the search parameters to the database search parameter logging table via a JDBC routine.

As we've identified the chokepoint method, we'll add a call to a new method logQueryCriteria() as follows:

@Override
protected void executeQueryForCollection(Object queryCollection, Object[] bindParams, int noUserParams) {
  if (firstQuery)
    firstQuery = false;
  else {
     
                              
logQueryCriteria(bindParams);
    enforceQueryCriteria();
  }
  super.executeQueryForCollection(queryCollection, bindParams, noUserParams);
}
The new method logQueryCriteria() this: 
private void logQueryCriteria(Object[] bindParams) {
  String bindParamName;
  Object bindParamValue;
  
  AppModuleImpl am = (AppModuleImpl)getApplicationModule();

  for (int i = 0; i < bindParams.length; i++) {
    bindParamName = ((Object[])bindParams[i])[0].toString();
    bindParamValue = ((Object[])bindParams[i])[1];

    am.insertLogParam(bindParamName, bindParamValue);
  }
}
And finally, we create the new AppModuleImpl method insertLogParam() that via a JDBC call inserts the query parameters into the log_param table: 
public void insertLogParam(String paramName, Object paramValue) {
  DBTransaction trans = getDBTransaction();

  SequenceImpl seq = new SequenceImpl("LOG_PARAM_SEQ", trans);

  CallableStatement statement = null;

  String plsql =
    "DECLARE "
   +  "PRAGMA AUTONOMOUS_TRANSACTION; "
   +"BEGIN "
   +  "INSERT INTO log_param "
   +  "(log_param_id, log_query_id, param_name, param_value, datetime) "
   +  "VALUES (?,?,?,?,sysdate); "
   +  "COMMIT; "
   +"END;";

  statement = trans.createCallableStatement(plsql, 4);
  try {
    statement.setInt(1, seq.getSequenceNumber().intValue());
    statement.setInt(2, getLogQueryId().intValue());
    statement.setString(3, paramName);

    if (paramValue == null) {
      statement.setNull(4, Types.VARCHAR);
    } else if (paramValue instanceof String) {
      if (((String)paramValue).equals(""))
        statement.setNull(4, Types.VARCHAR);
      else
        statement.setString(4, (String)paramValue);
    } else {
      statement.setString(4, paramValue.toString());
    }

    int rows = statement.executeUpdate();

  } catch (SQLException s) {
    throw new JboException(s);
  } finally {
    try {
      if (statement != null)
        statement.close();
    } catch (SQLException s) { /* ignore */
    }
  }
}
If we run our SearchPage, and either accept or reject the terms and conditions, the user information is written to the log table.

Summary

In this section we have added code to satisfy the COULD-Have requirements of our application, including logging all search criteria entered by the user, as well as the number of records returned. The next and final chapter shows how we can add some functionality to prevent malicious access. Click here to see "Taking an Oracle ADF Application from Design to Reality" description and Table of Contents