Java/Database SQL JDBC/ResultSet Scrollable

Материал из Java эксперт
Версия от 06:34, 1 июня 2010; Admin (обсуждение | вклад) (1 версия)
(разн.) ← Предыдущая | Текущая версия (разн.) | Следующая → (разн.)
Перейти к: навигация, поиск

Change the fetch size on the 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);
    // Get the fetch size of a statement
    Statement stmt = connection.createStatement ();
    int fetchSize = stmt.getFetchSize();
    // Set the fetch size on the statement
    stmt.setFetchSize(100);
    // Create a result set
    ResultSet resultSet = stmt.executeQuery("SELECT * FROM my_table");
    // Change the fetch size on the result set
    resultSet.setFetchSize(100);
  }
}





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





Create a scrollable result sets

  
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");
    connection.close();
  }
}





Create a sensitive 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_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
  }
}





Demo Scrollable 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 DemoScrollableResultSet_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_READ_ONLY);
      rs = stmt.executeQuery(query);
      // 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);
      }
      // 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);
      }
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      // release database resources
      try {
        rs.close();
        stmt.close();
        conn.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
  }
}





Determining If a Database Supports Scrollable Result Sets

  
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.supportsResultSetType(ResultSet.TYPE_SCROLL_INSENSITIVE)) {
        System.out.println("Insensitive scrollable result sets are supported");
    }
    if (dmd.supportsResultSetType(ResultSet.TYPE_SCROLL_SENSITIVE)) {
        System.out.println("Sensitive scrollable result sets are supported"); 
    }
    if (!dmd.supportsResultSetType(ResultSet.TYPE_SCROLL_INSENSITIVE)
        && !dmd.supportsResultSetType(ResultSet.TYPE_SCROLL_SENSITIVE)) {
        System.out.println("Updatable result sets are not supported");
    }
  }
}





Get Number Of Rows in a Scrollable 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 GetNumberOfRowsScrollableResultSet_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);
    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 from employees";
      stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
      rs = stmt.executeQuery(query);
      // extract data from the ResultSet scroll from top
      while (rs.next()) {
        String id = rs.getString(1);
        System.out.println("id=" + id);
      }
      // 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=" + rowCount);
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      try {
        rs.close();
        stmt.close();
        conn.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
  }
}





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 fetch size of a statement

  
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);
    // Get the fetch size of a statement
    Statement stmt = connection.createStatement ();
    int fetchSize = stmt.getFetchSize();
    // Set the fetch size on the statement
    stmt.setFetchSize(100);
    // Create a result set
    ResultSet resultSet = stmt.executeQuery("SELECT * FROM my_table");
    // Change the fetch size on the result set
    resultSet.setFetchSize(100);
  }
}





Getting 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[] 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);
    ResultSet resultSet = stmt.executeQuery("SELECT * FROM my_table");
    // Get cursor position
    int pos = resultSet.getRow(); 
    boolean b = resultSet.isBeforeFirst(); 
    // Move cursor to the first row
    resultSet.next();
    // Get cursor position
    pos = resultSet.getRow(); 
    b = resultSet.isFirst(); 
    // Move cursor to the last row
    resultSet.last();
    // Get cursor position
    pos = resultSet.getRow(); 
    b = resultSet.isLast(); 
    // Move cursor past last row
    resultSet.afterLast();
    // Get cursor position
    pos = resultSet.getRow(); 
    b = resultSet.isAfterLast(); 
  }
}





Getting the Number of Rows in a Table Using 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");
    // Move to the end of the result set
    resultSet.last();
    // Get the row number of the last row which is also the row count
    int rowCount = resultSet.getRow();
  }
}





If cursor is in 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[] 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.isAfterLast()) {
      System.out.println("You are at the beginning of the result set.");
    }
    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 in scrollable result sets

  
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");
      String productName = resultSet.getString("product_name");
      int quantity = resultSet.getInt("quantity");
      double price = resultSet.getDouble("price");
      System.out.println(productCode + "\t" + productName + "\t" + quantity + "\t" + price);
    }
    while (resultSet.previous()) {
      String productCode = resultSet.getString("product_code");
      String productName = resultSet.getString("product_name");
      int quantity = resultSet.getInt("quantity");
      double price = resultSet.getDouble("price");
      System.out.println(productCode + "\t" + productName + "\t" + quantity + "\t" + price);
    }
    connection.close();
  }
}





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 record

  
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");
    resultSet.afterLast();
    while (resultSet.previous()) {
      String productCode = resultSet.getString("product_code");
      String productName = resultSet.getString("product_name");
      int quantity = resultSet.getInt("quantity");
      double price = resultSet.getDouble("price");
      System.out.println(productCode + "\t" + productName + "\t" + quantity + "\t" + price);
    }
    connection.close();
  }
}





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 absolute 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);
  }
}





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();

  }
}





Set the fetch size on the statement

  
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);
    // Get the fetch size of a statement
    Statement stmt = connection.createStatement ();
    int fetchSize = stmt.getFetchSize();
    // Set the fetch size on the statement
    stmt.setFetchSize(100);
    // Create a result set
    ResultSet resultSet = stmt.executeQuery("SELECT * FROM my_table");
    // Change the fetch size on the result set
    resultSet.setFetchSize(100);
  }
}


Источник — «http://jexp.ru/index.php?title=Java/Database_SQL_JDBC/ResultSet_Scrollable&oldid=6937»