import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLWarning;

import javax.swing.JOptionPane;
import javax.swing.JPasswordField;
import java.util.Scanner;

import java.io.File;  // Import the File class
import java.io.FileNotFoundException; 

public class TestDB4 {
    static private boolean debug = true;

    // JDBC driver name and database URL
    static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
    static final String URL = "jdbc:mysql://localhost:3306/";
    //static final String URL = "jdbc:mysql://127.0.0.1:3306/";

    //  Database credentials
    static final String DBASE = "company";
    static final String USER = "root";
    static final String PASS = "";

    public static void main(String[] args) {
        String inpdname;
        Scanner console = new Scanner(System.in);
        Connection conn = null;
        PreparedStatement stmt1 = null, stmt2 = null;
		String fileName;
		int [] projectNum = new int [10];
		int  projectNumQ;
		int counter=0;
		int counterQ=0;
		

        try {
            //STEP 2: Register JDBC driver
            Class.forName(JDBC_DRIVER);

            //STEP 3: Open a connection
            conn = DriverManager.getConnection(URL + DBASE, USER, PASS);

            if (conn != null) {
                System.out.println();
                System.out.println("Successfully connected to the " + DBASE + " database");
                System.out.println();
				System.out.println("Input the name of a testFile.");
				fileName=console.nextLine();
				try{
				File theFile = new File(fileName);
				Scanner reader = new Scanner (theFile)
				while(reader.hasNextLine()) {
					projectNum[counter]=reader.nextInt();
					counter++;
				}
				}
				testFile.close();
				catch (FileNotFoundException e) {
					System.out.println("An error occurred.");
					e.printStackTrace();
				}
					
					
				
                String pnumberQuery = "SELECT pnumber " +
                    "FROM project" ;
                stmt1 = conn.prepareStatement(outerQuery);
				String innerQuery = "SELECT pnumber,pname,dname,fname,lname FROM department,employee,project WHERE employee.ssn=department.mgrssn";
                stmt2 = conn.prepareStatement(innerQuery);

                stmt1.clearParameters();
                //STEP 4: Execute outer query
                ResultSet rs1 = stmt1.executeQuery();
                System.out.println("\nOuter loop output:");
                while (rs1.next()) {
                    projectNumQ = rs1.getInt("projectNum");
				
					
                    SQLWarning warning = stmt1.getWarnings();
                    if (warning != null) {
                        System.out.println("\n---Warning---\n");
                        while (warning != null) {
                            System.out.println("Message: " + warning.getMessage());
                            System.out.println("SQLState: " + warning.getSQLState());
                            System.out.print("Vendor error code: ");
                            System.out.println(warning.getErrorCode());
                            System.out.println("");
                            warning = warning.getNextWarning();
                        }
                    }
					if(projectNumQ == projectNum[counterQ])
					{
						
                    stmt2.clearParameters();
                    stmt2.setString(1, EmpSSN); // replaces the first argument (?) with EmpSSN value
                    //STEP 5: Execute inner query
                    ResultSet rs2 = stmt2.executeQuery();
                    System.out.println("    Inner loop output:");
                    while (rs2.next()) {
                        String dependentName = rs2.getString(1);
                        warning = stmt2.getWarnings();
                        if (warning != null) {
                            System.out.println("\n---Warning---\n");
                            while (warning != null) {
                                System.out.println("Message: " + warning.getMessage());
                                System.out.println("SQLState: " + warning.getSQLState());
                                System.out.print("Vendor error code: ");
                                System.out.println(warning.getErrorCode());
                                System.out.println("");
                                warning = warning.getNextWarning();
                            }
                        }
                        System.out.println("      " + dependentName);
                    }
                    rs2.close();
                }
                rs1.close();
            }
        } catch (SQLException se) {
            //Handle errors for JDBC
            se.printStackTrace();
        } catch (Exception e) {
            //Handle errors for Class.forName
            e.printStackTrace();
        } finally {
            //finally block used to close resources
            //STEP 6: Clean-up environment
            try {
                if (stmt1 != null)
                    stmt1.close();
                if (stmt2 != null)
                    stmt2.close();
            } catch (SQLException se2) {} // nothing we can do
            try {
                if (conn != null)
                    conn.close();
            } catch (SQLException se) {
                se.printStackTrace();
			}
            } //end finally try
        } //end try
        System.out.println("\nBye!");
    } //end main
} //end TestDB4