Java From Zero to Job-ReadyLesson 28 of 41

Lessons

1 · Java Foundations

2 · Object-Oriented Java

3 · Core Java

4 · Collections and Generics

5 · Functional Java

6 · The JVM and Concurrency

7 · Tools and Craft

8 · Databases and JDBC

9 · Web and Spring

10 · Persistence with JPA

34JPA and Hibernate: Entities, the Persistence Context, Spring Data and Flyway Migrations
35JPA Relationships, Lazy Loading, the N+1 Problem, Transactions and Optimistic Locking

11 · Secure, Tested, Production-Ready

12 · Interviews and Readiness

Lesson 28 of 41

Lesson 28 · Databases and JDBC

JDBC: Talking to a Database from Java

Learn JDBC, the standard way Java programs use a relational database: connecting with a driver, running queries with PreparedStatement, mapping rows, preventing SQL injection, transactions, error codes, batching and connection pooling, with every program run against PostgreSQL 18.

By Karthikeyan A25 min readTechnology

For

Learners who know basic SQL and need to use a database safely from Java code, before moving on to the frameworks that build on JDBC.

Level

Intermediate

Before you start

SQL and PostgreSQL fundamentals (article 27), try-with-resources and exceptions (article 10), and a Maven project (article 23). The programs were run on JDK 21 and JDK 25 against PostgreSQL 18.6 with PostgreSQL JDBC driver 42.7.13 and HikariCP 7.1.0.

On this page

Key takeaways

JDBC is the standard Java API for relational databases. Your code uses interfaces from java.sql (Connection, PreparedStatement, ResultSet) and a driver, a library you add as a dependency, does the real work for one database.
Always use PreparedStatement with ? parameters for values. Building SQL by joining strings with user input is SQL injection, and the demonstration below shows it returning rows that were never asked for.
Close every Connection, Statement and ResultSet with try-with-resources, and read configuration, especially the password, from the environment and never from source code.
A transaction needs setAutoCommit(false), then commit on success and rollback on failure. A failed batch of three inserts leaves the table exactly as it was.
Opening a connection is slow, so applications use a connection pool such as HikariCP, which hands out a few shared connections. A connection you forget to close is a leak that eventually starves the whole application.

Every Java application that stores data in a relational database, whether it uses Spring, Hibernate or plain code, ends up speaking JDBC (Java Database Connectivity). Frameworks hide it, and when something goes wrong you need to know what is underneath. In this article you write the JDBC code by hand, against the PostgreSQL tables from the last article.

Every program shown here was run against a real PostgreSQL 18.6 server, on both JDK 21 and JDK 25, and the outputs are identical on both.

What you will learn

What JDBC is and how drivers fit in.
How to connect, query, map rows to objects and write data.
Why PreparedStatement matters, with a real SQL injection demonstration.
How to handle transactions, errors and bulk inserts.
Why connection pools exist and how to use one.

What JDBC is

JDBC is a standard API in the java.sql package, a set of interfaces, that describes how Java talks to a relational database. The interfaces do not know about any one database. A driver supplies the implementation for one particular database: the PostgreSQL driver knows PostgreSQL's network protocol, the MySQL driver knows MySQL's. Your code depends on the standard interfaces, and the driver is a library on the classpath.

Code

  your code ---> java.sql interfaces ---> PostgreSQL JDBC driver ---> network ---> PostgreSQL server                 Connection                 PreparedStatement                 ResultSet

The four types you use constantly:

Type	What it is
`Connection`	A session with the database. Transactions belong to it. Opening one is expensive
`PreparedStatement`	A SQL statement with `?` placeholders, to which you attach values
`ResultSet`	The rows returned by a query, read one at a time through a cursor
`SQLException`	The checked exception for database problems. It carries an error code called the SQLState

Setting up

You need a PostgreSQL server, a database for the expenses, and the driver on the classpath. For the server, install PostgreSQL 18 from the PostgreSQL website or your package manager, or run it in Docker. This is the usual Docker command (not run in the environment where this article was verified, which had no Docker, so check the current image documentation if it fails; the programs themselves were verified against a PostgreSQL 18.6 server started another way):

Shell

docker run --name expenses-db -e POSTGRES_PASSWORD=choose-a-password -e POSTGRES_DB=expenses -p 5432:5432 -d postgres:18

Pick your own password and keep it out of the code, as the next sections show. The Maven project depends on the PostgreSQL driver, on HikariCP for the pooling section, and on a logging backend that Hikari needs:

XML

