JDBC

suggest change

JDBC (Java Database Connectivity) is the standard API for talking to relational databases from Java. It lives in the java.sql and javax.sql packages, which ship with the JDK. Each database vendor supplies a driver, a jar that implements the JDBC interfaces for that database's wire protocol. Your code is written against the interfaces, so the same code runs on H2, SQLite, PostgreSQL, MySQL, Oracle or SQL Server. Only the driver jar and the connection URL change (plus any SQL dialect differences).

Higher-level tools such as Spring's JdbcTemplate, jOOQ, MyBatis and Hibernate/JPA all run on top of JDBC. Knowing the plain API helps you understand and debug them.

The examples below use H2, a pure-Java database that can run in memory. You need nothing but one jar, so every example is a self-contained program:

<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <version>2.5.252</version>
</dependency>

Run any example with java -cp h2-2.5.252.jar Example.java (JDK 11+ can run a single source file directly; the examples were run on JDK 25). For database-specific features see PostgreSQL and, for a non-relational store, Redis.

The core interfaces

Type Role
DriverManager Finds a registered driver for a URL and opens a Connection
DataSource A factory for connections, usually backed by a connection pool
Connection A session with the database. Owns transactions
Statement Executes SQL text with no parameters
PreparedStatement Precompiled SQL with ? placeholders. Use this for almost everything
CallableStatement Calls stored procedures
ResultSet A cursor over the rows a query returns
SQLException The checked exception thrown by every JDBC method

Connection, Statement and ResultSet hold resources (sockets, server-side cursors, locks), so always close them, preferably with try-with-resources.

Connecting

A JDBC URL starts with jdbc:, followed by a sub-protocol that selects the driver:

Database Driver (Maven) Example URL
H2 (in memory) com.h2database:h2 jdbc:h2:mem:shop
H2 (file) com.h2database:h2 jdbc:h2:./data/shop
SQLite org.xerial:sqlite-jdbc jdbc:sqlite:shop.db
PostgreSQL org.postgresql:postgresql jdbc:postgresql://localhost:5432/shop
MySQL com.mysql:mysql-connector-j jdbc:mysql://localhost:3306/shop
SQL Server com.microsoft.sqlserver:mssql-jdbc jdbc:sqlserver://localhost:1433;databaseName=shop

Since JDBC 4.0, drivers register themselves through ServiceLoader when their jar is on the classpath. The old Class.forName("org.h2.Driver") call is no longer needed.

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.SQLException;

public class Connect {
    public static void main(String[] args) throws SQLException {
        String url = "jdbc:h2:mem:shop";
        try (Connection conn = DriverManager.getConnection(url, "sa", "")) {
            DatabaseMetaData meta = conn.getMetaData();
            System.out.println("Database: " + meta.getDatabaseProductName()
                    + " " + meta.getDatabaseProductVersion());
            System.out.println("Driver:   " + meta.getDriverName()
                    + " " + meta.getDriverVersion());
            System.out.println("JDBC:     " + meta.getJDBCMajorVersion()
                    + "." + meta.getJDBCMinorVersion());
            System.out.println("Valid:    " + conn.isValid(2));
        }
    }
}

Output:

Database: H2 2.5.252 (2026-09-23)
Driver:   H2 JDBC Driver 2.5.252 (2026-09-23)
JDBC:     4.3
Valid:    true

DatabaseMetaData describes the database and driver. isValid(timeoutSeconds) checks that the connection is still alive. An H2 mem: database disappears when its last connection closes. Add ;DB_CLOSE_DELAY=-1 to the URL to keep it alive for the life of the JVM.

Don't hard-code passwords. Read them from environment variables or a secrets manager.

Executing SQL with Statement

