1. What is JDBC?

JDBC stands for Java Database Connectivity. It is a Java API that lets your Java program talk to a database (MySQL, Oracle, PostgreSQL, etc.) — send SQL queries, get results back, and manipulate data.

Why Do We Need JDBC?

Your Java application stores data in variables and objects — but when the app closes, all that data is lost. Databases store data permanently. JDBC is the bridge that lets Java save and retrieve data from a database.

• Java app needs to store user registrations → needs a database
• Java app needs to show a list of products → reads from a database
• Without JDBC, Java has no way to talk to MySQL, Oracle, etc.

2. JDBC Architecture

JDBC sits between your Java code and the database. Here's the flow:

┌─────────────────┐
│  Java Application │
└────────┬────────┘
         │ calls methods like executeQuery()
┌────────▼────────┐
│    JDBC API       │  ← java.sql package (Connection, Statement, ResultSet)
└────────┬────────┘
         │ translates to database-specific protocol
┌────────▼────────┐
│   JDBC Driver     │  ← e.g., MySQL Connector/J, Oracle JDBC driver
└────────┬────────┘
         │ sends actual SQL over the network
┌────────▼────────┐
│    Database       │  ← MySQL, Oracle, PostgreSQL
└─────────────────┘

3. Types of JDBC Drivers

4. JDBC Setup

Step 1: Get the MySQL Connector/J JAR

Download mysql-connector-j-8.x.x.jar from the MySQL website and add it to your project's classpath (or lib folder).

Step 2: Load the Driver

Class.forName("com.mysql.cj.jdbc.Driver");

Step 3: Connection URL Format

// Format:
jdbc:mysql://hostname:port/database_name

// Example — connecting to "ilpdb" on your own machine:
jdbc:mysql://localhost:3306/ilpdb

5. The 7 Steps of JDBC

// Step 1: Import the java.sql package
import java.sql.*;

