java - 运行多个选择查询 apache derby 时出错

Question

我无法使用多个选择查询运行下面的代码。我可以单独运行下面的查询，但我想一起运行并将它们的结果存储在 ArrayList 中。我得到的错误是 java.sql.SQLException: ResultSet not open. Operation 'next' not permitted. Verify that autocommit is off.

有什么建议吗？或者有更好的方法来做到这一点吗？谢谢

public ArrayList<String> getTotalCountBasicQueries() {

        ArrayList<String> totalCount = new ArrayList();
            Statement stmt = null;

            stmt = conn.createStatement();
            conn.setAutoCommit(false);

            try {

                String q1 = "select count query";
                String q2 = "select count query2";
                String q3 = "select count query3 ";

                ResultSet rs = stmt.executeQuery(q1);
                ResultSet rs2 = stmt.executeQuery(q2);
                ResultSet rs3 = stmt.executeQuery(q3);

                while (rs.next()) {
                    totalBasicCount.add(rs.getString(1));
                }

                while (rs2.next()) {
                    totalCount.add(rs2.getString(1));
                }
                while (rs3.next()) {
                    totalCount.add(rs3.getString(1));
                }


                rs.close();
                rs2.close();
                rs3.close();
                stmt.close();
            } catch (Throwable e) {
                System.out.println("Table fetch failed or result data failed");
            } finally {
                if (stmt != null) {
                    try {
                        stmt.close();
                        System.out.println("Could not close query");
                    } catch (SQLException ex) {
                        System.out.println("Could not close query");
                    }
                }

                return totalBasicCount;

            }
        }
    }

Answer 1

请参阅 ResultSet 的 javadoc:

A ResultSet object is automatically closed when the Statement object that generated it is closed, re-executed, or used to retrieve the next result from a sequence of multiple results.

根据规范，您不能为一个唯一的语句打开多个结果集。然而，一些 jdbc 驱动程序允许这样做

尝试:

ResultSet rs = stmt.executeQuery(q1);
while (rs.next()) {
   totalBasicCount.add(rs.getString(1));
}
ResultSet rs2 = stmt.executeQuery(q2);
while (rs2.next()) {
   totalCount.add(rs2.getString(1));
}
ResultSet rs3 = stmt.executeQuery(q3);
while (rs3.next()) {
   totalCount.add(rs3.getString(1));
}