Introduction to JDBC

While C# utilizes ADO.NET for database connectivity with systems like SQL Server and Oracle, Java employs the JDBC API to enable database access. By implementing the JDBC interfaces, database vendors ensure that Java applications can interact with their respective database systems seamlessly.

JDBC Operational Flow

The JDBC process follows a sequence of well-defined steps to establish connections and perform database operations:

1. Driver Loading: The appropriate database driver must be included in the project. For MySQL, this involves adding the MySQL Connector/J JAR file to the WEB-INF/lib directory.
2. Driver Registration: Once loaded, the driver needs to be registered with the DriverManager, which returns a driver management object specific to that database.
3. Connection Establishment: Create a connection to the database, which may be hosted remotely.
4. Statement Execution: Execute SQL operations such as SELECT, INSERT, UPDATE, or DELETE through Statement objects.
5. Result Processing: Retreive and process the results returned from the dataabse operations.
6. Resource Cleanup: Properly close all database resources to free up system resources.

Basic JDBC Implementation

Here's an example demonstrating basic database connectivity and query execution:

<%@page import="com.mysql.jdbc.Driver"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>



<meta content="text/html; charset=UTF-8" http-equiv="Content-Type"></meta>
<title>JDBC Query Example</title>


<%
    // Declare connection and result variables
    DatabaseConnector dbConnector = null;
    QueryExecutor executor = null;
    DataResult result = null;
    
    try {
        // Initialize database connection
        dbConnector = new DatabaseConnector();
        Connection connection = dbConnector.getConnection("jdbc:mysql://localhost:3306/testdb", "admin", "password");
        
        // Create query executor
        executor = new QueryExecutor(connection);
        
        // Execute query and retrieve results
        result = executor.executeQuery("SELECT name, age, birthday FROM users");
        
        // Process and display results
        while (result.hasNext()) {
            UserRecord record = result.next();
            out.println("Name: " + record.getName() + 
                       " | Age: " + record.getAge() + 
                       " | Birth Date: " + record.getBirthDate());
        }
    } catch (SQLException e) {
        out.println("Database Error: " + e.getMessage());
        e.printStackTrace();
    } finally {
        // Clean up resources
        if (result != null) result.close();
        if (executor != null) executor.close();
        if (dbConnector != null) dbConnector.close();
    }
%>

Parameterized SQL with PreparedStatement

Using Statement objects for SQL queries is vulnerable to injection attacks. PreparedStatement provides a safer approach by using parameterized queries:

<%@page import="com.mysql.jdbc.Driver"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>



<meta content="text/html; charset=UTF-8" http-equiv="Content-Type"></meta>
<title>Parameterized Query Example</title>


<%
    // Database resources
    DatabaseConnection dbConn = null;
    ParameterizedQuery query = null;
    GeneratedKeysResult keys = null;
    
    try {
        // Establish database connection
        dbConn = new DatabaseConnection();
        Connection connection = dbConn.getConnection("jdbc:mysql://localhost:3306/testdb", "admin", "password");
        
        // Create parameterized query
        String sql = "INSERT INTO users (name, age, birthday) VALUES (?, ?, ?)";
        query = new ParameterizedQuery(connection, sql, Statement.RETURN_GENERATED_KEYS);
        
        // Set parameters
        query.setString(1, "john_doe");
        query.setInt(2, 30);
        query.setDate(3, new java.sql.Date(System.currentTimeMillis()));
        
        // Execute update
        int rowsAffected = query.executeUpdate();
        
        if (rowsAffected > 0) {
            out.println("Record added successfully");
            
            // Retrieve generated keys
            keys = query.getGeneratedKeys();
            while (keys.next()) {
                out.println("Generated ID: " + keys.getInt(1));
            }
        }
    } catch (SQLException e) {
        out.println("Error: " + e.getMessage());
        e.printStackTrace();
    } finally {
        // Release resources
        if (keys != null) keys.close();
        if (query != null) query.close();
        if (dbConn != null) dbConn.close();
    }