<?xml version="1.0" encoding="UTF-8"?><project xmlns="http://maven.apache.org/POM/4.0.0"         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">  <modelVersion>4.0.0</modelVersion>  <groupId>com.example</groupId>  <artifactId>jdbc-demo</artifactId>  <version>1.0.0</version>  <properties>    <maven.compiler.release>21</maven.compiler.release>    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>  </properties>  <dependencies>    <!-- The JDBC driver: needed when the program runs, not to compile it -->    <dependency>      <groupId>org.postgresql</groupId>      <artifactId>postgresql</artifactId>      <version>42.7.13</version>      <scope>runtime</scope>    </dependency>    <!-- Connection pool (used in PoolDemo) -->    <dependency>      <groupId>com.zaxxer</groupId>      <artifactId>HikariCP</artifactId>      <version>7.1.0</version>    </dependency>    <dependency>      <groupId>org.slf4j</groupId>      <artifactId>slf4j-simple</artifactId>      <version>2.0.18</version>      <scope>runtime</scope>    </dependency>  </dependencies></project>

The driver is in runtime scope (article 23) because the code uses only java.sql types and never imports the driver's own classes. The tables are the ones from article 27. This script drops and recreates them with four sample rows, and every program below starts from this state. It deletes data, so use it only on a throwaway database:

SQL

DROP TABLE IF EXISTS expenses;DROP TABLE IF EXISTS categories;CREATE TABLE categories (    id   integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,    name text NOT NULL UNIQUE);CREATE TABLE expenses (    id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,    spent_on     date   NOT NULL,    amount_cents bigint NOT NULL CHECK (amount_cents > 0),    category_id  integer NOT NULL REFERENCES categories (id),    description  text   NOT NULL DEFAULT '');INSERT INTO categories (name)VALUES ('FOOD'), ('TRANSPORT'), ('HOUSING'), ('UTILITIES'), ('HEALTH'), ('ENTERTAINMENT'), ('OTHER');INSERT INTO expenses (spent_on, amount_cents, category_id, description)VALUES ('2026-09-01', 120000, 3, 'rent'),       ('2026-09-03', 1250, 1, 'lunch with Sam'),       ('2026-09-05', 4500, 2, 'monthly metro pass'),       ('2026-10-02', 320, 1, 'coffee, and a muffin');

Older style. Old tutorials start with Class.forName("org.postgresql.Driver"); to load the driver. Since JDBC 4 (Java 6) that is unnecessary: drivers register themselves through the service-loader mechanism when they are on the classpath, so DriverManager.getConnection(...) just works.

Connecting

A connection needs a URL, a user and a password. The PostgreSQL URL has the form jdbc:postgresql://host:port/database. Never hard-code the password (or other secrets) in source code, because source code is copied, committed and shared (article 24). Read it from the environment instead. This small class is used by every program that follows:

Java

// File: src/main/java/com/example/jdbcdemo/Db.javapackage com.example.jdbcdemo;import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;/** Opens connections using settings from environment variables, never from source code. */public final class Db {    private Db() {    }    public static String url() {        return env("DB_URL", "jdbc:postgresql://localhost:5432/expenses");    }    public static String user() {        return env("DB_USER", "expenses");    }    /** No default on purpose: a password must never be written into the code. */    public static String password() {        return System.getenv("DB_PASSWORD");    }    public static Connection connect() throws SQLException {        return DriverManager.getConnection(url(), user(), password());    }    private static String env(String name, String fallback) {        String value = System.getenv(name);        return value == null || value.isBlank() ? fallback : value;    }}

The password has no default on purpose. DriverManager.getConnection opens a new connection each time, which is simple and fine for scripts and for learning, and much too slow for a busy server, as the pooling section explains.

Query and map rows

Reading rows is a pattern you will write hundreds of times: prepare, set parameters, execute, loop over the ResultSet, map each row to an object, and close everything.

Java

// File: src/main/java/com/example/jdbcdemo/ListExpenses.javapackage com.example.jdbcdemo;import java.sql.Connection;import java.sql.DatabaseMetaData;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.time.LocalDate;import java.util.ArrayList;import java.util.List;public class ListExpenses {    record Expense(long id, LocalDate spentOn, long amountCents, String category, String description) {    }    static List<Expense> findBetween(Connection connection, LocalDate from, LocalDate toExclusive)            throws SQLException {        String sql = """                SELECT e.id, e.spent_on, e.amount_cents, c.name AS category, e.description                FROM expenses e                JOIN categories c ON c.id = e.category_id                WHERE e.spent_on >= ? AND e.spent_on < ?                ORDER BY e.spent_on, e.id                """;        List<Expense> result = new ArrayList<>();        try (PreparedStatement statement = connection.prepareStatement(sql)) {            statement.setObject(1, from);            statement.setObject(2, toExclusive);            try (ResultSet rs = statement.executeQuery()) {                while (rs.next()) {                    result.add(new Expense(                            rs.getLong("id"),                            rs.getObject("spent_on", LocalDate.class),                            rs.getLong("amount_cents"),                            rs.getString("category"),                            rs.getString("description")));                }            }        }        return result;    }    public static void main(String[] args) throws SQLException {        try (Connection connection = Db.connect()) {            DatabaseMetaData meta = connection.getMetaData();            System.out.println("Connected to " + meta.getDatabaseProductName() + " "                    + meta.getDatabaseMajorVersion() + " using " + meta.getDriverName());            List<Expense> september = findBetween(connection, LocalDate.of(2026, 9, 1), LocalDate.of(2026, 10, 1));            september.forEach(System.out::println);            System.out.println(september.size() + " expense(s) in September 2026");        }    }}

