1. java
  2. /enterprise
  3. /jdbc

Java JDBC and Database Programming

JDBC (Java Database Connectivity)

JDBC is Java's standard API for database connectivity, providing a uniform interface for accessing relational databases. It enables Java applications to execute SQL statements, retrieve results, and manage database transactions across different database vendors.

Table of Contents

JDBC Fundamentals

Core JDBC Components

JDBC consists of several key interfaces:

  • DriverManager: Manages database drivers
  • Connection: Represents database connection
  • Statement: Executes SQL statements
  • PreparedStatement: Pre-compiled SQL statements
  • CallableStatement: Executes stored procedures
  • ResultSet: Contains query results

Basic JDBC Setup

import java.sql.*;

public class JDBCExample {
    // Database configuration
    private static final String URL = "jdbc:mysql://localhost:3306/mydb";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "password";
    
    static {
        try {
            // Load MySQL JDBC driver (optional for JDBC 4.0+)
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(URL, USERNAME, PASSWORD);
    }
    
    public static void main(String[] args) {
        try (Connection conn = getConnection()) {
            System.out.println("Database connected successfully!");
            System.out.println("Database: " + conn.getCatalog());
            System.out.println("URL: " + conn.getMetaData().getURL());
        } catch (SQLException e) {
            System.err.println("Connection failed: " + e.getMessage());
        }
    }
}

Connection Management

Database Configuration Properties

import java.util.Properties;

public class DatabaseConfig {
    private static Properties getConnectionProperties() {
        Properties props = new Properties();
        props.setProperty("user", "myuser");
        props.setProperty("password", "mypassword");
        props.setProperty("useSSL", "false");
        props.setProperty("serverTimezone", "UTC");
        props.setProperty("allowPublicKeyRetrieval", "true");
        props.setProperty("autoReconnect", "true");
        props.setProperty("characterEncoding", "utf8");
        return props;
    }
    
    public static Connection getConnection() throws SQLException {
        String url = "jdbc:mysql://localhost:3306/mydb";
        return DriverManager.getConnection(url, getConnectionProperties());
    }
    
    // Alternative connection methods
    public static Connection getPostgreSQLConnection() throws SQLException {
        String url = "jdbc:postgresql://localhost:5432/mydb";
        Properties props = new Properties();
        props.setProperty("user", "postgres");
        props.setProperty("password", "password");
        props.setProperty("ssl", "false");
        return DriverManager.getConnection(url, props);
    }
    
    public static Connection getOracleConnection() throws SQLException {
        String url = "jdbc:oracle:thin:@localhost:1521:xe";
        return DriverManager.getConnection(url, "hr", "password");
    }
}

Connection Utility Class

public class DatabaseUtil {
    private static final String CONFIG_FILE = "database.properties";
    private static Properties dbProps;
    
    static {
        loadDatabaseProperties();
    }
    
    private static void loadDatabaseProperties() {
        dbProps = new Properties();
        try (InputStream input = DatabaseUtil.class.getClassLoader()
                .getResourceAsStream(CONFIG_FILE)) {
            if (input != null) {
                dbProps.load(input);
            }
        } catch (IOException e) {
            System.err.println("Error loading database properties: " + e.getMessage());
        }
    }
    
    public static Connection getConnection() throws SQLException {
        String url = dbProps.getProperty("db.url");
        String username = dbProps.getProperty("db.username");
        String password = dbProps.getProperty("db.password");
        
        return DriverManager.getConnection(url, username, password);
    }
    
    public static void closeQuietly(AutoCloseable resource) {
        if (resource != null) {
            try {
                resource.close();
            } catch (Exception e) {
                System.err.println("Error closing resource: " + e.getMessage());
            }
        }
    }
    
    public static void closeQuietly(Connection conn, Statement stmt, ResultSet rs) {
        closeQuietly(rs);
        closeQuietly(stmt);
        closeQuietly(conn);
    }
}

Executing SQL Statements

Basic Statement Operations

public class StatementExamples {
    
