Java 如何从单个 CallableStatement 获得多个结果集？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20254115/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do you get multiple resultset from a single CallableStatement?
提问by James
When I call the stored proc from command line I get the following.
当我从命令行调用存储过程时，我得到以下信息。
CALL `events`.`get_event_by_id`(10)
+---------+----------+-------------+---------------------+---------------------+---------------------+--------+----------+
| evet_id | name | description | starttime | endtime | last_modified | active | addre_id |
+---------+----------+-------------+---------------------+---------------------+---------------------+--------+----------+
| 10 | samole 3 | sanely | 2013-11-27 17:37:00 | 2013-11-27 18:09:00 | 2013-11-27 09:37:42 | 1 | 20 |
+---------+----------+-------------+---------------------+---------------------+---------------------+--------+----------+
1 row in set (0.00 sec)
+---------+------------+---------+
| user_id | username | picture |
+---------+------------+---------+
| 1 | jamess2000 | NULL |
| 2 | yferna2012 | NULL |
+---------+------------+---------+
2 rows in set (0.00 sec)
+----------+------------------------------+---------------------+-------------+--------+
| addre_id | street | name | description | active |
+----------+------------------------------+---------------------+-------------+--------+
| 20 | Schieffelin | Manhattan Ville Loc | NULL | 1 |
+----------+------------------------------+---------------------+-------------+--------+
Here is a snippet of my Java Code
这是我的 Java 代码片段
String SP_GET_EVENT_BY_ID = "CALL `events`.`get_event_by_id`(?)";
String PROC_PARAM_EVENT_ID = "evet_id";
mCallableStatement = mConnection.prepareCall(SP_GET_EVENT_BY_ID);
mCallableStatement.setInt(10, PROC_PARAM_EVENT_ID);
When I execute the statement, only the event_table results are returned. I read the query with the following:
当我执行语句时，只返回 event_table 结果。我阅读了以下查询：
ResultSet reader = mCallableStatement.executeQuery();
while(reader.next())
{
//etc..... here i assign db values to properties.
}
I am trying to avoid making multiple request to the database because it's extremely slow (300 ms depending on on how many results)
我试图避免向数据库发出多个请求，因为它非常慢（300 毫秒，取决于结果的数量）
Is it even possible?
甚至有可能吗？
采纳答案by James
I found this great article. http://www.herongyang.com/JDBC/MySQL-CallableStatement-Multiple-ResulSet.html
我发现了这篇很棒的文章。http://www.herongyang.com/JDBC/MySQL-CallableStatement-Multiple-ResulSet.html
Here is the code from that article.
这是那篇文章中的代码。
/**
* MySqlCallMultipleResultSet.java
* Copyright (c) 2007 by Dr. Herong Yang. All rights reserved.
*/
import java.sql.*;
public class MySqlCallMultipleResultSet {
public static void main(String [] args) {
Connection con = null;
try {
com.mysql.jdbc.jdbc2.optional.MysqlDataSource ds
= new com.mysql.jdbc.jdbc2.optional.MysqlDataSource();
ds.setServerName("localhost");
ds.setPortNumber(3306);
ds.setDatabaseName("HerongDB");
ds.setUser("Herong");
ds.setPassword("TopSecret");
con = ds.getConnection();
// Create CallableStatement
CallableStatement cs = con.prepareCall("CALL HeadTail(?)");
// Register OUT parameters
cs.registerOutParameter(1, java.sql.Types.INTEGER);
// Execute the CALL statement and expecting multiple result sets
boolean isResultSet = cs.execute();
// First ReulstSet object
if (!isResultSet) {
System.out.println("The first result is not a ResultSet.");
return;
}
// First ReulstSet object
System.out.println("Head of the table:");
ResultSet res = cs.getResultSet();
while (res.next()) {
System.out.println(" "+res.getInt("ID")
+", "+res.getString("FirstName")
+", "+res.getString("LastName")
+", "+res.getTimestamp("ModTime"));
}
res.close();
// Move to the next result
isResultSet = cs.getMoreResults();
if (!isResultSet) {
System.out.println("The next result is not a ResultSet.");
return;
}
// Second ReulstSet object
System.out.println("Tail of the table:");
res = cs.getResultSet();
while (res.next()) {
System.out.println(" "+res.getInt("ID")
+", "+res.getString("FirstName")
+", "+res.getString("LastName")
+", "+res.getTimestamp("ModTime"));
}
res.close();
// Retrieve OUT parameters
System.out.println("Total number of records: "+cs.getInt(1));
// Close resource
cs.close();
con.close();
} catch (Exception e) {
System.err.println("Exception: "+e.getMessage());
e.printStackTrace();
}
}
}
回答by Indunil Girihagama
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 splashout
According to the Javadocs for Statement.getMoreResults(), you should also check that getUpdateCount() != -1when looking for more result sets.
根据Statement.getMoreResults()的 Javadocs ，您还应该getUpdateCount() != -1在查找更多结果集时进行检查。
This code works for Sybase and probably a lot of other databases except Oracle:
此代码适用于 Sybase 以及可能适用于除 Oracle 之外的许多其他数据库：
Connection conn = null;
try {
Class.forName("com.sybase.jdbc3.jdbc.SybDriver");
conn = DriverManager.getConnection(url, username, password);
CallableStatement call = conn.prepareCall("{call events.get_event_by_id(?)}");
call.setInt("@evet_id", 10);
boolean moreResults = call.execute();
int updateCount = call.getUpdateCount();
while (moreResults || updateCount != -1) {
if (moreResults) {
ResultSet rs = call.getResultSet();
processResults(rs);
}
moreResults = call.getMoreResults();
updateCount = call.getUpdateCount();
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (Exception e) {}
}
https://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#getMoreResults()
https://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#getMoreResults()
