JDBC — Database Access in Java

JDBC is Java’s standard API for relational databases. Each database ships a JDBC driver — a jar that implements the java.sql interfaces — and your code talks only to those interfaces. This lesson uses H2, an embedded database that runs inside the JVM, so every example is real SQL against a real database with no server to install. Examples run on JDK 21 with the source launcher, the H2 jar on the classpath, and an in-memory database that starts empty on each run.

Getting a driver and connecting

Download the H2 driver jar from Maven Central (in a Maven project you would add the com.h2database:h2 dependency instead), then open a Connection with a JDBC URL.

import java.sql.*;

public class Connect {
    public static void main(String[] args) throws SQLException {
        try (Connection conn = DriverManager.getConnection("jdbc:h2:mem:shop")) {
            DatabaseMetaData meta = conn.getMetaData();
            System.out.println(meta.getDatabaseProductName() + " " + meta.getDatabaseProductVersion());
            System.out.println("driver: " + meta.getDriverName() + ", autocommit=" + conn.getAutoCommit());
        }
    }
}

$ curl -fsSLO https://repo1.maven.org/maven2/com/h2database/h2/2.3.232/h2-2.3.232.jar
$ java -cp h2-2.3.232.jar Connect.java
H2 2.3.232 (2024-08-11)
driver: H2 JDBC Driver, autocommit=true
$ java Connect.java
Exception in thread "main" java.sql.SQLException: No suitable driver found for jdbc:h2:mem:shop
	at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:708)
	at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:253)
	at Connect.main(Connect.java:5)

DriverManager asks every driver on the classpath whether it understands the URL. Without the jar, none does — the classic “No suitable driver” error. Connections hold network or file resources, so always close them; try-with-resources does it for you. For MySQL the URL would be jdbc:mysql://localhost:3306/shop plus a user and password read from the environment.

Insert with PreparedStatement and generated keys

? placeholders are filled with typed setters. RETURN_GENERATED_KEYS hands back the ids the database assigned.

import java.sql.*;
import java.time.LocalDate;

public class Insert {
    public static void main(String[] args) throws SQLException {
        try (Connection conn = DriverManager.getConnection("jdbc:h2:mem:")) {
            conn.createStatement().execute("""
                CREATE TABLE employees (
                  id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL,
                  department VARCHAR(50) NOT NULL, salary DECIMAL(10,2) NOT NULL, hired DATE NOT NULL)""");
            String sql = "INSERT INTO employees (name, department, salary, hired) VALUES (?, ?, ?, ?)";
            try (PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
                Object[][] rows = {{"Alice", "Engineering", 95000, "2022-03-01"}, {"Bob", "Marketing", 72000, "2021-07-15"}};
                for (Object[] r : rows) {
                    ps.setString(1, (String) r[0]);
                    ps.setString(2, (String) r[1]);
                    ps.setBigDecimal(3, new java.math.BigDecimal((Integer) r[2]));
                    ps.setDate(4, Date.valueOf(LocalDate.parse((String) r[3])));
                    int affected = ps.executeUpdate();
                    try (ResultSet keys = ps.getGeneratedKeys()) {
                        keys.next();
                        System.out.println("inserted " + r[0] + ", rows=" + affected + ", id=" + keys.getInt(1));
                    }
                }
            }
        }
    }
}

$ java -cp h2-2.3.232.jar Insert.java
inserted Alice, rows=1, id=1
inserted Bob, rows=1, id=2

One PreparedStatement is reused for both rows: the SQL is parsed once and only the parameters change. executeUpdate returns the number of rows affected. Money uses DECIMAL and BigDecimal, never double.

Querying into records

executeQuery returns a ResultSet, a cursor: next() moves to the next row and returns false after the last one.

import java.sql.*;
import java.util.*;

public class Query {
    record Employee(int id, String name, String department, double salary) {}

    public static void main(String[] args) throws SQLException {
        try (Connection conn = DriverManager.getConnection("jdbc:h2:mem:")) {
            conn.createStatement().execute("CREATE TABLE employees (id INT PRIMARY KEY, name VARCHAR(100), department VARCHAR(50), salary DECIMAL(10,2))");
            conn.createStatement().execute("INSERT INTO employees VALUES (1,'Alice','Engineering',95000),(2,'Bob','Marketing',72000),(3,'Carol','Engineering',88000)");

            System.out.println(byDepartment(conn, "Engineering"));
            System.out.println(findById(conn, 2));
            System.out.println(findById(conn, 99));
        }
    }

