JDBC - Java Database Connectivity

What is JDBC?

JDBC (Java Database Connectivity) is Java's standard API for connecting to and interacting with relational databases. It's the foundation upon which all Java database access is built, including JPA, Hibernate, and Spring Data.

JDBC is to databases what JDBC drivers are to specific databases. JDBC provides the API (interfaces), and database vendors provide the implementations (drivers). Your code uses the same JDBC API regardless of whether you're connecting to MySQL, PostgreSQL, Oracle, or any other database.

JDBC Architecture

// JDBC Architecture - Layered Design

┌─────────────────────────────────────────────────────────────┐
│                    YOUR JAVA APPLICATION                     │
│      (Uses JDBC API: Connection, Statement, ResultSet)       │
└─────────────────────────────────────────────────────────────┘
                              │
                              ▼
┌─────────────────────────────────────────────────────────────┐
│                      JDBC API (java.sql)                     │
│   Interfaces: Connection, Statement, PreparedStatement,      │
│               ResultSet, DataSource, Driver                  │
└─────────────────────────────────────────────────────────────┘
                              │
                              ▼
┌─────────────────────────────────────────────────────────────┐
│                   JDBC DRIVER MANAGER                        │
│          (Loads and manages database drivers)                │
└─────────────────────────────────────────────────────────────┘
                              │
        ┌─────────────────────┼─────────────────────┐
        ▼                     ▼                     ▼
┌──────────────┐    ┌──────────────┐    ┌──────────────┐
│ MySQL Driver │    │ PostgreSQL   │    │ Oracle       │
│ (Connector/J)│    │ Driver       │    │ Driver       │
└──────────────┘    └──────────────┘    └──────────────┘
        │                     │                     │
        ▼                     ▼                     ▼
┌──────────────┐    ┌──────────────┐    ┌──────────────┐
│    MySQL     │    │  PostgreSQL  │    │   Oracle     │
│   Database   │    │   Database   │    │   Database   │
└──────────────┘    └──────────────┘    └──────────────┘

Key JDBC Components

Component	Interface/Class	Purpose
Driver	java.sql.Driver	Vendor-specific database driver
DriverManager	java.sql.DriverManager	Loads drivers, creates connections
DataSource	javax.sql.DataSource	Factory for connections (preferred)
Connection	java.sql.Connection	Session with the database
Statement	java.sql.Statement	Execute SQL statements
PreparedStatement	java.sql.PreparedStatement	Parameterized SQL (recommended)
CallableStatement	java.sql.CallableStatement	Call stored procedures
ResultSet	java.sql.ResultSet	Query results (rows)

Setting Up JDBC

Adding the Driver Dependency

<!-- Maven dependencies for common databases -->

<!-- MySQL -->
<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>8.3.0</version>
</dependency>

<!-- PostgreSQL -->
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.7.1</version>
</dependency>

<!-- H2 (in-memory, great for testing) -->
<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <version>2.2.224</version>
</dependency>

<!-- Oracle -->
<dependency>
    <groupId>com.oracle.database.jdbc</groupId>
    <artifactId>ojdbc11</artifactId>
    <version>23.3.0.23.09</version>
</dependency>

<!-- Microsoft SQL Server -->
<dependency>
    <groupId>com.microsoft.sqlserver</groupId>
    <artifactId>mssql-jdbc</artifactId>
    <version>12.4.2.jre11</version>
</dependency>

JDBC URL Formats

// JDBC URL format: jdbc:subprotocol:subname

// MySQL
"jdbc:mysql://localhost:3306/mydb"
"jdbc:mysql://localhost:3306/mydb?useSSL=true&serverTimezone=UTC"

// PostgreSQL
"jdbc:postgresql://localhost:5432/mydb"
"jdbc:postgresql://localhost:5432/mydb?ssl=true"

// H2 (in-memory)
"jdbc:h2:mem:testdb"
// H2 (file-based)
"jdbc:h2:file:./data/mydb"

// Oracle
"jdbc:oracle:thin:@localhost:1521:ORCL"
"jdbc:oracle:thin:@//localhost:1521/servicename"

// SQL Server
"jdbc:sqlserver://localhost:1433;databaseName=mydb"
"jdbc:sqlserver://localhost:1433;databaseName=mydb;encrypt=true"

Basic JDBC Operations

Establishing a Connection

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

public class JdbcBasics {

    // Database connection parameters
    private static final String URL = "jdbc:mysql://localhost:3306/mydb";
    private static final String USER = "root";
    private static final String PASSWORD = "password";