public class JDBCDemo {
    public static void main(String[] args) {
        try {
            // Step 2: Load/Register the driver
            Class.forName("com.mysql.cj.jdbc.Driver");

            // Step 3: Establish the connection
            Connection con = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/ilpdb",  // URL
                "root",                                // username
                "password"                              // password
            );

            // Step 4: Create a Statement object
            Statement stmt = con.createStatement();

            // Step 5: Execute the SQL query
            ResultSet rs = stmt.executeQuery("SELECT * FROM employees");

            // Step 6: Process the results
            while (rs.next()) {
                System.out.println(rs.getInt("id") + " - " + rs.getString("name"));
            }

            // Step 7: Close the connection
            rs.close();
            stmt.close();
            con.close();

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

6. Connection

Getting a Connection

Connection con = DriverManager.getConnection(url, username, password);

The Connection object represents a live session with the database. As long as it's open, you can send queries.

Important Connection Methods

Try-With-Resources (Best Practice)

Always use try-with-resources so connections auto-close even if an exception occurs:

try (Connection con = DriverManager.getConnection(url, user, pass);
     Statement stmt = con.createStatement();
     ResultSet rs = stmt.executeQuery("SELECT * FROM employees")) {

    while (rs.next()) {
        System.out.println(rs.getString("name"));
    }
    // No need to call close() — happens automatically!

} catch (SQLException e) {
    e.printStackTrace();
}

7. Statement Types

Statement Example

Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM employees WHERE dept='IT'");

Why Statement is Dangerous

// User enters this as their name: ' OR '1'='1
String userInput = "' OR '1'='1";
String sql = "SELECT * FROM users WHERE name='" + userInput + "'";
// Becomes: SELECT * FROM users WHERE name='' OR '1'='1'
// This returns ALL rows! This is SQL Injection.
stmt.executeQuery(sql);

8. PreparedStatement in Detail

PreparedStatement is the right way to execute SQL with user input. It uses ? placeholders instead of string concatenation.

// Step 1: Write SQL with ? placeholders (no quotes around ?)
String sql = "SELECT * FROM employees WHERE dept = ? AND salary > ?";

// Step 2: Create the PreparedStatement
PreparedStatement pstmt = con.prepareStatement(sql);

// Step 3: Set the parameter values (index starts at 1, not 0!)
pstmt.setString(1, "IT");       // First ? → "IT"
pstmt.setDouble(2, 50000.0);   // Second ? → 50000.0

// Step 4: Execute
ResultSet rs = pstmt.executeQuery();

Common Setter Methods

9. executeQuery() vs executeUpdate() vs execute()

// executeQuery() — for SELECT
ResultSet rs = stmt.executeQuery("SELECT * FROM employees");

// executeUpdate() — for INSERT/UPDATE/DELETE
int rowsAffected = stmt.executeUpdate("DELETE FROM employees WHERE id=5");
System.out.println(rowsAffected + " row(s) deleted");

// execute() — for any SQL (rarely used in exams)
boolean hasResultSet = stmt.execute("SELECT * FROM employees");
if (hasResultSet) {
    ResultSet rs2 = stmt.getResultSet();
}

10. ResultSet

A ResultSet is like a table of results. It has a cursor that starts before the first row. You call next() to move to each row.

ResultSet rs = stmt.executeQuery("SELECT id, name, salary FROM employees");

// Cursor starts BEFORE first row
// rs.next() moves to first row, returns true
// rs.next() moves to second row, returns true
// ... when no more rows, returns false

while (rs.next()) {
    int id = rs.getInt("id");            // by column name
    String name = rs.getString(2);       // by column index (starts at 1!)
    double salary = rs.getDouble("salary");
    System.out.println(id + " | " + name + " | " + salary);
}

ResultSet Navigation Methods

ResultSet Types

ResultSet Concurrency

Creating a Scrollable ResultSet

Statement stmt = con.createStatement(
    ResultSet.TYPE_SCROLL_INSENSITIVE,  // scrollable
    ResultSet.CONCUR_READ_ONLY           // read-only
);
ResultSet rs = stmt.executeQuery("SELECT * FROM employees");

rs.last();                 // jump to last row
System.out.println("Total rows: " + rs.getRow());  // get current row number
rs.beforeFirst();           // go back to start

11. CRUD Operations — Complete Examples

CREATE — INSERT a New Employee

String sql = "INSERT INTO employees (name, dept, salary) VALUES (?, ?, ?)";

try (PreparedStatement pstmt = con.prepareStatement(sql)) {
    pstmt.setString(1, "Darshan");    // name
    pstmt.setString(2, "Engineering"); // dept
    pstmt.setDouble(3, 75000.0);      // salary

    int rows = pstmt.executeUpdate();  // returns number of rows inserted
    System.out.println(rows + " row inserted");
}

READ — SELECT All Employees

String sql = "SELECT id, name, dept, salary FROM employees";

try (PreparedStatement pstmt = con.prepareStatement(sql);
     ResultSet rs = pstmt.executeQuery()) {

    while (rs.next()) {
        System.out.println(
            rs.getInt("id") + " | " +
            rs.getString("name") + " | " +
            rs.getString("dept") + " | " +
            rs.getDouble("salary")
        );
    }
}

READ — SELECT with WHERE (find by department)

String sql = "SELECT * FROM employees WHERE dept = ?";

try (PreparedStatement pstmt = con.prepareStatement(sql)) {
    pstmt.setString(1, "IT");
    ResultSet rs = pstmt.executeQuery();

    while (rs.next()) {
        System.out.println(rs.getString("name") + " — " + rs.getDouble("salary"));
    }
}

UPDATE — Change Salary

String sql = "UPDATE employees SET salary = ? WHERE id = ?";

try (PreparedStatement pstmt = con.prepareStatement(sql)) {
    pstmt.setDouble(1, 85000.0);  // new salary
    pstmt.setInt(2, 101);         // employee ID

    int rows = pstmt.executeUpdate();
    System.out.println(rows + " row(s) updated");
}

DELETE — Remove an Employee

String sql = "DELETE FROM employees WHERE id = ?";

try (PreparedStatement pstmt = con.prepareStatement(sql)) {
    pstmt.setInt(1, 101);          // ID to delete

    int rows = pstmt.executeUpdate();
    System.out.println(rows + " row(s) deleted");
}

12. Transaction Management

By default, JDBC runs in auto-commit mode — every SQL statement is committed immediately. But sometimes you need multiple statements to succeed or fail together.

try {
    con.setAutoCommit(false);   // Step 1: Turn OFF auto-commit

    // Step 2: Execute multiple statements
    PreparedStatement ps1 = con.prepareStatement(
        "UPDATE accounts SET balance = balance - ? WHERE id = ?");
    ps1.setDouble(1, 5000);
    ps1.setString(2, "A");
    ps1.executeUpdate();

    PreparedStatement ps2 = con.prepareStatement(
        "UPDATE accounts SET balance = balance + ? WHERE id = ?");
    ps2.setDouble(1, 5000);
    ps2.setString(2, "B");
    ps2.executeUpdate();

    con.commit();                // Step 3: All good — COMMIT both
    System.out.println("Transfer successful!");

} catch (SQLException e) {
    con.rollback();              // Step 4: Something failed — ROLLBACK both
    System.out.println("Transfer failed, rolled back.");
    e.printStackTrace();
} finally {
    con.setAutoCommit(true);    // Reset auto-commit
}

Savepoints

A Savepoint marks a point within a transaction. You can rollback to a savepoint instead of rolling back the entire transaction.

con.setAutoCommit(false);

stmt.executeUpdate("INSERT INTO logs VALUES (1, 'Step 1 done')");

Savepoint sp = con.setSavepoint("afterStep1");  // Mark this point

stmt.executeUpdate("INSERT INTO logs VALUES (2, 'Step 2 done')");

// Oops, Step 2 was wrong — rollback only Step 2
con.rollback(sp);   // Rolls back to savepoint — Step 1 is still intact

con.commit();        // Commits Step 1 only

13. Batch Processing

When you need to insert 100 or 1000 rows, running them one by one is slow. Batch processing sends multiple SQL statements to the database in one go.

String sql = "INSERT INTO employees (name, dept, salary) VALUES (?, ?, ?)";
PreparedStatement pstmt = con.prepareStatement(sql);

con.setAutoCommit(false);   // Use transaction for batch

// Add row 1 to batch
pstmt.setString(1, "Alice");
pstmt.setString(2, "HR");
pstmt.setDouble(3, 50000);
pstmt.addBatch();              // Add to batch — not executed yet

// Add row 2 to batch
pstmt.setString(1, "Bob");
pstmt.setString(2, "IT");
pstmt.setDouble(3, 60000);
pstmt.addBatch();

// Add row 3 to batch
pstmt.setString(1, "Charlie");
pstmt.setString(2, "Finance");
pstmt.setDouble(3, 55000);
pstmt.addBatch();

int[] results = pstmt.executeBatch();  // Execute all 3 at once!
con.commit();                           // Commit the transaction

System.out.println("Inserted " + results.length + " rows");

14. Connection Pooling (Brief)

Creating a new database connection is expensive (takes time and resources). In a real web app, hundreds of users hit the server — creating a new connection for each request is wasteful.

Connection Pooling = keep a pool of pre-created connections ready. When a request comes, grab one from the pool. When done, return it to the pool instead of closing it.

Popular Connection Pool Libraries

15. Common JDBC Exceptions

16. Practice Questions