Skip to content
Happy Programming Guide
Start learning
Java

Java Database Connectivity (JDBC)

Connecting to a database from Java with JDBC: prepared statements, try-with-resources, transactions, connection pooling, and when to use JPA instead.

A close-up of a printed circuit board

JDBC is the standard way Java talks to a relational database. Even if you use JPA or Spring Data, JDBC is the layer underneath, and understanding it explains a great deal of what those tools do for you. This guide covers connecting, querying safely, transactions, and the resource handling that prevents connection leaks.

Adding a driver#

XML
<dependency>
  <groupId>org.postgresql</groupId>
  <artifactId>postgresql</artifactId>
  <version>42.7.3</version>
</dependency>

Older tutorials call Class.forName("org.postgresql.Driver") first. That has not been necessary since JDBC 4 — drivers register themselves when they are on the classpath.

Connecting#

JAVA
String url = "jdbc:postgresql://localhost:5432/shop";
String user = System.getenv("DB_USER");
String password = System.getenv("DB_PASSWORD");

try (Connection conn = DriverManager.getConnection(url, user, password)) {
    System.out.println("Connected: " + conn.getCatalog());
} catch (SQLException e) {
    System.err.println("Could not connect: " + e.getMessage());
}

Credentials come from the environment, never from source. The connection URL format varies by database:

Output
jdbc:postgresql://host:5432/database
jdbc:mysql://host:3306/database
jdbc:sqlserver://host:1433;databaseName=db
jdbc:sqlite:/path/to/file.db
jdbc:h2:mem:testdb

Querying safely#

JAVA
String sql = "SELECT id, name, price FROM products WHERE price < ? AND active = ?";

try (Connection conn = DriverManager.getConnection(url, user, password);
     PreparedStatement stmt = conn.prepareStatement(sql)) {

    stmt.setBigDecimal(1, new BigDecimal("20.00"));
    stmt.setBoolean(2, true);

    try (ResultSet rs = stmt.executeQuery()) {
        while (rs.next()) {
            System.out.printf("%d %s %s%n",
                rs.getInt("id"), rs.getString("name"), rs.getBigDecimal("price"));
        }
    }
}

Parameters are numbered from 1, not 0 — a small detail that catches everyone once.

try-with-resources#

Connections, statements and result sets all hold operating system resources. Leaking them exhausts the database’s connection limit, and the symptom — an application that works for an hour and then stops — is confusing.

JAVA
// Everything in the brackets is closed automatically,
// in reverse order, whether or not an exception is thrown
try (Connection conn = dataSource.getConnection();
     PreparedStatement stmt = conn.prepareStatement(sql);
     ResultSet rs = stmt.executeQuery()) {

    while (rs.next()) { }
}

Inserting and getting the generated key#

JAVA
String sql = "INSERT INTO products (name, price) VALUES (?, ?)";

try (Connection conn = dataSource.getConnection();
     PreparedStatement stmt =
         conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {

    stmt.setString(1, "Notebook");
    stmt.setBigDecimal(2, new BigDecimal("4.99"));
    stmt.executeUpdate();

    try (ResultSet keys = stmt.getGeneratedKeys()) {
        if (keys.next()) {
            System.out.println("New id: " + keys.getLong(1));
        }
    }
}

Transactions#

JAVA
try (Connection conn = dataSource.getConnection()) {
    conn.setAutoCommit(false);

    try (PreparedStatement debit = conn.prepareStatement(
             "UPDATE accounts SET balance = balance - ? WHERE id = ?");
         PreparedStatement credit = conn.prepareStatement(
             "UPDATE accounts SET balance = balance + ? WHERE id = ?")) {

        debit.setBigDecimal(1, amount);
        debit.setLong(2, fromId);
        debit.executeUpdate();

        credit.setBigDecimal(1, amount);
        credit.setLong(2, toId);
        credit.executeUpdate();

        conn.commit();
    } catch (SQLException e) {
        conn.rollback();
        throw e;
    }
}

By default each statement commits on its own. Turning autocommit off is what makes the two updates one atomic operation — either both happen or neither does.

Batch inserts#

JAVA
try (Connection conn = dataSource.getConnection();
     PreparedStatement stmt = conn.prepareStatement(
         "INSERT INTO products (name, price) VALUES (?, ?)")) {

    conn.setAutoCommit(false);

    int count = 0;
    for (Product p : products) {
        stmt.setString(1, p.name());
        stmt.setBigDecimal(2, p.price());
        stmt.addBatch();

        if (++count % 1000 == 0) {
            stmt.executeBatch();
        }
    }
    stmt.executeBatch();
    conn.commit();
}

Inserting ten thousand rows one at a time means ten thousand round trips. Batching in groups of a thousand typically turns minutes into seconds.

Connection pooling#

Opening a connection is expensive — a network handshake and authentication. A pool keeps a set of them open and hands them out:

JAVA
HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:postgresql://localhost:5432/shop");
config.setUsername(System.getenv("DB_USER"));
config.setPassword(System.getenv("DB_PASSWORD"));
config.setMaximumPoolSize(10);
config.setConnectionTimeout(5000);

DataSource dataSource = new HikariDataSource(config);

Calling close() on a pooled connection returns it to the pool rather than closing it, so try-with-resources is still exactly right. Never use DriverManager directly in a server application.

Handling nulls#

JAVA
int quantity = rs.getInt("quantity");
if (rs.wasNull()) {
    // the column was actually NULL, not zero
}

// Cleaner for nullable numbers
Integer quantity = rs.getObject("quantity", Integer.class);

getInt returns 0 for a NULL column, which is indistinguishable from a genuine zero. getObject with a wrapper type returns null properly.

When to move up a layer#

Use When
Plain JDBC a handful of queries, or full control over SQL
Spring JdbcTemplate you want the boilerplate gone but keep the SQL
JPA / Hibernate object mapping, relationships, change tracking
jOOQ type-safe SQL checked at compile time

JPA is the default in Spring applications, but knowing JDBC underneath is what lets you diagnose a slow query or an N+1 problem when the abstraction misbehaves.

Questions people ask#

Do I still need Class.forName?

No. Drivers register automatically since JDBC 4. You will see it in older code and can safely delete it.

Why does my application run out of connections?

Somewhere a connection is not being closed. Use try-with-resources everywhere, and set a leak-detection threshold in your pool configuration to find the offending code.

Should I use Statement or PreparedStatement?

PreparedStatement, always — even without parameters. It prevents injection and the database can cache the execution plan.

How do I see the SQL that actually ran?

Turn on statement logging in the driver or the database. With Spring, set the JPA show-sql property. Reading the real SQL is the fastest way to diagnose a slow page.

Where to go next#

Hibernate, and what it does on top of JDBCRead next

Keep reading

Java

Building a Complete Java Project

Take Java from a single file to a real project: Maven, package structure, dependency injection by hand, tests, configuration, logging and a…

7 min read

Keep going — pick your next guide

The fastest way to improve is to read one guide, then build the thing it describes. Start with the basics, or jump straight to a project.

Ask a question or share what worked

Your email address will not be published. Required fields are marked *