5.3

Suppose that we wish to find all courses that must be taken before some given course. That means finding not only the prerequisites of that course, but prerequisites of prerequisites, and so on. Write a complete Java program using JDBC that: * Takes a course_id value from the keyboard. * Finds prerequisites of that course using an SQL query submitted via JDBC. * For each course returned, find its prerequisites and continues this process iteratively until no new prerequisite courses are found. * Prints out the result

For this exercise, do not use a recursive SQL query, but rather use the iterative approach described previously. A well-developed solution will be robust to the error case where a university has accidentally created a cycle of prerequisites (that is, for example, course A is a prerequisite for course B, course B is a prerequisite for course C, and course C is a prerequisite for course A).

---

import java.sql.*;
import java.util.Scanner;
import java.util.Arrays;

public class AllCoursePrereqs { 
    public static void main(String[] args) { 
        try (
            Connection con = DriverManager.getConnection(
                "jdbc:oracle:thin:@edgar0.cse.lehigh.edu:1521:cse241","star","pw"
            );
            Statement s = con.createStatement();
        ) { 
            String q;
            String c;
            ResultSet result;
            int maxCourse = 0;
            q = "SELECT COUNT(*) AS C FROM course";
            result = s.executeQuery(q);
            if (!result.next()) System.out.println("Unexpected empty result");
            else maxCourse = Integer.parseInt(result.getString("C"));
            int numCourse = 0, oldNumCourse = -1;
            String[] prereqs = new String [maxCourse];
            Scanner krb = new Scanner(System.in);
            System.out.print("Input a course id (number): ");
            String course = krb.next();
            String courseString = "" + '\'' + course + '\'';
            while (numCourse != oldNumCourse) { 
                for (int i = oldNumCourse + 1; i < numCourse; i++) { 
                    courseString += ", " + '\'' + prereqs[i] + '\'';
                }
                oldNumCourse = numCourse;
                q = "SELECT prereq_id FROM prereq WHERE course_id IN (" + courseString + ")";
                result = s.executeQuery(q);
                while (result.next()) { 
                    c = result.getString("prereq_id");
                    boolean found = false;
                    for (int i = 0; i < numCourse; i++)
                        found |= prereqs[i].equals(c);
                    if (!found) prereqs[numCourse++] = c;
                }
                courseString = "" + '\'' + prereqs[oldNumCourse] + '\'';
            }
            Arrays.sort(prereqs,0,numCourse);
            System.out.print("The course that must be taken prior to " + course + " are: ");
            for (int i = 0; i < numCourse; i++)
                System.out.print((i==0 ? " " : ", ") + prereqs[i]);
            System.out.println();
        } catch (Exception e) { 
            e.printStackTrace();
        }
    }
}