    public void createTable() {
        String sql = """
            CREATE TABLE IF NOT EXISTS users (
                id INT AUTO_INCREMENT PRIMARY KEY,
                username VARCHAR(50) UNIQUE NOT NULL,
                email VARCHAR(100) UNIQUE NOT NULL,
                password VARCHAR(255) NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                status ENUM('ACTIVE', 'INACTIVE', 'SUSPENDED') DEFAULT 'ACTIVE'
            )
            """;
        
        try (Connection conn = DatabaseUtil.getConnection();
             Statement stmt = conn.createStatement()) {
            
            stmt.executeUpdate(sql);
            System.out.println("Table 'users' created successfully");
            
        } catch (SQLException e) {
            System.err.println("Error creating table: " + e.getMessage());
        }
    }
    
    public void insertUser(String username, String email, String password) {
        String sql = String.format(
            "INSERT INTO users (username, email, password) VALUES ('%s', '%s', '%s')",
            username, email, password
        );
        
        try (Connection conn = DatabaseUtil.getConnection();
             Statement stmt = conn.createStatement()) {
            
            int rowsAffected = stmt.executeUpdate(sql);
            System.out.println("Inserted " + rowsAffected + " row(s)");
            
        } catch (SQLException e) {
            System.err.println("Error inserting user: " + e.getMessage());
        }
    }
    
    public void updateUserStatus(String username, String newStatus) {
        String sql = String.format(
            "UPDATE users SET status = '%s' WHERE username = '%s'",
            newStatus, username
        );
        
        try (Connection conn = DatabaseUtil.getConnection();
             Statement stmt = conn.createStatement()) {
            
            int rowsAffected = stmt.executeUpdate(sql);
            if (rowsAffected > 0) {
                System.out.println("User status updated successfully");
            } else {
                System.out.println("No user found with username: " + username);
            }
            
        } catch (SQLException e) {
            System.err.println("Error updating user: " + e.getMessage());
        }
    }
    
    public void deleteUser(String username) {
        String sql = "DELETE FROM users WHERE username = '" + username + "'";
        
        try (Connection conn = DatabaseUtil.getConnection();
             Statement stmt = conn.createStatement()) {
            
            int rowsAffected = stmt.executeUpdate(sql);
            System.out.println("Deleted " + rowsAffected + " row(s)");
            
        } catch (SQLException e) {
            System.err.println("Error deleting user: " + e.getMessage());
        }
    }
}

Result Set Handling

Reading Query Results

public class ResultSetExamples {
    
    public void displayAllUsers() {
        String sql = "SELECT * FROM users ORDER BY created_at DESC";
        
        try (Connection conn = DatabaseUtil.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            
            System.out.println("All Users:");
            System.out.println("-".repeat(80));
            System.out.printf("%-5s %-15s %-25s %-10s %-20s%n", 
                            "ID", "Username", "Email", "Status", "Created");
            System.out.println("-".repeat(80));
            
            while (rs.next()) {
                int id = rs.getInt("id");
                String username = rs.getString("username");
                String email = rs.getString("email");
                String status = rs.getString("status");
                Timestamp createdAt = rs.getTimestamp("created_at");
                
                System.out.printf("%-5d %-15s %-25s %-10s %-20s%n",
                                id, username, email, status, createdAt);
            }
            
        } catch (SQLException e) {
            System.err.println("Error retrieving users: " + e.getMessage());
        }
    }
    
