import java.sql.*;
import java.util.Scanner;
public class DBTest {
  public static void main (String args[]) {
    Connection theConn=null;
    ResultSet rs;   Statement stmt;
    String sql, id, name="",fac1="",fac2=""; float score=0;
    Scanner scan = new Scanner(System.in);
    System.out.print("Enter Your ID >>> ");
    id = scan.nextLine();
    try {  
      DBConnection  MyCon = new DBConnection();
      theConn = MyCon.getConnection();           
      sql =  "SELECT pstudent.sid, student.sname, pstudent.sscore, "
              + "faculty.fname FROM (pstudent INNER JOIN student ON "
              + "pstudent.sid = student.sid)INNER JOIN faculty ON "
              + "pstudent.fid1 = faculty.fid WHERE (pstudent.sid)=\'"
              + id + "\';";
      stmt = theConn.createStatement();      
      rs = stmt.executeQuery(sql);      
      while (rs.next()){
        id    = rs.getString(1);        
        name  = rs.getString(2);
        score = rs.getFloat(3);
        fac1  = rs.getString(4);
      }
      sql =  "SELECT pstudent.sid, faculty.fname "
              + "FROM (pstudent INNER JOIN faculty ON "
              + "pstudent.fid2 = faculty.fid) WHERE "
              + "(pstudent.sid)=\'" + id + "\';";
      stmt = theConn.createStatement();      
      rs = stmt.executeQuery(sql);
      while (rs.next())
        fac2  = rs.getString(2);
      if (fac2.equals("")) fac2 = "-";
      rs.close();
      stmt.close();
    }
    catch (Exception e) {
        System.out.println(e);
    }
    finally {
      try {
        if (theConn != null) theConn.close();
        if (name.equals("")) {
        System.out.println("SorrY !!! You Can't PASS tHe eXaM ...");
        System.exit(0);
      }
        System.out.println("======================================");
        System.out.println("    The Contest Examination Report    ");
        System.out.println("======================================");
        System.out.printf("%-5s%-10s","ID :", id);
        System.out.printf("%-5s%10s\n","Name  :", name);
        System.out.printf("%22s%10.2f%%\n","Score :",score);
        System.out.println("======================================");
        System.out.printf("%-5s%10s\n","1.", fac1);
        System.out.printf("%-5s%10s\n","2.", fac2);
        System.out.println("======================================");
      }
      catch (Exception e) {
        System.out.println(e);
      }
    }
  }
}