Advertisement
Guest User

Untitled

a guest
Feb 22nd, 2018
83
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 6.38 KB | None | 0 0
  1. /*
  2. Output:
  3. Martin Vasquez Seth Carney 3.98
  4. Tom Moody Richard Ellison 3.96
  5. Sameer Shah John Bracewell 3.96
  6. Merv Hughes Darren Lehmann 3.88
  7. Jim Slater Min Tuyet 3.88
  8. */
  9.  
  10. package proj2;
  11.  
  12. import java.sql.*;
  13. import java.io.PrintWriter;
  14.  
  15.  
  16. public class P3 {
  17.  
  18. public static void main(String[] args) throws Exception {
  19. // Load and register a JDBC driver
  20. try {
  21. // Load the driver (registers itself)
  22. Class.forName("com.mysql.jdbc.Driver");
  23. } catch (Exception E) {
  24. System.err.println("Unable to load driver.");
  25. E.printStackTrace();
  26. }
  27. try {
  28. // Connect to the database
  29. Connection conn1;
  30. String dbUrl = "jdbc:mysql://csdb.cs.iastate.edu:3306/db363schultz1";
  31. String user = "dbu363schultz1";
  32. String password = "IxjD1867";
  33. conn1 = DriverManager.getConnection(dbUrl, user, password);
  34. System.out.println("*** Connected to the database ***");
  35.  
  36. // Create Statement and ResultSet variables to use throughout the
  37. // project
  38. Statement statement = conn1.createStatement();
  39. ResultSet rs;
  40.  
  41. // rs = statement.executeQuery("drop table Person");
  42. // rs = statement.executeQuery("drop table Instructor");
  43. // rs = statement.executeQuery("drop table Student");
  44. // rs = statement.executeQuery("drop table Course");
  45. // rs = statement.executeQuery("drop table Enrollment");
  46. // rs = statement.executeQuery("drop table Offering");
  47. //
  48. // rs = statement.executeQuery("create table Person (" + " "
  49. // + "Name char (20)," + " "
  50. // + "ID char (9) not null," + " "
  51. // + "Address char (30)," + " "
  52. // + "DOB date," + " "
  53. // + "Primary key (ID)");
  54. //
  55. // rs = statement.executeQuery("create table Instructor (" + " "
  56. // + "InstructorID char (9) not null references Person (ID)," + " "
  57. // + "Rank char (12)," + " "
  58. // + "Salary int," + " "
  59. // + "primary key (InstructorID))");
  60. //
  61. // rs = statement.executeQuery("create table Student (" + " "
  62. // + "StudentID char (9) not null references Person (ID)," + " "
  63. // + "Classification char (10)," + " "
  64. // + "GPA double," + " "
  65. // + "MentorID char (9) references Instructor (InstructorID)," + " "
  66. // + "CreditHours int," + " "
  67. // + "Primary key (StudentID))");
  68. //
  69. // rs = statement.executeQuery("create table Course (" + " "
  70. // + "CourseCode char (6) not null," + " "
  71. // + "CourseName char (50)," + " "
  72. // + "PreReq char (6))");
  73. //
  74. // rs = statement.executeQuery("create table Offering (" + " "
  75. // + "CourseCode char (6) not null references Course (CourseCode)," + " "
  76. // + "SectionNo int not null," + " "
  77. // + "InstructorID char (9) not null references Instructor (InstructorID)," + " "
  78. // + "Primary key (CourseCode, SectionNo))");
  79. //
  80. // rs = statement.executeQuery("create table Enrollment (" + " "
  81. // + "CourseCode char(6) NOT NULL," + " "
  82. // + "SectionNo int NOT NULL," + " "
  83. // + "StudentID char(9) NOT NULL references Student," + " "
  84. // + "Grade char(4) NOT NULL," + " "
  85. // + "primary key (CourseCode, StudentID)," + " "
  86. // + "foreign key (CourseCode, SectionNo) references Offering(CourseCode, SectionNo))");
  87. //
  88. // System.out.println("it made it this far");
  89.  
  90.  
  91.  
  92. rs = statement.executeQuery("select S.StudentID, S.GPA, S.CreditHours, E.Grade" + " "
  93. + "from Student S, Enrollment E" + " "
  94. + "where S.StudentID = E.StudentID");
  95. double gradeValue = 0;
  96.  
  97. while(rs.next()){
  98.  
  99. switch(rs.getString("Grade").trim()){
  100. case "A": //do things
  101. gradeValue = 4.0;
  102. break;
  103. case "A-":
  104. gradeValue = 3.66;
  105. break;
  106. case "B+":
  107. gradeValue = 3.33;
  108. break;
  109. case "B":
  110. gradeValue = 3.0;
  111. break;
  112. case "B-":
  113. gradeValue = 2.66;
  114. break;
  115. case "C+":
  116. gradeValue = 2.33;
  117. break;
  118. case "C":
  119. gradeValue = 2.0;
  120. break;
  121. case "C-":
  122. gradeValue = 1.66;
  123. break;
  124. case "D+":
  125. gradeValue = 1.33;
  126. break;
  127. case "D":
  128. gradeValue = 1.00;
  129. break;
  130. case "F":
  131. gradeValue = 0.0;
  132. break;
  133. }
  134.  
  135. int newCreditHours = rs.getInt("CreditHours") + 3;
  136. double newGPA = ((rs.getDouble("GPA") * rs.getInt("CreditHours")) + (gradeValue * 3)) / (newCreditHours);
  137. newGPA = Math.round(newGPA*100)/100.0;
  138.  
  139. String newClassification = "";
  140. if(newCreditHours < 30){
  141. newClassification = "Freshman";
  142. }
  143. else if(newCreditHours < 60){
  144. newClassification = "Sophomore";
  145. }
  146. else if(newCreditHours < 90){
  147. newClassification = "Junior";
  148. }
  149. else{
  150. newClassification = "Senior";
  151. }
  152.  
  153. PreparedStatement stmt2 =
  154. conn1.prepareStatement("update Student" + " "
  155. + "set GPA=?, Classification=?, CreditHours=?" + " "
  156. + "where StudentID=?"
  157. );
  158. stmt2.setDouble(1, newGPA);
  159. stmt2.setString(2, newClassification);
  160. stmt2.setInt(3, newCreditHours);
  161. stmt2.setString(4, rs.getString("StudentID"));
  162. stmt2.executeUpdate();
  163.  
  164. }
  165.  
  166.  
  167. ResultSet rs2;
  168. rs2 = statement.executeQuery("select P.Name as SName, Q.Name as MName, S.GPA" + " "
  169. + "from Student S, Person P, Person Q" + " "
  170. + "where (S.Classification = 'Senior'" + " "
  171. + "and P.ID = S.StudentID" + " "
  172. + "and Q.ID = S.MentorID)" + " "
  173. + "order by S.GPA desc"
  174. );
  175.  
  176. double lowestOfHigh = 0;
  177. rs2.next();
  178. PrintWriter writer = new PrintWriter("P3Output.txt");
  179.  
  180. for(int i=0; i<5; i++){
  181. //System.out.println(rs2.getString("SName") + " " + rs2.getString("MName") + " " + rs2.getDouble("GPA"));
  182. writer.println(rs2.getString("SName") + " " + rs2.getString("MName") + " " + rs2.getDouble("GPA"));
  183. lowestOfHigh = rs2.getDouble("GPA");
  184. rs2.next();
  185. }
  186. while(rs2.getDouble("GPA") == lowestOfHigh){
  187. //System.out.println(rs2.getString("SName") + " " + rs2.getString("MName") + " " + rs2.getDouble("GPA"));
  188. writer.println(rs2.getString("SName") + " " + rs2.getString("MName") + " " + rs2.getDouble("GPA"));
  189. rs2.next();
  190. }
  191.  
  192.  
  193. // Close all statements and connections
  194. statement.close();
  195. rs.close();
  196. conn1.close();
  197. writer.close();
  198.  
  199.  
  200. } catch (SQLException e) {
  201. System.out.println("SQLException: " + e.getMessage());
  202. System.out.println("SQLState: " + e.getSQLState());
  203. System.out.println("VendorError: " + e.getErrorCode());
  204. }
  205. }
  206.  
  207. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement