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

   <source lang="java">

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", "");
 }

}</source>

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.

   <source lang="java">

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", "");
 }

}</source>

java.sql.ResultSet.TYPE_FORWARD_ONLY