%>

Batch Processing with Transactions

Batch processing allows multiple SQL statements to be executed together, often used with transactions to maintain data consistency:

<%@page import="com.mysql.jdbc.Driver"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>



<meta content="text/html; charset=UTF-8" http-equiv="Content-Type"></meta>
<title>Batch Processing Example</title>


<%
    // Database components
    ConnectionManager connManager = null;
    BatchOperation batchOp = null;
    
    try {
        // Get connection with batch optimization
        connManager = new ConnectionManager();
        Connection connection = connManager.getConnection("jdbc:mysql://localhost:3306/testdb?rewriteBatchedStatements=true", "admin", "password");
        
        // Prepare batch operation
        String sql = "INSERT INTO users (name, age, birthday) VALUES (?, ?, ?)";
        batchOp = new BatchOperation(connection, sql);
        
        // Disable auto-commit for transaction
        connection.setAutoCommit(false);
        
        // Add multiple records to batch
        for (int i = 0; i < 5; i++) {
            batchOp.setString(1, "user_" + i);
            batchOp.setInt(2, 25 + i);
            batchOp.setDate(3, new java.sql.Date(System.currentTimeMillis()));
            batchOp.addToBatch();
        }
        
        // Execute batch and commit transaction
        int[] results = batchOp.executeBatch();
        connection.commit();
        
        // Report results
        int totalRows = 0;
        for (int result : results) {
            totalRows += result;
        }
        out.println("Total rows inserted: " + totalRows);
        
        // Restore auto-commit
        connection.setAutoCommit(true);
        
    } catch (SQLException e) {
        try {
            // Rollback transaction on error
            if (connManager != null) connManager.getConnection().rollback();
        } catch (SQLException ex) {
            out.println("Rollback failed: " + ex.getMessage());
        }
        out.println("Batch processing error: " + e.getMessage());
        e.printStackTrace();
    } finally {
        // Clean up resources
        if (batchOp != null) batchOp.close();
        if (connManager != null) connManager.close();
    }
%>

Calling Stored Procedures

CallableStatement enables Java applications to invoke stored procedures in the database:

<%@page import="com.mysql.jdbc.Driver"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>



<meta content="text/html; charset=UTF-8" http-equiv="Content-Type"></meta>
<title>Stored Procedure Example</title>


<%
    // Database resources
    DatabaseLink dbLink = null;
    ProcedureCaller procCaller = null;
    ResultSet resultSet = null;
    
    try {
        // Establish connection
        dbLink = new DatabaseLink();
        Connection connection = dbLink.getConnection("jdbc:mysql://localhost:3306/testdb", "admin", "password");
        
        // Prepare stored procedure call
        procCaller = new ProcedureCaller(connection, "{call getUserById(?, ?)}");
        
        // Set input parameter
        procCaller.setInt(1, 3);
        
        // Register output parameter
        procCaller.registerOutParameter(2, Types.INTEGER);
        
        // Execute procedure
        resultSet = procCaller.executeQuery();
        
        // Process result set
        while (resultSet.next()) {
            out.println("User: " + resultSet.getString("name") + 
                       " | Age: " + resultSet.getInt("age") + 
                       " | Birthday: " + resultSet.getDate("birthday"));
        }
        
        // Get output parameter
        int returnValue = procCaller.getInt(2);
        out.println("Procedure returned: " + returnValue);
        
    } catch (SQLException e) {
        out.println("Procedure execution error: " + e.getMessage());
        e.printStackTrace();
    } finally {
        // Release resources
        if (resultSet != null) resultSet.close();
        if (procCaller != null) procCaller.close();
        if (dbLink != null) dbLink.close();
    }
%>

Additional JDBC Features

JDBC provides extensive functionality beyond basic operations, including support for binary data handling, metadata retrieval, and connection pooling. In modern applications, these features are typically encapsulated within utility classes or database frameworks rather than being implemented directly through raw JDBC calls.