JDBC.java

package uk.ac.qub.databases;

// Importing required SQL functions
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class AssessmentTwo {
    
    // Declare database login credentials as constants
    public final String URL = "jdbc:mysql://web2.eeecs.qub.ac.uk/40002617";
    public final String USER_NUMBER = "40002617";
    public final String PASSWORD = "CHZ8859";
    
    // Question One constants
    public PreparedStatement updateSalary;
    public String UPDATE_STRING = "update Staff_Members set salary = ? where idStaff = ?";
    public String RESULTS_ONE = "SELECT name, position, salary FROM Staff_Members";
    
    // Question Two constants
    public String RESULTS_TWO = "SELECT name, position, salary" 
                + " FROM Staff_Members WHERE salary >= 29000 AND salary <= 39000";
    
    // Question Three constants
    public PreparedStatement applyPayRise;
    public String APPLY_STRING = "update Staff_Members set salary"
        +" = salary*? where position = ?";
    public String RESULTS_THREE = "SELECT name, position, salary"
            +" FROM Staff_Members WHERE position = 'Manager'";
    
    /**
      * This is a Class which connects to a database containing a table called "Staff_Members".
     * The Class first uses an SQL Prepared Statement to update the salaries of all employees.
     * It then runs a queries  and outputs results so
     * the user can ensure the data has been updated correctly.
     * It then grants a 15% pay rise to all Management staff.
     * Finally, it closes the connection to the databases.
     * This main method calls the method "runningOrder".
     * @param args
     */
    public static void main(String[] args) {
        AssessmentTwo at = new AssessmentTwo();
        at.runningOrder();
    }
    
    /**
     * The following method is designed to call each of the Question methods in order.
     * The method also wraps the method calls in a call to the databaseConnect method.
     * The statement handles any SQL Exceptions that may occur,
     * it prints the user a message explaining why and where the error occurred.
     * There is also error-handling in place for the event of a ClassNotFoundException.
     * This method passes the connection object "con" to each of the methods,
     * this allows the methods to connection to the SQL server.
     */
    public void runningOrder(){
        try {
            Class.forName("com.mysql.jdbc.Driver");
            try (Connection con = databaseConnect()){
            questionOne(con);
            questionTwo(con);
            questionThree(con);
            } catch(SQLException ex) {
                System.err.println("SQLException: " + ex.getMessage());
            }
        } catch(java.lang.ClassNotFoundException e) {
            System.err.print("ClassNotFoundException: "); 
            System.err.println(e.getMessage());
        }
    }
    
    /**
     * This method takes the constant String login credentials from the top of the Class
     * and uses them to connect the Class to the SQL server using the DriverManager class
     * from the SQL library.
     * @return - This method returns a connection to the server.
     * @throws SQLException - Throws any SQL Exceptions (e.g. inaccurate login information).
     */
    public Connection databaseConnect() throws SQLException{
        return DriverManager.getConnection(URL, USER_NUMBER, PASSWORD);
    }
    
    /**
     * This is the method for the first question in the assessment.
     * This method uses the constant PreparedStatement object "updateSalary" to apply the
     * "UPDATE_STRING" SQL command. The values of Salary amount and Staff ID are selected
     * from within the Arrays below by the for loop statement which applies each salary
     * to the Staff ID in the corresponding position within the array.
     * The changes execute at the end of each iteration of the for loop.
     * The updateSalary object is then closed.
     * 
     * Finally, the constant resultSet query RESULTS_ONE
     * is then passed as a parameter (with the connection object)
     * to the getResults method.
     * @param con - Database Connection object
     * @throws SQLException - Throws any SQL Exceptions.
     */
    public void questionOne(Connection con) throws SQLException {
        updateSalary = con.prepareStatement(UPDATE_STRING);
        
        // Use the following arrays and for loop to populate salary fields correctly
        String [] salaryArray = {"45000", "40000", "35000", "42000", "29000",
                "29500", "47000", "34000",
                "30500", "42000", "44500", "41000", "29500", "29500", "30000", "34000"};
        
        String [] idArray = {"M001", "M002", "M003", "M004", "M005", "M006",
                    "M007", "M008", "M009", "M010",
                    "M011", "M012", "M013", "M014", "M015", "M016"};
        
        for (int loop =0; loop < salaryArray.length; loop++){
            updateSalary.setString(1, salaryArray[loop]);
            updateSalary.setString(2, idArray[loop]);
            updateSalary.executeUpdate();
        }
        // Close Prepared Statement and calling results method
        updateSalary.close();
        getResults(con, RESULTS_ONE);
    }
    
    /**
     * This is the method for the second question in the assessment.
     * This method passes the constant resultSet query RESULTS_TWO
     * (with the connection object) to the getResults method.
     * 
     * The RESULTS_TWO query will find the name, position and salary of all staff members
     * who earn between 29000 and 39000 (inclusive)
     * @param con - Database Connection object
     * @throws SQLException - Throws any SQL Exceptions
     */
    public void questionTwo(Connection con) throws SQLException{
        getResults(con, RESULTS_TWO);
    }
    
    /**
     * This is the method for the third question in the assessment.
     * This method uses the constant PreparedStatement object applyPayRise
     * to give all managers a 15% pay rise. It does this by multiplying their
     * current salaries by 1.15 and setting the result as their new salary.
     * 
     * This method passes the constant resultSet query RESULTS_THREE
     * (with the connection object) to the getResults method.
     * The query finds the name, position and salary of all Managers.
     * 
     * The applyPayRise object is closed. The server connection is closed.
     * @param con - Database Connection object
     * @throws SQLException - Throws any SQL Exceptions
     */
    public void questionThree(Connection con) throws SQLException{
        // Extending program to provide managers with a 15% pay raise
        applyPayRise = con.prepareStatement(APPLY_STRING);
        applyPayRise.setString(1, "1.15");
        applyPayRise.setString(2, "Manager");
        applyPayRise.executeUpdate();
        getResults (con, RESULTS_THREE);
        // Closing preparedStatement and Connection objects
        applyPayRise.close();
        con.close();
    }
    
    /**
     * This method calculates the results of the various queries
     * within the assignment and outputs the results to the screen.
     * A statement object is created to send the query String to the server
     * in order to query the database for results, the results
     * are then output to screen in a pre-determined format.
     * The format as Name first following by position and salary.
     * @param con - Database Connection object
     * @param query - A string holding a query to send to the server
     * @throws SQLException - Throws any SQL Exceptions
     */
    public void getResults(Connection con, String query) throws SQLException{
        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery(query);
        System.out.println("\nName\t\tPosition\t Salary");
        System.out.println("----------\t---------\t-------");
        // Printing results
        while (rs.next()) {
            String n = rs.getString("name");
            String p = rs.getString("position");
            String s = rs.getString("salary");
            System.out.println(n+" \t"+p+" \t" +s);
        }   
    }
}