Java/Database SQL JDBC/ResultSet Updatable

Cancelling Updates to an Updatable Result Set

  
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Main {
  public static void main(String[] argv) throws Exception {
    String driverName = "com.jnetdirect.jsql.JSQLDriver";
    Class.forName(driverName);
    String serverName = "127.0.0.1";
    String portNumber = "1433";
    String mydatabase = serverName + ":" + portNumber;
    String url = "jdbc:JSQLConnect://" + mydatabase;
    String username = "username";
    String password = "password";
    Connection connection = DriverManager.getConnection(url, username, password);
    Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
        ResultSet.CONCUR_UPDATABLE);
    ResultSet resultSet = stmt.executeQuery("SELECT * FROM my_table");
    // Move cursor to the row to update
    resultSet.first();
    // Update the value of column col_string on that row
    resultSet.updateString("col_string", "new data");
    // Discard the update to the row
    resultSet.cancelRowUpdates();
  }
}

Creating an Updatable Result Set

  
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Main {
  public static void main(String[] argv) throws Exception {
    String driverName = "com.jnetdirect.jsql.JSQLDriver";
    Class.forName(driverName);
    String serverName = "127.0.0.1";
    String portNumber = "1433";
    String mydatabase = serverName + ":" + portNumber;
    String url = "jdbc:JSQLConnect://" + mydatabase;
    String username = "username";
    String password = "password";
    Connection connection = DriverManager.getConnection(url, username, password);
    // Create a statement that will return updatable result sets
    Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
    // Primary key must be specified so that the result set is updatable
    ResultSet resultSet = stmt.executeQuery("SELECT col_string FROM my_table");
  }
}

