JDBC: Connecting Java to Databases

TAKEAWAYS:

• JDBC provides a standardized API to interact with relational databases from Java using SQL.
• The four core JDBC interfaces are DriverManager, Connection, Statement, and ResultSet.
• Prepared statements prevent SQL injection and improve performance for repeated queries.
• Transactions ensure data integrity with commit() and rollback() methods.
• Result sets can be processed in forward-only, scrollable, or updatable modes.
• JDBC drivers follow the JDBC compliance levels (Type 1–4) with Type 4 being pure Java drivers.

---

### Core JDBC Architecture
JDBC follows a layered architecture to abstract database-specific details. The key components are:

```mermaid
classDiagram
    class Application {
        +uses JDBC API
    }
    class JDBCManager {
        +loads drivers
        +manages connections
    }
    class JDBCDriver {
        <<interface>>
        +connects to DB
        +executes SQL
    }
    class Database {
        +MySQL/PostgreSQL/Oracle
    }
    Application --> JDBCManager : uses
    JDBCManager --> JDBCDriver : loads
    JDBCDriver --> Database : communicates

How it works:

1. The Java application loads a JDBC driver (e.g., com.mysql.jdbc.Driver).
2. The DriverManager establishes a connection to the database.
3. SQL queries are executed via Statement or PreparedStatement objects.
4. Results are returned as ResultSet objects.

JDBC API Components

The four fundamental interfaces form the backbone of JDBC operations:

Visualizing a Connection:

// Step-by-step connection setup
Connection conn = DriverManager.getConnection(
    "jdbc:mysql://localhost:3306/mydb", "user", "pass");
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM customers");

State after each step:

Real-World Applications

1. eSewa (Nepal’s Digital Payment System)

• JDBC Use Case: Transaction processing
• How: When you pay your electricity bill via eSewa, JDBC connects the Java backend to the NTC database to:
  1. Verify your account balance
  2. Deduct the bill amount
  3. Update the transaction log
• Key Methods: PreparedStatement (to prevent SQL injection), commit() (to finalize transactions).

2. Ncell’s Customer Management System

• JDBC Use Case: Customer data retrieval
• How: When you check your Ncell bill online, JDBC queries the PostgreSQL database to:
  1. Fetch your account details (SELECT * FROM customers WHERE phone='98XXXXXXXX')
  2. Calculate usage charges (JOIN with call_logs table)
  3. Generate the bill PDF
• Optimization: Uses ResultSet in scrollable mode for large datasets.

3. Daraz’s Order Processing

• JDBC Use Case: Inventory management
• How: When you place an order on Daraz:
  1. JDBC checks stock levels (SELECT quantity FROM products WHERE id=123)
  2. Updates inventory (UPDATE products SET quantity=quantity-1 WHERE id=123)
  3. Logs the transaction (INSERT INTO orders VALUES (...))
• Critical Feature: Transactions ensure no overselling occurs.

Worked Example: Bank Loan Interest Calculator

Scenario: A bank uses JDBC to calculate monthly interest for loans. The loans table has columns: loan_id, principal, interest_rate, term_months.

// Step 1: Connect to the database
Connection conn = DriverManager.getConnection(
    "jdbc:postgresql://localhost:5432/bankdb", "admin", "secure123");

// Step 2: Use PreparedStatement to prevent SQL injection
String sql = "SELECT principal, interest_rate, term_months FROM loans WHERE loan_id = ?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, 1001); // Loan ID 1001

// Step 3: Execute query and process result
ResultSet rs = pstmt.executeQuery();
if (rs.next()) {
    double principal = rs.getDouble("principal");
    double rate = rs.getDouble("interest_rate");
    int term = rs.getInt("term_months");
    double monthlyInterest = (principal * rate / 100) / 12;
    System.out.printf("Monthly interest for loan 1001: %.2f", monthlyInterest);
} else {
    System.out.println("Loan not found!");
}

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

Trace Table:

JDBC Driver Types

JDBC drivers vary in performance and implementation. The compliance levels are:

Visual Comparison:

flowchart LR
    A["Java App"] --> B["Type 1: JDBC-ODBC Bridge"]
    A --> C["Type 2: Native-API"]
    A --> D["Type 3: Network Middleware"]
    A --> E["Type 4: Thin Driver"]
    B --> F["ODBC Driver"]
    C --> G["Native Library"]
    D --> H["Middleware Server"]
    E --> I["Direct JDBC Driver"]
    F --> J["Database"]
    G --> J
    H --> J
    I --> J

SQL Injection Prevention

Vulnerable Code (SQL Injection Risk):

String userInput = "admin'; DROP TABLE users;--";
String sql = "SELECT * FROM users WHERE username = '" + userInput + "'";
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql); // DANGER: Deletes users table!

Secure Code (Using PreparedStatement):

String userInput = "admin'; DROP TABLE users;--";
String sql = "SELECT * FROM users WHERE username = ?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, userInput); // Parameterized query
ResultSet rs = pstmt.executeQuery(); // Safe: Input treated as data

Why It Works:

Transaction Management

Transactions ensure data integrity using ACID properties. JDBC provides methods to control transactions:

Example: Transferring Funds Between Accounts

Connection conn = DriverManager.getConnection("jdbc:mysql://...", "user", "pass");
conn.setAutoCommit(false); // Start transaction

try {
    // Withdraw from account A
    Statement stmtA = conn.createStatement();
    stmtA.executeUpdate("UPDATE accounts SET balance = balance - 1000 WHERE id = 1");

    // Deposit to account B
    Statement stmtB = conn.createStatement();
    stmtB.executeUpdate("UPDATE accounts SET balance = balance + 1000 WHERE id = 2");

    conn.commit(); // Success: Save changes
} catch (SQLException e) {
    conn.rollback(); // Failure: Revert changes
    System.err.println("Transaction failed: " + e.getMessage());
}

State Diagram:

stateDiagram-v2
    [*] --> StartTransaction: setAutoCommit(false)
    StartTransaction --> ExecuteSQL: stmt.executeUpdate()
    ExecuteSQL --> CheckSuccess: if (no error)
    CheckSuccess --> Commit: conn.commit()
    CheckSuccess --> Rollback: conn.rollback()
    Commit --> [*]
    Rollback --> [*]

ResultSet Processing Modes

Result sets can be configured for different access patterns:

Example: Scrollable ResultSet

String sql = "SELECT * FROM employees ORDER BY salary DESC";
Statement stmt = conn.createStatement(
    ResultSet.TYPE_SCROLL_INSENSITIVE,
    ResultSet.CONCUR_READ_ONLY);
ResultSet rs = stmt.executeQuery(sql);

// Navigate to the 5th highest-paid employee
rs.absolute(5);
String name = rs.getString("name");
double salary = rs.getDouble("salary");

Exam Tip

1. Connection Handling: Always close ResultSet, Statement, and Connection in a finally block or use try-with-resources (Java 7+).

   try (Connection conn = DriverManager.getConnection(...);
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * FROM users")) {
       // Process results
   } // Resources auto-closed here
   

2. SQL Injection: Use PreparedStatement every time you accept user input for queries. This is a common exam question.

3. Transaction Questions: Expect questions on commit()/rollback() scenarios, especially in banking or inventory systems.

4. Driver Types: Know the differences between Type 1–4 drivers, especially when asked about performance or portability.

5. ResultSet Methods: Memorize key methods like next(), previous(), absolute(), getString(), and getInt().

6. Real-World Scenarios: Be ready to relate JDBC to systems like eSewa (transactions), Ncell (data retrieval), or Daraz (inventory updates). Always tie your answer to a concrete example.

Visual Summary:

mindmap
  root((JDBC))
    Connection
      DriverManager
      URL Format
      Connection Pooling
    Statement
      Statement
      PreparedStatement
      CallableStatement
    ResultSet
      TYPE_FORWARD_ONLY
      TYPE_SCROLL_INSENSITIVE
      TYPE_SCROLL_SENSITIVE
    Transaction
      setAutoCommit(false)
      commit()
      rollback()
    Security
      SQL Injection
      PreparedStatement
    Drivers
      Type 1-4