Java Tutorial/Database/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();
  }
}

Cancel Updates to an Updatable ResultSet

You cannot cancel updates after you have called updateRow().

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 {
    Connection conn = getConnection();
    Statement st = conn
        .createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
    st.executeUpdate("create table survey (id int,name varchar(30));");
    st.executeUpdate("insert into survey (id,name ) values (1,"nameValue")");
    st.executeUpdate("insert into survey (id,name ) values (2,null)");
    st.executeUpdate("insert into survey (id,name ) values (3,"Tom")");

    ResultSet rs = st.executeQuery("SELECT * FROM survey");
    // Move cursor to the row to update
    rs.first();
    // Update the value of column column_1 on that row
    rs.updateString("name", "new data");
    // Discard the update to the row
    rs.cancelRowUpdates();
    rs.close();
    st.close();
    conn.close();
  }
  private static Connection getConnection() throws Exception {
    Class.forName("org.hsqldb.jdbcDriver");
    String url = "jdbc:hsqldb:mem:data/tutorial";
    return DriverManager.getConnection(url, "sa", "");
  }
}

Create an 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 {
    Connection conn = getConnection();
    Statement st = conn
        .createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
    st.executeUpdate("create table survey (pk_column int,name varchar(30));");
    st.executeUpdate("insert into survey (pk_column,name ) values (1,"nameValue")");
    st.executeUpdate("insert into survey (pk_column,name ) values (2,null)");
    st.executeUpdate("insert into survey (pk_column,name ) values (3,"Tom")");
    // Primary key pk_column must be specified
    // so that the result set is updatable
    ResultSet rs = st.executeQuery("SELECT pk_column FROM survey");
    rs.close();
    st.close();
    conn.close();
  }
  private static Connection getConnection() throws Exception {
    Class.forName("org.hsqldb.jdbcDriver");
    String url = "jdbc:hsqldb:mem:data/tutorial";
    return DriverManager.getConnection(url, "sa", "");
  }
}

Delete a Row from a Database Table Using an 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 {
    Connection conn = getConnection();
    Statement st = conn
        .createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
    st.executeUpdate("create table survey (id int,name varchar(30));");
    st.executeUpdate("insert into survey (id,name ) values (1,"nameValue")");
    st.executeUpdate("insert into survey (id,name ) values (2,null)");
    st.executeUpdate("insert into survey (id,name ) values (3,"Tom")");
    ResultSet rs = st.executeQuery("SELECT id,name FROM survey");
    // Delete the first row
    rs.first();
    rs.deleteRow();
    rs.close();
    st.close();
    conn.close();
  }
  private static Connection getConnection() throws Exception {
    Class.forName("org.hsqldb.jdbcDriver");
    String url = "jdbc:hsqldb:mem:data/tutorial";
    return DriverManager.getConnection(url, "sa", "");
  }
}

Determine if a Database Supports Updatable ResultSets

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
public class Main {
  public static void main(String[] args) throws Exception {
    Connection conn = getConnection();
    DatabaseMetaData dbmd = conn.getMetaData();
    if (dbmd.supportsResultSetConcurrency(ResultSet.TYPE_FORWARD_ONLY,
                                          ResultSet.CONCUR_UPDATABLE)) {
        System.out.println("Updatable ResultSets are supported");
    }
    else {
        System.out.println("Updatable ResultSets are not supported");
    }

    
    conn.close();
  }
  private static Connection getConnection() throws Exception {
    Class.forName("org.hsqldb.jdbcDriver");
    String url = "jdbc:hsqldb:mem:data/tutorial";
    return DriverManager.getConnection(url, "sa", "");
  }
}

Determine if a ResultSet 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[] args) throws Exception {
    Connection conn = getConnection();
    Statement st = conn
        .createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
    st.executeUpdate("create table survey (id int,name varchar(30));");
    st.executeUpdate("insert into survey (id,name ) values (1,"nameValue")");
    st.executeUpdate("insert into survey (id,name ) values (2,null)");
    st.executeUpdate("insert into survey (id,name ) values (3,"Tom")");

    ResultSet resultSet = st.executeQuery("SELECT * FROM survey");
    // get concurrency of the ResultSet object
    int concurrency = resultSet.getConcurrency();
    if (concurrency == ResultSet.CONCUR_UPDATABLE) {
        System.out.println("ResultSet is updatable");
    }
    else {
        System.out.println("ResultSet is not updatable");
    }
    resultSet.close();
    st.close();
    conn.close();
  }
  private static Connection getConnection() throws Exception {
    Class.forName("org.hsqldb.jdbcDriver");
    String url = "jdbc:hsqldb:mem:data/tutorial";
    return DriverManager.getConnection(url, "sa", "");
  }
}

Insert a Row into a Database Table Using an 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 {
    Connection conn = getConnection();
    Statement st = conn
        .createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
    st.executeUpdate("create table survey (id int,name varchar(30));");
    st.executeUpdate("insert into survey (id,name ) values (1,"nameValue")");
    st.executeUpdate("insert into survey (id,name ) values (2,null)");
    st.executeUpdate("insert into survey (id,name ) values (3,"Tom")");

    ResultSet rs = st.executeQuery("SELECT id,name FROM survey");
    rs.moveToInsertRow();
    rs.updateString("id", "66");
    rs.updateString("name", "H F");
    // Insert the row
    rs.insertRow();

    rs.close();
    st.close();
    conn.close();
  }
  private static Connection getConnection() throws Exception {
    Class.forName("org.hsqldb.jdbcDriver");
    String url = "jdbc:hsqldb:mem:data/tutorial";
    return DriverManager.getConnection(url, "sa", "");
  }
}

Refresh a Row in an 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 {
    Connection conn = getConnection();
    Statement st = conn
        .createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
    st.executeUpdate("create table survey (id int,name varchar(30));");
    st.executeUpdate("insert into survey (id,name ) values (1,"nameValue")");
    st.executeUpdate("insert into survey (id,name ) values (2,null)");
    st.executeUpdate("insert into survey (id,name ) values (3,"Tom")");
    ResultSet rs = st.executeQuery("SELECT id,name FROM survey");
    // Use the result set
    // Retrieve the current values of the row from the database
    rs.refreshRow();

    rs.close();
    st.close();
    conn.close();
  }
  private static Connection getConnection() throws Exception {
    Class.forName("org.hsqldb.jdbcDriver");
    String url = "jdbc:hsqldb:mem:data/tutorial";
    return DriverManager.getConnection(url, "sa", "");
  }
}

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

Updating the current row of an updatable result set involves calling the ResultSet.updateXXX() methods followed by a call to updateRow():

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 {
    Connection conn = getConnection();
    Statement st = conn
        .createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
    st.executeUpdate("create table survey (id int,name varchar(30));");
    st.executeUpdate("insert into survey (id,name ) values (1,"nameValue")");
    st.executeUpdate("insert into survey (id,name ) values (2,null)");
    st.executeUpdate("insert into survey (id,name ) values (3,"Tom")");

    ResultSet rs = st.executeQuery("SELECT * FROM survey");
    // Move cursor to the row to update
    rs.first();
    // Update the value of column column_1 on that row
    rs.updateString("name", "new data");
    // Update the row; if autocommit is enabled,
    // update is committed
    rs.updateRow();
    rs.close();
    st.close();
    conn.close();
  }
  private static Connection getConnection() throws Exception {
    Class.forName("org.hsqldb.jdbcDriver");
    String url = "jdbc:hsqldb:mem:data/tutorial";
    return DriverManager.getConnection(url, "sa", "");
  }
}