通过Java获取创建并填充的MSSQL临时表的结果集 [英] Fetch Resultset of created&filled MSSQL temp table over Java

问题描述

query5

String query5 ="USE DBTwo\n" +
    "DECLARE @temp_table table (column1 VARCHAR(60))\n" +
    "insert into @temp_table (column1)\n" +
    "select column1 from real_table (nolock)";

query3

String query3 = "USE DBTwo\n" +
    "select column1 from @temp_table";

连接；

ResultSet rs1;
Statement stmt;

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
String connectionUrl = "jdbc:sqlserver://192.168.131.10;" +"databaseName=DBOne;" +"user=" + "exuser" + ";" + "password="+"userpass" + ";" + "allowMultiQueries=true" + ";"; 
Connection con = DriverManager.getConnection(connectionUrl);
stmt = con.createStatement();

获取结果集；

   try {
       int rowcount = stmt.executeUpdate(query5);
       System.out.println(rowcount);

       rs1 = stmt.executeQuery(query3);
       while (rs1.next()) {
             System.out.println(rs1.getString(1)); 
       } 
        rs1.close();
   }
   catch (SQLException sqlex){
       sqlex.printStackTrace();
   }

Sql异常抛出错误；

Sql Exception throw below error;

com.microsoft.sqlserver.jdbc.SQLServerException: Must declare the table variable "@temp_table".`rowcount` return 7 

所以我成功地填充了@temp_table，但我没有关闭stmt连接并尝试从此@temp_table中获取结果集.但是SQL说我还没有声明这个表.怎么可能?

so i succesfully filled the @temp_table, I don't close stmt connection and try to fetch Resultset from this @temp_table. But SQL says I have not declared this table yet. How is it possible ?

-已解决-

仅创建一个查询；

String query5 ="USE DBTwo\n" +
"DECLARE @temp_table table (column1 VARCHAR(60))\n" +
"insert into @temp_table (column1)\n" +
"select column1 from real_table(nolock)\n" +
"select column1 from @temp_table";

获取多个如下所示的结果；

Fetch multiple results like below;

try {
  boolean result = stmt.execute(query5);
  while (true)
   if(result){
    rs1 = stmt.getResultSet();
    while (rs1.next()) {
    System.out.println(rs1.getString(1)); 
    }

  } else {
    int updateCount = stmt.getUpdateCount();
    if (updateCount == -1){
    break; 
  }
    result = stmt.getMoreResults();
}
catch (SQLException sqlex){
   sqlex.printStackTrace();
}

推荐答案

从技术上讲，遵循JDBC规范，您一次只能执行一条语句. SQL Server JDBC驱动程序允许一次执行多个语句，这是对标准的偏离.还要注意，像USE databasename这样的操作与JDBC中建议的Connection方法(例如setCatalog)相反，因为这样做可能会使驱动程序处于不一致状态.

Technically following the JDBC spec you are only allowed to execute a single statement at a time. That the SQL Server JDBC driver allows the execution of multiple statements in one execute is a deviation of the standard. Also note that doing things like USE databasename is contrary the recommendation in JDBC to use Connection methods (like setCatalog) for this, as it may bring the driver in an inconsistent state.

关于您的特定问题，我有两种理论(我还没有验证这些是否是实际原因):

As to your specific problem, I have two theories (I haven't verified if these are the actual cause):

1. 您已启用自动提交.执行语句后，将提交连接，并放置临时表.
2. 语句执行后，查询上下文由服务器处理，因此后续执行无法访问临时表.

这些是理论，我相信1.最有可能.因此，请尝试禁用自动提交(Connection.setAutoCommit(false)).

These are theories, and I believe that 1. is the most likely. So try to disable auto commit (Connection.setAutoCommit(false)).

如果理论2是原因，那么您需要一次性执行它们，并处理多个结果(一个计数和一个结果集).为此，您将使用Statement.execute(...)并处理多个结果.有关示例，请参见我对 Java SQL:Statement.hasResultSet()?的回答.有关如何处理多个结果的信息.

If theory 2. is the cause, then you need to execute them in one go, and process multiple results (a count and a result set). For this you would use Statement.execute(...) and process for multiple results. See my answer to Java SQL: Statement.hasResultSet()? for an example on how to process multiple results.

如果您不想禁用自动提交，则最后一种解决方案也可能适用.

This last solution probably also works if you don't want to disable auto commit.

这篇关于通过Java获取创建并填充的MSSQL临时表的结果集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！