Skip to content
javahacks.dev
Hibernate

JDBC Tutorial How to Connect to a Database Step by Step

Connect to PostgreSQL with JDBC, run queries and updates with prepared statements, read results safely and manage transactions, in one runnable example.

6 min read
Summarize in AI
Flat illustration of an application reaching a database through a JDBC driver, with the PostgreSQL, OpenJDK, Flyway and MariaDB logos.

Every Java database library, from Hibernate to Spring's JdbcClient, sits on JDBC. Knowing the layer directly makes their behaviour and their errors easier to read: a "connection not available" message or a surprise rollback stops being mysterious once you have written the connection handling yourself. This tutorial connects to PostgreSQL 17, creates a table, inserts and queries rows and runs a transaction, on JDK 25 with the PostgreSQL driver. The mapping layer built on top is covered in the Hibernate and JPA guide.

Start a database and add the driver

Shell
docker run -d --name jdbc-demo -p 5432:5432 \
  -e POSTGRES_USER=demo -e POSTGRES_PASSWORD=change-me -e POSTGRES_DB=demo postgres:17
pom.xmlXML
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.7.4</version>
</dependency>

The container gives you a throwaway database with a known user and password, and docker rm -f jdbc-demo removes it afterwards. The driver is a normal dependency; JDBC 4 drivers register themselves, so no Class.forName call is needed. If the connection later fails, the usual causes are covered in connection refused and no suitable driver.

Connect

src/main/java/dev/javahacks/jdbc/Demo.javajava
package dev.javahacks.jdbc;

import java.sql.*;

public class Demo {

    static final String URL = "jdbc:postgresql://localhost:5432/demo";

    public static void main(String[] args) throws SQLException {
        try (Connection conn = DriverManager.getConnection(URL, "demo", "change-me")) {
            System.out.println("Connected to " + conn.getMetaData().getDatabaseProductName());
        }
    }
}

DriverManager picks the registered driver from the URL prefix jdbc:postgresql:. Try-with-resources closes the connection when the block ends, on success and on exceptions alike. The program prints Connected to PostgreSQL. In an application, never call DriverManager per request: opening a connection involves a network round trip, authentication and server-side setup, so use a pooled DataSource such as HikariCP, which Spring Boot configures for you. The tutorial uses DriverManager only because it is the shortest path to a first connection.

Create a table and insert

java
try (Connection conn = DriverManager.getConnection(URL, "demo", "change-me");
     Statement st = conn.createStatement()) {

    st.execute("create table if not exists product (id serial primary key, name text not null, price_cents bigint not null)");

    try (PreparedStatement ps = conn.prepareStatement(
            "insert into product (name, price_cents) values (?, ?)")) {
        ps.setString(1, "Espresso machine");
        ps.setLong(2, 24_900);
        int rows = ps.executeUpdate();
        System.out.println(rows + " row inserted");
    }
}

The ? placeholders take values through typed setters, indexed from 1. The output is 1 row inserted. Never build the SQL by joining strings with user input; the placeholder is what separates data from code and prevents injection, a topic expanded in preventing SQL injection. Statement runs fixed SQL such as DDL. PreparedStatement is for anything with values, and its differences are covered in Statement versus PreparedStatement. Money is stored as integer cents here for a reason: a double cannot represent 0.10 exactly.

Query

java
try (Connection conn = DriverManager.getConnection(URL, "demo", "change-me");
     PreparedStatement ps = conn.prepareStatement(
             "select id, name, price_cents from product where price_cents < ? order by price_cents")) {

    ps.setLong(1, 50_000);
    try (ResultSet rs = ps.executeQuery()) {
        while (rs.next()) {
            long id = rs.getLong("id");
            String name = rs.getString("name");
            long price = rs.getLong("price_cents");
            System.out.printf("%d %s %d%n", id, name, price);
        }
    }
}

rs.next() advances to the next row and returns false at the end. Read columns by name or by index, which starts at 1. With the single row inserted above, the loop prints 1 Espresso machine 24900. For a nullable numeric column, rs.getLong returns 0 for NULL; call rs.wasNull() right after, or read the column with rs.getObject("col", Long.class) to get a real null. If you run the insert twice, the query prints two rows with different ids, which shows the serial column at work.

Transactions

By default each statement commits as soon as it runs. To make several statements one unit, turn auto-commit off and decide the outcome yourself.

java
try (Connection conn = DriverManager.getConnection(URL, "demo", "change-me")) {
    conn.setAutoCommit(false);
    try {
        debit(conn, 1, 1_000);
        credit(conn, 2, 1_000);
        conn.commit();
    } catch (SQLException e) {
        conn.rollback();
        throw e;
    }
}

If either call fails, rollback() undoes both, and the money neither vanishes nor appears twice. Hold a transaction for as short a time as possible and never call a remote service inside it: the transaction keeps a pooled connection busy while it waits, which is how a slow dependency empties a pool, a failure described in connection timeouts. A connection returned to a pool should have its auto-commit mode restored; pools usually do this, but custom code should not assume it.

Errors

JDBC throws the checked SQLException. Its getSQLState() and getErrorCode() classify the failure: state 23505 is a unique violation in PostgreSQL, and 40001 is a serialisation failure that is safe to retry. Branch on the state, not on message text, because messages change between driver versions and languages. A constraint failure is explained in integrity violations.

Things that go wrong in a first program

Forgetting to close a result set or statement leaks cursors on the server, and the leak appears only under load. Mixing setAutoCommit(false) with a missing commit makes writes disappear when the connection closes, with no error. And reading a column index that is off by one throws SQLException: The column index is out of range, because JDBC counts from one. Run the examples with a small main first, read the exceptions as they come and add the pool afterwards.

From DriverManager to a pool

The change to a pooled setup is small and worth making early. Create a HikariDataSource, set the URL, user, password and a maximum pool size, and call getConnection() on it instead of DriverManager. Closing a pooled connection returns it to the pool rather than closing the socket, so the try-with-resources blocks above stay exactly as they are. Choose the pool size from the database's capacity and the number of application instances, not from the number of threads; a pool of ten is a good starting point for a small service, and sizing it is a calculation rather than a guess.

A second improvement is to extract the connection details into configuration. Read the URL, user and password from environment variables or a properties file kept out of version control. The demo above has a password in the source file for brevity, which is acceptable for a disposable container and wrong for anything shared.

Checking your understanding

Change the program so the transfer fails halfway, by throwing an exception after the debit, and confirm that the row count is unchanged afterwards. Then remove the rollback() call and see what the next connection observes, which depends on the driver closing the connection with an open transaction. Doing this once on purpose teaches more about transactions than reading about them, and it gives you the confidence to read the logs when a real transfer fails.

Reading the JDBC log of a failing query

When a statement fails, the SQLException carries the vendor message, the SQL state and often the position of the error in the statement. Print all three, and log the SQL text with placeholders, not the values. The cause chain can hold a second exception from the driver, so call getNextException() or walk getCause() as well. Two minutes of reading that output usually names the missing column or the wrong type.

Where this tutorial stops

Real code wraps this in a repository class, uses a pool and often a helper such as Spring's JdbcClient to remove the boilerplate. The primary sources are the Java JDBC tutorial and the PostgreSQL JDBC documentation.

Also inJava Core
Share

Keep reading

Get new posts by email

We send one email when a new post is published. No spam.

Unsubscribe in one click. No spam, ever.