    public User getUserByUsername(String username) {
        String sql = "SELECT * FROM users WHERE username = ?";
        
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setString(1, username);
            
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    return mapResultSetToUser(rs);
                }
            }
            
        } catch (SQLException e) {
            System.err.println("Error retrieving user: " + e.getMessage());
        }
        
        return null;
    }
    
    public List<User> getUsersByStatus(String status) {
        String sql = "SELECT * FROM users WHERE status = ? ORDER BY username";
        List<User> users = new ArrayList<>();
        
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setString(1, status);
            
            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    users.add(mapResultSetToUser(rs));
                }
            }
            
        } catch (SQLException e) {
            System.err.println("Error retrieving users by status: " + e.getMessage());
        }
        
        return users;
    }
    
    private User mapResultSetToUser(ResultSet rs) throws SQLException {
        User user = new User();
        user.setId(rs.getInt("id"));
        user.setUsername(rs.getString("username"));
        user.setEmail(rs.getString("email"));
        user.setPassword(rs.getString("password"));
        user.setStatus(rs.getString("status"));
        user.setCreatedAt(rs.getTimestamp("created_at").toLocalDateTime());
        return user;
    }
    
    // Aggregation queries
    public void getUserStatistics() {
        String sql = """
            SELECT 
                status,
                COUNT(*) as user_count,
                MIN(created_at) as oldest_user,
                MAX(created_at) as newest_user
            FROM users 
            GROUP BY status
            ORDER BY user_count DESC
            """;
        
        try (Connection conn = DatabaseUtil.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            
            System.out.println("User Statistics:");
            while (rs.next()) {
                String status = rs.getString("status");
                int count = rs.getInt("user_count");
                Timestamp oldest = rs.getTimestamp("oldest_user");
                Timestamp newest = rs.getTimestamp("newest_user");
                
                System.out.printf("Status: %s, Count: %d, Oldest: %s, Newest: %s%n",
                                status, count, oldest, newest);
            }
            
        } catch (SQLException e) {
            System.err.println("Error retrieving statistics: " + e.getMessage());
        }
    }
}

Prepared Statements

Using Prepared Statements for Security and Performance

public class PreparedStatementExamples {
    
    // Safe insertion with prepared statements
    public boolean createUser(User user) {
        String sql = """
            INSERT INTO users (username, email, password, status) 
            VALUES (?, ?, ?, ?)
            """;
        
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
            
            pstmt.setString(1, user.getUsername());
            pstmt.setString(2, user.getEmail());
            pstmt.setString(3, user.getPassword());
            pstmt.setString(4, user.getStatus());
            
            int rowsAffected = pstmt.executeUpdate();
            
            if (rowsAffected > 0) {
                // Get generated ID
                try (ResultSet generatedKeys = pstmt.getGeneratedKeys()) {
                    if (generatedKeys.next()) {
                        user.setId(generatedKeys.getInt(1));
                    }
                }
                return true;
            }
            
        } catch (SQLException e) {
            System.err.println("Error creating user: " + e.getMessage());
        }
        
        return false;
    }
    
    // Batch operations with prepared statements
    public int[] batchInsertUsers(List<User> users) {
        String sql = """
            INSERT INTO users (username, email, password, status) 
            VALUES (?, ?, ?, ?)
            """;
        
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            conn.setAutoCommit(false); // Start transaction
            
            for (User user : users) {
                pstmt.setString(1, user.getUsername());
                pstmt.setString(2, user.getEmail());
                pstmt.setString(3, user.getPassword());
                pstmt.setString(4, user.getStatus());
                pstmt.addBatch();
            }
            
            int[] results = pstmt.executeBatch();
            conn.commit(); // Commit transaction
            
            System.out.println("Batch insert completed: " + results.length + " statements executed");
            return results;
            
        } catch (SQLException e) {
            System.err.println("Error in batch insert: " + e.getMessage());
            return new int[0];
        }
    }
    
    // Complex query with multiple parameters
    public List<User> searchUsers(String usernamePattern, String emailDomain, 
                                 String status, Date fromDate, Date toDate) {
        StringBuilder sql = new StringBuilder("""
            SELECT * FROM users WHERE 1=1
            """);
        
        List<Object> parameters = new ArrayList<>();
        
        if (usernamePattern != null && !usernamePattern.trim().isEmpty()) {
            sql.append(" AND username LIKE ?");
            parameters.add("%" + usernamePattern + "%");
        }
        
        if (emailDomain != null && !emailDomain.trim().isEmpty()) {
            sql.append(" AND email LIKE ?");
            parameters.add("%" + emailDomain);
        }
        
        if (status != null && !status.trim().isEmpty()) {
            sql.append(" AND status = ?");
            parameters.add(status);
        }
        
        if (fromDate != null) {
            sql.append(" AND created_at >= ?");
            parameters.add(fromDate);
        }
        
        if (toDate != null) {
            sql.append(" AND created_at <= ?");
            parameters.add(toDate);
        }
        
        sql.append(" ORDER BY created_at DESC");
        
        List<User> users = new ArrayList<>();
        
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql.toString())) {
            
            // Set parameters
            for (int i = 0; i < parameters.size(); i++) {
                pstmt.setObject(i + 1, parameters.get(i));
            }
            
            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    users.add(mapResultSetToUser(rs));
                }
            }
            
        } catch (SQLException e) {
            System.err.println("Error searching users: " + e.getMessage());
        }
        
        return users;
    }
    
    // Stored procedure call
    public boolean updateUserPassword(String username, String newPassword) {
        String sql = "{CALL update_user_password(?, ?)}";
        
        try (Connection conn = DatabaseUtil.getConnection();
             CallableStatement cstmt = conn.prepareCall(sql)) {
            
            cstmt.setString(1, username);
            cstmt.setString(2, newPassword);
            
            boolean hasResults = cstmt.execute();
            
            if (!hasResults) {
                int updateCount = cstmt.getUpdateCount();
                return updateCount > 0;
            }
            
        } catch (SQLException e) {
            System.err.println("Error updating password: " + e.getMessage());
        }
        
        return false;
    }
}

