Working with ResultSet

A ResultSet represents the tabular data returned by a SQL query. It is not loaded into memory as one big collection you can index freely — it behaves like a cursor pointing to one row at a time, which you advance and read column by column.

The Cursor Model

Immediately after executeQuery() returns, the cursor is positioned before the first row. Calling next() moves it forward one row and returns true if a row exists there, or false once you have passed the last row. This is why every ResultSet loop follows the same shape: a while (rs.next()) { ... } loop that reads the current row's columns and then advances.

Reading Columns

Typed getter methods retrieve a column's value from the current row, and each getter can be called either with a 1-based column index or with the column's name (or alias) as a String.

• rs.getInt("id") or rs.getInt(1) — reads an integer column.
• rs.getString("name") — reads a text column as a Java String.
• rs.getLong("created_at_epoch") — reads a large integer / epoch-style value.
• rs.getDouble("price"), rs.getBoolean("active"), rs.getDate("order_date") — other common typed accessors.

ResultSetMetaData

Sometimes you need to inspect a query's shape without knowing its columns in advance — for example, writing a generic table-printing utility. ResultSetMetaData, obtained via rs.getMetaData(), exposes information like getColumnCount(), getColumnLabel(i) (the alias if the query used AS, otherwise the name), and getColumnTypeName(i), letting you iterate over a result set's structure generically instead of hardcoding column names.

NULL Values and wasNull()

SQL has NULL, but Java's primitive types do not. When a column is NULL, getInt() returns 0, getDouble() returns 0.0 and getBoolean() returns false, so a missing stock count looks exactly like "out of stock". Call rs.wasNull() right after the getter to tell the two apart, or read the value as an object, rs.getObject("stock", Integer.class), which returns null. Getters for object types such as getString() and getBigDecimal() return null directly.

Choosing the Right Getter for Money and Dates

Use getBigDecimal() for prices and other exact decimal values. getDouble() converts to binary floating point, so a stored 0.1 cannot be represented exactly and sums drift. For dates and times, JDBC 4.2 drivers support the java.time types directly: rs.getObject("added", LocalDate.class) for a DATE column and LocalDateTime or OffsetDateTime for timestamps. That is clearer than the old java.sql.Date and java.sql.Timestamp classes. When binding parameters, the matching call is ps.setObject(i, localDate).

Scrollable Result Sets

If you really need to move backward or jump to a row, ask for a scrollable result set when creating the statement: conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY). It adds previous(), first(), last(), absolute(n) and getRow(). Most applications never need this: the driver may have to buffer the whole result, and copying rows into a List of your own objects is usually simpler. For paging, use LIMIT/OFFSET (or FETCH FIRST) in the SQL instead.

Examples

// Table: products(id INT, name VARCHAR, price DECIMAL(8,2), stock INT, added DATE)
// Rows:  1 USB Cable 9.99 120 | 2 Mechanical Keyboard 59.99 35
//        3 Mouse Pad 12.50 NULL | 4 27-inch Monitor 189.00 8

String sql = "SELECT id, name, price, stock, added FROM products WHERE price > ? ORDER BY price";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
    ps.setBigDecimal(1, new BigDecimal("10"));
    try (ResultSet rs = ps.executeQuery()) {
        ResultSetMetaData meta = rs.getMetaData();
        System.out.print("Columns: " + meta.getColumnCount() + " ->");
        for (int i = 1; i <= meta.getColumnCount(); i++) {
            System.out.print(" " + meta.getColumnLabel(i) + ":" + meta.getColumnTypeName(i));
        }
        System.out.println();

        while (rs.next()) {
            int id = rs.getInt("id");
            String name = rs.getString("name");
            BigDecimal price = rs.getBigDecimal("price");
            int stock = rs.getInt("stock");
            boolean stockMissing = rs.wasNull();          // was that 0 really NULL?
            LocalDate added = rs.getObject("added", LocalDate.class);
            System.out.printf("#%d %-20s %7s  stock=%s  added=%s%n",
                    id, name, price, stockMissing ? "unknown" : stock, added);
        }
    }
}

Columns: 5 -> ID:INTEGER NAME:CHARACTER VARYING PRICE:DECIMAL STOCK:INTEGER ADDED:DATE
#3 Mouse Pad              12.50  stock=unknown  added=2026-03-20
#2 Mechanical Keyboard    59.99  stock=35  added=2026-03-02
#4 27-inch Monitor       189.00  stock=8  added=2026-05-11

try (Statement st = conn.createStatement();
     ResultSet rs = st.executeQuery("SELECT name FROM products")) {
    try { rs.getString("name"); }                     // 1. no next() yet
    catch (SQLException e) { System.out.println("before next(): " + e.getMessage()); }
    rs.next();
    try { rs.previous(); }                            // 2. forward-only by default
    catch (SQLException e) { System.out.println("previous() on forward-only: " + e.getMessage()); }
    try { rs.getString("nme"); }                      // 3. typo in the column name
    catch (SQLException e) { System.out.println("wrong column: " + e.getMessage()); }
}

ResultSet leaked;
try (Statement st = conn.createStatement()) {
    leaked = st.executeQuery("SELECT name FROM products");
}                                                     // statement closed here
try { leaked.next(); }                                // 4. used after close
catch (SQLException e) { System.out.println("after statement closed: " + e.getMessage()); }

try (Statement st = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
     ResultSet rs = st.executeQuery("SELECT name FROM products ORDER BY id")) {
    rs.last();      System.out.println("last row #" + rs.getRow() + ": " + rs.getString(1));
    rs.absolute(2); System.out.println("row 2: " + rs.getString(1));
    rs.previous();  System.out.println("previous: " + rs.getString(1));
}

before next(): No data is available [2000-240]
previous() on forward-only: The result set is not scrollable and can not be reset. You may need to use conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ..). [90128-240]
wrong column: Column "nme" not found [42122-240]
after statement closed: The object is already closed [90007-240]
last row #4: 27-inch Monitor
row 2: Mechanical Keyboard
previous: USB Cable

Exercise

Using the lesson's Reading rows, metadata, NULLs, BigDecimal and LocalDate (H2 2.4.240: java -cp h2.jar RsDemo.java) example, create a small implementation or design sketch that demonstrates ResultSet is a forward-moving cursor by default; next() advances it and returns false once rows are exhausted.. Add a test case or scenario that would catch Calling a getter before the first rs.next(), when the cursor is still positioned before row one — this throws a SQLException., and describe the expected result.

Quick quiz

Interview questions