JDBC Architecture, Drivers, CRUD, Transactions & Connection Pooling

Core Concepts: JDBC Architecture

JDBC (Java Database Connectivity) is an API that enables Java programs to interact with databases. It follows a two-tier or three-tier architecture:

flowchart TD
    A["Java Application"] -->|"JDBC API"| B["JDBC Driver Manager"]
    B -->|"Driver Selection"| C["JDBC Driver"]
    C -->|"SQL Query"| D["Database"]
    D -->|"Result Set"| C
    C -->|"Result"| B
    B -->|"Result"| A

Key Components:

1. JDBC API: Provides interfaces (Connection, Statement, ResultSet) and classes for database operations.
2. JDBC Driver Manager: Loads and manages JDBC drivers.
3. JDBC Driver: Acts as a bridge between Java and the database (e.g., MySQL, Oracle).

Real-World Example:

• eSewa uses JDBC to store user transactions (e.g., electricity bill payments) in a MySQL database. When you pay a bill, JDBC executes SQL queries to update your account balance and log the transaction.

JDBC Driver Types

JDBC supports four driver types, each with trade-offs:

Visual: JDBC Driver Types

pie
    title JDBC Driver Usage in Nepal (2024)
    "Type 4 (Pure Java)" : 65
    "Type 2 (Native)" : 25
    "Type 3 (Middleware)" : 8
    "Type 1 (Deprecated)" : 2

Exam Tip:

• Type 4 drivers (e.g., MySQL Connector/J) are most commonly tested in exams. Memorize the URL format:

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

CRUD Operations with JDBC

CRUD (Create, Read, Update, Delete) operations are performed using:

• Connection: Represents a database connection.
• Statement: Executes SQL queries.
• PreparedStatement: Precompiled SQL (prevents SQL injection).
• ResultSet: Holds query results.

Example: Inserting a Student Record (eSewa-like)

// Step 1: Load and register the driver
Class.forName("com.mysql.cj.jdbc.Driver");

// Step 2: Establish connection
Connection conn = DriverManager.getConnection(
    "jdbc:mysql://localhost:3306/nesdb", "root", "password");

// Step 3: Create a PreparedStatement (prevents SQL injection)
String sql = "INSERT INTO students (id, name, email) VALUES (?, ?, ?)";
PreparedStatement pstmt = conn.prepareStatement(sql);

// Step 4: Set parameters and execute
pstmt.setInt(1, 101);
pstmt.setString(2, "Rohan Thapa");
pstmt.setString(3, "rohan@example.com");
pstmt.executeUpdate();  // Inserts 1 row

// Step 5: Close resources
pstmt.close();
conn.close();

State After Execution (Database Table):

| id  | name         | email               |
|-----|--------------|---------------------|
| 101 | Rohan Thapa  | rohan@example.com   |

Real-World Tie-In:

• Ncell’s customer database uses JDBC to insert new SIM registrations. The PreparedStatement ensures no SQL injection when storing user details.

Transaction Management

Transactions ensure atomicity (all operations succeed or fail together). Use:

• Connection.setAutoCommit(false): Disable auto-commit.
• commit(): Save changes.
• rollback(): Undo changes on failure.

Example: Transferring Money (Bank-like)

Connection conn = DriverManager.getConnection(url);
conn.setAutoCommit(false);  // Start transaction

try {
    // Deduct from Account A
    String sql1 = "UPDATE accounts SET balance = balance - 1000 WHERE id = 1";
    conn.createStatement().executeUpdate(sql1);

    // Add to Account B
    String sql2 = "UPDATE accounts SET balance = balance + 1000 WHERE id = 2";
    conn.createStatement().executeUpdate(sql2);

    conn.commit();  // Success: Save both updates
} catch (SQLException e) {
    conn.rollback();  // Failure: Revert both updates
}

Visual: Transaction States

stateDiagram-v2
    [*] --> Active: Transaction starts
    Active --> Committed: commit() called
    Active --> RolledBack: rollback() or exception
    Committed --> [*]
    RolledBack --> [*]