    static List<Employee> byDepartment(Connection conn, String dept) throws SQLException {
        String sql = "SELECT id, name, department, salary FROM employees WHERE department = ? ORDER BY salary DESC";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, dept);
            try (ResultSet rs = ps.executeQuery()) {
                List<Employee> out = new ArrayList<>();
                while (rs.next()) out.add(new Employee(rs.getInt("id"), rs.getString("name"), rs.getString("department"), rs.getDouble("salary")));
                return out;
            }
        }
    }

    static Optional<String> findById(Connection conn, int id) throws SQLException {
        try (PreparedStatement ps = conn.prepareStatement("SELECT name FROM employees WHERE id = ?")) {
            ps.setInt(1, id);
            try (ResultSet rs = ps.executeQuery()) {
                return rs.next() ? Optional.of(rs.getString("name")) : Optional.empty();
            }
        }
    }
}

$ java -cp h2-2.3.232.jar Query.java
[Employee[id=1, name=Alice, department=Engineering, salary=95000.0], Employee[id=3, name=Carol, department=Engineering, salary=88000.0]]
Optional[Bob]
Optional.empty

Columns are read by name, which survives column reordering in the query. A missing row is not an error — next() just returns false — so findById maps “no row” to Optional.empty().

SQL injection: Statement vs PreparedStatement

Building SQL by concatenating input lets the input rewrite the query.

import java.sql.*;

public class Injection {
    public static void main(String[] args) throws SQLException {
        try (Connection conn = DriverManager.getConnection("jdbc:h2:mem:")) {
            conn.createStatement().execute("CREATE TABLE users (name VARCHAR(50), secret VARCHAR(50))");
            conn.createStatement().execute("INSERT INTO users VALUES ('alice','a-123'),('bob','b-456')");
            String input = "nobody' OR '1'='1";

            try (ResultSet rs = conn.createStatement().executeQuery("SELECT * FROM users WHERE name = '" + input + "'")) {
                while (rs.next()) System.out.println("Statement leaked: " + rs.getString("name") + " " + rs.getString("secret"));
            }
            try (PreparedStatement ps = conn.prepareStatement("SELECT * FROM users WHERE name = ?")) {
                ps.setString(1, input);
                try (ResultSet rs = ps.executeQuery()) {
                    System.out.println("PreparedStatement found a row? " + rs.next());
                }
            }
        }
    }
}

$ java -cp h2-2.3.232.jar Injection.java
Statement leaked: alice a-123
Statement leaked: bob b-456
PreparedStatement found a row? false

Concatenated, the input closed the string literal and added OR '1'='1', so every row matched. As a parameter, the same text is just a strange name that matches nobody. Never concatenate user input into SQL.

Update and delete: checking the row count

import java.sql.*;

public class UpdateDelete {
    public static void main(String[] args) throws SQLException {
        try (Connection conn = DriverManager.getConnection("jdbc:h2:mem:")) {
            conn.createStatement().execute("CREATE TABLE employees (id INT PRIMARY KEY, name VARCHAR(100), salary DECIMAL(10,2))");
            conn.createStatement().execute("INSERT INTO employees VALUES (1,'Alice',95000),(2,'Bob',72000)");

            try (PreparedStatement ps = conn.prepareStatement("UPDATE employees SET salary = salary * 1.10 WHERE id = ?")) {
                ps.setInt(1, 1);
                System.out.println("raise id 1: " + ps.executeUpdate() + " row(s)");
                ps.setInt(1, 42);
                System.out.println("raise id 42: " + ps.executeUpdate() + " row(s)");
            }
            try (PreparedStatement ps = conn.prepareStatement("DELETE FROM employees WHERE id = ?")) {
                ps.setInt(1, 2);
                System.out.println("delete id 2: " + ps.executeUpdate() + " row(s)");
            }
            try (ResultSet rs = conn.createStatement().executeQuery("SELECT id, name, salary FROM employees")) {
                while (rs.next()) System.out.println(rs.getInt(1) + " " + rs.getString(2) + " " + rs.getBigDecimal(3));
            }
        }
    }
}

$ java -cp h2-2.3.232.jar UpdateDelete.java
raise id 1: 1 row(s)
raise id 42: 0 row(s)
delete id 2: 1 row(s)
1 Alice 104500.00

Updating a row that doesn’t exist is not an error; the only signal is a count of 0. Check it when “not found” matters.

Transactions: all or nothing

In auto-commit mode every statement commits on its own. Turn it off to group statements, then commit() — or rollback() if anything fails.

import java.sql.*;

public class Transfer {
    public static void main(String[] args) throws SQLException {
        try (Connection conn = DriverManager.getConnection("jdbc:h2:mem:")) {
            conn.createStatement().execute("CREATE TABLE accounts (id VARCHAR(10) PRIMARY KEY, balance INT CHECK (balance >= 0))");
            conn.createStatement().execute("INSERT INTO accounts VALUES ('A', 100), ('B', 50)");
            transfer(conn, "A", "B", 30);
            transfer(conn, "A", "B", 500);
        }
    }