Statement has three execute methods:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Statements {
    public static void main(String[] args) throws SQLException {
        try (Connection conn = DriverManager.getConnection("jdbc:h2:mem:", "sa", "");
             Statement st = conn.createStatement()) {
            st.execute("""
                CREATE TABLE products (
                    id    INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
                    name  VARCHAR(100) NOT NULL,
                    price DECIMAL(10, 2) NOT NULL,
                    stock INT NOT NULL DEFAULT 0
                )
                """);

            int inserted = st.executeUpdate("""
                INSERT INTO products (name, price, stock) VALUES
                    ('Keyboard', 89.99, 10),
                    ('Mouse',    24.50, 25),
                    ('Monitor', 199.00,  3)
                """);
            System.out.println("Inserted: " + inserted);

            int updated = st.executeUpdate("UPDATE products SET stock = stock + 5 WHERE price < 100");
            System.out.println("Updated:  " + updated);

            try (ResultSet rs = st.executeQuery("SELECT id, name, price, stock FROM products ORDER BY id")) {
                while (rs.next()) {
                    System.out.printf("%d %-8s %7s %3d%n",
                            rs.getInt("id"), rs.getString("name"),
                            rs.getBigDecimal("price"), rs.getInt("stock"));
                }
            }

            boolean isQuery = st.execute("DELETE FROM products WHERE stock < 5");
            System.out.println("Returned a ResultSet: " + isQuery
                    + ", update count: " + st.getUpdateCount());
        }
    }
}

Output:

Inserted: 3
Updated:  2
1 Keyboard   89.99  15
2 Mouse      24.50  30
3 Monitor   199.00   3
Returned a ResultSet: false, update count: 1

ResultSet starts positioned before the first row. Each next() call moves to the next row and returns false when there are no more. Columns can be read by label (rs.getString("name")) or by 1-based index (rs.getString(2)). Labels are more readable and survive column reordering. Indexes are slightly faster.

Plain Statement is fine for fixed SQL like DDL. As soon as any value comes from outside your code, switch to PreparedStatement.

PreparedStatement and SQL injection

Building SQL by string concatenation lets the data change the meaning of the query:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Injection {
    static int countUnsafe(Connection conn, String name) throws SQLException {
        String sql = "SELECT COUNT(*) FROM users WHERE name = '" + name + "'";
        try (Statement st = conn.createStatement(); ResultSet rs = st.executeQuery(sql)) {
            rs.next();
            return rs.getInt(1);
        }
    }

    static int countSafe(Connection conn, String name) throws SQLException {
        String sql = "SELECT COUNT(*) FROM users WHERE name = ?";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, name);
            try (ResultSet rs = ps.executeQuery()) {
                rs.next();
                return rs.getInt(1);
            }
        }
    }

    public static void main(String[] args) throws SQLException {
        try (Connection conn = DriverManager.getConnection("jdbc:h2:mem:", "sa", "");
             Statement st = conn.createStatement()) {
            st.execute("CREATE TABLE users (name VARCHAR(50))");
            st.execute("INSERT INTO users VALUES ('alice'), ('bob'), ('carol')");

            String input = "nobody' OR '1'='1";
            System.out.println("unsafe: " + countUnsafe(conn, input));
            System.out.println("safe:   " + countSafe(conn, input));
            System.out.println("safe:   " + countSafe(conn, "O'Brien"));
        }
    }
}

Output:

unsafe: 3
safe:   0
safe:   0

The unsafe version ran SELECT COUNT(*) FROM users WHERE name = 'nobody' OR '1'='1', which matches every row. With a PreparedStatement the SQL text is sent separately from the values, so the input is always treated as data, quotes included. As a bonus, the database can cache the parsed statement and reuse it for different values.

Placeholders stand for values only. You can't use ? for a table name, column name or ORDER BY direction. If those must be dynamic, pick them from a fixed whitelist in your code.

Setting parameters, NULLs and generated keys

Parameters are set with typed setters, using 1-based indexes. Use setObject() for java.time types (LocalDate, LocalDateTime, OffsetDateTime), which JDBC 4.2 drivers support directly. You don't need the legacy java.sql.Date or java.sql.Timestamp.

To find out the id the database generated for a new row, pass Statement.RETURN_GENERATED_KEYS when preparing the statement and read getGeneratedKeys() after executing it:

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.time.LocalDate;