Transactions

Transaction Management

public class TransactionExamples {
    
    // Simple transaction
    public boolean transferFunds(int fromAccountId, int toAccountId, double amount) {
        String debitSql = "UPDATE accounts SET balance = balance - ? WHERE id = ? AND balance >= ?";
        String creditSql = "UPDATE accounts SET balance = balance + ? WHERE id = ?";
        String logSql = "INSERT INTO transaction_log (from_account, to_account, amount, transaction_date) VALUES (?, ?, ?, ?)";
        
        Connection conn = null;
        try {
            conn = DatabaseUtil.getConnection();
            conn.setAutoCommit(false); // Start transaction
            
            // Debit from source account
            try (PreparedStatement debitStmt = conn.prepareStatement(debitSql)) {
                debitStmt.setDouble(1, amount);
                debitStmt.setInt(2, fromAccountId);
                debitStmt.setDouble(3, amount); // Ensure sufficient balance
                
                int debitResult = debitStmt.executeUpdate();
                if (debitResult == 0) {
                    throw new SQLException("Insufficient funds or invalid source account");
                }
            }
            
            // Credit to destination account
            try (PreparedStatement creditStmt = conn.prepareStatement(creditSql)) {
                creditStmt.setDouble(1, amount);
                creditStmt.setInt(2, toAccountId);
                
                int creditResult = creditStmt.executeUpdate();
                if (creditResult == 0) {
                    throw new SQLException("Invalid destination account");
                }
            }
            
            // Log transaction
            try (PreparedStatement logStmt = conn.prepareStatement(logSql)) {
                logStmt.setInt(1, fromAccountId);
                logStmt.setInt(2, toAccountId);
                logStmt.setDouble(3, amount);
                logStmt.setTimestamp(4, new Timestamp(System.currentTimeMillis()));
                
                logStmt.executeUpdate();
            }
            
            conn.commit(); // Commit transaction
            System.out.println("Transfer completed successfully");
            return true;
            
        } catch (SQLException e) {
            System.err.println("Transfer failed: " + e.getMessage());
            if (conn != null) {
                try {
                    conn.rollback(); // Rollback transaction
                    System.out.println("Transaction rolled back");
                } catch (SQLException rollbackEx) {
                    System.err.println("Rollback failed: " + rollbackEx.getMessage());
                }
            }
            return false;
        } finally {
            if (conn != null) {
                try {
                    conn.setAutoCommit(true); // Restore auto-commit
                    conn.close();
                } catch (SQLException e) {
                    System.err.println("Error closing connection: " + e.getMessage());
                }
            }
        }
    }
    
