Lesson 01 · JDBC Core

Beyond the 1Z0-830 exam

JDBC is the JDK's low-level database API — the foundation under every higher-level tool (JPA, Spring Data, jOOQ). You rarely write raw JDBC in apps anymore, but understanding it (and Statement vs PreparedStatement) is essential for testing data access and reasoning about SQL injection.

Objectives

After this lesson you will be able to:

• Open a Connection and run queries/updates.
• Use a PreparedStatement (and explain why, vs Statement).
• Read a ResultSet and close resources correctly.
• Explain how parameterization prevents SQL injection.

The JDBC objects

String url = "jdbc:h2:mem:test";   // driver-specific URL identifies the database
try (Connection conn = DriverManager.getConnection(url)) {
    try (Statement st = conn.createStatement()) {
        st.execute("CREATE TABLE users (id BIGINT PRIMARY KEY, name VARCHAR(100))");
    }
}

Object	Role
Connection	a session with the database
Statement	a static SQL statement (DDL, fixed queries)
PreparedStatement	a parameterized statement (? placeholders) — the default for queries with input
ResultSet	a cursor over query results

Trap — always close JDBC resources

Connection, Statement, and ResultSet hold database/native resources the GC won't reclaim promptly. Leaking them exhausts the connection pool and crashes the app. Always use try-with-resources (Module 04) — and note the order: a ResultSet is closed before its Statement, which closes before its Connection.

PreparedStatement and ResultSet

String sql = "SELECT id, name FROM users WHERE email = ?";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
    ps.setString(1, email);          // 1-based index! binds the parameter
    try (ResultSet rs = ps.executeQuery()) {
        while (rs.next()) {          // advance the cursor; false when exhausted
            long id = rs.getLong("id");
            String name = rs.getString("name");
        }
    }
}

• executeQuery() → ResultSet (SELECT); executeUpdate() → int row count (INSERT/UPDATE/DELETE).
• Parameter and column indices are 1-based, a classic off-by-one trap.
• For generated keys: prepareStatement(sql, Statement.RETURN_GENERATED_KEYS) then ps.getGeneratedKeys().

SQL injection — and why PreparedStatement stops it

The unforgivable mistake — building SQL by concatenation:

// ❌ NEVER do this:
st.executeQuery("SELECT * FROM users WHERE email = '" + email + "'");

If email is x' OR '1'='1, the query becomes ... WHERE email = 'x' OR '1'='1' — returning every row. Worse payloads can drop tables.

With a PreparedStatement, the ? value is sent to the database separately from the SQL text and treated as a literal — never parsed as SQL. The same malicious string simply matches no rows. The lab proves it:

dao.insert("Real", "[email protected]");
assertThat(dao.findByEmail("x' OR '1'='1")).isEmpty(); // payload is just a literal

Gotcha

PreparedStatement parameterizes values, not identifiers. You can't bind a table or column name with ? (SELECT * FROM ? is invalid). If a table/column name must be dynamic, validate it against an allow-list — never concatenate user input into the SQL structure.

SDET note

The DAO in this module's lab takes a Connection in its constructor rather than creating one — the same dependency-injection move from Module 14. That's what lets the test run it against in-memory H2 while production passes a pooled connection. Testable data access starts with not hard-wiring the connection.

Key Takeaways

• JDBC: Connection → Statement/PreparedStatement → ResultSet; executeQuery returns rows, executeUpdate returns a count.
• Always close JDBC resources with try-with-resources (ResultSet → Statement → Connection).
• Use PreparedStatement for anything with input — indices are 1-based.
• Parameterization sends values separately from SQL, neutralizing SQL injection; never concatenate user input into SQL.
• ? binds values, not identifiers — allow-list dynamic table/column names.

Lesson Quiz

Lesson Quiz · JDBC Core0 / 5

1. Why prefer PreparedStatement over concatenating values into a Statement's SQL?

   • AIt runs faster only
   • BBound parameters are treated as data, not SQL — preventing injection (and allowing reuse)
   • CStatement can't do SELECT
   • DIt auto-closes itself

2. What does executeUpdate() return?

   • AA ResultSet
   • BThe number of affected rows
   • CA boolean
   • DThe generated key

3. JDBC parameter and column indices are…

   • A0-based
   • B1-based
   • Cnamed only
   • Drandom

4. Best way to ensure a Connection/Statement/ResultSet is closed?

   • ASet them to null
   • Btry-with-resources
   • CCall System.gc()
   • DNothing — JDBC closes them

5. Can you bind a table name with a ? placeholder, e.g. SELECT * FROM ?

   • AYes, always
   • BNo — ? binds values, not identifiers; validate dynamic names against an allow-list
   • COnly for SELECT
   • DOnly in H2

---

Next: Transactions & Pooling. This module's lab is in labs/src/main/java/com/jse21/m16_databases/.