import java.io.Console;
import java.util.Scanner;

import java.sql.*;
public class Example15_2 {
	public static Scanner input = new Scanner( System.in );
//public static Console console = System.console();
	public static void main(String[] args) {
		int c;
        while((c = menu()) != 6) {
           switch(c) {
             case 1:
        	   insertRecord();
        	   break;
             case 2:
            	 deleteRecord();
            	 break;
             case 3:
            	 updateRecord();
            	 break;
             case 4: 
            	 searchRecord();
            	 break;
             case 5:
            	 displayRecords();
            }
        }
    } //end main
	public static  int menu() {
		int choice;
		System.out.println(" 1. Insert Records : ");
		System.out.println(" 2. Delete a Record : ");
		System.out.println(" 3. update Records : ");
		System.out.println(" 4. Search a Record : ");
		System.out.println(" 5. Display All Records : ");
		System.out.print(" 6  Enter your select : ");
		choice = input.nextInt();
		return choice;
	}
    //*****************
	public static void insertRecord() {
		System.out.print("Enter Student Number : ");
	 	String stno = input.next(); 
		System.out.print("Enter Student Name : ");
		String name = input.next();
		System.out.print("Enter Student Average : ");
		float ave = input.nextFloat();
        try {
			Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/University", "root", "ghomi2020");
			Statement st = (Statement) conn.createStatement();
			String sql = "insert into Student(Stno, Name, Ave)";
			sql += "values ('" + stno + "', '" + name + "', '" + ave + "')";
           ((java.sql.Statement) st).executeUpdate(sql);
        } catch (SQLException e) {
			e.printStackTrace();
		}
        System.out.println("Connected With the database successfully");
	}
	//********************
	public static void deleteRecord() {
		System.out.print("Enter student number to delete : ");
		String stnum = input.next();
        try {
			Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/University", "root", "ghomi2020");
			Statement st = (Statement) conn.createStatement();
			String sql = "delete from  Student where Stno = '" + stnum + "'"; 
           ((java.sql.Statement) st).executeUpdate(sql);
        } catch (SQLException e) {
			e.printStackTrace();
		}
	}
	//*************************
	public static void searchRecord() {
		
	//	Scanner keyIn = new Scanner(System.in);

		

		System.out.print("Enter student number to search : ");
		String stnum = input.next();
        try {
		   Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/University", "root", "ghomi2020");
		   Statement st = (Statement) conn.createStatement();
		   String sql = "select * from  Student where Stno = '" + stnum + "'"; 
           ResultSet result = st.executeQuery(sql);
           if(!result.next())
                 System.out.println("Record not found ");
           else
        	   System.out.println("Record Found ");
           System.out.println("Press a key to continue! ");
           
           System.out.print("Press the enter key to continue");
           input.nextLine();
   	//	keyIn.nextLine();
   //		keyIn.close();
   		//keyIn.nextLine();    
           
           
           
        } catch (SQLException e) {
			e.printStackTrace();
		}
	}
	//***************************
	public static void updateRecord() {
		System.out.print("Enter student number and ave to update : ");
		String stnum = input.next();
		float ave = input.nextFloat();
        try {
		   Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/University", "root", "ghomi2020");
		   Statement st = (Statement) conn.createStatement();
		   String sql = "update Student set Ave =  '" + ave + "'"   + " where Stno = '" + stnum + "'"; 
		   int result = st.executeUpdate(sql);
		   System.out.println("Number of records affected :: " + result);
        } catch (SQLException e) {
			e.printStackTrace();
		}
	}
	//********************
	public static void displayRecords() {
        try {
		   Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/University", "root", "ghomi2020");
		   Statement st = (Statement) conn.createStatement();
		   String sql = "select * from  Student"; 
           ResultSet resultSet = st.executeQuery(sql);
           System.out.println("Stno      Name     Average");
           System.out.println("--------------------------");
           while(resultSet.next()){
              String stno = resultSet.getString("Stno");
   	       String name = resultSet.getString("Name");
              Float ave = resultSet.getFloat("Ave");
              //Printing Results
              System.out.println(stno + "        " + name + "     " + ave);
           } //end while
           input.next();
        } catch (SQLException e) {
			e.printStackTrace();
		}
	}
}