    // Savepoint usage
    public boolean complexBusinessOperation(List<BusinessOperation> operations) {
        Connection conn = null;
        Savepoint savepoint = null;
        
        try {
            conn = DatabaseUtil.getConnection();
            conn.setAutoCommit(false);
            
            // Perform initial operations
            performInitialOperations(conn);
            
            // Create savepoint before risky operations
            savepoint = conn.setSavepoint("beforeRiskyOps");
            
            // Perform risky operations
            for (BusinessOperation op : operations) {
                if (!executeOperation(conn, op)) {
                    // Rollback to savepoint only
                    conn.rollback(savepoint);
                    System.out.println("Rolled back to savepoint");
                    break;
                }
            }
            
            // Finalize
            performFinalOperations(conn);
            
            conn.commit();
            return true;
            
        } catch (SQLException e) {
            System.err.println("Business operation failed: " + e.getMessage());
            if (conn != null) {
                try {
                    if (savepoint != null) {
                        conn.rollback(savepoint);
                    } else {
                        conn.rollback();
                    }
                } catch (SQLException rollbackEx) {
                    System.err.println("Rollback failed: " + rollbackEx.getMessage());
                }
            }
            return false;
        } finally {
            DatabaseUtil.closeQuietly(conn);
        }
    }
    
    private void performInitialOperations(Connection conn) throws SQLException {
        // Implementation
    }
    
    private boolean executeOperation(Connection conn, BusinessOperation op) throws SQLException {
        // Implementation
        return true;
    }
    
    private void performFinalOperations(Connection conn) throws SQLException {
        // Implementation
    }
}

Connection Pooling

Basic Connection Pool Implementation

import java.util.concurrent.BlockingQueue;
import java.util.concurrent.LinkedBlockingQueue;

public class SimpleConnectionPool {
    private final BlockingQueue<Connection> pool;
    private final String url;
    private final String username;
    private final String password;
    private final int maxPoolSize;
    private int currentPoolSize;
    
    public SimpleConnectionPool(String url, String username, String password, int maxPoolSize) {
        this.url = url;
        this.username = username;
        this.password = password;
        this.maxPoolSize = maxPoolSize;
        this.pool = new LinkedBlockingQueue<>(maxPoolSize);
        this.currentPoolSize = 0;
        
        // Initialize with some connections
        initializePool();
    }
    
    private void initializePool() {
        int initialSize = Math.min(5, maxPoolSize);
        for (int i = 0; i < initialSize; i++) {
            try {
                Connection conn = createConnection();
                pool.offer(conn);
                currentPoolSize++;
            } catch (SQLException e) {
                System.err.println("Error initializing connection pool: " + e.getMessage());
            }
        }
    }
    
    private Connection createConnection() throws SQLException {
        return DriverManager.getConnection(url, username, password);
    }
    
    public Connection getConnection() throws SQLException {
        Connection conn = pool.poll();
        
        if (conn == null && currentPoolSize < maxPoolSize) {
            conn = createConnection();
            currentPoolSize++;
        }
        
        if (conn == null) {
            throw new SQLException("No available connections in pool");
        }
        
        // Validate connection
        if (conn.isClosed() || !conn.isValid(5)) {
            conn = createConnection();
        }
        
        return conn;
    }
    
    public void returnConnection(Connection conn) {
        if (conn != null) {
            try {
                if (!conn.isClosed()) {
                    pool.offer(conn);
                } else {
                    currentPoolSize--;
                }
            } catch (SQLException e) {
                System.err.println("Error returning connection to pool: " + e.getMessage());
                currentPoolSize--;
            }
        }
    }
    
    public void closePool() {
        while (!pool.isEmpty()) {
            try {
                Connection conn = pool.poll();
                if (conn != null && !conn.isClosed()) {
                    conn.close();
                }
            } catch (SQLException e) {
                System.err.println("Error closing connection: " + e.getMessage());
            }
        }
        currentPoolSize = 0;
    }
    
    public int getCurrentPoolSize() {
        return currentPoolSize;
    }
    
    public int getAvailableConnections() {
        return pool.size();
    }
}