    public static void main(String[] args) {
        // Method 1: Try-with-resources (recommended)
        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD)) {
            System.out.println("Connected successfully!");
            System.out.println("Database: " + conn.getCatalog());
            System.out.println("Auto-commit: " + conn.getAutoCommit());
        } catch (SQLException e) {
            System.err.println("Connection failed: " + e.getMessage());
            e.printStackTrace();
        }
        // Connection automatically closed here
    }

    // Method 2: Manual resource management (legacy)
    public void connectManually() {
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(URL, USER, PASSWORD);
            // Use connection...
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

Executing Queries (SELECT)

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class EmployeeDao {

    // Find all employees
    public List<Employee> findAll() {
        List<Employee> employees = new ArrayList<>();
        String sql = "SELECT id, first_name, last_name, email, salary FROM employees";

        try (Connection conn = getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            while (rs.next()) {
                Employee emp = new Employee();
                emp.setId(rs.getLong("id"));
                emp.setFirstName(rs.getString("first_name"));
                emp.setLastName(rs.getString("last_name"));
                emp.setEmail(rs.getString("email"));
                emp.setSalary(rs.getBigDecimal("salary"));
                employees.add(emp);
            }
        } catch (SQLException e) {
            throw new RuntimeException("Error fetching employees", e);
        }

        return employees;
    }

    // Find by ID using PreparedStatement (ALWAYS use for parameters!)
    public Employee findById(long id) {
        String sql = "SELECT id, first_name, last_name, email, salary FROM employees WHERE id = ?";

        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setLong(1, id);  // Parameter index starts at 1!

            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    Employee emp = new Employee();
                    emp.setId(rs.getLong("id"));
                    emp.setFirstName(rs.getString("first_name"));
                    emp.setLastName(rs.getString("last_name"));
                    emp.setEmail(rs.getString("email"));
                    emp.setSalary(rs.getBigDecimal("salary"));
                    return emp;
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException("Error finding employee", e);
        }

        return null;
    }

    // Search with multiple parameters
    public List<Employee> search(String lastName, BigDecimal minSalary) {
        List<Employee> employees = new ArrayList<>();
        String sql = "SELECT * FROM employees WHERE last_name LIKE ? AND salary >= ?";

        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, lastName + "%");  // Wildcard for LIKE
            pstmt.setBigDecimal(2, minSalary);

            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    employees.add(mapResultSetToEmployee(rs));
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException("Error searching employees", e);
        }

        return employees;
    }

    private Employee mapResultSetToEmployee(ResultSet rs) throws SQLException {
        Employee emp = new Employee();
        emp.setId(rs.getLong("id"));
        emp.setFirstName(rs.getString("first_name"));
        emp.setLastName(rs.getString("last_name"));
        emp.setEmail(rs.getString("email"));
        emp.setSalary(rs.getBigDecimal("salary"));
        return emp;
    }
}

SQL Injection Prevention

NEVER concatenate user input into SQL strings! Always use PreparedStatement with parameters.

// DANGEROUS - SQL Injection vulnerability!
String sql = "SELECT * FROM users WHERE name = '" + userInput + "'";
// If userInput = "'; DROP TABLE users; --" ... disaster!

// SAFE - Use PreparedStatement
String sql = "SELECT * FROM users WHERE name = ?";
pstmt.setString(1, userInput);  // Properly escaped

INSERT, UPDATE, DELETE Operations

INSERT - Creating Records

public class EmployeeDao {

    // Basic INSERT
    public void save(Employee emp) {
        String sql = "INSERT INTO employees (first_name, last_name, email, salary, hire_date) "
                   + "VALUES (?, ?, ?, ?, ?)";

        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, emp.getFirstName());
            pstmt.setString(2, emp.getLastName());
            pstmt.setString(3, emp.getEmail());
            pstmt.setBigDecimal(4, emp.getSalary());
            pstmt.setDate(5, java.sql.Date.valueOf(emp.getHireDate()));

