Skip to content

Expert · JDBC, Maven, Gradle and JUnit · topic 46 of 60 · 30 min

JDBC: talking to a database

Explanation

JDBC (Java Database Connectivity) is the standard API for SQL databases. You get a Connection (from DriverManager or, in real apps, a connection pool), run SQL with a PreparedStatement and ? placeholders, read rows from a ResultSet, and close everything with try-with-resources. Group related changes in a transaction with commit and rollback.

Why JDBC?

Almost every business application stores data in a relational database such as PostgreSQL, MySQL or Oracle. JDBC is the low-level standard API all Java database tools build on, including JPA and Spring Data (module 11). Each database provides a driver (a JAR) that implements JDBC for it.

Connecting and querying

java
import java.sql.*;

String url = "jdbc:postgresql://localhost:5432/shop";      // MySQL: jdbc:mysql://localhost:3306/shop
String user = System.getenv("DB_USER");                     // never hard-code credentials
String pass = System.getenv("DB_PASSWORD");

String sql = "SELECT id, name, city FROM customers WHERE city = ? ORDER BY name";
try (Connection conn = DriverManager.getConnection(url, user, pass);
     PreparedStatement ps = conn.prepareStatement(sql)) {
    ps.setString(1, "Pune");                                 // fill the first ? (1-based)
    try (ResultSet rs = ps.executeQuery()) {
        while (rs.next()) {                                  // move to the next row
            System.out.println(rs.getInt("id") + " " + rs.getString("name"));
        }
    }
} catch (SQLException e) {
    System.out.println("Database error: " + e.getMessage());
}

executeQuery is for SELECT and returns a ResultSet. executeUpdate is for INSERT, UPDATE and DELETE and returns the number of rows changed. Parameter positions start at 1, not 0.

java
String insert = "INSERT INTO orders (customer_id, amount, status) VALUES (?, ?, ?)";
try (PreparedStatement ps = conn.prepareStatement(insert)) {
    ps.setInt(1, 1);
    ps.setLong(2, 49_900);
    ps.setString(3, "PAID");
    int rows = ps.executeUpdate();     // 1
}

Transactions

A transaction groups several statements so they all succeed or all fail. Moving money must debit one account AND credit another; if the credit fails, the debit must be undone. By default each statement commits on its own (auto-commit). Turn it off, commit at the end, and roll back on error.

java
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.setLong(1, amount); debit.setLong(2, fromId); debit.setLong(3, amount);
    if (debit.executeUpdate() != 1) throw new SQLException("Insufficient balance");
    credit.setLong(1, amount); credit.setLong(2, toId);
    credit.executeUpdate();
    conn.commit();                  // both changes become permanent together
} catch (SQLException e) {
    conn.rollback();                // undo everything since the last commit
    throw e;
}

Connection pools

Opening a database connection takes time (network handshake, authentication). Real applications use a connection pool, which keeps a set of open connections and lends them out. HikariCP is the most common pool and the default in Spring Boot. Code asks the pool (a DataSource) for a connection and "closing" it returns it to the pool.

Careful: Forgetting to close Connection, Statement and ResultSet objects leaks connections until the pool is exhausted and the app hangs. Always use try-with-resources. And never put passwords in source code or Git; read them from environment variables or a secrets manager.
Think of it like this: JDBC is like a phone line to the database: the Connection is the call, the PreparedStatement is the question you read out with blanks (?) for details, and the ResultSet is the list of answers you note down one line at a time. A connection pool is like the office having a few phone lines always open instead of dialling fresh every time.
Tip: Try the SQL in the tasks below; they run here on SQLite, whose SELECT syntax matches what you would send through JDBC to PostgreSQL or MySQL for these simple queries.

Need help?

Stuck on a word or an idea? Search this course, or every course, and jump straight to the explanation or a worked question.

Try:

Watch the lesson

JDBC: talking to a database · 1/6

JDBC

In this lesson you will connect Java to a SQL database with JDBC.

Prefer a video by a teacher? Find free videos on “java jdbc tutorial preparedstatement resultset transaction” on YouTube ↗

In real life: Loading a customer's orders

An order-history screen in a shopping app runs one parameterised query that joins customers and orders for the logged-in user and maps each row into a Java record.

java
record OrderRow(int id, long amount, String status) {}
List<OrderRow> orders = new ArrayList<>();
String sql = "SELECT id, amount, status FROM orders WHERE customer_id = ? ORDER BY id DESC";
try (Connection c = dataSource.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) {
    ps.setInt(1, customerId);
    try (ResultSet rs = ps.executeQuery()) {
        while (rs.next()) orders.add(new OrderRow(rs.getInt("id"), rs.getLong("amount"), rs.getString("status")));
    }
}

Practice (0/3 done)

Task 1 · Code · SQL

The query a Java app would send through JDBC: list the names of customers from Pune, sorted by name.

Task 2 · Code · SQL

Show each customer's name with the total amount of their PAID orders, only for customers who have paid orders, highest total first. (A Java report screen would run this with JDBC.)

Task 3 · Question

Which JDBC method runs an UPDATE statement and returns the number of changed rows?

Quick quiz

Question 1

1.In ps.setString(1, "Pune"), what does 1 mean?

Question 2

2.What does rs.next() do?

Question 3

3.Why use a transaction for a money transfer?

Question 4

4.What is HikariCP?

Read more (free)

Open a question to see the answer and why. The first ones are this topic's own quiz, so try the quiz above before you peek.

In ps.setString(1, "Pune"), what does 1 mean?

Answer: The first ? placeholder (parameters are 1-based)

JDBC parameter indexes start at 1.

What does rs.next() do?

Answer: Moves to the next row and returns false when there are no more

Loop while rs.next() is true.

Why use a transaction for a money transfer?

Answer: So the debit and credit succeed or fail together

Atomicity: all or nothing.

What is HikariCP?

Answer: A JDBC connection pool

It is the default pool in Spring Boot.

What happens if you forget rows.Close()?Related

Answer: Connections leak and the pool can run out, making requests hang

Open Rows hold a connection.

From Databases with database/sql

Inside a transaction you run SAVEPOINT s1, then an UPDATE, then ROLLBACK TO s1. What happens?Related

Answer: Only the work after s1 is undone, and the transaction stays open

ROLLBACK TO undoes back to the marker and keeps the earlier work pending.

From Transactions: all or nothing

Why should you not send an SMS from inside an open transaction?Related

Answer: The slow call keeps locks held and makes other users wait; and the SMS cannot be rolled back

Transactions should be short. Outside calls are slow, and an SMS already sent cannot be undone by ROLLBACK.

From Transactions and ACID

What is the main fix for SQL injection in Java?Related

Answer: PreparedStatement with parameters

Parameters are the correct default.

From SQL injection and safe queries

Pass the quiz (60% or more) or mark this topic as done when you are ready.

Your progress is saved on this device only.

Next: SQL injection and safe queries →

Next steps

  1. 4 more topics in “JDBC, Maven, Gradle and JUnit” before its exam: SQL injection and safe queries, Maven: building and dependencies, Gradle in brief, Unit testing with JUnit.
  2. Module 10 exam: JDBC, Maven, Gradle and JUnit · 12 questions · 25 min, without help. 5 topics of this module still to finish first.
  3. Then the next module, Spring Boot basics. 60 topics left in the course before the final exam.

← Previous: Virtual threads (Java 21)