ResultSet & Metadata in JDBC

When you execute a query in JDBC, the result is returned in the form of a ResultSet. JDBC also provides metadata APIs to get information about the database and query results.

What is ResultSet in JDBC?

ResultSet is an interface used to store and process data returned from a SQL query.

In simple words: It is like a table of data returned by the database.

Example: Basic ResultSet Usage

import java.sql.*;

public class Test {
    public static void main(String[] args) throws Exception {

        Connection con = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/testdb", "root", "password");

        Statement stmt = con.createStatement();

        ResultSet rs = stmt.executeQuery("SELECT id, name FROM users");

        while (rs.next()) {
            System.out.println(rs.getInt("id") + " " + rs.getString("name"));
        }

        con.close();
    }
}

Output Example

1 Amit
2 Rahul

How ResultSet Works

• Data is fetched from the database using SQL query
• Stored inside the ResultSet object
• A cursor (pointer) is used to navigate through rows
• Initially, the cursor is placed before the first row
• rs.next() moves the cursor one row forward each time

ResultSet Interface

The ResultSet interface provides methods to:

• Navigate through rows
• Retrieve column values
• Process query results

👉 It acts as a container that holds data returned from the database and allows you to access it row by row.

Example

Statement stmt = con.createStatement();

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

while (rs.next()) {
    System.out.println(rs.getString("name"));
}

Explanation

• createStatement() → Creates a Statement object
• executeQuery() → Executes SELECT query and returns ResultSet
• rs.next() → Moves cursor to the next row
• getString("name") → Retrieves value from the name column

👉 The loop continues until all rows are processed.

Cursor Navigation

ResultSet uses a cursor (pointer) to navigate through rows in the data.

👉 The cursor controls which row you are currently accessing in the ResultSet.

Default Behavior

• Cursor starts before the first row.
• You must call next() to move to the first row.
• Each call to next() moves the cursor forward by one row.

Common Cursor Methods

• next() → Move to next row.
• previous() → Move to previous row.
• first() → Move to first row.
• last() → Move to last row.
• absolute(int row) → Move to a specific row number.

Example

rs.next();     // moves to first row
rs.next();     // moves to second row
rs.previous(); // moves back to first row

Important Note

Methods like previous(), first(), and last() work only with scrollable ResultSet (not default).

Example to create scrollable ResultSet:

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

👉 Cursor navigation allows you to move forward and backward in data, making data processing more flexible in JDBC.

Reading Data from ResultSet

You can retrieve data from a ResultSet using column name or column index.

👉 It is recommended to use column names because they are more readable and less error-prone.

Example

while (rs.next()) {
    int id = rs.getInt("id");
    String name = rs.getString("name");

    System.out.println(id + " " + name);
}

Explanation

• rs.next() → Moves cursor to the next row
• getInt("id") → Retrieves integer value from id column
• getString("name") → Retrieves string value from name column

👉 You can also use index:

int id = rs.getInt(1);
String name = rs.getString(2);

Common Getter Methods

• getInt() → For integer values
• getString() → For text values
• getDouble() → For decimal values
• getDate() → For date values

👉 Always match the getter method with the column data type, otherwise it may cause errors or incorrect data retrieval.

Scrollable ResultSet (Forward / Backward)

By default, ResultSet is forward-only, which means you can only move in one direction using next().

👉 To move backward or jump between rows, we use a scrollable ResultSet.

Creating Scrollable ResultSet

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

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

Explanation

• TYPE_SCROLL_INSENSITIVE → Allows moving forward and backward (data does not change during processing)
• CONCUR_READ_ONLY → ResultSet is read-only (cannot update data)

Features

• Move forward and backward using next() and previous()
• Jump to any row using absolute()
• Directly access first and last rows using first() and last()

Example Usage

rs.last();
System.out.println("Last Row: " + rs.getString("name"));

rs.first();
System.out.println("First Row: " + rs.getString("name"));

👉 Scrollable ResultSet gives you more control over data navigation, especially useful in complex data processing scenarios.

Updatable ResultSet

JDBC allows updating database records directly using a ResultSet.

👉 This means you can modify data without writing separate SQL UPDATE queries.

Creating Updatable ResultSet

Statement stmt = con.createStatement(
    ResultSet.TYPE_SCROLL_SENSITIVE,
    ResultSet.CONCUR_UPDATABLE
);

Explanation

• TYPE_SCROLL_SENSITIVE → Reflects changes made to the database while navigating
• CONCUR_UPDATABLE → Allows updating data through the ResultSet

Example

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

rs.next();

rs.updateString("name", "Rahul Updated");
rs.updateRow();

How it Works

• rs.next() → Moves to the row you want to update
• updateString() → Updates the column value in memory
• updateRow() → Applies the changes to the database

DatabaseMetaData

DatabaseMetaData provides information about the database itself.

👉 It helps you understand database details like version, driver, and supported features programmatically.

What it can provide

• Database name and version
• JDBC driver name and version
• Supported features (transactions, SQL grammar, etc.)
• Information about tables, schemas, and catalogs

Example

DatabaseMetaData dbmd = con.getMetaData();

System.out.println(dbmd.getDatabaseProductName());
System.out.println(dbmd.getDatabaseProductVersion());
System.out.println(dbmd.getDriverName());

Explanation

• getMetaData() → Returns DatabaseMetaData object
• getDatabaseProductName() → Returns database name (e.g., MySQL)
• getDatabaseProductVersion() → Returns version of database
• getDriverName() → Returns JDBC driver details

ResultSetMetaData

ResultSetMetaData provides information about the columns of the query result.

👉 It helps you understand the structure of the data returned by a query.

What it can provide

• Number of columns
• Column names
• Column data types

Example

ResultSetMetaData rsmd = rs.getMetaData();

int count = rsmd.getColumnCount();

for (int i = 1; i <= count; i++) {
    System.out.println(rsmd.getColumnName(i));
}

Explanation

• getMetaData() → Returns ResultSetMetaData object
• getColumnCount() → Returns total number of columns
• getColumnName(i) → Returns name of each column

👉 You can also get column types:

System.out.println(rsmd.getColumnTypeName(i));

Use Cases

ResultSet and metadata APIs are widely used in real-world applications where dynamic data handling is required.

• Dynamic table generation (UI grids that display database data).
• Reporting systems (generating reports based on query results).
• Data export tools (CSV, Excel, PDF generation).
• ORM frameworks like Hibernate (mapping database data to objects).
• Database analysis and admin tools.

Common Mistakes to Avoid

• Not calling next() before reading data (cursor must move to first row).
• Using wrong data type methods (e.g., getInt() for a String column).
• Not closing ResultSet (can cause memory/resource leaks).
• Assuming scrollable behavior by default (default is forward-only).
• Ignoring null values (may cause unexpected results or errors).

Conclusion

ResultSet and Metadata APIs are essential for handling and understanding database data in JDBC.

• ResultSet helps in reading and processing query results.
• Cursor navigation allows controlled movement across rows.
• Metadata APIs provide structural and database-related information.
• Advanced features (scrollable, updatable ResultSet) enable flexible data handling.