All topics
92 / 101
library
intermediate

JDBC Basics & PreparedStatement

Connect to databases, execute queries safely with PreparedStatements, and manage transactions.

JDBC is the standard API for talking to a relational database from Java.

JDBC = a phone call to a database. Connection = the phone line. PreparedStatement = a form letter with blanks (parameters). Statement = dictating the letter freeform (risky if caller inserts malicious text).

Key Concepts

1
    String sql = "SELECT id, total FROM orders WHERE status = ?";
2
    try (Connection c = dataSource.getConnection();
         PreparedStatement ps = c.prepareStatement(sql)) {
      ps.setString(1, status);
      try (ResultSet rs = ps.executeQuery()) {
        while (rs.next()) {
          String id = rs.getString("id");
          double total = rs.getDouble("total");
        }
      }
    }
3
Always use PreparedStatement. The alternative is a SQL injection:
PreparedStatement
4
    // never do this
    stmt.executeQuery("SELECT * FROM users WHERE name = '" + name + "'");
5
    // name = "x' OR '1'='1"  returns every user
    // name = "x'; DROP TABLE users; --"  does worse
6
A placeholder is not string substitution. The value is sent separately from the statement, so it can never be read as SQL. Prepared statements are also compiled once and reusable, which is faster for repeated queries.
7
Close everything, with try-with-resources. A leaked Connection is not returned to the pool, so the application runs fine and then stops dead when the pool is exhausted.
Connection
8
Transactions are off by default. Each statement commits on its own:
9
    c.setAutoCommit(false);
    try {
      debit(c, from, amount);
      credit(c, to, amount);
      c.commit();
    } catch (SQLException e) {
      c.rollback();
      throw e;
    }
10
Without this, a failure between the two leaves money debited and never credited.
11
The isolation levels, weakest first:
12
    READ_UNCOMMITTED   sees uncommitted rows
    READ_COMMITTED     the usual default
    REPEATABLE_READ    the same row reads the same twice
    SERIALIZABLE       as if transactions ran one at a time
13
Never use DriverManager in production. Opening a connection costs a TCP handshake plus authentication — tens of milliseconds. Use HikariCP and reuse connections.
DriverManager
14
Batch for bulk writes.
    for (var o : orders) { ps.setString(1, o.id()); ps.addBatch(); }
    ps.executeBatch();
15
One round trip instead of thousands, which is often a hundredfold difference.
16
What the interviewer is probing.1. "Why does PreparedStatement prevent SQL injection?" Probing: the mechanism, not the slogan. Stalls: "It escapes the input." Moves up: the statement and the values travel separately, so a parameter is never parsed as SQL — it is not escaping, it is a different channel.
17
2. "What happens if you leak a Connection?" Probing: pool exhaustion. Stalls: "The GC closes it." Moves up: it is never returned to the pool, so the application runs normally until the pool is exhausted and then stops dead.
18
3. "Why set autoCommit false?" Probing: transaction boundaries. Stalls: "For speed." *Moves up:* each statement otherwise commits on its own, so a failure between a debit and a credit leaves money gone — the two must be one transaction.
19
4. "Why is DriverManager wrong in production?" Probing: connection cost. Stalls: "It works." Moves up: every call opens a TCP connection and authenticates, tens of milliseconds; a pool like HikariCP reuses them.
Use cases:DatabaseSecurityFundamentals