Run against the reset data, it prints:

Code

Connected to PostgreSQL 18 using PostgreSQL JDBC DriverExpense[id=1, spentOn=2026-09-01, amountCents=120000, category=HOUSING, description=rent]Expense[id=2, spentOn=2026-09-03, amountCents=1250, category=FOOD, description=lunch with Sam]Expense[id=3, spentOn=2026-09-05, amountCents=4500, category=TRANSPORT, description=monthly metro pass]3 expense(s) in September 2026

What to notice:

Everything is in try-with-resources (article 10), so the Connection, PreparedStatement and ResultSet are closed even if an exception occurs. Forgetting to close a connection is a leak (see pooling).
The ? placeholders are filled by setObject, setString, setLong and friends, numbered from 1.
A ResultSet is a cursor. It starts before the first row, and rs.next() moves to the next row and returns false when there are none. A query that returns no rows is not an error, and the loop simply does not run.
Read columns by name or by position (also from 1): rs.getLong("id") or rs.getLong(1). Names are clearer, and positions are marginally faster.
Dates map to java.time: rs.getObject("spent_on", LocalDate.class) gives a LocalDate directly (JDBC 4.2).
The SQL is a fixed text block (article 8). The half-open date range uses the same pattern as article 27.
The first line of output comes from DatabaseMetaData, which tells you about the database and driver you are really connected to, a handy sanity check.

Types and nulls

SQL type	Java getter	Java type
`integer`	`getInt`	`int`
`bigint`	`getLong`	`long`
`numeric`	`getBigDecimal`	`BigDecimal`
`text`	`getString`	`String`
`boolean`	`getBoolean`	`boolean`
`date`	`getObject(col, LocalDate.class)`	`LocalDate`
`timestamptz`	`getObject(col, OffsetDateTime.class)`	`OffsetDateTime`

The primitive getters (getInt, getLong) return 0 for a SQL NULL, which is indistinguishable from a real zero. For a nullable column, call rs.wasNull() right after the getter, or read it as an object: rs.getObject("col", Long.class) returns null for NULL, and you can wrap it in an Optional (article 17). To write a NULL, use statement.setNull(index, Types.BIGINT) or setObject(index, null).

Older style. Older JDBC code uses java.sql.Date and java.sql.Timestamp with getDate and getTimestamp. They are awkward, mutable classes from before java.time. Use the java.time types with getObject and setObject in new code.

SQL injection

This is the most important security idea in the database layer. SQL injection happens when user-supplied text is glued into a SQL string, so the text can change the structure of the query. It has been one of the most common causes of serious data breaches for decades, and it is entirely avoidable.

The program below contains both a vulnerable method and a safe one, and runs them with an honest input and a hostile one:

Java

// File: src/main/java/com/example/jdbcdemo/InjectionDemo.javapackage com.example.jdbcdemo;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.util.ArrayList;import java.util.List;public class InjectionDemo {    /** VULNERABLE: the user's text becomes part of the SQL. */    static List<String> findUnsafe(Connection connection, String categoryName) throws SQLException {        String sql = "SELECT e.description FROM expenses e JOIN categories c ON c.id = e.category_id "                + "WHERE c.name = '" + categoryName + "' ORDER BY e.id";        System.out.println("  SQL sent: " + sql);        List<String> result = new ArrayList<>();        try (Statement statement = connection.createStatement();             ResultSet rs = statement.executeQuery(sql)) {            while (rs.next()) {                result.add(rs.getString(1));            }        }        return result;    }    /** SAFE: the SQL is fixed, the user's text is sent separately as a parameter. */    static List<String> findSafe(Connection connection, String categoryName) throws SQLException {        String sql = "SELECT e.description FROM expenses e JOIN categories c ON c.id = e.category_id "                + "WHERE c.name = ? ORDER BY e.id";        List<String> result = new ArrayList<>();        try (PreparedStatement statement = connection.prepareStatement(sql)) {            statement.setString(1, categoryName);            try (ResultSet rs = statement.executeQuery()) {                while (rs.next()) {                    result.add(rs.getString(1));                }            }        }        return result;    }    public static void main(String[] args) throws SQLException {        String honest = "FOOD";        String attack = "x' OR '1'='1";        try (Connection connection = Db.connect()) {            System.out.println("Unsafe, honest input:");            System.out.println("  rows: " + findUnsafe(connection, honest));            System.out.println("Unsafe, hostile input:");            System.out.println("  rows: " + findUnsafe(connection, attack));            System.out.println("Safe, hostile input:");            System.out.println("  rows: " + findSafe(connection, attack));        }    }}

