Raw JDBC — feel the pain.

What JDBC is

JDBC stands for Java Database Connectivity. It is the official, low-level API in the standard library for talking to a relational database from Java.

JDBC has two layers:

1. The JDBC API — interfaces in java.sql.* like Connection, Statement, ResultSet
2. The JDBC Driver — a vendor-specific JAR that implements those interfaces for a particular database (Postgres driver, MySQL driver, Oracle driver…)

Day-11 hands-on · setup

If you want to follow along (recommended), you need:

1. A local Postgres (or any DB) running. brew install postgresql && brew services start postgresql on macOS, or run docker run --name pg -e POSTGRES_PASSWORD=pass -p 5432:5432 -d postgres
2. A users table — CREATE TABLE users (id SERIAL PRIMARY KEY, email TEXT UNIQUE, name TEXT, created_at TIMESTAMPTZ DEFAULT now());
3. The Postgres JDBC driver on your classpath (Maven: org.postgresql:postgresql:42.7.2)

If you'd rather just read, the code below tells the story without running.

The five interfaces you must know

Hello JDBC — the simplest possible read

import java.sql.*;

public class HelloJdbc {
  public static void main(String[] args) throws SQLException {
    String url = "jdbc:postgresql://localhost:5432/postgres";
    String user = "postgres";
    String pass = "pass";

    try (Connection conn = DriverManager.getConnection(url, user, pass);
         Statement stmt = conn.createStatement();
         ResultSet rs = stmt.executeQuery("SELECT id, email FROM users")) {
      while (rs.next()) {
        long id = rs.getLong("id");
        String email = rs.getString("email");
        System.out.println(id + " " + email);
      }
    }
  }
}

That's it. Five things happen:

1. DriverManager.getConnection opens a TCP connection to Postgres
2. conn.createStatement() gives you a thing that runs SQL
3. executeQuery(...) returns a ResultSet (a cursor)
4. rs.next() advances the cursor; returns false when done
5. try-with-resources auto-closes the three resources

PreparedStatement — and why Statement is dangerous

Look at this innocent-looking code:

String email = req.getParameter("email"); // from a form
String sql = "SELECT * FROM users WHERE email = '" + email + "'";
stmt.executeQuery(sql);

If a malicious user submits the email ' OR '1'='1, the SQL becomes:

SELECT * FROM users WHERE email = '' OR '1'='1'

Now you've returned every user. Worse versions can drop tables. This is SQL injection. The fix is PreparedStatement with placeholders:

String sql = "SELECT * FROM users WHERE email = ?";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
  ps.setString(1, email); // driver escapes safely
  try (ResultSet rs = ps.executeQuery()) {
    while (rs.next()) { ... }
  }
}

Full CRUD example — feel the boilerplate

// CREATE
public long createUser(Connection conn, String email, String name) throws SQLException {
  String sql = "INSERT INTO users(email, name) VALUES (?, ?)";
  try (PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
    ps.setString(1, email);
    ps.setString(2, name);
    ps.executeUpdate();
    try (ResultSet keys = ps.getGeneratedKeys()) {
      if (keys.next()) return keys.getLong(1);
      throw new SQLException("no key returned");
    }
  }
}

// READ — by id
public Optional<User> findById(Connection conn, long id) throws SQLException {
  String sql = "SELECT id, email, name FROM users WHERE id = ?";
  try (PreparedStatement ps = conn.prepareStatement(sql)) {
    ps.setLong(1, id);
    try (ResultSet rs = ps.executeQuery()) {
      if (!rs.next()) return Optional.empty();
      return Optional.of(new User(rs.getLong("id"), rs.getString("email"), rs.getString("name")));
    }
  }
}

// UPDATE
public int updateName(Connection conn, long id, String newName) throws SQLException {
  try (PreparedStatement ps = conn.prepareStatement("UPDATE users SET name = ? WHERE id = ?")) {
    ps.setString(1, newName);
    ps.setLong(2, id);
    return ps.executeUpdate(); // rows affected
  }
}

// DELETE
public int delete(Connection conn, long id) throws SQLException {
  try (PreparedStatement ps = conn.prepareStatement("DELETE FROM users WHERE id = ?")) {
    ps.setLong(1, id);
    return ps.executeUpdate();
  }
}

Look at the noise. Try-blocks nesting. Manual parameter setting. Manual ResultSet iteration. Manual mapping from columns to fields. Every single Spring/JPA convenience replaces a chunk of this.

Transactions in raw JDBC

By default, JDBC is auto-commit: every executeUpdate() commits immediately. To group multiple statements:

try (Connection conn = DriverManager.getConnection(...)) {
  conn.setAutoCommit(false); // start a transaction
  try {
    // many INSERT/UPDATE/DELETE here...
    conn.commit();
  } catch (SQLException e) {
    conn.rollback();
    throw e;
  }
}

Spring's @Transactional wraps your method with these exact lines. Now you know.

Connection pooling — why DriverManager isn't enough

Opening a TCP connection to a database takes ~50–200ms. If every HTTP request opens its own, your app is dead at 100 RPS.

The fix is a connection pool: a pre-warmed cache of open connections. You "borrow" one for a request and "return" it when done. The most popular pool today is HikariCP — Spring Boot's default.

The pain points — what you just felt

1. Boilerplate everywhere — every query has try-blocks, parameter setting, iteration
2. Manual mapping — column → field is hand-coded for every table
3. Checked SQLException — pollutes every signature with throws SQLException
4. No type safety — column names are strings; typos surface at runtime
5. Easy to leak resources — forget to close → app dies eventually
6. Easy to introduce SQL injection — one momentary lapse and you're vulnerable

What every layer above JDBC fixes

End of Day 11. Tomorrow: Servlets — concept walkthrough. No real coding, just diagrams and the lifecycle.