JDBC

JDBC (Java Database Connectivity) is the standard API for talking to relational databases from Java. It defines a vendor-neutral set of interfaces — Connection, Statement, ResultSet — that database drivers implement. Your code targets the interfaces; swapping PostgreSQL for MySQL becomes a matter of changing the driver and URL.

The JDBC Flow

A typical operation moves through four stages.

  DriverManager                                   ResultSet
  (or DataSource)  ──►  Connection  ──►  Statement ──►  (rows)
   loads driver,        open session    SQL command    iterate &
   resolves URL                          + params      read columns

1. Driver — the vendor implementation, auto-loaded from the classpath (JDBC 4+).
2. Connection — an authenticated session with the database.
3. Statement / PreparedStatement — carries the SQL to execute.
4. ResultSet — a cursor over the rows returned by a query.

Establishing a Connection

DriverManager.getConnection opens a connection from a URL, username, and password. The URL format is jdbc:<vendor>://<host>:<port>/<database>.

import java.sql.*;

String url = "jdbc:postgresql://localhost:5432/shop";

try (Connection conn = DriverManager.getConnection(url, "admin", "secret")) {
    System.out.println("connected: " + !conn.isClosed());
} catch (SQLException e) {
    System.out.println("connection failed: " + e.getMessage());
}

The connection is declared in try-with-resources so it is closed automatically, even on exception.

Note: Since JDBC 4.0 you no longer need Class.forName("...Driver"). Drivers register themselves via the ServiceLoader mechanism when present on the classpath.

PreparedStatement and SQL Injection Safety

Never build SQL by concatenating user input — that is the classic SQL injection vulnerability. PreparedStatement sends the query and parameters separately; the database treats parameters strictly as data, never as executable SQL.

// VULNERABLE — never do this
String sql = "SELECT * FROM users WHERE email = '" + email + "'";

// SAFE — parameterized
String safe = "SELECT * FROM users WHERE email = ?";
try (PreparedStatement ps = conn.prepareStatement(safe)) {
    ps.setString(1, email); // bound as data, not code
    try (ResultSet rs = ps.executeQuery()) {
        while (rs.next()) {
            System.out.println(rs.getString("name"));
        }
    }
}

PreparedStatement also performs better: the database can cache the parsed query plan and reuse it across executions.

Warning: Parameter indexes are 1-based, not 0-based. setString(1, ...) binds the first ?.

A Full CRUD Example

The four operations share the same pattern: prepare, bind, execute.

import java.sql.*;

// CREATE
try (PreparedStatement ps = conn.prepareStatement(
        "INSERT INTO products (name, price) VALUES (?, ?)")) {
    ps.setString(1, "Keyboard");
    ps.setBigDecimal(2, new java.math.BigDecimal("49.99"));
    int rows = ps.executeUpdate(); // returns affected row count
    System.out.println("inserted " + rows);
}

// READ
try (PreparedStatement ps = conn.prepareStatement(
        "SELECT id, name, price FROM products WHERE price < ?")) {
    ps.setBigDecimal(1, new java.math.BigDecimal("100"));
    try (ResultSet rs = ps.executeQuery()) {
        while (rs.next()) {
            System.out.printf("%d: %s%n", rs.getInt("id"), rs.getString("name"));
        }
    }
}

// UPDATE
try (PreparedStatement ps = conn.prepareStatement(
        "UPDATE products SET price = ? WHERE id = ?")) {
    ps.setBigDecimal(1, new java.math.BigDecimal("39.99"));
    ps.setInt(2, 1);
    ps.executeUpdate();
}

// DELETE
try (PreparedStatement ps = conn.prepareStatement(
        "DELETE FROM products WHERE id = ?")) {
    ps.setInt(1, 1);
    ps.executeUpdate();
}

Operation	Method	Returns
SELECT	executeQuery()	ResultSet
INSERT/UPDATE/DELETE	executeUpdate()	affected row count (int)
DDL or unknown	execute()	boolean (has result set?)

Transactions

By default each statement auto-commits. For multi-statement atomicity, disable auto-commit and commit explicitly, rolling back on failure.

conn.setAutoCommit(false);
try {
    // ... multiple updates ...
    conn.commit();
} catch (SQLException e) {
    conn.rollback();
    throw e;
}

Connection Pooling

Opening a database connection is expensive — TCP handshake, authentication, session setup. In production, never use raw DriverManager per request. A connection pool (HikariCP, the de facto standard) keeps a set of warm connections and hands them out instantly; close() returns the connection to the pool rather than tearing it down.

import com.zaxxer.hikari.*;

HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:postgresql://localhost:5432/shop");
config.setUsername("admin");
config.setPassword("secret");
config.setMaximumPoolSize(10);

try (HikariDataSource ds = new HikariDataSource(config);
     Connection conn = ds.getConnection()) {
    // conn comes from the pool; close() returns it
}

Tip: Size the pool to your database’s capacity, not your traffic. A small pool (often cores * 2) usually outperforms a large one by reducing contention inside the database.

Best Practices

• Always use PreparedStatement with bound parameters — never concatenate user input into SQL.
• Wrap Connection, Statement, and ResultSet in try-with-resources so they close reliably.
• Use a connection pool (HikariCP) in any real application; reserve DriverManager for scripts.
• Manage transactions explicitly for multi-statement units of work, with rollback on error.
• Select only the columns you need and fetch in batches for large result sets.
• Log SQLException with its SQLState and error code for actionable diagnostics.