public class Insert {
    static long insertBook(Connection conn, String title, BigDecimal price,
                           LocalDate published, Integer pages) throws SQLException {
        String sql = "INSERT INTO books (title, price, published, pages) VALUES (?, ?, ?, ?)";
        try (PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
            ps.setString(1, title);
            ps.setBigDecimal(2, price);
            ps.setObject(3, published);           // java.time types go through setObject
            if (pages != null) {
                ps.setInt(4, pages);
            } else {
                ps.setNull(4, Types.INTEGER);     // setInt can't express NULL
            }
            ps.executeUpdate();

            try (ResultSet keys = ps.getGeneratedKeys()) {
                keys.next();
                return keys.getLong(1);
            }
        }
    }

    public static void main(String[] args) throws SQLException {
        try (Connection conn = DriverManager.getConnection("jdbc:h2:mem:", "sa", "");
             Statement st = conn.createStatement()) {
            st.execute("""
                CREATE TABLE books (
                    id        BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
                    title     VARCHAR(200) NOT NULL,
                    price     DECIMAL(10, 2) NOT NULL,
                    published DATE NOT NULL,
                    pages     INT
                )
                """);

            long a = insertBook(conn, "Effective Java", new BigDecimal("45.00"),
                    LocalDate.of(2018, 1, 6), 412);
            long b = insertBook(conn, "Java Concurrency in Practice", new BigDecimal("52.99"),
                    LocalDate.of(2006, 5, 9), null);
            System.out.println("Generated ids: " + a + ", " + b);
        }
    }
}

Output:

Generated ids: 1, 2

setInt() takes a primitive int, so it can't express NULL. Use setNull(index, Types.X), or setObject(index, null), which most drivers accept.

RETURN_GENERATED_KEYS works with every major driver. PostgreSQL and recent SQLite versions also support INSERT ... RETURNING id, which you read with executeQuery() instead.

Reading results and mapping rows to records

A common pattern is one method per query that maps each row to a record:

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;

public class Query {
    record Book(long id, String title, BigDecimal price, LocalDate published, Integer pages) {}

    static Book mapBook(ResultSet rs) throws SQLException {
        return new Book(
                rs.getLong("id"),
                rs.getString("title"),
                rs.getBigDecimal("price"),
                rs.getObject("published", LocalDate.class),
                rs.getObject("pages", Integer.class));   // null stays null
    }

    static List<Book> findCheaperThan(Connection conn, BigDecimal max) throws SQLException {
        String sql = "SELECT id, title, price, published, pages FROM books WHERE price < ? ORDER BY price";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setBigDecimal(1, max);
            try (ResultSet rs = ps.executeQuery()) {
                List<Book> result = new ArrayList<>();
                while (rs.next()) {
                    result.add(mapBook(rs));
                }
                return result;
            }
        }
    }

    static Optional<Book> findById(Connection conn, long id) throws SQLException {
        String sql = "SELECT id, title, price, published, pages FROM books WHERE id = ?";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setLong(1, id);
            try (ResultSet rs = ps.executeQuery()) {
                return rs.next() ? Optional.of(mapBook(rs)) : Optional.empty();
            }
        }
    }

    public static void main(String[] args) throws SQLException {
        try (Connection conn = DriverManager.getConnection("jdbc:h2:mem:", "sa", "");
             Statement st = conn.createStatement()) {
            st.execute("""
                CREATE TABLE books (
                    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
                    title VARCHAR(200) NOT NULL, price DECIMAL(10, 2) NOT NULL,
                    published DATE NOT NULL, pages INT)
                """);
            st.execute("""
                INSERT INTO books (title, price, published, pages) VALUES
                    ('Effective Java', 45.00, DATE '2018-01-06', 412),
                    ('Java Concurrency in Practice', 52.99, DATE '2006-05-09', NULL),
                    ('Modern Java in Action', 39.99, DATE '2018-11-15', 592)
                """);

            findCheaperThan(conn, new BigDecimal("50")).forEach(System.out::println);
            System.out.println(findById(conn, 2));
            System.out.println(findById(conn, 99));

            // Primitive getters return 0 for NULL; wasNull() tells them apart
            try (ResultSet rs = st.executeQuery("SELECT pages FROM books WHERE id = 2")) {
                rs.next();
                int pages = rs.getInt("pages");
                System.out.println("getInt: " + pages + ", wasNull: " + rs.wasNull());
            }
        }
    }
}

Output:

Book[id=3, title=Modern Java in Action, price=39.99, published=2018-11-15, pages=592]
Book[id=1, title=Effective Java, price=45.00, published=2018-01-06, pages=412]
Optional[Book[id=2, title=Java Concurrency in Practice, price=52.99, published=2006-05-09, pages=null]]
Optional.empty
getInt: 0, wasNull: true

Watch out for NULLs. getInt(), getLong(), getDouble() and getBoolean() return 0 or false for SQL NULL. Either call wasNull() right after the getter, or use getObject(column, Integer.class), which returns null.

Recommended Java types for common SQL types:

SQL type Java type Getter
INTEGER, BIGINT int, long getInt, getLong
DECIMAL, NUMERIC BigDecimal getBigDecimal
VARCHAR, TEXT String getString
BOOLEAN boolean getBoolean
DATE LocalDate getObject(col, LocalDate.class)
TIMESTAMP LocalDateTime getObject(col, LocalDateTime.class)
TIMESTAMP WITH TIME ZONE OffsetDateTime getObject(col, OffsetDateTime.class)
BLOB, BYTEA byte[] getBytes

Never use double for money. DECIMAL maps to BigDecimal, which is exact.

A small reusable query helper

Most JDBC code repeats the same steps: prepare, bind parameters, execute, loop over rows, close everything. A few lines of generic code with a lambda for the row mapping remove the repetition. This is essentially what Spring's JdbcTemplate does. ResultSetMetaData lets you handle queries whose columns you don't know in advance:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

public class Helper {
    @FunctionalInterface
    interface RowMapper<T> {
        T map(ResultSet rs) throws SQLException;
    }

    static <T> List<T> query(Connection conn, String sql, RowMapper<T> mapper, Object... params)
            throws SQLException {
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            for (int i = 0; i < params.length; i++) {
                ps.setObject(i + 1, params[i]);
            }
            try (ResultSet rs = ps.executeQuery()) {
                List<T> rows = new ArrayList<>();
                while (rs.next()) {
                    rows.add(mapper.map(rs));
                }
                return rows;
            }
        }
    }

    static int update(Connection conn, String sql, Object... params) throws SQLException {
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            for (int i = 0; i < params.length; i++) {
                ps.setObject(i + 1, params[i]);
            }
            return ps.executeUpdate();
        }
    }

    // Generic mapper: any query into a column-name -> value map
    static Map<String, Object> toMap(ResultSet rs) throws SQLException {
        ResultSetMetaData md = rs.getMetaData();
        Map<String, Object> row = new LinkedHashMap<>();
        for (int i = 1; i <= md.getColumnCount(); i++) {
            row.put(md.getColumnLabel(i), rs.getObject(i));
        }
        return row;
    }

    record City(String name, int population) {}

    public static void main(String[] args) throws SQLException {
        try (Connection conn = DriverManager.getConnection("jdbc:h2:mem:", "sa", "")) {
            update(conn, "CREATE TABLE cities (name VARCHAR(50) PRIMARY KEY, country CHAR(2), population INT)");
            update(conn, "INSERT INTO cities VALUES (?, ?, ?)", "Warsaw", "PL", 1_861_000);
            update(conn, "INSERT INTO cities VALUES (?, ?, ?)", "Krakow", "PL", 804_000);
            update(conn, "INSERT INTO cities VALUES (?, ?, ?)", "Berlin", "DE", 3_878_000);

            List<City> polish = query(conn,
                    "SELECT name, population FROM cities WHERE country = ? ORDER BY population DESC",
                    rs -> new City(rs.getString("name"), rs.getInt("population")),
                    "PL");
            System.out.println(polish);

            List<String> names = query(conn, "SELECT name FROM cities ORDER BY name",
                    rs -> rs.getString(1));
            System.out.println(names);

            query(conn, "SELECT country, COUNT(*) AS cities, SUM(population) AS total "
                    + "FROM cities GROUP BY country ORDER BY country", Helper::toMap)
                    .forEach(System.out::println);

            try (PreparedStatement ps = conn.prepareStatement("SELECT * FROM cities");
                 ResultSet rs = ps.executeQuery()) {
                ResultSetMetaData md = rs.getMetaData();
                for (int i = 1; i <= md.getColumnCount(); i++) {
                    System.out.printf("%-10s %-18s nullable=%d -> %s%n",
                            md.getColumnName(i), md.getColumnTypeName(i),
                            md.isNullable(i), md.getColumnClassName(i));
                }
            }
        }
    }
}

Output:

[City[name=Warsaw, population=1861000], City[name=Krakow, population=804000]]
[Berlin, Krakow, Warsaw]
{COUNTRY=DE, CITIES=1, TOTAL=3878000}
{COUNTRY=PL, CITIES=2, TOTAL=2665000}
NAME       CHARACTER VARYING  nullable=0 -> java.lang.String
COUNTRY    CHARACTER          nullable=1 -> java.lang.String
POPULATION INTEGER            nullable=1 -> java.lang.Integer

The RowMapper interface declares throws SQLException. You can't use java.util.function.Function here, because the ResultSet getters throw a checked exception. Use getColumnLabel() rather than getColumnName() so that AS aliases are respected. H2 reports unquoted identifiers in upper case. Other databases may use lower case.

Transactions

A new connection is in auto-commit mode: every statement is its own transaction and is committed right away. To group statements so that either all of them take effect or none do, turn auto-commit off, then call commit() or rollback():

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Transfer {
    static void transfer(Connection conn, long from, long to, BigDecimal amount) throws SQLException {
        boolean oldAutoCommit = conn.getAutoCommit();
        conn.setAutoCommit(false);
        try (PreparedStatement debit = conn.prepareStatement(
                     "UPDATE accounts SET balance = balance - ? WHERE id = ? AND balance >= ?");
             PreparedStatement credit = conn.prepareStatement(
                     "UPDATE accounts SET balance = balance + ? WHERE id = ?")) {
            debit.setBigDecimal(1, amount);
            debit.setLong(2, from);
            debit.setBigDecimal(3, amount);
            if (debit.executeUpdate() != 1) {
                throw new SQLException("Insufficient funds in account " + from);
            }

            credit.setBigDecimal(1, amount);
            credit.setLong(2, to);
            if (credit.executeUpdate() != 1) {
                throw new SQLException("No such account " + to);
            }
            conn.commit();
        } catch (SQLException e) {
            conn.rollback();
            throw e;
        } finally {
            conn.setAutoCommit(oldAutoCommit);
        }
    }

    static void printBalances(Connection conn) throws SQLException {
        try (Statement st = conn.createStatement();
             ResultSet rs = st.executeQuery("SELECT id, balance FROM accounts ORDER BY id")) {
            StringBuilder sb = new StringBuilder("  balances:");
            while (rs.next()) {
                sb.append(' ').append(rs.getLong(1)).append('=').append(rs.getBigDecimal(2));
            }
            System.out.println(sb);
        }
    }

    public static void main(String[] args) throws SQLException {
        try (Connection conn = DriverManager.getConnection("jdbc:h2:mem:", "sa", "");
             Statement st = conn.createStatement()) {
            st.execute("CREATE TABLE accounts (id BIGINT PRIMARY KEY, balance DECIMAL(12, 2) NOT NULL)");
            st.execute("INSERT INTO accounts VALUES (1, 100.00), (2, 50.00)");

            transfer(conn, 1, 2, new BigDecimal("30.00"));
            System.out.println("transfer 30 from 1 to 2: ok");
            printBalances(conn);

            try {
                transfer(conn, 1, 99, new BigDecimal("10.00"));
            } catch (SQLException e) {
                System.out.println("transfer 10 from 1 to 99: " + e.getMessage());
            }
            printBalances(conn);   // the debit was rolled back

            try {
                transfer(conn, 2, 1, new BigDecimal("500.00"));
            } catch (SQLException e) {
                System.out.println("transfer 500 from 2 to 1: " + e.getMessage());
            }
            printBalances(conn);
        }
    }
}

Output:

transfer 30 from 1 to 2: ok
  balances: 1=70.00 2=80.00
transfer 10 from 1 to 99: No such account 99
  balances: 1=70.00 2=80.00
transfer 500 from 2 to 1: Insufficient funds in account 2
  balances: 1=70.00 2=80.00

In the second transfer the debit succeeded, but the credit found no account 99. The rollback() undid the debit, so no money vanished. Restoring the old auto-commit setting in finally matters when connections come from a pool and get reused by other code.

The balance >= ? condition in the UPDATE makes the check and the change one atomic step. Reading the balance with a SELECT and then updating it would let two concurrent transfers both pass the check.

Savepoints and isolation levels

A savepoint marks a point inside a transaction that you can roll back to without abandoning the whole transaction. The isolation level controls what concurrent transactions can see of each other's uncommitted and newly committed changes:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Savepoint;
import java.sql.Statement;

public class Savepoints {
    public static void main(String[] args) throws SQLException {
        try (Connection conn = DriverManager.getConnection("jdbc:h2:mem:", "sa", "");
             Statement st = conn.createStatement()) {
            st.execute("CREATE TABLE log (msg VARCHAR(50))");

            conn.setAutoCommit(false);
            conn.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);

            st.executeUpdate("INSERT INTO log VALUES ('order created')");
            Savepoint sp = conn.setSavepoint("before_optional");
            st.executeUpdate("INSERT INTO log VALUES ('gift wrap added')");
            conn.rollback(sp);   // undo only the work after the savepoint
            st.executeUpdate("INSERT INTO log VALUES ('order paid')");
            conn.commit();

            try (ResultSet rs = st.executeQuery("SELECT msg FROM log")) {
                while (rs.next()) {
                    System.out.println(rs.getString(1));
                }
            }
            System.out.println("isolation = " + conn.getTransactionIsolation());
        }
    }
}

Output:

order created
order paid
isolation = 4

