JDBC PS ResultSet Deleting/ Inserting rows

Deleting and Inserting a row in a ResultSet using a PreparedStatement object.

  1. First you need to Create a Database Connection.
  2. For this purpose, you must create an updateable ResultSet object as earlier decribed.

Insert a row into a ResultSet.

  1. With an Updateable ResultSet object you can insert a row using the following ResultSet methods:
    Methods in ResultSet interface Description
    void moveToInsertRow() Moves the cursor to the insert row. The current cursor position is remembered while the cursor is positioned on the insert row. The insert row is a special row associated with an updatable result set.
    void insertRow() Inserts the contents of the insert row into this ResultSet object and into the database. The cursor must be on the insert row when this method is called.
    void moveToCurrentRow() Moves the cursor to the remembered cursor position, usually the current row. This method has no effect if the cursor is not on the insert row.
  2. Cursor will be placed on an "Insert row" after moveToInsertRow() method is executed and we are then in a condition called the insert-mode.
  3. You can now updated the columns using all the "update column" methods as previously described.
    For some reason, you can use "fetch the column" methods as well.
  4. When you have updated the columns as required, (this depend on the Database table Column definition) you must use the insertRow() method to update the database table.
  5. If you may decide that you don’t want to insert the row, you can call the void moveToCurrentRow() method instead.

How do we Delete a row in a ResultSet using a PreparedStatement object?

  1. You must set the cursor to a selected row using one of the positioning methods in the ResultSet object. Which methods of these to use depends on the scrolling you have selected when you created the PreparedStatement object.
  2. With an Updateable ResultSet you can delete a row with the ResultSet method:
    Method in ResultSet interface Description
    void deleteRow() Deletes the current row from this ResultSet object and from the underlying database. This method cannot be called when the cursor is on the insert row.
  3. To make sure that the deleted row operation will be reflected in the resultSet you must refresh the resultSet with a ResultSet.close() method and then create a new ResultSet with the Statement.executeQuery() method.
    Example using ResultSet delete and insert methods:
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.util.logging.Level;
    import java.util.logging.Logger;
    
    public class InsertResultSet {
    
     public static void main(String[] args) {
      Connection conn = null;
      PreparedStatement stmt = null;
      try {
       // Register a driver for the MySQL database
       Class.forName("com.mysql.jdbc.Driver");
       // Create a url for accessing the MySQL
       // database CarDB
       String url = "jdbc:mysql://localhost:3306/CarDB";
       // user and password to access the database
       String username = "root";
       String password = "root";
       // User the DriverManager to get a Connection to the database
       conn = DriverManager.getConnection(url, username, password);
       // Create a  PreparedStatement for a ResultSet that is
       // sensitive for other users updating of tables in the database and
       // the ResultSet is updatable for a selected table in the database.
       // Select all columns from DB table Carpark where regNo
       // is "XY34567"
       stmt = conn.prepareStatement("select * from Carpark " +
               " where regNo=?", ResultSet.TYPE_SCROLL_SENSITIVE,
               ResultSet.CONCUR_UPDATABLE);
       // set the regnNo in the Where clause to be "XY34567"
       stmt.setString(1, "XY34567");
       // Gets the ResultSet from the PreparedStatement object
       ResultSet resultSet = stmt.executeQuery();
       // We Delete the car we want to insert if it exist already.
       if (resultSet.next()) {
        resultSet.deleteRow();
        resultSet.close();
       }
       // Create a  PreparedStatement for a ResultSet that is
       // sensitive for other users updating of tables in the database and
       // the ResultSet is updatable for a selected table in the database.
       // Select all columns and rows from DB table Carpark
       stmt = conn.prepareStatement("select * from Carpark ",
               ResultSet.TYPE_SCROLL_SENSITIVE,
               ResultSet.CONCUR_UPDATABLE);
       // Gets the ResultSet from the PreparedStatement object
       resultSet = stmt.executeQuery();
       // Inserts a new row with a random generated dayprice
       resultSet.moveToInsertRow();
       resultSet.updateString("regNo", "XY34567");
       resultSet.updateString("cartype", "BMW");
       resultSet.updateInt("model", 2009);
       resultSet.updateDouble("dayPrice", 200.00 +
               (double) (Math.round(Math.random() * 200)));
       resultSet.insertRow();
       resultSet.moveToCurrentRow();
       // After Insert the Cursor return to it's previous position
       // As cursor was at the before first row position
       // we use the next() method to
       // test and read the first row in the ResultSet.
       if (resultSet.next()) {
        // Then we use a loop to retrieve rows and column data
        // and creates a html coded table output
        System.out.println("<table border='1'>");
        do {
    
         System.out.println("<tr >");
         System.out.print("<td>" + resultSet.getString("regNo") + "</td>");
         System.out.print("<td>" + resultSet.getString("cartype") + "</td>");
         System.out.println("<td>" + resultSet.getInt("model") + "</td>");
         System.out.println("<td>" + resultSet.getDouble("dayPrice") + "</td>");
         System.out.println("</tr>");
        } while (resultSet.next());
        System.out.println("</table>");
       }
      } catch (ClassNotFoundException ex) {
       Logger.getLogger(InsertResultSet.class.getName()).log(Level.SEVERE, null, ex);
      } catch (SQLException e) {
       e.printStackTrace();
      } finally {
       try {
        // Close the Statement, which also close the ResultSet
        stmt.close();
        conn.close();
       } catch (Exception xe) {
        xe.printStackTrace();
       }
      }
     }
    }

    You should use the close() method in the Statement object when you do not need it anymore.

    You should also use the close() method in the Connection object when you do not need it anymore.

    You can download this example here (needed tools can be found in the right menu on this page).

    If we run this application the result should be:
    As pure output Translated by a Browser
    <table border='1'>
    <tr >
    <td>DE12345</td><td>AUDI</td><td>2003</td>
    <td>250.0</td>
    </tr>
    <tr >
    <td>AD23443</td><td>BMW</td><td>2001</td>
    <td>300.0</td>
    </tr>
    <tr >
    <td>DE23453</td><td>FORD</td><td>2002</td>
    <td>375.0</td>
    </tr>
    <tr >
    <td>RE23456</td><td>FORD</td><td>2005</td>
    <td>350.0</td>
    </tr>
    <tr >
    <td>CE23473</td><td>AUDI</td><td>2001</td>
    <td>400.0</td>
    </tr>
    <tr >
    <td>DE34562</td><td>OPEL</td><td>2001</td>
    <td>340.0</td>
    </tr>
    <tr >
    <td>AX72345</td><td>AUDI</td><td>2003</td>
    <td>350.0</td>
    </tr>
    <tr >
    <td>XY34567</td><td>BMW</td><td>2009</td>
    <td>500.0</td>
    </tr>
    </table>
    DE12345AUDI2003 250.0
    AD23443BMW2001 300.0
    DE23453FORD2002 375.0
    RE23456FORD2005 350.0
    CE23473AUDI2001 400.0
    DE34562OPEL2001 340.0
    AX72345AUDI2003 350.0
    XY34567BMW2009 500.0
© 2010 by Finnesand Data. All rights reserved.
This site aims to provide FREE programming training and technics.
Finnesand Data as site owner gives no warranty for the correctness in the pages or source codes.
The risk of using this web-site pages or any program codes from this website is entirely at the individual user.