java - 在sql server上使用jdbcPreparedStatement获取查询计划

Question

使用 Statment，resultSet.getObject 以 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

 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 实现此目的的想法。

Answer 1

我还没有找到任何引用资料来说明为什么执行 SET SHOWPLAN_XML ON 作为准备好的语句不起作用；但是，当您直接运行此语句并将实际查询作为准备好的语句时，您应该会获得所需的结果。在代码中:

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); 
}

希望有帮助。