Java/Database SQL JDBC/ResultSet Scrollable

Содержание

1 Change the fetch size on the result set
2 Check if cursor is in the first row
3 Create an insensitive scrollable result set
4 Create a scrollable result sets
5 Create a sensitive scrollable result set
6 Demo Scrollable ResultSet from MySQL
7 Determining If a Database Supports Scrollable Result Sets
8 Get Number Of Rows in a Scrollable ResultSet from MySQL
9 Get the current position of cursor
10 Get the fetch size of a statement
11 Getting the Cursor Position in a Scrollable Result Set
12 Getting the Number of Rows in a Table Using a Scrollable Result Set
13 If cursor is in the last row
14 Move cursor backward
15 Move cursor down 5 rows from the current row. If this moves cursor beyond the last row, cursor is put after the last row
16 Move cursor in scrollable result sets
17 Move cursor to the beginning, before the first row
18 Move cursor to the end, after the last row
19 Move cursor to the first row
20 Move cursor to the last record
21 Move cursor to the last row
22 Move cursor to the second last row with absolute position
23 Move cursor up 3 rows from the current row. If this moves cursor beyond the first row, cursor is put before the first row
24 Move the cursor back and forth with absolute index
25 Moving the Cursor in a Scrollable Result Set
26 Set the fetch size on the statement

Change the fetch size on the result set

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

}

 </source>

Check if cursor is in the first row

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

}

 </source>

Create an insensitive scrollable result set

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

}

 </source>

Create a scrollable result sets

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

}

 </source>

Create a sensitive scrollable result set

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

}

 </source>

Demo Scrollable ResultSet from MySQL

   <source lang="java">

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

}

 </source>

Determining If a Database Supports Scrollable Result Sets

   <source lang="java">

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

}

 </source>

Get Number Of Rows in a Scrollable ResultSet from MySQL

   <source lang="java">

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

}

 </source>

Get the current position of cursor

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

}

 </source>

Get the fetch size of a statement

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

}

 </source>

Getting the Cursor Position in a Scrollable Result Set

   <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[] 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(); 
 }

}

 </source>

Getting the Number of Rows in a Table Using a Scrollable Result Set

   <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[] 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();
 }

}

 </source>

If cursor is in the last row

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

}

 </source>

Move cursor backward

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

}

 </source>

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

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

}

 </source>

Move cursor in scrollable result sets

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

}

 </source>

Move cursor to the beginning, before the first row

   <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[] 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();
 }

}

 </source>

Move cursor to the end, after the last row

   <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[] 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();
 }

}

 </source>

Move cursor to the first row

   <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[] 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();
 }

}

 </source>

Move cursor to the last record

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

}

 </source>

Move cursor to the last row

   <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[] 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();
 }

}

 </source>

Move cursor to the second last row with absolute position

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

}

 </source>

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

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

}

 </source>

Move the cursor back and forth with absolute index

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

}

 </source>

Moving the Cursor in a Scrollable Result Set

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

}

 </source>

Set the fetch size on the statement

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

}

 </source>

Java/Database SQL JDBC/ResultSet Scrollable

Содержание

Change the fetch size on the result set

Check if cursor is in the first row

Create an insensitive scrollable result set

Create a scrollable result sets

Create a sensitive scrollable result set

Demo Scrollable ResultSet from MySQL

Determining If a Database Supports Scrollable Result Sets

Get Number Of Rows in a Scrollable ResultSet from MySQL

Get the current position of cursor

Get the fetch size of a statement

Getting the Cursor Position in a Scrollable Result Set

Getting the Number of Rows in a Table Using a Scrollable Result Set

If cursor is in the last row

Move cursor backward

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

Move cursor in scrollable result sets

Move cursor to the beginning, before the first row

Move cursor to the end, after the last row

Move cursor to the first row

Move cursor to the last record

Move cursor to the last row

Move cursor to the second last row with absolute position

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

Move the cursor back and forth with absolute index

Moving the Cursor in a Scrollable Result Set

Set the fetch size on the statement

Навигация

Персональные инструменты

Пространства имён

Варианты

Просмотры

Ещё

Поиск

Разделы

Навигация

Инструменты