Working with ResultSets

The Data Retrieval Gateway

When you execute a SELECT query, JDBC returns a ResultSet - a table-like object containing the data your query fetched. Think of it as a scrollable cursor pointing to your data, allowing you to navigate row by row and extract column values.

Understanding ResultSet

A ResultSet represents a set of results from a database query:

┌─────────────────────────────────────────────────────────────────┐
│                        ResultSet Structure                       │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│   ┌─────────────────────────────────────────────────────┐       │
│   │  Before First Row  ← Initial cursor position        │       │
│   ├─────────────────────────────────────────────────────┤       │
│   │  Row 1: id=1, name="Alice", email="alice@mail.com" │       │
│   ├─────────────────────────────────────────────────────┤       │
│   │  Row 2: id=2, name="Bob", email="bob@mail.com"     │  ←    │
│   ├─────────────────────────────────────────────────────┤       │
│   │  Row 3: id=3, name="Carol", email="carol@mail.com" │       │
│   ├─────────────────────────────────────────────────────┤       │
│   │  After Last Row                                     │       │
│   └─────────────────────────────────────────────────────┘       │
│                                                                 │
│   ↑ Cursor can move through rows                                │
│                                                                 │
└─────────────────────────────────────────────────────────────────┘

Basic ResultSet Navigation

The most common pattern - iterating forward through all rows:

String sql = "SELECT id, name, email FROM users";

try (Statement stmt = conn.createStatement();
     ResultSet rs = stmt.executeQuery(sql)) {
    
    while (rs.next()) {  // Move to next row, returns false when done
        int id = rs.getInt("id");
        String name = rs.getString("name");
        String email = rs.getString("email");
        
        System.out.println(id + ": " + name + " - " + email);
    }
}

Important: The cursor starts before the first row. You must call next() to move to the first row.

Retrieving Data from Columns

ResultSet provides type-specific getter methods. You can access columns by name or by index (1-based):

By Column Name (Recommended)

while (rs.next()) {
    String name = rs.getString("name");        // More readable
    int age = rs.getInt("age");                // Self-documenting
    double salary = rs.getDouble("salary");    // Clearer intent
}

By Column Index (1-based)

while (rs.next()) {
    int id = rs.getInt(1);           // First column
    String name = rs.getString(2);    // Second column
    double price = rs.getDouble(3);   // Third column
}

Best Practice: Use column names for readability and maintainability. Column indices are faster but harder to maintain if the query changes.

Complete Data Type Getters

Handling NULL Values

Database NULL values require special handling. After calling a getter, use wasNull() to check:

while (rs.next()) {
    int age = rs.getInt("age");
    
    if (rs.wasNull()) {
        System.out.println("Age is not specified");
    } else {
        System.out.println("Age: " + age);
    }
}

Alternative approach - Use wrapper types with getObject():

Integer age = rs.getObject("age", Integer.class);  // Returns null if NULL
Double salary = rs.getObject("salary", Double.class);

if (age != null) {
    // Process age
}

Working with Dates and Times

JDBC uses its own date/time classes, but you can convert to java.time:

while (rs.next()) {
    // Get JDBC types
    java.sql.Date sqlDate = rs.getDate("birth_date");
    java.sql.Time sqlTime = rs.getTime("login_time");
    java.sql.Timestamp sqlTimestamp = rs.getTimestamp("created_at");
    
    // Convert to java.time (modern API)
    if (sqlDate != null) {
        LocalDate birthDate = sqlDate.toLocalDate();
    }
    
    if (sqlTime != null) {
        LocalTime loginTime = sqlTime.toLocalTime();
    }
    
    if (sqlTimestamp != null) {
        LocalDateTime createdAt = sqlTimestamp.toLocalDateTime();
    }
}

ResultSet Types

When creating a Statement, you can specify the ResultSet type for different navigation capabilities:

TYPE_FORWARD_ONLY (Default)

Cursor can only move forward. Most efficient for simple reads.

Statement stmt = conn.createStatement(
    ResultSet.TYPE_FORWARD_ONLY,
    ResultSet.CONCUR_READ_ONLY
);

TYPE_SCROLL_INSENSITIVE

Cursor can move in any direction. Changes by others are not visible.

Statement stmt = conn.createStatement(
    ResultSet.TYPE_SCROLL_INSENSITIVE,
    ResultSet.CONCUR_READ_ONLY
);

TYPE_SCROLL_SENSITIVE

Cursor can move in any direction. Changes by others are visible.

Statement stmt = conn.createStatement(
    ResultSet.TYPE_SCROLL_SENSITIVE,
    ResultSet.CONCUR_READ_ONLY
);

Scrollable ResultSet Navigation

With scrollable ResultSets, you can navigate freely:

Statement stmt = conn.createStatement(
    ResultSet.TYPE_SCROLL_INSENSITIVE,
    ResultSet.CONCUR_READ_ONLY
);

ResultSet rs = stmt.executeQuery("SELECT * FROM products");

