java - 流式处理结果集错误

Question

我正在尝试运行多个相互构建的 MySQL 查询:即每行中一个元素的字段值用作另一个查询的输入。

我最终得到以下错误:

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)

我在查询的每个 ResultSet 和 Statement 之后调用了 .close()。我想我们不能同时打开多个结果集。有什么办法可以解决这个问题吗？

相关代码如下:

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();

    }

}

Answer 1

确切地说，正如错误所说，当您打开了一个流 结果集时，您不能通过同一连接发出其他查询。相关文档为here .

因此，显而易见的解决方案是不为驾驶查询使用流式结果集。这样做的缺点是它会使用更多的内存。代码顶部相当晦涩的注释暗示可能有人已经尝试过此操作并且存在内存问题。

总之，有更好的解决办法。这是过度程序化的典型例子。您正在使用可以由数据库引擎更好地处理的代码进行工作。您可以使用联接将这些查询组合成一个查询，而不是执行相互驱动的单表查询:

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'

然后，您有第三个“嵌套”查询，您可以将其与附加连接合并。我认为会是:

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

您可能不需要最终选择列表中的所有这些列，但我还没有仔细阅读您的代码以查看真正使用的是什么。