deleteRow() - Deleting Rows through ResultSet Objects

JDBC Tutorials - Herong's Tutorial Examples

∟Derby (Java DB) - ResultSet Objects of Queries

∟deleteRow() - Deleting Rows through ResultSet Objects

This section describes how to delete rows from target tables through ResultSet objects.

A ResultSet object with update capability can also be used to delete rows from database tables. All you need to do are:

Create a statement with concurrency type set to ResultSet.CONCUR_UPDATABLE.
Call executeQuery() method to return a ResultSet object on the target table.
Call next(), absolute(), or relative() method to move the cursor to the row to be deleted.
Call deleteRow() method delete the current row from the underlying table of the ResultSet object.

I wrote the following sample program to test this feature:

/* DerbyResultSetDeleteRow.java
 * Copyright (c) HerongYang.com. All Rights Reserved.
 */
import java.sql.*;
public class DerbyResultSetDeleteRow {
  public static void main(String [] args) {
    Connection con = null;
    try {
      con = DriverManager.getConnection(
        "jdbc:derby://localhost/TestDB");

// Create a Statement for scrollable ResultSet
      Statement sta = con.createStatement(
        ResultSet.TYPE_FORWARD_ONLY,
        ResultSet.CONCUR_UPDATABLE);

// Catch the ResultSet object
      ResultSet res = sta.executeQuery("SELECT * FROM Profile");

// Check ResultSet's updatability
      if (res.getConcurrency() == ResultSet.CONCUR_READ_ONLY) {
        System.out.println("ResultSet non-updatable.");
      } else {
        System.out.println("ResultSet updatable.");
      }

// Looping through the rows in the ResultSet object
      while (res.next()) {
        int id = res.getInt("ID");
        if (id == 4 || id == 6) {
          res.deleteRow();
        }
      }
      System.out.println("Rows deleted ok.");

// Close ResultSet and Statement
      res.close();
      sta.close();

      con.close();
    } catch (Exception e) {
      System.err.println("Exception: "+e.getMessage());
      e.printStackTrace();
    }
  }
}