Exam Tip:

• Always close resources (Connection, Statement) in a finally block to avoid memory leaks.
• Use try-with-resources (Java 7+) for automatic closing:

  try (Connection conn = DriverManager.getConnection(url);
       Statement stmt = conn.createStatement()) {
      stmt.execute("SELECT * FROM students");
  } // Resources auto-close here
  

Connection Pooling

Reusing connections improves performance. HikariCP (a popular pool) manages:

• Pool size: Number of connections.
• Idle timeout: Close unused connections.
• Max lifetime: Prevent stale connections.

Example: HikariCP Setup

HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:mysql://localhost:3306/nesdb");
config.setUsername("root");
config.setPassword("password");
config.setMaximumPoolSize(10);  // Max 10 connections

HikariDataSource ds = new HikariDataSource(config);

// Get a connection from the pool
Connection conn = ds.getConnection();

Visual: Connection Pool States

flowchart LR
    A["Application Request"] --> B["Check Pool"]
    B -->|"Available"| C["Return Connection"]
    B -->|"None"| D["Create New"]
    C --> E["Execute Query"]
    E --> F["Return to Pool"]
    D --> F

Real-World Example:

• NEPSE’s stock trading system uses connection pooling to handle thousands of simultaneous trades without overwhelming the database.

Error Handling

Common JDBC exceptions:

• SQLException: Database errors (e.g., syntax error, connection failure).
• SQLIntegrityConstraintViolationException: Constraint violations (e.g., duplicate key).

Example: Handling Duplicate Entry

try {
    pstmt.executeUpdate();
} catch (SQLIntegrityConstraintViolationException e) {
    System.err.println("Duplicate ID: " + e.getMessage());
}

Exam Tip:

• Log exceptions using e.printStackTrace() or a logging framework (e.g., SLF4J).
• Use try-with-resources to avoid NullPointerException from unclosed connections.

## In the Real World

1. eSewa (Nepal)

   • Idea Used: JDBC with connection pooling (HikariCP) to handle 10,000+ simultaneous transactions during festival seasons.
   • How: Each payment request (e.g., electricity bill) is processed via a JDBC PreparedStatement to update user balances atomically.

2. Ncell’s Customer Portal

   • Idea Used: Transaction management for SIM top-ups.
   • How: When you recharge, JDBC executes:

     UPDATE accounts SET balance = balance - 100 WHERE phone = '98XXXXXX';
     INSERT INTO transactions (phone, amount, type) VALUES ('98XXXXXX', 100, 'RECHARGE');
     

     If the update fails, the entire transaction rolls back.

3. Daraz’s Order Processing

   • Idea Used: ResultSet for fetching product inventory.
   • How: When you place an order, JDBC queries:

     SELECT stock FROM products WHERE id = 123;
     

     The ResultSet checks stock availability before processing.

## Exam Tip

1. Architecture Questions:

   • Draw the two-tier vs. three-tier JDBC architecture diagram.
   • List driver types and their pros/cons (Type 4 is most important).

2. Code Questions:

   • Always use PreparedStatement (not Statement) to avoid SQL injection.
   • Close resources in finally or use try-with-resources.
   • Trace variable states (e.g., ResultSet after executeQuery()).

3. Common Pitfalls:

   • Forgetting commit() or rollback() in transactions.
   • Not handling SQLException properly.
   • Using Statement instead of PreparedStatement (exam favorite!).

4. Real-World Scenarios:

   • Bank loan interest: Use JDBC to update monthly interest with transactions.
   • Traffic routes (Kathmandu): Simulate a ResultSet for shortest-path queries (though this uses JDBC + algorithms).

Visual Summary: JDBC Workflow

flowchart TD
    A["1. Load Driver"] --> B["2. Get Connection"]
    B --> C["3. Create Statement"]
    C --> D["4. Execute Query"]
    D --> E["5. Process ResultSet"]
    E --> F["6. Close Resources"]
    F --> G["End"]