Make JDBC Query logs simple

A simple extension of the JDBC Java.sql.PreparedStatement interface can make the query log less error-making, while organizing your code. In this article, IBM e-commerce advisor Jens Wyke shows you how to apply the basic encapsulation technology ("Extend by encapsulation" also known as the Decorator design pattern) to achieve the most satisfying results.

In most cases, JDBC preparedstatements makes it easier to execute database queries and significantly improves the performance of your overall application. When it comes to log query statements, the PreparedStatement interface appears to be a bit inadequate. The advantage of PreparedStatement is its variability, but a good log entry must correctly describe how to send SQL to the database, and it will pay close attention to replacing all parameter placeholders with actual parameter values. Although there are several ways to solve this problem, none of them is easy to implement on a large scale and most of it will disrupt your program code.

In this article, you will learn how to extend the JDBC PreparedStatement interface to query logs. The Loggablestatement class implements the PreparedStatement interface, but adds methods for obtaining a query string, using a format that applies to records. Using the Loggablestatement class can reduce the chance of errors in your log code and generate simple and manageable code.

Note: This article assumes that you have rich experience in JDBC and PreparedStatement classes.

Typical Log Solutions

Table 1 describes how PreparedStatement is typically used when querying a database (although initialization and error handling are ignored). In this article, we'll use SQL query select as an example, but we'll discuss using other types of SQL statements, such as delete, update, and insert.

Table 1: A typical SQL database query


String sql = "Select Foo, bar from Foobar where Foo <?" and bar =? "; String foovalue = new Long (99); String barvalue = "Christmas"; Connection conn = Datasource.getconnection (); PreparedStatement pstmt = conn.preparestatement (sql); Pstmt.setlong (1,foovalue); Pstmt.setstring (2,barvalue); ResultSet rs = Pstmt.executequery (); Parse result ...


A good query log entry in table 1 should look similar to the following:


Executing query:select foo,bar from Foobar where Foo < and bar= ' Christmas '


The following is an example of a query's log code. Note: The question mark in table 1 has been replaced by the value of each parameter.


System.out.println ("Executing query:select foo, bar from Foobar where foo<" +foovalue+ "and bar = ' +barvalue+ '")


A better approach is to create a method, which we call Replacefirstquestionmark, which reads the query string and replaces the question mark with the parameter value, as shown in table 2. The use of such methods does not require creating a copied string to describe the SQL statement.

Table 2: Using Replacefirstquestionmark for string substitution


Listing 1 goes here sql = Replacefirstquestionmark (sql, foovalue); sql = Replacefirstquestionmark (sql, barvalue); SYSTEM.OUT.PRINTLN ("Executing query:" +sql);


Although these solutions are easy to implement, none of them is perfect. The problem is that you must change the log code while you change the SQL template. It is almost inevitable that you will make a mistake at some point. The query changes but you forget to update the log code, and you end the log entry that does not match the query that will be sent to the database-debug nightmare.

What we really need is a design that enables us to use each parameter variable (foovalue and Barvalue in our case) at once. We want to have a method that allows us to get the query string and replace the parameter placeholder with the actual parameter values. Since Java.sql.PreparedStatement does not have such a method, we must implement it ourselves.

Custom solutions

Our PreparedStatement custom implementation will be the "real statement" wrapper (wrapper) that is provided around the JDBC drive. The wrapper statement forwards all method calls (such as Setlong (int, long) and setstring (int,string)) to the "real statement". Before doing so, it will save the related parameter values so that they can be used to generate log output results.

Table 3 describes how the Loggablestatement class implements Java.sql.PreparedStatement and how it is built using JDBC connections and SQL templates as input.

Table 3:loggablestatement Implementation Java.sql.PreparedStatement


