JDBC & Database Connectivity: Connecting Java to Databases

TAKEAWAYS:

• JDBC bridges Java applications and relational databases using standard APIs like DriverManager, Connection, Statement, and ResultSet.
• SQL statements are executed via Statement (static queries) or PreparedStatement (dynamic queries with parameters) to prevent SQL injection.
• ResultSet objects fetch query results row-by-row, with types like TYPE_FORWARD_ONLY, TYPE_SCROLL_INSENSITIVE, and TYPE_SCROLL_SENSITIVE.
• Transactions ensure data integrity with methods like commit() and rollback(), and JDBC supports isolation levels (e.g., TRANSACTION_READ_COMMITTED).
• Layout managers (e.g., BorderLayout, GridLayout) organize GUI components for database-driven apps, while RowSet objects enable offline database operations.
• Real-world use cases include eSewa’s transaction logging, Daraz’s inventory management, and NEPSE’s stock market data retrieval.

1. Introduction to JDBC

Java Database Connectivity (JDBC) is a Java API that enables applications to interact with relational databases (e.g., MySQL, Oracle, PostgreSQL) using standard SQL. It abstracts database-specific details, allowing portable database access.

1.1 JDBC Architecture

JDBC follows a three-tier architecture:

1. Java Application: The client code (e.g., a Java servlet or Swing app).
2. JDBC API: Provides interfaces like DriverManager, Connection, Statement, and ResultSet.
3. Database System: The actual database (e.g., MySQL, Oracle).

figure:

flowchart TD
    A["Java Application"] -->|"JDBC Driver"| B["DriverManager"]
    B --> C["Connection"]
    C --> D["Statement/PreparedStatement/CallableStatement"]
    D --> E["ResultSet"]
    E --> F["Database System"]

1.2 Key JDBC Interfaces

1.3 JDBC Driver Types

2. Connecting to a Database

To connect to a database, you need:

1. JDBC Driver: Loaded via Class.forName() (for Type 2/4).
2. Connection URL: Format: jdbc:subprotocol:subname
   • Example: jdbc:mysql://localhost:3306/Shop (for MySQL).
3. Credentials: Username and password.

2.1 Example: Establishing a Connection

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

```figure
{"type":"network","nodes":["Java Code","Class.forName()","DriverManager","Connection","Database"],"edges":[["Java Code","Class.forName()","loads"],["Class.forName()","DriverManager","registers"],["DriverManager","Connection","creates"],["Connection","Database","connects to"]],"directed":true,"caption":"Step-by-step process of loading JDBC driver, creating a connection, and establishing a database link."}

public class DatabaseConnection { public static void main(String[] args) { String url = "jdbc:mysql://localhost:3306/Shop"; String user = "root"; String password = "password";

    try {
        // Load the JDBC driver (Type 4: Thin Driver)
        Class.forName("com.mysql.cj.jdbc.Driver");

        // Establish connection
        Connection conn = DriverManager.getConnection(url, user, password);
        System.out.println("Connected to database!");
        conn.close();
    } catch (ClassNotFoundException | SQLException e) {
        e.printStackTrace();
    }
}

}

2.2 Connection Pooling (Optimization)

• Problem: Creating a new connection for every request is inefficient.
• Solution: Use a connection pool (e.g., Apache DBCP, HikariCP) to reuse connections.
• Example: NEPSE uses connection pooling to handle thousands of stock market queries per second.

3. Executing SQL Statements

JDBC provides three ways to execute SQL:

flowchart TD
    A["PreparedStatement"] -->|"setInt(1, 2)"| B["? = 2"]
    B -->|"setString(2, 'Smartphone')"| C["? = 'Smartphone'"]
    C -->|"setDouble(3, 35000)"| D["executeUpdate()"]
    D --> E["INSERT INTO Item VALUES (2, 'Smartphone', 35000)"]
    E --> F["1 row inserted"]

3.1 Statement (Static Queries)

String sql = "INSERT INTO Item (ItemID, Name, UnitPrice) VALUES (1, 'Laptop', 50000)";
Statement stmt = conn.createStatement();
int rowsAffected = stmt.executeUpdate(sql);
System.out.println(rowsAffected + " rows inserted.");

3.2 PreparedStatement (Dynamic Queries)

String sql = "INSERT INTO Item (ItemID, Name, UnitPrice) VALUES (?, ?, ?)";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, 2);
pstmt.setString(2, "Smartphone");
pstmt.setDouble(3, 35000);
int rowsAffected = pstmt.executeUpdate(sql);

Why use PreparedStatement?

• Prevents SQL injection: Parameters are escaped automatically.
• Performance: Query plans are cached.

3.3 CallableStatement (Stored Procedures)

String sql = "{call GetItemPrice(?)}";
CallableStatement cstmt = conn.prepareCall(sql);
cstmt.setInt(1, 1);
ResultSet rs = cstmt.executeQuery();
while (rs.next()) {
    System.out.println("Price: " + rs.getDouble("UnitPrice"));
}

4. Working with ResultSet

A ResultSet holds query results. It supports three types:

4.1 Example: Fetching Data

String sql = "SELECT * FROM Item WHERE UnitPrice > ?";
PreparedStatement pstmt = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
pstmt.setDouble(1, 20000);
ResultSet rs = pstmt.executeQuery();

while (rs.next()) {
    int id = rs.getInt("ItemID");
    String name = rs.getString("Name");
    System.out.println(id + ": " + name);
}

4.2 RowSet (Offline Database Operations)

• Extends ResultSet to work offline (e.g., in a disconnected environment).
• Example: Daraz’s mobile app caches product data for offline browsing.

import javax.sql.rowset.CachedRowSet;

CachedRowSet crs = new CachedRowSetImpl();
crs.populate(rs); // Populate from ResultSet
crs.absolute(2); // Move to row 2
System.out.println(crs.getString("Name"));

5. Transactions in JDBC

Transactions ensure data integrity by grouping operations into atomic units.

5.1 Transaction Methods

5.2 Example: Transferring Funds (Banking)

conn.setAutoCommit(false);
try {
    // Deduct from Account A
    String sql1 = "UPDATE Account SET Balance = Balance - 100 WHERE AccountID = 101";
    stmt.executeUpdate(sql1);

```figure
{"type":"network","nodes":["Account A (₹1000)","Account B (₹500)","Transaction","Database"],"edges":[["Account A","Transaction","withdraws 200"],["Transaction","Account B","deposits 200"],["Transaction","Database","commits"],["Database","Account A","updates balance"],["Database","Account B","updates balance"]],"directed":true,"caption":"Transaction flow in a banking system: Withdrawal, deposit, and commit operations."}

