RecordManager.java

import java.util.*;
import java.sql.*;

public class RecordManager
{
	static Scanner scan = new Scanner(System.in); //one scanner object will be used for the entire program execution
    static Connection conn = null; // one connection object will also be used for the entire program execution
    public static void main(String[] args)
	{
		String url = "jdbc:mysql://localhost:3306/classrecord";
        String user = "root";
        String password = "";
        boolean connection = false;
        RecordManager recMan = new RecordManager();
        
        try//establishing the connection
        {  
            // Connecting to MySQL
            conn = DriverManager.getConnection(url, user, password);
            connection = true;
        } 
        catch (SQLException e) 
        {
            System.out.println("Connection failed!");
            e.printStackTrace();
        }

        if(connection)//program only executes when connection is successful
        {
            /*The block of code below will repeatively execute until user select 0*/
            while(true)
            {
                recMan.viewRec(conn); // this will show a list of studentID along with their surName(these data are retrieved from mysql db) so the user can have an initial view of the contents of the database
                int c = recMan.userChoice(); // this will show the menu 
                scan.nextLine(); //user input/choice is taken
                
                if(c==0)//if user selects 0, program exits
                {
                    System.out.println("-Execution Finished-"); 
                    break;
                }
                else
                    recMan.processUserChoice(c, conn); //if user !selects 0, input is processed accordingly
                
                
            }    
        }
	}
    
    /**
     * processUserChoice()
     * Executes the record operation corresponding to the user's choice.
     * @param c    the user's choice (1=Add, 2=Edit, 3=Delete, 4=View all, 5=Sort)
     * @param conn a Connection object used for database operations
     */
    void processUserChoice(int c, Connection conn) 
    {
        switch(c)
        {
        case 1:
            System.out.println("Adding a record");
            addRec(conn);
            break;
        case 2:
            System.out.println("Editing a record");
            editRec(conn);
            break;
        case 3:
            System.out.println("Deleting a record");
            deleteRec(conn);
            break;
        case 4:
            System.out.println("View all record");
            viewEntireRec(conn);
            break;
        case 5:
            System.out.println("Sort records");
            sortRec(conn);
            break;
        }
    }
    
    /**
     * getNum()
     * an auxilliary function specifically for taking integer user inputs only
     * @param message contains details of what the user needs to input
     * returns the inputted number
     */
    int getNum(String message)
    {
        int x=0;
        while(true)
        {
            try
            {
                System.out.print(message);
                x=scan.nextInt();
                break;
            }
            catch(InputMismatchException e)
            {
                System.out.println("Integer type only.");
                scan.nextLine();
            }
        }
        return x;
    }
    
    /**
     * getNum()
     * a function overload, an auxilliary function specifiaclly for taking integer inputs that strictly has a range
     * this function will not exit until conditions are satisfied (0> input <=range)
     * @param message contains the details of what the user needs to input
     * @param range range of user input
     * returns the inputted number
     */
    int getNum(String message, int range)
    {
        int num=0;
        while(true)
        {
            try
            {
                System.out.print(message);
                num = scan.nextInt();

                if(num<0 || num>range)
                {
                    System.out.println("Valid Range: 1-"+range);
                    continue;
                }
                else
                    break;
            }
            catch(InputMismatchException e)
            {
                System.out.println("Integers only.");
                scan.nextLine();
            }
        }
        return num;
    }
    
    /**
     * getString()
     * an auxilliary function specifically for taking String type inputs
     * @param message contains the details of what the user needs to input
     * returns the inputted string
     */
    String getString(String message)
    {
        String m;
        System.out.print(message);
        m = scan.nextLine();

        return m;
    }

