SqlAbstraction.java

// Import necessary packages for database operations, data structures, and user input
import java.sql.*;                 // JDBC API for database connectivity (Connection, Statement, ResultSet, etc.)
import java.util.HashMap;          // Key-value pair collection for storing predefined SQL commands
import java.util.Map;              // Map interface that HashMap implements
import java.util.Scanner;          // For reading user input from the console/terminal

// Main class definition - entry point of the program
public class SqlAbstraction {

    // Main method - program execution starts here
    public static void main(String[] args) {
        // Create a Scanner object to read input from standard input (keyboard)
        Scanner scanner = new Scanner(System.in);
        
        // Create a HashMap to store predefined SQL commands with integer keys (1-5)
        // Integer = command number, String = SQL query
        Map<Integer, String> sqlCommands = new HashMap<>();

        // Populate the HashMap with 5 predefined SQL commands
        // Each command is assigned a unique number (key)
        sqlCommands.put(1, "SELECT * FROM users;");                    // Command 1: Retrieve all user records
        sqlCommands.put(2, "SELECT * FROM products WHERE price > 50.00;");  // Command 2: Get products over $50
        sqlCommands.put(3, "INSERT INTO orders (user_id, product_id, quantity) VALUES (1, 2, 3);");  // Command 3: Create an order
        sqlCommands.put(4, "UPDATE users SET active = 1 WHERE user_id = 1;");  // Command 4: Activate a specific user
        sqlCommands.put(5, "DELETE FROM products WHERE product_id = 10;");  // Command 5: Delete a specific product

        // Display welcome message and program description
        System.out.println("Welcome to the SQL Command Executor!");
        System.out.println("You can execute predefined commands or enter custom SQL queries.");

        // Main program loop - continues until user chooses to exit (option 3)
        while (true) {
            // Display header for predefined commands section
            System.out.println("\nAvailable Predefined SQL Commands:");
            
            // Loop through all entries in the sqlCommands HashMap
            // Map.Entry represents a key-value pair in the map
            for (Map.Entry<Integer, String> entry : sqlCommands.entrySet()) {
                // Display each command with its number and SQL statement
                System.out.println(entry.getKey() + ": " + entry.getValue());
            }

            // Display menu options for user interaction
            System.out.println("\nOptions:");
            System.out.println("1. Execute a predefined SQL command");
            System.out.println("2. Enter a custom SQL query");
            System.out.println("3. Exit");
            System.out.print("Enter your choice: ");  // Prompt user for choice
            
            // Read the user's menu choice as an integer
            int choice = scanner.nextInt();
            scanner.nextLine(); // Consume the leftover newline character from nextInt()
            // This prevents the newline from interfering with subsequent nextLine() calls

            // Option 1: Execute a predefined SQL command
            if (choice == 1) {
                // Sub-menu loop for selecting predefined commands
                while (true) {
                    // Prompt user to select a command number
                    System.out.print("Enter the number of the SQL command to execute (or 0 to go back): ");
                    int commandNumber = scanner.nextInt();  // Read command number
                    scanner.nextLine(); // Consume newline
                    
                    // Check if user wants to return to main menu
                    if (commandNumber == 0) {
                        break; // Exit this while loop, return to main menu
                    } 
                    // Check if the entered command number exists in the HashMap
                    else if (sqlCommands.containsKey(commandNumber)) {
                        // Retrieve the SQL command string using the command number as key
                        String sql = sqlCommands.get(commandNumber);
                        System.out.println("Executing: " + sql);  // Display what's being executed
                        executeSql(sql);  // Call the method to execute the SQL command
                    } 
                    // Handle invalid command numbers
                    else {
                        System.out.println("Invalid command number. Please try again.");
                    }
                }
            } 
            // Option 2: Execute a custom SQL query
            else if (choice == 2) {
                // Sub-menu loop for entering custom SQL
                while (true) {
                    // Prompt user for custom SQL query
                    System.out.print("Enter your custom SQL query (or type 'back' to go back): ");
                    String customSql = scanner.nextLine();  // Read full line including spaces
                    
                    // Check if user wants to return to main menu
                    if (customSql.equalsIgnoreCase("back")) {
                        break; // Exit this while loop, return to main menu
                    } 
                    // Execute the custom SQL query
                    else {
                        System.out.println("Executing: " + customSql);
                        executeSql(customSql);  // Execute the user-provided SQL
                    }
                }
            } 
            // Option 3: Exit the program
            else if (choice == 3) {
                // Display exit message and break out of main loop
                System.out.println("Exiting the program. Goodbye!");
                break; // Exit the main while(true) loop, ending the program
            } 
            // Handle invalid menu choices
            else {
                System.out.println("Invalid choice. Please try again.");
            }
        }

        // Close the Scanner to prevent resource leak
        scanner.close();
    }

