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>