EMPLOYEE

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

public class Menu {
    static Scanner scan;
    public static void main(String args[]) {

        scan = new Scanner(System.in);
        while (true) {
            System.out.println("----------------------------------------------");
            System.out.println("1. Add Employee(empno,ename,sal,desig,deptno)");
            System.out.println("2. Update employee salary by empno");
            System.out.println("3. Delete employee by empno");
            System.out.println("4. Display emp details by deptno");
            System.out.println("5. Display all emp details");
            System.out.println("0. Exit");
            System.out.println("----------------------------------------------");
            System.out.println("Enter choice:");
            int ch = Integer.parseInt(scan.nextLine());
            switch (ch) {
            case 1:
                addEmp();
                break;
            case 2:
                updateSal();
                break;
            case 3:
                deleteEmp();
                break;
            case 4:
                displayDept();
                break;
            case 5:
                displayAll();
                break;
            case 0:
                System.exit(0);

            }
        }
    }
    public static void addEmp() {
        try {
            System.out.println("Enter empno");
            int eno = Integer.parseInt(scan.nextLine());
            System.out.println("Enter ename");
            String enm = scan.nextLine();
            System.out.println("Enter salary");
            double sl = Double.parseDouble(scan.nextLine());
            System.out.println("Enter desig");
            String dsg = scan.nextLine();
            System.out.println("Enter deptno");
            int dno = Integer.parseInt(scan.nextLine());
            Class.forName("oracle.jdbc.driver.OracleDriver");
            Connection con = DriverManager.getConnection(
                "jdbc:oracle:thin:@localhost:1521:xe", "scott", "tiger");
            PreparedStatement stmt = con.prepareStatement("insert into emp(empno,ename,sal,job,deptno) values(?,?,?,?,?)");
            stmt.setInt(1, eno);
            stmt.setString(2, enm);
            stmt.setDouble(3, sl);
            stmt.setString(4, dsg);
            stmt.setInt(5, dno);
            int noe = stmt.executeUpdate();
            if (noe > 0)
                System.out.println("Added");
            else
                System.out.println("Not added");
            con.close();
        } catch (SQLException e) {
            //System.out.println(e.getErrorCode()+"*"+e.getMessage());
            if (e.getErrorCode() == 1)
                System.out.println("Error:Duplicate entry");
            else if (e.getErrorCode() == 1438)
                System.out.println("Error: Larger data ");
            else if (e.getErrorCode() == 2291)
                System.out.println("Error: Invalid deptno");
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
    }
    public static void updateSal() {
        try {
            System.out.println("Enter empno");
            int eno = Integer.parseInt(scan.nextLine());
            Class.forName("oracle.jdbc.driver.OracleDriver");
            Connection con = DriverManager.getConnection(
                "jdbc:oracle:thin:@localhost:1521:xe", "scott", "tiger");
            PreparedStatement stmt = con.prepareStatement("UPDATE emp SET sal=? WHERE empno=?");
            stmt.setInt(2, eno);
            //check for existence, repeats in all below	
            PreparedStatement exist = con.prepareStatement("SELECT count(*) FROM emp WHERE empno=?");
            exist.setInt(1, eno);
            ResultSet existing = exist.executeQuery();
            existing.next();
            int exists = existing.getInt(1);
            //exist code finish
            int noe = 0;
            if (exists == 1) {
                System.out.println("Enter salary");
                double sl = Double.parseDouble(scan.nextLine());
                stmt.setDouble(1, sl);
                noe = stmt.executeUpdate();
            } else
                System.out.println("ID doesn't exist");

            if (noe > 0)
                System.out.println("Updated Salary");
            else
                System.out.println("Failed Updation");
            con.close();
        } catch (SQLException e) {
            if (e.getErrorCode() == 1438)
                System.out.println("Error: Larger data ");
        } catch (NumberFormatException e) {
            System.out.println("Takes integer and double as input only");
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
    }
    public static void deleteEmp() {
        try {
            System.out.println("Enter EMP ID: ");
            int eno = Integer.parseInt(scan.nextLine());
            Class.forName("oracle.jdbc.driver.OracleDriver");
            Connection con = DriverManager.getConnection(
                "jdbc:oracle:thin:@localhost:1521:xe", "scott", "tiger");
            PreparedStatement stmt = con.prepareStatement("DELETE FROM emp WHERE empno=?");
            PreparedStatement exist = con.prepareStatement("SELECT count(*) FROM emp WHERE empno=?");
            stmt.setInt(1, eno);
            exist.setInt(1, eno);
            ResultSet existing = exist.executeQuery();
            existing.next();
            int exists = existing.getInt(1);
            int noe = 0;
            if (exists == 1) {
                noe = stmt.executeUpdate();
                System.out.println("The record is deleted successfully");
            } else {
                System.out.println("EMP ID doesn't exist");
            }
        } catch (SQLException e) {
            if (e.getErrorCode() == 1438)
                System.out.println("Error: Larger data ");
        } catch (NumberFormatException e) {
            System.out.println("Takes integer and double as input only");
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
    }
    public static void displayDept() {
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "scott", "tiger");
            System.out.println("Enter the Department no.:");
            int deptno = Integer.parseInt(scan.nextLine());
            PreparedStatement stmt = con.prepareStatement("SELECT * FROM emp WHERE deptno=?");
            stmt.setInt(1, deptno);

            PreparedStatement exist = con.prepareStatement("SELECT count(*) FROM emp WHERE deptno=?");
            exist.setInt(1, deptno);
            ResultSet existing = exist.executeQuery();
            existing.next();
            int exists = existing.getInt(1);
            if (exists > 0) {
                ResultSet rs = stmt.executeQuery();
                while (rs.next())
                    System.out.println("|	" + rs.getInt(1) + "	|	" + rs.getString(2) + "	|	" + rs.getString(3) + "	|	" + rs.getInt(4) + "	|	" + rs.getString(5) + "	|	" + rs.getInt(6) + "	|	" + rs.getInt(7) + "	|");
            } else {
                System.out.println("Department ID doesn't exist.");
            }
        } catch (SQLException e) {
            if (e.getErrorCode() == 1438)
                System.out.println("Error: Larger data ");
        } catch (NumberFormatException e) {
            System.out.println("Takes integer and double as input only");
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
    }
    public static void displayAll() {
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "scott", "tiger");

            Statement existing = con.createStatement();
            ResultSet exist = existing.executeQuery("SELECT count(*) FROM emp");
            exist.next();
            int exists = exist.getInt(1);

            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM emp");

            if (exists > 0) {
                while (rs.next())
                    System.out.println("|	" + rs.getInt(1) + "	|	" + rs.getString(2) + "	|	" + rs.getString(3) + "	|	" + rs.getInt(4) + "	|	" + rs.getString(5) + "	|	" + rs.getInt(6) + "	|	" + rs.getInt(7) + "	|");
            } else {
                System.out.println("No Records in EMP Table");
            }
        } catch (SQLException e) {
            System.out.println(e.getErrorCode() + "*" + e.getMessage());
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
    }
}