Java Tutorial/Database/ResultSet Scrollable

Содержание

1 Check if cursor is in the first row
2 Create an insensitive scrollable result set
3 Demonstrates various methods for moving the cursor in a scrollable ResultSet object
4 Get the current position of cursor
5 Get the Cursor Position in a Scrollable Result Set
6 Get the Number of Rows in a Table Using a Scrollable ResultSet
7 If database support scrollable result sets
8 Move cursor backward
9 Move cursor down 5 rows from the current row. If this moves cursor beyond the last row, cursor is put after the last row
10 Move cursor to the beginning, before the first row
11 Move cursor to the end, after the last row
12 Move cursor to the first row
13 Move cursor to the last row
14 Move cursor to the second last row with aboslute position
15 Move cursor up 3 rows from the current row. If this moves cursor beyond the first row, cursor is put before the first row
16 Move the cursor back and forth with absolute index
17 Move the Cursor in a Scrollable ResultSet
18 Moving the Cursor in a Scrollable Result Set
19 Scrollable ResultSet

Check if cursor is in the first row

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_INSENSITIVE,
        ResultSet.CONCUR_READ_ONLY);
    ResultSet resultSet = statement.executeQuery("SELECT * FROM products");
    if (resultSet.isBeforeFirst()) {
      System.out.println("beginning");
    }
    connection.close();
  }
}

Create an insensitive scrollable 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_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
  }
}

Demonstrates various methods for moving the cursor in a scrollable ResultSet object

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");
    // Move cursor forward
    while (rs.next()) {
      // Get data at cursor
      String id = rs.getString(1);
      String name = rs.getString(2);
    }
    // Move cursor backward
    while (rs.previous()) {
      // Get data at cursor
      String id = rs.getString(1);
      String name = rs.getString(2);
    }
    // Move cursor to the first row
    rs.first();
    // Move cursor to the last row
    rs.last();
    // Move cursor to the end, after the last row
    rs.afterLast();
    // Move cursor to the beginning, before the first row.
    // cursor position is 0.
    rs.beforeFirst();
    // Move cursor to the second row
    rs.absolute(2);
    // Move cursor to the last row
    rs.absolute(-1);
    // Move cursor to the second-to-last row
    rs.absolute(-2);
    // Move cursor down 5 rows from the current row. If this moves
    // cursor beyond the last row, cursor is put after the last row
    rs.relative(5);
    // Move cursor up 3 rows from the current row. If this moves
    // cursor beyond the first row, cursor is put before the first row
    rs.relative(-3);
    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", "");
  }
}

Get the current position of cursor

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_INSENSITIVE,
        ResultSet.CONCUR_READ_ONLY);
    ResultSet resultSet = statement.executeQuery("SELECT * FROM products");
    while (resultSet.next()) {
      String productCode = resultSet.getString("product_code");
      int row = resultSet.getRow();
      System.out.println(row + ". " + productCode);
    }
    connection.close();
  }
}

Get the Cursor Position in a Scrollable 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[] 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");
    // Get cursor position
    int pos = rs.getRow();          // 0
    System.out.println(pos);
    boolean b = rs.isBeforeFirst(); // true
    System.out.println(b);
    // Move cursor to the first row
    rs.next();
    // Get cursor position
    pos = rs.getRow();              // 1
    b = rs.isFirst();               // true
    System.out.println(pos);
    System.out.println(b);
    
    
    // Move cursor to the last row
    rs.last();
    // Get cursor position
    pos = rs.getRow();   
    System.out.println(pos);
    b = rs.isLast();     // true
    // Move cursor past last row
    rs.afterLast();
    // Get cursor position
    pos = rs.getRow();       
    b = rs.isAfterLast();    // true
    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", "");
  }
}

Get the Number of Rows in a Table Using a Scrollable 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_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");
    // move to the end of the result set
    rs.last();
    // get the row number of the last row, which is also the row count
    int rowCount = rs.getRow();
    System.out.println(rowCount);
    // now you may move the cursor to the front of this ResultSet object,
    // just before the first row
    rs.beforeFirst();
    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", "");
  }
}

If database support scrollable 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 supportForwardOnly = metadata.supportsResultSetType(ResultSet.TYPE_FORWARD_ONLY);
    System.out.println("supportForwardOnly = " + supportForwardOnly);
    boolean supportScrollInsensitive = metadata
        .supportsResultSetType(ResultSet.TYPE_SCROLL_INSENSITIVE);
    System.out.println("supportScrollInsensitive = " + supportScrollInsensitive);
    boolean supportScrollSensitive = metadata
        .supportsResultSetType(ResultSet.TYPE_SCROLL_SENSITIVE);
    System.out.println("supportScrollSensitive = " + supportScrollSensitive);
    connection.close();
  }
}

Move cursor backward

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 scrollable result set
    Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
        ResultSet.CONCUR_READ_ONLY);
    ResultSet resultSet = stmt.executeQuery("SELECT * FROM my_table");
    // Move cursor forward
    while (resultSet.next()) {
      // Get data at cursor
      String s = resultSet.getString(1);
    }
    // Move cursor backward
    while (resultSet.previous()) {
      // Get data at cursor
      String s = resultSet.getString(1);
    }

  }
}

Move cursor down 5 rows from the current row. If this moves cursor beyond the last row, cursor is put after the last row

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 scrollable result set
    Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
    ResultSet resultSet = stmt.executeQuery("SELECT * FROM my_table");
    // Move cursor forward
    while (resultSet.next()) {
      // Get data at cursor
      String s = resultSet.getString(1);
    }
    // Move cursor backward
    while (resultSet.previous()) {
      // Get data at cursor
      String s = resultSet.getString(1);
    }
    // Move cursor down 5 rows from the current row. If this moves cursor beyond the last row, cursor is put after the last row
    resultSet.relative(5);
  }
}

