JDBC Database Programming: Connecting Java to SQL Databases

TAKEAWAYS:

• JDBC provides a standardized API to interact with relational databases from Java using SQL, abstracting vendor-specific details.
• A JDBC driver acts as a bridge between Java code and database systems (MySQL, Oracle, PostgreSQL, etc.).
• CRUD operations (Create, Read, Update, Delete) are performed via Statement, PreparedStatement, and CallableStatement interfaces.
• Result sets from queries can be processed row-by-row using ResultSet methods like next(), getString(), and getInt().
• Transactions ensure data integrity by grouping multiple operations into atomic units using commit() and rollback().
• Connection pooling improves performance by reusing database connections instead of creating new ones for each request.

JDBC Architecture and Components

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

graph LR
    A["Java Application"] -->|"JDBC API"| B["JDBC Driver Manager"]
    B -->|"Driver Selection"| C["JDBC Driver"]
    C -->|"Database Protocol"| D["Database Server"]
    D -->|"SQL Execution"| E["Database"]

Key Components:

1. JDBC API: Provides interfaces and classes (Connection, Statement, ResultSet, etc.) for database operations.
2. JDBC Driver Manager: Loads and manages database drivers dynamically.
3. JDBC Driver: Implements vendor-specific protocols (e.g., MySQL Connector/J, Oracle JDBC Driver).
4. Database: Stores and retrieves data (e.g., MySQL, PostgreSQL, Oracle).

How JDBC Works:

1. Register the Driver: Load the appropriate JDBC driver class.
2. Establish Connection: Use DriverManager.getConnection() to connect to the database.
3. Create Statement: Generate SQL queries using Connection.createStatement().
4. Execute Query: Use Statement.executeQuery() (for SELECT) or executeUpdate() (for INSERT/UPDATE/DELETE).
5. Process Results: Retrieve data from ResultSet objects.
6. Close Resources: Release connections, statements, and result sets to avoid memory leaks.

Step-by-Step JDBC Connection Example

Let’s connect to a MySQL database and execute a simple query. Assume we have a table employees with columns id, name, and salary.

Code Example:

import java.sql.*;

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

        try {
            // 1. Register the driver (optional in JDBC 4.0+)
            Class.forName("com.mysql.cj.jdbc.Driver");

            // 2. Establish connection
            Connection conn = DriverManager.getConnection(url, username, password);

            // 3. Create statement
            Statement stmt = conn.createStatement();

            // 4. Execute query
            ResultSet rs = stmt.executeQuery("SELECT * FROM employees");

            // 5. Process results
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                double salary = rs.getDouble("salary");
                System.out.println("ID: " + id + ", Name: " + name + ", Salary: " + salary);
            }

            // 6. Close resources
            rs.close();
            stmt.close();
            conn.close();
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }
}

State After Each Step (Visual Trace):

In the Real World

1. eSewa (Nepal):

   • Use of JDBC: eSewa’s backend Java applications use JDBC to interact with databases storing user transactions, bill payments, and recharge records.
   • How: When a user pays an electricity bill via eSewa, the Java backend uses JDBC to:
     • Check the user’s balance (SELECT balance FROM users WHERE id = ?).
     • Deduct the bill amount (UPDATE users SET balance = balance - ? WHERE id = ?).
     • Log the transaction (INSERT INTO transactions VALUES (?, ?, ?)).

2. Khalti (Nepal):

   • Use of JDBC: Khalti’s payment gateway uses JDBC to manage merchant accounts, transaction histories, and fraud detection rules.
   • How: When a merchant queries for sales data, the Java backend fetches records from a PostgreSQL database using JDBC:

     PreparedStatement stmt = conn.prepareStatement(
         "SELECT product_name, amount FROM sales WHERE merchant_id = ? AND date > ?");
     stmt.setInt(1, merchantId);
     stmt.setDate(2, startDate);
     ResultSet rs = stmt.executeQuery();
     

3. Ncell (Nepal):

   • Use of JDBC: Ncell’s customer service portal uses JDBC to retrieve and update subscriber details (e.g., plan changes, bill inquiries).
   • How: A customer service agent might run:

     SELECT plan_name, validity FROM subscribers WHERE phone_number = '98XXXXXXXX'
     

     via JDBC to check a subscriber’s current plan before upgrading them.

JDBC Statement Types

JDBC provides three types of statements for executing SQL queries:

Example: PreparedStatement (Safe from SQL Injection)

String sql = "INSERT INTO employees (name, salary) VALUES (?, ?)";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, "John Doe");
pstmt.setDouble(2, 50000.0);
int rowsAffected = pstmt.executeUpdate();

State After Parameter Binding:

Transaction Management in JDBC

Transactions ensure that a group of database operations either all succeed or all fail (atomicity). JDBC supports transactions via:

• Connection.setAutoCommit(false): Disables auto-commit mode.
• commit(): Saves changes permanently.
• rollback(): Reverts changes if an error occurs.

Example: Transferring Money Between Accounts

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

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

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

    conn.commit(); // Success: transaction committed
} catch (SQLException e) {
    conn.rollback(); // Failure: transaction rolled back
    e.printStackTrace();
} finally {
    conn.setAutoCommit(true);
    stmt1.close();
    stmt2.close();
    conn.close();
}

State After Each Transaction Step:

ResultSet Processing

A ResultSet object holds the data returned by a SELECT query. It can be processed in three ways:

1. Forward-only: Default mode (read-only, moves forward only).
2. Scroll-insensitive: Can move backward/forward but does not reflect changes in the database.
3. Scroll-sensitive: Reflects changes made by other transactions (requires ResultSet.TYPE_SCROLL_INSENSITIVE or TYPE_SCROLL_SENSITIVE).

Example: Scrollable ResultSet

String sql = "SELECT * FROM employees";
ResultSet rs = stmt.executeQuery(sql);
rs.setType(ResultSet.TYPE_SCROLL_INSENSITIVE);
rs.setConcurrency(ResultSet.CONCUR_READ_ONLY);

// Move to the last row
rs.last();
System.out.println("Last employee: " + rs.getString("name"));

// Move to the first row
rs.first();
System.out.println("First employee: " + rs.getString("name"));

State After Navigation:

Connection Pooling

Creating a new database connection for every request is inefficient. Connection pooling reuses existing connections, reducing overhead.

How Connection Pooling Works:

1. A pool of connections is created when the application starts.
2. When a request comes, a connection is borrowed from the pool.
3. After use, the connection is returned to the pool instead of being closed.

Example: Using HikariCP (Popular Pooling Library)

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

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

HikariDataSource dataSource = new HikariDataSource(config);

try (Connection conn = dataSource.getConnection()) {
    // Use the connection
    Statement stmt = conn.createStatement();
    ResultSet rs = stmt.executeQuery("SELECT * FROM employees");
    // Process results...
} catch (SQLException e) {
    e.printStackTrace();
}

State After Pooling Operations:

Batch Processing in JDBC

Batch processing allows executing multiple SQL statements efficiently by reducing network overhead.

Example: Inserting Multiple Rows in a Batch

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

pstmt.setString(1, "Alice");
pstmt.setDouble(2, 50000.0);
pstmt.addBatch();

pstmt.setString(1, "Bob");
pstmt.setDouble(2, 60000.0);
pstmt.addBatch();

int[] updateCounts = pstmt.executeBatch(); // Executes all in one go
System.out.println("Rows inserted: " + updateCounts.length);

State After Batch Execution:

JDBC Best Practices

1. Use try-with-resources: Ensures resources (Connection, Statement, ResultSet) are closed automatically.

   try (Connection conn = DriverManager.getConnection(url);
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * FROM employees")) {
       // Process results
   } catch (SQLException e) {
       e.printStackTrace();
   }
   

2. Always Use PreparedStatement: Prevents SQL injection attacks.
3. Enable Connection Pooling: Improves performance in web applications.
4. Handle Exceptions Properly: Use specific exception types (SQLException) and log errors.
5. Close Resources in Reverse Order: Close ResultSet → Statement → Connection.
6. Use Transactions for Critical Operations: Ensures data integrity.

Exam Tip

1. Understand JDBC Architecture: Be able to draw the four-tier architecture and explain the role of each component.
2. CRUD Operations: Know how to perform INSERT, UPDATE, DELETE, and SELECT using Statement and PreparedStatement.
3. ResultSet Processing: Practice iterating over ResultSet objects and retrieving data using getInt(), getString(), etc.
4. Transaction Management: Memorize the steps for commit() and rollback() and when to use them.
5. Connection Pooling: Explain why pooling is used and how libraries like HikariCP work.
6. Batch Processing: Know when and how to use addBatch() and executeBatch().
7. SQL Injection: Always prefer PreparedStatement over Statement to avoid security vulnerabilities.
8. Real-World Scenarios: Be ready to relate JDBC concepts to applications like eSewa, Khalti, or banking systems (e.g., loan processing, transaction logging).

Visual Summary of JDBC Workflow:

flowchart TD
    A["Java Application"] --> B["Load JDBC Driver"]
    B --> C["Establish Connection"]
    C --> D["Create Statement"]
    D --> E["Execute Query"]
    E --> F["Process ResultSet"]
    F --> G["Close Resources"]
    G --> H["End"]
    D -->|"PreparedStatement"| I["Parameterized Query"]
    E -->|"Transaction"| J["Commit/Rollback"]