// Usage example
public class ConnectionPoolExample {
    private static SimpleConnectionPool pool;
    
    static {
        pool = new SimpleConnectionPool(
            "jdbc:mysql://localhost:3306/mydb",
            "user", "password", 10
        );
    }
    
    public void performDatabaseOperation() {
        Connection conn = null;
        try {
            conn = pool.getConnection();
            
            // Perform database operations
            try (Statement stmt = conn.createStatement();
                 ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM users")) {
                if (rs.next()) {
                    System.out.println("User count: " + rs.getInt(1));
                }
            }
            
        } catch (SQLException e) {
            System.err.println("Database operation failed: " + e.getMessage());
        } finally {
            if (conn != null) {
                pool.returnConnection(conn);
            }
        }
    }
}

Best Practices

Secure and Efficient JDBC Usage

public class JDBCBestPractices {
    
    // 1. Always use try-with-resources
    public List<User> getAllUsersCorrect() {
        String sql = "SELECT * FROM users";
        List<User> users = new ArrayList<>();
        
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {
            
            while (rs.next()) {
                users.add(mapResultSetToUser(rs));
            }
            
        } catch (SQLException e) {
            System.err.println("Error retrieving users: " + e.getMessage());
        }
        
        return users;
    }
    
    // 2. Use prepared statements to prevent SQL injection
    public User authenticateUser(String username, String password) {
        // SECURE: Using prepared statement
        String sql = "SELECT * FROM users WHERE username = ? AND password = ?";
        
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setString(1, username);
            pstmt.setString(2, password); // Should be hashed
            
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    return mapResultSetToUser(rs);
                }
            }
            
        } catch (SQLException e) {
            System.err.println("Authentication error: " + e.getMessage());
        }
        
        return null;
    }
    
    // BAD EXAMPLE - Don't do this!
    public User authenticateUserInsecure(String username, String password) {
        // VULNERABLE TO SQL INJECTION
        String sql = "SELECT * FROM users WHERE username = '" + username + 
                    "' AND password = '" + password + "'";
        // This allows attacks like: username = "admin'; DROP TABLE users; --"
        return null; // Don't implement this!
    }
    
    // 3. Handle large result sets efficiently
    public void processLargeDataset() {
        String sql = "SELECT * FROM large_table ORDER BY id";
        
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            // Set fetch size for better memory management
            pstmt.setFetchSize(1000);
            
            try (ResultSet rs = pstmt.executeQuery()) {
                int processedCount = 0;
                
                while (rs.next()) {
                    // Process each row
                    processRow(rs);
                    processedCount++;
                    
                    // Periodic progress update
                    if (processedCount % 10000 == 0) {
                        System.out.println("Processed " + processedCount + " rows");
                    }
                }
                
                System.out.println("Total processed: " + processedCount + " rows");
            }
            
        } catch (SQLException e) {
            System.err.println("Error processing large dataset: " + e.getMessage());
        }
    }
    
    // 4. Pagination for large result sets
    public List<User> getUsersPaginated(int page, int pageSize) {
        String sql = "SELECT * FROM users ORDER BY id LIMIT ? OFFSET ?";
        List<User> users = new ArrayList<>();
        
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setInt(1, pageSize);
            pstmt.setInt(2, page * pageSize);
            
            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    users.add(mapResultSetToUser(rs));
                }
            }
            
        } catch (SQLException e) {
            System.err.println("Error retrieving paginated users: " + e.getMessage());
        }
        
        return users;
    }
    
    // 5. Database metadata and introspection
    public void analyzeDatabaseStructure() {
        try (Connection conn = DatabaseUtil.getConnection()) {
            DatabaseMetaData metaData = conn.getMetaData();
            
            System.out.println("Database Product: " + metaData.getDatabaseProductName());
            System.out.println("Database Version: " + metaData.getDatabaseProductVersion());
            System.out.println("Driver Name: " + metaData.getDriverName());
            System.out.println("Driver Version: " + metaData.getDriverVersion());
            
            // List all tables
            try (ResultSet tables = metaData.getTables(null, null, "%", new String[]{"TABLE"})) {
                System.out.println("\nTables:");
                while (tables.next()) {
                    String tableName = tables.getString("TABLE_NAME");
                    System.out.println("- " + tableName);
                    
                    // Get column information for each table
                    try (ResultSet columns = metaData.getColumns(null, null, tableName, "%")) {
                        while (columns.next()) {
                            String columnName = columns.getString("COLUMN_NAME");
                            String dataType = columns.getString("TYPE_NAME");
                            int columnSize = columns.getInt("COLUMN_SIZE");
                            String nullable = columns.getString("IS_NULLABLE");
                            
                            System.out.printf("  %s %s(%d) %s%n", 
                                            columnName, dataType, columnSize, 
                                            "YES".equals(nullable) ? "NULL" : "NOT NULL");
                        }
                    }
                }
            }
            
        } catch (SQLException e) {
            System.err.println("Error analyzing database structure: " + e.getMessage());
        }
    }
    
    // Helper methods
    private User mapResultSetToUser(ResultSet rs) throws SQLException {
        User user = new User();
        user.setId(rs.getInt("id"));
        user.setUsername(rs.getString("username"));
        user.setEmail(rs.getString("email"));
        user.setStatus(rs.getString("status"));
        user.setCreatedAt(rs.getTimestamp("created_at").toLocalDateTime());
        return user;
    }
    
    private void processRow(ResultSet rs) throws SQLException {
        // Process individual row
    }
}