// Add to Account B
String sql2 = "UPDATE Account SET Balance = Balance + 100 WHERE AccountID = 102";
stmt.executeUpdate(sql2);

conn.commit(); // Success: changes saved

} catch (SQLException e) { conn.rollback(); // Failure: revert changes e.printStackTrace(); } finally { conn.setAutoCommit(true); } Real-World Example:

• eSewa uses transactions to ensure funds are deducted from your account and credited to the merchant’s account atomically. If the merchant’s bank fails, eSewa rolls back your deduction.

6. JDBC and GUI (Swing)

GUI apps often interact with databases. Layout managers (e.g., BorderLayout, GridLayout) organize components neatly.

6.1 Example: Customer Data Form

import javax.swing.*;
import java.awt.*;
import java.sql.*;

public class CustomerForm {
    public static void main(String[] args) {
        JFrame frame = new JFrame("Customer Data");
        frame.setLayout(new GridLayout(5, 2));

        // Add components (e.g., JTextFields, JButtons)
        frame.add(new JLabel("Customer ID:"));
        JTextField cidField = new JTextField();
        frame.add(cidField);

        JButton submitBtn = new JButton("Submit");
        frame.add(submitBtn);

        frame.setSize(300, 200);
        frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        frame.setVisible(true);

        // Handle button click
        submitBtn.addActionListener(e -> {
            String cid = cidField.getText();
            try {
                String sql = "INSERT INTO Customer (cid, name) VALUES (?, 'John Doe')";
                PreparedStatement pstmt = conn.prepareStatement(sql);
                pstmt.setString(1, cid);
                pstmt.executeUpdate();
                JOptionPane.showMessageDialog(frame, "Data saved!");
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        });
    }
}

7. Error Handling in JDBC

Common JDBC exceptions:

• SQLException: General database error.
• ClassNotFoundException: JDBC driver not found.
• DataTruncation: Data exceeds column size.

7.1 Example: Handling Errors

try {
    // Database operations
} catch (SQLException e) {
    System.err.println("Database error: " + e.getMessage());
    if (e.getErrorCode() == 1045) { // Authentication failed
        System.err.println("Invalid credentials!");
    }
}

In the real world

1. eSewa’s Transaction Logging

   • Idea: JDBC’s PreparedStatement and transactions ensure that every payment is atomically recorded in the database (either both accounts are updated or neither).
   • How: When you pay via eSewa, the app uses PreparedStatement to insert records into Transactions and UserBalances tables. If the merchant’s bank fails, eSewa rolls back your transaction.

2. Daraz’s Inventory Management

   • Idea: ResultSet and RowSet allow Daraz to fetch and cache product stock levels offline.
   • How: The app queries the database for items in stock (SELECT * FROM Products WHERE Stock > 0), stores results in a CachedRowSet, and displays them even without internet.

3. NEPSE’s Stock Market Data

   • Idea: CallableStatement executes stored procedures to fetch real-time stock prices.
   • How: NEPSE’s backend uses JDBC to call procedures like GetStockPrice(?) (where ? is the stock symbol), returning live data to traders’ apps.

Exam Tip

• Focus on:
  • JDBC architecture (3-tier model, interfaces like DriverManager, Connection).
  • SQL execution (Statement vs. PreparedStatement; always prefer the latter).
  • ResultSet types and when to use each (e.g., TYPE_SCROLL_SENSITIVE for updates).
  • Transactions (commit/rollback, isolation levels).
  • Real-world mapping: Link JDBC to banking (transactions), e-commerce (inventory), or stock markets (data retrieval).
• Common mistakes to avoid:
  • Forgetting to close() Connection, Statement, or ResultSet (leaks resources).
  • Using Statement for dynamic queries (vulnerable to SQL injection).
  • Not handling exceptions properly (e.g., swallowing SQLException).
• Practical question patterns:
  • Write a program to insert/update/delete records (use PreparedStatement).
  • Explain how to fetch and display data from a table (use ResultSet).
  • Describe transaction management in a banking scenario (commit/rollback).
  • Compare Statement and PreparedStatement (security and performance).

Visual Summary:

flowchart TD
    A["Java App"] -->|"JDBC API"| B["DriverManager"]
    B --> C["Connection"]
    C --> D{"SQL Type"}
    D -->|"Static"| E["Statement"]
    D -->|"Dynamic"| F["PreparedStatement"]
    D -->|"Stored Proc"| G["CallableStatement"]
    E --> H["executeQuery() / executeUpdate()"]
    F --> H
    G --> H
    H --> I["ResultSet"]
    I --> J["Fetch Data"]