Java 17 Recipes

12-9. Creating a Scrollable ResultSet
 Problem
You have queried the database and obtained some results. You want to store those 
results in an object that allows you to traverse forward and backward through the results, 
updating values as needed.
Chapter 12 Working With Databases

---

461
 Solution
Create a scrollable ResultSet object, and then you can read the next, first, last, and 
previous record. Using a scrollable ResultSet object allows the query results to be 
fetched in any direction so that the data can be retrieved as needed. The following 
example method demonstrates the creation of a scrollable ResultSet object.
private static void queryDbRecipes(){
String sql = "SELECT ID, RECIPE_NUMBER, RECIPE_NAME, DESCRIPTION " +
"FROM RECIPES";
try(PreparedStatement pstmt =conn.prepareStatement(sql,
ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
ResultSet rs = pstmt.executeQuery()) {
rs.first();
System.out.println(rs.getString(2) + ": " + rs.getString(3) +
" - " + rs.getString(4));
rs.next();
System.out.println(rs.getString(2) + ": " + rs.getString(3) +
" - " + rs.getString(4));
rs.previous();
System.out.println(rs.getString(2) + ": " + rs.getString(3) +
" - " + rs.getString(4));
rs.last();
System.out.println(rs.getString(2) + ": " + rs.getString(3) +
" - " + rs.getString(4));
} catch (SQLException ex) {
ex.printStackTrace();
}
}
The main method is:
public static void main(String[] args) {
boolean successFlag = false;
try {
Chapter 12 Working With Databases

---

462
CreateConnection createConn = new CreateConnection();
conn = createConn.getConnection();
// Perform Scrollable Query
queryDbRecipes();
} catch (java.sql.SQLException ex) {
System.out.println(ex);
} finally {
if (conn != null) {
try {
conn.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
}
Executing this method results in the following output using the originally loaded 
data for this chapter.
Successfully connected
12-1: Connecting to a Database - DriverManager and DataSource 
Implementations - More to Come
12-2: Querying a Database and Retrieving Results - Obtaining and Using Data 
from a DBMS
12-1: Connecting to a Database - DriverManager and DataSource 
Implementations - More to Come
12-3: Handling SQL Exceptions - Using SQLException