            int rowsAffected = pstmt.executeUpdate();
            System.out.println("Inserted " + rowsAffected + " row(s)");

        } catch (SQLException e) {
            throw new RuntimeException("Error saving employee", e);
        }
    }

    // INSERT and get generated ID
    public long saveAndGetId(Employee emp) {
        String sql = "INSERT INTO employees (first_name, last_name, email, salary) VALUES (?, ?, ?, ?)";

        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {

            pstmt.setString(1, emp.getFirstName());
            pstmt.setString(2, emp.getLastName());
            pstmt.setString(3, emp.getEmail());
            pstmt.setBigDecimal(4, emp.getSalary());

            int rowsAffected = pstmt.executeUpdate();

            if (rowsAffected > 0) {
                try (ResultSet generatedKeys = pstmt.getGeneratedKeys()) {
                    if (generatedKeys.next()) {
                        long id = generatedKeys.getLong(1);
                        emp.setId(id);
                        return id;
                    }
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException("Error saving employee", e);
        }
        return -1;
    }

    // Batch INSERT for multiple records (much faster!)
    public void saveAll(List<Employee> employees) {
        String sql = "INSERT INTO employees (first_name, last_name, email, salary) VALUES (?, ?, ?, ?)";

        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            conn.setAutoCommit(false);  // Start transaction

            for (Employee emp : employees) {
                pstmt.setString(1, emp.getFirstName());
                pstmt.setString(2, emp.getLastName());
                pstmt.setString(3, emp.getEmail());
                pstmt.setBigDecimal(4, emp.getSalary());
                pstmt.addBatch();  // Add to batch
            }

            int[] results = pstmt.executeBatch();  // Execute all at once
            conn.commit();  // Commit transaction

            System.out.println("Inserted " + results.length + " employees");

        } catch (SQLException e) {
            throw new RuntimeException("Error batch inserting employees", e);
        }
    }
}

UPDATE - Modifying Records

// Update single record
public void update(Employee emp) {
    String sql = "UPDATE employees SET first_name = ?, last_name = ?, email = ?, salary = ? WHERE id = ?";

    try (Connection conn = getConnection();
         PreparedStatement pstmt = conn.prepareStatement(sql)) {

        pstmt.setString(1, emp.getFirstName());
        pstmt.setString(2, emp.getLastName());
        pstmt.setString(3, emp.getEmail());
        pstmt.setBigDecimal(4, emp.getSalary());
        pstmt.setLong(5, emp.getId());

        int rowsAffected = pstmt.executeUpdate();

        if (rowsAffected == 0) {
            throw new RuntimeException("Employee not found: " + emp.getId());
        }
    } catch (SQLException e) {
        throw new RuntimeException("Error updating employee", e);
    }
}

// Bulk update with conditional logic
public int giveRaise(long departmentId, BigDecimal percentage) {
    String sql = "UPDATE employees SET salary = salary * (1 + ? / 100) WHERE department_id = ?";

    try (Connection conn = getConnection();
         PreparedStatement pstmt = conn.prepareStatement(sql)) {

        pstmt.setBigDecimal(1, percentage);
        pstmt.setLong(2, departmentId);

        return pstmt.executeUpdate();  // Returns number of rows updated

    } catch (SQLException e) {
        throw new RuntimeException("Error giving raises", e);
    }
}

DELETE - Removing Records

// Delete single record
public boolean delete(long id) {
    String sql = "DELETE FROM employees WHERE id = ?";

    try (Connection conn = getConnection();
         PreparedStatement pstmt = conn.prepareStatement(sql)) {

        pstmt.setLong(1, id);
        int rowsAffected = pstmt.executeUpdate();

        return rowsAffected > 0;  // true if deleted

    } catch (SQLException e) {
        throw new RuntimeException("Error deleting employee", e);
    }
}

// Soft delete (recommended pattern)
public void softDelete(long id) {
    String sql = "UPDATE employees SET active = false, deleted_at = ? WHERE id = ?";

    try (Connection conn = getConnection();
         PreparedStatement pstmt = conn.prepareStatement(sql)) {

        pstmt.setTimestamp(1, Timestamp.from(Instant.now()));
        pstmt.setLong(2, id);
        pstmt.executeUpdate();

    } catch (SQLException e) {
        throw new RuntimeException("Error soft-deleting employee", e);
    }
}

Transaction Management

Transactions group multiple database operations into a single atomic unit. Either all operations succeed, or none do.

public class TransferService {

