Java JDBC

ADVANCED ~8 min read Tutorial

JDBC (Java Database Connectivity) is the standard Java API for connecting to relational databases and executing SQL. With JDBC you can connect to a Postgres, MySQL, Oracle or SQL Server database, run queries and updates, read results, and manage transactions — all from pure Java, without vendor-specific native libraries.

This tutorial covers the seven core JDBC classes (DriverManager, Connection, Statement, PreparedStatement, ResultSet, CallableStatement, ResultSetMetaData), the try-with-resources pattern, parameterised queries to prevent SQL injection, and transaction management.

1. Connecting to a Database

A JDBC URL identifies the database and the driver picks up the connection:

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

String url = "jdbc:postgresql:class=class="tok-str">"tok-cmt">//localhost:class="tok-num">5432/mydb";
String user = "app";
String password = "secret";

try (Connection conn = DriverManager.getConnection(url, user, password)) {
    System.out.println("Connected, schema: " + conn.getSchema());
} catch (java.sql.SQLException e) {
    e.printStackTrace();
}

class=class="tok-str">"tok-cmt">// common URLs:
class=class="tok-str">"tok-cmt">// jdbc:postgresql://host:class="tok-num">5432/dbname       (PostgreSQL)
class=class="tok-str">"tok-cmt">// jdbc:mysql://host:class="tok-num">3306/dbname            (MySQL)
class=class="tok-str">"tok-cmt">// jdbc:h2:mem:test                         (H2 in-memory)
class=class="tok-str">"tok-cmt">// jdbc:oracle:thin:@host:class="tok-num">1521:sid          (Oracle)
class=class="tok-str">"tok-cmt">// jdbc:sqlserver://host:class="tok-num">1433;database=db   (SQL Server)

class=class="tok-str">"tok-cmt">// in-memory H2 - great for testing
try (Connection c = DriverManager.getConnection("jdbc:h2:mem:test")) {
    class=class="tok-str">"tok-cmt">// schema is created and lives as long as the connection
}

Modern code uses a DataSource rather than DriverManager — the data source implementation (often provided by the driver vendor or a pool like HikariCP) handles pooling and configuration. For tutorials and small scripts, DriverManager is fine.

2. Running a Simple Query

Use a Statement for one-off SQL with no parameters:

java
try (Connection conn = DriverManager.getConnection(url, user, pw);
     java.sql.Statement stmt = conn.createStatement();
     java.sql.ResultSet rs = stmt.executeQuery(
         "SELECT id, name, age FROM users ORDER BY name")) {

    while (rs.next()) {
        long id = rs.getLong("id");
        String name = rs.getString("name");
        int age = rs.getInt("age");
        System.out.println(id + " | " + name + " | " + age);
    }
}

For queries with parameters, always use PreparedStatement (below) instead of building SQL by concatenation. It is both safer (no SQL injection) and faster (the database can cache the parsed plan).

3. PreparedStatement — The Safe Form

A prepared statement pre-parses SQL with placeholders, then binds values safely:

java
String sql = "SELECT id, name FROM users WHERE age > ? AND name LIKE ?";

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

    class=class="tok-str">"tok-cmt">// bind parameters - class="tok-num">1-indexed
    stmt.setInt(class="tok-num">1, class="tok-num">18);
    stmt.setString(class="tok-num">2, "A%");   class=class="tok-str">"tok-cmt">// names starting with A

    try (java.sql.ResultSet rs = stmt.executeQuery()) {
        while (rs.next()) {
            System.out.println(rs.getLong("id") + " " + rs.getString("name"));
        }
    }
}

class=class="tok-str">"tok-cmt">// re-execute with different parameters - the statement is pre-compiled
class=class="tok-str">"tok-cmt">// stmt.setInt(class="tok-num">1, class="tok-num">21);
class=class="tok-str">"tok-cmt">// try (var rs = stmt.executeQuery()) { ... }
Never concatenate user input into SQL

Building SQL like "SELECT ... WHERE name = '" + input + "'" leaves you wide open to SQL injection. Always use PreparedStatement with ? placeholders. There is no excuse.

4. Reading the Results

A ResultSet is a cursor over the rows returned by a query. You advance it row by row with next() and read columns by index or name:

java
try (var rs = stmt.executeQuery("SELECT * FROM users")) {
    class=class="tok-str">"tok-cmt">// column metadata
    var meta = rs.getMetaData();
    int cols = meta.getColumnCount();
    for (int i = class="tok-num">1; i <= cols; i++) {
        System.out.print(meta.getColumnLabel(i) + "\t");
    }
    System.out.println();

    class=class="tok-str">"tok-cmt">// iterate rows
    while (rs.next()) {
        class=class="tok-str">"tok-cmt">// access by index (class="tok-num">1-indexed!)
        long id   = rs.getLong(class="tok-num">1);
        String n  = rs.getString(class="tok-num">2);
        Integer a = rs.getInt(class="tok-num">3);
        if (rs.wasNull()) a = null;   class=class="tok-str">"tok-cmt">// distinguish class="tok-num">0 from NULL
        System.out.println(id + " | " + n + " | " + a);
    }
}

For large result sets, the default cursor reads all rows into memory. Set stmt.setFetchSize() to control batching, or use a streaming cursor (ResultSet.TYPE_FORWARD_ONLY with CONCUR_READ_ONLY).

5. Insert, Update, Delete

Use executeUpdate for DML. It returns the number of affected rows:

java
class=class="tok-str">"tok-cmt">// insert with generated key
String sql = "INSERT INTO users (name, age) VALUES (?, ?)";
try (var stmt = conn.prepareStatement(sql, java.sql.Statement.RETURN_GENERATED_KEYS)) {
    stmt.setString(class="tok-num">1, "Alice");
    stmt.setInt(class="tok-num">2, class="tok-num">30);
    int affected = stmt.executeUpdate();   class=class="tok-str">"tok-cmt">// class="tok-num">1
    try (var keys = stmt.getGeneratedKeys()) {
        if (keys.next()) {
            long id = keys.getLong(class="tok-num">1);
            System.out.println("Inserted with id=" + id);
        }
    }
}

class=class="tok-str">"tok-cmt">// update
try (var stmt = conn.prepareStatement("UPDATE users SET age = ? WHERE id = ?")) {
    stmt.setInt(class="tok-num">1, class="tok-num">31);
    stmt.setLong(class="tok-num">2, class="tok-num">1);
    stmt.executeUpdate();
}

class=class="tok-str">"tok-cmt">// delete
try (var stmt = conn.prepareStatement("DELETE FROM users WHERE age < ?")) {
    stmt.setInt(class="tok-num">1, class="tok-num">18);
    int n = stmt.executeUpdate();   class=class="tok-str">"tok-cmt">// number deleted
}

For inserts that auto-generate a key, retrieve the generated key with Statement.RETURN_GENERATED_KEYS and getGeneratedKeys().

6. Transactions

By default, JDBC commits every statement immediately. To group several statements into a transaction, turn off auto-commit:

java
Connection conn = DriverManager.getConnection(url, user, pw);
try {
    conn.setAutoCommit(false);   class=class="tok-str">"tok-cmt">// start transaction

    try (var s1 = conn.prepareStatement("UPDATE accounts SET balance = balance - ? WHERE id = ?")) {
        s1.setDouble(class="tok-num">1, class="tok-num">100.0);
        s1.setLong(class="tok-num">2, fromId);
        s1.executeUpdate();
    }
    try (var s2 = conn.prepareStatement("UPDATE accounts SET balance = balance + ? WHERE id = ?")) {
        s2.setDouble(class="tok-num">1, class="tok-num">100.0);
        s2.setLong(class="tok-num">2, toId);
        s2.executeUpdate();
    }

    conn.commit();   class=class="tok-str">"tok-cmt">// both succeed - commit
} catch (Exception e) {
    conn.rollback();   class=class="tok-str">"tok-cmt">// anything fails - undo everything
    throw e;
} finally {
    conn.setAutoCommit(true);   class=class="tok-str">"tok-cmt">// restore default
    conn.close();
}
Always close in finally or try-with-resources

Connection, Statement and ResultSet all implement AutoCloseable. Wrap them in try-with-resources so they are closed even on exception — otherwise you leak database connections, which are expensive.

7. Discovering Schema with ResultSetMetaData

java
try (var stmt = conn.createStatement();
     var rs = stmt.executeQuery("SELECT * FROM users LIMIT class="tok-num">1")) {

    var meta = rs.getMetaData();
    int n = meta.getColumnCount();
    System.out.println("Columns: " + n);
    for (int i = class="tok-num">1; i <= n; i++) {
        System.out.printf("%s - %s (nullable: %s)%n",
            meta.getColumnLabel(i),
            meta.getColumnTypeName(i),
            meta.isNullable(i) == class="tok-num">1 ? "yes" : "no");
    }
}

class=class="tok-str">"tok-cmt">// database-level metadata
var dbMeta = conn.getMetaData();
System.out.println(dbMeta.getDatabaseProductName());    class=class="tok-str">"tok-cmt">// PostgreSQL
System.out.println(dbMeta.getDatabaseProductVersion()); class=class="tok-str">"tok-cmt">// class="tok-num">16.2

8. Connection Pooling in Practice

In production, never use DriverManager.getConnection() per request — opening a database connection is slow. Use a pool like HikariCP, which keeps a set of warm connections and hands them out cheaply:

java
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import javax.sql.DataSource;

HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:postgresql:class=class="tok-str">"tok-cmt">//localhost:class="tok-num">5432/mydb");
config.setUsername("app");
config.setPassword("secret");
config.setMaximumPoolSize(class="tok-num">20);
config.setMinimumIdle(class="tok-num">5);
config.setConnectionTimeout(class="tok-num">30_000);   class=class="tok-str">"tok-cmt">// 30s to obtain a connection

DataSource ds = new HikariDataSource(config);

class=class="tok-str">"tok-cmt">// borrow from the pool, return on close
try (Connection conn = ds.getConnection()) {
    class=class="tok-str">"tok-cmt">// use conn - it's actually a proxy, close() returns it to the pool
}

class=class="tok-str">"tok-cmt">// HikariCP is a runtime dependency; add to pom.xml:
class=class="tok-str">"tok-cmt">// <dependency>
class=class="tok-str">"tok-cmt">//   <groupId>com.zaxxer</groupId>
class=class="tok-str">"tok-cmt">//   <artifactId>HikariCP</artifactId>
class=class="tok-str">"tok-cmt">//   <version>class="tok-num">5.1.class="tok-num">0</version>
class=class="tok-str">"tok-cmt">// </dependency>

Exercises

  1. Connect to an in-memory H2 database, create a table users (id, name, age), insert three rows, and print them.
  2. Rewrite an insert that uses string concatenation to use PreparedStatement.
  3. Transfer money between two accounts in a single transaction, rolling back if the second update fails.
  4. Retrieve the auto-generated ID of an inserted row and print it.
  5. Use ResultSetMetaData to print the column names and types of any query result.

Related Tutorials

Continue learning Java with these adjacent tutorials. Each one builds on the previous.