Operation not allowed after ResultSet closed error

Question

I have a code which is inserting data into table after processing but again and again I am getting error

Operation not allowed after ResultSet closed

Here is my code.

 try {
        Connection con = null;
        Class.forName("com.mysql.jdbc.Driver");
        con = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/asteriskcdrdb", "root", "techsoft");
        Statement st = con.createStatement();
        ResultSet rs = st.executeQuery("Select * from asteriskcdrdb.sp1");
        while (rs.next()) {
            AreaCode = rs.getString("AreaCode");
            //System.out.println(AreaCode);
            String Pulse = rs.getString("Pulse");
            Rate = rs.getInt("Rate/pulse");
            // System.out.println(Rate);
            if (AreaCode.equals(str)) {
                System.out.println("Hii");
                try {
                    Connection conn = null;
                    Class.forName("com.mysql.jdbc.Driver");
                    conn = DriverManager.getConnection(
                            "jdbc:mysql://localhost:3306/asteriskcdrdb", "root", "techsoft");
                    Statement stmt = conn.createStatement();
                    rst = stmt.executeQuery("Select * from cdr where src ='9035020090'");
                    while (rst.next()) {
                        calldate = rst.getString("calldate");
                        // System.out.println(calldate);
                        clid = rst.getString("clid");
                        src = rst.getString("src");
                        dst = rst.getString("dst");
                        dcontext = rst.getString("dcontext");
                        channel = rst.getString("channel");
                        dstchannel = rst.getString("dstchannel");
                        lastapp = rst.getString("lastapp");
                        lastdata = rst.getString("lastdata");
                        duration = rst.getString("duration");
                        //System.out.println(duration);
                        dur = Integer.parseInt(duration);
                        //System.out.println(dur);
                        data.add(dur);
                        billsec = rst.getString("billsec");
                        disposition = rst.getString("disposition");
                        amaflags = rst.getString("amaflags");
                        accountcode = rst.getString("accountcode");
                        uniqueid = rst.getString("uniqueid");
                        userfield = rst.getString("userfield");
                        int newcost = checktime(dur, Rate);
                        stmt.executeUpdate("insert into cdrcost (
         calldate,clid,src,dst,dcontext,channel,
         dstchannel,lastapp, lastdata,duration,billsec,
         disposition,amaflags,accountcode,uniqueid,
         userfield,cdrcost) values ('" + calldate + "','" + 
         clid + "','" + src + "','" + dst + "','" + dcontext 
         + "','" + channel + "','" + dstchannel + "','" + 
         lastapp + "','" + lastdata + "','" + duration + "','" + 
         billsec + "','" + disposition + "','" + amaflags 
          + "','" + accountcode + "','" + uniqueid + "','" + userfield  
         + "','" + newcost + "')");
                     }

                } catch (Exception e) {
                    System.out.println(e);
                }
            } else if (AreaCode.equals(str2)) {
                System.out.println("Hii2");
            }
        }

    } catch (Exception e) {
        System.out.println(e);
    }
}

public static int checktime(int dur, int Rate) {
    int cost = 0;

    // System.out.println(c);
    int min = 60;

    int quotient = dur / min;
    // System.out.println(quotient);

    int reminder = dur % min;
    //  System.out.println(reminder);

    if (reminder > 0) {
        quotient = quotient + 1;
        // System.out.println(quotient);

        // System.out.println(cost);
    }
    cost = quotient * Rate;
    return cost;
}

Answer 1

stmt.executeUpdate("insert into cdrcost (
         calldate,clid,src,dst,dcontext,channel,
         dstchannel,lastapp, lastdata,duration,billsec,
         disposition,amaflags,accountcode,uniqueid,
         userfield,cdrcost) values ('" + calldate + "','" + 
         clid + "','" + src + "','" + dst + "','" + dcontext 
         + "','" + channel + "','" + dstchannel + "','" + 
         lastapp + "','" + lastdata + "','" + duration + "','" + 
         billsec + "','" + disposition + "','" + amaflags 
          + "','" + accountcode + "','" + uniqueid + "','" + userfield  
         + "','" + newcost + "')");

Here, when you execute this update inside the while loop then the current resultset of the previous select query will get closed. So you cannot do res.next() in the next iteration.

If you want to hold data after disconnecting, you can use Cached Row Set:

ResultSet res = ....
CachedRowSet rowset = new CachedRowSetImpl();
rowset.populate(res);

CachedRowSet is a connectionless ResultSet. I have not used it much, because I didn't have any need. But, I can share some link here to help you understand the concepts

• http://download.java.net/jdk8/docs/api/javax/sql/rowset/CachedRowSet.html
• Where to close a JDBC Connection while I want to return the ResultSet

Or you can better take a look at RowSetFactory which provides you with method createCachedRowSet() that can create CachedRowSet instance for you.