Code

Unsafe, honest input:  SQL sent: SELECT e.description FROM expenses e JOIN categories c ON c.id = e.category_id WHERE c.name = 'FOOD' ORDER BY e.id  rows: [lunch with Sam, coffee, and a muffin]Unsafe, hostile input:  SQL sent: SELECT e.description FROM expenses e JOIN categories c ON c.id = e.category_id WHERE c.name = 'x' OR '1'='1' ORDER BY e.id  rows: [rent, lunch with Sam, monthly metro pass, coffee, and a muffin]Safe, hostile input:  rows: []

Read the second run carefully. The program was meant to find the expenses of one category, but the hostile input x' OR '1'='1 closed the string literal early and added an OR that is always true. The SQL that was actually sent (printed by the program) returns every row. An attacker can use the same trick to read other tables, bypass a login check, or run destructive statements, and the database cannot tell the difference, because it just receives text. The third run is the safe version with the same hostile input: it returned nothing, because the text was treated as a value, a category name, that simply matches no row.

Why does the PreparedStatement version work? The SQL text with its ? placeholders is sent to the database as the statement, and the values are sent separately. The database never parses a value as SQL, so no text a user supplies can alter the query's structure.

The rules:

Never build SQL by concatenating or formatting user input (+, String.format, formatted, text-block interpolation). This applies to every value, even ones that "come from the database" or "can't contain quotes".
Always use ? parameters for values. Escaping by hand is fragile and wrong.
Placeholders work for values only, not for table names, column names or ORDER BY directions. If those must vary, choose them from a fixed list in your code (a switch or an enum), never from the raw input.
Give the application's database user only the permissions it needs. If an injection ever happens, the damage is limited. An application rarely needs DROP TABLE.
Keep detailed database errors out of responses to users, because they reveal table and column names (more on this below).

(ORMs and frameworks use PreparedStatement for you, but they still allow raw query strings, so the rules still apply.)

Writing data

INSERT, UPDATE and DELETE use executeUpdate, which returns the number of rows affected. This is how you detect "nothing matched". For an insert, you usually want the generated id, and PostgreSQL's RETURNING clause gives it back in the same round trip, so you run it with executeQuery:

Java

// File: src/main/java/com/example/jdbcdemo/CrudDemo.javapackage com.example.jdbcdemo;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.time.LocalDate;import java.util.Optional;public class CrudDemo {    static long insert(Connection connection, LocalDate date, long cents, String category, String description)            throws SQLException {        String sql = """                INSERT INTO expenses (spent_on, amount_cents, category_id, description)                VALUES (?, ?, (SELECT id FROM categories WHERE name = ?), ?)                RETURNING id                """;        try (PreparedStatement statement = connection.prepareStatement(sql)) {            statement.setObject(1, date);            statement.setLong(2, cents);            statement.setString(3, category);            statement.setString(4, description);            try (ResultSet rs = statement.executeQuery()) {                rs.next();                return rs.getLong(1);            }        }    }    static Optional<String> findDescription(Connection connection, long id) throws SQLException {        try (PreparedStatement statement =                     connection.prepareStatement("SELECT description FROM expenses WHERE id = ?")) {            statement.setLong(1, id);            try (ResultSet rs = statement.executeQuery()) {                return rs.next() ? Optional.of(rs.getString(1)) : Optional.empty();            }        }    }    static int updateAmount(Connection connection, long id, long newCents) throws SQLException {        try (PreparedStatement statement =                     connection.prepareStatement("UPDATE expenses SET amount_cents = ? WHERE id = ?")) {            statement.setLong(1, newCents);            statement.setLong(2, id);            return statement.executeUpdate();        }    }    static int delete(Connection connection, long id) throws SQLException {        try (PreparedStatement statement = connection.prepareStatement("DELETE FROM expenses WHERE id = ?")) {            statement.setLong(1, id);            return statement.executeUpdate();        }    }    public static void main(String[] args) throws SQLException {        try (Connection connection = Db.connect()) {            long id = insert(connection, LocalDate.of(2026, 10, 5), 1850, "FOOD", "dinner");            System.out.println("inserted id " + id);            System.out.println("found: " + findDescription(connection, id));            System.out.println("updated rows: " + updateAmount(connection, id, 1900));            System.out.println("deleted rows: " + delete(connection, id));            System.out.println("deleted again: " + delete(connection, id));            System.out.println("found after delete: " + findDescription(connection, id));        }    }}

