How do I display a prepared statement's variables via a Log Driver?
Author: Deron Eriksson
Description: This tutorial describes how to display a prepared statement including bind variable values using a Log Driver.
Tutorial created using:
Windows XP || JDK 1.5.0_09 || Eclipse Web Tools Platform 1.5.4 || MySQL 5.0.27
A common problem experienced when debugging JDBC-related code is to determine the values of bind variables in prepared statements and how these bind variable values look within the SQLW query. In another tutorial, we saw that the MySQLW JDBCW driver makes this easy since all you need to do is call toString on the prepared statement object in order to view the finished prepared statement including the bind variable values. However, with other JDBC drivers, such as Oracle JDBC drivers, this is not so simple. Thankfully, Ryan Bloom has created a handy little 'Log Driver' library located at http://www.rkbloom.net/logdriver that allows you to view this information. It relies on log4j for logging the results and is very easy to use. To demonstrate this, I've created a project shown below. It features the MySQL JDBC driver (mysql-connector-java-5.0.4-bin.jar), the Log4J library (log4j-1.2.14), and the Log Driver library (logdriver.jar). It features a LogDriverTest class that allows us to test the library. The LogDriverTest class is shown down below. Since the Log Driver relies on Log4J, I configure Log4J using the default BasicConfigurator.configure(). Next, rather than initializing the MySQL JDBC driver as we would normally do, we instead initialize the Log Driver. So, we replace Class.forName("com.mysql.jdbc.Driver").newInstance(); with Class.forName("net.rkbloom.logdriver.LogDriver").newInstance(); Next, we make a slight modification to our connection URL. Notice that we include the name of our actual JDBC driver in the connection URL (in this case, com.mysql.jdbc.Driver). We replace String connectionUrl = "jdbc:mysql://localhost:3306/testdatabase"; with String connectionUrl = "jdbc:log:com.mysql.jdbc.Driver:mysql://localhost:3306/testdatabase"; That's all we need to do! The full LogDriverTest class is shown directly below. LogDriverTest.javapackage test; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import org.apache.log4j.BasicConfigurator; public class LogDriverTest { public static void main(String[] args) { BasicConfigurator.configure(); Connection conn = null; PreparedStatement prepStmt = null; ResultSet rs = null; try { // Class.forName("com.mysql.jdbc.Driver").newInstance(); // String connectionUrl = "jdbc:mysql://localhost:3306/testdatabase"; Class.forName("net.rkbloom.logdriver.LogDriver").newInstance(); String connectionUrl = "jdbc:log:com.mysql.jdbc.Driver:mysql://localhost:3306/testdatabase"; String connectionUser = "testuser"; String connectionPassword = "testpassword"; conn = DriverManager.getConnection(connectionUrl, connectionUser, connectionPassword); String sqlStmt = "SELECT * FROM employees where last_name=? and first_name like ?"; System.out.println("SQL Statement:\n\t" + sqlStmt); prepStmt = conn.prepareStatement(sqlStmt); prepStmt.setString(1, "Doe"); prepStmt.setString(2, "%n%"); rs = prepStmt.executeQuery(); while (rs.next()) { String id = rs.getString("id"); String firstName = rs.getString("first_name"); String lastName = rs.getString("last_name"); System.out.println("ID: " + id + ", First Name: " + firstName + ", Last Name: " + lastName); } } catch (Exception e) { e.printStackTrace(); } finally { try { if (rs != null) rs.close(); } catch (SQLException e) { e.printStackTrace(); } try { if (prepStmt != null) prepStmt.close(); } catch (SQLException e) { e.printStackTrace(); } try { if (conn != null) conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } } (Continued on page 2) |