// Navigation methods
rs.first();          // Move to first row
rs.last();           // Move to last row
rs.beforeFirst();    // Move before first row
rs.afterLast();      // Move after last row

rs.absolute(5);      // Move to row 5
rs.relative(-2);     // Move 2 rows back
rs.relative(3);      // Move 3 rows forward

rs.previous();       // Move to previous row

// Position checks
boolean isFirst = rs.isFirst();
boolean isLast = rs.isLast();
boolean isBeforeFirst = rs.isBeforeFirst();
boolean isAfterLast = rs.isAfterLast();
int currentRow = rs.getRow();

Practical Example: Building Objects from ResultSet

A common pattern is mapping ResultSet rows to Java objects:

public class UserDAO {
    
    public List<User> findAllUsers() throws SQLException {
        String sql = "SELECT id, name, email, created_at, active FROM users";
        List<User> users = new ArrayList<>();
        
        try (Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            
            while (rs.next()) {
                User user = mapRowToUser(rs);
                users.add(user);
            }
        }
        
        return users;
    }
    
    private User mapRowToUser(ResultSet rs) throws SQLException {
        User user = new User();
        user.setId(rs.getLong("id"));
        user.setName(rs.getString("name"));
        user.setEmail(rs.getString("email"));
        
        Timestamp createdAt = rs.getTimestamp("created_at");
        if (createdAt != null) {
            user.setCreatedAt(createdAt.toLocalDateTime());
        }
        
        user.setActive(rs.getBoolean("active"));
        
        return user;
    }
    
    public Optional<User> findUserById(long id) throws SQLException {
        String sql = "SELECT * FROM users WHERE id = ?";
        
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setLong(1, id);
            
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    return Optional.of(mapRowToUser(rs));
                }
            }
        }
        
        return Optional.empty();
    }
}

ResultSet Metadata

You can inspect the structure of a ResultSet without knowing the query:

ResultSet rs = stmt.executeQuery("SELECT * FROM products");
ResultSetMetaData metaData = rs.getMetaData();

int columnCount = metaData.getColumnCount();

for (int i = 1; i <= columnCount; i++) {
    System.out.println("Column " + i + ":");
    System.out.println("  Name: " + metaData.getColumnName(i));
    System.out.println("  Type: " + metaData.getColumnTypeName(i));
    System.out.println("  Size: " + metaData.getColumnDisplaySize(i));
    System.out.println("  Nullable: " + metaData.isNullable(i));
}

This is particularly useful for:

• Building dynamic reports
• Creating generic data export tools
• Debugging query results

Updatable ResultSets

With CONCUR_UPDATABLE, you can modify data directly through the ResultSet:

Statement stmt = conn.createStatement(
    ResultSet.TYPE_SCROLL_INSENSITIVE,
    ResultSet.CONCUR_UPDATABLE
);

ResultSet rs = stmt.executeQuery("SELECT id, price FROM products");

while (rs.next()) {
    double currentPrice = rs.getDouble("price");
    
    // Apply 10% increase
    rs.updateDouble("price", currentPrice * 1.10);
    rs.updateRow();  // Commit the change to database
}

Inserting and Deleting Rows

// Insert a new row
rs.moveToInsertRow();
rs.updateString("name", "New Product");
rs.updateDouble("price", 29.99);
rs.insertRow();
rs.moveToCurrentRow();

// Delete current row
rs.absolute(3);
rs.deleteRow();

Resource Management and Best Practices

Always close ResultSets (preferably with try-with-resources):

// ✅ Automatic resource management
try (PreparedStatement pstmt = conn.prepareStatement(sql);
     ResultSet rs = pstmt.executeQuery()) {
    
    while (rs.next()) {
        // Process data
    }
}  // Both rs and pstmt are automatically closed

Important considerations:

• Closing a Statement automatically closes its ResultSet
• Closing a Connection closes all its Statements and ResultSets
• Don't return a ResultSet from a method (it depends on open resources)
• Convert ResultSet data to objects before closing

Common Pitfalls to Avoid

❌ Forgetting to Call next()

ResultSet rs = stmt.executeQuery("SELECT name FROM users WHERE id = 1");
String name = rs.getString("name");  // Error! Cursor is before first row

✅ Always Call next() First

ResultSet rs = stmt.executeQuery("SELECT name FROM users WHERE id = 1");
if (rs.next()) {
    String name = rs.getString("name");
}

Wrong Column Index

// Column indices are 1-based, not 0-based!
String name = rs.getString(0);  // Error! 

Use 1-Based Indices

String name = rs.getString(1);  // First column

Always call next() before accessing data. Navigate with next(), previous(), first(), last(), absolute(n). Use type-specific getters (getString(), getInt()). Reference columns by name (recommended) or index (1-based). Handle NULLs with wasNull() or getObject() with wrapper types. Close ResultSets with try-with-resources. Types: FORWARD_ONLY, SCROLL_INSENSITIVE, SCROLL_SENSITIVE. Concurrency: READ_ONLY or UPDATABLE. Convert to objects before returning from methods.