ResultSet Handling

import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * ResultSet represents the result of a database query.
 * PostgreSQL driver provides full JDBC ResultSet implementation.
 */
public interface ResultSet extends AutoCloseable {
    /**
     * Moves cursor to next row.
     *
     * @return true if new row is valid, false if no more rows
     * @throws SQLException if navigation fails
     */
    boolean next() throws SQLException;

    /**
     * Closes the ResultSet and releases resources.
     *
     * @throws SQLException if close fails
     */
    void close() throws SQLException;

    /**
     * Reports whether last column read was SQL NULL.
     *
     * @return true if last column was NULL
     * @throws SQLException if check fails
     */
    boolean wasNull() throws SQLException;

    // Scrollable ResultSet methods (when supported)

    /**
     * Moves cursor to previous row.
     * Requires TYPE_SCROLL_INSENSITIVE or TYPE_SCROLL_SENSITIVE.
     *
     * @return true if cursor is on valid row
     * @throws SQLException if navigation fails
     */
    boolean previous() throws SQLException;

    /**
     * Moves cursor before first row.
     *
     * @throws SQLException if navigation fails
     */
    void beforeFirst() throws SQLException;

    /**
     * Moves cursor after last row.
     *
     * @throws SQLException if navigation fails
     */
    void afterLast() throws SQLException;

    /**
     * Moves cursor to first row.
     *
     * @return true if cursor is on valid row
     * @throws SQLException if navigation fails
     */
    boolean first() throws SQLException;

    /**
     * Moves cursor to last row.
     *
     * @return true if cursor is on valid row
     * @throws SQLException if navigation fails
     */
    boolean last() throws SQLException;

    /**
     * Moves cursor to specified row number (1-based).
     *
     * @param row Row number
     * @return true if cursor is on valid row
     * @throws SQLException if navigation fails
     */
    boolean absolute(int row) throws SQLException;

    /**
     * Moves cursor relative to current position.
     *
     * @param rows Number of rows to move (negative for backwards)
     * @return true if cursor is on valid row
     * @throws SQLException if navigation fails
     */
    boolean relative(int rows) throws SQLException;

    /**
     * Returns current row number (1-based).
     *
     * @return Row number, or 0 if before first or after last
     * @throws SQLException if retrieval fails
     */
    int getRow() throws SQLException;

    /**
     * Checks if cursor is before first row.
     *
     * @return true if before first
     * @throws SQLException if check fails
     */
    boolean isBeforeFirst() throws SQLException;

    /**
     * Checks if cursor is after last row.
     *
     * @return true if after last
     * @throws SQLException if check fails
     */
    boolean isAfterLast() throws SQLException;

    /**
     * Checks if cursor is on first row.
     *
     * @return true if on first row
     * @throws SQLException if check fails
     */
    boolean isFirst() throws SQLException;

    /**
     * Checks if cursor is on last row.
     *
     * @return true if on last row
     * @throws SQLException if check fails
     */
    boolean isLast() throws SQLException;
}

/**
 * ResultSet data retrieval methods.
 * Columns can be accessed by index (1-based) or name.
 */
public interface ResultSet {
    // Retrieve by column index (1-based)

    String getString(int columnIndex) throws SQLException;
    boolean getBoolean(int columnIndex) throws SQLException;
    byte getByte(int columnIndex) throws SQLException;
    short getShort(int columnIndex) throws SQLException;
    int getInt(int columnIndex) throws SQLException;
    long getLong(int columnIndex) throws SQLException;
    float getFloat(int columnIndex) throws SQLException;
    double getDouble(int columnIndex) throws SQLException;
    java.math.BigDecimal getBigDecimal(int columnIndex) throws SQLException;
    byte[] getBytes(int columnIndex) throws SQLException;
    java.sql.Date getDate(int columnIndex) throws SQLException;
    java.sql.Time getTime(int columnIndex) throws SQLException;
    java.sql.Timestamp getTimestamp(int columnIndex) throws SQLException;
    Object getObject(int columnIndex) throws SQLException;
    java.sql.Array getArray(int columnIndex) throws SQLException;
    java.sql.Blob getBlob(int columnIndex) throws SQLException;
    java.sql.Clob getClob(int columnIndex) throws SQLException;
    java.io.InputStream getBinaryStream(int columnIndex) throws SQLException;
    java.io.Reader getCharacterStream(int columnIndex) throws SQLException;

    // Retrieve by column name