Move cursor to the beginning, before the first row

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 scrollable result set
    Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
        ResultSet.CONCUR_READ_ONLY);
    ResultSet resultSet = stmt.executeQuery("SELECT * FROM my_table");
    // Move cursor forward
    while (resultSet.next()) {
      // Get data at cursor
      String s = resultSet.getString(1);
    }
    // Move cursor backward
    while (resultSet.previous()) {
      // Get data at cursor
      String s = resultSet.getString(1);
    }
    // Move cursor to the beginning, before the first row.
    // cursor position is 0.
    resultSet.beforeFirst();
  }
}

Move cursor to the end, after the last row

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 scrollable result set
    Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
        ResultSet.CONCUR_READ_ONLY);
    ResultSet resultSet = stmt.executeQuery("SELECT * FROM my_table");
    // Move cursor forward
    while (resultSet.next()) {
      // Get data at cursor
      String s = resultSet.getString(1);
    }
    // Move cursor backward
    while (resultSet.previous()) {
      // Get data at cursor
      String s = resultSet.getString(1);
    }
    // Move cursor to the end, after the last row
    resultSet.afterLast();
  }
}

Move cursor to the first row

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 scrollable result set
    Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
        ResultSet.CONCUR_READ_ONLY);
    ResultSet resultSet = stmt.executeQuery("SELECT * FROM my_table");
    // Move cursor forward
    while (resultSet.next()) {
      // Get data at cursor
      String s = resultSet.getString(1);
    }
    // Move cursor backward
    while (resultSet.previous()) {
      // Get data at cursor
      String s = resultSet.getString(1);
    }
    // Move cursor to the first row
    resultSet.first();
  }
}

Move cursor to the last row

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 scrollable result set
    Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
        ResultSet.CONCUR_READ_ONLY);
    ResultSet resultSet = stmt.executeQuery("SELECT * FROM my_table");
    // Move cursor forward
    while (resultSet.next()) {
      // Get data at cursor
      String s = resultSet.getString(1);
    }
    // Move cursor backward
    while (resultSet.previous()) {
      // Get data at cursor
      String s = resultSet.getString(1);
    }
    // Move cursor to the last row
    resultSet.last();
  }
}

Move cursor to the second last row with aboslute position

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 scrollable result set
    Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
        ResultSet.CONCUR_READ_ONLY);
    ResultSet resultSet = stmt.executeQuery("SELECT * FROM my_table");
    // Move cursor forward
    while (resultSet.next()) {
      // Get data at cursor
      String s = resultSet.getString(1);
    }
    // Move cursor backward
    while (resultSet.previous()) {
      // Get data at cursor
      String s = resultSet.getString(1);
    }
    // Move cursor to the second row
    resultSet.absolute(2);
    // Move cursor to the last row
    resultSet.absolute(-1);
    // Move cursor to the second last row
    resultSet.absolute(-2);
  }
}

Move cursor up 3 rows from the current row. If this moves cursor beyond the first row, cursor is put before the first row

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 scrollable result set
    Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
        ResultSet.CONCUR_READ_ONLY);
    ResultSet resultSet = stmt.executeQuery("SELECT * FROM my_table");
    // Move cursor forward
    while (resultSet.next()) {
      // Get data at cursor
      String s = resultSet.getString(1);
    }
    // Move cursor backward
    while (resultSet.previous()) {
      // Get data at cursor
      String s = resultSet.getString(1);
    }
    // Move cursor up 3 rows from the current row. If this moves cursor beyond the first row, cursor is put before the first row
    resultSet.relative(-3);
  }
}

Move the cursor back and forth with absolute index

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 scrollable result set
    Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
        ResultSet.CONCUR_READ_ONLY);
    ResultSet resultSet = stmt.executeQuery("SELECT * FROM my_table");
    // Move cursor forward
    while (resultSet.next()) {
      // Get data at cursor
      String s = resultSet.getString(1);
    }
    // Move cursor backward
    while (resultSet.previous()) {
      // Get data at cursor
      String s = resultSet.getString(1);
    }
    // Move cursor to the second row
    resultSet.absolute(2);
    // Move cursor to the last row
    resultSet.absolute(-1);
    // Move cursor to the second last row
    resultSet.absolute(-2);
  }
}

Move the Cursor in a Scrollable ResultSet

A scrollable ResultSet object has a set of specific methods for moving cursors.

MethodSemanticsfirst()Moves to the first recordlast()Moves to the last recordnext()Moves to the next recordprevious()Moves to the previous recordbeforeFirst()Moves to immediately before the first recordafterLast()Moves to immediately after the last recordabsolute(int)Moves to an absolute row number, and takes a positive or negative argumentrelative(int)Moves backward or forward a specified number of rows, and takes a positive or negative argument

Moving the Cursor in a Scrollable 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 scrollable result set
    Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
        ResultSet.CONCUR_READ_ONLY);
    ResultSet resultSet = stmt.executeQuery("SELECT * FROM my_table");
    while (resultSet.next()) {
      // Get data at cursor
      String s = resultSet.getString(1);
    }
    while (resultSet.previous()) {
      // Get data at cursor
      String s = resultSet.getString(1);
    }
    // Move cursor to the first row
    resultSet.first();
    // Move cursor to the last row
    resultSet.last();
    // Move cursor to the end, after the last row
    resultSet.afterLast();

  }
}

Scrollable ResultSet

For a scrollable ResultSet, you can move its cursor backward as well as forward.

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")");
    st = conn.createStatement();
    ResultSet rs = st.executeQuery("SELECT * 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", "");
  }
}