JDBC

Reviewed & published by Brayan K

Almost every real Java app stores data in a database. By the end of this lesson you'll connect to one, run safe parameterized queries, read results, and wrap changes in transactions — the foundation every ORM is built on.

Part of the free Java course at LearnCodingFast — hands-on lessons with worked examples and the output they print, plus practice exercises and a quick quiz.

What You'll Learn in This Lesson

• Open a connection with DriverManager.getConnection
• Run parameterized queries with PreparedStatement
• Tell executeQuery and executeUpdate apart
• Walk a ResultSet row by row with next()
• Auto-close resources with try-with-resources
• Group changes into transactions with commit/rollback

1️⃣ The JDBC Flow (Plain English)

JDBC is the standard Java API for talking to a relational database. It's a small set of objects you use in order: a Connection (your session with the database), a Statement or PreparedStatement (the SQL you want to run), and a ResultSet (the rows that come back).

💡 Analogy: Using JDBC is like making a phone call. You dial (open a Connection), speak (send a query), listen (read the ResultSet), and hang up (close everything). A PreparedStatement is like a fill-in-the-blank form — you hand over the values separately so they can never be mistaken for instructions.

2️⃣ Connect and Run CRUD

You open a connection with DriverManager.getConnection(url, user, password). The JDBC URL tells Java which database to reach and how — for example jdbc:h2:mem:demo (H2 in memory) or jdbc:postgresql://localhost:5432/shop (Postgres).

Wrap the Connection, every Statement, and every ResultSet in try-with-resources (try (Connection conn = ...)). Java then closes them automatically when the block ends — even on an exception — so you never leak connections.

Two execute methods cover everything: executeQuery() for SELECT (it returns a ResultSet), and executeUpdate() for INSERT/UPDATE/DELETE (it returns an int, the row count). Read each row by looping while (rs.next()) and pulling columns with rs.getInt(...) / rs.getString(...).

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 Main {
    public static void main(String[] args) throws SQLException {
        // The JDBC URL says WHICH database to reach. Here: in-memory H2.
        String url = "jdbc:h2:mem:demo;DB_CLOSE_DELAY=-1";

        // try-with-resources auto-closes the Connection when the block ends —
        // even if an exception is thrown. No leaked connections.
        try (Connection conn = DriverManager.getConnection(url, "sa", "")) {

            // Statement is fine for fixed SQL with NO user input.
            try (Statement st = conn.createStatement()) {
                st.execute("CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, "
                    + "name VARCHAR(100), email VARCHAR(100))");
            }

            // INSERT with a PreparedStatement. The ? marks a parameter;
            // setString fills it in safely (no string concatenation).
            String insert = "INSERT INTO users (name, email) VALUES (?, ?)";
            String[][] people = {
                {"Alice", "[email protected]"},
                {"Bob", "[email protected]"},
                {"Charlie", "[email protected]"}
            };
            try (PreparedStatement ps = conn.prepareStatement(insert)) {
                for (String[] p : people) {
                    ps.setString(1, p[0]);   // first ? -> name
                    ps.setString(2, p[1]);   // second ? -> email
                    // executeUpdate returns the number of rows changed (1 here).
                    System.out.println("INSERT rows affected: " + ps.executeUpdate());
                }
            }

            // SELECT returns a ResultSet — a cursor you walk with next().
            System.out.println("SELECT ALL:");
            try (PreparedStatement ps = conn.prepareStatement(
                     "SELECT id, name, email FROM users ORDER BY id");
                 ResultSet rs = ps.executeQuery()) {     // executeQuery -> ResultSet
                while (rs.next()) {                        // true while rows remain
                    System.out.println("  id=" + rs.getInt("id")
                        + ", name=" + rs.getString("name")
                        + ", email=" + rs.getString("email"));
                }
            }

            // UPDATE one row — executeUpdate again, returns rows affected.
            try (PreparedStatement ps = conn.prepareStatement(
                     "UPDATE users SET name = ? WHERE id = ?")) {
                ps.setString(1, "Alice Smith");
                ps.setInt(2, 1);
                System.out.println("UPDATE rows affected: " + ps.executeUpdate());
            }

            // DELETE one row.
            try (PreparedStatement ps = conn.prepareStatement(
                     "DELETE FROM users WHERE id = ?")) {
                ps.setInt(1, 3);
                System.out.println("DELETE rows affected: " + ps.executeUpdate());
            }
        }
    }
}

🎯 Your Turn #1 — Insert and Read One Row