Code

inserted id 5found: Optional[dinner]updated rows: 1deleted rows: 1deleted again: 0found after delete: Optional.empty

The id is 5 because the four sample rows took 1 to 4. Notice that deleting twice shows the value of the return count: the first DELETE affected one row, the second affected none (0), which a real repository would turn into "not found". findDescription returns an Optional rather than null for a missing row.

The portable alternative to RETURNING is connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS) followed by statement.getGeneratedKeys(). It works with several databases, while RETURNING is a PostgreSQL feature that is simpler to read.

Transactions

By default a JDBC connection is in autocommit mode: each statement commits as soon as it runs. To group statements (article 27), turn autocommit off, then commit or roll back yourself. The standard pattern is below. The method adds several expenses as one unit, and the demonstration deliberately makes the last one fail:

Java

// File: src/main/java/com/example/jdbcdemo/TransactionDemo.javapackage com.example.jdbcdemo;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.time.LocalDate;public class TransactionDemo {    /** Adds all the amounts as expenses of one category, or none of them. */    static void addAll(Connection connection, long[] amounts) throws SQLException {        boolean originalAutoCommit = connection.getAutoCommit();        connection.setAutoCommit(false);                       // start a transaction        try (PreparedStatement statement = connection.prepareStatement(                "INSERT INTO expenses (spent_on, amount_cents, category_id) VALUES (?, ?, 1)")) {            for (long amount : amounts) {                statement.setObject(1, LocalDate.of(2026, 10, 6));                statement.setLong(2, amount);                statement.executeUpdate();            }            connection.commit();                               // all succeeded        } catch (SQLException e) {            connection.rollback();                             // undo everything since setAutoCommit(false)            throw e;        } finally {            connection.setAutoCommit(originalAutoCommit);        }    }    static int count(Connection connection) throws SQLException {        try (Statement statement = connection.createStatement();             ResultSet rs = statement.executeQuery("SELECT count(*) FROM expenses")) {            rs.next();            return rs.getInt(1);        }    }    public static void main(String[] args) throws SQLException {        try (Connection connection = Db.connect()) {            System.out.println("before: " + count(connection));            addAll(connection, new long[] {500, 600});            System.out.println("after a good batch of 2: " + count(connection));            try {                addAll(connection, new long[] {700, 800, -1});   // the last one breaks the CHECK            } catch (SQLException e) {                System.out.println("failed with SQLState " + e.getSQLState());            }            System.out.println("after a failed batch of 3: " + count(connection));        }    }}

Code

before: 4after a good batch of 2: 6failed with SQLState 23514after a failed batch of 3: 6

The batch of three had two valid inserts (700 and 800) and one that breaks the CHECK (amount_cents > 0) constraint with -1. The result is that the count is still 6: none of the three was stored, including the two that were valid when they ran. That is atomicity, and the code that makes it so is the shape you should memorise:

setAutoCommit(false) starts the transaction.
Do the work. If everything succeeds, commit().
In the catch, rollback() and rethrow, so the caller still sees the failure.
In finally, restore autocommit, because the connection may be reused by someone else (a pooled connection especially).

Transaction rules of thumb: keep them short, never wait for user input inside one, and keep one transaction on one connection (and, in a multi-threaded program, one thread). connection.setTransactionIsolation(...) selects the isolation level from article 27 when the default Read Committed is not what you need, and connection.setSavepoint() lets you roll back part of a transaction. Spring's @Transactional (article 35) wraps exactly this try/commit/rollback logic so you do not write it by hand.

Reading database errors

When the database rejects a statement, the driver throws a SQLException. Its message is for developers, and its SQLState is a five-character code that a program can act on. The first two characters name the class of error. PostgreSQL publishes the full list in its documentation, and the ones you meet most often are:

SQLState	Meaning	Typical cause
`23505`	Unique violation	Inserting a duplicate
`23503`	Foreign key violation	A parent row is missing, or still referenced
`23514`	Check violation	A `CHECK` rule failed
`23502`	Not-null violation	A required column was missing
`42703`	Undefined column	A typo in the SQL
`40001`	Serialization failure	A conflict at a strict isolation level. Retry the transaction
`40P01`	Deadlock detected	Two transactions waited for each other. Retry

This program provokes four different errors and reports the SQLState for each:

Java

