JDBC Fundamentals in Java

What JDBC is

JDBC is a set of interfaces in the standard library. Each database vendor supplies a driver that implements them, so the same code works against different databases with only the connection details changing.

Your code -> JDBC API -> Driver -> Database

The four steps

String url = "jdbc:mysql://localhost:3306/notes_management";

try (Connection connection = DriverManager.getConnection(url, user, password);
     PreparedStatement statement = connection.prepareStatement(
             "SELECT id, title, views_count FROM notes WHERE status = ?")) {

    statement.setString(1, "published");

    try (ResultSet rows = statement.executeQuery()) {
        while (rows.next()) {
            long id = rows.getLong("id");
            String title = rows.getString("title");
            int views = rows.getInt("views_count");
            System.out.printf("%d %s (%d)%n", id, title, views);
        }
    }
}

1. Open a Connection.
2. Create a Statement, almost always a PreparedStatement.
3. Execute it and read the ResultSet.
4. Close everything, which try with resources does for you.

Since JDBC 4 the driver is discovered automatically from the classpath. Class.forName("com.mysql.jdbc.Driver") appears in older tutorials and is no longer needed.

The connection URL

jdbc:mysql://host:3306/database?useSSL=true
jdbc:postgresql://host:5432/database
jdbc:h2:mem:testdb                          an in memory database, useful in tests
jdbc:sqlite:notes.db

The three statement types

Reading a ResultSet

try (ResultSet rows = statement.executeQuery()) {
    while (rows.next()) {                 // starts before the first row
        String title = rows.getString("title");

        Integer views = rows.getObject("views_count", Integer.class);   // null safe
        LocalDate published = rows.getObject("published_on", LocalDate.class);

        int raw = rows.getInt("views_count");
        if (rows.wasNull()) {              // getInt returns 0 for SQL NULL
            raw = -1;
        }
    }
}

The primitive getters cannot represent NULL: getInt returns 0 and getBoolean returns false. Either call wasNull() immediately afterwards, or use getObject with a wrapper type, which returns null honestly. This is one of the most common JDBC bugs.

java.time types

statement.setObject(1, LocalDate.of(2026, 8, 21));
statement.setObject(2, Instant.now().atOffset(ZoneOffset.UTC));

LocalDate date = rows.getObject("published_on", LocalDate.class);
OffsetDateTime created = rows.getObject("created_at", OffsetDateTime.class);

JDBC 4.2 maps java.time types directly. java.sql.Date and java.sql.Timestamp are legacy and should not appear in new code.

Insert, update and delete

String sql = "INSERT INTO notes (title, slug, status) VALUES (?, ?, ?)";

try (PreparedStatement statement =
             connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {

    statement.setString(1, "JDBC basics");
    statement.setString(2, "jdbc-basics");
    statement.setString(3, "draft");

    int affected = statement.executeUpdate();

    try (ResultSet keys = statement.getGeneratedKeys()) {
        if (keys.next()) {
            long id = keys.getLong(1);
            System.out.println("Inserted note " + id);
        }
    }
}

Mapping rows to objects

public record Note(long id, String title, int views, LocalDate published) { }

public List<Note> findPublished() throws SQLException {
    String sql = """
            SELECT id, title, views_count, published_on
            FROM notes
            WHERE status = ?
            ORDER BY published_on DESC""";

    List<Note> notes = new ArrayList<>();

    try (Connection connection = dataSource.getConnection();
         PreparedStatement statement = connection.prepareStatement(sql)) {

        statement.setString(1, "published");

        try (ResultSet rows = statement.executeQuery()) {
            while (rows.next()) {
                notes.add(new Note(
                        rows.getLong("id"),
                        rows.getString("title"),
                        rows.getInt("views_count"),
                        rows.getObject("published_on", LocalDate.class)));
            }
        }
    }
    return notes;
}

DataSource and pooling

// DriverManager opens a fresh connection every time: slow
Connection connection = DriverManager.getConnection(url, user, password);

// A pooled DataSource reuses connections
DataSource dataSource = configuredPool();
try (Connection pooled = dataSource.getConnection()) {
    // close() returns it to the pool rather than closing the socket
}

Opening a database connection involves a network round trip and authentication, so it is expensive. Any real application uses a connection pool behind a DataSource. Keep the mention brief: the pool is a library, the DataSource interface is standard.

Closing matters

// Leaks a connection on any exception
Connection connection = dataSource.getConnection();
Statement statement = connection.createStatement();
ResultSet rows = statement.executeQuery(sql);
// ...
rows.close();
statement.close();
connection.close();

// Closed on every path, in reverse order
try (Connection connection = dataSource.getConnection();
     PreparedStatement statement = connection.prepareStatement(sql);
     ResultSet rows = statement.executeQuery()) {
    // ...
}

A leaked connection is not reclaimed by garbage collection in any timely way. Enough of them and the pool is exhausted and the application stops.

Metadata

DatabaseMetaData metadata = connection.getMetaData();
System.out.println(metadata.getDatabaseProductName());
System.out.println(metadata.getDriverVersion());

ResultSetMetaData columns = rows.getMetaData();
for (int i = 1; i <= columns.getColumnCount(); i++) {
    System.out.println(columns.getColumnLabel(i) + " " + columns.getColumnTypeName(i));
}

Note that JDBC column indexes start at 1, not 0. This catches almost everyone at least once.

Exceptions

try {
    repository.save(note);
} catch (SQLException e) {
    logger.severe("SQL state " + e.getSQLState() + ", code " + e.getErrorCode());
    throw new StorageException("Could not save note", e);
}

SQLException is checked. getSQLState() returns a standard code and getErrorCode() a vendor specific one, which is how a duplicate key can be distinguished from a connection failure.

Common mistakes

• Building SQL by concatenating input, which opens an injection hole.
• Not closing connections, statements or result sets.
• Using index 0 for a column or a parameter.
• Ignoring wasNull() after a primitive getter.
• Calling DriverManager per request instead of using a pool.
• Selecting every column when only two are needed.
• Loading a whole table into memory instead of filtering in SQL.

Best practices

• Always use PreparedStatement with bound parameters.
• Always use try with resources.
• Use a pooled DataSource.
• Select only the columns you use.
• Use getObject with a wrapper or java.time type for nullable columns.
• Wrap SQLException in a domain exception, keeping the cause.
• Keep SQL in one layer rather than scattered through the application.

Practice

1. Write a method that returns every note in a category, using a bound parameter.
2. Explain why getInt returns 0 for a NULL column and how to detect it.
3. Insert a row and read back the generated key.
4. Why does a leaked connection eventually stop the whole application?
5. Convert a manual close sequence into try with resources and list what improved.

Conclusion

JDBC is a small API: a connection, a prepared statement, a result set. Bind parameters, close everything with try with resources, pool your connections, and map rows into records at the boundary.