Finish the three blanks: set the third parameter, run the insert, and advance the ResultSet. The expected output is in the comments so you can check yourself.

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 Main {
    public static void main(String[] args) throws SQLException {
        // 🎯 YOUR TURN — fill in the blanks marked with ___
        String url = "jdbc:h2:mem:turn1;DB_CLOSE_DELAY=-1";
        try (Connection conn = DriverManager.getConnection(url, "sa", "")) {
            try (Statement st = conn.createStatement()) {
                st.execute("CREATE TABLE books (id INT PRIMARY KEY, title VARCHAR(80), year INT)");
            }

            // 1) Insert ONE book with a PreparedStatement.
            String sql = "INSERT INTO books (id, title, year) VALUES (?, ?, ?)";
            try (PreparedStatement ps = conn.prepareStatement(sql)) {
                ps.setInt(1, 1);
                ps.setString(2, "Effective Java");
                ___                       // 👉 set the 3rd parameter (an int) to 2018
                int rows = ps.___;        // 👉 run an INSERT — returns rows affected
                System.out.println("Inserted rows: " + rows);
            }

            // 2) Read it back and print the title.
            try (PreparedStatement ps = conn.prepareStatement("SELECT title FROM books WHERE id = ?")) {
                ps.setInt(1, 1);
                try (ResultSet rs = ps.executeQuery()) {
                    if (rs.___) {                       // 👉 advance to the first row
                        System.out.println("Title: " + rs.getString("title"));
                    }
                }
            }

            // ✅ Expected output:
            // Inserted rows: 1
            // Title: Effective Java
        }
    }
}

3️⃣ Why PreparedStatement Stops SQL Injection

SQL injection is the #1 database vulnerability in the world. It happens when you build SQL by gluing user input into the query string. A crafted input can then break out of where you intended it and rewrite the query.

❌ Vulnerable: "SELECT * FROM users WHERE name='" + input + "'"

If input is ' OR '1'='1, the WHERE clause is always true — every row leaks.

✅ Safe: "SELECT * FROM users WHERE name = ?" then ps.setString(1, input)

With a PreparedStatement the SQL is parsed before your value is supplied, so the input is bound as a single literal value. It can never become part of the query's structure — the attack just searches for a user literally named ' OR '1'='1 and finds nobody.

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 Main {
    public static void main(String[] args) throws SQLException {
        String url = "jdbc:h2:mem:sec;DB_CLOSE_DELAY=-1";
        try (Connection conn = DriverManager.getConnection(url, "sa", "")) {
            try (Statement st = conn.createStatement()) {
                st.execute("CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(50), role VARCHAR(20))");
                st.execute("INSERT INTO users VALUES "
                    + "(1,'Alice','user'),(2,'Bob','admin'),(3,'Charlie','user')");
            }

            // Pretend this came from a login form.
            String attack = "' OR '1'='1";

            // VULNERABLE: user input is glued straight into the SQL text, so the
            // quote in 'attack' ends the string and OR '1'='1' matches every row.
            System.out.println("VULNERABLE (string concatenation):");
            String evil = "SELECT * FROM users WHERE name = '" + attack + "'";
            try (Statement st = conn.createStatement();
                 ResultSet rs = st.executeQuery(evil)) {
                int n = 0;
                while (rs.next()) n++;
                System.out.println("  rows returned: " + n + "  <-- ALL data leaked!");
            }

            // SAFE: the ? is a parameter. setString passes 'attack' as a literal
            // VALUE, never as SQL, so it matches a name that is literally "' OR '1'='1".
            System.out.println("SAFE (PreparedStatement):");
            try (PreparedStatement ps = conn.prepareStatement(
                     "SELECT * FROM users WHERE name = ?")) {
                ps.setString(1, attack);
                try (ResultSet rs = ps.executeQuery()) {
                    int n = 0;
                    while (rs.next()) n++;
                    System.out.println("  rows returned: " + n + "  <-- attack neutralized");
                }
            }
        }
    }
}

4️⃣ Transactions: All or Nothing

By default JDBC auto-commits — every statement is made permanent the instant it runs. That's wrong when several changes must happen together. A bank transfer is the classic case: if the debit succeeds but the credit fails, money vanishes.