// File: src/main/java/com/example/jdbcdemo/ErrorsDemo.javapackage com.example.jdbcdemo;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.SQLException;public class ErrorsDemo {    static void attempt(Connection connection, String label, String sql, Object... parameters) {        try (PreparedStatement statement = connection.prepareStatement(sql)) {            for (int i = 0; i < parameters.length; i++) {                statement.setObject(i + 1, parameters[i]);            }            statement.executeUpdate();            System.out.println(label + ": ok");        } catch (SQLException e) {            String firstLine = e.getMessage().lines().findFirst().orElse("");            System.out.println(label + ": SQLState " + e.getSQLState() + " (" + meaning(e.getSQLState()) + ")");            System.out.println("    " + firstLine);        }    }    static String meaning(String sqlState) {        return switch (sqlState) {            case "23505" -> "unique violation";            case "23503" -> "foreign key violation";            case "23514" -> "check violation";            case "23502" -> "not-null violation";            default -> "other";        };    }    public static void main(String[] args) throws SQLException {        try (Connection connection = Db.connect()) {            attempt(connection, "duplicate category", "INSERT INTO categories (name) VALUES (?)", "FOOD");            attempt(connection, "unknown category", "INSERT INTO expenses (spent_on, amount_cents, category_id) "                    + "VALUES (current_date, 100, ?)", 99);            attempt(connection, "negative amount", "INSERT INTO expenses (spent_on, amount_cents, category_id) "                    + "VALUES (current_date, ?, 1)", -5L);            attempt(connection, "misspelled column", "UPDATE expenses SET amount = 1 WHERE id = 1");        }    }}

Code

duplicate category: SQLState 23505 (unique violation)    ERROR: duplicate key value violates unique constraint "categories_name_key"unknown category: SQLState 23503 (foreign key violation)    ERROR: insert or update on table "expenses" violates foreign key constraint "expenses_category_id_fkey"negative amount: SQLState 23514 (check violation)    ERROR: new row for relation "expenses" violates check constraint "expenses_amount_cents_check"misspelled column: SQLState 42703 (other)    ERROR: column "amount" of relation "expenses" does not exist

Use the SQLState, not the message text, to decide what to do: a 23505 on categories can become "that category already exists" for the user, while 42703 is a bug that should be logged and fixed. Two warnings: the messages contain internal names and sometimes the offending values, so do not send them to end users, and log them with care, because a value in an error message may be personal data (article 22).

Batching

Sending many similar statements one by one costs one network round trip each. A batch queues the statements and sends them together:

Java

// File: src/main/java/com/example/jdbcdemo/BatchDemo.javapackage com.example.jdbcdemo;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.time.LocalDate;public class BatchDemo {    public static void main(String[] args) throws SQLException {        try (Connection connection = Db.connect()) {            connection.setAutoCommit(false);            try (PreparedStatement statement = connection.prepareStatement(                    "INSERT INTO expenses (spent_on, amount_cents, category_id, description) VALUES (?, ?, 1, ?)")) {                for (int i = 1; i <= 1000; i++) {                    statement.setObject(1, LocalDate.of(2026, 1, 1).plusDays(i % 28));                    statement.setLong(2, 100 + i);                    statement.setString(3, "bulk " + i);                    statement.addBatch();                     // queue the row                }                int[] counts = statement.executeBatch();      // send them together                connection.commit();                System.out.println("batch sent " + counts.length + " inserts");            }            try (Statement statement = connection.createStatement();                 ResultSet rs = statement.executeQuery("SELECT count(*) FROM expenses WHERE description LIKE 'bulk %'")) {                rs.next();                System.out.println("rows stored: " + rs.getInt(1));            }        }    }}

Code

batch sent 1000 insertsrows stored: 1000

Notice that the batch runs inside one transaction (autocommit is off, and there is a single commit()), which is both faster and safer than 1000 separate commits. The PostgreSQL driver also has a connection property, reWriteBatchedInserts, that can combine batched inserts further. Whether it helps depends on your workload, so measure it instead of assuming.

Connection pools

Opening a connection is slow: a network connection, authentication and server-side setup for every connection, and a database can serve only a limited number of them at once. A connection pool keeps a small set of open connections and lends them out. pool.getConnection() borrows one, and connection.close() does not close it: it returns it to the pool for the next caller. The pool used by Spring Boot by default is HikariCP.

Java

