updatable resultsets JDBC

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());

}

  }
}