JDBC: Architecture, Drivers, Statements & Database Operations

Core Concepts

1. JDBC Overview

Java Database Connectivity (JDBC) is an API (Application Programming Interface) that enables Java programs to interact with relational databases. It provides a standardized way to execute SQL queries, process results, and manage transactions.

Key Components

classDiagram
    class JDBC_API {
        +DriverManager
        +Connection
        +Statement
        +PreparedStatement
        +CallableStatement
        +ResultSet
        +ResultSetMetaData
    }
    class Database {
        <<Database>>
        MySQL, Oracle, PostgreSQL
    }
    JDBC_API --> DriverManager : "Manages drivers"
    DriverManager --> Connection : "Creates connections"
    Connection --> Statement : "Creates statements"
    Statement --> ResultSet : "Executes queries"

How JDBC Works

1. Load JDBC Driver: Register the database driver (e.g., com.mysql.jdbc.Driver).
2. Establish Connection: Use DriverManager.getConnection() with URL, username, and password.
3. Create Statement: Generate Statement, PreparedStatement, or CallableStatement.
4. Execute Query: Call executeQuery(), executeUpdate(), or execute().
5. Process Results: Handle ResultSet or check affected rows.
6. Close Resources: Release connections, statements, and result sets.

2. JDBC Driver Types

JDBC supports four types of drivers, each with trade-offs in performance, compatibility, and setup complexity.

Exam Tip: Type 4 drivers (e.g., MySQL Connector/J, Oracle JDBC Thin) are preferred in modern applications due to their pure Java implementation and performance.

3. JDBC Statements

JDBC provides three types of statements for SQL execution:

a) Statement Interface

• Used for dynamic SQL queries (e.g., SELECT * FROM TABLE).
• Vulnerable to SQL injection if user input is directly concatenated.
• Example:

  Statement stmt = conn.createStatement();
  ResultSet rs = stmt.executeQuery("SELECT * FROM MOVIE");
  

b) PreparedStatement Interface

• Precompiled SQL template with placeholders (?) for parameters.
• Prevents SQL injection and improves performance for repeated queries.
• Example:

  PreparedStatement pstmt = conn.prepareStatement(
      "UPDATE MOVIE SET genre = ? WHERE title = ?");
  pstmt.setString(1, "Comedy");
  pstmt.setString(2, "Jatra");
  pstmt.executeUpdate();
  

c) CallableStatement Interface

• Used for stored procedures (e.g., CALL procedure_name(?)).
• Supports IN, OUT, and INOUT parameters.
• Example:

  CallableStatement cstmt = conn.prepareCall("{CALL get_movie_count(?)}");
  cstmt.setInt(1, 1); // Genre ID
  ResultSet rs = cstmt.executeQuery();
  

Comparison Table:

4. ResultSet and Metadata

a) ResultSet

• Holds query results in a table-like structure.
• Types:
  • TYPE_FORWARD_ONLY: Default (read-only, forward-only).
  • TYPE_SCROLL_INSENSITIVE: Scrollable but not sensitive to changes.
  • TYPE_SCROLL_SENSITIVE: Scrollable and sensitive to changes.
• Concurrency:
  • CONCUR_READ_ONLY: Default (read-only).
  • CONCUR_UPDATABLE: Allows updates/deletes.

Example: Scrollable ResultSet

Statement stmt = conn.createStatement(
    ResultSet.TYPE_SCROLL_INSENSITIVE,
    ResultSet.CONCUR_READ_ONLY);
ResultSet rs = stmt.executeQuery("SELECT * FROM TEACHER");
rs.last(); // Move to last row
System.out.println("Last record: " + rs.getString("Name"));

b) ResultSetMetaData

• Provides schema information (column names, types, sizes).
• Example:

  ResultSetMetaData meta = rs.getMetaData();
  int columns = meta.getColumnCount();
  for (int i = 1; i <= columns; i++) {
      System.out.println("Column " + i + ": " + meta.getColumnName(i));
  }
  

5. Transaction Management

JDBC supports ACID transactions (Atomicity, Consistency, Isolation, Durability).

• Methods:
  • conn.setAutoCommit(false): Disable auto-commit.
  • conn.commit(): Save changes.
  • conn.rollback(): Undo changes.
  • conn.setTransactionIsolation(level): Set isolation level (e.g., TRANSACTION_READ_COMMITTED).

Example: Transaction Handling

conn.setAutoCommit(false);
try {
    PreparedStatement pstmt = conn.prepareStatement(
        "UPDATE ACCOUNT SET balance = balance - ? WHERE id = ?");
    pstmt.setDouble(1, 100.0);
    pstmt.setInt(2, 1);
    pstmt.executeUpdate();

    pstmt = conn.prepareStatement(
        "UPDATE ACCOUNT SET balance = balance + ? WHERE id = ?");
    pstmt.setDouble(1, 100.0);
    pstmt.setInt(2, 2);
    pstmt.executeUpdate();

    conn.commit();
} catch (SQLException e) {
    conn.rollback();
    e.printStackTrace();
} finally {
    conn.setAutoCommit(true);
}

6. Batch Processing

• Improves performance by grouping multiple SQL statements into a single batch.
• Methods:
  • addBatch(String sql): Add a query to the batch.
  • executeBatch(): Execute all batched queries.