Call conn.setAutoCommit(false) to begin a transaction. Now nothing is permanent until you call conn.commit(). If anything goes wrong, conn.rollback() undoes every change since the transaction started. Put commit() at the end of the happy path and rollback() in the catch block.

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 Main {
    // Moves money so that BOTH updates happen, or NEITHER does (atomicity).
    static void transfer(Connection conn, String from, String to, int amount) throws SQLException {
        conn.setAutoCommit(false);   // begin a transaction (stop committing each statement)
        try (PreparedStatement debit = conn.prepareStatement(
                 "UPDATE accounts SET balance = balance - ? WHERE name = ?");
             PreparedStatement credit = conn.prepareStatement(
                 "UPDATE accounts SET balance = balance + ? WHERE name = ?")) {

            if (balanceOf(conn, from) < amount) throw new SQLException("Insufficient funds");

            debit.setInt(1, amount);  debit.setString(2, from);  debit.executeUpdate();
            credit.setInt(1, amount); credit.setString(2, to);   credit.executeUpdate();

            conn.commit();            // both succeeded -> make the changes permanent
            System.out.println("  COMMIT: moved $" + amount + " " + from + " -> " + to);
        } catch (SQLException e) {
            conn.rollback();          // anything failed -> undo EVERYTHING in this transaction
            System.out.println("  ROLLBACK: " + e.getMessage());
        } finally {
            conn.setAutoCommit(true); // restore the default for the next caller
        }
    }

    static int balanceOf(Connection conn, String name) throws SQLException {
        try (PreparedStatement ps = conn.prepareStatement("SELECT balance FROM accounts WHERE name = ?")) {
            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:bank;DB_CLOSE_DELAY=-1", "sa", "")) {
            try (Statement st = conn.createStatement()) {
                st.execute("CREATE TABLE accounts (name VARCHAR(20) PRIMARY KEY, balance INT)");
                st.execute("INSERT INTO accounts VALUES ('Alice', 1000), ('Bob', 500)");
            }

            System.out.println("Before: Alice=$" + balanceOf(conn, "Alice") + ", Bob=$" + balanceOf(conn, "Bob"));
            transfer(conn, "Alice", "Bob", 300);    // succeeds -> COMMIT
            System.out.println("After:  Alice=$" + balanceOf(conn, "Alice") + ", Bob=$" + balanceOf(conn, "Bob"));

            System.out.println("Attempting overdraft:");
            transfer(conn, "Alice", "Bob", 5000);   // fails the check -> ROLLBACK
            System.out.println("After:  Alice=$" + balanceOf(conn, "Alice") + ", Bob=$" + balanceOf(conn, "Bob") + " (unchanged)");
        }
    }
}

🎯 Your Turn #2 — Wrap an Update in a Transaction

Fill the four blanks: turn off auto-commit, commit on success, and roll back on failure. The expected output is in the comments.

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

public class Main {
    public static void main(String[] args) throws SQLException {
        // 🎯 YOUR TURN — fill in the blanks marked with ___
        String url = "jdbc:h2:mem:turn2;DB_CLOSE_DELAY=-1";
        try (Connection conn = DriverManager.getConnection(url, "sa", "")) {
            try (Statement st = conn.createStatement()) {
                st.execute("CREATE TABLE seats (row INT PRIMARY KEY, taken BOOLEAN)");
                st.execute("INSERT INTO seats VALUES (1, FALSE)");
            }

            conn.___;                 // 👉 turn OFF auto-commit to start a transaction
            try (PreparedStatement ps = conn.prepareStatement("UPDATE seats SET taken = TRUE WHERE row = ?")) {
                ps.setInt(1, 1);
                ps.executeUpdate();
                conn.___;             // 👉 make the change permanent
                System.out.println("Seat booked");
            } catch (SQLException e) {
                conn.___;             // 👉 undo the change if anything failed
                System.out.println("Booking failed: " + e.getMessage());
            } finally {
                conn.setAutoCommit(true);
            }

            // ✅ Expected output:
            // Seat booked
        }
    }
}

5️⃣ Connection Pooling (HikariCP)

Opening a connection is slow — a network round trip plus authentication. Doing it for every request can be 100–1000× slower than reusing one. A connection pool keeps a handful of open connections ready and lends them out, so getConnection() is nearly instant and close() just returns the connection to the pool.

HikariCP is the fastest and most popular pool — Spring Boot uses it by default. You configure a DataSource once, then your JDBC code is identical to everything above:

HikariConfig cfg = new HikariConfig();
cfg.setJdbcUrl("jdbc:postgresql://localhost:5432/shop");
cfg.setUsername("app");
cfg.setPassword(System.getenv("DB_PASSWORD")); // never hardcode!
cfg.setMaximumPoolSize(10);

HikariDataSource ds = new HikariDataSource(cfg);

// getConnection() borrows from the pool; close() returns it.
try (Connection conn = ds.getConnection()) {
    // ... same PreparedStatement / ResultSet code as before ...
}

Common Errors (and the Fix)

