Q: repeatedly execute() on PreparedStatement without close()

[Yan Zhou]

Hi,

I wonder if I can repeatedly call executeUpdate() on a PreparedStatement without closing the statement? Like this:

                stmt = con.prepareStatement(INSERT_SQL);
                stmt.setString(1, value);
                if (condition1IsMet)
                    stmt.setInt(2, value2);
                    stmt.executeUpdate();   // execute()
                }
                Collection list = getIds();
                if (list != null) {
                    Iterator i = list.iterator();
                    while (i.hasNext()) {
                        stmt.setInt(2, anotherValue);
                        stmt.executeUpdate();    // execute many times
                    }
                }
 
                stmt.close(); // close at end
 

Would this have problem of leaking resource?

Yan

[Annie Smith]

Yes, it might.

Oracle, for instance, throws up a runtime exception saying "maximum number of cursors exceeded..."

[Jesper de Jong]

For updating a number of rows this way, you'd better use addBatch() and executeBatch().

See the API documentation of classes java.sql.PreparedStatement and java.sql.Statement.