// File: src/main/java/com/example/jdbcdemo/PoolDemo.javapackage com.example.jdbcdemo;import com.zaxxer.hikari.HikariConfig;import com.zaxxer.hikari.HikariDataSource;import java.sql.Connection;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.SQLTransientConnectionException;import java.sql.Statement;import java.util.ArrayList;import java.util.List;import java.util.Set;import java.util.concurrent.ConcurrentHashMap;import java.util.concurrent.ExecutorService;import java.util.concurrent.Executors;import java.util.concurrent.Future;public class PoolDemo {    static HikariConfig config(int maximumPoolSize, long connectionTimeoutMillis) {        HikariConfig config = new HikariConfig();        config.setJdbcUrl(Db.url());        config.setUsername(Db.user());        config.setPassword(Db.password());        config.setMaximumPoolSize(maximumPoolSize);        config.setConnectionTimeout(connectionTimeoutMillis);        config.setPoolName("expenses-pool");        return config;    }    public static void main(String[] args) throws Exception {        // Part 1: many tasks share a few real connections.        Set<Integer> serverProcesses = ConcurrentHashMap.newKeySet();        try (HikariDataSource pool = new HikariDataSource(config(3, 5000));             ExecutorService threads = Executors.newFixedThreadPool(10)) {            List<Future<Integer>> results = new ArrayList<>();            for (int i = 0; i < 20; i++) {                results.add(threads.submit(() -> {                    try (Connection connection = pool.getConnection();     // borrow                         Statement statement = connection.createStatement();                         ResultSet rs = statement.executeQuery("SELECT pg_backend_pid(), count(*) FROM expenses")) {                        rs.next();                        serverProcesses.add(rs.getInt(1));                 // which real connection did we get?                        return rs.getInt(2);                    } catch (SQLException e) {                              // close() returns it to the pool                        throw new IllegalStateException(e);                    }                }));            }            int total = 0;            for (Future<Integer> f : results) {                total += f.get();            }            System.out.println("20 queries from 10 threads ran, each saw 4 rows: " + (total == 80));            System.out.println("used no more than the pool maximum of 3: " + (serverProcesses.size() <= 3));        }        // Part 2: a connection that is never returned (a leak) starves everyone else.        try (HikariDataSource small = new HikariDataSource(config(1, 500))) {            Connection leaked = small.getConnection();                     // borrowed, not yet returned            try {                small.getConnection();            } catch (SQLTransientConnectionException e) {                System.out.println("second borrow failed: " + e.getClass().getSimpleName());                System.out.println("message says the request timed out: " + e.getMessage().contains("request timed out"));            }            leaked.close();                                                // return it            try (Connection again = small.getConnection()) {                System.out.println("after the first connection was returned, borrowing works: " + !again.isClosed());            }        }    }}

Code

20 queries from 10 threads ran, each saw 4 rows: trueused no more than the pool maximum of 3: truesecond borrow failed: SQLTransientConnectionExceptionmessage says the request timed out: trueafter the first connection was returned, borrowing works: true

The first part runs 20 queries from 10 threads through a pool whose maximum size is 3, and no more than 3 real connections were ever used (how many were used, from one to three, varies from run to run, which is why the program prints only the guarantee). The second part is the failure that every team meets sooner or later: a connection leak. One piece of code borrowed the only connection and did not return it, so the next caller waited, then failed with SQLTransientConnectionException after the timeout (500 ms here, 30 seconds by default). Once the connection was returned, borrowing worked again.

In a real application, a leak is usually a missing close() on a path that throws, which is why try-with-resources is not optional. The symptom is exactly this: the application works for a while, then hangs under load with "Connection is not available" errors.

Pool sizing, in brief: HikariCP's default maximum is 10. A larger pool is not faster, because the database has a limited number of CPU cores and a big pool mostly adds contention. A small pool that is always busy is normal. The pool maximum should also fit the database's own connection limit, multiplied by the number of application instances. Spring Boot configures all of this from spring.datasource.* properties (article 32).

From JDBC to a repository

Writing PreparedStatement code in every part of the program is repetitive and mixes SQL into the business logic. The usual structure, which Project 2 uses, is a repository class (also called a DAO, data access object) that hides JDBC behind an interface: the rest of the program asks repository.add(...) and repository.findAll(), and only this class knows about SQL, connections and SQLException. You have already designed that interface in Project 1, which is why swapping the CSV file for PostgreSQL will not touch the service or the CLI.

JDBC's limits explain why frameworks exist: you map every row to an object by hand, write every SQL statement, and manage transactions yourself. Spring's JdbcClient and JPA/Hibernate remove different parts of that work, and later articles cover them. They sit on top of this API, so everything here still applies.

Common mistakes

Concatenating user input into SQL. Use ? parameters.
Not closing resources, which leaks connections. Use try-with-resources for the connection, statement and result set.
Hard-coding the URL, user or password in source code, or committing them to Git.
Opening a new connection for every query in a server application instead of using a pool.
Forgetting that parameter and column numbers start at 1.
Using getInt or getLong on a nullable column and treating the 0 for NULL as real data.
Using SELECT *, or reading columns by position after the query changed.
Leaving autocommit off after a transaction, so later work on a reused connection silently never commits, or skipping rollback on failure.
Catching SQLException and ignoring it, or showing its message to end users.
Holding a transaction open across slow work such as an HTTP call.

Exercises

Exercise 1

Count by category

Write static int countByCategory(Connection connection, String categoryName) that returns how many expenses a category has, using a PreparedStatement. It must be safe against input such as x' OR '1'='1.

Exercise 2: Find the problems

What is wrong with this code?

Java