    // Transfer money between accounts (classic transaction example)
    public void transferMoney(long fromAccountId, long toAccountId, BigDecimal amount) {
        Connection conn = null;

        try {
            conn = getConnection();
            conn.setAutoCommit(false);  // Start transaction

            // Step 1: Debit from source account
            String debitSql = "UPDATE accounts SET balance = balance - ? WHERE id = ? AND balance >= ?";
            try (PreparedStatement debitStmt = conn.prepareStatement(debitSql)) {
                debitStmt.setBigDecimal(1, amount);
                debitStmt.setLong(2, fromAccountId);
                debitStmt.setBigDecimal(3, amount);  // Check sufficient funds

                int debitRows = debitStmt.executeUpdate();
                if (debitRows == 0) {
                    throw new RuntimeException("Insufficient funds or account not found");
                }
            }

            // Step 2: Credit to destination account
            String creditSql = "UPDATE accounts SET balance = balance + ? WHERE id = ?";
            try (PreparedStatement creditStmt = conn.prepareStatement(creditSql)) {
                creditStmt.setBigDecimal(1, amount);
                creditStmt.setLong(2, toAccountId);

                int creditRows = creditStmt.executeUpdate();
                if (creditRows == 0) {
                    throw new RuntimeException("Destination account not found");
                }
            }

            // Step 3: Record the transaction
            String logSql = "INSERT INTO transactions (from_account, to_account, amount, timestamp) VALUES (?, ?, ?, ?)";
            try (PreparedStatement logStmt = conn.prepareStatement(logSql)) {
                logStmt.setLong(1, fromAccountId);
                logStmt.setLong(2, toAccountId);
                logStmt.setBigDecimal(3, amount);
                logStmt.setTimestamp(4, Timestamp.from(Instant.now()));
                logStmt.executeUpdate();
            }

            // All operations successful - commit!
            conn.commit();
            System.out.println("Transfer successful!");

        } catch (Exception e) {
            // Something went wrong - rollback everything!
            if (conn != null) {
                try {
                    conn.rollback();
                    System.out.println("Transaction rolled back");
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            throw new RuntimeException("Transfer failed", e);

        } finally {
            if (conn != null) {
                try {
                    conn.setAutoCommit(true);  // Reset for connection pool
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

// Using Savepoints for partial rollback
public void processOrderWithSavepoint(Order order) throws SQLException {
    Connection conn = getConnection();
    Savepoint savepoint = null;

    try {
        conn.setAutoCommit(false);

        // Create order header
        insertOrder(conn, order);
        savepoint = conn.setSavepoint("orderCreated");

        // Try to add items
        for (OrderItem item : order.getItems()) {
            try {
                insertOrderItem(conn, item);
            } catch (SQLException e) {
                // Rollback to savepoint, try alternative
                conn.rollback(savepoint);
                insertBackorderItem(conn, item);
            }
        }

        conn.commit();

    } catch (SQLException e) {
        conn.rollback();
        throw e;
    } finally {
        conn.setAutoCommit(true);
        conn.close();
    }
}

Transaction Isolation Levels

// Set isolation level on connection
conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);

// Isolation levels (from least to most restrictive):

// READ_UNCOMMITTED - Can see uncommitted changes (dirty reads)
conn.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);

// READ_COMMITTED - Only see committed changes (default for most DBs)
conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);

// REPEATABLE_READ - Same query returns same results within transaction
conn.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);

// SERIALIZABLE - Full isolation, transactions run as if serial
conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);

Working with ResultSet

ResultSet Navigation and Types

// ResultSet types
// TYPE_FORWARD_ONLY - Can only move forward (default, most efficient)
// TYPE_SCROLL_INSENSITIVE - Can scroll, doesn't see changes
// TYPE_SCROLL_SENSITIVE - Can scroll, sees changes

// ResultSet concurrency
// CONCUR_READ_ONLY - Cannot update through ResultSet (default)
// CONCUR_UPDATABLE - Can update through ResultSet

// Creating scrollable, updatable ResultSet
Statement stmt = conn.createStatement(
    ResultSet.TYPE_SCROLL_INSENSITIVE,
    ResultSet.CONCUR_UPDATABLE
);

ResultSet rs = stmt.executeQuery("SELECT * FROM employees");

// Navigation methods
rs.next();           // Move to next row
rs.previous();       // Move to previous row (scrollable only)
rs.first();          // Move to first row
rs.last();           // Move to last row
rs.absolute(5);      // Move to row 5
rs.relative(-2);     // Move 2 rows backward
rs.beforeFirst();    // Move before first row
rs.afterLast();      // Move after last row

// Position checks
rs.isFirst();        // Are we on first row?
rs.isLast();         // Are we on last row?
rs.isBeforeFirst();  // Are we before first row?
rs.isAfterLast();    // Are we after last row?
rs.getRow();         // Current row number

Getting Data from ResultSet

// Get by column name (preferred - more readable)
long id = rs.getLong("id");
String name = rs.getString("first_name");
BigDecimal salary = rs.getBigDecimal("salary");
Date hireDate = rs.getDate("hire_date");
Timestamp createdAt = rs.getTimestamp("created_at");
boolean active = rs.getBoolean("active");
byte[] photo = rs.getBytes("photo");

// Get by column index (slightly faster, but fragile)
long id = rs.getLong(1);
String name = rs.getString(2);

// Handle NULL values
int managerId = rs.getInt("manager_id");
if (rs.wasNull()) {
    // The value was NULL in the database
    managerId = -1;  // Or handle differently
}

// Better approach for nullable primitives
Integer managerId = rs.getObject("manager_id", Integer.class);  // Returns null if NULL

// Get metadata about the ResultSet
ResultSetMetaData metaData = rs.getMetaData();
int columnCount = metaData.getColumnCount();

for (int i = 1; i <= columnCount; i++) {
    System.out.println("Column: " + metaData.getColumnName(i));
    System.out.println("Type: " + metaData.getColumnTypeName(i));
    System.out.println("Nullable: " + metaData.isNullable(i));
}

Updatable ResultSet

// Update through ResultSet (rarely used, but possible)
Statement stmt = conn.createStatement(
    ResultSet.TYPE_SCROLL_SENSITIVE,
    ResultSet.CONCUR_UPDATABLE
);

ResultSet rs = stmt.executeQuery("SELECT * FROM employees WHERE department_id = 1");

while (rs.next()) {
    BigDecimal currentSalary = rs.getBigDecimal("salary");
    rs.updateBigDecimal("salary", currentSalary.multiply(new BigDecimal("1.10")));
    rs.updateRow();  // Apply changes to database
}

// Insert new row through ResultSet
rs.moveToInsertRow();
rs.updateString("first_name", "New");
rs.updateString("last_name", "Employee");
rs.updateString("email", "new@example.com");
rs.updateBigDecimal("salary", new BigDecimal("50000"));
rs.insertRow();
rs.moveToCurrentRow();

// Delete current row
rs.deleteRow();

Stored Procedures with CallableStatement

// Assume we have this stored procedure in MySQL:
/*
DELIMITER //
CREATE PROCEDURE get_employee_count(
    IN dept_id INT,
    OUT emp_count INT
)
BEGIN
    SELECT COUNT(*) INTO emp_count
    FROM employees
    WHERE department_id = dept_id;
END //
DELIMITER ;
*/

// Calling stored procedure from Java
public int getEmployeeCount(int departmentId) {
    String sql = "{CALL get_employee_count(?, ?)}";

    try (Connection conn = getConnection();
         CallableStatement cstmt = conn.prepareCall(sql)) {

        // Set IN parameter
        cstmt.setInt(1, departmentId);

        // Register OUT parameter
        cstmt.registerOutParameter(2, Types.INTEGER);

        // Execute
        cstmt.execute();

        // Get OUT parameter value
        return cstmt.getInt(2);

    } catch (SQLException e) {
        throw new RuntimeException("Error calling stored procedure", e);
    }
}

// Stored procedure returning ResultSet
public List<Employee> getEmployeesByDepartment(int deptId) {
    String sql = "{CALL get_employees_by_dept(?)}";
    List<Employee> employees = new ArrayList<>();

    try (Connection conn = getConnection();
         CallableStatement cstmt = conn.prepareCall(sql)) {

        cstmt.setInt(1, deptId);

        try (ResultSet rs = cstmt.executeQuery()) {
            while (rs.next()) {
                employees.add(mapResultSetToEmployee(rs));
            }
        }

    } catch (SQLException e) {
        throw new RuntimeException("Error calling stored procedure", e);
    }

    return employees;
}

Best Practices and Patterns

DAO Pattern (Data Access Object)

// Interface defining data access operations
public interface EmployeeDao {
    Employee findById(long id);
    List<Employee> findAll();
    List<Employee> findByDepartment(long deptId);
    void save(Employee employee);
    void update(Employee employee);
    void delete(long id);
}

// JDBC implementation
public class JdbcEmployeeDao implements EmployeeDao {

    private final DataSource dataSource;

    public JdbcEmployeeDao(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    private Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }

    @Override
    public Employee findById(long id) {
        String sql = "SELECT * FROM employees WHERE id = ?";

        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setLong(1, id);

            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    return mapRow(rs);
                }
            }
        } catch (SQLException e) {
            throw new DataAccessException("Error finding employee", e);
        }
        return null;
    }

    private Employee mapRow(ResultSet rs) throws SQLException {
        return new Employee(
            rs.getLong("id"),
            rs.getString("first_name"),
            rs.getString("last_name"),
            rs.getString("email"),
            rs.getBigDecimal("salary")
        );
    }
}

