Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /*
- Output:
- Martin Vasquez Seth Carney 3.98
- Tom Moody Richard Ellison 3.96
- Sameer Shah John Bracewell 3.96
- Merv Hughes Darren Lehmann 3.88
- Jim Slater Min Tuyet 3.88
- */
- package proj2;
- import java.sql.*;
- import java.io.PrintWriter;
- public class P3 {
- public static void main(String[] args) throws Exception {
- // Load and register a JDBC driver
- try {
- // Load the driver (registers itself)
- Class.forName("com.mysql.jdbc.Driver");
- } catch (Exception E) {
- System.err.println("Unable to load driver.");
- E.printStackTrace();
- }
- try {
- // Connect to the database
- Connection conn1;
- String dbUrl = "jdbc:mysql://csdb.cs.iastate.edu:3306/db363schultz1";
- String user = "dbu363schultz1";
- String password = "IxjD1867";
- conn1 = DriverManager.getConnection(dbUrl, user, password);
- System.out.println("*** Connected to the database ***");
- // Create Statement and ResultSet variables to use throughout the
- // project
- Statement statement = conn1.createStatement();
- ResultSet rs;
- // rs = statement.executeQuery("drop table Person");
- // rs = statement.executeQuery("drop table Instructor");
- // rs = statement.executeQuery("drop table Student");
- // rs = statement.executeQuery("drop table Course");
- // rs = statement.executeQuery("drop table Enrollment");
- // rs = statement.executeQuery("drop table Offering");
- //
- // rs = statement.executeQuery("create table Person (" + " "
- // + "Name char (20)," + " "
- // + "ID char (9) not null," + " "
- // + "Address char (30)," + " "
- // + "DOB date," + " "
- // + "Primary key (ID)");
- //
- // rs = statement.executeQuery("create table Instructor (" + " "
- // + "InstructorID char (9) not null references Person (ID)," + " "
- // + "Rank char (12)," + " "
- // + "Salary int," + " "
- // + "primary key (InstructorID))");
- //
- // rs = statement.executeQuery("create table Student (" + " "
- // + "StudentID char (9) not null references Person (ID)," + " "
- // + "Classification char (10)," + " "
- // + "GPA double," + " "
- // + "MentorID char (9) references Instructor (InstructorID)," + " "
- // + "CreditHours int," + " "
- // + "Primary key (StudentID))");
- //
- // rs = statement.executeQuery("create table Course (" + " "
- // + "CourseCode char (6) not null," + " "
- // + "CourseName char (50)," + " "
- // + "PreReq char (6))");
- //
- // rs = statement.executeQuery("create table Offering (" + " "
- // + "CourseCode char (6) not null references Course (CourseCode)," + " "
- // + "SectionNo int not null," + " "
- // + "InstructorID char (9) not null references Instructor (InstructorID)," + " "
- // + "Primary key (CourseCode, SectionNo))");
- //
- // rs = statement.executeQuery("create table Enrollment (" + " "
- // + "CourseCode char(6) NOT NULL," + " "
- // + "SectionNo int NOT NULL," + " "
- // + "StudentID char(9) NOT NULL references Student," + " "
- // + "Grade char(4) NOT NULL," + " "
- // + "primary key (CourseCode, StudentID)," + " "
- // + "foreign key (CourseCode, SectionNo) references Offering(CourseCode, SectionNo))");
- //
- // System.out.println("it made it this far");
- rs = statement.executeQuery("select S.StudentID, S.GPA, S.CreditHours, E.Grade" + " "
- + "from Student S, Enrollment E" + " "
- + "where S.StudentID = E.StudentID");
- double gradeValue = 0;
- while(rs.next()){
- switch(rs.getString("Grade").trim()){
- case "A": //do things
- gradeValue = 4.0;
- break;
- case "A-":
- gradeValue = 3.66;
- break;
- case "B+":
- gradeValue = 3.33;
- break;
- case "B":
- gradeValue = 3.0;
- break;
- case "B-":
- gradeValue = 2.66;
- break;
- case "C+":
- gradeValue = 2.33;
- break;
- case "C":
- gradeValue = 2.0;
- break;
- case "C-":
- gradeValue = 1.66;
- break;
- case "D+":
- gradeValue = 1.33;
- break;
- case "D":
- gradeValue = 1.00;
- break;
- case "F":
- gradeValue = 0.0;
- break;
- }
- int newCreditHours = rs.getInt("CreditHours") + 3;
- double newGPA = ((rs.getDouble("GPA") * rs.getInt("CreditHours")) + (gradeValue * 3)) / (newCreditHours);
- newGPA = Math.round(newGPA*100)/100.0;
- String newClassification = "";
- if(newCreditHours < 30){
- newClassification = "Freshman";
- }
- else if(newCreditHours < 60){
- newClassification = "Sophomore";
- }
- else if(newCreditHours < 90){
- newClassification = "Junior";
- }
- else{
- newClassification = "Senior";
- }
- PreparedStatement stmt2 =
- conn1.prepareStatement("update Student" + " "
- + "set GPA=?, Classification=?, CreditHours=?" + " "
- + "where StudentID=?"
- );
- stmt2.setDouble(1, newGPA);
- stmt2.setString(2, newClassification);
- stmt2.setInt(3, newCreditHours);
- stmt2.setString(4, rs.getString("StudentID"));
- stmt2.executeUpdate();
- }
- ResultSet rs2;
- rs2 = statement.executeQuery("select P.Name as SName, Q.Name as MName, S.GPA" + " "
- + "from Student S, Person P, Person Q" + " "
- + "where (S.Classification = 'Senior'" + " "
- + "and P.ID = S.StudentID" + " "
- + "and Q.ID = S.MentorID)" + " "
- + "order by S.GPA desc"
- );
- double lowestOfHigh = 0;
- rs2.next();
- PrintWriter writer = new PrintWriter("P3Output.txt");
- for(int i=0; i<5; i++){
- //System.out.println(rs2.getString("SName") + " " + rs2.getString("MName") + " " + rs2.getDouble("GPA"));
- writer.println(rs2.getString("SName") + " " + rs2.getString("MName") + " " + rs2.getDouble("GPA"));
- lowestOfHigh = rs2.getDouble("GPA");
- rs2.next();
- }
- while(rs2.getDouble("GPA") == lowestOfHigh){
- //System.out.println(rs2.getString("SName") + " " + rs2.getString("MName") + " " + rs2.getDouble("GPA"));
- writer.println(rs2.getString("SName") + " " + rs2.getString("MName") + " " + rs2.getDouble("GPA"));
- rs2.next();
- }
- // Close all statements and connections
- statement.close();
- rs.close();
- conn1.close();
- writer.close();
- } catch (SQLException e) {
- System.out.println("SQLException: " + e.getMessage());
- System.out.println("SQLState: " + e.getSQLState());
- System.out.println("VendorError: " + e.getErrorCode());
- }
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement