Java 返回多个结果集的查询

Queries returning multiple result sets

提问by Vishu Singhvi

I have a MSSQLdatabase and am running the following query:

我有一个MSSQL数据库并且正在运行以下查询：

select * from projects; select * from user

The above query returns two result sets at once, and I cannot fire both queries separately. How can I handle both the result set at once in a Java class?

上面的查询一次返回两个结果集，我不能分别触发这两个查询。如何在 Java 类中同时处理两个结果集？

采纳答案by Aaron Digulla

Correct code to process multiple ResultSets returned by a JDBC statement:

处理ResultSetJDBC 语句返回的多个s 的正确代码：

PreparedStatement stmt = ...;
boolean isResultSet = stmt.execute();

int count = 0;
while(true) {
    if(isResultSet) {
        rs = stmt.getResultSet();
        while(rs.next()) {
            processEachRow(rs);
        }

        rs.close();
    } else {
        if(stmt.getUpdateCount() == -1) {
            break;
        }

        log.info("Result {} is just a count: {}", count, stmt.getUpdateCount());
    }

    count ++;
    isResultSet = stmt.getMoreResults();
}

Important bits:

重要的位：

• getMoreResults()and execute()return falseto indicate that the result of the statement is just a number and not a ResultSet.
• You need to check stmt.getUpdateCount() == -1to know if there are more results.
• Make sure you either close the result sets or use stmt.getMoreResults(Statement.CLOSE_CURRENT_RESULT)

• getMoreResults()并execute()返回false以指示语句的结果只是一个数字而不是 a ResultSet。
• 您需要检查stmt.getUpdateCount() == -1以了解是否有更多结果。
• 确保您关闭结果集或使用 stmt.getMoreResults(Statement.CLOSE_CURRENT_RESULT)

回答by Vishu Singhvi

The UNION ALL query allows you to combine the result sets of 2 or more "select" queries. It returns all rows (even if the row exists in more than one of the "select" statements).

UNION ALL 查询允许您组合 2 个或更多“选择”查询的结果集。它返回所有行（即使该行存在于多个“select”语句中）。

Each SQL statement within the UNION ALL query must have the same number of fields in the result sets with similar data types.........

UNION ALL 查询中的每个 SQL 语句在结果集中必须具有相同数量的字段，并且具有相似的数据类型......

select * from projects
UNION ALL
select * from user

回答by Raghav

The answer: it is NOTpossible. The only way: Run them as separate queries.

答案：它是不是可能。唯一的方法：将它们作为单独的查询运行。

回答by Honza

You can use Statement.execute(), getResultSet();

您可以使用 Statement.execute(), getResultSet();

PreparedStatement stmt = ... prepare your statement result
boolean hasResults = stmt.execute();
while (hasResults) {
    ResultSet rs = stmt.getResultSet();
    ... your code parsing the results ...
    hasResults = stmt.getMoreResults();
}

回答by Ritesh Chiddarwar

public static void executeProcedure(Connection con) {
   try {
      CallableStatement stmt = con.prepareCall(...);
      .....  //Set call parameters, if you have IN,OUT, or IN/OUT parameters

      boolean results = stmt.execute();
      int rsCount = 0;

      //Loop through the available result sets.
     while (results) {
           ResultSet rs = stmt.getResultSet();
           //Retrieve data from the result set.
           while (rs.next()) {
        ....// using rs.getxxx() method to retrieve data
           }
           rs.close();

        //Check for next result set
        results = stmt.getMoreResults();
      } 
      stmt.close();
   }
   catch (Exception e) {
      e.printStackTrace();
   }
}

回答by Dmitry

Yes, You can. See this MSDN article https://msdn.microsoft.com/en-us/library/ms378758(v=sql.110).aspx

是的你可以。请参阅此 MSDN 文章 https://msdn.microsoft.com/en-us/library/ms378758(v=sql.110).aspx

public static void executeStatement(Connection con) {
   try {
      String SQL = "SELECT TOP 10 * FROM Person.Contact; " +
                   "SELECT TOP 20 * FROM Person.Contact";
      Statement stmt = con.createStatement();
      boolean results = stmt.execute(SQL);
      int rsCount = 0;

      //Loop through the available result sets.
     do {
        if(results) {
           ResultSet rs = stmt.getResultSet();
           rsCount++;

           //Show data from the result set.
           System.out.println("RESULT SET #" + rsCount);
           while (rs.next()) {
              System.out.println(rs.getString("LastName") + ", " + rs.getString("FirstName"));
           }
           rs.close();
        }
        System.out.println();
        results = stmt.getMoreResults();
        } while(results);
      stmt.close();
      }
   catch (Exception e) {
      e.printStackTrace();
   }
}

I've tested that and it works fine.

我已经测试过了，它工作正常。

回答by John Medeiros

Before use java, you need look at the RESULT SETS clause.

在使用 java 之前，您需要查看 RESULT SETS 子句。

MSSQL has this feature that can help you with your java code, in a more practical way.

MSSQL 具有此功能，可以以更实用的方式帮助您处理 Java 代码。

This example will exec two queries:

此示例将执行两个查询：

EXEC('SELECT id_person, name, age FROM dbo.PERSON; SELECT id_url, url FROM dbo.URL;')
WITH RESULT SETS
(
  (
    id_person BIGINT,
    name VARCHAR(255),
    age TINYINT
  ),
  (
    id_url BIGINT,
    url VARCHAR(2000)
  )
);

You can use stored procedures with RESULT SETS as well.

您也可以将存储过程与 RESULT SETS 一起使用。

More about: https://technet.microsoft.com/en-us/library/ms188332(v=sql.110).aspx

更多信息：https: //technet.microsoft.com/en-us/library/ms188332(v=sql.110).aspx