JDBC (Java Database Connectivity) serves as the standard API for database interactions in Java applications. It establishes a unified interface for accessing various relational database systems through a driver-based architecture. Database vendors implement specific drivers that translate JDBC calls into database-native protocols.

Core API Components

The JDBC API consists of several fundamental interfaces and classes:

• DriverManager: Handles driver registration and connection establishment
• Connection: Represents an active session with the database server
• Statement and PreparedStatement: Execute SQL commadns against the database
• ResultSet: Encapsulates query results in a tabular format

Database Setup

Create a sample database schema to demonstrate JDBC operations:

CREATE DATABASE IF NOT EXISTS company_db;
USE company_db;

CREATE TABLE IF NOT EXISTS employees (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    full_name VARCHAR(50),
    years INT,
    department VARCHAR(30)
);

INSERT INTO employees VALUES 
    (NULL, 'Alice Johnson', 32, 'Engineering'),
    (NULL, 'Bob Smith', 28, 'Marketing'),
    (NULL, 'Carol White', 35, 'Sales');

Retrieving Data

The following example demonstrates establishing a connection and executing a SELECT query:

package com.example.db;

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

public class DatabaseClient {
    public static void main(String[] args) {
        String jdbcUrl = "jdbc:mysql://localhost:3306/company_db";
        String username = "root";
        String password = "secure_pass";
        
        try (Connection dbConnection = DriverManager.getConnection(jdbcUrl, username, password);
             Statement sqlStatement = dbConnection.createStatement();
             ResultSet queryResults = sqlStatement.executeQuery("SELECT * FROM employees")) {
            
            while (queryResults.next()) {
                int identifier = queryResults.getInt("emp_id");
                String name = queryResults.getString("full_name");
                int age = queryResults.getInt("years");
                String dept = queryResults.getString("department");
                
                System.out.printf("%d\t%s\t%d\t%s%n", identifier, name, age, dept);
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }
}

Note that with JDBC 4.0 and later, explicit driver regisrtation using Class.forName() or DriverManager.registerDriver() is unnecessary, as the driver loads automatically when the application attempts to establish a connection.

Modifying Data

For INSERT, UPDATE, and DELETE operations, use executeUpdate() which returns the number of affected rows:

package com.example.db;

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

public class DataModifier {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/company_db";
        
        try (Connection conn = DriverManager.getConnection(url, "root", "secure_pass");
             Statement stmt = conn.createStatement()) {
            
            // Insert new record
            int inserted = stmt.executeUpdate(
                "INSERT INTO employees VALUES (NULL, 'David Brown', 29, 'HR')"
            );
            System.out.println("Rows inserted: " + inserted);
            
            // Update existing record
            int updated = stmt.executeUpdate(
                "UPDATE employees SET years = 33 WHERE full_name = 'Alice Johnson'"
            );
            System.out.println("Rows updated: " + updated);
            
            // Delete record
            int deleted = stmt.executeUpdate(
                "DELETE FROM employees WHERE full_name = 'Bob Smith'"
            );
            System.out.println("Rows deleted: " + deleted);
            
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

The executeUpdate() method returns an integer representing the row count affected by the SQL statement, making it suitable for data manipulation operations where no result set is expected.