Scrollable ResultSet example

In this example we shall show you how to create and use a scrollable ResultSet. To use a scrollable ResultSet one should perform the following steps:

• Load the JDBC driver, using the forName(String className) API method of the Class. In this example we use the MySQL JDBC driver.
• Create a Connection to the database. Invoke the getConnection(String url, String user, String password) API method of the DriverManager to create the connection.
• Create a Statement, using the createStatement() API method of the Connection. The Statement must have the type ResultSet.TYPE_SCROLL_INSENSITIVE or ResultSet.TYPE_SCROLL_SENSITIVE and the concurrency ResultSet.CONCUR_UPDATABLE, in order to return scrollable result sets.
• Execute the query to the database, using the executeQuery(String sql) API method. The data produced by the given query is a ResultSet.
• Get the cursor position, with the getRow() API method and check if it is before the first row, with the isBeforeFirst() API method.
• Invoke the next() API method to move the cursor to next row, and last() API method to move cursor to the last row. In order to check if it is in the last row, we can call the isLast() API method.
• Move the cursor to the end of this ResultSet object, just after the last row, with the afterLast() API method and use the isAfterLast() API method to check if it is after the last row.
• Move cursor to other rows, with the absolute(int row) API method and check again its position.
• Invoke the relative(int rows) API method to move the cursor

as described in the code snippet below.

001	package com.javacodegeeks.snippets.core;

002

003	import java.sql.Connection;

004	import java.sql.DriverManager;

005	import java.sql.ResultSet;

006	import java.sql.SQLException;

007	import java.sql.Statement;

008

009	public class ScrollableResultSetExample {

010

011	public static void main(String[] args) {

012

013	Connection connection = null;

014

try {

015

016	// Load the MySQL JDBC driver

017

018	String driverName = "com.mysql.jdbc.Driver";

019

020	Class.forName(driverName);

021

022

023	// Create a connection to the database

024

025	String serverName = "localhost";

026

027	String schema = "test";

028

029	String url = "jdbc:mysql://" + serverName +  "/" + schema;

030

031	String username = "username";

032

033	String password = "password";

034

035	connection = DriverManager.getConnection(url, username, password);

036

037

038

039	System.out.println("Successfully Connected to the database!");

040

041

042	} catch (ClassNotFoundException e) {

043

044	System.out.println("Could not find the database driver " + e.getMessage());

045	} catch (SQLException e) {

046

047	System.out.println("Could not connect to the database " + e.getMessage());

048

}

049

050

try {

051

052

053

/*

054

055	* An insensitive scrollable result set is one where the values captured in the

056

057	* result set never change, even if changes are made to the table from which the

058

059	* data was retrieved.

060

061	* A sensitive scrollable result set is one where the current values in the table

062

063	* are reflected in the result set. So if a change is made to a row in the table,

064

065	* the result set will show the new data when the cursor is moved to that row

066

067

*/

068

069

070	// Create an insensitive scrollable result set (for sensitive scrollable result sets use ResultSet.TYPE_SCROLL_SENSITIVE directive)

071

072	Statement statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

073

074	ResultSet results = statement.executeQuery("SELECT * FROM test_table");

075

076

077	// Get cursor position

078

079	System.out.println("Cursor position " + results.getRow() + ", is before first ? " + results.isBeforeFirst());

080

081

082	// Every call to next() moves cursor to the next row - in this case the first row

083

084	results.next();

085

086

087	// Get cursor position

088

089	System.out.println("Cursor position " + results.getRow() + ", is first ? " + results.isFirst());

090

091

092	// A call to last() moves cursor to the last row; the row number is also the row count

093

094	results.last();

095

096

097	// Get cursor position

098

099	System.out.println("Cursor position " + results.getRow() + ", is last ? " + results.isLast());

100

101

102	// A call to after last moves cursor past last row (before first row)

103

104	results.afterLast();

105

106

107	// Get cursor position

108

109	System.out.println("Cursor position " + results.getRow() + ", is after last ? " + results.isAfterLast());

110

111

112	// Move cursor to the third row

113

114	results.absolute(3);

115

116

117	// Get cursor position

118

119	System.out.println("Cursor position " + results.getRow());

120

121

122	// Move cursor to the last row

123

124	results.absolute(-1);

125

126

127	// Get cursor position

128

129	System.out.println("Cursor position " + results.getRow() + ", is last ? " + results.isLast());

130

131

132	// Move cursor to the forth last row

133

134	results.absolute(-4);

135

136

137	// Get cursor position

138

139	System.out.println("Cursor position " + results.getRow());

140

141

142	// Move cursor down 5 rows from the current row.  If this moves

143

144	// cursor beyond the last row, cursor is put after the last row

145

146	results.relative(5);

147

148

149	// Get cursor position

150

151	System.out.println("Cursor position " + results.getRow() + ", is after last ? " + results.isAfterLast());

152

153

154	// Move cursor up 13 rows from the current row.  If this moves

155

156	// cursor beyond the first row, cursor is put before the first row

157

158	results.relative(-13);

159

160

161	// Get cursor position

162

163	System.out.println("Cursor position " + results.getRow() + ", is before first ? " + results.isBeforeFirst());

164

165

166	} catch (SQLException e) {

167

168	System.out.println("Could not retrieve data from the database " + e.getMessage());

169

170

}

171

172

}

173

}

Example Output:

Successfully Connected to the database!
Cursor position 0, is before first ? true
Cursor position 1, is first ? true
Cursor position 11, is last ? true
Cursor position 0, is after last ? true
Cursor position 3
Cursor position 11, is last ? true
Cursor position 8
Cursor position 0, is after last ? true
Cursor position 0, is before first ? true

 
This was an example of how to create and use a scrollable ResultSet in Java.