i have created a program that allows a user to create, delete, update, employee info by presenting the user with a menu similar to the following:
Please choose an option: 1. Create new Employee 2. View All Employee Details 3.Delete Employee Details 4.Update Employee Values 5. Show Updated Values 6. Sum of Salary of all the Employee 7. Exit
If the user chooses to create new employee, it will ask to enter all the details of employee like empid,first name, last name...etc.After successfully creating the new employee it should ask,do you want to create more new employee?? If user says "YES" then user should get the previous menu of create new employee. If user says "NO" then program should return to the main menu. Same should happen to all the cases. If the user chooses to remove employee, the program should ask the user which employee to remove and remove it from the table. If the user chooses to update employee details, the program should ask which field will be updated and for a new description of the task. The program should loop until the user chooses to exit. And if user chooses the update option,he will get another list, which asks user to which field he want to update. After successfully updating the employee details it should ask,do you want to update more employee?? If user says "YES" then user should get the previous menu of update employee. If user says "NO" then program should exit from update menu and return to the main menu that is "jdbc selection menu".
package jdbcapp;
import java.sql.*;
import java.util.Scanner;
public class Driver {
public static void main(String[] args) {
Scanner scanner = new Scanner(System.in);
int choice;
int option;
int Phone= 00;
int Salary = 000 ;
int empID= 0;
String Address=null;
String City=null;
String State=null;
String Country=null;
String empEmailId = null;
try {
Class.forName("com.mysql.jdbc.Driver");
Connection myConn =DriverManager.getConnection("jdbc:mysql://localhost:3306/sys", "root", "root12345");
System.out.println("Connection is Established");
System.out.println("\n\n");
Statement myStmt = myConn.createStatement();
do {
//Main Switch Menu
System.out.println("*****JDBC Selection Menu*****");
System.out.println("\n\n");
System.out.println("1. Create new Employee");
System.out.println("2. View All Employee Details");
System.out.println("3. Delete Employee Details");
System.out.println("4. Update Employee Values");
System.out.println("5. Show Updated Values");
System.out.println("6. Sum of Salary of all the Employee");
System.out.println("7. Exit");
System.out.println("\n\n");
//Ask the user to enter your choice from jdbc selection menu
System.out.println("Enter your Choice");
System.out.println("\n\n");
choice =scanner.nextInt();
switch(choice)
{
case 1:
System.out.println("Enter employee id");
int empId=scanner.nextInt();
System.out.println("Enter employee First name");
String empFirstName=scanner.next();
System.out.println("Enter employee last name");
String empLastName=scanner.next();
System.out.println("Enter employee EmailId");
String EmailId=scanner.next();
System.out.println("Enter employee Phone");
int empPhone=scanner.nextInt();
System.out.println("Enter employee Address");
String empAddress=scanner.next();
System.out.println("Enter employee City");
String empCity=scanner.next();
System.out.println("Enter employee State");
String empState=scanner.next();
System.out.println("Enter employee Country");
String empCountry=scanner.next();
System.out.println("Enter employee Salary");
int empSalary=scanner.nextInt();
myStmt.executeUpdate("insert into EmployeeInfo values('"+empId+"', '"+empFirstName+"', '"+empLastName+"','"+empEmailId+"', '"+empPhone+"','"+empAddress+"','"+empCity+"','"+empState+"','"+empCountry+"','"+empSalary+"')");
System.out.println("Successfully Created");
//Ask user if he want to create more or not.
System.out.println("Do you want to create more new employee??");
System.out.println("Press 1 for Yes");
System.out.println("Press 2 for No");
option =scanner.nextInt();
int Yes=1;
if(Yes<=1) {
System.out.println("Enter employee id");
empId=scanner.nextInt();
System.out.println("Enter employee First name");
empFirstName=scanner.next();
System.out.println("Enter employee last name");
empLastName=scanner.next();
System.out.println("Enter employee EmailId");
EmailId=scanner.next();
System.out.println("Enter employee Phone");
empPhone=scanner.nextInt();
System.out.println("Enter employee Address");
empAddress=scanner.next();
System.out.println("Enter employee City");
empCity=scanner.next();
System.out.println("Enter employee State");
empState=scanner.next();
System.out.println("Enter employee Country");
empCountry=scanner.next();
System.out.println("Enter employee Salary");
empSalary=scanner.nextInt();
myStmt.executeUpdate("insert into EmployeeInfo values('"+empId+"', '"+empFirstName+"', '"+empLastName+"','"+empEmailId+"', '"+empPhone+"','"+empAddress+"','"+empCity+"','"+empState+"','"+empCountry+"','"+empSalary+"')");
System.out.println("Successfully Created");
}
else if(Yes<=2){
return;
}
break;
//Show all the employee details
case 2:
ResultSet myRs1 = myStmt.executeQuery("select * from employeeinfo");
while (myRs1.next())
{
System.out.println(myRs1.getInt(1)+"\t\t"+myRs1.getString(2)+"\t\t"+myRs1.getString(3)+"\t\t"+myRs1.getString(4)+"\t\t"+myRs1.getInt(5)+"\t\t"+myRs1.getString(6)+"\t\t"+myRs1.getString(7)+"\t\t"+myRs1.getString(8)+"\t\t"+myRs1.getString(9)+"\t\t"+myRs1.getInt(10) );
}
break;
//delete employee
case 3:
ResultSet myRs = myStmt.executeQuery("select * from employeeinfo");
while(myRs.next()) {
System.out.println(myRs.getInt("empID")+ "," + myRs.getString("firstName"));
}
System.out.println("Enter the employee Id which you want to delete");
empID=scanner.nextInt();
myStmt.execute("delete from Employeeinfo where empid=('"+empID+"')");
System.out.println("Successfully Deleted");
break;
//update query
case 4:System.out.println("Enter employee id which you want to update");
empID=scanner.nextInt();
do {
//ask user to which field you want to update
System.out.println("Which field you want to update");
System.out.println("8. First Name ");
System.out.println("9. Last Name ");
System.out.println("10. Email ID ");
System.out.println("11. Phone");
System.out.println("12. Address ");
System.out.println("13. City");
System.out.println("14. State");
System.out.println("15. Country");
System.out.println("16. Salary");
System.out.println("17. Exit");
System.out.println("\n\n");
System.out.println("Enter your Option");
System.out.println("\n\n");
option =scanner.nextInt();
// another switch case for update query
switch(option)
{
case 8:
System.out.println("Enter employee first name");
String FirstName=scanner.next();
myStmt.executeUpdate("update Employeeinfo set FirstName =('"+FirstName+"') where empid= ('"+empID+"')");
System.out.println("Successfully Updatedted");
System.out.println("Do you want to create more new employee?");
System.out.println("Press 1 for YES\n\n Press 2 for NO");
case 9:
System.out.println("Enter employee last name");
String LastName=scanner.next();
myStmt.executeUpdate("update Employeeinfo set LastName =('"+LastName+"') where empid= ('"+empID+"')");
System.out.println("Successfully Updatedted");
break;
case 10:
System.out.println("Enter employee EmailId");
EmailId=scanner.next();
myStmt.executeUpdate("update Employeeinfo set EmailId =('"+EmailId+"') where empid= ('"+empID+"')");
System.out.println("Successfully Updatedted");
break;
case 11:
System.out.println("Enter employee Phone");
Phone=scanner.nextInt();
myStmt.executeUpdate("update Employeeinfo set Phone =('"+Phone+"') where empid= ('"+empID+"')");
System.out.println("Successfully Updatedted");
break;
case 12:
System.out.println("Enter employee Address");
Address=scanner.next();
myStmt.executeUpdate("update Employeeinfo set Address =('"+Address+"') where empid= ('"+empID+"')");
System.out.println("Successfully Updatedted");
break;
case 13:
System.out.println("Enter employee City");
City=scanner.next();
myStmt.executeUpdate("update Employeeinfo set City =('"+City+"') where empid= ('"+empID+"')");
System.out.println("Successfully Updatedted");
break;
case 14:
System.out.println("Enter employee State");
State=scanner.next();
myStmt.executeUpdate("update Employeeinfo set State =('"+State+"') where empid= ('"+empID+"')");
System.out.println("Successfully Updatedted");
break;
case 15:
System.out.println("Enter employee Country");
Country=scanner.next();
myStmt.executeUpdate("update Employeeinfo set Country =('"+Country+"') where empid= ('"+empID+"')");
System.out.println("Successfully Updatedted");
break;
case 16:
System.out.println("Enter employee Salary");
Salary=scanner.nextInt();
myStmt.executeUpdate("update Employeeinfo set Salary =('"+Salary+"') where empid= ('"+empID+"')");
System.out.println("Successfully Updatedted");
break;
//to exit from the update query menu
case 17:
System.out.println("Thanks!!");
break;
}
}while(option!=17);
//show updated table
case 5:
ResultSet myRs2 = myStmt.executeQuery("select * from employeeinfo");
while (myRs2.next())
{
System.out.println(myRs2.getInt(1)+"\t\t"+myRs2.getString(2)+"\t\t"+myRs2.getString(3)+"\t\t"+myRs2.getString(4)+"\t\t"+myRs2.getInt(5)+"\t\t"+myRs2.getString(6)+"\t\t"+myRs2.getString(7)+"\t\t"+myRs2.getString(8)+"\t\t"+myRs2.getString(9)+"\t\t"+myRs2.getInt(10) );
}
break;
//sum of salary
case 6:
myStmt = myConn.createStatement();
ResultSet myRs3 = myStmt.executeQuery("SELECT SUM(salary)FROM employeeinfo");
while (myRs3.next())
{
String sumOfSalary = myRs3.getString(1);
System.out.println("Sum of salary is:\t"+sumOfSalary);
System.out.println("\n");
}
break;
//exit from whole program
case 7:
System.out.println("Thanks!! you are out of program now");
System.exit(0);
break;
}
}while(choice!=7);
myConn.close();
}
catch(ClassNotFoundException e)
{
e.printStackTrace();
}
catch(SQLException e)
{
e.printStackTrace();
}
}
}
Aucun commentaire:
Enregistrer un commentaire