for循环中的java mysql结果集错误

Question

在下面的代码中:

String GET_DISTINCT_LISTENER_IDS_QUERY = "select distinct(listener_id) from ";
ResultSet rs = null;
ResultSet rs2 = null;
String temp_table = "CREATE TABLE IF NOT EXISTS temp (id INT NOT NULL COMMENT '',"
        + "date_start DATETIME NULL COMMENT ''," + "listener_id VARCHAR(45) NULL COMMENT '',"
        + "gateway_id VARCHAR(45) NULL COMMENT ''," + "entryway_id VARCHAR(45) NULL COMMENT '')";

stmt.executeUpdate(temp_table);

for (int i = 0; i < table_names_list.length; i++) {
    GET_DISTINCT_LISTENER_IDS_QUERY += table_names_list[i];
    PreparedStatement ps1 = conn.prepareStatement(GET_DISTINCT_LISTENER_IDS_QUERY);
    rs = ps1.executeQuery();

    while (rs.next()) {
        String query = "Select * from " + table_names_list[i] + " where listener_id = ? order by date_start LIMIT 1";
        PreparedStatement ps2 = conn.prepareStatement(query);
        ps2.setString(1, rs.getString("listener_id"));
        rs2 = ps2.executeQuery();
        String temp_insert = "INSERT INTO " + table_names_list[i]
                + " (id, date_start, listener_id, gateway_id, entryway_id)" + " values(?,?,?,?,?)";
        PreparedStatement ps = conn.prepareStatement(temp_insert);
        while (rs2.next()) {
            ps.setInt(1, rs2.getInt("id"));
            ps.setDate(2, rs2.getDate("date_start"));
            ps.setString(3, rs2.getString("listener_id"));
            ps.setString(4, rs2.getString("gateway_id"));
            ps.setString(5, rs2.getString("entryway_id"));
            System.out.println("Updating statement...");
            ps.executeUpdate();
            System.out.println("Record is inserted into DBUSER table!");
        }
    }
}

在上面的代码中，我从用户那里得到了一个表名列表，然后我在 for 循环中迭代它们。代码有效，但它没有将数据插入临时表并以错误结束。

错误:

java.sql.SQLException: Operation not allowed after ResultSet closed
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:910)
    at com.mysql.jdbc.ResultSet.checkClosed(ResultSet.java:666)
    at com.mysql.jdbc.ResultSet.next(ResultSet.java:7274)
    at database.database.main(database.java:62)

62: while (rs.next()) { (this line number 62)

请帮我找出为什么会出现此错误？

Answer 1

ResultSet rs 引用取决于生成它的 Statement stmt 引用。由于您正在将语句更改为在 stmt 中执行，因此 rs 将在幕后关闭并且将无法再工作。

要解决这个问题，只需为每个 rs 和 rs2 使用一个 PreparedStatement。