使用数据库API游标与JDBC和SQLServer来选择批处理结果 [英] Using a database API cursor with JDBC and SQLServer to select batch results
问题描述
已解决（请参阅下面的答案。）
真正的问题是，我的查询返回多个 ResultSet 对象，我从来没有遇到过。我已在下面发布了解决问题的代码。
PROBLEM
$ b
我有一个SQL Server数据库表，有几千行。我的目标是从源数据库中取回数据并将其写入第二个数据库。由于应用程序的内存限制，我不能一次拉回所有的数据。另外，由于这个特定表的模式（我没有控制），没有好的方法让我使用某种ID列来勾选行。
一个绅士在数据库管理员StackExchange帮助我把一些称为数据库API游标，基本上写了这个复杂的查询，我只需要把我的语句。当我在SQL Management Studio（SSMS）中运行查询时，它工作的很好。我得到所有的数据，一次一千行。
不幸的是，当我尝试将其转换为JDBC代码，我回到前千行。
QUESTION
可以使用JDBC检索数据库API游标，从其中拉出第一组行，允许光标前进，然后一次拉一个后续集合？ （在这种情况下，一次只能有一千行）
SQL CODE
这变得很复杂，所以我要打破它。
实际的查询可以简单或复杂。没关系。我在我的实验中尝试了几个不同的查询，他们都工作。你只是把它放在适当的地方的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;
- 打开游标并返回前1000行
EXECUTE @rc = sys.sp_cursoropen @cur OUTPUT
，'SELECT MyColumn FROM MyTable'
，@ scrollopt OUTPUT
，@ ccopt OUTPUT
，@ rowcount OUTPUT;
IF @rc<> 16 - FastForward光标自动关闭
BEGIN
- 命名游标，以便我们可以使用CURSOR_STATUS
EXECUTE sys.sp_cursoroption @cur
，2
，'MyCursorName' ;
- 直到光标自动关闭
WHILE CURSOR_STATUS（'global'，'MyCursorName'）= 1
BEGIN
EXECUTE sys.sp_cursorfetch @cur
，2
，0
，1000;
END;
END;
正如我所说，上述在数据库中创建一个游标，并要求数据库执行语句，跟踪（内部）其返回的数据，并且一次返回数据一千行。效果非常好。
JDBC CODE
问题。我的Java代码没有编译问题或运行时问题。我遇到的问题是，它只返回前千行。我不明白如何正确使用数据库游标。我尝试了Java基础的变化：
//希望获得所有的数据， 。
ResultSet rs = stmt.executeQuery（fq.getQuery（））;
while（rs.next（））{
System.out.println（rs.getString（MyColumn））;
}
我并不惊讶的结果，但所有的变化，我尝试产生相同的结果。
从我的研究，似乎JDBC在做数据库游标时，数据库是Oracle，但你必须设置返回的数据类型在结果集中作为Oracle游标对象。我猜想有与SQL Server类似的东西，但我还是找不到任何东西。
有没有人知道一种方法？
我包括完整的示例Java代码（非常丑陋）。
// FancyQuery.java
import java.sql。*;
public class FancyQuery {
//改编自http://dba.stackexchange.com/a/82806
String query =DECLARE @cur INTEGER\\ \\ n
+，\\\
+ - 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; \\\
+\\\
+ - 打开游标返回前1000行\ n
+EXECUTE @rc = sys.sp_cursoropen @cur OUTPUT\\\
+，'SELECT MyColumn FROM MyTable;'\\\
+，@ scrollopt OUTPUT\\\
+，@ ccopt OUTPUT \
+，@ rowcount OUTPUT; \\\
+\\\
+IF @rc<> 16 - FastForward光标自动关闭\\\
+BEGIN \\\
+ - 命名光标，以便我们可以使用CURSOR_STATUS \\\
+EXECUTE sys .sp_cursoroption @ cur \\\
+，2 \\\
+，'MyCursorName'; \\\
+\\\
+ - 直到光标自动关闭\\\
+WHILE CURSOR_STATUS（'global'，'MyCursorName'）= 1 \\\
+BEGIN\\\
+EXECUTE sys.sp_cursorfetch @ cur \\\
+，2 \\\
+，0 \\\
+，1000; \\\
+END; \\\
+END; \\\
;
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）;
连接conn = DriverManager.getConnection（dbUrl，用户，密码）;
语句stmt = conn.createStatement（）;
//我们期望一次获得1000行。
ResultSet rs = stmt.executeQuery（fq.getQuery（））;
while（rs.next（））{
System.out.println（rs.getString（MyColumn））;
}
//唉，我们只得到1000行，总计。
rs.close（）;
stmt.close（）;
conn.close（）;
}
}
I想出来。
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 实例中的数据。我需要的是一种方法，不仅仅是遍历ResultSet中的每一行，而是遍历整个ResultSet集合。这是上面的代码。
SOLVED (See answer below.)
I did not understand my problem within the proper context. The real issue was that my query was returning multiple ResultSet objects, and I had never come across that before. I have posted code below that solves the problem.
PROBLEM
I have an SQL Server database table with many thousand rows. My goal is to pull the data back from the source database and write it to a second database. Because of application memory constraints, I will not be able to pull the data back all at once. Also, because of this particular table's schema (over which I have no control) there is no good way for me to tick off the rows using some sort of ID column.
A gentleman over at the Database Administrators StackExchange helped me out by putting together something called a database API cursor, and basically wrote this complicated query that I only need to drop my statement into. When I run the query in SQL Management Studio (SSMS) it works great. I get all the data back, a thousand rows at a time.
Unfortunately, when I try to translate this into JDBC code, I get back the first thousand rows only.
QUESTION
Is it possible using JDBC to retrieve a database API cursor, pull the first set of rows from it, allow the cursor to advance, and then pull the subsequent sets one at a time? (In this case, a thousand rows at a time.)
SQL CODE
This gets complicated, so I'm going to break it up.
The actual query can be simple or complicated. It doesn't matter. I've tried several different queries during my experimentation and they all work. You just basically drop it into the the SQL code in the appropriate place. So, let's take this simple statement as our query:
SELECT MyColumn FROM MyTable;
The actual SQL database API cursor is far more complicated. I will print it out below. You can see the above query buried in it:
-- 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;
As I've said, the above creates a cursor in the database and asks the database to execute the statement, keep track (internally) of the data it's returning, and return the data a thousand rows at a time. It works great.
JDBC CODE
Here's where I'm having the problem. I have no compilation problems or run-time problems with my Java code. The problem I am having is that it returns only the first thousand rows. I don't understand how to utilize the database cursor properly. I have tried variations on the Java basics:
// 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"));
}
I'm not surprised by the results, but all of the variations I've tried produce the same results.
From my research it seems like the JDBC does something with database cursors when the database is Oracle, but you have to set the data type returned in the result set as an Oracle cursor object. I'm guessing there is something similar with SQL Server, but I have been unable to find anything yet.
Does anyone know of a way?
I'm including example Java code in full (as ugly as that gets).
// FancyQuery.java
import java.sql.*;
public class FancyQuery {
// Adapted from http://dba.stackexchange.com/a/82806
String query = "DECLARE @cur INTEGER\n"
+ " ,\n"
+ " -- FAST_FORWARD | AUTO_FETCH | AUTO_CLOSE\n"
+ " @scrollopt INTEGER = 16 | 8192 | 16384\n"
+ " ,\n"
+ " -- READ_ONLY, CHECK_ACCEPTED_OPTS, READ_ONLY_ACCEPTABLE\n"
+ " @ccopt INTEGER = 1 | 32768 | 65536\n"
+ " ,@rowcount INTEGER = 1000\n"
+ " ,@rc INTEGER;\n"
+ "\n"
+ "-- Open the cursor and return the first 1,000 rows\n"
+ "EXECUTE @rc = sys.sp_cursoropen @cur OUTPUT\n"
+ " ,'SELECT MyColumn FROM MyTable;'\n"
+ " ,@scrollopt OUTPUT\n"
+ " ,@ccopt OUTPUT\n"
+ " ,@rowcount OUTPUT;\n"
+ " \n"
+ "IF @rc <> 16 -- FastForward cursor automatically closed\n"
+ "BEGIN\n"
+ " -- Name the cursor so we can use CURSOR_STATUS\n"
+ " EXECUTE sys.sp_cursoroption @cur\n"
+ " ,2\n"
+ " ,'MyCursorName';\n"
+ "\n"
+ " -- Until the cursor auto-closes\n"
+ " WHILE CURSOR_STATUS('global', 'MyCursorName') = 1\n"
+ " BEGIN\n"
+ " EXECUTE sys.sp_cursorfetch @cur\n"
+ " ,2\n"
+ " ,0\n"
+ " ,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();
}
}
I figured it out.
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();
}
After some additional googling, I found a bit of code posted back in 2004:
http://www.coderanch.com/t/300865/JDBC/databases/SQL-Server-JDBC-Registering-cursor
The gentleman who posted the snippet that I found helpful (Julian Kennedy) suggested: "Read the Javadoc for getUpdateCount() and getMoreResults() for a clear understanding." I was able to piece it together from that.
Basically, I don't think I understood my problem well enough at the outset in order to phrase it correctly. What it comes down to is that my query will be returning the data in multiple ResultSet instances. What I needed was a way to not merely iterate through each row in a ResultSet but, rather, iterate through the entire set of ResultSets. That's what the code above does.
这篇关于使用数据库API游标与JDBC和SQLServer来选择批处理结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
