将数据库 API 游标与 JDBC 和 SQLServer 结合使用来选择批处理结果

Question

已解决（见下面的答案。

我没有在适当的背景下理解我的问题。真正的问题是我的查询返回了多个ResultSet对象，而我以前从未遇到过这种情况。我在下面发布了解决问题的代码。

---

问题

我有一个包含数千行的 SQL Server 数据库表。我的目标是将数据从源数据库拉回，并将其写入第二个数据库。由于应用程序内存限制，我将无法一次将所有数据拉回。此外，由于这个特定表的架构（我无法控制），我没有好方法使用某种 ID 列勾选行。

数据库管理员 StackExchange 的一位先生帮助我把一个叫做数据库 API 游标的东西放在一起，基本上写了这个复杂的查询，我只需要把我的语句放进去。当我在 SQL 管理工作室 （SSMS） 中运行查询时，它工作得很好。我一次取回一千行所有数据。

不幸的是，当我尝试将其转换为 JDBC 代码时，我只返回前千行。

问题

是否可以使用 JDBC 检索数据库 API 游标，从中拉取第一组行，允许游标前进，然后一次拉取一组后续游标？（在本例中，一次一千行。

SQL 代码

这很复杂，所以我要把它分解。

实际查询可以简单，也可以复杂。这不重要。我在实验过程中尝试了几个不同的查询，它们都有效。你基本上只是把它放到SQL代码的适当位置。因此，让我们将这个简单的语句作为我们的查询：

SELECT MyColumn FROM MyTable; 

实际的 SQL 数据库 API 游标要复杂得多。我将在下面打印出来。你可以看到上面的查询埋藏在其中：

-- http://dba.stackexchange.com/a/82806
DECLARE @cur INTEGER
    ,
    -- FAST_FORWARD | AUTO_FETCH | AUTO_CLOSE
    @scrollopt INTEGER = 16 | 8192 | 16384
    ,
    -- READ_ONLY, CHECK_ACCEPTED_OPTS, READ_ONLY_ACCEPTABLE
    @ccopt INTEGER = 1 | 32768 | 65536
    ,@rowcount INTEGER = 1000
    ,@rc INTEGER;
-- Open the cursor and return the first 1,000 rows
EXECUTE @rc = sys.sp_cursoropen @cur OUTPUT
    ,'SELECT MyColumn FROM MyTable'
    ,@scrollopt OUTPUT
    ,@ccopt OUTPUT
    ,@rowcount OUTPUT;
IF @rc <> 16 -- FastForward cursor automatically closed
BEGIN
    -- Name the cursor so we can use CURSOR_STATUS
    EXECUTE sys.sp_cursoroption @cur
        ,2
        ,'MyCursorName';
    -- Until the cursor auto-closes
    WHILE CURSOR_STATUS('global', 'MyCursorName') = 1
    BEGIN
        EXECUTE sys.sp_cursorfetch @cur
            ,2
            ,0
            ,1000;
    END;
END;

正如我所说，上面在数据库中创建一个游标，并要求数据库执行语句，跟踪（内部）它返回的数据，并一次返回一千行数据。效果很好。

JDBC 代码

这就是我遇到问题的地方。我的 Java 代码没有编译问题或运行时问题。我遇到的问题是它只返回前千行。我不明白如何正确使用数据库游标。我已经尝试了Java基础知识的变体：

// Hoping to get all of the data, but I only get the first thousand.
ResultSet rs = stmt.executeQuery(fq.getQuery());
while (rs.next()) {
    System.out.println(rs.getString("MyColumn"));
}

我对结果并不感到惊讶，但我尝试过的所有变体都产生了相同的结果。

根据我的研究，当数据库是 Oracle 时，JDBC 似乎对数据库游标做了一些事情，但您必须将结果集中返回的数据类型设置为 Oracle 游标对象。我猜SQL Server也有类似的东西，但我还没有找到任何东西。

有谁知道办法？

我完整地包含示例 Java 代码（尽管这很丑陋）。