public class Loggablestatement implements Java.sql.PreparedStatement {//used for storing parameter values needed Producing log private ArrayList parametervalues; The query string with question marks AS//parameter placeholders private string sqltemplate; A statement created from real database//connection private PreparedStatement wrappedstatement; Public Loggablestatement (Connection Connection, String sql) throws SQLException {//Use Connection to make a prepared STA Tement wrappedstatement = connection.preparestatement (sql); sqltemplate = SQL; ParameterValues = new ArrayList (); } }


How Loggablestatement Works

Table 4 describes how loggablestatement adds a call to the Savequeryparamvalue () method and calls the appropriate method on the "real statement" of method Setlong and SetString. We add Savequeryparamvalue () calls in the same way as all methods used for parameter setting (such as SetChar, Setlong, Setref, and Setobj). Table 4 also shows how to encapsulate method ExecuteQuery without calling Savequeryparamvalue (), because it is not a parameter setting method.

Table 4:loggablestatement Method


public void Setlong (int parameterindex, long x) throws Java.sql.SQLException {Wrappedstatement.setlong (Parameterindex, x); Savequeryparamvalue (Parameterindex, New Long (x)); The public void setstring (int parameterindex, String x) throws Java.sql.SQLException {wrappedstatement.setstring ( Parameterindex, x); Savequeryparamvalue (Parameterindex, x); Public ResultSet executequery () throws Java.sql.SQLException {return wrappedstatement.executequery ();}


The Savequeryparamvalue () method is shown in table 5. It converts each parameter value into a string representation, which is saved so that the GetQueryString method is used later. By default, an object uses its ToString method to be converted to a string, but if the object is string or date, it is represented by a single quotation mark (""). The GetQueryString () method allows you to copy most queries from the log and paste them, and you can test and debug without modifying the interactive SQL processor. You can revise the method to convert the parameter values of other classes as needed.

Table 5:savequeryparamvalue () method


private void Savequeryparamvalue (int position, Object obj) {string strvalue; if (obj instanceof string | | obj instanceof Date) {//If we have a String, include ' in the saved value strvalue = ' ' + obj + ' ';} else {if (obj = = null) {//Convert NULL to the string null strvalue = ' null ';} else {//Unknown object (includes all N Umbers), just call toString strvalue = obj.tostring (); }//If we are setting a position larger than current size of//parametervalues, the It larger while (position & gt;= parametervalues.size ()) {parametervalues.add (null);}//Save the parameter Parametervalues.set (position, strvalue ); }


When we use the standard method to set all parameters, we simply call the GetQueryString () method in loggablestatement to get the query string. All question marks are replaced by real parameter values, which are ready to be exported to our selected log destinations.

Using Loggablestatement

Table 6 shows how to change the code in Table 1 and table 2 to use Loggablestatement. Introducing Loggablestatement into our application code can solve the problem of parameter variables in replication. If you change the SQL template, we simply update the parameter settings call on the PreparedStatement (for example, add a pstmt.setstring (3, "New-param-value")). This change will be reflected in the log output results without any manual updates of the recording code.

Table 6: Using Loggablestatement

　　
String sql = "Select Foo, bar from Foobar where Foo <?" and bar =? "; Long foovalue = 99; String barvalue = "Christmas"; Connection conn = Datasource.getconnection (); PreparedStatement pstmt; if (logenabled)//Use a switch to toggle logging. pstmt = new Loggablestatement (conn,sql); else pstmt = conn.preparestatement (sql); Pstmt.setlong (1,foovalue); Pstmt.setstring (2,barvalue); if (logenabled) System.out.println ("Executing query:" + (loggablestatement) pstmt). GetQueryString ()); ResultSet rs = Pstmt.executequery ();


Conclusion

Using the very simple steps described in this article, you can extend the JDBC PreparedStatement interface for query records. The technology we use here can be seen as "extending through encapsulation," or as an example of a decorator design pattern (see Resources). Extending by encapsulation is extremely useful when you have to extend the API but subclassing is not an optional feature.

You will find the source code for the Loggablestatement class in the Resources section. You can use it as-is or tailor it to meet the special needs of your database application.

Resources

Download the source code for the Loggablestatement class.

You'll find a brief introduction to using Preparedstatements in the tips and tricks of Roman vichr: JDBC hints (DeveloperWorks, October 2002).

Lennart Jorelid's "use JDBC for Industrial-strength performance" (developerworks,2000 year January) is a good two-part introductory article in the design pattern in JDBC.

Josh Heidebrecht's "JDBC 3.0 new feature" (developerworks,2001 year July) provides an overview of JDBC 3.0.

You can download the Java Platform, Standard Edition and JDBC 3.0 API specification from java.sun.com.

David Gallardo's "Java Design mode" (developerworks,2002 year January) is a good introduction to the gang of four template.

Paul Monday's "Java Design pattern 201" (developerworks,2002 year April) provides a more conceptual description of the Java design pattern for senior students.

Vince Huston's design Patterns site is another resource that understands the pattern.

Brian Goetz's "Java Theory and Practice: Performance Management-Do you have a plan?" "(developerworks,2003 March) illustrates some of the measures that you can implement to improve the overall performance of your Java applications.