Java Notes

ResultSet in JDBC

Complete guide to JDBC ResultSet — navigating results, scrollable and updatable cursors, ResultSetMetaData, and efficient data extraction patterns.

What is ResultSet?

ResultSet is a table of data representing a database result set. It maintains a cursor pointing to the current row — initially positioned before the first row. You move the cursor with navigation methods and extract data with getter methods.

Example

// Cursor positions: // [Before First] → [Row 1] → [Row 2] → ... → [Row N] → [After Last] ResultSet rs = stmt.executeQuery("SELECT * FROM employees"); // Cursor is BEFORE first row while (rs.next()) { // Move to next row (returns false after last) int id = rs.getInt(1); // Get by column index (1-based) String name = rs.getString("first_name"); // Get by column name (preferred) }

Output

[Before First] → [Row 1] → [Row 2] → ... → [Row N] → [After Last]

Data Extraction Methods

java example

public static void extractAllTypes(ResultSet rs) throws SQLException {
    while (rs.next()) {
        // Numeric types
        int id = rs.getInt("id");
        long bigId = rs.getLong("big_id");
        double salary = rs.getDouble("salary");
        float rate = rs.getFloat("rate");
        short age = rs.getShort("age");
        byte flag = rs.getByte("flag");
        BigDecimal price = rs.getBigDecimal("price");
        
        // String types
        String name = rs.getString("name");
        
        // Boolean
        boolean active = rs.getBoolean("is_active");
        
        // Date/Time types
        java.sql.Date date = rs.getDate("hire_date");
        java.sql.Time time = rs.getTime("login_time");
        java.sql.Timestamp ts = rs.getTimestamp("created_at");
        
        // Convert to Java 8 types
        LocalDate localDate = date != null ? date.toLocalDate() : null;
        LocalTime localTime = time != null ? time.toLocalTime() : null;
        LocalDateTime localDT = ts != null ? ts.toLocalDateTime() : null;
        
        // Binary data
        byte[] bytes = rs.getBytes("photo");
        InputStream stream = rs.getBinaryStream("document");
        
        // Large objects
        Blob blob = rs.getBlob("file_data");
        Clob clob = rs.getClob("long_text");
        
        // Generic (auto-detect type)
        Object obj = rs.getObject("any_column");
        
        // Check for NULL
        double val = rs.getDouble("nullable_col");
        if (rs.wasNull()) {
            System.out.println("Value was NULL!");
        }
    }
}

Output

Value was NULL!

Scrollable ResultSet

java example

public static void scrollableDemo() throws SQLException {
    Connection conn = DriverManager.getConnection(url, user, pass);
    Statement stmt = conn.createStatement(
        ResultSet.TYPE_SCROLL_INSENSITIVE,
        ResultSet.CONCUR_READ_ONLY
    );
    
    ResultSet rs = stmt.executeQuery("SELECT * FROM employees ORDER BY id");
    
    // Move to last row
    rs.last();
    System.out.println("Total rows: " + rs.getRow());
    System.out.println("Last: " + rs.getString("first_name"));
    
    // Move to first row
    rs.first();
    System.out.println("First: " + rs.getString("first_name"));
    
    // Move to specific row
    rs.absolute(3); // 3rd row
    System.out.println("3rd: " + rs.getString("first_name"));
    
    // Move relative to current position
    rs.relative(2); // Move 2 rows forward (now at 5th)
    System.out.println("5th: " + rs.getString("first_name"));
    
    rs.relative(-1); // Move 1 row backward (now at 4th)
    System.out.println("4th: " + rs.getString("first_name"));
    
    // Move before first / after last
    rs.beforeFirst();
    rs.afterLast();
    
    // Iterate backward
    rs.afterLast();
    while (rs.previous()) {
        System.out.println("Reverse: " + rs.getString("first_name"));
    }
    
    // Check position
    boolean isFirst = rs.isFirst();
    boolean isLast = rs.isLast();
    boolean isBeforeFirst = rs.isBeforeFirst();
    boolean isAfterLast = rs.isAfterLast();
}

Updatable ResultSet

java example