• ❌ SQL injection via Statement: building SQL with "... WHERE name='" + input + "'" lets input rewrite the query. Fix: use a PreparedStatement with ? and setString/setInt — never concatenate user input.
• ❌ Resource leaks: forgetting to close a Connection, Statement, or ResultSet exhausts the pool and you get "Connection is not available, request timed out". Fix: declare each in a try-with-resources so they close automatically.
• ❌ Forgetting commit / not handling transactions: after setAutoCommit(false), changes silently vanish if you never call commit(), and a half-done failure corrupts data if you never rollback(). Fix: commit on success, rollback in the catch, restore auto-commit in finally.
• ❌ N+1 queries: running one query to fetch a list, then a separate query per row inside the loop. 100 orders becomes 101 queries and the page crawls. Fix: use a single JOIN (or IN (...)) to fetch related rows in one round trip.
• ❌ Calling getString before next(): a fresh ResultSet sits before the first row, so reading a column throws "No data is available". Fix: call rs.next() first (use if for one row, while for many).

🧩 Mini-Challenge — Count Adults Safely

Now write it yourself from an outline. Create a people table, insert a few rows, then run a parameterized COUNT(*) with the age threshold bound as a parameter — never concatenated. Read the single result and print it.

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 Main {
    public static void main(String[] args) throws SQLException {
        // 🎯 MINI-CHALLENGE: count adult users safely
        // 1. Connect to "jdbc:h2:mem:chal;DB_CLOSE_DELAY=-1" (user "sa", password "").
        // 2. CREATE TABLE people (name VARCHAR(40), age INT) and insert a few rows
        //    (e.g. Alice 30, Bob 15, Charlie 42).
        // 3. Run "SELECT COUNT(*) FROM people WHERE age >= ?" with a PreparedStatement,
        //    setting the ? to 18 (NEVER concatenate it into the SQL).
        // 4. Read the single result with rs.next() then rs.getInt(1) and print it.
        //
        // ✅ Expected (with the rows above): Adults: 2

        // your code here
    }
}

📋 Quick Reference

🎉 Lesson Complete!

Great work! You can now open a connection with DriverManager, run safe parameterized queries with PreparedStatement, tell executeQuery from executeUpdate, walk a ResultSet, auto-close everything with try-with-resources, and make changes atomic with commit/rollback — plus why pooling with HikariCP matters in production.

Practice quiz

Continue this course

• Previous: I/O Streams & NIO2 for High-Performance File Handling
• Next: Building REST APIs in Java (Servlets + HTTP basics) — Handle HTTP requests and build RESTful endpoints with Java Servlets
• Quick reference: Java cheat sheet

Frequently asked questions

What is JDBC and do I still need it if I use Hibernate or JPA?

JDBC (Java Database Connectivity) is the standard Java API for talking to relational databases: you get a Connection, run SQL through a Statement or PreparedStatement, and read results from a ResultSet. Frameworks like Hibernate and JPA are built on top of JDBC, so understanding JDBC explains what runs underneath, helps you debug slow or failing queries, and lets you drop down to raw SQL when an ORM gets in the way.

What is the difference between Statement and PreparedStatement?

A Statement sends SQL as one finished string, so any user input concatenated into it can change the query's meaning — that is SQL injection. A PreparedStatement sends the SQL with ? placeholders first, then sends the values separately, so the database always treats them as data, never as code. Always use PreparedStatement for any query that includes user input; it is also faster when you run the same query many times because the database can reuse the parsed plan.

When do I use executeQuery versus executeUpdate?

Use executeQuery for SELECT statements — it returns a ResultSet you iterate with next(). Use executeUpdate for INSERT, UPDATE, and DELETE — it returns an int, the number of rows affected. If you do not know the statement type ahead of time, execute() returns a boolean telling you whether a ResultSet is available.

What does setAutoCommit(false) do, and when should I use transactions?

By default JDBC commits every statement immediately. Calling setAutoCommit(false) starts a transaction: nothing becomes permanent until you call commit(), and rollback() undoes everything since the transaction began. Use a transaction whenever two or more changes must succeed or fail together — a bank transfer (debit plus credit), placing an order plus reducing stock, and so on.

Why do I need a connection pool like HikariCP?

Opening a database connection is expensive — it involves a network round trip and authentication — and doing it per request is often 100 to 1000 times slower than reusing one. A connection pool such as HikariCP keeps a set of open connections ready and hands them out on demand, so your code calls dataSource.getConnection() (and closes it to return it to the pool). Spring Boot ships with HikariCP by default for exactly this reason.

Related lessons

• Exception Handling
• SQL