Error Handling and Logging

public class JDBCErrorHandling {
    private static final Logger logger = LoggerFactory.getLogger(JDBCErrorHandling.class);
    
    public boolean executeWithRetry(String sql, Object... parameters) {
        int maxRetries = 3;
        int retryCount = 0;
        
        while (retryCount < maxRetries) {
            try (Connection conn = DatabaseUtil.getConnection();
                 PreparedStatement pstmt = conn.prepareStatement(sql)) {
                
                // Set parameters
                for (int i = 0; i < parameters.length; i++) {
                    pstmt.setObject(i + 1, parameters[i]);
                }
                
                pstmt.executeUpdate();
                logger.info("SQL executed successfully: {}", sql);
                return true;
                
            } catch (SQLTransientException e) {
                // Temporary failure - retry
                retryCount++;
                logger.warn("Transient SQL error (attempt {}): {}", retryCount, e.getMessage());
                
                if (retryCount < maxRetries) {
                    try {
                        Thread.sleep(1000 * retryCount); // Exponential backoff
                    } catch (InterruptedException ie) {
                        Thread.currentThread().interrupt();
                        break;
                    }
                }
            } catch (SQLIntegrityConstraintViolationException e) {
                // Data integrity violation - don't retry
                logger.error("Data integrity violation: {}", e.getMessage());
                return false;
            } catch (SQLException e) {
                // Other SQL error - don't retry
                logger.error("SQL error: {} (SQLState: {}, ErrorCode: {})", 
                           e.getMessage(), e.getSQLState(), e.getErrorCode());
                return false;
            }
        }
        
        logger.error("Failed to execute SQL after {} attempts: {}", maxRetries, sql);
        return false;
    }
}

Summary

JDBC provides the foundation for database connectivity in Java applications:

Key Benefits:

  • Database vendor independence through standard API
  • Direct control over SQL execution and performance
  • Support for advanced features like transactions and metadata
  • Foundation for higher-level frameworks like JPA

Best Practices:

  • Always use try-with-resources for automatic resource management
  • Use PreparedStatement to prevent SQL injection and improve performance
  • Implement proper transaction management and error handling
  • Use connection pooling for better performance and resource utilization
  • Handle large result sets efficiently with pagination and fetch sizing

Performance Considerations:

  • Use connection pooling to avoid connection overhead
  • Implement batch operations for bulk data modifications
  • Set appropriate fetch sizes for large result sets
  • Monitor and optimize SQL queries

JDBC remains essential for Java developers, providing low-level database access capabilities that enable fine-tuned control over data operations while serving as the foundation for modern ORM frameworks.