Slip No 24 Q B

Write a menu driven program in Java for the following: Assume Emp table with attributes ( ENo, EName, salary, Desg ) is already created.

1. Insert
2. Update
3. Delete
4. Search
5. Display
6. Exit.

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

public class MenuDrivenEmp {

    // Database connection parameters
    static final String URL = "jdbc:mysql://localhost:3306/yourDB"; // replace yourDB with your database name
    static final String USER = "root"; // MySQL username
    static final String PASS = "";     // MySQL password

    public static void main(String[] args) {
        Scanner sc = new Scanner(System.in);
        int choice;

        try {
            // Load MySQL JDBC Driver
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(URL, USER, PASS);

            do {
                System.out.println("\n--- Employee Menu ---");
                System.out.println("1. Insert");
                System.out.println("2. Update");
                System.out.println("3. Delete");
                System.out.println("4. Search");
                System.out.println("5. Display");
                System.out.println("6. Exit");
                System.out.print("Enter your choice: ");
                choice = sc.nextInt();
                sc.nextLine(); // consume newline

                switch(choice) {
                    case 1: // Insert
                        System.out.print("Enter ENo: ");
                        int eno = sc.nextInt();
                        sc.nextLine();
                        System.out.print("Enter EName: ");
                        String ename = sc.nextLine();
                        System.out.print("Enter Salary: ");
                        double salary = sc.nextDouble();
                        sc.nextLine();
                        System.out.print("Enter Designation: ");
                        String desg = sc.nextLine();

                        String insertQuery = "INSERT INTO Emp (ENo, EName, Salary, Desg) VALUES (?, ?, ?, ?)";
                        PreparedStatement psInsert = con.prepareStatement(insertQuery);
                        psInsert.setInt(1, eno);
                        psInsert.setString(2, ename);
                        psInsert.setDouble(3, salary);
                        psInsert.setString(4, desg);

                        int rows = psInsert.executeUpdate();
                        if(rows > 0) System.out.println("Record inserted successfully!");
                        break;

                    case 2: // Update
                        System.out.print("Enter ENo to update: ");
                        int updateEno = sc.nextInt();
                        sc.nextLine();
                        System.out.print("Enter new Salary: ");
                        double newSalary = sc.nextDouble();
                        sc.nextLine();
                        System.out.print("Enter new Designation: ");
                        String newDesg = sc.nextLine();

                        String updateQuery = "UPDATE Emp SET Salary=?, Desg=? WHERE ENo=?";
                        PreparedStatement psUpdate = con.prepareStatement(updateQuery);
                        psUpdate.setDouble(1, newSalary);
                        psUpdate.setString(2, newDesg);
                        psUpdate.setInt(3, updateEno);

                        int updated = psUpdate.executeUpdate();
                        if(updated > 0) System.out.println("Record updated successfully!");
                        else System.out.println("Record not found!");
                        break;

                    case 3: // Delete
                        System.out.print("Enter ENo to delete: ");
                        int deleteEno = sc.nextInt();
                        sc.nextLine();

                        String deleteQuery = "DELETE FROM Emp WHERE ENo=?";
                        PreparedStatement psDelete = con.prepareStatement(deleteQuery);
                        psDelete.setInt(1, deleteEno);

                        int deleted = psDelete.executeUpdate();
                        if(deleted > 0) System.out.println("Record deleted successfully!");
                        else System.out.println("Record not found!");
                        break;

                    case 4: // Search
                        System.out.print("Enter ENo to search: ");
                        int searchEno = sc.nextInt();
                        sc.nextLine();

                        String searchQuery = "SELECT * FROM Emp WHERE ENo=?";
                        PreparedStatement psSearch = con.prepareStatement(searchQuery);
                        psSearch.setInt(1, searchEno);

                        ResultSet rsSearch = psSearch.executeQuery();
                        if(rsSearch.next()) {
                            System.out.println("ENo: " + rsSearch.getInt("ENo"));
                            System.out.println("EName: " + rsSearch.getString("EName"));
                            System.out.println("Salary: " + rsSearch.getDouble("Salary"));
                            System.out.println("Desg: " + rsSearch.getString("Desg"));
                        } else {
                            System.out.println("Record not found!");
                        }
                        break;

                    case 5: // Display all
                        String displayQuery = "SELECT * FROM Emp";
                        Statement stmt = con.createStatement();
                        ResultSet rs = stmt.executeQuery(displayQuery);

                        System.out.println("\nENo\tEName\tSalary\tDesg");
                        System.out.println("----------------------------------");
                        while(rs.next()) {
                            System.out.println(rs.getInt("ENo") + "\t" +
                                               rs.getString("EName") + "\t" +
                                               rs.getDouble("Salary") + "\t" +
                                               rs.getString("Desg"));
                        }
                        break;

                    case 6:
                        System.out.println("Exiting...");
                        break;

                    default:
                        System.out.println("Invalid choice! Try again.");
                }

            } while(choice != 6);

            con.close();
            sc.close();
        } catch(Exception e) {
            System.out.println("Error: " + e.getMessage());
        }
    }
}