Java JDBC Tutorial

What and Why

JDBC (Java Database Connectivity) is the standard API for talking to relational databases from Java. Every higher-level tool, from Spring Data to Hibernate to jOOQ, sits on top of JDBC. Understanding it makes you a better consumer of those libraries and unblocks you when frameworks misbehave.

JDBC gives you four building blocks: DriverManager, Connection, Statement/PreparedStatement, and ResultSet. Master those and you can query any RDBMS.

Mental Model

JDBC is request/response over a TCP connection. The driver translates Java method calls into the database’s wire protocol. A Connection is an expensive socket; in production you almost always reuse them through a pool. A Statement is one query execution; a ResultSet is a cursor over rows.

Java code --> DriverManager --> Driver --> TCP --> Database
                                            <-- ResultSet rows

Hands-on Example

Add the driver to your classpath (e.g., org.postgresql:postgresql). Then:

import java.sql.*;

public class JdbcDemo {
  public static void main(String[] args) throws SQLException {
    String url = "jdbc:postgresql://localhost:5432/shop";
    try (Connection c = DriverManager.getConnection(url, "app", "secret")) {
      c.setAutoCommit(false);
      try (PreparedStatement ps = c.prepareStatement(
             "INSERT INTO orders(user_id, total) VALUES (?, ?) RETURNING id")) {
        ps.setLong(1, 42);
        ps.setBigDecimal(2, new java.math.BigDecimal("19.99"));
        try (ResultSet rs = ps.executeQuery()) {
          if (rs.next()) System.out.println("New order id: " + rs.getLong(1));
        }
      }
      c.commit();
    }
  }
}

Three key habits already visible: try-with-resources for everything, PreparedStatement for parameters, and explicit transactions instead of auto-commit.

Common Pitfalls

String concatenation in SQL. Never build queries with "WHERE name = '" + input + "'". That’s SQL injection waiting to happen. Use ? placeholders and setString.

Leaking connections. Forgetting to close a Connection, Statement, or ResultSet exhausts the pool. Try-with-resources closes them in reverse order automatically.

Default isolation surprises. Different databases default to different isolation levels (Postgres: Read Committed; SQL Server: Read Committed too, but with snapshot semantics differences). Set it explicitly when correctness depends on it: c.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ).

Auto-commit on by default. Each statement becomes its own transaction. For multi-statement work, disable auto-commit and commit explicitly, or your “transaction” isn’t atomic.

Fetch size for large reads. Many drivers buffer the entire result set in memory. Call ps.setFetchSize(1000) and use a server-side cursor for big queries (Postgres requires auto-commit off for this).

Practical Tips

• Use a connection pool: HikariCP is the de-facto standard. DriverManager alone is for tests and demos.
• Wrap SQLException with context (which query, which params) before rethrowing; the default message is rarely enough.
• Prefer getObject(column, Class) to typed getters when nullability matters; typed getters like getLong return 0 for SQL NULL.
• For batch inserts, use ps.addBatch() and ps.executeBatch() to send N rows in one round-trip.
• Validate JDBC URLs and credentials early at startup so failures don’t surface on first user request.

Wrap-up

JDBC is small but unforgiving: forget a close, skip a ?, or ignore a transaction boundary and you’ll hit production issues. The good news is the surface area is tiny. Internalize try-with-resources, PreparedStatement, explicit transactions, and pooling, and you have a solid foundation for any Java data-access stack you build on top.