// Custom exception for data access errors
public class DataAccessException extends RuntimeException {
    public DataAccessException(String message, Throwable cause) {
        super(message, cause);
    }
}

Connection Utility Class

public class DatabaseUtil {

    private static DataSource dataSource;

    static {
        // Initialize connection pool (using HikariCP)
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:mysql://localhost:3306/mydb");
        config.setUsername("user");
        config.setPassword("password");
        config.setMaximumPoolSize(10);
        config.setMinimumIdle(2);
        config.setConnectionTimeout(30000);
        config.setIdleTimeout(600000);

        dataSource = new HikariDataSource(config);
    }

    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }

    public static DataSource getDataSource() {
        return dataSource;
    }

    // Execute query with automatic resource management
    public static <T> T executeQuery(String sql, ResultSetHandler<T> handler, Object... params) {
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            for (int i = 0; i < params.length; i++) {
                pstmt.setObject(i + 1, params[i]);
            }

            try (ResultSet rs = pstmt.executeQuery()) {
                return handler.handle(rs);
            }
        } catch (SQLException e) {
            throw new DataAccessException("Query failed: " + sql, e);
        }
    }

    // Execute update with automatic resource management
    public static int executeUpdate(String sql, Object... params) {
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            for (int i = 0; i < params.length; i++) {
                pstmt.setObject(i + 1, params[i]);
            }

            return pstmt.executeUpdate();
        } catch (SQLException e) {
            throw new DataAccessException("Update failed: " + sql, e);
        }
    }
}