static String find(String name) throws SQLException {    Connection c = Db.connect();    Statement s = c.createStatement();    ResultSet rs = s.executeQuery("SELECT description FROM expenses WHERE description = '" + name + "'");    return rs.getString(1);}

Exercise 3: Why zero?

updateAmount(connection, 999, 100) returns 0 and throws no exception. What does that mean, and what should a repository do with it?

Exercise 4: Atomic delete

A category cannot be deleted while expenses refer to it. Write deleteCategoryWithExpenses(Connection, String name) that deletes the category's expenses and then the category, as one transaction.

Exercise 5: Pool sizing

A service handles 200 requests at once, each running one 20 ms query. A teammate proposes a pool of 200 connections "so nobody waits". What would you say?

Show solutionHide solution

Java

static int countByCategory(Connection connection, String categoryName) throws SQLException {    String sql = "SELECT count(*) FROM expenses e JOIN categories c ON c.id = e.category_id WHERE c.name = ?";    try (PreparedStatement statement = connection.prepareStatement(sql)) {        statement.setString(1, categoryName);        try (ResultSet rs = statement.executeQuery()) {            rs.next();            return rs.getInt(1);        }    }}

Run on the reset data, it returned 2 for FOOD, 0 for HEALTH, and 0 for the hostile text x' OR '1'='1, which is treated as an unusual category name that does not exist.

Exercise 2

Show solutionHide solution

Several problems. SQL injection: name is concatenated into the SQL. Resource leaks: the Connection, Statement and ResultSet are never closed. The ResultSet is read without calling next(), so getString fails because the cursor is before the first row. It also returns a plain String, so "no row" has no clean way to be represented (an Optional<String> would). And Db.connect() opens a new connection for one query. The fixed version uses try-with-resources, prepareStatement with ?, if (rs.next()), and returns Optional.

Exercise 3

Show solutionHide solution

The statement was valid but matched no row, since there is no expense with id 999, so zero rows were updated. It is not a database error, so no exception is thrown. A repository should check the count and turn zero into "not found" (return false, an empty Optional, or throw its own not-found exception), because silently ignoring it hides bugs.

Exercise 4

Show solutionHide solution

Java

static void deleteCategoryWithExpenses(Connection connection, String name) throws SQLException {    boolean originalAutoCommit = connection.getAutoCommit();    connection.setAutoCommit(false);    try (PreparedStatement expenses = connection.prepareStatement(                 "DELETE FROM expenses WHERE category_id = (SELECT id FROM categories WHERE name = ?)");         PreparedStatement category = connection.prepareStatement("DELETE FROM categories WHERE name = ?")) {        expenses.setString(1, name);        expenses.executeUpdate();        category.setString(1, name);        category.executeUpdate();        connection.commit();    } catch (SQLException e) {        connection.rollback();        throw e;    } finally {        connection.setAutoCommit(originalAutoCommit);    }}

Run on the reset data, it removed the two FOOD expenses and the category: the count for FOOD became 0 and the table held 2 expenses afterwards. Both deletes happen in one transaction, so a failure of the second would also undo the first.

Exercise 5

Show solutionHide solution

A pool of 200 is almost certainly wrong. The database can only do useful work in parallel up to roughly its number of CPU cores and its disk capacity, and 200 connections add memory, scheduling and lock contention, and may exceed the server's connection limit (especially with several application instances). A small pool is enough because each query is short: with 20 ms queries, one connection can serve about 50 queries a second, so a pool of 10 serves roughly 500 per second. Requests wait briefly for a connection instead of overloading the database. Measure, set the pool size from the load test and the database limit, and set a sensible connection timeout.

Where to go next

Next: Project 2: Expense Tracker on PostgreSQL puts this code behind the ExpenseRepository interface, in layers, with tests.
Later: Dependency Injection and Inversion of Control shows how Spring supplies the DataSource and wires the repository for you.
For the persistence frameworks built on JDBC: JPA and Hibernate: The Persistence Model.

Sources & further reading

Connected lessons

References

Show all 12 →

Linked from 3 guides

Other pages in the library that point here.

JDBC: Talking to a Database from Java

For

Level

Before you start

Key takeaways

What you will learn

What JDBC is

Setting up

Connecting

Query and map rows

Types and nulls

SQL injection

Writing data

Transactions

Reading database errors

Batching

Connection pools

From JDBC to a repository

Common mistakes

Exercises

Count by category

Exercise 2: Find the problems

Exercise 3: Why zero?

Exercise 4: Atomic delete

Exercise 5: Pool sizing

Where to go next

Sources & further reading

Connected lessons

Linked from 3 guides

Keep reading

Heaps and Priority Queues

Dynamic Programming Practice: From 1D to 2D

DSA From Zero to Job-Ready: A Practical Guide with Java