Using a cursor with a PreparedStatement object

Once you create a PreparedStatement object, you can use it multiple times with the same or different values for its input parameters. If you use a cursor with a PreparedStatement object, you must close the cursor after each use and then reopen the cursor to use it again. A cursor is closed when you close its result set (ResultSet.close). It is opened when you execute its prepared statement (PreparedStatement.executeQuery).

The following example shows how to create a PreparedStatement object, assign it a cursor, and execute the PreparedStatement object twice, closing and then reopening the cursor.

// Create a prepared statement object with a 
// parameterized query.
PreparedStatement prep_stmt =
conn.prepareStatement(
"SELECT au_id, au_lname, au_fname "+
"FROM authors WHERE city = ? "+
"FOR UPDATE OF au_lname");
 
//Create a cursor for the statement.
prep_stmt.setCursorName("author_cursor");
 
// Assign the parameter in the query a value. 
// Execute the prepared statement to return a 
// result set.
prep_stmt.setString(1, "Oakland");
ResultSet rs = prep_stmt.executeQuery();
 
//Do some processing on the result set.
while(rs.next())

{
    ...
}
 
// Close the result, which also closes the cursor.
rs.close();
 
// Execute the prepared statement again with a new 

// parameter value. 
prep_stmt.setString(1,"San Francisco");
rs = prep_stmt.executeQuery();

// reopens cursor