    String getString(String columnLabel) throws SQLException;
    boolean getBoolean(String columnLabel) throws SQLException;
    byte getByte(String columnLabel) throws SQLException;
    short getShort(String columnLabel) throws SQLException;
    int getInt(String columnLabel) throws SQLException;
    long getLong(String columnLabel) throws SQLException;
    float getFloat(String columnLabel) throws SQLException;
    double getDouble(String columnLabel) throws SQLException;
    java.math.BigDecimal getBigDecimal(String columnLabel) throws SQLException;
    byte[] getBytes(String columnLabel) throws SQLException;
    java.sql.Date getDate(String columnLabel) throws SQLException;
    java.sql.Time getTime(String columnLabel) throws SQLException;
    java.sql.Timestamp getTimestamp(String columnLabel) throws SQLException;
    Object getObject(String columnLabel) throws SQLException;
    java.sql.Array getArray(String columnLabel) throws SQLException;

    // Type-specific methods

    /**
     * Gets a column value as specified Java type.
     *
     * @param columnIndex Column index
     * @param type Target Java class
     * @return Value converted to target type
     * @throws SQLException if conversion fails
     */
    <T> T getObject(int columnIndex, Class<T> type) throws SQLException;

    /**
     * Gets column value with custom type map.
     *
     * @param columnIndex Column index
     * @param map Type mappings
     * @return Mapped object
     * @throws SQLException if retrieval fails
     */
    Object getObject(int columnIndex, java.util.Map<String,Class<?>> map)
            throws SQLException;
}

import java.sql.*;

// Example 1: Basic ResultSet processing
public class BasicResultSetExample {
    public static void processResults(Connection conn) throws SQLException {
        String sql = "SELECT id, name, email, created_at FROM users";

        try (Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String email = rs.getString("email");
                Timestamp createdAt = rs.getTimestamp("created_at");

                System.out.printf("%d: %s <%s> - %s%n",
                    id, name, email, createdAt);
            }
        }
    }
}

// Example 2: Handle NULL values
public class NullHandlingExample {
    public static void handleNulls(ResultSet rs) throws SQLException {
        String name = rs.getString("name");
        if (rs.wasNull()) {
            name = "Unknown";
        }

        Integer age = rs.getInt("age");
        if (rs.wasNull()) {
            age = null; // Or handle appropriately
        }
    }
}

// Example 3: Scrollable ResultSet
public class ScrollableResultSetExample {
    public static void scrollResults(Connection conn) throws SQLException {
        String sql = "SELECT * FROM products";

        // Create scrollable ResultSet
        try (Statement stmt = conn.createStatement(
                 ResultSet.TYPE_SCROLL_INSENSITIVE,
                 ResultSet.CONCUR_READ_ONLY);
             ResultSet rs = stmt.executeQuery(sql)) {

            // Jump to last row
            if (rs.last()) {
                System.out.println("Last row: " + rs.getInt("id"));
            }

            // Go back to first
            if (rs.first()) {
                System.out.println("First row: " + rs.getInt("id"));
            }

            // Move to specific row
            if (rs.absolute(5)) {
                System.out.println("Row 5: " + rs.getInt("id"));
            }

            // Iterate backwards
            while (rs.previous()) {
                System.out.println("Row: " + rs.getInt("id"));
            }
        }
    }
}

// Example 4: Binary data retrieval
public class BinaryDataExample {
    public static byte[] getImageData(Connection conn, int imageId)
            throws SQLException {
        String sql = "SELECT data FROM images WHERE id = ?";

        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, imageId);

            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    return rs.getBytes("data");
                }
            }
        }
        return null;
    }
}

package org.postgresql;

import java.sql.ResultSetMetaData;
import java.sql.SQLException;

/**
 * PostgreSQL extensions to ResultSetMetaData.
 * Provides additional metadata about result columns.
 */
public interface PGResultSetMetaData extends ResultSetMetaData {
    /**
     * Returns the underlying column name before any aliasing.
     *
     * @param column Column number (1-based)
     * @return Base column name
     * @throws SQLException if column is invalid
     */
    String getBaseColumnName(int column) throws SQLException;

    /**
     * Returns the underlying table name before any aliasing.
     *
     * @param column Column number (1-based)
     * @return Base table name
     * @throws SQLException if column is invalid
     */
    String getBaseTableName(int column) throws SQLException;

    /**
     * Returns the underlying schema name.
     *
     * @param column Column number (1-based)
     * @return Base schema name
     * @throws SQLException if column is invalid
     */
    String getBaseSchemaName(int column) throws SQLException;

