15.1 JDBC Architecture & Driver Types

JDBC (Java Database Connectivity) is a vendor-neutral API (java.sql) — your code talks to interfaces (Connection, Statement, ResultSet); a vendor driver implements them for a specific database.

15.2 The Six Standard Steps

// 1. Load driver (optional since JDBC 4 — auto-discovered from the JAR)
Class.forName("com.mysql.cj.jdbc.Driver");
// 2. Open connection (URL, user, password)
Connection con = DriverManager.getConnection(
        "jdbc:mysql://localhost:3306/college", "root", "secret");
// 3. Create a statement          4. Execute SQL
Statement st = con.createStatement();
ResultSet rs = st.executeQuery("SELECT id, name, marks FROM student");
// 5. Process results
while (rs.next())                               // cursor starts BEFORE row 1
    System.out.println(rs.getInt("id") + " " + rs.getString("name"));
// 6. Close (reverse order) — or use try-with-resources
rs.close(); st.close(); con.close();

URL anatomy: jdbc:<subprotocol>://<host>:<port>/<database>. Execution methods: executeQuery → SELECT, returns ResultSet; executeUpdate → INSERT/UPDATE/DELETE/DDL, returns affected-row count; execute → either, returns boolean (true = ResultSet available).

15.3 Statement vs PreparedStatement — and SQL Injection

// VULNERABLE: attacker enters  x' OR '1'='1  and dumps every row
String q = "SELECT * FROM student WHERE name = '" + userInput + "'";

// SAFE: value is sent separately from the SQL, never parsed as SQL
PreparedStatement ps = con.prepareStatement("SELECT * FROM student WHERE name = ?");
ps.setString(1, userInput);                     // 1-based index!
ResultSet rs = ps.executeQuery();

SQL injection is the exam keyword: with Statement, crafted input like '; DROP TABLE student; -- becomes executable SQL. PreparedStatement transmits parameters out-of-band, so input can never change the query's structure.

15.4 ResultSet Essentials

The cursor starts before the first row; next() advances and returns false past the end — hence while (rs.next()). Getters by column index (1-based) or label: getInt, getString, getDouble, getDate. Default ResultSet is forward-only and read-only; scrollable/updatable variants come from createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE). Metadata: rs.getMetaData().getColumnCount() — handy for generic table printers.

15.5 Transactions

con.setAutoCommit(false);                        // begin manual transaction
try {
    debit.executeUpdate(); credit.executeUpdate();
    con.commit();                                // both or nothing
} catch (SQLException e) {
    con.rollback();                              // undo partial work
}

By default JDBC auto-commits every statement; money-transfer style logic must group statements atomically. Mention ACID here for bonus marks.

15.6 CRUD Cheat Sheet

CREATE → INSERT INTO student(name, marks) VALUES (?, ?) + executeUpdate; READ → SELECT + executeQuery; UPDATE → UPDATE student SET marks = ? WHERE id = ?; DELETE → DELETE FROM student WHERE id = ?. The code snippet implements all four against SQLite/MySQL-style SQL with try-with-resources throughout.

🎯 Exam Focus

1. Explain JDBC architecture and the four driver types. Why is Type-4 preferred?
2. List the six steps to connect Java to a database, with the API class/interface used at each step.
3. Differentiate Statement and PreparedStatement. Explain SQL injection with an attack string and show how PreparedStatement blocks it.
4. Differentiate executeQuery(), executeUpdate() and execute(). How does ResultSet's cursor work?
5. Write a JDBC program performing full CRUD on a STUDENT(id, name, marks) table using PreparedStatement and try-with-resources.
6. How are transactions managed in JDBC? Explain setAutoCommit(false), commit() and rollback() with a funds-transfer example.