ResultSet

import java.sql.*;

public class ResultSetBasics {
    public static void main(String[] args) throws SQLException {
        try (Connection conn = DriverManager.getConnection(
                "jdbc:postgresql://localhost:5432/storedb", "app_user", "secret");
             PreparedStatement ps = conn.prepareStatement(
                     "SELECT id, name, price, in_stock, created_at FROM products WHERE category = ?")) {

            ps.setString(1, "electronics");

            try (ResultSet rs = ps.executeQuery()) {
                // Cursor starts BEFORE the first row — next() must be called first
                while (rs.next()) {
                    // Label-based access — preferred, resilient to column reordering
                    long id = rs.getLong("id");
                    String name = rs.getString("name");
                    java.math.BigDecimal price = rs.getBigDecimal("price");
                    boolean inStock = rs.getBoolean("in_stock");
                    Timestamp createdAt = rs.getTimestamp("created_at");

                    System.out.printf("%d: %s — $%.2f — stock=%b — created=%s%n",
                            id, name, price, inStock, createdAt);
                }
            }

            // Index-based access — works, but breaks silently if SELECT column
            // order changes; generally avoided in favor of label-based access
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    long idByIndex = rs.getLong(1);   // 1-based, matches SELECT order above
                    String nameByIndex = rs.getString(2);
                }
            }
        }
    }
}

import java.sql.*;

public class ResultSetScrollableUpdatable {
    public static void main(String[] args) throws SQLException {
        try (Connection conn = DriverManager.getConnection(
                "jdbc:postgresql://localhost:5432/storedb", "app_user", "secret")) {

            // ── Scrollable, read-only: random access without modification ──
            try (Statement stmt = conn.createStatement(
                    ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                 ResultSet rs = stmt.executeQuery("SELECT id, name FROM products ORDER BY id")) {

                if (rs.last()) {                       // jump straight to the last row
                    System.out.println("Last product id: " + rs.getLong("id"));
                }
                rs.beforeFirst();                       // reset to before the first row
                if (rs.relative(3)) {                    // move forward 3 rows from current position
                    System.out.println("Third product: " + rs.getString("name"));
                }
            }

            // ── Scrollable AND updatable: modify rows directly through the cursor ──
            try (Statement stmt = conn.createStatement(
                    ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
                 ResultSet rs = stmt.executeQuery("SELECT id, name, price FROM products WHERE id = 42")) {

                if (rs.next()) {
                    rs.updateBigDecimal("price", new java.math.BigDecimal("24.99"));
                    rs.updateRow();   // pushes the change back to the database
                }

                rs.moveToInsertRow();
                rs.updateString("name", "New Widget");
                rs.updateBigDecimal("price", new java.math.BigDecimal("9.99"));
                rs.insertRow();   // inserts a brand-new row via the ResultSet itself
                rs.moveToCurrentRow();
            }
        }
    }
}

import java.sql.*;

public class ResultSetMetadataAndNulls {
    public static void describeAnyQuery(Connection conn, String sql) throws SQLException {
        try (Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            ResultSetMetaData meta = rs.getMetaData();
            int columnCount = meta.getColumnCount();

            for (int i = 1; i <= columnCount; i++) {
                System.out.printf("Column %d: %s (%s), nullable=%s%n",
                        i, meta.getColumnLabel(i), meta.getColumnTypeName(i),
                        meta.isNullable(i) == ResultSetMetaData.columnNullable);
            }
        }
    }

    public static void demonstrateNullPitfall(Connection conn) throws SQLException {
        try (Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(
                     "SELECT discount_percent FROM products WHERE id = 42")) {
            if (rs.next()) {
                int discount = rs.getInt("discount_percent");
                // BUG-PRONE: if discount_percent is SQL NULL, discount is silently 0 —
                // indistinguishable from an actual stored value of 0 without checking wasNull()
                if (rs.wasNull()) {
                    System.out.println("No discount set (column was NULL)");
                } else {
                    System.out.println("Discount: " + discount + "%");
                }

                // Alternative: use the Object-returning getter, where null IS
                // distinguishable directly without a follow-up check
                Integer discountObj = rs.getObject("discount_percent", Integer.class);
                if (discountObj == null) {
                    System.out.println("No discount set (via getObject)");
                }
            }
        }
    }
}