You can get RowSetFactory from RowSetProvider. Then you can get CachedRowSet and iterate over it.

RowSetFactory factory = RowSetProvider.newFactory();
CachedRowSet crs = factory.createCachedRowSet();
crs.populate(res);

while(crs.next()) {
    crs.getString(1);  // Works similar to `ResultSet`
}

Answer 2

Before giving you an answer, you should know some basic things about database access and JDBC:

• You must not create many connections to access the database in a big operation. If you need to read, insert, update or delete data in a single method, you should use only 1 connection. Opening a connection is a great cost operation. If you don't notice it yet is because you're in a single-user (you) environment.

• Every Statement uses one or more ResultSets. Since you're a beginner, assume that each Statement will have a single ResultSet. If you modify the data in a Statement, the ResultSet bounded to this Statement will be closed and can't be used in future operations. That's why you have the problem (as stated in other answers).

• If you will execute a SQL statement that will use parameters, use a PreparedStatement. Otherwise, your application will be prone to SQL Injection attack (i.e. a hacker could shut down your database server, you and me know that's a bad thing to happen).

• You should close the resources after using them. This means, you should close the ResultSets, Statements and Connection (in this order).

Based in all these notes, your code will change to this:

Connection con = null;
Statement st = null;
ResultSet rs = null;
try {
    Class.forName("com.mysql.jdbc.Driver");
    con = DriverManager.getConnection(
            "jdbc:mysql://localhost:3306/asteriskcdrdb", "root", "techsoft");
    st = con.createStatement();
    rs = st.executeQuery("Select * from asteriskcdrdb.sp1");
    while (rs.next()) {
        AreaCode = rs.getString("AreaCode");
        String Pulse = rs.getString("Pulse");
        Rate = rs.getInt("Rate/pulse");
        if (AreaCode.equals(str)) {
            Statement stmt = null;
            ResultSet rst = null;
            PreparedStatement insSt = null;
            try {
                //using the first connection
                stmt = con.createStatement();
                rst = stmt.executeQuery("Select * from cdr where src ='9035020090'");
                while (rst.next()) {
                    calldate = rst.getString("calldate");
                    clid = rst.getString("clid");
                    src = rst.getString("src");
                    dst = rst.getString("dst");
                    dcontext = rst.getString("dcontext");
                    channel = rst.getString("channel");
                    dstchannel = rst.getString("dstchannel");
                    lastapp = rst.getString("lastapp");
                    lastdata = rst.getString("lastdata");
                    duration = rst.getString("duration");
                    dur = Integer.parseInt(duration);
                    data.add(dur);
                    billsec = rst.getString("billsec");
                    disposition = rst.getString("disposition");
                    amaflags = rst.getString("amaflags");
                    accountcode = rst.getString("accountcode");
                    uniqueid = rst.getString("uniqueid");
                    userfield = rst.getString("userfield");
                    int newcost = checktime(dur, Rate);
                    //every ? is a parameter in the query
                    insSt = con.prepareStatement(
                        "insert into cdrcost (calldate,clid,src,dst,dcontext,channel, dstchannel, lastapp, lastdata,duration,billsec, disposition,amaflags,accountcode,uniqueid, userfield,cdrcost) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
                    //setting every parameter
                    insSt.setObject(1, calldate);
                    insSt.setObject(2, clid);
                    insSt.setObject(3, src);
                    insSt.setObject(4, dst);
                    insSt.setObject(5, dcontext);
                    insSt.setObject(6, channel);
                    insSt.setObject(7, dstchannel);
                    insSt.setObject(8, lastapp);
                    insSt.setObject(9, lastdata);
                    insSt.setObject(10, duration);
                    insSt.setObject(11, billsec);
                    insSt.setObject(12, disposition);
                    insSt.setObject(13, amaflags);
                    insSt.setObject(14, accountcode);
                    insSt.setObject(15, uniqueid);
                    insSt.setObject(16, userfield);
                    insSt.setObject(17, newcost);
                    //executing the insert statement
                    insSt.executeUpdate();
                }
            } catch (Exception e) {
                System.out.println(e);
            } finally {
                //closing the resources in this transaction
                try {
                    //the insSt statement doesn't have a resultset
                    if (insSt != null) {
                        insSt.close();
                    }
                    //the rst ResultSet is bounded to stmt Statement, it must be closed first
                    if (rst != null) {
                        rst.close();
                    }
                    if (stmt != null) {
                        stmt.close();
                    }
                } catch (SQLException sqle) {}
            }
        } else if (AreaCode.equals(str2)) {
            System.out.println("Hii2");
        }
    }
} catch (Exception e) {
    System.out.println(e);
} finally {
    //closing the resources in this transaction
    //similar logic than the used in the last close block code
    try {
        if (rs != null) {
            rs.close();
        }
        if (st != null) {
            st.close();
        }
        //at the last of all the operations, close the connection
        if (con != null) {
            con.close();
        }
    } catch (SQLException sqle) {}
}

---

As a side note, the fact that you're a beginner doesn't mean that you should code the thing just to make it work. You should always follow the best practices. IMO it's good to ask for guidance in these scenarios.

Answer 3

At a given point of time, a connection can be used by only one statement. The resultsets that are a result of executing the statement share the same connection. So, if you want to perform an update for each row while reading it from result set, you should request for an UPDATEABLE result set while creating the statement, then update the column in the current row in place while browsing through the resultset.

Since you seem to need to insert a record into a different table, you can do one of the following:

1. Use cached row sets

   1. Get your resultset into a CachedRowSet.
   2. Close the result set and statement.
   3. Iterate through the cached row set and create a statement for each insertion and execute it.

2. Use separate objects to track what needs to be inserted

   1. Build the to-be-inserted records as objects into a list.
   2. Once done with the resultset, close it, close the statement.
   3. prepare a new statement i.e. PreparedStatement, for the INSERT statement.
   4. insert the records as a batch.

3. Worst-case, use three connections, one for outer query, one for inner query and one for insertion

The following code (not at all production-quality, but just a dirty implementation of third option above):

    try
    {
        // used for outer query.
        Connection outerCon = null;
        // used for inner query.
        Connection innerCon = null;
        // used to insert records.
        Connection insCon = null;
        Class.forName("com.mysql.jdbc.Driver");
        outerCon = DriverManager.getConnection("jdbc:mysql://localhost:3306/asteriskcdrdb", "root", "techsoft");
        innerCon = DriverManager.getConnection("jdbc:mysql://localhost:3306/asteriskcdrdb", "root", "techsoft");
        insCon = DriverManager.getConnection("jdbc:mysql://localhost:3306/asteriskcdrdb", "root", "techsoft");
        Statement st = outerCon.createStatement();
        ResultSet rs = st.executeQuery("Select * from asteriskcdrdb.sp1");
        while (rs.next())
        {
            AreaCode = rs.getString("AreaCode");
            // System.out.println(AreaCode);
            String Pulse = rs.getString("Pulse");
            Rate = rs.getInt("Rate/pulse");
            // System.out.println(Rate);
            if (AreaCode.equals(str))
            {
                System.out.println("Hii");
                try
                {
                    Statement stmt = innerCon.createStatement();
                    ResultSet rst = stmt.executeQuery("Select * from cdr where src ='9035020090'");
                    while (rst.next())
                    {
                        calldate = rst.getString("calldate");
                        // System.out.println(calldate);
                        clid = rst.getString("clid");
                        src = rst.getString("src");
                        dst = rst.getString("dst");
                        dcontext = rst.getString("dcontext");
                        channel = rst.getString("channel");
                        dstchannel = rst.getString("dstchannel");
                        lastapp = rst.getString("lastapp");
                        lastdata = rst.getString("lastdata");
                        duration = rst.getString("duration");
                        // System.out.println(duration);
                        dur = Integer.parseInt(duration);
                        // System.out.println(dur);
                        data.add(dur);
                        billsec = rst.getString("billsec");
                        disposition = rst.getString("disposition");
                        amaflags = rst.getString("amaflags");
                        accountcode = rst.getString("accountcode");
                        uniqueid = rst.getString("uniqueid");
                        userfield = rst.getString("userfield");
                        int newcost = checktime(dur, Rate);
                        Statement insStmt = insCon.createStatement();
                        stmt
                            .executeUpdate("insert into cdrcost (calldate,clid,src,dst,dcontext,channel,dstchannel,lastapp, lastdata,duration,billsec,disposition,amaflags,accountcode,uniqueid,userfield,cdrcost) values ('"
                                + calldate
                                + "','"
                                + clid
                                + "','"
                                + src
                                + "','"
                                + dst
                                + "','"
                                + dcontext
                                + "','"
                                + channel
                                + "','"
                                + dstchannel
                                + "','"
                                + lastapp
                                + "','"
                                + lastdata
                                + "','"
                                + duration
                                + "','"
                                + billsec
                                + "','"
                                + disposition
                                + "','"
                                + amaflags
                                + "','"
                                + accountcode + "','" + uniqueid + "','" + userfield + "','" + newcost + "')");
                        insStmt.close();
                    }
                    rst.close();
                    stmt.close();

                }
                catch (Exception e)
                {
                    System.out.println(e);
                }
            }
            else if (AreaCode.equals(str2))
            {
                System.out.println("Hii2");
            }
        }
        rs.close();
        st.close();

    }
    catch (Exception e)
    {
        System.out.println(e);
    }