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;
}
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.