从Derby获取PreparedStatement查询 [英] get PreparedStatement query from Derby

问题描述

我正在尝试连接数据库，运行查询并打印出查询。到目前为止，我有什么工作，但我需要得到输出并将其特定部分分配给字符串

I'm trying to connect to a database, run a query and print out the query. So far what I have works but I need to get the output and assign a particular part of it to a String

public static void main(String args[]) {
        BasicConfigurator.configure();
        Logger.getGlobal().setLevel(Level.INFO);
        PreparedStatement preparedStatement = null;
        try {
            connect();
            String sql = "SELECT * FROM foo WHERE ID = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, 1);
            try (ResultSet resultSet = preparedStatement.executeQuery()) {
                while (resultSet.next()) {
                    break;
                }
            }
            //String usedSql = "query should  go here";
        } catch (SQLException ex) {
            Logger.getLogger(Test.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException ex) {
                    Logger.getLogger(Test.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
            disconnect();
        }
    }

我正在使用 log4jdbc 来监视我的查询。

I'm using log4jdbc to spy on my queries.

目前我的记录输出如下：

At the moment I get logged output like:

594 [main] DEBUG jdbc.foo  - 1. Connection.new Connection returned   java.sql.DriverManager.getConnection(DriverManager.java:664)
608 [main] DEBUG jdbc.foo  - 1. PreparedStatement.new PreparedStatement returned   com.example.Test.main(Test.java:63)
608 [main] DEBUG jdbc.foo  - 1. Connection.prepareStatement(SELECT * FROM foo WHERE ID = ?) returned net.sf.log4jdbc.PreparedStatementSpy@7d70d1b1  com.example.Test.main(Test.java:63)
608 [main] DEBUG jdbc.foo  - 1. PreparedStatement.setInt(1, 1) returned   com.example.Test.main(Test.java:64)
608 [main] DEBUG jdbc.foo  - 1. PreparedStatement.setMaxRows(1) returned   com.example.Test.main(Test.java:65)
609 [main] DEBUG jdbc.sqlonly  -  com.example.Test.main(Test.java:66)
1. SELECT * FROM foo WHERE ID = 1 

我想将 SELECT * FROM foo WHERE ID = 1 分配给 usedSql 。我怎么能这样做？

I want to assign SELECT * FROM foo WHERE ID = 1 to usedSql. How can I go about doing this?

推荐答案

通常是 preparedStatement.toString（）将为您提供查询（包括绑定参数）。但这取决于 PreparedStatement 的实际实现（例如，PostgreSQL impl可以工作）。

Usually a preparedStatement.toString() will give you the query (incl. bound parameters). But it depends on the actual implementation of the PreparedStatement (with the PostgreSQL impl this works, for example).

您提到 preparedStatement.toString（）返回 net.sf.log4jdbc.PreparedStatementSpy @ 7d70d1b1 为你服务。我不熟悉 log4jdbc ，但我看起来像 PreparedStatementSpy 正在包装你的实际 PreparedStatement 。要从 preparedStatement 获取它，请尝试类似

You mentioned that preparedStatement.toString() returns net.sf.log4jdbc.PreparedStatementSpy@7d70d1b1 for you. I'm not familiar with log4jdbc but I looks like PreparedStatementSpy is wrapping your actual PreparedStatement. To get it from your preparedStatement try something like

if(preparedStatement instanceof PreparedStatementSpy)
     usedSql = ((PreparedStatementSpy) preparedStatement).getRealStatement().toString();

修改：因为您使用的是 Derby 一个简单的 toString（）不会这样做。解决这个问题的方法可能是使用 PreparedStatementSpy.dumpedSql（），它将返回用于记录的相同字符串 log4jdbc 。不幸的是它是 protected 方法，你必须使用反射：

Edit: since you are using Derby a simple toString() won't do. A way around this could be to use PreparedStatementSpy.dumpedSql(), which will return the same string log4jdbc uses for logging. Unfortunately its a protected method and you have to use reflection:

if (preparedStatement instanceof PreparedStatementSpy) {
    Method m = PreparedStatementSpy.class.getDeclaredMethod("dumpedSql");
    m.setAccessible(true);
    usedSql = (String) m.invoke(preparedStatement);
}
// omitted exception handling

这篇关于从Derby获取PreparedStatement查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！