    static void transfer(Connection conn, String from, String to, int amount) throws SQLException {
        conn.setAutoCommit(false);
        try (PreparedStatement credit = conn.prepareStatement("UPDATE accounts SET balance = balance + ? WHERE id = ?");
             PreparedStatement debit = conn.prepareStatement("UPDATE accounts SET balance = balance - ? WHERE id = ?")) {
            credit.setInt(1, amount); credit.setString(2, to);   credit.executeUpdate();
            debit.setInt(1, amount);  debit.setString(2, from);  debit.executeUpdate();
            conn.commit();
            System.out.println("transferred " + amount);
        } catch (SQLException e) {
            conn.rollback();
            System.out.println("rolled back " + amount + ": " + e.getMessage().lines().findFirst().orElse(""));
        } finally {
            conn.setAutoCommit(true);
        }
        try (ResultSet rs = conn.createStatement().executeQuery("SELECT id, balance FROM accounts ORDER BY id")) {
            StringBuilder sb = new StringBuilder("  balances:");
            while (rs.next()) sb.append(' ').append(rs.getString(1)).append('=').append(rs.getInt(2));
            System.out.println(sb);
        }
    }
}

$ java -cp h2-2.3.232.jar Transfer.java
transferred 30
  balances: A=70 B=80
rolled back 500: Check constraint violation: "CONSTRAINT_AF: "; SQL statement:
  balances: A=70 B=80

In the second transfer the credit to B succeeded, then the debit violated the CHECK constraint. rollback() undid the credit too, so B didn’t receive money that never left A. Without the transaction, B would show 580.

Batch inserts

addBatch() queues parameter sets and executeBatch() sends them together, saving a round trip per row. Inside one transaction it is the standard way to load many rows.

import java.sql.*;

public class Batch {
    public static void main(String[] args) throws SQLException {
        try (Connection conn = DriverManager.getConnection("jdbc:h2:mem:")) {
            conn.createStatement().execute("CREATE TABLE readings (id INT PRIMARY KEY, reading DOUBLE)");
            conn.setAutoCommit(false);
            try (PreparedStatement ps = conn.prepareStatement("INSERT INTO readings VALUES (?, ?)")) {
                for (int i = 1; i <= 1000; i++) {
                    ps.setInt(1, i);
                    ps.setDouble(2, i * 0.5);
                    ps.addBatch();
                }
                int[] counts = ps.executeBatch();
                conn.commit();
                System.out.println("batch statements: " + counts.length + ", rows in first: " + counts[0]);
            }
            try (ResultSet rs = conn.createStatement().executeQuery("SELECT COUNT(*), SUM(reading) FROM readings")) {
                rs.next();
                System.out.println("count=" + rs.getInt(1) + " sum=" + rs.getDouble(2));
            }
        }
    }
}

$ java -cp h2-2.3.232.jar Batch.java
batch statements: 1000, rows in first: 1
count=1000 sum=250250.0

executeBatch returns one update count per queued parameter set. With an embedded database the speed-up is small; against a networked database such as MySQL or PostgreSQL, batching avoids a network round trip per row and is often many times faster.

Reading SQLException

SQLException carries a vendor error code and a standard five-character SQLState you can branch on.

import java.sql.*;

public class Errors {
    public static void main(String[] args) throws SQLException {
        try (Connection conn = DriverManager.getConnection("jdbc:h2:mem:")) {
            conn.createStatement().execute("CREATE TABLE users (email VARCHAR(100) PRIMARY KEY)");
            conn.createStatement().execute("INSERT INTO users VALUES ('a@example.com')");
            for (String sql : new String[] {"INSERT INTO users VALUES ('a@example.com')", "SELECT nope FROM users"}) {
                try {
                    conn.createStatement().execute(sql);
                } catch (SQLException e) {
                    System.out.println(e.getClass().getSimpleName() + " state=" + e.getSQLState() + " code=" + e.getErrorCode());
                }
            }
        }
    }
}

$ java -cp h2-2.3.232.jar Errors.java
JdbcSQLIntegrityConstraintViolationException state=23505 code=23505
JdbcSQLSyntaxErrorException state=42S22 code=42122

SQLState class 23 means a constraint violation (23505 is “unique violation” on every major database), and 42 a syntax or naming error. Branching on getSQLState() keeps error handling portable; error codes are vendor-specific.

Practice

1. Add a salary >= 0 check before inserting, and show that a negative salary inserts no row.

import java.sql.*;

public class Validate {
    static void addEmployee(Connection conn, String name, int salary) throws SQLException {
        if (salary < 0) throw new IllegalArgumentException("negative salary for " + name);
        try (PreparedStatement ps = conn.prepareStatement("INSERT INTO employees (name, salary) VALUES (?, ?)")) {
            ps.setString(1, name);
            ps.setInt(2, salary);
            ps.executeUpdate();
        }
    }

