JDBC Exception: Operation not allowed after ResultSet closed

Question

Possible Duplicate:
Getting java.sql.SQLException: Operation not allowed after ResultSet closed

I'm working on some code that is throwing Operation not allowed after ResultSet closed exceptions. Here it is:

ResultSet res = stmt.executeQuery("SELECT codigo FROM projecto WHERE nome='"
    + auxiliarNomes.elementAt(i).toString() + "'");
while (res.next()) {
    codigo = res.getString("codigo");
    stmt.executeUpdate("insert into categoriaprojectos values("
        + "'" + codigo + "'" + "," + "'" + Antena.RetornaCodigoProjecto() + "')");
}

What am I doing wrong?

Search "SQL injection" and find out why your SQL construction is wrong — gbn, Feb 15 '12 at 10:39

score 7 · Answer 1 · edited Jun 02 '12 at 03:02

7

This is possible. Just use an extra connection and statement.

Statement statementOne = connectionOne.createStatement();
Statement statementTwo = connectionTwo.createStatement();
ResultSet resultSetOne = statementOne.executeQuery("select * from x");

while (resultSetOne.next()) {
    ResultSet resultSetTwo = statementTwo.executeQuery(String.format("select * from y where xy = %s", resultSetOne.getString(0)));

    while (resultSetTwo.next()) {
        String result = resultSetTwo.getString(0);
    }
}

edited Jun 02 '12 at 03:02

random

9,774
10
66
83

answered Jun 01 '12 at 19:59

Richard Lieverse

71
1
2

Ofcourse a executeUpdate is also possible instead of a second Query – Richard Lieverse Jun 01 '12 at 20:01

score 6 · Answer 2 · edited May 23 '17 at 10:29

6

Looks like a duplicate of Getting java.sql.SQLException: Operation not allowed after ResultSet closed. In brief, you cannot iterate over a ResultSet from some Statement and execute updates on the same Statement at the same time.

edited May 23 '17 at 10:29

Community

1
1

answered Feb 15 '12 at 10:33

Alexander Pavlov

31,598
5
67
93

score 3 · Answer 3 · edited May 23 '17 at 11:47

Your code is very dirty written, you should use PreparedStatements with parametrized SQL statements, because without them there is big danger of SQL Injection. This approach is more faster, cleaner and much more safer! Read this

And to your code, try to do like this, you should use batch for insert more than one operation.

String SEL_QUERY = "SELECT codigo FROM projecto WHERE nome= ?";
String UPDATE_QUERY = "INSERT INTO categoriaprojectos values(?)";

PreparedStatement ps,ps1 = null;
ResultSet rs = null;

ps = con.prepareStatement(SEL_QUERY);
ps.setYourType(<data>);
rs =stmt.executeQuery();
ps1 = con.prepareStatement(UPDATE_QUERY );
while(rs.next())
{
   ps1.setYourType(rs.getType());
   ps1.addBatch();
}
ps1.executeBatch();

You should use batch for this. It's faster, safer and more cleaner. addBatch() method adds a set of parameters to your PreparedStatement object's batch of commands. You will be for example 4 inserts in batch and then you will execute them mass.This technique is named as data clustering

JDBC Exception: Operation not allowed after ResultSet closed

3 Answers3

Linked