public static void updatableDemo() throws SQLException {
    Connection conn = DriverManager.getConnection(url, user, pass);
    Statement stmt = conn.createStatement(
        ResultSet.TYPE_SCROLL_INSENSITIVE,
        ResultSet.CONCUR_UPDATABLE
    );
    
    ResultSet rs = stmt.executeQuery("SELECT * FROM employees WHERE department = 'Engineering'");
    
    // UPDATE a row
    while (rs.next()) {
        double currentSalary = rs.getDouble("salary");
        rs.updateDouble("salary", currentSalary * 1.10); // 10% raise
        rs.updateRow(); // Commit changes to database
        System.out.println("Updated: " + rs.getString("first_name"));
    }
    
    // INSERT a new row
    rs.moveToInsertRow(); // Move to special "insert" row
    rs.updateString("first_name", "New");
    rs.updateString("last_name", "Employee");
    rs.updateString("email", "new@company.com");
    rs.updateString("department", "Engineering");
    rs.updateDouble("salary", 70000.00);
    rs.updateDate("hire_date", java.sql.Date.valueOf(LocalDate.now()));
    rs.insertRow(); // Insert into database
    rs.moveToCurrentRow(); // Move back to where we were
    
    // DELETE a row
    rs.absolute(1);
    rs.deleteRow(); // Delete current row from database
}

ResultSetMetaData

Example

