Java Tutorial/Database/ResultSet Scrollable
Содержание
- 1 Check if cursor is in the first row
- 2 Create an insensitive scrollable result set
- 3 Demonstrates various methods for moving the cursor in a scrollable ResultSet object
- 4 Get the current position of cursor
- 5 Get the Cursor Position in a Scrollable Result Set
- 6 Get the Number of Rows in a Table Using a Scrollable ResultSet
- 7 If database support scrollable result sets
- 8 Move cursor backward
- 9 Move cursor down 5 rows from the current row. If this moves cursor beyond the last row, cursor is put after the last row
- 10 Move cursor to the beginning, before the first row
- 11 Move cursor to the end, after the last row
- 12 Move cursor to the first row
- 13 Move cursor to the last row
- 14 Move cursor to the second last row with aboslute position
- 15 Move cursor up 3 rows from the current row. If this moves cursor beyond the first row, cursor is put before the first row
- 16 Move the cursor back and forth with absolute index
- 17 Move the Cursor in a Scrollable ResultSet
- 18 Moving the Cursor in a Scrollable Result Set
- 19 Scrollable ResultSet
Check if cursor is in the first row
<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>
Demonstrates various methods for moving the cursor in a scrollable ResultSet object
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class Main {
public static void main(String[] args) throws Exception {
Connection conn = getConnection();
Statement st = conn
.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
st.executeUpdate("create table survey (id int,name varchar(30));");
st.executeUpdate("insert into survey (id,name ) values (1,"nameValue")");
st.executeUpdate("insert into survey (id,name ) values (2,null)");
st.executeUpdate("insert into survey (id,name ) values (3,"Tom")");
ResultSet rs = st.executeQuery("SELECT * FROM survey");
// Move cursor forward
while (rs.next()) {
// Get data at cursor
String id = rs.getString(1);
String name = rs.getString(2);
}
// Move cursor backward
while (rs.previous()) {
// Get data at cursor
String id = rs.getString(1);
String name = rs.getString(2);
}
// Move cursor to the first row
rs.first();
// Move cursor to the last row
rs.last();
// Move cursor to the end, after the last row
rs.afterLast();
// Move cursor to the beginning, before the first row.
// cursor position is 0.
rs.beforeFirst();
// Move cursor to the second row
rs.absolute(2);
// Move cursor to the last row
rs.absolute(-1);
// Move cursor to the second-to-last row
rs.absolute(-2);
// Move cursor down 5 rows from the current row. If this moves
// cursor beyond the last row, cursor is put after the last row
rs.relative(5);
// Move cursor up 3 rows from the current row. If this moves
// cursor beyond the first row, cursor is put before the first row
rs.relative(-3);
rs.close();
st.close();
conn.close();
}
private static Connection getConnection() throws Exception {
Class.forName("org.hsqldb.jdbcDriver");
String url = "jdbc:hsqldb:mem:data/tutorial";
return DriverManager.getConnection(url, "sa", "");
}
}</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 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[] args) throws Exception {
Connection conn = getConnection();
Statement st = conn
.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
st.executeUpdate("create table survey (id int,name varchar(30));");
st.executeUpdate("insert into survey (id,name ) values (1,"nameValue")");
st.executeUpdate("insert into survey (id,name ) values (2,null)");
st.executeUpdate("insert into survey (id,name ) values (3,"Tom")");
ResultSet rs = st.executeQuery("SELECT * FROM survey");
// Get cursor position
int pos = rs.getRow(); // 0
System.out.println(pos);
boolean b = rs.isBeforeFirst(); // true
System.out.println(b);
// Move cursor to the first row
rs.next();
// Get cursor position
pos = rs.getRow(); // 1
b = rs.isFirst(); // true
System.out.println(pos);
System.out.println(b);
// Move cursor to the last row
rs.last();
// Get cursor position
pos = rs.getRow();
System.out.println(pos);
b = rs.isLast(); // true
// Move cursor past last row
rs.afterLast();
// Get cursor position
pos = rs.getRow();
b = rs.isAfterLast(); // true
rs.close();
st.close();
conn.close();
}
private static Connection getConnection() throws Exception {
Class.forName("org.hsqldb.jdbcDriver");
String url = "jdbc:hsqldb:mem:data/tutorial";
return DriverManager.getConnection(url, "sa", "");
}
}</source>
Get the Number of Rows in a Table Using a Scrollable ResultSet
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class Main {
public static void main(String[] args) throws Exception {
Connection conn = getConnection();
Statement st = conn
.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
st.executeUpdate("create table survey (id int,name varchar(30));");
st.executeUpdate("insert into survey (id,name ) values (1,"nameValue")");
st.executeUpdate("insert into survey (id,name ) values (2,null)");
st.executeUpdate("insert into survey (id,name ) values (3,"Tom")");
ResultSet rs = st.executeQuery("SELECT * FROM survey");
// move to the end of the result set
rs.last();
// get the row number of the last row, which is also the row count
int rowCount = rs.getRow();
System.out.println(rowCount);
// now you may move the cursor to the front of this ResultSet object,
// just before the first row
rs.beforeFirst();
rs.close();
st.close();
conn.close();
}
private static Connection getConnection() throws Exception {
Class.forName("org.hsqldb.jdbcDriver");
String url = "jdbc:hsqldb:mem:data/tutorial";
return DriverManager.getConnection(url, "sa", "");
}
}</source>
If database support 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 {
private static final String DRIVER = "com.mysql.jdbc.Driver";
private static final String URL = "jdbc:mysql://localhost/yourDatabase";
private static final String USERNAME = "root";
private static final String PASSWORD = "";
public static void main(String[] args) throws Exception {
Class.forName(DRIVER);
Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
DatabaseMetaData metadata = connection.getMetaData();
boolean supportForwardOnly = metadata.supportsResultSetType(ResultSet.TYPE_FORWARD_ONLY);
System.out.println("supportForwardOnly = " + supportForwardOnly);
boolean supportScrollInsensitive = metadata
.supportsResultSetType(ResultSet.TYPE_SCROLL_INSENSITIVE);
System.out.println("supportScrollInsensitive = " + supportScrollInsensitive);
boolean supportScrollSensitive = metadata
.supportsResultSetType(ResultSet.TYPE_SCROLL_SENSITIVE);
System.out.println("supportScrollSensitive = " + supportScrollSensitive);
connection.close();
}
}</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 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 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 aboslute 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>
Move the Cursor in a Scrollable ResultSet
A scrollable ResultSet object has a set of specific methods for moving cursors.
MethodSemanticsfirst()Moves to the first recordlast()Moves to the last recordnext()Moves to the next recordprevious()Moves to the previous recordbeforeFirst()Moves to immediately before the first recordafterLast()Moves to immediately after the last recordabsolute(int)Moves to an absolute row number, and takes a positive or negative argumentrelative(int)Moves backward or forward a specified number of rows, and takes a positive or negative argument
Moving the Cursor in a Scrollable Result Set
<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>
Scrollable ResultSet
For a scrollable ResultSet, you can move its cursor backward as well as forward.
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class Main {
public static void main(String[] args) throws Exception {
Connection conn = getConnection();
Statement st = conn.createStatement(
ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);
st.executeUpdate("create table survey (id int,name varchar(30));");
st.executeUpdate("insert into survey (id,name ) values (1,"nameValue")");
st.executeUpdate("insert into survey (id,name ) values (2,null)");
st.executeUpdate("insert into survey (id,name ) values (3,"Tom")");
st = conn.createStatement();
ResultSet rs = st.executeQuery("SELECT * FROM survey");
rs.close();
st.close();
conn.close();
}
private static Connection getConnection() throws Exception {
Class.forName("org.hsqldb.jdbcDriver");
String url = "jdbc:hsqldb:mem:data/tutorial";
return DriverManager.getConnection(url, "sa", "");
}
}</source>