    public static void main(String[] args) throws SQLException {
        try (Connection conn = DriverManager.getConnection("jdbc:h2:mem:")) {
            conn.createStatement().execute("CREATE TABLE employees (name VARCHAR(50), salary INT)");
            addEmployee(conn, "Alice", 95000);
            try { addEmployee(conn, "Mallory", -1); } catch (IllegalArgumentException e) { System.out.println("rejected: " + e.getMessage()); }
            try (ResultSet rs = conn.createStatement().executeQuery("SELECT COUNT(*) FROM employees")) {
                rs.next();
                System.out.println("rows: " + rs.getInt(1));
            }
        }
    }
}

$ java -cp h2-2.3.232.jar Validate.java
rejected: negative salary for Mallory
rows: 1

2. Write findByNamePrefix(conn, "Al") using LIKE ? with a parameter, and test it with the input "%". Should % match everything?

import java.sql.*;
import java.util.*;

public class Prefix {
    static List<String> findByNamePrefix(Connection conn, String prefix) throws SQLException {
        String escaped = prefix.replace("\\", "\\\\").replace("%", "\\%").replace("_", "\\_");
        try (PreparedStatement ps = conn.prepareStatement("SELECT name FROM people WHERE name LIKE ? ESCAPE '\\' ORDER BY name")) {
            ps.setString(1, escaped + "%");
            try (ResultSet rs = ps.executeQuery()) {
                List<String> out = new ArrayList<>();
                while (rs.next()) out.add(rs.getString(1));
                return out;
            }
        }
    }

    public static void main(String[] args) throws SQLException {
        try (Connection conn = DriverManager.getConnection("jdbc:h2:mem:")) {
            conn.createStatement().execute("CREATE TABLE people (name VARCHAR(50))");
            conn.createStatement().execute("INSERT INTO people VALUES ('Alice'),('Alan'),('Bob'),('50% off')");
            System.out.println(findByNamePrefix(conn, "Al"));
            System.out.println(findByNamePrefix(conn, "%"));
            System.out.println(findByNamePrefix(conn, "50%"));
        }
    }
}

$ java -cp h2-2.3.232.jar Prefix.java
[Alan, Alice]
[]
[50% off]

3. Make the transfer roll back when the to account doesn’t exist (update count 0), even though no SQLException is thrown.

import java.sql.*;

public class MissingAccount {
    public static void main(String[] args) throws SQLException {
        try (Connection conn = DriverManager.getConnection("jdbc:h2:mem:")) {
            conn.createStatement().execute("CREATE TABLE accounts (id VARCHAR(10) PRIMARY KEY, balance INT)");
            conn.createStatement().execute("INSERT INTO accounts VALUES ('A', 100)");
            conn.setAutoCommit(false);
            try (PreparedStatement debit = conn.prepareStatement("UPDATE accounts SET balance = balance - 30 WHERE id = 'A'");
                 PreparedStatement credit = conn.prepareStatement("UPDATE accounts SET balance = balance + 30 WHERE id = ?")) {
                debit.executeUpdate();
                credit.setString(1, "ZZZ");
                if (credit.executeUpdate() == 0) throw new SQLException("no such account ZZZ");
                conn.commit();
            } catch (SQLException e) {
                conn.rollback();
                System.out.println("rolled back: " + e.getMessage());
            }
            try (ResultSet rs = conn.createStatement().executeQuery("SELECT balance FROM accounts WHERE id = 'A'")) {
                rs.next();
                System.out.println("A = " + rs.getInt(1));
            }
        }
    }
}

$ java -cp h2-2.3.232.jar MissingAccount.java
rolled back: no such account ZZZ
A = 100

4. Insert 2,000 rows once with executeUpdate per row and once with a batch, each in its own transaction, and time both.

import java.sql.*;

public class BatchTiming {
    public static void main(String[] args) throws SQLException {
        try (Connection conn = DriverManager.getConnection("jdbc:h2:mem:")) {
            conn.createStatement().execute("CREATE TABLE t (id INT PRIMARY KEY)");
            conn.setAutoCommit(false);
            for (boolean batch : new boolean[] {false, true}) {
                conn.createStatement().execute("DELETE FROM t");
                long start = System.nanoTime();
                try (PreparedStatement ps = conn.prepareStatement("INSERT INTO t VALUES (?)")) {
                    for (int i = 0; i < 2000; i++) {
                        ps.setInt(1, i);
                        if (batch) ps.addBatch(); else ps.executeUpdate();
                    }
                    if (batch) ps.executeBatch();
                }
                conn.commit();
                System.out.println((batch ? "batch:      " : "row by row: ") + (System.nanoTime() - start) / 1_000_000 + " ms (varies)");
            }
        }
    }
}

$ java -cp h2-2.3.232.jar BatchTiming.java
row by row: 63 ms (varies)
batch:      13 ms (varies)