JDBC Deep Dive: Talking to Databases the Hard Way in Java

JDBC — the Java Database Connectivity API — shipped with JDK 1.1 in 1997. It was low-level, verbose, and unforgiving of errors. It was also the most important API in enterprise Java for the next decade. Before Hibernate, before Spring's JdbcTemplate, before connection pools were standard library features, JDBC was how Java applications talked to databases.

Here is a complete walkthrough of the API, the patterns we developed to make it workable, and the places it would bite you if you were not careful.

Connecting to a Database

import java.sql.*;

// Load the JDBC driver — MySQL in this case
Class.forName("org.gjt.mm.mysql.Driver");

// Open a connection
Connection conn = DriverManager.getConnection(
    "jdbc:mysql://dbserver:3306/nms",
    "nmsuser",
    "secret"
);

Class.forName loaded the driver class, which registered itself with DriverManager. Every connection was a TCP socket to the database server. Opening connections was expensive — typically 50–200ms. You never opened one per query.

Querying with PreparedStatement

Always use PreparedStatement for queries with parameters. Never concatenate user input into SQL strings.

// WRONG — SQL injection risk
String sql = "SELECT * FROM devices WHERE ip = '" + ipInput + "'";
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);

// RIGHT — parameterised query
PreparedStatement ps = conn.prepareStatement(
    "SELECT ip, name, status, last_polled FROM devices WHERE ip = ?"
);
ps.setString(1, ipInput);
ResultSet rs = ps.executeQuery();

while (rs.next()) {
    String ip      = rs.getString("ip");
    String name    = rs.getString("name");
    String status  = rs.getString("status");
    long   polled  = rs.getLong("last_polled");
    System.out.printf("%s (%s) — %s%n", name, ip, status);
}

PreparedStatement also compiles the query once and executes it many times, which was relevant for frequently-run queries.

Resource Cleanup

The most common JDBC bug in 1997 was resource leaks. Connections and ResultSets were backed by network sockets and database cursors. If you did not close them, they accumulated until the database refused new connections.

The pattern was try/finally:

Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
    conn = DriverManager.getConnection(url, user, pass);
    ps   = conn.prepareStatement("SELECT * FROM devices WHERE status = ?");
    ps.setString(1, "DOWN");
    rs   = ps.executeQuery();
    while (rs.next()) {
        processDevice(rs.getString("ip"), rs.getString("name"));
    }
} finally {
    // Close in reverse order of opening
    if (rs   != null) try { rs.close();   } catch (SQLException ignored) {}
    if (ps   != null) try { ps.close();   } catch (SQLException ignored) {}
    if (conn != null) try { conn.close(); } catch (SQLException ignored) {}
}

This is verbose but correct. The try-with-resources syntax in Java 7 replaced it, but the resource management discipline it enforces is the same.

Transactions

JDBC connections default to auto-commit: each statement is a separate transaction. For operations that must succeed or fail atomically, disable auto-commit and manage the transaction manually:

conn.setAutoCommit(false);
try {
    PreparedStatement update = conn.prepareStatement(
        "UPDATE devices SET status = ? WHERE ip = ?");
    PreparedStatement log = conn.prepareStatement(
        "INSERT INTO status_changes (ip, old_status, new_status, changed_at) VALUES (?,?,?,?)");

    update.setString(1, "DOWN");
    update.setString(2, "192.168.1.10");
    update.executeUpdate();

    log.setString(1, "192.168.1.10");
    log.setString(2, "UP");
    log.setString(3, "DOWN");
    log.setLong(4, System.currentTimeMillis());
    log.executeUpdate();

    conn.commit(); // both updates succeed together
} catch (SQLException e) {
    conn.rollback(); // or neither does
    throw e;
} finally {
    conn.setAutoCommit(true);
}

A Minimal Connection Pool

Opening a connection per query was too expensive. We built a simple pool that kept a fixed number of connections open and recycled them:

public class ConnectionPool {
    private final Stack<Connection> available = new Stack<>();
    private final String url, user, pass;
    private final int    maxSize;

    public ConnectionPool(String url, String user, String pass, int size)
            throws SQLException {
        this.url = url; this.user = user; this.pass = pass; this.maxSize = size;
        for (int i = 0; i < size; i++) available.push(openConnection());
    }

    public synchronized Connection acquire() throws SQLException, InterruptedException {
        while (available.isEmpty()) wait();
        return available.pop();
    }

    public synchronized void release(Connection conn) {
        available.push(conn);
        notifyAll();
    }

    private Connection openConnection() throws SQLException {
        return DriverManager.getConnection(url, user, pass);
    }
}

Usage:

Connection conn = pool.acquire();
try {
    // use conn
} finally {
    pool.release(conn); // always return to pool
}

This pool did not handle broken connections — a production pool needed health checks and reconnection logic. Libraries like DBCP (2001) and c3p0 handled these cases.

Batch Inserts

Inserting rows one at a time was slow — each executeUpdate was a round trip to the database. For bulk inserts, batching reduced round trips dramatically:

conn.setAutoCommit(false);
PreparedStatement ps = conn.prepareStatement(
    "INSERT INTO poll_results (ip, status, uptime, polled_at) VALUES (?,?,?,?)");

for (PollResult result : results) {
    ps.setString(1, result.ip);
    ps.setString(2, result.status);
    ps.setLong(3, result.uptime);
    ps.setLong(4, result.polledAt);
    ps.addBatch(); // queue, don't execute
}
ps.executeBatch(); // single round trip for all rows
conn.commit();

For our NMS polling 500 devices every 30 seconds, this reduced database load significantly.

What Came Next

Hibernate (2001) layered an object-relational mapping on top of JDBC. Spring's JdbcTemplate (2003) eliminated the boilerplate while keeping direct SQL. Both of these were improvements. But understanding JDBC directly meant understanding what the ORM was actually doing — which was essential when you needed to diagnose slow queries, debug connection pool exhaustion, or understand a transaction isolation issue.

The abstraction is only safe when you understand what it abstracts.