java 流式结果集错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3550263/
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
Streaming ResultSet Error
提问by JJunior
I am trying to run multiple MySQL queries which build up on each other: i.e field value of one element in each row is used as input for another query.
我正在尝试运行多个相互建立的 MySQL 查询：即每行中一个元素的字段值用作另一个查询的输入。
I end up getting the following error:
我最终收到以下错误：
java.sql.SQLException: Streaming result set com.mysql.jdbc.RowDataDynamic@174cc1f is still active. No statements may be issued when any streaming result sets are open and in use on a given connection. Ensure that you have called .close() on any active streaming result sets before attempting more queries.
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:914)
at com.mysql.jdbc.MysqlIO.checkForOutstandingStreamingData(MysqlIO.java:2074)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1484)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3170)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3099)
at com.mysql.jdbc.Statement.executeQuery(Statement.java:1169)
at Stats.readInterfaces(Stats.java:105)
at Stats.connect(Stats.java:63)
at automateExport.main(automateExport.java:15)
I have called .close()after every ResultSetand Statementof the query. I guess we cannot have multiple resultsets open at one time. Is there any way to get around this problem?
我呼吁.close()每一个之后ResultSet和Statement查询。我想我们不能同时打开多个结果集。有什么办法可以解决这个问题吗？
Here is the relevant code:
这是相关的代码：
public class Stats {
public static int UTC = 0;
public String interfaceId = "no value";
public String rId = "no value";
public String NL = System.getProperty("line.separator");
public String CSV = ",";
public static String startTime,endTime,performanceTable =null;
public static int outputType = 1;
public String pTable = "('2010-7-13 00:00')";
public String start = "('2010-7-13 09:00')";
public String end = "('2010-7-13 17:00')";
Connection conn;
Statement stmtRouter, stmtInterface, stmtTime, stmtD;
String query;
ResultSet rsRouter, rsInterface, rsD, rsTime;
public Connection connect(String db_connect_str,String db_userid, String db_password) {
String routerName,routerId = null, routerNetwork = null;
// inputfile - csv
try {
// to bifurcate heap memory error
Class.forName("com.mysql.jdbc.Driver").newInstance();
conn = DriverManager.getConnection(db_connect_str,db_userid, db_password);
stmtRouter = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
java.sql.ResultSet.CONCUR_READ_ONLY);
stmtRouter.setFetchSize(Integer.MIN_VALUE);
query = "Select r.name,r.rid,r.network FROM router AS r Where r.network = 'ITPN'";
String append = null;
// writing to file
performanceTable = readTime(pTable);
startTime = readTime(start);
endTime = readTime(end);
rsRouter = stmtRouter.executeQuery(query);
while (rsRouter.next()) {
routerName = rsRouter.getString(1);
System.out.println(routerName);
// routerId = rsRouter.getString("rid");
// routerNetwork = rsRouter.getString("network");
append = routerName+CSV+routerId+CSV+routerNetwork;
readInterfaces(routerId,startTime,endTime,performanceTable, append);
}
stmtRouter.close() ;
rsRouter.close();
// output(2,input);
// output(outputType , input);
} catch(Exception e) {
e.printStackTrace();
conn = null;
}
return conn;
}
private String readTime(String time) throws SQLException {
stmtTime = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
java.sql.ResultSet.CONCUR_READ_ONLY);
stmtTime.setFetchSize(Integer.MIN_VALUE);
query = "Select unix_timestamp"+time;
rsTime = stmtTime.executeQuery(query);
String unixTime = null;
while(rsTime.next()){
unixTime = rsTime.getString(1);
System.out.println(unixTime);
}
rsTime.close();
stmtTime.close();
return unixTime;
}
private void readInterfaces(String routerId, String startTime, String endTime, String performanceTable, String append) throws SQLException, IOException {
String interfaceId, iDescp, iStatus = null;
String dtime, ingress, egress = null;
stmtInterface = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
java.sql.ResultSet.CONCUR_READ_ONLY);
stmtInterface.setFetchSize(Integer.MIN_VALUE);
stmtD = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
java.sql.ResultSet.CONCUR_READ_ONLY);
stmtD.setFetchSize(Integer.MIN_VALUE);
query = " Select i.id,i.description, i.status from interface as i Where i.rid = " + routerId +" And i.status = 'active'";
rsInterface = stmtInterface.executeQuery(query);
String input = "inputData.txt";
BufferedWriter fw = new BufferedWriter(new FileWriter(input));
stmtInterface.close();
while(rsInterface.next()){
interfaceId = rsInterface.getString("id");
iDescp = rsInterface.getString("description");
iStatus = rsInterface.getString("status");
if(!iStatus.equals("active")){
/* performance table query*/
query = " Select d.dtime,d.ifInOctets, d.ifOutOctets from "+performanceTable+"_1_60" +" AS d Where d.id = " +
interfaceId + "AND dtime BETWEEN " +startTime+ " AND "+ endTime + " Order By d.id";
rsD = stmtD.executeQuery(query);
while(rsD.next()){
dtime = rsD.getString("dtime");
ingress = rsD.getString("ifInOctets");
egress = rsD.getString("ifOutOctets");
fw.write(append + CSV + interfaceId+CSV+iDescp+CSV+dtime+CSV+ingress+CSV+egress+NL);
}// end of while
rsD.close();
stmtD.close();
}
}
fw.close();
// rsInterface.close() ;
// stmtInterface.close();
}
}
采纳答案by spbfox
You cannot have more than one result set being open per connection. I believe here is the reason for the failure:
每个连接打开的结果集不能超过一个。我相信这是失败的原因：
You call readInterfaces(routerId,startTime,endTime,performanceTable, append) (where you open new resultset) before you close Router resultset:
在关闭路由器结果集之前，您调用 readInterfaces(routerId,startTime,endTime,performanceTable, append)（在其中打开新结果集）：
readInterfaces(routerId,startTime,endTime,performanceTable, append);
}
stmtRouter.close() ;
rsRouter.close();
I would move close statements before the call to readInterfaces(...). I would also close the result set and statement in opposite order (resultset first).
我会在调用 readInterfaces(...) 之前移动 close 语句。我还会以相反的顺序关闭结果集和语句（首先是结果集）。
回答by Dave Costa
Exactly, as the error says, you cannot issue additional queries over the same connection when you have a streamingresult set open. Relevant documentation is here.
确切地说，正如错误所说，当您打开流结果集时，您无法通过同一连接发出其他查询。相关文档在这里。
So, the obvious solution would be not to use a streaming result set for the driving query. The downside of this is that it will use more memory. The rather obscure comment at the top of your code implies that maybe someone tried this already and had memory issues.
因此，显而易见的解决方案是不对驾驶查询使用流式结果集。这样做的缺点是它会使用更多的内存。代码顶部相当晦涩的注释暗示可能有人已经尝试过并且遇到了内存问题。
Anyway, there is a better solution. This is a classic example of over-proceduralization. You are doing work in your code that could be better handled by the database engine. Instead of executing single-table queries that drive each other, you can combine those into one query using a join:
无论如何，有一个更好的解决方案。这是过度程序化的典型例子。您正在处理可以由数据库引擎更好地处理的代码。您可以使用连接将它们组合成一个查询，而不是执行相互驱动的单表查询：
Select r.name,r.rid,r.network,i.id,i.description, i.status
FROM router AS r JOIN interface as i ON i.rid = r.rid
Where r.network = 'ITPN'
AND i.status='active'
Then, you have a third "nested" query, which you can incorporate with an additional join. I think it would be:
然后，您有第三个“嵌套”查询，您可以将其与附加连接合并。我想会是：
Select r.name,r.rid,r.network,i.id,i.description, i.status,d.dtime,d.ifInOctets, d.ifOutOctets
FROM router AS r JOIN interface as i ON i.rid = r.rid
JOIN <performanceTable>_1_60 as d ON d.id = i.id
Where r.network = 'ITPN'
AND i.status='active'
AND dtime BETWEEN <startTime> AND <endTime>
Order By d.id
You probably don't need all those columns in the final select list, but I haven't pored through your code to see what is really being used.
您可能不需要最终选择列表中的所有这些列，但我没有仔细阅读您的代码以查看真正使用的内容。
回答by a1ex07
You definitely shouldn't close Statement object before you end working with ResultSet (function readInterfaces).
在结束使用 ResultSet（函数 readInterfaces）之前，您绝对不应该关闭 Statement 对象。
rsInterface = stmtInterface.executeQuery(query);
String input = "inputData.txt";
BufferedWriter fw = new BufferedWriter(new FileWriter(input));
stmtInterface.close(); // Don't do this!!! It should be when you are done with rsInterface)
while(rsInterface.next()){
回答by Vinod Cyriac
Connectionobject should not hold multiple ResultSetobject at a time.
Connection对象不应一次容纳多个ResultSet对象。
After creation of ResultSetand Statementobjects, each has to closed explicitly like,
在创建ResultSet和Statement对象之后，每个对象都必须明确关闭，例如，
resultSet.close()
statement.close()