    /**
     * userChoice()
     * serves as the "main menu" of the program
     * allows the user to select 1,2,3,4,5,0 only.
     * 1 adding, 2 editing, 3 deleting, 4 viewing, 5 sorting, 0 exit
     * return the inputted number
     */
    int userChoice() 
    {
        int choice = 0;
        while(true) 
        {
            try 
            {
                System.out.printf("%-12s %-12s %-12s %-12s %-12s %-12s%n", 
                                  "Add[1]", "Edit[2]", "Delete[3]", "View[4]", "Sort[5]", "Exit[0]");
                System.out.print("Choice: ");
                choice = scan.nextInt();

                if(choice < 0 || choice > 5) 
                {
                    System.out.println("Valid Range: 0-5");
                    continue;
                }
                break;
            } 
            catch(InputMismatchException e) 
            {
                System.out.println("Valid Inputs Only: 0,1,2,3,4,5");
                scan.nextLine(); 
            }
        }
        return choice;
    }

    /**
     * addRec()
     * Adds a new student record to the database.
     * This function prompts the user to enter student details including ID, name,
     * address, sex, and date of birth. It then constructs an SQL INSERT query
     * and executes it using the provided Connection object.
     *
     * @param conn the Connection object used to execute SQL queries on the database
     */
    void addRec(Connection conn)
    {
        String surName, firstName, address, birthDate="", sex;
        int studentID;
        int[] dateOfBirth = new int [3];

        studentID = getNum("Student ID: ");
        scan.nextLine();
        surName = getString("SurName: ");
        firstName = getString("FirstName: ");
        address = getString("Address: ");
        sex = getString("Sex: ");
        System.out.println("-Date of birth-");
        dateOfBirth[0] = getNum("Month: ", 12);
        dateOfBirth[1] = getNum("Day: ", 31);
        dateOfBirth[2] = getNum("Year: ", 2025);
        scan.nextLine();

        for(int i=0; i<3; i++)
        {
            birthDate += String.valueOf(dateOfBirth[i]);
            if(i<2)
            {
                birthDate+="/";
            }
        }

        String query = "INSERT INTO student (studentID, surName, firstName, address, birthDate, sex) VALUES(?,?,?,?,?,?)";
        try{
            PreparedStatement ps = conn.prepareStatement(query);
            ps.setInt(1, studentID);
            ps.setString(2, surName);
            ps.setString(3, firstName);
            ps.setString(4, address);
            ps.setString(5, birthDate);
            ps.setString(6, sex);
            ps.executeUpdate();
            ps.close();
            System.out.println("Success operation");
        }
        catch(SQLException e)
        {
            System.out.println("Failed operation.");
            e.printStackTrace();

        }
        System.out.println();
    }

    /**
     * viewEntireRec()
     * Displays all student records from the database.
     * This function retrieves all columns of the 'student' table and prints
     * them in a formatted table to the console. It uses the provided Connection
     * object to execute the SQL SELECT query.
     *
     * @param conn the Connection object used to execute SQL queries on the database
     */
    void viewEntireRec(Connection conn) 
    { 
        try 
        {
            // Retrieve all attributes of the student table
            String query = "SELECT * FROM student";
            PreparedStatement ps = conn.prepareStatement(query);
            ResultSet rs = ps.executeQuery();

            System.out.println("\n--- Student Records ---");
            // Table header
            System.out.printf("%-10s %-20s %-20s %-30s %-12s %-5s%n", 
                              "ID", "SurName", "FirstName", "Address", "BirthDate", "Sex");
            System.out.println("----------------------------------------------------------------------------------------------------------");

            // Table rows
            while(rs.next()) 
            {
                int id = rs.getInt("studentID");
                String surName = rs.getString("surName");
                String firstName = rs.getString("firstName");
                String address = rs.getString("address");
                String birthDate = rs.getString("birthDate");
                String sex = rs.getString("sex");

                System.out.printf("%-10d %-20s %-20s %-30s %-12s %-5s%n", 
                                  id, surName, firstName, address, birthDate, sex);
            }

            rs.close();
            ps.close();

        }
        catch(SQLException e) 
        {
            System.out.println("Failed to retrieve student record.");
            e.printStackTrace();
        }
        System.out.println();
    }

