Database Programming with JDBC

What you'll learn

Almost every serious application stores data in a database. JDBC (Java Database Connectivity) is the standard API for talking to relational databases like MySQL and PostgreSQL from Java. This module takes you from connecting to building a safe, well-structured data layer.

By the end you'll be able to:

• Understand JDBC's architecture and drivers
• Connect, run queries, and read results with ResultSet
• Perform full CRUD operations safely with PreparedStatement
• Prevent SQL injection — a critical security skill
• Use transactions with commit and rollback
• Structure data access with the DAO pattern

JDBC architecture

JDBC is a set of interfaces; a database-specific driver (a JAR you add to your project) implements them. Your code talks to the standard API, and the driver translates that into whatever the database understands — so switching databases barely changes your code:

The core objects are Connection (a session with the database), Statement/PreparedStatement (a SQL command), and ResultSet (the rows returned by a query).

Connecting & querying

You open a Connection with a JDBC URL, run a SELECT, and walk the results row by row with rs.next(). Declare everything in a try-with-resources block so connections and result sets close automatically:

import java.sql.*;

String url = "jdbc:mysql://localhost:3306/shop";

try (Connection conn = DriverManager.getConnection(url, "user", "pass");
     Statement stmt = conn.createStatement();
     ResultSet rs = stmt.executeQuery("SELECT name, price FROM products")) {

    while (rs.next()) {                         // move to the next row
        System.out.println(rs.getString("name") + ": $" + rs.getDouble("price"));
    }
} catch (SQLException e) {
    e.printStackTrace();
}

PreparedStatement & CRUD

For any query with input — and for all inserts, updates, and deletes — use a PreparedStatement. It uses ? placeholders you fill with typed setters, which is safer and faster (the database can reuse the compiled query). Use executeQuery for SELECT and executeUpdate for changes:

import java.sql.*;

String sql = "INSERT INTO users (name, email) VALUES (?, ?)";

try (PreparedStatement ps = conn.prepareStatement(sql)) {
    ps.setString(1, "Sara");                 // fill placeholders safely
    ps.setString(2, "sara@example.com");
    int rows = ps.executeUpdate();           // for INSERT/UPDATE/DELETE
    System.out.println(rows + " row inserted");
}

Preventing SQL injection

This is one of the most important security lessons in all of programming. If you build SQL by concatenating user input, an attacker can inject their own SQL and read, alter, or destroy your data. A PreparedStatement sends the input separately from the query, so it can never be interpreted as code:

String input = request.getParameter("name");

// ✗ DANGER: user input concatenated straight into SQL
String sql = "SELECT * FROM users WHERE name = '" + input + "'";
// If input is:   ' OR '1'='1
// the query becomes ...WHERE name = '' OR '1'='1'  -> returns EVERYONE

// ✓ SAFE: a PreparedStatement treats input as data, never as SQL
PreparedStatement ps =
    conn.prepareStatement("SELECT * FROM users WHERE name = ?");
ps.setString(1, input);

Transactions

Some operations must happen all together or not at all — transferring money debits one account and credits another; if the second fails, the first must be undone. A transaction groups statements: turn off auto-commit, do the work, then commit() — or rollback() everything on failure:

try {
    conn.setAutoCommit(false);        // begin a transaction

    // transfer money: two updates that must both succeed
    debit.executeUpdate();
    credit.executeUpdate();

    conn.commit();                    // make both permanent
} catch (SQLException e) {
    conn.rollback();                  // failure? undo BOTH
} finally {
    conn.setAutoCommit(true);
}

The DAO pattern

Scattering SQL throughout your app is a maintenance nightmare. The DAO (Data Access Object) pattern isolates all database code for an entity behind a clean interface — e.g. a UserDao with methods like save(user), findById(id), and findAll(). The rest of your program works with objects and methods, never raw SQL.

Recap & quick check

Excellent — your apps can now persist data to a real database. Next up: Module 24 — Networking.