sql

Updatable ResultSet Example

In this example we shall show you how to use an updatable ResultSet. An updatable result set allows modification to data in a table through the result set. To create an Updatable ResultSet and use its capabilites for data updates in a database one should perform the following steps:

  • Load the JDBC driver, using the forName(String className) API method of the Class. In this example we use the MySQL JDBC driver.
  • Create a Connection to the database. Invoke the getConnection(String url, String user, String password) API method of the DriverManager to create the connection.
  • Create a Statement, using the createStatement() API method of the Connection. The Statement must have the concurrency type ResultSet.CONCUR_UPDATABLE, in order to return updatable result sets.
  • Execute the query to the database, using the executeQuery(String sql) API method of the Statement over a specified column. The data produced by the given query is a ResultSet.
  • Get the data from the current row. Use the next() API method and the getString(String columnLabel) API method of the ResultSet, to get the value of the designated column in the current row of this ResultSet object.
  • Invoke the moveToInsertRow() API method to use the insert row. It is a buffer where a new row may be constructed, by calling the updater methods prior to inserting the row into the result set.
  • Set values for the new row, using the updateString(String columnLabel, String x) API method.
  • Insert the new row, using the insertRow() API method.
  • Move the cursor to another row, with the absolute(int row) API method.
  • Update the value of a specific column on that row, with the updateString(String columnLabel, String x) API method.
  • Update the row, with the updateRow() API method.
  • If we want to discard the update to the row we could use cancelRowUpdates() API method.
  • Move the cursor to another row in order to delete it, with the deleteRow() API method.
  • Retrieve the current values of the row from the database, with the refreshRow() API method.
  • Move the cursor to the front of this ResultSet object, using the beforeFirst() API method, and then display table data again to check the updates, with next() and getString(String columnLabel) API methods,

as described in the code snippet below.

package com.javacodegeeks.snippets.core;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class UpdatableResultSetExample {
 
  public static void main(String[] args) {

    Connection connection = null;
    try {

  // Load the MySQL JDBC driver

  String driverName = "com.mysql.jdbc.Driver";

  Class.forName(driverName);


  // Create a connection to the database

  String serverName = "localhost";

  String schema = "test";

  String url = "jdbc:mysql://" + serverName +  "/" + schema;

  String username = "username";

  String password = "password";

  connection = DriverManager.getConnection(url, username, password);

  

  System.out.println("Successfully Connected to the database!");

  
    } catch (ClassNotFoundException e) {

  System.out.println("Could not find the database driver " + e.getMessage());
    } catch (SQLException e) {

  System.out.println("Could not connect to the database " + e.getMessage());
    }

    try {


  /*

    * An updatable result set allows modification to data in a table through the result set. 

    * If the database does not support updatable result sets, the result sets returned from 

    * executeQuery() will be read-only. To get updatable results, the Statement object used 

    * to create the result sets must have the concurrency type ResultSet.CONCUR_UPDATABLE. 

    * The query of an updatable result set must specify the primary key as one of the selected 

    * columns and select from only one table.

    */


  // Create a statement that will return updatable result sets

  Statement statement = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);


  // Primary key test_col must be specified so that the result set is updatable

  ResultSet results = statement.executeQuery("SELECT test_col FROM test_table");


  System.out.println("Table data prior results handling... ");


  // Display table data

  while (results.next()) {


    // Get the data from the current row using the column name - column data are in the VARCHAR format

    String data = results.getString("test_col");

    System.out.println("Fetching data by column name for row " + results.getRow() + " : " + data);


  }

  

  // An updatable result supports a row called the "insert row". It is a buffer for holding the values of a new row

  results.moveToInsertRow();


  // Set values for the new row.

  results.updateString("test_col", "inserted_test_value");


  // Insert the new row

  results.insertRow();


  // Move cursor to the third row

  results.absolute(3);


  // Update the value of column test_col on that row

  results.updateString("test_col", "updated_test_value");


  // Update the row; if auto-commit is enabled, update is committed

  results.updateRow();


  // Discard the update to the row we could use 

  // results.cancelRowUpdates();


  // Delete the fifth row

  results.absolute(5);

  results.deleteRow();


  // Retrieve the current values of the row from the database

  results.refreshRow();


  System.out.println("Table data after results handling... ");


  results.beforeFirst();


  // Display table data

  while (results.next()) {


    // Get the data from the current row using the column name - column data are in the VARCHAR format

    String data = results.getString("test_col");

    System.out.println("Fetching data by column name for row " + results.getRow() + " : " + data);


  }


} catch (SQLException e) {

    System.out.println("Error while operating on updatable ResultSet " + e.getMessage());

}

  }
}

Example Output:

Successfully Connected to the database!
Table data prior results handling... 
Fetching data by column name for row 1 : test_value_0
Fetching data by column name for row 2 : test_value_1
Fetching data by column name for row 3 : test_value_2
Fetching data by column name for row 4 : test_value_3
Fetching data by column name for row 5 : test_value_4
Fetching data by column name for row 6 : test_value_5
Fetching data by column name for row 7 : test_value_6
Fetching data by column name for row 8 : test_value_7
Fetching data by column name for row 9 : test_value_8
Fetching data by column name for row 10 : test_value_9
Table data after results handling... 
Fetching data by column name for row 1 : test_value_0
Fetching data by column name for row 2 : test_value_1
Fetching data by column name for row 3 : updated_test_value
Fetching data by column name for row 4 : test_value_3
Fetching data by column name for row 5 : test_value_5
Fetching data by column name for row 6 : test_value_6
Fetching data by column name for row 7 : test_value_7
Fetching data by column name for row 8 : test_value_8
Fetching data by column name for row 9 : test_value_9
Fetching data by column name for row 10 : inserted_test_value

 
This was an example of how to use an updatable ResultSet in Java.

Ilias Tsagklis

Ilias is a software developer turned online entrepreneur. He is co-founder and Executive Editor at Java Code Geeks.
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Back to top button