import java.sql.*;
import java.io.*;
import java.lang.*;
import oracle.jdbc.driver.*; //make sure this is in classpath
//import java.util.Scanner;
//----------------------------------------------------------------------------------
public class CallGet2 {

	static String servername = "147.252.224.76"; //"ferdia.student.comp.dit.ie";
	    static String portnumber = "1521";
	    static String sid = "ORA11GDB";
	    static String url = "jdbc:oracle:thin:@" + servername + ":" + portnumber + ":" + sid;
	    static String user = "lmariano";
	    static String pass = "c07685874";
	    static Connection conn = DriverManager.getConnection(url, user, pass);
	    //System.out.println(url);
	    //DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
	    //Connection conn = DriverManager.getConnection(url, user, pass);


  public static void main (String args [])
	throws SQLException, IOException {
    try {
	  Class.forName("oracle.jdbc.driver.OracleDriver");
      System.out.println("driver loaded");
    } catch (ClassNotFoundException e) {
	    System.out.println ("Could not load the driver");
	}
   /* String servername = "147.252.224.76"; //"ferdia.student.comp.dit.ie";
    String portnumber = "1521";
    String sid = "ORA11GDB";
    String url = "jdbc:oracle:thin:@" + servername + ":" + portnumber + ":" + sid;
    String user, pass;
	user = "lmariano";
	pass = "c07685874";
    System.out.println(url);*/
    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

    //Connection conn = DriverManager.getConnection(url, user, pass);
    System.out.println ("after connection");



	//Student.student = new Student();

	//Scanner input = new Scanner (System.in);





              // conn.close();


	System.out.print("\n\nLESSON CATEGORY TABLE\n\n");
    PreparedStatement pst2 = conn.prepareStatement("select * from lesson_category");
    ResultSet rs2 = pst2.executeQuery();

  	            System.out.println("catno\tcatname\tcatdesc");
  	            while (rs2.next()) {
  	                System.out.print(rs2.getInt(1) + "\t");
  	                System.out.print(rs2.getString(2) + "\t ");
  	                System.out.print(rs2.getString(3));
  	                System.out.print("\n");

  	            }

  	            //rs2.close();
  	            //pst2.close();
               //conn.close();



	   System.out.print("\n\nEDUCATION TABLE\n\n");
	   PreparedStatement pst3 = conn.prepareStatement("select * from education_level");
	   ResultSet rs3 = pst3.executeQuery();

					System.out.println("eduno\teduname\tedudesc");
					while (rs3.next()) {
						System.out.print(rs3.getInt(1) + "\t");
						System.out.print(rs3.getString(2) + "\t ");
						System.out.print(rs3.getString(3));
						System.out.print("\n\n");

					}

					rs3.close();
					pst3.close();



System.out.println("\n\nDelete\n\n");
String fname2 = readEntry("Enter student firstname to delete in the database : ");

String sql2 = "DELETE from student where firstname like ?";
PreparedStatement pst4 = conn.prepareStatement(sql2);
    pst4.setString(1,fname2);

    pst4.executeQuery();
    pst4.close();


int num = readNumber("Enter student number : ");
int enumber = readNumber("Enter edu : ");
int vnum = readNumber("Enter vis : ");
String fnam = readEntry("Enter fname : ");
String lnam = readEntry("Enter lname : ");
String dob = readEntry("Enter dob : ");

String sql1 = "INSERT INTO student values (?,?,?,?,?,?)";

PreparedStatement pstmt2 = conn.prepareStatement(sql1);

pstmt2.setInt(1,num);
pstmt2.setInt(2,enumber);
pstmt2.setInt(3,vnum);
pstmt2.setString(4,fnam);
pstmt2.setString(5,lnam);
pstmt2.setString(6,dob);

pstmt2.executeUpdate();
pstmt2.close();
display();
conn.close();


  	           /* System.out.println("catno\tcatname\tcatdesc");
  	            while (rs2.next()) {
  	                System.out.print(rs2.getInt(1) + "\t");
  	                System.out.print(rs2.getString(2) + "\t ");
  	                System.out.print(rs2.getString(3));
  	                System.out.print("\n");

  	            }*/
//conn.close();*/


  }


public static void display()
{
	Connection conn = DriverManager.getConnection(url, user, pass);



		System.out.print("\n\nSTUDENT TABLE\n\n");
	    PreparedStatement pst = conn.prepareStatement("select * from student");
		         	ResultSet rs = pst.executeQuery();

		            System.out.println("stno\teduno\tvisno\tfname\tlname\tdob");
		            while (rs.next()) {
		                System.out.print(rs.getInt(1) + "\t");
		                System.out.print(rs.getInt(2) + "\t ");
		                System.out.print(rs.getInt(3) + "\t ");
		                System.out.print(rs.getString(4) + "\t ");
		                System.out.print(rs.getString(5) + "\t ");
		                System.out.println(rs.getString(6));
		            }

		            rs.close();
	            pst.close();

}

//readEntry function -- to read input string
static String readEntry(String prompt) {
	   try {
		 StringBuffer buffer = new StringBuffer();
		 System.out.print(prompt);
		 System.out.flush();
		 int c = System.in.read();
		 while (c != '\n' && c != -1) {
		   buffer.append((char)c);
		   c = System.in.read();
	     							 }
	     return buffer.toString().trim();
      }  catch (IOException e) {
		 return "";
	     }
  }

//readNumber function -- to read input number
static int readNumber(String prompt)
	   throws IOException{
	   String snum;
	   int num = 0;
	   boolean numok;
	   do {
		 snum = readEntry(prompt);
		 try {
		   num = Integer.parseInt(snum);
		   numok = true;
	   } catch (NumberFormatException e) {
		   numok = false;
		   System.out.println("Invalid number; enter again");
	   }
  } while (!numok);
  return num;
  }


}