    /**
     * Returns the format of the column data.
     *
     * @param column Column number (1-based)
     * @return 0 for text format, 1 for binary format
     * @throws SQLException if column is invalid
     */
    int getFormat(int column) throws SQLException;
}

import java.sql.ResultSetMetaData;
import java.sql.SQLException;

/**
 * Standard ResultSetMetaData interface.
 * Provides information about ResultSet columns.
 */
public interface ResultSetMetaData {
    /**
     * Returns the number of columns in the ResultSet.
     */
    int getColumnCount() throws SQLException;

    /**
     * Returns the column name or alias.
     */
    String getColumnName(int column) throws SQLException;

    /**
     * Returns the column label (alias if specified).
     */
    String getColumnLabel(int column) throws SQLException;

    /**
     * Returns the SQL type code for the column.
     */
    int getColumnType(int column) throws SQLException;

    /**
     * Returns the database-specific type name.
     */
    String getColumnTypeName(int column) throws SQLException;

    /**
     * Returns the table name for the column.
     */
    String getTableName(int column) throws SQLException;

    /**
     * Returns the schema name for the column.
     */
    String getSchemaName(int column) throws SQLException;

    /**
     * Returns the catalog name for the column.
     */
    String getCatalogName(int column) throws SQLException;

    /**
     * Returns the column display size.
     */
    int getColumnDisplaySize(int column) throws SQLException;

    /**
     * Returns the precision for numeric columns.
     */
    int getPrecision(int column) throws SQLException;

    /**
     * Returns the scale for numeric columns.
     */
    int getScale(int column) throws SQLException;

    /**
     * Indicates whether column is automatically numbered.
     */
    boolean isAutoIncrement(int column) throws SQLException;

    /**
     * Indicates whether column is case sensitive.
     */
    boolean isCaseSensitive(int column) throws SQLException;

    /**
     * Indicates whether column can be used in WHERE clause.
     */
    boolean isSearchable(int column) throws SQLException;

    /**
     * Indicates whether column is a currency value.
     */
    boolean isCurrency(int column) throws SQLException;

    /**
     * Indicates nullability of column values.
     * Returns columnNoNulls, columnNullable, or columnNullableUnknown.
     */
    int isNullable(int column) throws SQLException;

    /**
     * Indicates whether column is signed numeric.
     */
    boolean isSigned(int column) throws SQLException;

    /**
     * Indicates whether column is read-only.
     */
    boolean isReadOnly(int column) throws SQLException;

    /**
     * Indicates whether column is writable.
     */
    boolean isWritable(int column) throws SQLException;

    /**
     * Indicates whether write on column will definitely succeed.
     */
    boolean isDefinitelyWritable(int column) throws SQLException;

    /**
     * Returns the fully-qualified Java class name for column values.
     */
    String getColumnClassName(int column) throws SQLException;
}

import org.postgresql.PGResultSetMetaData;
import java.sql.*;

// Example 1: Examine ResultSet metadata
public class MetadataExample {
    public static void printMetadata(ResultSet rs) throws SQLException {
        ResultSetMetaData meta = rs.getMetaData();
        int columnCount = meta.getColumnCount();

        System.out.println("Column count: " + columnCount);

        for (int i = 1; i <= columnCount; i++) {
            String name = meta.getColumnName(i);
            String label = meta.getColumnLabel(i);
            String type = meta.getColumnTypeName(i);
            int sqlType = meta.getColumnType(i);
            boolean nullable = (meta.isNullable(i) == ResultSetMetaData.columnNullable);

            System.out.printf("Column %d: %s (%s) [%s] - nullable: %b%n",
                i, label, name, type, nullable);
        }
    }
}

// Example 2: PostgreSQL-specific metadata
public class PGMetadataExample {
    public static void printPGMetadata(ResultSet rs) throws SQLException {
        PGResultSetMetaData pgMeta = rs.getMetaData().unwrap(PGResultSetMetaData.class);
        int columnCount = pgMeta.getColumnCount();

        for (int i = 1; i <= columnCount; i++) {
            String baseColumn = pgMeta.getBaseColumnName(i);
            String baseTable = pgMeta.getBaseTableName(i);
            String baseSchema = pgMeta.getBaseSchemaName(i);
            int format = pgMeta.getFormat(i);

            System.out.printf("Column %d: %s.%s.%s (format: %s)%n",
                i, baseSchema, baseTable, baseColumn,
                format == 0 ? "text" : "binary");
        }
    }
}

