jdbc2CrudStatements

1package pkg11jdbc;2 3import java.sql.Connection;4import java.sql.DriverManager;5import java.sql.PreparedStatement;6import java.sql.ResultSet;7import java.sql.SQLException;8import java.sql.Statement;9 10/*11 * jdbc2CrudStatements.java12 * ------------------------13 * CRUD with Statement vs PreparedStatement (Create/Read/Update/Delete).14 *15 * DEFINITION:16 *   Statement sends fixed SQL; PreparedStatement sends parameterized SQL with ?17 *   placeholders. ALWAYS prefer PreparedStatement: it prevents SQL injection and18 *   lets the DB cache the query plan.19 *20 * KEY POINTS:21 *   - executeUpdate() returns affected row count (INSERT/UPDATE/DELETE/DDL).22 *   - executeQuery() returns a ResultSet (SELECT).23 *   - ps.setInt/setString bind params (1-based index) — never concatenate input.24 *   - getGeneratedKeys() retrieves auto-increment ids.25 *26 * Runs a full demo IF an in-memory DB driver (H2/SQLite/HSQLDB) is on the27 * classpath; otherwise prints the SQL it would run. Enable with e.g.:28 *   java -cp ".;h2.jar" pkg11jdbc/jdbc2CrudStatements.java29 */30public class jdbc2CrudStatements {31 32    /** Tries common in-memory databases; returns a live Connection or null. */33    static Connection tryConnect() {34        String[] urls = {"jdbc:h2:mem:demo;DB_CLOSE_DELAY=-1",35                         "jdbc:sqlite::memory:",36                         "jdbc:hsqldb:mem:demo"};37        for (String url : urls) {38            try { return DriverManager.getConnection(url); }39            catch (SQLException ignored) { /* driver not present, try next */ }40        }41        return null;42    }43 44    public static void main(String[] args) throws SQLException {45        Connection conn = tryConnect();46        if (conn == null) {47            System.out.println("No in-memory DB driver found. The SQL this demo runs:");48            System.out.println("""49                  CREATE TABLE users (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50), age INT);50                  INSERT INTO users (name, age) VALUES ('Ada', 36);   -- Create51                  SELECT id, name, age FROM users;                    -- Read52                  UPDATE users SET age = 37 WHERE name = 'Ada';       -- Update53                  DELETE FROM users WHERE name = 'Ada';               -- Delete""");54            System.out.println("\nAdd h2.jar/sqlite-jdbc.jar to the classpath to run it for real.");55            return;56        }57 58        try (conn) {59            // CREATE (DDL)60            try (Statement st = conn.createStatement()) {61                st.execute("CREATE TABLE users (id INT PRIMARY KEY AUTO_INCREMENT, " +62                           "name VARCHAR(50), age INT)");63            }64 65            // CREATE (rows) via PreparedStatement, fetching generated key66            try (PreparedStatement ps = conn.prepareStatement(67                    "INSERT INTO users (name, age) VALUES (?, ?)",68                    Statement.RETURN_GENERATED_KEYS)) {69                ps.setString(1, "Ada");70                ps.setInt(2, 36);71                int rows = ps.executeUpdate();72                try (ResultSet keys = ps.getGeneratedKeys()) {73                    if (keys.next()) System.out.println("Inserted " + rows + " row, id=" + keys.getInt(1));74                }75            }76 77            // READ78            try (PreparedStatement ps = conn.prepareStatement("SELECT id, name, age FROM users");79                 ResultSet rs = ps.executeQuery()) {80                while (rs.next())81                    System.out.printf("Read   : id=%d name=%s age=%d%n",82                            rs.getInt("id"), rs.getString("name"), rs.getInt("age"));83            }84 85            // UPDATE86            try (PreparedStatement ps = conn.prepareStatement("UPDATE users SET age = ? WHERE name = ?")) {87                ps.setInt(1, 37);88                ps.setString(2, "Ada");89                System.out.println("Updated: " + ps.executeUpdate() + " row(s)");90            }91 92            // DELETE93            try (PreparedStatement ps = conn.prepareStatement("DELETE FROM users WHERE name = ?")) {94                ps.setString(1, "Ada");95                System.out.println("Deleted: " + ps.executeUpdate() + " row(s)");96            }97        }98    }99}