    // Private helper method to execute SQL queries on the database
    // static: can be called without creating an instance of SqlAbstraction
    // void: doesn't return a value (output is printed to console)
    private static void executeSql(String sql) {
        // Database connection parameters
        String jdbcUrl = "jdbc:mysql://localhost:3306/group_db";  // JDBC URL for MySQL database
        // Format: jdbc:mysql://[host]:[port]/[database_name]
        String username = "root";  // Database username (⚠️ Security risk: hardcoded)
        String password = "Beracahone7245&";  // Database password (⚠️ Major security risk!)
        
        // Display the SQL being executed
        System.out.println("Executing SQL: " + sql);

        // Try-with-resources block: automatically closes resources when done
        // Resources declared in parentheses will be closed automatically
        try (
            // 1. Establish connection to the database
            Connection connection = DriverManager.getConnection(jdbcUrl, username, password);
            
            // 2. Create a Statement object for executing SQL
            // ResultSet.TYPE_SCROLL_INSENSITIVE: ResultSet can be scrolled forward/backward
            // ResultSet.CONCUR_READ_ONLY: ResultSet cannot be updated
            Statement statement = connection.createStatement(
                ResultSet.TYPE_SCROLL_INSENSITIVE, 
                ResultSet.CONCUR_READ_ONLY
            );
            
            // 3. Execute query and get ResultSet if it's a SELECT statement
            // Conditional expression: checks if SQL starts with "SELECT"
            // sql.trim().toUpperCase().startsWith("SELECT"): 
            //   - trim(): removes leading/trailing whitespace
            //   - toUpperCase(): makes check case-insensitive
            //   - startsWith("SELECT"): checks if it's a SELECT query
            // If SELECT: executeQuery() returns ResultSet
            // If not SELECT: resultSet is null
            ResultSet resultSet = sql.trim().toUpperCase().startsWith("SELECT") ? 
                                   statement.executeQuery(sql) : null
        ) {
            // Process SELECT queries (when resultSet is not null)
            if (resultSet != null) {
                // Get metadata about the ResultSet (column names, types, etc.)
                ResultSetMetaData metaData = resultSet.getMetaData();
                int columnCount = metaData.getColumnCount();  // Number of columns in result
                
                // Create array to store optimal width for each column for formatting
                int[] columnWidths = new int[columnCount];
                
                // Initialize column widths based on column names
                for (int i = 1; i <= columnCount; i++) {
                    // Start with width of column name, minimum 10 characters
                    columnWidths[i - 1] = Math.max(metaData.getColumnName(i).length(), 10);
                }
                
                // First pass: move through all rows to find maximum data width per column
                resultSet.beforeFirst();  // Move cursor before first row (scrollable ResultSet feature)
                while (resultSet.next()) {  // Loop through all rows
                    for (int i = 1; i <= columnCount; i++) {
                        String value = resultSet.getString(i);  // Get value as String
                        if (value != null) {
                            // Update column width if current value is wider
                            columnWidths[i - 1] = Math.max(columnWidths[i - 1], value.length());
                        }
                    }
                }
                
                // Print column headers with calculated widths
                for (int i = 1; i <= columnCount; i++) {
                    // Format specifier: %-[width]s
                    // -: left-justify
                    // width: columnWidths[i-1] + 2 (adds 2 spaces padding)
                    // s: string format
                    System.out.printf("%-" + (columnWidths[i - 1] + 2) + "s", metaData.getColumnName(i));
                }
                System.out.println();  // New line after headers
                
                // Print separator line (not in original code, but would be helpful)
                // for (int i = 1; i <= columnCount; i++) {
                //     System.out.printf("%-" + (columnWidths[i - 1] + 2) + "s", 
                //                      "-".repeat(columnWidths[i - 1]));
                // }
                // System.out.println();
                
                // Second pass: print all data rows
                resultSet.beforeFirst();  // Reset cursor to beginning
                while (resultSet.next()) {
                    for (int i = 1; i <= columnCount; i++) {
                        // Print each value with the same width as column header
                        System.out.printf("%-" + (columnWidths[i - 1] + 2) + "s", resultSet.getString(i));
                    }
                    System.out.println();  // New line after each row
                }
            } 
            // Process non-SELECT queries (INSERT, UPDATE, DELETE)
            else {
                // executeUpdate() returns number of rows affected
                int rowsAffected = statement.executeUpdate(sql);
                System.out.println(rowsAffected + " rows affected.");
            }

        } 
        // Handle SQL exceptions (database errors)
        catch (SQLException e) {
            // Print detailed error information
            System.err.println("SQL Exception: " + e.getMessage());  // Human-readable error
            System.err.println("SQL State: " + e.getSQLState());  // SQL state code (standardized)
            System.err.println("Vendor Error Code: " + e.getErrorCode());  // Database-specific error code
            
            // Print stack trace for debugging (shows method call sequence)
            e.printStackTrace();
        }
        // Note: No finally block needed - try-with-resources auto-closes connection
    }
}