// Example 3: Dynamic column processing
public class DynamicProcessingExample {
    public static void processDynamically(ResultSet rs) throws SQLException {
        ResultSetMetaData meta = rs.getMetaData();
        int columnCount = meta.getColumnCount();

        while (rs.next()) {
            for (int i = 1; i <= columnCount; i++) {
                String columnName = meta.getColumnLabel(i);
                Object value = rs.getObject(i);

                if (rs.wasNull()) {
                    System.out.println(columnName + ": NULL");
                } else {
                    System.out.println(columnName + ": " + value);
                }
            }
            System.out.println("---");
        }
    }
}

// Example: Type conversions
public class TypeConversionExample {
    public static void convertTypes(ResultSet rs) throws SQLException {
        // Numeric types
        int intVal = rs.getInt("int_col");
        long longVal = rs.getLong("bigint_col");
        BigDecimal decVal = rs.getBigDecimal("numeric_col");

        // Date/time types
        Date date = rs.getDate("date_col");
        Timestamp timestamp = rs.getTimestamp("timestamp_col");

        // Binary data
        byte[] bytes = rs.getBytes("bytea_col");

        // UUID (PostgreSQL 9.4+)
        UUID uuid = rs.getObject("uuid_col", UUID.class);

        // Array
        Array array = rs.getArray("array_col");
        String[] strArray = (String[]) array.getArray();

        // JSON (as String)
        String json = rs.getString("json_col");

        // PostgreSQL types
        PGobject pgObj = (PGobject) rs.getObject("custom_col");
        String typeName = pgObj.getType();
        String value = pgObj.getValue();
    }
}

/**
 * Updateable ResultSet methods.
 * Available when ResultSet is created with CONCUR_UPDATABLE.
 */
public interface ResultSet {
    /**
     * Updates a column value in current row.
     */
    void updateString(int columnIndex, String x) throws SQLException;
    void updateInt(int columnIndex, int x) throws SQLException;
    void updateLong(int columnIndex, long x) throws SQLException;
    void updateDouble(int columnIndex, double x) throws SQLException;
    void updateTimestamp(int columnIndex, Timestamp x) throws SQLException;
    void updateNull(int columnIndex) throws SQLException;

    /**
     * Updates the underlying database with changes to current row.
     */
    void updateRow() throws SQLException;

    /**
     * Deletes the current row from underlying database.
     */
    void deleteRow() throws SQLException;

    /**
     * Inserts the contents of insert row into database.
     */
    void insertRow() throws SQLException;

    /**
     * Moves cursor to insert row (special row for building new rows).
     */
    void moveToInsertRow() throws SQLException;

    /**
     * Moves cursor back from insert row to previous position.
     */
    void moveToCurrentRow() throws SQLException;

    /**
     * Cancels updates made to current row.
     */
    void cancelRowUpdates() throws SQLException;

    /**
     * Refreshes current row with latest database values.
     */
    void refreshRow() throws SQLException;
}

// Updateable ResultSet example
public class UpdateableResultSetExample {
    public static void updateResults(Connection conn) throws SQLException {
        String sql = "SELECT id, name, email FROM users WHERE active = true";

        // Create updateable ResultSet
        try (Statement stmt = conn.createStatement(
                 ResultSet.TYPE_SCROLL_SENSITIVE,
                 ResultSet.CONCUR_UPDATABLE);
             ResultSet rs = stmt.executeQuery(sql)) {

            while (rs.next()) {
                String email = rs.getString("email");

                // Update email if needed
                if (email.endsWith("@old.com")) {
                    String newEmail = email.replace("@old.com", "@new.com");
                    rs.updateString("email", newEmail);
                    rs.updateRow(); // Commit changes to database
                }
            }
        }
    }

    public static void insertViaResultSet(Connection conn) throws SQLException {
        String sql = "SELECT id, name, email FROM users";

        try (Statement stmt = conn.createStatement(
                 ResultSet.TYPE_SCROLL_SENSITIVE,
                 ResultSet.CONCUR_UPDATABLE);
             ResultSet rs = stmt.executeQuery(sql)) {

            // Move to insert row
            rs.moveToInsertRow();

            // Set values for new row
            rs.updateString("name", "New User");
            rs.updateString("email", "new@example.com");

            // Insert into database
            rs.insertRow();

            // Move back to normal cursor
            rs.moveToCurrentRow();
        }
    }
}