jdbc

the picture

you know how one charger cable works with phones from a dozen brands? the plug is a standard; each phone implements its side. JDBC is the plug. your code only talks to the interfaces in java.sql. the driver jar (postgres, mysql, oracle...) is the vendor's side, translating those calls into the database's own wire protocol.

the JDK ships the interfaces and no driver at all. you can see the plug with nothing in it:

import java.sql.*;
 
void main() {
    IO.println("drivers on the classpath: " + DriverManager.drivers().count());
    try (Connection c = DriverManager.getConnection("jdbc:postgresql://localhost/shop", "app", "secret")) {
        IO.println("connected?");
    } catch (SQLException e) {
        IO.println(e.getMessage());
    }
}

drivers on the classpath: 0
No suitable driver found for jdbc:postgresql://localhost/shop

that's why the rest of this chapter's code is shown, not run: there's no database here. put the postgres driver on the classpath (and a database behind it) and the code below works as written.

the shape of every query

String sql = "SELECT id, name, total FROM orders WHERE total > ? ORDER BY id";
 
try (Connection conn = dataSource.getConnection();
     PreparedStatement ps = conn.prepareStatement(sql)) {
 
    ps.setInt(1, 100);                          // parameters are 1-based
 
    try (ResultSet rs = ps.executeQuery()) {
        while (rs.next()) {                     // move the cursor, false when done
            long id = rs.getLong("id");
            String name = rs.getString("name");
            int total = rs.getInt("total");
            orders.add(new Order(id, name, total));
        }
    }
}

three resources, all AutoCloseable, all in try-with-resources. a leaked Connection is a leaked server-side session, and with a pool it's worse: the pool runs dry and everything starts waiting.

writes use executeUpdate(), which returns the number of rows changed. for many rows, addBatch() and executeBatch() send them in one round trip instead of one each, which is usually the single biggest JDBC speedup there is.

never build SQL with +

the one rule of JDBC. if user input is concatenated into SQL, the user is writing part of your query.

void main() {
    String[] inputs = { "ada", "x' OR '1'='1" };
    for (String name : inputs) {
        String sql = "SELECT * FROM users WHERE name = '" + name + "'";
        IO.println(sql);
    }
}

SELECT * FROM users WHERE name = 'ada'
SELECT * FROM users WHERE name = 'x' OR '1'='1'

String sql = "SELECT * FROM users WHERE name = '"
    + name + "' AND password = '" + pw + "'";
ResultSet rs = conn.createStatement().executeQuery(sql);

var ps = conn.prepareStatement(
    "SELECT * FROM users WHERE name = ? AND password_hash = ?");
ps.setString(1, name);
ps.setString(2, hash(pw));
ResultSet rs = ps.executeQuery();

placeholders work for values only. table names, column names, ORDER BY directions and IN (...) lists of unknown length can't be ?. for those, pick from an allowlist in code (Map.of("newest", "created_at DESC")), never from raw input. (and yes, the good side also hashes the password. see security and crypto.)

transactions

by default a JDBC connection is in autocommit mode: every statement is its own transaction, committed the moment it finishes. to make several statements succeed or fail together, turn that off:

try (Connection conn = dataSource.getConnection()) {
    conn.setAutoCommit(false);
    try (var debit = conn.prepareStatement("UPDATE accounts SET balance = balance - ? WHERE id = ?");
         var credit = conn.prepareStatement("UPDATE accounts SET balance = balance + ? WHERE id = ?")) {
        debit.setInt(1, 80);  debit.setLong(2, alice);  debit.executeUpdate();
        credit.setInt(1, 80); credit.setLong(2, bob);   credit.executeUpdate();
        conn.commit();                     // both, or...
    } catch (SQLException e) {
        conn.rollback();                   // ...neither
        throw e;
    }
}

atomic is only half of it. the other half is isolation: while your transaction runs, other transactions are running too, and the isolation level decides how much of their unfinished or freshly-committed work you can see.

that's the standard's table. real databases differ, and the differences matter: postgres, oracle and SQL server default to READ_COMMITTED, mysql's InnoDB defaults to REPEATABLE_READ. postgres and InnoDB implement REPEATABLE_READ with a snapshot, so the phantom in the figure doesn't happen there for plain reads. and SERIALIZABLE often doesn't block, it aborts one of the transactions with a serialization error that your code must catch and retry. set the level per connection with conn.setTransactionIsolation(...), and read your database's docs for what it actually means.

connection pools

opening a connection is a TCP handshake, usually TLS, authentication, and server-side session setup: several network round trips and real work on the database. doing that per request is the classic slow-app bug. a pool opens a few connections once and lends them out: getConnection() borrows one, close() gives it back instead of closing it.

the pool size is a knob on the database's capacity, not your app's thread count: a database with 8 cores doesn't run 200 queries in parallel any faster than 20. and since connections now get reused, anything a request changes on one (autocommit, isolation, a session variable) has to be reset before the next borrower gets it. good pools do that for you.