//Import required packages
import java.sql.*;

public class jdbc {
   // JDBC driver name and database URL
   static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
   static final String DB_URL = "jdbc:mysql://localhost/STUDENTS";

   // Database credentials
   static final String USERNAME = "username";
   static final String PASSWORD = "password";


   /*
   * Function which returns point value based on the grades
   */
   public static float getGradeValue(String c) {
       if (c.equalsIgnoreCase("A")) {
           return (float) 4.0;
       } else if (c.equalsIgnoreCase("A-")) {
           return (float) 3.66;
       } else if (c.equalsIgnoreCase("B+")) {
           return (float) 3.33;
       } else if (c.equalsIgnoreCase("B")) {
           return (float) 3.00;
       } else if (c.equalsIgnoreCase("B-")) {
           return (float) 2.66;
       } else if (c.equalsIgnoreCase("C+")) {
           return (float) 2.33;
       } else if (c.equalsIgnoreCase("C")) {
           return (float) 2.00;
       } else if (c.equalsIgnoreCase("C-")) {
           return (float) 1.66;
       } else if (c.equalsIgnoreCase("D+")) {
           return (float) 1.33;
       } else if (c.equalsIgnoreCase("D")) {
           return (float) 1.00;
       } else {
           return 0;
       }
   }

   /*
   * Function which returns classification value based on the total credits of student
   */
   public static String getClassification(int creditHour) {
       if (creditHour < 30) {
           return "Freshman";
       } else if (creditHour < 60) {
           return "Sophomore";
       } else if (creditHour < 90) {
           return "Junior";
       } else {
           return "Senior";
       }
   }

   /**
   * Driver program. Please update the database setting such as driver, username password etc.
   * @param args
   */
   public static void main(String[] args) {
       Connection connection = null;
       Statement statement = null;
       try {
           // Register JDBC driver
           Class.forName("com.mysql.jdbc.Driver");

           // Open a connection
           System.out.println("Connecting to a selected database...");
           connection = DriverManager
                   .getConnection(DB_URL, USERNAME, PASSWORD);
           System.out.println("Connected database successfully...");

           // Execute a query
           System.out.println("Creating statement...");
           statement = connection.createStatement();

           String sql = "SELECT StudentID, Classification, GPA, CreditHours FROM Student";
           ResultSet outerResultSet = statement.executeQuery(sql);

           // Extract data from result set.. executing once for each student
           while (outerResultSet.next()) {

               // Retrieve Each Student record's individual columns
               int studentId = outerResultSet.getInt("StudentID");
               String classification = outerResultSet.getString("Classification");
               float gpa = outerResultSet.getFloat("GPA");
               int creditHour = outerResultSet.getInt("CreditHours");

               // Display values before updating
               System.out.println("\nBefore updating:");
               System.out.print("Student Id: " + studentId);
               System.out.print(", GPA: " + gpa);
               System.out.print(", Classification: " + classification);
               System.out.println(", CreditHour: " + creditHour);

               // select enrollment record for this particualr student
               sql = "SELECT StudentID, Grade FROM Enrollment where StudentID="
                       + studentId;
               ResultSet innerResultSet = statement.executeQuery(sql);

               int totalCreditHours = creditHour;
               float gpaSum = gpa * creditHour;
               while (innerResultSet.next()) {
                   String grade = innerResultSet.getString("Grade");
                   totalCreditHours += 3;
                   gpaSum += getGradeValue(grade) * 3;
               }

               // calculate final gpa by dividing the sum by total credits
               float gpaToBeUpdated = gpaSum / totalCreditHours;

               // update the new values for this student into the database
               sql = "UPDATE Student " +
                       "SET Classification = '" + getClassification(totalCreditHours) +
                       "', GPA = " + gpaToBeUpdated +
                       ", CreditHours = " + totalCreditHours +
                       "WHERE StudentID=" + studentId;
               statement.executeUpdate(sql);

               // again select
               sql = "SELECT StudentID, Classification, GPA, CreditHours FROM Student where StudentID="
                       + studentId;
               innerResultSet = statement.executeQuery(sql);

               // Retrieve by column name
               studentId = innerResultSet.getInt("StudentID");
               classification = innerResultSet.getString("Classification");
               gpa = innerResultSet.getFloat("GPA");
               creditHour = innerResultSet.getInt("CreditHours");

               System.out.println("\nAfter update:");
               System.out.print("Student Id: " + studentId);
               System.out.print(", GPA: " + gpa);
               System.out.print(", Classification: " + classification);
               System.out.println(", CreditHour: " + creditHour);
               System.out
                       .println("****************************************************************");

           }
           outerResultSet.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
           try {
               if (statement != null)
                   connection.close();
           } catch (SQLException se) {
           }// do nothing
           try {
               if (connection != null)
                   connection.close();
           } catch (SQLException se) {
               se.printStackTrace();
           }// end finally try
       }// end try

       System.out.println("Finishing program!");
   }
}