JDBC Java.sql.preparedStatement interface simple extension makes query logs less mistakes while sorting your code. In this article, the IBM E-commerce consultant JENS WYKE introduces you how to apply basic package technology ("" "" "" also known as Decorator design mode) to obtain the most satisfactory results. In most cases, JDBC PreparedStatements makes the execution database query easier and significantly improves your overall application. When it comes to the log query statement, the PreparedStatement interface is somewhat deficiencies. The advantage of PreparedStatement is its variability, but a good log entry must correctly describe how to send SQL to the database, which will pay close attention to replace all parameter placeholders with the actual parameter value. Although there are many ways to solve this problem, there is no easy implementation and most of them will disturb your program code.
In this article, you will learn how to extend the JDBC PreparedStatement interface for query logs. The LoggableStatement class implements the PreparedStateMent interface, but adds a method for obtaining a query string, using a format suitable for records. Using the LogGableStatement class can reduce the chances of errors in the log code to generate simple and easy-to-manage code. Note: This article assumes that you have a rich experience in JDBC and PREPAREDSTATEMENT. Typical Log Solution Table 1 describes how to use PreparedStatement when a database query (although initialization and error handling) are ignored. In this article, we will use SQL Query Select as an example, but discuss use 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
String foovalue = new long (99);
String barvalue = "Christmas";
Connection conn = DataSource.getConnection ();
PreparedState pstmt = conn.preparestatement (SQL);
PSTMT.SETLONG (1, FooValue);
PSTMT.SetString (2, barvalue);
ResultSet RS = pstmt.executeQuery ();
// PARSE RESULT ...
Table 1 A good query log entry appears to be a bit similar to:
Executing Query: SELECT FOO, BAR from Foobar WHERE FOO <99 and
Bar = 'Christmas'
Here is an example of the log code of the query. 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 way is to create a method, we call REPLACEFIRSTQUESTIONMARK, which read the query string and replaced the question mark with the parameter value, as shown in Table 2. This type of method is used to describe the SQL statement without creating a replicated string. Table 2: Use the ReplaceFirstQuestionMark to make a string replacement
// listing 1 goes heresql = replacefirstquestMark (SQL, FooValue);
SQL = ReplaceFirstQuestionMark (SQL, BARVALUE);
System.out.println ("Executing Query: SQL);
Although these solutions are easy to implement, no one is perfect. The problem is to change the log code while changing the SQL template. You will make mistakes in a certain point is almost inevitable. The query will be changed but you have forgotten the log code, you will end the log entry that does not match the query that will be sent to the database - debugging the nightmare. What we really need is an design that enables us to use each parameter variable (FooValue and Barvalue in our instance). We want to have a method that allows us to get query strings and replace parameter placeholders with actual parameter values. Since java.sql.preparedStatement does not have such methods, we must implement themselves. Customization Solution Our prepaaredStatement custom implementation is a "real statement" package that is provided around the JDBC drive. The package statement will forward all methods calls (such as setlong (int, line) and setString (int, string) to "real statements". It will save the associated parameter value before doing so can be used to generate the log output. Table 3 describes how the LogGableStatement class implements java.sql.preparedStatement, and how it uses JDBC connections and SQL templates as input. Table 3: LoggableStatement implementation java.sql.preparedStatement
Public class loggablestatement imports java.sql.preparedStatement {
// use for storing parameter values needed
// forproducing log
Private ArrayList ParameterValues;
// the query string with Question Marks AS
// Parameter PlaceHolders
Private string sqltemplate;
// a Statement Created from a real database
// connection
Private prepaaredStatement WrappedStatement Download and PRIVATETATEMENT
Public loggableStatement (Connection Connection, String SQL)
Throws sqlexception {
// use connection to make a prepared statement
WrappedStatement = Connection.PrepareStatement (SQL);
SqlTemplate = SQL;
Parametervalues = new arraylist ();
}
}
LogGableStatement How Table 4 describes how to add a call to the SaveryParamValue () method and call the corresponding method on the "Real Statement" of the method setlong and setstring. We use the same way as all methods for parameter settings (such as setChar, setlong, setRef, and setobj) to increase SaveQueryParamValue () calls. 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));
}
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 to String, saved to get QueryString method in the future. By default, an object uses its toString method to be converted to string, but if the object is string or Date, it will be represented by single quotation marks (''). GetQueryString () Method allows you to copy most queries from the log and paste, without modifying an interactive SQL processor, you can test and debug. You can revise this method as needed to convert other class parameter values. 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, incrude '' in the Saved Value
Strvalue = "'" Obj "'";
} else {
IF (Obj == null) {
// convert null to the string null
Strvalue = "null";
} else {
// Unknown Object (Includes All Numbers), Just Call Tostring
Strval = Obj.toString ();
}
}
// if we are setting a position larger Than Current Size of
// ParameterValues, First Make it Larger
While (position> = parametervalues.size ()) {
ParameterValues.Add (null);
}
// save the parameter
Parametervalues.Set (position, strvalue);
}
When we use standard methods to set all parameters, we simply call the getQueryString () method in the logGableStatement to get the query string. All question marks will be replaced by real parameter values, which are ready to output to our selected log destination. Use the LogGableStateMent Table 6 to display how to change the code in Table 1 and Table 2 to use LogGABleStatement. Introducing LoggableStatement into our application code to resolve the copying parameter variable problem. If you change the SQL template, we only need to update the parameter setting call on PreparedStatement (for example, add a PSTMT.SetString (3, "new-param-value"). This change will reflect in the log output, without any manual update of any recording code. Table 6: Using LoggableStatement
String SQL = "SELECT FOO, BAR from FOOBAR WHERE FOO
Long foovalue = 99;
String barvalue = "Christmas";
Connection conn = DataSource.getConnection ();
PREPAREDSTATEMT 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 Use this very simple step in this article, you can extend the JDBC PreparedStatement interface for the query record. The techniques we used here can be considered "extended by packaging", or as an example of Decorator design mode (see Resources). By encapsulation, it is extremely useful when you must extend the API but subclassing is not an optional feature. You will find the source code for the LogGableStatement class in the Reference section. You can use it as it or customize to meet the special needs of your database application. Reference ◆ Download the LoggableStatement class
Source code
. ◆ You will be in Roman Vichr
Tips and tricks: JDBC Tips
(DeveloperWorks, October 2002) Find a brief introduction to PreparedStatements. ◆ Lennart Jorelid
Use jdbc for industrial-stregth performance, STRENGTH PERFORMANCE
"(DeveloperWorks, January 2000) is a nice two partial introductory articles in the design pattern in JDBC. ◆ Josh HeidebrechT written"
JDBC 3.0 new feature
"(DeveloperWorks, July 2001) provides an overview of JDBC 3.0. ◆ You can
Java.sun.com
Download Java Platform, Standard Edition and JDBC 3.0 API specification. ◆ David Gallardo's "Java Design Mode 101
"(DeveloperWorks, January 2002) is a good introduction to the Gang of Four template. ◆ Paul Monday
Java design pattern 201
"(DeveloperWorks, April 2002) provides senior practitioners with a more conceptual description of Java design models. ◆ Vince huston
Design Patterns Site
It is another resource that knows the design mode. ◆ "Java theory and practice:" Brian Goetz:
Performance Management - Do you have planned?
"(DeveloperWorks, March 2003) describes some measures that you can implement to enhance the overall performance of Java applications.