@FunctionalInterface
public interface ResultSetHandler<T> {
    T handle(ResultSet rs) throws SQLException;
}

// Usage example
List<Employee> employees = DatabaseUtil.executeQuery(
    "SELECT * FROM employees WHERE department_id = ?",
    rs -> {
        List<Employee> list = new ArrayList<>();
        while (rs.next()) {
            list.add(mapRow(rs));
        }
        return list;
    },
    departmentId
);

Common Pitfalls and Solutions

Resource Leaks

// BAD - Resources never closed!
public List<Employee> badFindAll() {
    Connection conn = DriverManager.getConnection(url, user, pass);
    Statement stmt = conn.createStatement();
    ResultSet rs = stmt.executeQuery("SELECT * FROM employees");
    // If exception occurs here, resources leak!
    return mapResults(rs);
}

// GOOD - Use try-with-resources
public List<Employee> goodFindAll() {
    try (Connection conn = getConnection();
         Statement stmt = conn.createStatement();
         ResultSet rs = stmt.executeQuery("SELECT * FROM employees")) {

        return mapResults(rs);
    } catch (SQLException e) {
        throw new DataAccessException("Query failed", e);
    }
}

N+1 Query Problem

// BAD - N+1 queries (1 for employees + N for departments)
List<Employee> employees = findAllEmployees();  // 1 query
for (Employee emp : employees) {
    Department dept = findDepartmentById(emp.getDepartmentId());  // N queries!
    emp.setDepartment(dept);
}

// GOOD - Single query with JOIN
String sql = """
    SELECT e.*, d.name as dept_name
    FROM employees e
    LEFT JOIN departments d ON e.department_id = d.id
    """;
// 1 query for everything!

Handling Large Result Sets

// Set fetch size to prevent loading millions of rows into memory
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setFetchSize(100);  // Fetch 100 rows at a time

// Or use streaming (database specific)
// MySQL: add ?useCursorFetch=true to URL and set fetch size

// For very large exports, consider:
// 1. Pagination with LIMIT/OFFSET
// 2. Cursor-based pagination (WHERE id > lastId LIMIT 1000)
// 3. Database-specific streaming features

Summary

JDBC: Java's standard API for database connectivity
Connection: Session with database, always close it!
PreparedStatement: Always use for parameterized queries (SQL injection prevention)
ResultSet: Query results, iterate with next()
Transactions: setAutoCommit(false), commit(), rollback()
Try-with-resources: Always use to prevent resource leaks
Connection Pooling: Use DataSource (HikariCP) in production
DAO Pattern: Encapsulate data access logic