// FancyQuery.java
import java.sql.*;
public class FancyQuery {
    // Adapted from http://dba.stackexchange.com/a/82806
    String query = "DECLARE @cur INTEGERn"
                 + "    ,n"
                 + "    -- FAST_FORWARD | AUTO_FETCH | AUTO_CLOSEn"
                 + "    @scrollopt INTEGER = 16 | 8192 | 16384n"
                 + "    ,n"
                 + "    -- READ_ONLY, CHECK_ACCEPTED_OPTS, READ_ONLY_ACCEPTABLEn"
                 + "    @ccopt INTEGER = 1 | 32768 | 65536n"
                 + "    ,@rowcount INTEGER = 1000n"
                 + "    ,@rc INTEGER;n"
                 + "n"
                 + "-- Open the cursor and return the first 1,000 rowsn"
                 + "EXECUTE @rc = sys.sp_cursoropen @cur OUTPUTn"
                 + "    ,'SELECT MyColumn FROM MyTable;'n"
                 + "    ,@scrollopt OUTPUTn"
                 + "    ,@ccopt OUTPUTn"
                 + "    ,@rowcount OUTPUT;n"
                 + "    n"
                 + "IF @rc <> 16 -- FastForward cursor automatically closedn"
                 + "BEGINn"
                 + "    -- Name the cursor so we can use CURSOR_STATUSn"
                 + "    EXECUTE sys.sp_cursoroption @curn"
                 + "        ,2n"
                 + "        ,'MyCursorName';n"
                 + "n"
                 + "    -- Until the cursor auto-closesn"
                 + "    WHILE CURSOR_STATUS('global', 'MyCursorName') = 1n"
                 + "    BEGINn"
                 + "        EXECUTE sys.sp_cursorfetch @curn"
                 + "            ,2n"
                 + "            ,0n"
                 + "            ,1000;n"
                 + "    END;n"
                 + "END;n";
    public String getQuery() {
        return this.query;
    }
    public static void main(String[ ] args) throws Exception {
        String dbUrl = "jdbc:sqlserver://tc-sqlserver:1433;database=MyBigDatabase";
        String user = "mario";
        String password = "p@ssw0rd";
        String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
        FancyQuery fq = new FancyQuery();
        Class.forName(driver);
        Connection conn = DriverManager.getConnection(dbUrl, user, password);
        Statement stmt = conn.createStatement();
        // We expect to get 1,000 rows at a time.
        ResultSet rs = stmt.executeQuery(fq.getQuery());
        while (rs.next()) {
            System.out.println(rs.getString("MyColumn"));
        }
        // Alas, we've only gotten 1,000 rows, total.
        rs.close();
        stmt.close();
        conn.close();
    }
}

Answer 1

我想通了。

stmt.execute(fq.getQuery());
ResultSet rs = null;
for (;;) {
    rs = stmt.getResultSet();
    while (rs.next()) {
        System.out.println(rs.getString("MyColumn"));
    }
    if ((stmt.getMoreResults() == false) && (stmt.getUpdateCount() == -1)) {
        break;
    }
}
if (rs != null) {
    rs.close();
}

经过一些额外的谷歌搜索，我发现了一些在2004年发布的代码：

http://www.coderanch.com/t/300865/JDBC/databases/SQL-Server-JDBC-Registering-cursor

发布我觉得有帮助的片段的那位先生（Julian Kennedy）建议："阅读Javadoc以获取getUpdateCount（）和getMoreResults（）以获得清晰的理解。我能够从中拼凑出来。

基本上，我认为我一开始就不够

了解我的问题，无法正确表达。归根结底，我的查询将在多个ResultSet实例中返回数据。我需要的是一种不仅循环访问结果集中的每一行，而且循环访问整个结果集的方法。这就是上面的代码所做的。

Answer 2

如果您想要表中的所有记录，只需执行"从表中选择 *"。

在块中检索的唯一原因是数据是否有一些中间位置：例如，如果您在屏幕上显示它，或者将其存储在内存中。

如果您只是从一个读取并插入到另一个，只需从第一个读取所有内容即可。尝试批量检索不会获得更好的性能。如果有差异，它将是负数。以带回所有内容的方式构建查询。 JDBC软件将处理您需要的所有其他分解和重组。

但是，您应该批量更新/插入方面。

设置将在两个连接上创建两个语句：

Statement stmt = null;
ResultSet rs = null;
PreparedStatement insStmt = null;
stmt = conDb1.createStatement();
insStmt = conDb2.prepareStament("insert into tgt_db2_table (?,?,?,?,?......etc. ?,?) ");
rs = stmt.executeQuery("select * from src_db1_table");

然后，正常循环选择，但在目标上使用批处理。

    int batchedRecordCount = 0;
    while (rs.next()) {
        System.out.println(rs.getString("MyColumn"));
        //Here you read values from the cursor and set them to the insStmt ...
        String field1 = rs.getString(1);
        String field2 = rs.getString(2);
        int field3 = rs.getInt(3);
        //--- etc. 
        insStmt.setString(1, field1);
        insStmt.setString(2, field2);
        insStmt.setInt(3, field3);
        //----- etc. for all the fields
        batchedRecordCount++;
        insStmt.addBatch();
        if (batchRecordCount > 1000) {
          insStmt.executeBatch();
        }
    }
    if (batchRecordCount > 0) {
       //Finish of the final (partial) set of records
       insStmt.executeBatch();
    }
    //Close resources...