The Op Framework: A better Java database framework

A holistic approach to database access with debugging, testing, tracing and usage reporting

By Paul Feuer, JavaWorld.com, 01/30/07

After first glancing at the title of this article, perhaps you're thinking, "yet another database access article, blah, blah, blah." But you'll find the approach described here very useful. And I'm hoping that not only you, as a programmer, will find it useful, but also your quality assurance team, your help desk and your database administrator.

At the company I work for, our original database framework was somewhat problematic, dynamically building its own SQL statements, executing statements and logging inconsistently. The statements were executed, and the results processed and returned, but in a larger sense, we didn't have much of an idea of how much work was being triggered by a single request or have much of a chance to optimize our SQL. Our Oracle DBA would just see some chunk of a statement in his "top offenders list" and send out an e-mail saying "Does anyone recognize this?" Many times, we wouldn't know what parameters were used when calling the statement, complicating our ability to track down why Oracle's cost-based optimizer was making poor decisions.

Now that we've begun to migrate to the Op Framework, we can quickly identify the worst-performing pages (in terms of number of queries, connections used, time in the database). What's more, when fielding a slow-page complaint, we can now see which calls are taking a long time for that particular request or return other debug information related to our request.

This tracing is made possible by taking a consistent approach to object creation, logging and resource usage in a way that each log statement can be associated back to a specific request. In this article, we look at the implementation of this database access framework and its tracing functionality.

These are some of the cool things the Op Framework does:

• Dynamically turns on "dev-mode" request tracing, which is returned in hidden HTML to the developer's browser.
• Logs database resource usage to the filesystem for automated graphing, monitoring and "worst-offenders" reports to support smart tuning.
• Dynamically turns on "database capture mode," which saves database result sets to the filesystem for later reuse (even in the absence of a database).
• Decouples execution from Enterprise JavaBeans (EJB) and Java Naming and Directory Interface to make unit testing more straightforward.

In describing this framework, I begin at the servlet level (Struts, in our example) by discussing the Op object. Next, we look at database call delegation, then at OpExecutors, retrieval of DataSources, and navigation of EJB (or not). Then we look at the advanced tracing and data-stubbing features. After examining these levels, we return to the servlet to see what trace information we collected.

The Op

Figure 1. The Op base class

The Op object is a simple implementation of a Command pattern. "Op" is, of course, short for "operation." The word captures the simplicity of the object and is more convenient to type. Ops are meant to encapsulate some piece of logic that will execute in the subclass's implementation of the abstract _execute(CallExecutor) method. The underscore is meant to convey that this is not a method the developer should be calling directly. This method is called by an OpExecutor.

The OpExecutor interface specifies a simple method for executing an Op. This is an interface because in different situations, we'll want to execute this Op differently. In an EJB environment, we would want to navigate EJB for execution, while in my IDE, I would just run it directly. The Op base class allows us to specify our own OpExecutor, but by default, we rely on the OpExecutorFactory to provide us one.

Notice in Figure 1 that there are two Op constructors: a public one requiring a TraceKey and a protected one requiring another Op. The TraceKey, which I'll discuss in more detail below, uniquely identifies every request so we can see inside every Op what request caused this Op to be created and executed. The TraceKeyFactory is responsible for creating or retrieving the TraceKey.

Example Op instantiation and invocation:

 TraceKey traceKey = TraceKeyFactory.getTraceKey( httpRequest );
    DoSomethingOp doSomething = new DoSomethingOp( traceKey );
    doSomething.setFooId( fooId );
    doSomething.setBarId( barId );
    doSomething.execute();
    List results = doSomething.getMyDbResults();

During the Op's execution, in addition to the usual business logic programming, we can call other Ops and access the database.

Example execute implementation for an Op:

 public void _execute( CallExecutor exec )
    {
        // use Op's call creation convenience method
        // "sp_my_proc" turns into "{call sp_my_proc (?,?)}"
        DatabaseCall callOne = makeProcedure( "sp_my_proc");
        callOne.setParameters( new Object[] { "Hello", "World" } );
        exec.executeDatabaseCall( callOne );

        // do more processing . . .

        // Oracle function call "f_my_function" turns into 
        // "{?=call f_my_function (?,?)}"
        DatabaseCall callTwo = makeFunction( "f_my_function" );
        callTwo.setParameters( Types.VARCHAR
                             , new Object[] { "So", "Long" } );
        exec.executeDatabaseCall( callTwo);

        String returnedString = callTwo.getParameter( 1 ).getValue();
    }

With the Op Framework, parameter setting is simplified from the verbose Java Database Connectivity (JDBC) statement methods. With DatabaseCalls, there is implicit behavior in the constructors: a single-argument object represents implicit input (with an attempt to look up common JDBC type mappings), and a single-argument int represents implicit output. DatabaseCall's setParameters() methods iterate the object array, and if an object is not a parameter, the object is made into a parameter using the single-argument constructor. For null or other atypical parameters, you must use the full three-argument parameter constructor identifying the jdbcType, direction, and value.

OpExecutors and CallExecutors

The OpExecutor interface is simply one method: executeOp(Op). In our default JBoss EJB environment, the EjbOpExecutorFactory creates the EjbOpExecutor objects for us. These EjbOpExecutors are initialized with a simple EJB path, where we find an EJB component that can run our Op. In our IDE, or in a continuous testing environment, it would probably suffice to use DirectOpExecutor, which directly invokes the Op's _execute(CallExecutor) method. Figure 2 is the sequence diagram depicting the instantiation and execution of an Op: its use of the OpExecutorFactory abstract factory, the navigation of EJB, and its invocation of the methods on the CallExecutor interface.

Figure 2. Op creation and execution. Click on thumbnail to view full-sized image.

The DirectOpExecutor instantiates the CallExecutorDB, which receives DatabaseCall objects, transforms them into JDBC statements, executes them, and puts any statement outputs back into the DatabaseCall's registered parameters (this sequence is discussed below).

Making database calls

Many frameworks delegate calls to the database by writing SQL for you. The Op Framework does not do this. In our company, much of our business logic resides in the database and is accessed through packages, functions and procedures. We also have a complex enough data model that the thought of trying to set up the XML to describe it and query against it gives me a headache. We can send parameterized SQL statements, but usually we just use procedure names. In using this approach, we not only receive the benefit of the database's usefulness in operating over sets, but we can also tune the SQL without redeploying our application server. It also means our Java code is now less coupled to our database schema, as the functions and procedures act as an API.

The Op Framework does delegate calls, because, when we delegate calls, we also receive the benefit of consistent logging, execution, result-set transformation and resource closing. Using the DatabaseCall object to describe our interaction with the database, we get a leaner and simpler interface to prepared and callable statements, without coupling ourselves to JDBC connections and implementations. The pieces of Statement and ResultSet interfaces that we lose using the DatabaseCall object shouldn't be accessed in high-performance applications; rather, we should implement database procedures. Furthermore, in the kinds of result sets we're returning, which may contain information across several tables, looking for updates and scrolling could really burn us.

The DatabaseCall object used by the Op is a container class holding:

• The text of the call (procedure name or parameterized SQL query).
• The Op responsible for its instantiation.
• A mapping of parameter index to parameter object.
• ResultSets returned by a multi-ResultSet returning call.
• The name of the datasource used to look up a connection in the MappedConnectionFactory.
• Options from Op used to enable special functionality.
• Information about the call's execution (timing, connection).

Figure 3 shows a class diagram of the DatabaseCall and Parameter classes.

Figure 3. DatabaseCall and Parameter classes. Click on thumbnail to view full-sized image.

DatabaseCalls, as I've said, are merely containers and don't have any behavior in and of themselves; they have the information required to articulate some JDBC call. Implementations of the CallExecutor interface, passed in through the _execute(CallExecutor) method, handle DatabaseCalls. And the usual one is the CallExecutorDB, which, as you might guess from the name, executes DatabaseCalls against a database.

The CallExecutorDB transforms DatabaseCall objects into JDBC statement objects and consistently executes them against the database: processing pre-call options; setting up the statement with its parameters; executing the statement; processing any output; recording timing information, row count information; logging the call with its parameters; and finally, processing any post-call options. Figure 4 shows how the CallExecutor uses auxiliary classes to build JDBC statements and record their outputs.

Figure 4. Database call execution sequence. Click on thumbnail to view full-sized image.

The CallOutputHandler handles the various types of output types, including, in our case, ResultSets (also Oracle RefCursors, which implement ResultSet). Our ResultSets, for example, are transformed into collections of maps. After this transformation, we can track how many rows we processed and how long that task took. These values are available in DatabaseCall's CallExecutionInfo object.

When finished, we've achieved execution of a database call delegated using an interface to an object that sends the described call through JDBC; the execution of the call is consistent, logged and timed.

Activating behavior using parameter triggers

Some of the Op Framework's features, such as data capture and return, and tracing (both to be discussed below), are activated by using special request parameters in the URL. The ParamTriggerFilter examines HttpServletRequest parameters for these special settings. The Filter is initialized by TriggerInitializer instances that are registered in init-params of the Filter's configuration in the web.xml file.

Each TriggerInitializer registers ParamTrigger objects in the Filter. When the Filter sees a new URL coming in, it looks to see if there are ParamTriggers that respond to each name. If a ParamTrigger responds to the parameter name, the trigger is fired, which causes any listeners on the trigger to execute; they typically set options that Ops in the request will use during their execution. The Op Framework implements several triggers that fall into two categories: tracing and data capture, both of which are described below.

Discuss

Subject

Replies

Last post

Free Download - 5 Minute Product Review. When slow equals Off: Manage the complexity of Web applications - Symphoniq

Free Download - 5 Minute Product Review. Realize the benefits of real user monitoring in less than an hour. - Symphoniq