Connection

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class ConnectionLifecycle {
    public static void main(String[] args) {
        String url = "jdbc:postgresql://localhost:5432/storedb";

        // try-with-resources: Connection, PreparedStatement, and ResultSet
        // are all AutoCloseable and are closed automatically, in reverse
        // order of creation, even if an exception is thrown
        try (Connection conn = DriverManager.getConnection(url, "app_user", "secret");
             PreparedStatement ps = conn.prepareStatement(
                     "SELECT id, name FROM products WHERE category = ?")) {

            ps.setString(1, "electronics");
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    System.out.println(rs.getLong("id") + ": " + rs.getString("name"));
                }
            }

        } catch (SQLException e) {
            // Connection (and any derived statements/result sets still open)
            // are guaranteed closed by this point, even though we landed here
            System.err.println("Database error: " + e.getMessage());
        }

        // ── The older, more error-prone manual pattern, for contrast ───────
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(url, "app_user", "secret");
            // ... use conn ...
        } catch (SQLException e) {
            System.err.println("Database error: " + e.getMessage());
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException ignored) {
                    // closing failures are usually safe to log and ignore
                }
            }
        }
    }
}

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Savepoint;

public class TransactionExample {
    public static void transferFunds(Connection conn, long fromId, long toId, java.math.BigDecimal amount)
            throws SQLException {
        boolean originalAutoCommit = conn.getAutoCommit();
        conn.setAutoCommit(false);  // start a manual transaction

        Savepoint beforeDebit = null;
        try {
            beforeDebit = conn.setSavepoint("beforeDebit");

            try (PreparedStatement debit = conn.prepareStatement(
                    "UPDATE accounts SET balance = balance - ? WHERE id = ?")) {
                debit.setBigDecimal(1, amount);
                debit.setLong(2, fromId);
                debit.executeUpdate();
            }

            try (PreparedStatement credit = conn.prepareStatement(
                    "UPDATE accounts SET balance = balance + ? WHERE id = ?")) {
                credit.setBigDecimal(1, amount);
                credit.setLong(2, toId);
                credit.executeUpdate();
            }

            conn.commit();  // both updates become permanent together

        } catch (SQLException e) {
            if (beforeDebit != null) {
                conn.rollback(beforeDebit);  // undo only the debit, if it happened
            } else {
                conn.rollback();  // undo everything in this transaction
            }
            throw e;
        } finally {
            conn.setAutoCommit(originalAutoCommit);  // restore prior state before returning to pool
        }
    }

    public static void demonstrateIsolation(Connection conn) throws SQLException {
        if (conn.getMetaData().supportsTransactionIsolationLevel(
                Connection.TRANSACTION_SERIALIZABLE)) {
            conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
        } else {
            conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
        }
    }
}

import java.sql.*;

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

            // ── Three kinds of statements, all created from Connection ─────
            try (Statement plain = conn.createStatement()) {
                ResultSet rs = plain.executeQuery("SELECT COUNT(*) FROM products");
                rs.next();
                System.out.println("Product count: " + rs.getInt(1));
            }

            try (PreparedStatement ps = conn.prepareStatement(
                    "SELECT name FROM products WHERE id = ?")) {
                ps.setLong(1, 42);
                try (ResultSet rs = ps.executeQuery()) {
                    if (rs.next()) System.out.println(rs.getString("name"));
                }
            }

            try (CallableStatement cs = conn.prepareCall("{call recalc_inventory(?)}")) {
                cs.setLong(1, 42);
                cs.execute();
            }

            // Scrollable, updatable ResultSet variant
            try (Statement scrollable = conn.createStatement(
                    ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE)) {
                ResultSet rs = scrollable.executeQuery("SELECT id, name FROM products");
                if (rs.last()) {
                    System.out.println("Last row id: " + rs.getLong("id"));
                }
            }

            // ── Metadata about the database/driver itself ──────────────────
            DatabaseMetaData meta = conn.getMetaData();
            System.out.println(meta.getDatabaseProductName() + " " + meta.getDatabaseProductVersion());
            System.out.println("Driver: " + meta.getDriverName() + " " + meta.getDriverVersion());

            // ── Health check, the kind pools run before lending a connection ──
            boolean alive = conn.isValid(2);  // 2-second timeout
            System.out.println("Connection healthy: " + alive);
        }
    }
}