Shulou(Shulou.com)06/01 Report--

Today, the editor would like to share with you what are the relevant knowledge points of JDBC query logging methods, detailed content, clear logic, I believe most people still know too much about this knowledge, so share this article for your reference, I hope you can get something after reading this article, let's take a look at it.

In most cases, JDBCPreparedStatement makes it easier to execute database queries and can significantly improve your overall application performance. But when it comes to record query statements in PreparedStatement, this interface is not enough. Although the advantage of aPreparedStatement is its variability, a good log entry must accurately describe what the SQL sent to the database looks like after all parameter placeholders have been replaced by actual parameter values. Although there are many ways to solve this problem, none of them can be easily implemented on a large scale, and most of them will confuse your code.

In this article, you will learn how to extend the JDBCPreparedStatement interface to query logging. Although the LoggableStatement class implements the PreparedStatement interface, it adds a method to get the query string in a format appropriate for the record. Using the LoggableStatement class not only reduces the incidence of errors in logging code, but also produces cleaner, more manageable code over time.

Please note that this article assumes that you have previous experience with JDBC and PreparedStatement classes.

A typical query record solution

Listing 1 shows how PreparedStatement typically uses a when making database queries (although initialization and error handling are omitted). We will use SQL to query SELECT our example in this article, but the discussion also applies to other types of SQL statements such as DELETE,UPDATE and INSERT.

Listing 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... 清单 1 中查询的良好日志条目可能如下所示： Executing query: select foo,bar from foobar where foo < 99 andbar='christmas' 下面是该条目的日志记录代码外观的一个示例。请注意，清单 1 中的问号已替换为每个参数的值。 System.out.println("Executing query: select foo, bar from foobar where foo< "+fooValue+" and bar = '+barValue+"'") 更好的方法是创建一个方法，我们称之为replaceFirstQuestionMark，它接受查询字符串并用参数值替换问号，如清单 2 所示。使用这种方法消除了创建重复字符串来描述的需要SQL 语句。 清单 2. 使用 replaceFirstQuestionMark 进行字符串替换// listing 1 goes here sql = replaceFirstQuestionMark(sql, fooValue); sql = replaceFirstQuestionMark(sql, barValue); System.out.println("Executing query: "+sql); 虽然易于实施，但这些解决方案都不是理想的。问题是，每当对 SQL 模板进行更改时，日志记录代码也必须更改。在某些时候你会犯错误几乎是不可避免的。查询将被更改，但您将忘记更新日志记录代码，并且你最终会得到与发送到数据库的查询不匹配的日志条目--这是调试噩梦。 我们真正需要的是一个设计，让我们使用的每个参数变量（fooValue和barValue在我们的例子）只有一次。我们想要一种方法，它可以让我们获取参数占位符替换为实际值的查询字符串。因为java.sql.PreparedStatement没有这样的方法，我们必须自己实现一个。 自定义解决方案 我们的自定义实现PreparedStatement将充当 JDBC 驱动程序提供的"真实语句"的包装器。包装器语句会将所有方法调用（例如，setLong(int, long)和setString(int,String)）转发到"真实语句"。在这样做之前，它将保存相关的参数值，以便它们可用于生成日志输出。 清单 3 显示了LoggableStatement类是如何实现的java.sql.PreparedStatement，以及它是如何使用 JDBC 连接和 SQL 模板作为输入来构建的。 清单 3. LoggableStatement 实现 java.sql.PreparedStatementpublic class LoggableStatement implements java.sql.PreparedStatement { // used for storing parameter values needed // for producing 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 PreparedStatement wrappedStatement; 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 如何工作 清单 4 说明了LoggableStatement如何添加对saveQueryParamValue()方法的调用，以及如何在方法setLong和 setString的 real语句上调用相应的方法。saveQueryParamValue()调用以类似的方式添加到用于参数设置的所有方法（例如setChar，setLong、setRef、 和setObj）。清单 4 还展示了在不调用saveQueryParamValue()的情况下如何包装方法executeQuery，因为它不是"参数设置"方法。 清单 4. LoggableStatement 方法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(); } saveQueryParamValue()方法如清单 5 所示。它将每个参数值转换为一种String表示形式，并将其保存以供该getQueryString方法以后使用。默认情况下，对象将String使用其toString方法转换为 a ，但如果对象是 aString或 a Date，它将用单引号 (") 括起来。getQueryString()方法允许你从日志中复制大多数查询并将它们粘贴到交互式 SQL 处理器中进行测试和调试，而无需修改。你可以根据需要修改该方法以转换其他类的参数值。 清单 5. saveQueryParamValue() 方法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 Numbers), just call toString strValue = 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 all the parameters are set using the standard method, we simply call our getQueryString () method LoggableStatement to get the query string. All question marks will be replaced with actual parameter values, which are ready to be output to the logging destination of our choice.

Use LoggableStatement

Listing 6 shows how to change the code in listings 1 and 2 to use LoggableStatement. Introducing LoggableStatement into our application code solves the problem of repetitive parameter variables. When making changes to the SQL template, we only need to update the PreparedStatement parameter setting call (for example, add pstmt.setString (3, "new-param-value")). The changes are reflected in the log output and no manual updates are required to the log code.

Listing 6. LoggableStatementString sql = "select foo, bar from foobar where foo

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.