JDBC ResultSet closed statement

Question

I'm trying to write a function that updates 2 tables in my database. I'm getting an error which i think is caused by calling next() on a resultset that has no more rows in the set. I was thinking an if condition on hasNext() would fix this but it's not available to the result set...

The error i'm getting is 'No operations allowed after statement closed.'

private void updateDatabase() throws Exception {
        Connection conn = getConnection();
        PreparedStatement updateMovieStmt = conn.prepareStatement("INSERT INTO movies VALUES(null,?,null,null,null)", Statement.RETURN_GENERATED_KEYS);
        PreparedStatement updateVideoStmt = conn.prepareStatement("INSERT INTO video_files VALUES(null,null,null,?,?)", Statement.RETURN_GENERATED_KEYS);
        try {
            for (Movie localMovie : getLocalMovies()) {
                // fetch a local movie{
                boolean newMovie = true;
                for (Movie dbMovie : getDatabaseMovies(conn)) {
                    newMovie = true;
                    Pattern p = Pattern.compile(localMovie.getTitlePattern(), Pattern.CASE_INSENSITIVE);
                    Matcher m = p.matcher(dbMovie.getTitle());
                    // if it's already in the database not new movie... but is
                    // is it a new video rip????????????;
                    if (m.find()) {
                        System.out.println("DB movie: " + dbMovie.getTitle() + " matches localpattern of: " + localMovie.getTitlePattern());
                        newMovie = false;
                        break;
                    }
                }
                if (newMovie == true && localMovie.getTitle() != null) {
                    updateMovieStmt.setString(1, localMovie.getTitle());
                    updateMovieStmt.executeUpdate();
                    // get new movie id and put into new video row
                    ResultSet rs = updateMovieStmt.getGeneratedKeys();
                    if (rs.next()) {
                        updateVideoStmt.setBytes(1, localMovie.getHash());
                        updateVideoStmt.setInt(2, rs.getInt(1));
                        updateVideoStmt.executeUpdate();
                    }

                }

            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            updateMovieStmt.close();
            updateVideoStmt.close();
            conn.close();
        }
    }

BalusC · Accepted Answer · 2013-02-21T14:21:29.317

1

If you're not using batches (addBatch()/executeBatch()), nor clearing the parameters (clearParameters()), then you should be creating the statements inside the loop, not outside the loop.

Move the both conn.prepareStatement() lines into the if (newMovie == true && localMovie.getTitle() != null) block, preferably refactored in separate methods. Your current method is doing way too much.

edited Feb 21 '13 at 14:21

answered May 16 '11 at 01:00

BalusC

1,082,665
372
3,610
3,555

[Check your this answer](http://stackoverflow.com/questions/5149135/jdbc-statement-preparedstatement-per-connection) – sudmong May 16 '11 at 01:16
thanks, do you think updateMovie(Connection conn, String title) updateVideo.... would be a suitable solution? – el_pup_le May 16 '11 at 01:39
3

"You can have only one open Statement at once per connection. Once you create a new Statement on the same connection and execute it, the previously created one will be closed." - "you can have only one open statement at a time. Once you create a new one, the previous one will be closed." - **Both these statements are wrong.** [The correct answer is in a comment by Mark Rotteveel here.](http://stackoverflow.com/a/5150085/269126) – Lumi Mar 29 '12 at 07:25
@BalusC: you could take Lumi's advice and edit this answer. It has shaken me quite a bit. – kellogs Feb 21 '13 at 14:10

score 1 · Answer 2 · answered May 16 '11 at 01:10

1

you can have only one open Statement per connection. Once you create a new Statement on the same connection, the previously created one will be closed

This holds true for resultSet that we can have only one resultSet open per statement. With a connection we can have multiple statements open.

answered May 16 '11 at 01:10

sudmong

2,036
13
12

1

Whether an answer to the OP's concrete problem or not, it is related, and the statements mady by @sudmong are correct. – Lumi Mar 29 '12 at 07:28

JDBC ResultSet closed statement

2 Answers2