Adding log functionality to JDBC code with enhanced PreparedStatement

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　<　99　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.