Java Tutorial/Database/ResultSet Type

Create Statement and set the ResultSet type: read only, scrollable

When creating a Statement object, you need to specify two arguments to the method createStatement().

The first argument indicates the type of a ResultSet object:

1. ResultSet.TYPE_FORWARD_ONLY: cursor may move only forward
2. ResultSet.TYPE_SCROLL_INSENSITIVE: scrollable but generally not sensitive to changes made by others
3. ResultSet.TYPE_SCROLL_SENSITIVE: scrollable and generally sensitive to changes made by others

The second argument:

1. ResultSet.CONCUR_READ_ONLY: the concurrency mode that may not be updated
2. ResultSet.CONCUR_UPDATABLE: the concurrency mode that may be updated

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_READ_ONLY);
    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");

    // extract data from the ResultSet scroll from top
    while (rs.next()) {
        String id = rs.getString(1);
        String name = rs.getString(2);
        System.out.println("id=" + id + " name=" + name);
    }
    System.out.println("---------");
    // scroll from the bottom
    rs.afterLast();
    while (rs.previous()) {
        String id = rs.getString(1);
        String name = rs.getString(2);
        System.out.println("id=" + id + " name=" + name);
    }
    
    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", "");
  }
}

id=1 name=nameValue
id=2 name=null
id=3 name=Tom
---------
id=3 name=Tom
id=2 name=null
id=1 name=nameValue

Get a ResultSet Type

1. java.sql.ResultSet.TYPE_FORWARD_ONLY
2. java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE
3. java.sql.ResultSet.TYPE_SCROLL_SENSITIVE

DatabaseMetaData.supportsResultSetType(): determine which ones are supported.

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_INSENSITIVE,
        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 = conn.createStatement();
    ResultSet rs = st.executeQuery("SELECT * FROM survey");
    int rsType = rs.getType();
    if (rsType == java.sql.ResultSet.TYPE_FORWARD_ONLY) {
      System.out.println("java.sql.ResultSet.TYPE_FORWARD_ONLY");
    } else if (rsType == java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE) {
      System.out.println("java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE");
    } else if (rsType == java.sql.ResultSet.TYPE_SCROLL_SENSITIVE) {
      System.out.println("java.sql.ResultSet.TYPE_SCROLL_SENSITIVE");
    } else {
      // it is an error
    }
    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", "");
  }
}

java.sql.ResultSet.TYPE_FORWARD_ONLY