JDBC PS updating ResultSet rows

Updating data in the ResultSet table 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.
  3. Now you can set the cursor to a 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.
  4. When a row in the ResultSet is accessible several ResultSet methods can be used to update column data.
    Here is the most used:
    Methods in ResultSet interface Description
    void updateDouble(int, double)
    void updateDouble(String, double)
    Updates a double in the column with number (1.. N) or database table defined column name.
    void updateFloat(int, float)
    void updateFloat(String, float)
    Updates a float in the column with number (1.. N) or database table defined column name.
    void updateInt(int, int)
    void updateInt(String, int)
    Updates an int in the column with number (1.. N) or database table defined column name.
    void updateLong(int, long)
    void updateLong(String, long)
    Updates a long in the column with number (1.. N) or database table defined column name.
    void updateShort(int, short)
    void updateShort(String, short)
    Updates a short in the column with number (1.. N) or database table defined column name.
    void updateString(int, String)
    void updateString(String, String)
    Updates a String in the column with number (1.. N) or database table defined column name.
    void updateTime(int, Time)
    void updateTime(String, Time)
    Updates a Time in the column with number (1.. N) or database table defined column name.
    void updateDate(int, Date)
    void updateDate(String, Date)
    Updates a Date in the column with number (1.. N) or database table defined column name.
    void updateTimestamp(int, Timestamp)
    void updateTimestamp(String, Timestamp)
    Updates a Timestamp in the column with number (1.. N) or database table defined column name.
    void updateBoolean(int, boolean)
    void updateBoolean(String, boolean)
    Updates a boolean in the column with number (1.. N) or database table defined column name.
    void updateByte(int, byte)
    void updateByte(String, byte)
    Updates a byte in the column with number (1.. N) or database table defined column name.
    void updateBytes(int, byte[])
    void updateBytes(String, byte[])
    Updates a array of byte in the column with number (1.. N) or database table defined column name.
    void updateArray(int, Array)
    void updateArray(String, Array)
    Updates a Array of objects in the column with number (1.. N) or database table defined column name.
    void updateBigDecimal(int, BigDecimal)
    void updateBigDecimal(String, BigDecimal)
    Updates a BigDecimal in the column with number (1.. N) or database table defined column name.
    void updateObject(int, Object)
    void updateObject(String, Object)
    Updates a Object in the column with number (1.. N) or database table defined column name.
    void updateObject(int, Object, int)
    void updateObject(String, Object, int)
    Updates a Object in the column with number (1.. N) or database table defined column name. The last parameter is for a BigDecimal , the number of digits after the decimal point an for Java Object types InputStream and Reader, this is the length of the data in the stream or reader.
    void updateRowId(int, RowId)
    void updateRowId(String, RowId)
    Updates a RowId in the column with number (1.. N) or database table defined column name.
    void updateBinaryStream(int, InputStream)
    void updateBinaryStream(String, InputStream)
    Updates a InputStream of bytes in the column with number (1.. N) or database table defined column name.
    void updateCharacterStream(int, Reader)
    void updateCharacterStream(String, Reader)
    Updates a Reader containing the column value using the column number (1.. N) or database table defined column name.
  5. When you have updated the columns as required, (depending on the Database table Column definition) you must use the void updateRow() method to update the database table. This method cannot be called when the cursor is on the insert row.
  6. If you may decide that you don’t want to update the row, you can call the void cancelRowUpdates() method instead.
    Example of updating data in a ResultSet table using a PrearedStatement object:
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.PreparedStatement;
    import java.util.logging.Level;
    import java.util.logging.Logger;
    
    public class UpdateResultSet {
    
     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  Statement 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 resultSet = stmt.executeQuery();
       // As cursor is 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>");
         // Calculate a new day price for each car and update
         // the database table CarPark 
         resultSet.updateDouble("NewPrice", resultSet.getDouble("dayPrice") * 0.8);
         resultSet.updateRow();
         // prints the new price
         System.out.println("<td>" + resultSet.getDouble("NewPrice") + "</td>");
         System.out.println("</tr>");
        } while (resultSet.next());
        System.out.println("</table>");
       }
      } catch (ClassNotFoundException ex) {
       Logger.getLogger(UpdateResultSet.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>
    <td>200.0</td>
    </tr>
    <tr >
    <td>AD23443</td><td>BMW</td><td>2001</td>
    <td>300.0</td>
    <td>240.0</td>
    </tr>
    <tr >
    <td>DE23453</td><td>FORD</td><td>2002</td>
    <td>375.0</td>
    <td>300.0</td>
    </tr>
    <tr >
    <td>RE23456</td><td>FORD</td><td>2005</td>
    <td>350.0</td>
    <td>280.0</td>
    </tr>
    <tr >
    <td>CE23473</td><td>AUDI</td><td>2001</td>
    <td>400.0</td>
    <td>320.0</td>
    </tr>
    <tr >
    <td>DE34562</td><td>OPEL</td><td>2001</td>
    <td>340.0</td>
    <td>272.0</td>
    </tr>
    <tr >
    <td>AX72345</td><td>AUDI</td><td>2003</td>
    <td>350.0</td>
    <td>280.0</td>
    </tr>
    <tr >
    <td>XY34567</td><td>BMW</td><td>2009</td>
    <td>500.0</td>
    <td>400.0</td>
    </tr>
    </table>
    DE12345AUDI2003 250.0 200.0
    AD23443BMW2001 300.0 240.0
    DE23453FORD2002 375.0 300.0
    RE23456FORD2005 350.0 280.0
    CE23473AUDI2001 400.0 320.0
    DE34562OPEL2001 340.0 272.0
    AX72345AUDI2003 350.0 280.0
    XY34567BMW2009 500.0 400.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.