    /**
     * editRec()
     * Edits an existing student record in the database.
     * This function allows the user to select a student by their student ID,view the current details, and update any field in the student's record including studentID, SurName, FirstName, Address, BirthDate, or Sex.
     * The user can perform multiple edits in a single session.
     * The function uses the provided Connection object to execute SQL SELECT and UPDATE queries. It validates that the student exists before editing and confirms success or failure of each update operation.
     * @param conn the Connection object used to execute SQL queries on the database
     */
    void editRec(Connection conn)
    {
        try {
            //Display all student IDs + surnames
            String query = "SELECT studentID, surName FROM student";
            PreparedStatement ps = conn.prepareStatement(query);//here
            ResultSet rs = ps.executeQuery();

            // Ask user which student to edit
            int idToEdit = getNum("Edit(studentId): ");

            // Fetch student’s full record
            query = "SELECT * FROM student WHERE studentID = ?";
            ps = conn.prepareStatement(query);
            ps.setInt(1, idToEdit);
            rs = ps.executeQuery();

            if (!rs.next()) 
            {
                System.out.println(" No student found with ID " + idToEdit);
                return;
            }

            // Display current info
            System.out.println("\nCurrent Record:");
            System.out.println("SurName: " + rs.getString("surName"));
            System.out.println("FirstName: " + rs.getString("firstName"));
            System.out.println("Address: " + rs.getString("address"));
            System.out.println("BirthDate: " + rs.getString("birthDate"));
            System.out.println("Sex: " + rs.getString("sex"));

            rs.close();
            ps.close();

            // Ask user for new info
            scan.nextLine(); // clear newline left by nextInt()
            boolean editing =true;
            while(editing)
            {
                System.out.println("\nSelect what to edit");
                System.out.println("[1]StudentId\t[2]SurName\t[3]FirstName\t[4]Address\t[5]BirthDate\t[6]Sex");
                int choice = getNum("Choice: ", 6);
                scan.nextLine();
                int newId=0;
                String newDetail="";
                String toChange="";
                switch(choice)
                {
                    case 1:
                        newId = getNum("New Id: ");
                        toChange = "studentID";
                        scan.nextLine();
                        break;
                    case 2:
                        newDetail = getString("New SurName: ");
                        toChange = "surName";
                        break;
                    case 3:
                        newDetail = getString("New FirstName: ");
                        toChange = "firstName";
                        break;
                    case 4:
                        newDetail = getString("New Address: ");
                        toChange = "address";
                        break;
                    case 5:
                        newDetail = getString("New Birthdate: ");
                        toChange = "birthDate";
                        break;
                    case 6:
                        newDetail = getString("New Sex: ");
                        toChange = "sex";
                        break;
                }
                

                // Update record
                query = "UPDATE student SET "+ toChange +" = ? WHERE studentID = ?";
                ps = conn.prepareStatement(query);
                
                if(choice == 1)ps.setInt(1, newId);
                else ps.setString(1, newDetail);  
                ps.setInt(2, idToEdit);
                
                int rows = ps.executeUpdate();
                if (rows > 0)
                    System.out.println("Success Operation.");
                else
                    System.out.println("Failed Operation.");
                ps.close();

                System.out.println("Edit further? [1]Yes [Any key]No");
                String cont = scan.nextLine();
                if(!cont.equals("1")) editing =false;
                
            }

        } catch (SQLException e) {
            System.out.println("Database error during edit!");
            e.printStackTrace();
        }
    }

    /**
     * deleteRec()
     * Deletes a student record from the database.
     * This function allows the user to select a student by their student ID and delete their record from the database. It confirms deletion with    the user before executing the operation. The user can delete multiple records in a single session. 
     * The function uses the provided Connection object to execute SQL SELECT and DELETE queries and handles cases where the student ID does not exist.
     * @param conn the Connection object used to execute SQL queries on the database
     */
    void deleteRec(Connection conn)
    {
        while(true)
        {
            try
            {
                String query = "SELECT studentID, surName FROM student";
                PreparedStatement ps = conn.prepareStatement(query);
                ResultSet rs = ps.executeQuery();

                // Ask user which student to delete
                int idToDelete = getNum("Delete(studentId): ");
                scan.nextLine();
                
                // Confirm deletion
                System.out.print("Are you sure you want to delete student ID " + idToDelete + "? [1]Yes [AnyKey]No: ");
                String confirm = scan.nextLine();
                if(!confirm.equals("1")) 
                {
                    System.out.println("Deletion cancelled.");
                } 
                else 
                {
                    query = "DELETE FROM student WHERE studentID = ?";
                    ps = conn.prepareStatement(query);
                    ps.setInt(1, idToDelete);
                    int rows = ps.executeUpdate();
                    if(rows > 0)
                        System.out.println("Student deleted successfully.");
                    else
                        System.out.println("No student found with ID " + idToDelete);
                    ps.close();
                }
            }
            catch(SQLException e)
            {
                System.out.println("Failed to get into database.");
            }
                
            System.out.print("Delete more record? [1]Yes, [AnyKey]No : ");
            String cont = scan.nextLine();
            if(!cont.equals("1")) break;
        }
        System.out.println();
    }

