问题描述
我正在尝试连接数据库,运行查询并打印出查询。到目前为止,我有什么工作,但我需要得到输出并将其特定部分分配给字符串
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();
}
}
我正在使用来监视我的查询。
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查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!