PreparedStatement

import java.sql.*;
import java.math.BigDecimal;
import java.time.LocalDate;

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

            // ── Binding multiple typed parameters by 1-based position ──────
            String sql = "INSERT INTO orders (customer_id, total, order_date, notes) " +
                         "VALUES (?, ?, ?, ?)";
            try (PreparedStatement ps = conn.prepareStatement(sql)) {
                ps.setLong(1, 1001L);
                ps.setBigDecimal(2, new BigDecimal("249.99"));
                ps.setDate(3, java.sql.Date.valueOf(LocalDate.now()));
                ps.setNull(4, Types.VARCHAR);  // explicit SQL NULL, not a Java null reference
                ps.executeUpdate();
            }

            // ── Reusing one PreparedStatement across multiple executions ───
            String selectSql = "SELECT name, price FROM products WHERE category = ? AND price < ?";
            try (PreparedStatement ps = conn.prepareStatement(selectSql)) {
                for (String category : new String[] {"electronics", "books", "toys"}) {
                    ps.setString(1, category);
                    ps.setBigDecimal(2, new BigDecimal("50.00"));
                    try (ResultSet rs = ps.executeQuery()) {
                        while (rs.next()) {
                            System.out.println(category + ": " + rs.getString("name")
                                    + " $" + rs.getBigDecimal("price"));
                        }
                    }
                    ps.clearParameters();  // optional here, since both setters are re-called each loop
                }
            }
        }
    }
}

import java.sql.*;
import java.math.BigDecimal;
import java.util.List;

public class PreparedStatementBatch {
    record ProductRow(String name, BigDecimal price, String category) {}

    public static void bulkInsert(Connection conn, List<ProductRow> rows) throws SQLException {
        String sql = "INSERT INTO products (name, price, category) VALUES (?, ?, ?)";
        boolean originalAutoCommit = conn.getAutoCommit();
        conn.setAutoCommit(false);

        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            int batchSize = 0;
            for (ProductRow row : rows) {
                ps.setString(1, row.name());
                ps.setBigDecimal(2, row.price());
                ps.setString(3, row.category());
                ps.addBatch();          // queue this parameter set; SQL text unchanged
                batchSize++;

                // Flushing every 1000 rows avoids holding an enormous batch in memory
                if (batchSize % 1000 == 0) {
                    ps.executeBatch();
                    ps.clearBatch();
                }
            }
            ps.executeBatch();   // flush any remaining rows under the last 1000-row boundary
            conn.commit();
        } catch (BatchUpdateException e) {
            conn.rollback();
            System.err.println("Batch insert failed after " + e.getUpdateCounts().length + " statements");
            throw e;
        } finally {
            conn.setAutoCommit(originalAutoCommit);
        }
    }
}

import java.sql.*;

public class GeneratedKeysExample {
    public static long insertAndGetId(Connection conn, String name, java.math.BigDecimal price)
            throws SQLException {
        String sql = "INSERT INTO products (name, price) VALUES (?, ?)";

        // Tell the driver to track whatever key the database generates
        try (PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
            ps.setString(1, name);
            ps.setBigDecimal(2, price);
            ps.executeUpdate();

            try (ResultSet keys = ps.getGeneratedKeys()) {
                if (keys.next()) {
                    return keys.getLong(1);  // generated key is usually the first/only column
                }
                throw new SQLException("Insert succeeded but no generated key was returned");
            }
        }
    }

    // ── Alternative on databases supporting RETURNING (e.g. PostgreSQL) ───────
    public static long insertAndGetIdWithReturning(Connection conn, String name, java.math.BigDecimal price)
            throws SQLException {
        String sql = "INSERT INTO products (name, price) VALUES (?, ?) RETURNING id";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, name);
            ps.setBigDecimal(2, price);
            try (ResultSet rs = ps.executeQuery()) {  // note: executeQuery, since RETURNING yields rows
                rs.next();
                return rs.getLong("id");
            }
        }
    }
}