public static void metadataDemo() throws SQLException { Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery("SELECT * FROM employees LIMIT 1"); ResultSetMetaData meta = rs.getMetaData(); int columnCount = meta.getColumnCount(); System.out.println("=== Table Structure ==="); System.out.printf("%-5s %-20s %-15s %-10s %-8s%n", "#", "Name", "Type", "Size", "Nullable"); System.out.println("-".repeat(60)); for (int i = 1; i <= columnCount; i++) { System.out.printf("%-5d %-20s %-15s %-10d %-8s%n", i, meta.getColumnName(i), meta.getColumnTypeName(i), meta.getColumnDisplaySize(i), meta.isNullable(i) == ResultSetMetaData.columnNullable ? "YES" : "NO" ); } // Other useful metadata methods for (int i = 1; i <= columnCount; i++) { String colName = meta.getColumnName(i); String colLabel = meta.getColumnLabel(i); // Alias if used String tableName = meta.getTableName(i); String className = meta.getColumnClassName(i); // Java class name int sqlType = meta.getColumnType(i); // java.sql.Types constant boolean autoInc = meta.isAutoIncrement(i); boolean readOnly = meta.isReadOnly(i); } }

Output

=== Table Structure ===

Generic ResultSet to List Converter

Example

public static List<Map<String, Object>> resultSetToList(ResultSet rs) throws SQLException { List<Map<String, Object>> results = new ArrayList<>(); ResultSetMetaData meta = rs.getMetaData(); int columnCount = meta.getColumnCount(); while (rs.next()) { Map<String, Object> row = new LinkedHashMap<>(); for (int i = 1; i <= columnCount; i++) { String columnName = meta.getColumnName(i); Object value = rs.getObject(i); row.put(columnName, value); } results.add(row); } return results; } // Usage ResultSet rs = stmt.executeQuery("SELECT * FROM employees"); List<Map<String, Object>> data = resultSetToList(rs); data.forEach(row -> System.out.println(row));

Performance Tips

java example

// 1. Use column index instead of name (slightly faster)
int id = rs.getInt(1);  // Faster
int id = rs.getInt("id"); // Clearer (preferred for readability)

// 2. Set fetch size for large result sets
stmt.setFetchSize(500); // Fetch 500 rows at a time

// 3. Use streaming for very large results (MySQL)
stmt.setFetchSize(Integer.MIN_VALUE); // Stream one row at a time

// 4. Close ResultSets promptly
try (ResultSet rs = stmt.executeQuery(sql)) {
    // Process immediately, don't hold open
}

// 5. Don't fetch columns you don't need
// ❌ SELECT * FROM employees (fetches all columns)
// ✅ SELECT id, first_name, salary FROM employees (only what you need)

Interview Questions

Q1: What is the default ResultSet type and concurrency?

Answer: TYPE_FORWARD_ONLY and CONCUR_READ_ONLY. This is the fastest option as the driver doesn't need to cache rows for scrolling or track changes for updating.

Q2: What is the difference between getInt(1) and getInt("id")?

Answer: getInt(1) uses column index (1-based), slightly faster. getInt("id") uses column name, more readable and resilient to column order changes. Column name is preferred for maintainability.

Q3: How do you check if a value is NULL in ResultSet?

Answer: Call the getter method, then immediately call rs.wasNull(). Primitive getters (getInt, getDouble) return 0 for NULL — wasNull() distinguishes between actual 0 and NULL. Alternatively, use getObject() which returns Java null.

Q4: What is the difference between TYPE_SCROLL_INSENSITIVE and TYPE_SCROLL_SENSITIVE?

Answer: Both allow scrolling (absolute, relative, first, last). INSENSITIVE takes a snapshot — doesn't reflect changes made by other transactions. SENSITIVE reflects external changes in real-time but is slower and not supported by all drivers.

Q5: Can every query produce an updatable ResultSet?

Answer: No. The query must select from a single table, include the primary key, not use joins/aggregates/GROUP BY/DISTINCT, and the driver must support it. If not updatable, the driver silently downgrades to READ_ONLY.

Q6: What does setFetchSize() do?

Answer: It hints to the driver how many rows to fetch per network round trip. For large results, higher fetch size means fewer network calls but more memory. For MySQL specifically, Integer.MIN_VALUE enables row-by-row streaming for very large result sets.

Summary

In this chapter, we learned about ResultSet in JDBC in detail. Here are the key points:

Basic introduction to the concept and why it is needed
Syntax and structure with complete examples
Internal working and best practices
Real-world applications and use cases
Common mistakes and how to avoid them
How this topic is asked in interviews

To practice these concepts, write and run the code in your IDE and verify the output. Modify each example and experiment so that you deeply understand the concept.

Exam Focus

Revise definitions, diagrams, examples, and short-answer points for ResultSet in JDBC.

Interview Use

Prepare one clear explanation, one practical example, and one common mistake for this Java Master Course topic.

Search Terms

java-master-course, java master course, java, master, course, jdbc, resultset, resultset in jdbc

Continue learning this concept

JDBCBatch Processing in JDBCMaster JDBC batch processing — Statement and PreparedStatement batching, performance optimization, error handling, and bulk data import patterns.JDBCCallableStatement in JDBCComplete guide to CallableStatement — calling stored procedures and functions, IN/OUT/INOUT parameters, and handling multiple result sets from MySQL procedures.JDBCJDBC ArchitectureDeep dive into JDBC architecture — two-tier and three-tier models, driver manager, connection pooling, DataSource, and internal execution flow.JDBCJDBC IntroductionComplete introduction to JDBC — Java Database Connectivity concepts, API overview, driver types, and how Java applications communicate with databases.JDBCJDBC ProjectsHands-on JDBC projects — Student Management System, Employee Payroll, Library Management with full working code including DAO pattern, connection pooling, and menu-driven interface.JDBCMySQL Connection with JDBCStep-by-step guide to connecting Java with MySQL — driver setup, connection URLs, properties, SSL configuration, and troubleshooting common errors.

PreviousPreparedStatement in JDBC NextStatement Interface in JDBC

Java Notes

ResultSet in JDBC

Complete guide to JDBC ResultSet — navigating results, scrollable and updatable cursors, ResultSetMetaData, and efficient data extraction patterns.

What is ResultSet?

Example

Output

[Before First] → [Row 1] → [Row 2] → ... → [Row N] → [After Last]

Data Extraction Methods

java example

public static void extractAllTypes(ResultSet rs) throws SQLException {
    while (rs.next()) {
        // Numeric types
        int id = rs.getInt("id");
        long bigId = rs.getLong("big_id");
        double salary = rs.getDouble("salary");
        float rate = rs.getFloat("rate");
        short age = rs.getShort("age");
        byte flag = rs.getByte("flag");
        BigDecimal price = rs.getBigDecimal("price");
        
        // String types
        String name = rs.getString("name");
        
        // Boolean
        boolean active = rs.getBoolean("is_active");
        
        // Date/Time types
        java.sql.Date date = rs.getDate("hire_date");
        java.sql.Time time = rs.getTime("login_time");
        java.sql.Timestamp ts = rs.getTimestamp("created_at");
        
        // Convert to Java 8 types
        LocalDate localDate = date != null ? date.toLocalDate() : null;
        LocalTime localTime = time != null ? time.toLocalTime() : null;
        LocalDateTime localDT = ts != null ? ts.toLocalDateTime() : null;
        
        // Binary data
        byte[] bytes = rs.getBytes("photo");
        InputStream stream = rs.getBinaryStream("document");
        
        // Large objects
        Blob blob = rs.getBlob("file_data");
        Clob clob = rs.getClob("long_text");
        
        // Generic (auto-detect type)
        Object obj = rs.getObject("any_column");
        
        // Check for NULL
        double val = rs.getDouble("nullable_col");
        if (rs.wasNull()) {
            System.out.println("Value was NULL!");
        }
    }
}

Output

Value was NULL!

Scrollable ResultSet

java example

public static void scrollableDemo() throws SQLException {
    Connection conn = DriverManager.getConnection(url, user, pass);
    Statement stmt = conn.createStatement(
        ResultSet.TYPE_SCROLL_INSENSITIVE,
        ResultSet.CONCUR_READ_ONLY
    );
    
    ResultSet rs = stmt.executeQuery("SELECT * FROM employees ORDER BY id");
    
    // Move to last row
    rs.last();
    System.out.println("Total rows: " + rs.getRow());
    System.out.println("Last: " + rs.getString("first_name"));
    
    // Move to first row
    rs.first();
    System.out.println("First: " + rs.getString("first_name"));
    
    // Move to specific row
    rs.absolute(3); // 3rd row
    System.out.println("3rd: " + rs.getString("first_name"));
    
    // Move relative to current position
    rs.relative(2); // Move 2 rows forward (now at 5th)
    System.out.println("5th: " + rs.getString("first_name"));
    
    rs.relative(-1); // Move 1 row backward (now at 4th)
    System.out.println("4th: " + rs.getString("first_name"));
    
    // Move before first / after last
    rs.beforeFirst();
    rs.afterLast();
    
    // Iterate backward
    rs.afterLast();
    while (rs.previous()) {
        System.out.println("Reverse: " + rs.getString("first_name"));
    }
    
    // Check position
    boolean isFirst = rs.isFirst();
    boolean isLast = rs.isLast();
    boolean isBeforeFirst = rs.isBeforeFirst();
    boolean isAfterLast = rs.isAfterLast();
}

Updatable ResultSet

java example

public static void updatableDemo() throws SQLException {
    Connection conn = DriverManager.getConnection(url, user, pass);
    Statement stmt = conn.createStatement(
        ResultSet.TYPE_SCROLL_INSENSITIVE,
        ResultSet.CONCUR_UPDATABLE
    );
    
    ResultSet rs = stmt.executeQuery("SELECT * FROM employees WHERE department = 'Engineering'");
    
    // UPDATE a row
    while (rs.next()) {
        double currentSalary = rs.getDouble("salary");
        rs.updateDouble("salary", currentSalary * 1.10); // 10% raise
        rs.updateRow(); // Commit changes to database
        System.out.println("Updated: " + rs.getString("first_name"));
    }
    
    // INSERT a new row
    rs.moveToInsertRow(); // Move to special "insert" row
    rs.updateString("first_name", "New");
    rs.updateString("last_name", "Employee");
    rs.updateString("email", "new@company.com");
    rs.updateString("department", "Engineering");
    rs.updateDouble("salary", 70000.00);
    rs.updateDate("hire_date", java.sql.Date.valueOf(LocalDate.now()));
    rs.insertRow(); // Insert into database
    rs.moveToCurrentRow(); // Move back to where we were
    
    // DELETE a row
    rs.absolute(1);
    rs.deleteRow(); // Delete current row from database
}

ResultSetMetaData

Example

Output

=== Table Structure ===

Generic ResultSet to List Converter

Example

Performance Tips

java example

// 1. Use column index instead of name (slightly faster)
int id = rs.getInt(1);  // Faster
int id = rs.getInt("id"); // Clearer (preferred for readability)

// 2. Set fetch size for large result sets
stmt.setFetchSize(500); // Fetch 500 rows at a time

// 3. Use streaming for very large results (MySQL)
stmt.setFetchSize(Integer.MIN_VALUE); // Stream one row at a time

// 4. Close ResultSets promptly
try (ResultSet rs = stmt.executeQuery(sql)) {
    // Process immediately, don't hold open
}

// 5. Don't fetch columns you don't need
// ❌ SELECT * FROM employees (fetches all columns)
// ✅ SELECT id, first_name, salary FROM employees (only what you need)

Interview Questions

Q1: What is the default ResultSet type and concurrency?

Answer: TYPE_FORWARD_ONLY and CONCUR_READ_ONLY. This is the fastest option as the driver doesn't need to cache rows for scrolling or track changes for updating.

Q2: What is the difference between getInt(1) and getInt("id")?

Q3: How do you check if a value is NULL in ResultSet?

Q4: What is the difference between TYPE_SCROLL_INSENSITIVE and TYPE_SCROLL_SENSITIVE?

Q5: Can every query produce an updatable ResultSet?

Q6: What does setFetchSize() do?

Summary

In this chapter, we learned about ResultSet in JDBC in detail. Here are the key points:

Basic introduction to the concept and why it is needed
Syntax and structure with complete examples
Internal working and best practices
Real-world applications and use cases
Common mistakes and how to avoid them
How this topic is asked in interviews

To practice these concepts, write and run the code in your IDE and verify the output. Modify each example and experiment so that you deeply understand the concept.

Exam Focus

Revise definitions, diagrams, examples, and short-answer points for ResultSet in JDBC.

Interview Use

Prepare one clear explanation, one practical example, and one common mistake for this Java Master Course topic.

Search Terms

java-master-course, java master course, java, master, course, jdbc, resultset, resultset in jdbc

Continue learning this concept

PreviousPreparedStatement in JDBC NextStatement Interface in JDBC