    /**
     * viewRec()
     * Displays list of studentID and corresponding surName
     * use for userinterface
     * @param conn the Connection object used to execute SQL queries on the database
     */
    void viewRec(Connection conn) 
    {
        try {
            String query = "SELECT studentID, surName FROM student";
            PreparedStatement ps = conn.prepareStatement(query);
            ResultSet rs = ps.executeQuery();

            System.out.println("\n---Records---");
            // Table header
            System.out.printf("%-10s %-20s%n", 
                              "ID", "SurName");
            System.out.println("-----------------");

            // Table rows
            while(rs.next()) 
            {
                int id = rs.getInt("studentID");
                String surName = rs.getString("surName");
                

                System.out.printf("%-10d %-20s %n", 
                                  id, surName);
            }

            rs.close();
            ps.close();

        } 
        catch(SQLException e) 
        {
            System.out.println("Failed to retrieve records from the database.");
            e.printStackTrace();
        }
        System.out.println();
    }

    /**
     * sortRec()
     * Displays student records sorted by a selected field.
     * This function prompts the user to choose a field to sort by: StudentID, SurName, or FirstName. It then retrieves all student records from the database, sorts them based on the selected field, and prints them in a formatted table.
     * The function uses the provided Connection object to execute an SQL SELECT query with an ORDER BY clause. It handles database access errors gracefully.
     * @param conn the Connection object used to execute SQL queries on the database
     */
    void sortRec(Connection conn) 
    {
        try {
                // Ask user which field to sort by
                System.out.println("Sort by:");
                System.out.printf("%-12s %-12s %-12s%n", "[1]StudentID", "[2]SurName", "[3]FirstName");
                int choice = getNum("Choice: ", 3);

                String sortColumn = "";
                switch(choice) 
                {
                    case 1: sortColumn = "studentID";
                        break;
                    case 2: sortColumn = "surName"; 
                        break;
                    case 3: sortColumn = "firstName"; 
                        break;
                }

                // Fetch sorted records
                String query = "SELECT studentID, surName, firstName, birthDate, address, sex FROM student ORDER BY " + sortColumn;
                PreparedStatement ps = conn.prepareStatement(query);
                ResultSet rs = ps.executeQuery();

                // Print table header
                System.out.println("\n--- Sorted Student Records By "+ sortColumn +" ---");
                System.out.printf("%-10s %-20s %-20s %-12s %-30s %-5s%n", 
                                  "ID", "SurName", "FirstName", "BirthDate", "Address", "Sex");
                System.out.println("------------------------------------------------------------------------------------------------------");

                // Print records
                while(rs.next()) {
                    int id = rs.getInt("studentID");
                    String surName = rs.getString("surName");
                    String firstName = rs.getString("firstName");
                    String birthDate = rs.getString("birthDate");
                    String address = rs.getString("address");
                    String sex = rs.getString("sex");

                    System.out.printf("%-10d %-20s %-20s %-12s %-30s %-5s%n", 
                                      id, surName, firstName, birthDate, address, sex);
                }

                rs.close();
                ps.close();

        } 
        catch(SQLException e) 
        {
            System.out.println("Failed to retrieve sorted records.");
            e.printStackTrace();
        }

        System.out.println("\n");
    }
}