deleteRow() - Deleting Rows through ResultSet Objects

<< Java DB (Derby) - ResultSet Objects of Queries

<< JDBC Tutorials - Herong's Tutorial Notes

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 sampple program to test this feature:

/**
 * DerbyResultSetDeleteRow.java
 * Copyright (c) 2007 by Dr. Herong Yang. 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 throug 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();
    }
  }
}

Two rows were deleted from the Profile table:

ResultSet updatable.
Rows deleted ok.

Sections in This Chapter

What Is ResultSet?

ResultSet Cursor and Scrollability

ResultSet Cursor Initial Position: Before First Row

Retrieving Column Values with getXXX() Methods

ResultSet Default Type: Forward-only

Scrollable ResultSet and Moving Cursor Backward

ResultSet Objects with Update Capability

insertRow() - Inserting New Rows through ResultSet Objects

updateXXX() - Updating Column Values for Row Update or Insert

deleteRow() - Deleting Rows through ResultSet Objects