/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package javaapplication2;
import java.sql.*;
import java.util.Scanner;
/**
 *
 * @author placements2017
 */
public class JavaApplication2 {
    /**
     * @param args the command line arguments
     */
    public static void createTable() throws SQLException, ClassNotFoundException
    {

            Class.forName("com.mysql.jdbc.Driver");

           Connection connect = DriverManager.getConnection("jdbc:mysql://localhost/SOE?"
                 + "user=root&password=iiita");
           String create = "CREATE TABLE IF NOT EXISTS student(" +
                   "ID varchar(10) PRIMARY KEY, Name varchar(30),Fathers_Name varchar(30),Address varchar(50),age INTEGER, Mobile INTEGER, CGPA DECIMAL )";
           Statement st = connect.createStatement();
           st.execute(create);

        //catch(Exception e) { System.out.println(e);}
    }
    public static void prn()
    {
        System.out.println("\n-----------------\n1.Add Student\'s Data\n2.View Student\'s Data\n3.Edit\n4.Delete\n5.Exit\n");
    }
    public static void main(String[] args)
    {
        String id,name,father,address;
        int age,phone;//,cgpa;
        double cgpa;
        int i;
        String ch="0";
        Scanner sc = new Scanner(System.in);
        while(!ch.equals("5"))
        {
            prn();
            ch = sc.next();


            if(ch.equals("5")){ break;}
            else if(ch.equals("1"))
            {

            System.out.println("Enter rollno:");
            id = sc.next();
            System.out.println("Enter name:");
            name = sc.next();
            System.out.println("Enter Father\'s Name:");
            father = sc.next();
            System.out.println("Enter address:");
            address = sc.next();
            System.out.println("Enter age:");
            age = sc.nextInt();
            System.out.println("Enter phone:");
            phone = sc.nextInt();
            System.out.println("Enter cgpa:");
            cgpa = sc.nextDouble();
                try{
                    Class.forName("com.mysql.jdbc.Driver");

                    Connection connect = DriverManager.getConnection("jdbc:mysql://localhost/SOE?"
                    + "user=root&password=iiita");
                    String add = "INSERT INTO student values("+"\""+id+"\""+","+"\""+name+"\""+","+"\""+father+"\""+","+"\""+address+"\""+","+age+","+phone+","+cgpa+")";
                    //System.out.println(add);
                    Statement st = connect.createStatement();
                    st.execute(add);
                }
                catch(Exception e){ System.out.println(e);
            }
            }
            else if(ch.equals("2"))
            {
                String inp = sc.next();
                try{
                    Class.forName("com.mysql.jdbc.Driver");

                    Connection connect = DriverManager.getConnection("jdbc:mysql://localhost/SOE?"
                    + "user=root&password=iiita");
                    String query = "SELECT * FROM student WHERE id ="+"\""+inp+"\"";
                    //System.out.println(add);
                    Statement st = connect.createStatement();

                    ResultSet rs = st.executeQuery(query);
                    final Object[][] table = new String[1][];
                    table[0] = new String[] { "id", "name", "father","address","age","phone","cgpa" };
                    //int k = 1;
                    for (final Object[] row : table) {
                        System.out.format("%15s%15s%15s%15s%15s%15s%15s\n", row);
                    }
                    while(rs.next())
                    {
                        final Object[][] t = new String[1][];
                        t[0] = new String[] { rs.getString(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getString(6),rs.getString(7)};
                        //k++;
                        for (final Object[] row : t) {
                        System.out.format("%15s%15s%15s%15s%15s%15s%15s\n", row);
                    }
                    }

                }catch(Exception e){System.out.println(e);}
            }
            else if(ch.equals("3"))
            {

                System.out.println("Enter rollno:");
            id = sc.next();
            try{
                Class.forName("com.mysql.jdbc.Driver");

                    Connection connect = DriverManager.getConnection("jdbc:mysql://localhost/SOE?"
                    + "user=root&password=iiita");
                    String c = "SELECT * from student where id ="+"\""+id+"\"";
                    Statement st = connect.createStatement();
                    ResultSet resultSet = st.executeQuery(c);
                    if (!resultSet.next() ) {
                    System.out.println("no data");

}else {
            System.out.println("Enter name:");
            name = sc.next();
            System.out.println("Enter Father\'s Name:");
            father = sc.next();
            System.out.println("Enter address:");
            address = sc.next();
            System.out.println("Enter age:");
            age = sc.nextInt();
            System.out.println("Enter phone:");
            phone = sc.nextInt();
            System.out.println("Enter cgpa:");
            cgpa = sc.nextDouble();


                    String add = "UPDATE student SET name ="+"\""+name+"\",Fathers_name=\""+father+"\""+",address="+"\""+address+"\""+",age="+age+",Mobile="+phone+",cgpa="+cgpa+")";
                    //System.out.println(add);

                    st.execute(add);
                    }
                }
                catch(Exception e){ System.out.println(e);
            }
            }
            else if(ch.equals("4"))
            {
                String inp = sc.next();
                try{
                    Class.forName("com.mysql.jdbc.Driver");

                    Connection connect = DriverManager.getConnection("jdbc:mysql://localhost/SOE?"
                    + "user=root&password=iiita");
                    String query = "SELECT * FROM student WHERE id ="+"\""+inp+"\"";
                    //System.out.println(add);
                    Statement st = connect.createStatement();

                    ResultSet rs = st.executeQuery(query);
                    if(!rs.next())
                    {
                        System.out.println("No Data");
                    }
                    else
                    {
                        String q = "DELETE FROM student WHERE id=\""+inp+"\"";
                        st.execute(q);
                    }



                }catch(Exception e){System.out.println(e);}
            }
        }


    }

}