• Example:

  Statement stmt = conn.createStatement();
  stmt.addBatch("INSERT INTO MOVIE VALUES (1, 'Jatra', 'Comedy')");
  stmt.addBatch("INSERT INTO MOVIE VALUES (2, 'Alamchi', 'Drama')");
  stmt.addBatch("INSERT INTO MOVIE VALUES (3, 'Sipahi', 'Action')");
  int[] counts = stmt.executeBatch(); // Returns affected rows per query
  

7. Error Handling

• SQLException: Thrown for database errors (e.g., syntax errors, connection failures).
• Best Practices:
  • Use try-catch-finally to close resources.
  • Log errors for debugging.
  • Example:

    try {
        conn = DriverManager.getConnection(url, user, password);
    } catch (SQLException e) {
        System.err.println("Connection failed: " + e.getMessage());
    } finally {
        if (conn != null) try { conn.close(); } catch (SQLException e) { /* Log */ }
    }
    

8. Worked Examples

Example 1: Insert Records into MOVIE Table

String url = "jdbc:mysql://localhost:3306/mydb";
String user = "root";
String password = "password";

try (Connection conn = DriverManager.getConnection(url, user, password)) {
    String[] titles = {"Jatra", "Alamchi", "Sipahi"};
    String[] genres = {"Comedy", "Drama", "Action"};

    for (int i = 0; i < titles.length; i++) {
        PreparedStatement pstmt = conn.prepareStatement(
            "INSERT INTO MOVIE (title, genre) VALUES (?, ?)");
        pstmt.setString(1, titles[i]);
        pstmt.setString(2, genres[i]);
        pstmt.executeUpdate();
    }
} catch (SQLException e) {
    e.printStackTrace();
}

Example 2: Update Genre Using PreparedStatement

try (Connection conn = DriverManager.getConnection(url, user, password)) {
    PreparedStatement pstmt = conn.prepareStatement(
        "UPDATE MOVIE SET genre = ? WHERE title = ?");
    pstmt.setString(1, "Comedy");
    pstmt.setString(2, "Jatra");
    int rowsAffected = pstmt.executeUpdate();
    System.out.println("Updated " + rowsAffected + " rows.");
} catch (SQLException e) {
    e.printStackTrace();
}

Example 3: Fetch and Display TEACHER Records

try (Connection conn = DriverManager.getConnection(url, user, password)) {
    Statement stmt = conn.createStatement();
    ResultSet rs = stmt.executeQuery("SELECT * FROM TEACHER");

    while (rs.next()) {
        int id = rs.getInt("ID");
        String name = rs.getString("Name");
        System.out.println("ID: " + id + ", Name: " + name);
    }
} catch (SQLException e) {
    e.printStackTrace();
}

Example 4: Insert into TEACHER Table

try (Connection conn = DriverManager.getConnection(url, user, password)) {
    PreparedStatement pstmt = conn.prepareStatement(
        "INSERT INTO TEACHER VALUES (?, ?)");
    pstmt.setInt(1, 8);
    pstmt.setString(2, "Ramesh");
    pstmt.executeUpdate();
} catch (SQLException e) {
    e.printStackTrace();
}

9. Best Practices

1. Use try-with-resources: Automatically closes Connection, Statement, and ResultSet.
2. Prefer PreparedStatement: Avoids SQL injection and improves performance.
3. Close Resources in finally: Ensures no resource leaks.
4. Use Connection Pooling: For production apps (e.g., HikariCP, Apache DBCP).
5. Handle Transactions Properly: Use commit()/rollback() for critical operations.
6. Validate Inputs: Always sanitize user inputs before using in SQL.

10. Common Pitfalls

• Forgetting to close resources: Leads to memory leaks.
• SQL Injection: Using Statement with user input (e.g., WHERE id = " + userInput).
• Not handling transactions: Risk of partial updates.
• Ignoring exceptions: Silent failures can corrupt data.

Exam Tip

What Examiners Look For

1. Correct Driver Loading: Ensure the driver is registered or loaded via Class.forName() (though modern JDBC uses ServiceLoader).
2. Proper Connection Handling: Use try-with-resources or finally blocks to close connections.
3. Statement Selection:
   • Use PreparedStatement for dynamic queries (avoids SQL injection).
   • Use CallableStatement for stored procedures.
4. ResultSet Processing:
   • Check rs.next() before accessing data.
   • Use ResultSetMetaData for column details if required.
5. Transaction Management:
   • Disable auto-commit for multi-step operations.
   • Always commit() or rollback().
6. Batch Processing: Use addBatch() for bulk operations.
7. Error Handling: Catch SQLException and handle gracefully.

Common Exam Questions & How to Answer

Model Answer Structure

For practical questions (e.g., "Write JDBC code to insert 3 records into MOVIE"):

1. Driver Loading: Class.forName() or service loader (if required).
2. Connection: DriverManager.getConnection() with URL, username, password.
3. Statement: Use PreparedStatement for safety.
4. Execution: Loop or batch for multiple records.
5. Closure: try-with-resources or manual close().
6. Error Handling: catch (SQLException e) { e.printStackTrace(); }.

For theoretical questions (e.g., "Explain JDBC driver types"):

1. Define each type in a table.
2. Compare pros/cons.
3. State which is most commonly used (Type 4).
4. Give an example driver class (e.g., com.mysql.cj.jdbc.Driver).

flowchart TD
    A[JDBC API] --> B[DriverManager]
    B --> C[Load Driver]
    C --> D[Get Connection]
    D --> E[Create Statement]
    E --> F[Execute Query]
    F --> G[Process ResultSet]
    G --> H[Close Resources]
    H -->|Error| I[SQLException Handling]
    H -->|Success| J[Transaction Commit/Rollback]