Lesson 34 • Advanced

JDBC – Database Connectivity

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.

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

Prerequisites: You should be comfortable with Exception Handling (especially try-with-resources for auto-closing) and have some basic SQL. Every example below uses H2, an in-memory database, so they run without installing a server.

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.

Object	Role	Key insight
Connection	A session with the database	Expensive to open — reuse via a pool
PreparedStatement	A parameterized query	Always use this for user input
ResultSet	A cursor over result rows	Call next() to advance, forward-only
DriverManager	Hands you a Connection from a URL	Driver auto-loaded since Java 6

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(...).

Worked Example: JDBC CRUD with PreparedStatement

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", "alice@test.com"},
                {"Bob", "bob@test.com"},
                {"Charlie", "charlie@test.com"}
            };
            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());
            }
        }
    }
}

Output

INSERT rows affected: 1
INSERT rows affected: 1
INSERT rows affected: 1
SELECT ALL:
  id=1, name=Alice, email=alice@test.com
  id=2, name=Bob, email=bob@test.com
  id=3, name=Charlie, email=charlie@test.com
UPDATE rows affected: 1
DELETE rows affected: 1

This is real code — run it for free atonecompiler.com/javaor in your own editor.

🎯 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.

Your Turn: Fill in the Blanks

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
        }
    }
}

This is real code — run it for free atonecompiler.com/javaor in your own editor.

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.

Worked Example: SQL Injection Attack & Defense

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");
                }
            }
        }
    }
}

Output

VULNERABLE (string concatenation):
  rows returned: 3  <-- ALL data leaked!
SAFE (PreparedStatement):
  rows returned: 0  <-- attack neutralized

This is real code — run it for free atonecompiler.com/javaor in your own editor.

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.

Worked Example: Transactions (commit & rollback)

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)");
        }
    }
}

Output

Before: Alice=$1000, Bob=$500
  COMMIT: moved $300 Alice -> Bob
After:  Alice=$700, Bob=$800
Attempting overdraft:
  ROLLBACK: Insufficient funds
After:  Alice=$700, Bob=$800 (unchanged)

This is real code — run it for free atonecompiler.com/javaor in your own editor.

🎯 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.

Your Turn: Transaction Control

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
        }
    }
}

This is real code — run it for free atonecompiler.com/javaor in your own editor.

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.

Mini-Challenge: Your Code Here

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
    }
}

This is real code — run it for free atonecompiler.com/javaor in your own editor.

📋 Quick Reference

Task	API	Returns
Open a connection	DriverManager.getConnection(url, user, pass)	Connection
Parameterized query	conn.prepareStatement(sql)	PreparedStatement
Bind a value	ps.setString(1, v) / ps.setInt(2, n)	void
Run a SELECT	ps.executeQuery()	ResultSet
Run INSERT/UPDATE/DELETE	ps.executeUpdate()	int (rows affected)
Advance / read a row	rs.next() / rs.getInt(col)	boolean / value
Begin a transaction	conn.setAutoCommit(false)	void
Finish a transaction	conn.commit() / conn.rollback()	void

❓ 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.

🎉 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.

Next up: REST APIs — exposing this data layer as web services with Spring Boot.

JDBC – Database Connectivity

What You'll Learn in This Lesson

1️⃣ The JDBC Flow (Plain English)

2️⃣ Connect and Run CRUD

🎯 Your Turn #1 — Insert and Read One Row

3️⃣ Why PreparedStatement Stops SQL Injection

4️⃣ Transactions: All or Nothing

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

5️⃣ Connection Pooling (HikariCP)

Common Errors (and the Fix)

🧩 Mini-Challenge — Count Adults Safely

📋 Quick Reference

❓ Frequently Asked Questions

🎉 Lesson Complete!

Cookie & Privacy Settings