本文介绍了在SQL Server上使用jdbc PreparedStatement获取查询计划的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
使用语句,resultSet.getObject将查询计划返回为xml
Using Statment, resultSet.getObject returns query plan as xml
Connection conn = getConnection();
String query = " SET SHOWPLAN_XML on ";
Statement st = conn.createStatement();
boolean execute=st.execute(query);
log.info("execute status {} " , execute);
query = " SELECT ATMPROFILES.TERMID as COLUMNID, ATMPROFILES.TERMID as COLUMNNAME FROM ATMPROFILES (NOLOCK) "
+ " WHERE Authprocessname = 'ATMST' "
+ "ORDER BY ATMPROFILES.TERMID ";
ResultSet rs = st.executeQuery(query);
while(rs.next())
{
Object object = rs.getObject(1);
log.info("Query Plan {} ", object);
}
但是,如果我通过PreparedStatement执行相同的操作,它将返回实际结果,而不是QueryPlan
But If I execute the same through PreparedStatement, it returns actual result insteadof QueryPlan
Connection conn = getConnection();
String query = " SET SHOWPLAN_XML on ";
PreparedStatement ps = conn.prepareStatement(query);
boolean execute = ps.execute();
log.info("execute status {} " , execute);
query = " SELECT ATMPROFILES.TERMID as COLUMNID, ATMPROFILES.TERMID as COLUMNNAME FROM ATMPROFILES (NOLOCK) "
+ " WHERE Authprocessname = 'ATMST' "
+ "ORDER BY ATMPROFILES.TERMID ";
ps=conn.prepareStatement(query);
execute=ps.execute();
log.info("execute status {} " , execute);
ResultSet rs = ps.getResultSet();
while(rs.next())
{
Object object = rs.getObject(1);
// here it returns selected object
log.info("Query Plan {} ", object);
}
任何通过PreparedStatement实现此目标的想法.
Any idea to acheive this via PreparedStatement.
推荐答案
我还没有找到为什么将SET SHOWPLAN_XML ON
作为准备好的语句执行将不起作用的参考;但是,当直接运行此语句并将实际查询作为准备好的语句运行时,应该会获得所需的结果.在代码中:
I haven't found any reference why executing SET SHOWPLAN_XML ON
as a prepared statement will not work; however, you should get the desired results when you run this statement directly and your actual query as a prepared statement. In code:
Connection conn = getConnection();
String showplanQuery = "SET SHOWPLAN_XML ON";
Statement st = conn.createStatement();
st.execute(showplanQuery);
String actualQuery = "SELECT ATMPROFILES.TERMID FROM ATMPROFILES (NOLOCK) ";
PreparedStatement ps=conn.prepareStatement(actualQuery);
ps.execute();
ResultSet rs = ps.getResultSet();
while(rs.next())
{
Object object = rs.getObject(1);
// should log the query plan
log.info("Query Plan {} ", object);
}
希望有帮助.
这篇关于在SQL Server上使用jdbc PreparedStatement获取查询计划的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!