CODEWITHSUNDEEP
javamysqljdbc
nikunj2512
nikunj2512

Reputation: 621

java mysql resultset error in for loop

In the below code:

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!");
        }
    }
}

In the above piece of code i am getting a list of table names from the user and then i am iterating over them in the for loop. The code is working but it is not inserting the data into the temp table and ending with an error.

Error:

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)

Please help me out finding why this error is coming?

Upvotes: 2

Views: 176

Answers (2)

Luiggi Mendoza
Luiggi Mendoza

Reputation: 85781

ResultSet rs reference depends on the Statement stmt reference that generated it. Since you're changing the statement to execute in stmt, rs will be closed behind the scenes and won't be able to work anymore.

To solve the problem, just use a single PreparedStatement for each rs and rs2.

Upvotes: 2

TheCodingFrog
TheCodingFrog

Reputation: 3504

When you run rs2 = stmt.executeQuery(query); the previous resultset i.e. rs get closed. More detail can be found here

Upvotes: 0

Related Questions