Delete Row from Updatable ResultSet for MySQL

 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class DeleteRowUpdatableResultSet_MySQL {
  public static Connection getConnection() throws Exception {
    String driver = "org.gjt.mm.mysql.Driver";
    String url = "jdbc:mysql://localhost/octopus";
    String username = "root";
    String password = "root";
    Class.forName(driver);
    Connection conn = DriverManager.getConnection(url, username, password);
    return conn;
  }
  public static void main(String[] args) {
    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;
    try {
      conn = getConnection();
      String query = "select id, name from employees";
      stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
      rs = stmt.executeQuery(query);
      while (rs.next()) {
        String id = rs.getString(1);
        String name = rs.getString(2);
        System.out.println("id=" + id + "  name=" + name);
      }
      rs.first();
      rs.deleteRow();
      rs.beforeFirst();
      while (rs.next()) {
        String id = rs.getString(1);
        String name = rs.getString(2);
        System.out.println("id=" + id + "  name=" + name);
      }
    } catch (Exception e) {
      e.printStackTrace();
      System.exit(1);
    } finally {
      try {
        rs.close();
        stmt.close();
        conn.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
  }
}

Deleting a Row from a Database Table Using an Updatable Result Set

  
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Main {
  public static void main(String[] argv) throws Exception {
    String driverName = "com.jnetdirect.jsql.JSQLDriver";
    Class.forName(driverName);
    String serverName = "127.0.0.1";
    String portNumber = "1433";
    String mydatabase = serverName + ":" + portNumber;
    String url = "jdbc:JSQLConnect://" + mydatabase;
    String username = "username";
    String password = "password";
    Connection connection = DriverManager.getConnection(url, username, password);
    // Create an updatable result set
    Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
    ResultSet resultSet = stmt.executeQuery("SELECT * FROM my_table");
    // Delete the first row
    resultSet.first();
    resultSet.deleteRow();
  }
}

Demo Updatable ResultSet

 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class DemoUpdatableResultSet {
  public static Connection getConnection() throws Exception {
    String driver = "oracle.jdbc.driver.OracleDriver";
    String url = "jdbc:oracle:thin:@localhost:1521:databaseName";
    String username = "name";
    String password = "password";
    Class.forName(driver);
    Connection conn = DriverManager.getConnection(url, username, password);
    return conn;
  }
  public static void main(String[] args) {
    ResultSet rs = null;
    Connection conn = null;
    PreparedStatement pstmt = null;
    try {
      conn = getConnection();
      String query = "select id, name, age from employees where age > ?";
      pstmt = conn.prepareStatement(query, ResultSet.TYPE_SCROLL_SENSITIVE,
          ResultSet.CONCUR_UPDATABLE);
      pstmt.setInt(1, 20); // set input values
      rs = pstmt.executeQuery(); // create an updatable ResultSet
                                               // update a column value in the current row.
      rs.absolute(2);                          // moves the cursor to the 2nd row of rs
      rs.updateString("name", "newName");      // updates the "name" column of row 2 to newName
      rs.updateRow();                          // updates the row in the data source
                                               // insert column values into the insert row.
      rs.moveToInsertRow();                    // moves cursor to the insert row
      rs.updateInt(1, 1234);                   // 1st column id=1234
      rs.updateString(2, "newName");           // updates the 2nd column
      rs.updateInt(3, 99);                     // updates the 3rd column to 99
      rs.insertRow();
      rs.moveToCurrentRow();
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      try {
        rs.close();
        pstmt.close();
        conn.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
  }
}

Determining If a Database Supports Updatable Result Sets: An updatable result set allows modification to data in a table through the result set.

  
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
public class Main {
  public static void main(String[] argv) throws Exception {
    String driverName = "com.jnetdirect.jsql.JSQLDriver";
    Class.forName(driverName);
    String serverName = "127.0.0.1";
    String portNumber = "1433";
    String mydatabase = serverName + ":" + portNumber;
    String url = "jdbc:JSQLConnect://" + mydatabase;
    String username = "username";
    String password = "password";
    Connection connection = DriverManager.getConnection(url, username, password);
    DatabaseMetaData dmd = connection.getMetaData();
    if (dmd.supportsResultSetConcurrency(
        ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE)) {
        System.out.println("Updatable result sets are supported"); 
    } else {
        System.out.println("Updatable result sets are not supported");
    }
  }
}

Determining If a Result Set Is Updatable

  
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Main {
  public static void main(String[] argv) throws Exception {
    String driverName = "com.jnetdirect.jsql.JSQLDriver";
    Class.forName(driverName);
    String serverName = "127.0.0.1";
    String portNumber = "1433";
    String mydatabase = serverName + ":" + portNumber;
    String url = "jdbc:JSQLConnect://" + mydatabase;
    String username = "username";
    String password = "password";
    Connection connection = DriverManager.getConnection(url, username, password);
    // Create a statement that will return updatable result sets
    Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
    // Primary key must be specified so that the result set is updatable
    ResultSet resultSet = stmt.executeQuery("SELECT col_string FROM my_table");
    int concurrency = resultSet.getConcurrency();
    if (concurrency == ResultSet.CONCUR_UPDATABLE) {
        System.out.println("Result set is updatable"); 
    } else {
        System.out.println("Result set is not updatable");
    }
  }
}

If database support updatable result sets

  
 
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
public class Main {
  private static final String DRIVER = "com.mysql.jdbc.Driver";
  private static final String URL = "jdbc:mysql://localhost/yourDatabase";
  private static final String USERNAME = "root";
  private static final String PASSWORD = "";
  public static void main(String[] args) throws Exception {
    Class.forName(DRIVER);
    Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
    DatabaseMetaData metadata = connection.getMetaData();
    boolean updatable = metadata.supportsResultSetConcurrency(ResultSet.TYPE_FORWARD_ONLY,
        ResultSet.CONCUR_UPDATABLE);
    System.out.println("Updatable ResultSet supported = " + updatable);
    connection.close();
  }
}

Inserting a Row into a Database Table Using an Updatable Result Set

  
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Main {
  public static void main(String[] argv) throws Exception {
    String driverName = "com.jnetdirect.jsql.JSQLDriver";
    Class.forName(driverName);
    String serverName = "127.0.0.1";
    String portNumber = "1433";
    String mydatabase = serverName + ":" + portNumber;
    String url = "jdbc:JSQLConnect://" + mydatabase;
    String username = "username";
    String password = "password";
    Connection connection = DriverManager.getConnection(url, username, password);
    Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
        ResultSet.CONCUR_UPDATABLE);
    ResultSet resultSet = stmt.executeQuery("SELECT * FROM my_table");
    // Move cursor to the "insert row"
    resultSet.moveToInsertRow();
    // Set values for the new row.
    resultSet.updateString("col_string", "new data");
    // Insert the row
    resultSet.insertRow();
  }
}

Insert Row to Updatable ResultSet from MySQL

 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class InsertRowUpdatableResultSet_MySQL {
  public static Connection getConnection() throws Exception {
    String driver = "org.gjt.mm.mysql.Driver";
    String url = "jdbc:mysql://localhost/databaseName";
    String username = "root";
    String password = "root";
    Class.forName(driver);
    return DriverManager.getConnection(url, username, password);
  }
  public static void main(String[] args) {
    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;
    try {
      conn = getConnection();
      String query = "select id, name from employees";
      stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
      rs = stmt.executeQuery(query);
      while (rs.next()) {
        String id = rs.getString(1);
        String name = rs.getString(2);
        System.out.println("id=" + id + "  name=" + name);
      }
      // Move cursor to the "insert row"
      rs.moveToInsertRow();
      // Set values for the new row.
      rs.updateString("id", "001");
      rs.updateString("name", "newName");
      // Insert the new row
      rs.insertRow();
      // scroll from the top again
      rs.beforeFirst();
      while (rs.next()) {
        String id = rs.getString(1);
        String name = rs.getString(2);
        System.out.println("id=" + id + "  name=" + name);
      }
    } catch (Exception e) {
      e.printStackTrace();
      System.exit(1);
    } finally {
      // release database resources
      try {
        rs.close();
        stmt.close();
        conn.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
  }
}

Make updates in Updatable ResultSet

  
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Main {
  public static void main(String[] args) throws Exception {
    Class.forName("com.mysql.jdbc.Driver");
    Connection connection = DriverManager
        .getConnection("jdbc:mysql://localhost/testdb", "root", "");
    Statement statement = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
        ResultSet.CONCUR_UPDATABLE);
    String query = "SELECT id, code, name, quantity, price FROM products";
    ResultSet uprs = statement.executeQuery(query);
    while (uprs.next()) {
      System.out.println(uprs.getString("id") + ":" + uprs.getString("code") + ":"
          + uprs.getString("name") + ":" + uprs.getInt("quantity") + ":" + uprs.getDouble("price"));
    }
    uprs.first();
    uprs.updateString("name", "Java");
    uprs.updateRow();
    uprs.next();
    uprs.deleteRow();
    uprs.moveToInsertRow();
    uprs.updateString("code", "1");
    uprs.updateString("name", "Data Structures");
    uprs.updateInt("quantity", 1);
    uprs.updateDouble("price", 5.99);
    uprs.insertRow();
    uprs.beforeFirst();
    while (uprs.next()) {
      System.out.println(uprs.getString("id") + "\t" + uprs.getString("code") + "\t"
          + uprs.getString("name") + "\t" + uprs.getInt("quantity") + "\t"
          + uprs.getDouble("price"));
    }
    connection.close();
  }
}

Refreshing a Row in an Updatable Result Set

  
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Main {
  public static void main(String[] argv) throws Exception {
    String driverName = "com.jnetdirect.jsql.JSQLDriver";
    Class.forName(driverName);
    String serverName = "127.0.0.1";
    String portNumber = "1433";
    String mydatabase = serverName + ":" + portNumber;
    String url = "jdbc:JSQLConnect://" + mydatabase;
    String username = "username";
    String password = "password";
    Connection connection = DriverManager.getConnection(url, username, password);
    Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
        ResultSet.CONCUR_UPDATABLE);
    ResultSet resultSet = stmt.executeQuery("SELECT * FROM my_table");
    // Retrieve the current values of the row from the database
    resultSet.refreshRow();
  }
}

Updatable resultset with Oracle Driver

  
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class UpdateableRs {
  public static void main(String[] args) throws Exception {
    Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
    Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL", "yourName", "mypwd");
    Statement stmt = conn.createStatement();
    stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
    ResultSet rs = stmt.executeQuery("SELECT ssn, name, salary FROM EMPLOYEES");
    printRs(rs);
    rs.beforeFirst();
    while (rs.next()) {
      double newSalary = rs.getDouble("salary") * 1.053;
      rs.updateDouble("salary", newSalary);
      rs.updateRow();
    }
    printRs(rs);
    conn.close();
  }
  public static void printRs(ResultSet rs) throws SQLException {
    rs.beforeFirst();
    while (rs.next()) {
      int ssn = rs.getInt("ssn");
      String name = rs.getString("name");
      double salary = rs.getDouble("salary");
      System.out.print("Row Number=" + rs.getRow());
      System.out.print(", SSN: " + ssn);
      System.out.print(", Name: " + name);
      System.out.println(", Salary: $" + salary);
    }
    System.out.println();
  }
}

Updating a Row in a Database Table Using an Updatable Result Set

  
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Main {
  public static void main(String[] argv) throws Exception {
    String driverName = "com.jnetdirect.jsql.JSQLDriver";
    Class.forName(driverName);
    String serverName = "127.0.0.1";
    String portNumber = "1433";
    String mydatabase = serverName + ":" + portNumber;
    String url = "jdbc:JSQLConnect://" + mydatabase;
    String username = "username";
    String password = "password";
    Connection connection = DriverManager.getConnection(url, username, password);
    Statement stmt = connection.createStatement(
        ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
    ResultSet resultSet = stmt.executeQuery("SELECT * FROM my_table");
    resultSet.first();
    // Update the value of column col_string on that row
    resultSet.updateString("col_string", "new data");
    // Update the row; if auto-commit is enabled, update is committed
    resultSet.updateRow();
  }
}

Using UpdatableResultSet to insert a new row

  

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class PrintResultSet {
  public static void main(String args[]) throws Exception {
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    Connection con = DriverManager.getConnection("jdbc:odbc:Contacts");
    Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
    ResultSet rs = stmt.executeQuery("select * from employee");
    rs.moveToInsertRow();
    rs.updateInt("Contact_ID", 150);
    rs.updateString("First_Name", "Nigel");
    rs.updateString("Last_Name", "Thornebury");
    rs.insertRow();
  }
}