import java.sql.*;
import java.util.*;
public class DatabaseConnection
{
    static Connection cnct=null;
    static PreparedStatement pds=null;
    static {
        try{

            Class.forName("oracle.jdbc.driver.OracleDriver");
            cnct=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","manoj");
        }
        catch(Exception e) {
            e.printStackTrace();
        }
    }
    public static void main(String[] arg) throws Exception {
        int i=0;
        System.out.println("Enter your choice in the DatabaseConnection");
        System.out.println("1.To creat the table  2.To insert into the table  3.To update the table  4.To delete");
        try {
            for (; ; i++) {
                Scanner scan = new Scanner(System.in);
                int manoj = scan.nextInt();
                switch (manoj) {
                    case 1:
                        System.out.println("you had selected to create the table");
                        create();
                        System.out.println("Enter your choice to continue DatabaseConnection");
                        System.out.println("1.To creat the table  2.To insert into the table  3.To update the table  4.To delete");
                        break;
                    case 2:
                        System.out.println("you had selected to insert values into the table");
                        insert();
                        System.out.println("Enter your choice to continue DatabaseConnection");
                        System.out.println("1.To creat the table  2.To insert into the table  3.To update the table  4.To delete");
                        break;
                    case 3:
                        System.out.println("you had selected to update the table");
                        update();
                        System.out.println("Enter your choice to continue DatabaseConnection");
                        System.out.println("1.To creat the table  2.To insert into the table  3.To update the table  4.To delete");
                        break;
                    case 4:
                        System.out.println("you had selected to delete");
                        delete();
                        System.out.println("Enter your choice to continue DatabaseConnection");
                        System.out.println("1.To creat the table  2.To insert into the table  3.To update the table  4.To delete");
                        break;
                    default:
                        System.out.println("you have to select the above choices only");
                        System.out.println("you had given a wrong input");
                        System.exit(1);
                }

            }
        }

        finally{
            cnct.close();
        }

    }
    public static void create() throws Exception {
        Scanner scan=new Scanner(System.in);
        String column_name;
        String column_type;
        String size;
        String columns="";
        System.out.println("Enter the table name");
        String table_name=scan.nextLine();
        System.out.println("Enter the Number of Columns to be created");
        int NoOfColumn=scan.nextInt();
        int a=1;
        for(int i=0;i<NoOfColumn;i++){
            System.out.println("Enter column name, column type and column size");
            column_name=scan.nextLine();
            column_type=scan.nextLine();
            size=scan.nextLine();
            if(a<NoOfColumn) {
                columns += column_name + " " + column_type + "(" + size + ")" + ",";
                a++;
            }
            else{
                columns += column_name + " " + column_type + "(" + size + ")";
            }
        }
        String s="create table "+table_name+"("+columns+")";
        System.out.println("The query is"+s);
        pds=cnct.prepareStatement(s);
        int i = pds.executeUpdate();
        System.out.println(i);

    }
    public static void insert() throws Exception {
        Scanner scan = new Scanner(System.in);
        String column_value = "";
        System.out.println("Enter the table name");
        String table_name = scan.nextLine();
        int a = 1;
        String type = "";
        pds = cnct.prepareStatement("select * from " + table_name + "");
        ResultSet i = pds.executeQuery();
        System.out.println(i);
        ResultSetMetaData rsmd = pds.getMetaData();
        int count = rsmd.getColumnCount();
        System.out.println("Number of columns in the table is " + count);
        String n="number";
        String v="varchar";
        System.out.println("Give the values as per the column type");
        for (int k = 1; k <=count; k++) {
            type = rsmd.getColumnTypeName(k);
            System.out.println("The type of the column is " + type);
            if (a < count) {
                if (type == n) {
                    column_value += scan.nextLine() + ",";
                    a++;
                } else if (type == v) {
                    column_value += scan.nextLine() + ",";//Give char values in 'single quotes'
                    System.out.println(column_value);
                    a++;
                } else {
                    column_value += scan.nextLine() + ",";
                    a++;
                }
            }
                else {
                if(type==n) {
                    column_value += scan.nextLine();
                }
                else if(type==v){
                    column_value += scan.nextLine();
                }
                else{
                    column_value += scan.nextLine();
                }
            }
        }
            String b="insert into "+ table_name+" values "+"("+column_value+")";
        System.out.println(b);
            pds=cnct.prepareStatement(b);
            int manoj = pds.executeUpdate();
            System.out.println(manoj);
    }
        public static void update() throws Exception {
            Scanner scan=new Scanner(System.in);
            String column_update="";
            System.out.println("Enter the table name");
            String table_name = scan.nextLine();
            int a=1;
            String type = "";
            pds = cnct.prepareStatement("select * from " + table_name + "");
            ResultSet i = pds.executeQuery();
            System.out.println(i);
            ResultSetMetaData rsmd = pds.getMetaData();
            int count = rsmd.getColumnCount();
            System.out.println("Number of columns in the table is " + count);
            String[] chukka=new String[count];
            String[] manoj=new String[count];
            for(int r=1;r<=count;r++){
                chukka[r-1]=rsmd.getColumnName(r);
                manoj[r-1] = rsmd.getColumnTypeName(r);
                System.out.println("The name of the column and column type is   "+chukka[r-1]+"----->"+manoj[r-1]);
            }
            System.out.println("Given the input that how many columns need to be updated");
            int NoOfUpdates=scan.nextInt();
            String column="";
            String value="";
            System.out.println("Give the column name and column value as per the column type");
            System.out.println("NOTE: you have to give column name with '=' sign");
            for(int k=1;k<=NoOfUpdates;k++){
                column=scan.nextLine();
                value=scan.nextLine();
                if(a<NoOfUpdates) {
                    column_update += column + value+ ",";
                    a++;
                }
                else{
                    column_update += column + value;
                }
            }
            String condition ="";
            String column_con="";
            String column_val="";
            System.out.println("If you wanted to update at particular condition Enter true");
            boolean bool=scan.nextBoolean();
            if(bool==true) {
                System.out.println("Give the column name and column value as per the column type to update at particular condition");
                System.out.println("NOTE: you have to give column name with '=' sign");
                column_con=scan.nextLine();
                column_val=scan.nextLine();
                condition = column_con + column_val;
                String b="update "+ table_name+" set "+ column_update +" where "+ condition;
                System.out.println("The query to update is: "+b);
                pds=cnct.prepareStatement(b);
                int sai = pds.executeUpdate();
                System.out.println(sai);
            }
            else{
                String b="update "+ table_name+" set "+ column_update;
                System.out.println("The query to update is: "+b);
                pds=cnct.prepareStatement(b);
                int sai = pds.executeUpdate();
                System.out.println(sai);
            }
        }
        public static void delete() throws Exception {
            Scanner scan=new Scanner(System.in);
            System.out.println("Enter the table name");
            String table_name = scan.nextLine();
            System.out.println("If you wanted to delete at particular condition Enter true");
            boolean bool=scan.nextBoolean();
            if(bool==true) {
                pds = cnct.prepareStatement("select * from " + table_name + "");
                ResultSet i = pds.executeQuery();
                System.out.println(i);
                ResultSetMetaData rsmd = pds.getMetaData();
                int count = rsmd.getColumnCount();
                System.out.println("Number of columns in the table is " + count);
                String n="number";
                String v="varchar";
                String[] chukka=new String[count];
                String[] manoj=new String[count];
                for(int r=1;r<=count;r++){
                    chukka[r-1]=rsmd.getColumnName(r);
                    manoj[r-1] = rsmd.getColumnTypeName(r);
                    System.out.println("The name of the column and column type is "+chukka[r-1]+" "+manoj[r-1]);
                }
                System.out.println("Give the condition where the record to be deleted, give the column name and values as per the column type");
                System.out.println("NOTE: you have to give column name with '=' sign");
                String condition=scan.nextLine()+" "+scan.nextLine();
                String sai="delete from "+ table_name+" where "+condition;
                System.out.println("The given query is : "+sai);
                pds = cnct.prepareStatement(sai);
                int k = pds.executeUpdate();
                System.out.println(k);
            }
            else{
                String manoj="delete from "+ table_name;
                pds = cnct.prepareStatement(manoj);
                System.out.println("all rows are affected");
                int i = pds.executeUpdate();
                System.out.println(i);
            }
        }
    }