JDBC Overview

JDBC (Java Database Connectivity) is the standard API for talking to relational databases from Java. It lives in the java.sql and javax.sql packages and is almost entirely interfaces: Connection, PreparedStatement, ResultSet and a few more. The code that actually speaks PostgreSQL's or MySQL's network protocol is a driver, a JAR published by the database vendor. Your code compiles against the interfaces; the driver supplies the implementations at run time.

The moving parts

• DataSource (or the older DriverManager) hands out connections.
• Connection is one session with the database: a socket, a login and the current transaction.
• PreparedStatement is SQL with ? placeholders plus the values bound to them.
• ResultSet is a cursor over the rows a query returned.
• SQLException is the checked exception nearly every method throws. getSQLState() returns a five-character code (in PostgreSQL, 23505 is a unique violation) that is far more reliable than parsing the message.

Finding the driver

The database is chosen by a JDBC URL such as jdbc:postgresql://db:5432/shop. Since JDBC 4.0, drivers register themselves through ServiceLoader as soon as their JAR is on the class path, so the Class.forName("org.postgresql.Driver") line in older tutorials is not needed. If no registered driver accepts the URL, you get No suitable driver found for ...: the driver JAR is missing at run time, or the URL prefix is misspelt.

Closing everything

Connections, statements and result sets hold resources on both ends of the socket. Open them in try-with-resources so they close in reverse order even when a query throws. A leaked connection does not fail straight away. In a pooled service it fails hours later, when the pool runs dry and every request starts timing out.

Where JDBC sits

The rest of the Java data world is built on top of it. Spring's JdbcClient and JdbcTemplate remove the boilerplate, jOOQ builds type-safe SQL, and JPA with Hibernate maps objects to tables. All of them call JDBC underneath, so its rules about connections, transactions and parameters still apply when a framework hides them, and the stack traces you debug in production end in JDBC calls.

JDBC is a blocking API: each call waits for the database to answer. That suits a thread-per-request server, and with virtual threads the waiting thread is cheap. The number of database connections stays the real limit, which is why pooling gets its own lesson.

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.sql.DataSource;

public class ProductQueries {
    public record Product(long id, String name, BigDecimal price) {}

    private final DataSource dataSource;

    public ProductQueries(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    public List<Product> cheaperThan(BigDecimal limit) throws SQLException {
        String sql = "SELECT id, name, price FROM products WHERE price < ? ORDER BY price";
        // Connection and statement close in reverse order, even if the query throws.
        try (Connection conn = dataSource.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setBigDecimal(1, limit);              // placeholders are numbered from 1
            try (ResultSet rs = ps.executeQuery()) {
                List<Product> products = new ArrayList<>();
                while (rs.next()) {                  // moves to the next row, false at the end
                    products.add(new Product(
                        rs.getLong("id"),
                        rs.getString("name"),
                        rs.getBigDecimal("price")));
                }
                return products;
            }
        }
    }
}

<!-- pom.xml: the code compiles against java.sql, so the driver is only needed at run time -->
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.7.13</version>
    <scope>runtime</scope>
</dependency>

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

void main() {
    // No driver JAR is on the class path here, so nothing accepts the URL.
    String url = "jdbc:postgresql://localhost:5432/shop";
    try (Connection conn = DriverManager.getConnection(url, "app", "not-a-real-password")) {
        IO.println("connected");
    } catch (SQLException e) {
        IO.println(e.getMessage());
        IO.println("SQLState: " + e.getSQLState());
        IO.println("drivers registered: " + DriverManager.drivers().count());
    }
}

// Output:
// No suitable driver found for jdbc:postgresql://localhost:5432/shop
// SQLState: 08001
// drivers registered: 0

# PostgreSQL
postgres.url=jdbc:postgresql://db.internal:5432/shop
# MySQL
mysql.url=jdbc:mysql://db.internal:3306/shop
# Microsoft SQL Server
sqlserver.url=jdbc:sqlserver://db.internal:1433;databaseName=shop;encrypt=true
# H2 in memory, for quick local experiments only
h2.url=jdbc:h2:mem:shop;DB_CLOSE_DELAY=-1