Lesson 21 — JDBC

Database Connectivity

JDBC (Java Database Connectivity) is the Java API for connecting to relational databases. It provides a standard interface so the same Java code can connect to MySQL, PostgreSQL, SQL Server, or SQLite with minimal changes.

The JDBC Architecture

JDBC works through a driver model. Each database vendor provides a JDBC driver — a library that implements the JDBC interfaces for their specific database protocol. Your code uses only the JDBC interfaces; the driver handles the network communication and SQL translation.

  1. Add the JDBC driver JAR to your classpath (or Maven/Gradle dependency).
  2. Get a Connection using a connection string (URL, username, password).
  3. Create a PreparedStatement with parameterised SQL.
  4. Bind parameter values and execute.
  5. Process the ResultSet.
  6. Close everything (use try-with-resources).

Connecting to a Database

JAVA — Connecting 
import java.sql.*;

// Connection strings for common databases:
// MySQL:      "jdbc:mysql://localhost:3306/your-it-tutor"
// PostgreSQL: "jdbc:postgresql://localhost:5432/your-it-tutor"
// SQL Server: "jdbc:sqlserver://localhost:1433;databaseName=your-it-tutor"
// SQLite:     "jdbc:sqlite:your-it-tutor.db"

String url      = "jdbc:mysql://localhost:3306/your-it-tutor";
String username = "root";
String password = System.getenv("DB_PASSWORD"); // never hardcode passwords

try (Connection conn = DriverManager.getConnection(url, username, password)) {

    System.out.println("Connected to: " + conn.getMetaData().getURL());
    System.out.println("Database:     " + conn.getCatalog());

} catch (SQLException e) {
    System.out.println("Connection failed: " + e.getMessage());
    System.out.println("SQL State: " + e.getSQLState());
    System.out.println("Error code: " + e.getErrorCode());
}

Querying Data (SELECT)

JAVA — SELECT 
String sql = "SELECT learner_id, full_name, course, mark " +
            "FROM learners " +
            "WHERE city = ? AND mark >= ? " +
            "ORDER BY mark DESC";

try (Connection conn = DriverManager.getConnection(url, username, password);
     PreparedStatement ps = conn.prepareStatement(sql)) {

    // Bind parameters — indexes start at 1
    ps.setString(1, "Johannesburg");
    ps.setDouble(2, 50.0);

    try (ResultSet rs = ps.executeQuery()) {

        System.out.printf("%-5s %-20s %-10s %s%n", "ID", "Name", "Course", "Mark");
        System.out.println("-".repeat(50));

        while (rs.next()) {
            int    id     = rs.getInt("learner_id");
            String name   = rs.getString("full_name");
            String course = rs.getString("course");
            double mark   = rs.getDouble("mark");

            System.out.printf("%-5d %-20s %-10s %.1f%%%n", id, name, course, mark);
        }
    }

} catch (SQLException e) {
    System.out.println("Query failed: " + e.getMessage());
}

Inserting Data (INSERT)

Use Statement.RETURN_GENERATED_KEYS to retrieve the auto-generated primary key after an insert.

JAVA — INSERT 
String insertSQL = "INSERT INTO learners (full_name, age, city, course, mark) VALUES (?, ?, ?, ?, ?)";

try (Connection conn = DriverManager.getConnection(url, username, password);
     PreparedStatement ps = conn.prepareStatement(insertSQL, Statement.RETURN_GENERATED_KEYS)) {

    ps.setString(1, "Thandi Mokoena");
    ps.setInt(2, 21);
    ps.setString(3, "Johannesburg");
    ps.setString(4, "Java");
    ps.setDouble(5, 74.5);

    int rowsAffected = ps.executeUpdate();
    System.out.println("Rows inserted: " + rowsAffected);

    // Get the generated ID
    try (ResultSet keys = ps.getGeneratedKeys()) {
        if (keys.next()) {
            System.out.println("New learner ID: " + keys.getInt(1));
        }
    }

} catch (SQLException e) {
    System.out.println("Insert failed: " + e.getMessage());
}

Updating and Deleting

JAVA — UPDATE and DELETE 
// UPDATE
String updateSQL = "UPDATE learners SET mark = ? WHERE learner_id = ?";
try (Connection conn = DriverManager.getConnection(url, username, password);
     PreparedStatement ps = conn.prepareStatement(updateSQL)) {
    ps.setDouble(1, 82.0);
    ps.setInt(2, 5);
    System.out.println("Rows updated: " + ps.executeUpdate());
} catch (SQLException e) { e.printStackTrace(); }

// DELETE
String deleteSQL = "DELETE FROM learners WHERE learner_id = ?";
try (Connection conn = DriverManager.getConnection(url, username, password);
     PreparedStatement ps = conn.prepareStatement(deleteSQL)) {
    ps.setInt(1, 7);
    System.out.println("Rows deleted: " + ps.executeUpdate());
} catch (SQLException e) { e.printStackTrace(); }

Transactions

By default, each statement commits immediately (auto-commit). For operations that must succeed or fail together (like a bank transfer — debit one account, credit another), disable auto-commit and manage transactions explicitly.

JAVA — Transactions 
try (Connection conn = DriverManager.getConnection(url, username, password)) {

    conn.setAutoCommit(false); // start manual transaction

    try {
        // Debit from account 1001
        PreparedStatement debit = conn.prepareStatement(
            "UPDATE accounts SET balance = balance - ? WHERE account_id = ?");
        debit.setDouble(1, 500.00);
        debit.setInt(2, 1001);
        debit.executeUpdate();

        // Credit to account 1002
        PreparedStatement credit = conn.prepareStatement(
            "UPDATE accounts SET balance = balance + ? WHERE account_id = ?");
        credit.setDouble(1, 500.00);
        credit.setInt(2, 1002);
        credit.executeUpdate();

        conn.commit(); // both succeeded — commit
        System.out.println("Transfer complete.");

    } catch (SQLException e) {
        conn.rollback(); // one failed — rollback both
        System.out.println("Transfer failed, rolled back: " + e.getMessage());
    }
}

Practice Task

Your Turn

Write a LearnerDAO (Data Access Object) class with a private Connection field and methods: findAll() returns ArrayList<Learner>, findById(int id) returns a Learner (or null), insert(Learner l) returns the generated ID, update(int id, double newMark), delete(int id). Each method should open a PreparedStatement, execute, and close resources. Write a main class that exercises all five methods with test data.

Common Mistakes

  • Concatenating user input into SQL strings: "SELECT * FROM learners WHERE name = '" + name + "'" is a SQL injection vulnerability. Always use PreparedStatement with parameter placeholders.
  • Not closing connections — database connections are expensive and limited. Always use try-with-resources.
  • Hardcoding database credentials — store them in environment variables or a configuration file, never in source code.
  • Using column index instead of column name in ResultSet — rs.getString(1) breaks if the query column order changes. Use rs.getString("full_name").
  • Auto-commit for multi-statement operations — if you need atomicity, disable auto-commit and manage commits and rollbacks yourself.

Professional Tip

The Data Access Object (DAO) pattern used in the practice task is the standard in enterprise Java. It separates database logic from business logic, making both easier to test and maintain. In real projects you will use frameworks like Spring Data JPA or MyBatis, but understanding raw JDBC first makes those abstractions transparent.

Mini Quiz

Why must you always use PreparedStatement instead of Statement for user input?