The constants are TRANSACTION_READ_UNCOMMITTED (1), TRANSACTION_READ_COMMITTED (2), TRANSACTION_REPEATABLE_READ (4) and TRANSACTION_SERIALIZABLE (8). Most databases default to READ_COMMITTED (MySQL's InnoDB defaults to REPEATABLE_READ). Not every database supports every level, so check DatabaseMetaData.supportsTransactionIsolationLevel(). Set the isolation level before the transaction starts.

Batch updates

Sending statements one at a time costs a network round trip each. addBatch() queues a set of parameters and executeBatch() sends them together. Combine this with a single transaction for bulk inserts:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Arrays;

public class Batch {
    public static void main(String[] args) throws SQLException {
        try (Connection conn = DriverManager.getConnection("jdbc:h2:mem:", "sa", "");
             Statement st = conn.createStatement()) {
            st.execute("CREATE TABLE events (id INT PRIMARY KEY, kind VARCHAR(20))");

            conn.setAutoCommit(false);
            long start = System.nanoTime();
            try (PreparedStatement ps = conn.prepareStatement("INSERT INTO events VALUES (?, ?)")) {
                for (int i = 1; i <= 10_000; i++) {
                    ps.setInt(1, i);
                    ps.setString(2, i % 3 == 0 ? "click" : "view");
                    ps.addBatch();
                    if (i % 1000 == 0) {
                        ps.executeBatch();   // flush every 1000 rows to bound memory
                    }
                }
                ps.executeBatch();           // flush the remainder (none here)
            }
            conn.commit();
            System.out.printf("Inserted 10000 rows in %d ms%n", (System.nanoTime() - start) / 1_000_000);

            try (ResultSet rs = st.executeQuery("SELECT kind, COUNT(*) FROM events GROUP BY kind ORDER BY kind")) {
                while (rs.next()) {
                    System.out.println(rs.getString(1) + ": " + rs.getInt(2));
                }
            }

            // Statement batches can mix different SQL
            st.addBatch("UPDATE events SET kind = 'tap' WHERE kind = 'click'");
            st.addBatch("DELETE FROM events WHERE id > 9990");
            int[] counts = st.executeBatch();
            conn.commit();
            System.out.println("Batch update counts: " + Arrays.toString(counts));
        }
    }
}

Output:

Inserted 10000 rows in 142 ms
click: 3333
view: 6667
Batch update counts: [3333, 10]

executeBatch() returns one update count per queued statement. A driver may return Statement.SUCCESS_NO_INFO (-2) when it knows the statement succeeded but not how many rows it touched. If one statement in a batch fails you get a BatchUpdateException. Its getUpdateCounts() shows which statements ran. Because auto-commit is off, you can roll the whole batch back.

Flush in chunks (every 500-1000 rows) so a huge batch doesn't pile up in memory. Some drivers need a URL option to really rewrite batches into multi-row inserts, for example reWriteBatchedInserts=true for PostgreSQL and rewriteBatchedStatements=true for MySQL.

Handling SQLException

SQLException carries more than a message:

JDBC 4 also defines subclasses such as SQLIntegrityConstraintViolationException, SQLSyntaxErrorException, SQLTimeoutException and SQLTransientConnectionException, which drivers throw when they can classify the error:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.SQLIntegrityConstraintViolationException;
import java.sql.SQLSyntaxErrorException;
import java.sql.Statement;

public class Errors {
    static void describe(SQLException e) {
        System.out.println(e.getClass().getSimpleName());
        System.out.println("  SQLState:   " + e.getSQLState());
        System.out.println("  error code: " + e.getErrorCode());
        System.out.println("  message:    " + e.getMessage().split(";")[0]);
    }

    public static void main(String[] args) throws SQLException {
        try (Connection conn = DriverManager.getConnection("jdbc:h2:mem:", "sa", "");
             Statement st = conn.createStatement()) {
            st.execute("CREATE TABLE users (email VARCHAR(100) PRIMARY KEY, age INT CHECK (age >= 0))");
            st.execute("INSERT INTO users VALUES ('ann@example.com', 30)");

            try (PreparedStatement ps = conn.prepareStatement("INSERT INTO users VALUES (?, ?)")) {
                ps.setString(1, "ann@example.com");
                ps.setInt(2, 31);
                ps.executeUpdate();
            } catch (SQLIntegrityConstraintViolationException e) {
                describe(e);
            }

            try {
                st.executeQuery("SELEC * FROM users");
            } catch (SQLSyntaxErrorException e) {
                describe(e);
            }

            try {
                st.executeUpdate("INSERT INTO users VALUES ('bob@example.com', -5)");
            } catch (SQLException e) {
                // SQLState class 23 = integrity constraint violation (standard across databases)
                if (e.getSQLState().startsWith("23")) {
                    System.out.println("constraint violated: " + e.getSQLState());
                } else {
                    throw e;
                }
            }
        }
    }
}

Output:

JdbcSQLIntegrityConstraintViolationException
  SQLState:   23505
  error code: 23505
  message:    Unique index or primary key violation: "PUBLIC.CONSTRAINT_4 INDEX PUBLIC.PRIMARY_KEY_4 ON PUBLIC.USERS(EMAIL) VALUES ( /* 1 */ 'ann@example.com' )"
JdbcSQLSyntaxErrorException
  SQLState:   42001
  error code: 42001
  message:    Syntax error in SQL statement "[*]SELEC * FROM users"
constraint violated: 23513

Checking the SQLState class is more portable than vendor error codes. 23505 (unique violation) is the same in H2 and PostgreSQL, for example. MySQL, however, reports duplicates as 23000. A common use is to retry a transaction when the SQLState starts with 40, which signals a deadlock or serialization failure.

Same code, different databases

Because the code only uses java.sql interfaces, switching databases means changing the URL and putting a different driver on the classpath. This program takes JDBC URLs as arguments:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Portable {
    public static void main(String[] args) throws SQLException {
        for (String url : args) {
            try (Connection conn = DriverManager.getConnection(url)) {
                System.out.println(conn.getMetaData().getDatabaseProductName()
                        + " (" + conn.getClass().getName() + ")");
                try (Statement st = conn.createStatement()) {
                    st.execute("DROP TABLE IF EXISTS notes");
                    st.execute("CREATE TABLE notes (id INTEGER PRIMARY KEY, body VARCHAR(200) NOT NULL)");
                }
                try (PreparedStatement ps = conn.prepareStatement("INSERT INTO notes VALUES (?, ?)")) {
                    String[] bodies = {"buy milk", "call mom", "write JDBC article"};
                    for (int i = 0; i < bodies.length; i++) {
                        ps.setInt(1, i + 1);
                        ps.setString(2, bodies[i]);
                        ps.addBatch();
                    }
                    ps.executeBatch();
                }
                try (PreparedStatement ps = conn.prepareStatement(
                        "SELECT id, body FROM notes WHERE body LIKE ? ORDER BY id")) {
                    ps.setString(1, "%a%");
                    try (ResultSet rs = ps.executeQuery()) {
                        while (rs.next()) {
                            System.out.println("  " + rs.getInt("id") + ": " + rs.getString("body"));
                        }
                    }
                }
            }
        }
    }
}

Run it against H2, SQLite and PostgreSQL:

$ java -cp "h2-2.5.252.jar;sqlite-jdbc-3.53.4.0.jar;postgresql-42.7.14.jar" --enable-native-access=ALL-UNNAMED \
    Portable.java jdbc:h2:mem:notes jdbc:sqlite:notes.db "jdbc:postgresql://localhost:5432/postgres?user=postgres&password=secret"
H2 (org.h2.jdbc.JdbcConnection)
  2: call mom
  3: write JDBC article
SQLite (org.sqlite.jdbc4.JDBC4Connection)
  2: call mom
  3: write JDBC article
PostgreSQL (org.postgresql.jdbc.PgConnection)
  2: call mom
  3: write JDBC article

(Use : instead of ; as the classpath separator on Linux and macOS.) DriverManager picked the right driver for each URL. The SQLite driver bundles a native library, and on JDK 24+ --enable-native-access=ALL-UNNAMED silences the warning about loading it.

The API is portable but SQL is not. Auto-increment columns (IDENTITY, AUTO_INCREMENT, SERIAL), upserts, pagination, date functions and type names all differ. Keep such code in one place, or let a library like jOOQ generate dialect-specific SQL.

DataSource and connection pooling

Opening a physical connection takes a TCP handshake, authentication and session setup, often several milliseconds. A server application should therefore use a connection pool exposed as a javax.sql.DataSource. getConnection() borrows a connection from the pool and close() returns it. HikariCP is the de facto standard (com.zaxxer:HikariCP:7.1.0):

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.Statement;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import javax.sql.DataSource;

public class Pool {
    static DataSource createDataSource() {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:h2:mem:app;DB_CLOSE_DELAY=-1");
        config.setUsername("sa");
        config.setPassword("");
        config.setMaximumPoolSize(4);
        config.setPoolName("app-pool");
        return new HikariDataSource(config);
    }

    // Code depends only on javax.sql.DataSource, not on the pool or the driver
    static int countOrders(DataSource ds) throws SQLException {
        try (Connection conn = ds.getConnection();     // borrowed from the pool
             Statement st = conn.createStatement();
             ResultSet rs = st.executeQuery("SELECT COUNT(*) FROM orders")) {
            rs.next();
            return rs.getInt(1);
        }                                              // close() returns it to the pool
    }

    public static void main(String[] args) throws Exception {
        DataSource ds = createDataSource();
        try (Connection conn = ds.getConnection(); Statement st = conn.createStatement()) {
            st.execute("CREATE TABLE orders (id INT PRIMARY KEY)");
            st.execute("INSERT INTO orders SELECT X FROM SYSTEM_RANGE(1, 42)");
        }

        try (ExecutorService pool = Executors.newVirtualThreadPerTaskExecutor()) {
            for (int i = 0; i < 100; i++) {
                pool.submit(() -> countOrders(ds));
            }
        }
        System.out.println("orders: " + countOrders(ds));
        ((HikariDataSource) ds).close();
    }
}

Output:

orders: 42

The calling code is the same as with DriverManager: get a connection, use it, close it. Only the code that builds the DataSource knows about the pool. Frameworks like Spring Boot create the DataSource from configuration and inject it. Keep the pool small: about the number of connections the database can run concurrently, not the number of threads in your application. Pool tuning and settings are covered in more detail in the PostgreSQL article.

Large results and timeouts

A few Statement settings protect your application from expensive queries:

Best practices

Feedback about page:

Feedback:
Optional: your email if you want me to get back to you:



Table Of Contents