JDBC

JDBC.java

package uk.ac.qub.assessedpracticaltwo;

// 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 final String UPDATE_STRING = "update Staff_Members set salary = ? where idStaff = ?";
	public final String RESULTS_ONE = "SELECT name, position, salary FROM Staff_Members";

	// Question Two constants
	public final String RESULTS_TWO = "SELECT name, position, salary FROM Staff_Members"+
					"WHERE salary BETWEEN 29000 AND  39000";

	// Question Three constants
	public final String APPLY_STRING = "update Staff_Members set salary ="+
					"salary*? where position = ?";
	public final String PAY_RISE = "1.15";
	public final String STAFF_POSITION = "Manager";
	public final 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 and closes the connection
	 * 
	 * 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 try with resources.
	 * This method passes the SQL autoclosable connection object "con" in each call.
	 * The statement handles any SQL Exceptions, and prints an explanatory message.
	 */
	public void runningOrder() {

		try (Connection con = databaseConnect()) {
			// Question One Update
			setSalary(con);
			// Question One Query
			ResultSet r1 = getResults(con, RESULTS_ONE);
			printResults(r1);
			// Question Two Query
			getResults(con, RESULTS_TWO);
			ResultSet r2 = getResults(con, RESULTS_TWO);
			printResults(r2);
			// Question Three Update
			payAdjust(con);
			// Question Three Query
			getResults(con, RESULTS_THREE);
			ResultSet r3 = getResults(con, RESULTS_THREE);
			printResults(r3);
		} catch (SQLException ex) {
			System.err.println("SQLException: " + ex.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
	 * SQL DriverManager class.
	 * 
	 * @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.
	 * 
	 * @param con
	 *            - Database Connection object
	 * @throws SQLException
	 *             - Throws any SQL Exceptions.
	 */
	public void setSalary(Connection con) throws SQLException {
		PreparedStatement updateSalary;
		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();
		}
		updateSalary.close();
	}

	/**
	 * 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. The
	 * applyPayRise object is closed. The server connection is closed.
	 * 
	 * @param con
	 *            - Connection object
	 * @throws SQLException
	 *             - Throws any SQL Exceptions
	 */
	public void payAdjust(Connection con) throws SQLException {
		// Extending program to provide managers with a 15% pay raise
		PreparedStatement applyPayRise;
		applyPayRise = con.prepareStatement(APPLY_STRING);
		applyPayRise.setString(1, PAY_RISE);
		applyPayRise.setString(2, STAFF_POSITION);
		applyPayRise.executeUpdate();
		// Closing preparedStatement and Connection objects
		applyPayRise.close();
	}

	/**
	 * This method calculates the results of the various queries within the
	 * assignment and returns the results as a ResultSet object.
	 * 
	 * @param con
	 *            - Database Connection object
	 * @param query
	 *            - A string holding a query to send to the server
	 * @throws SQLException
	 *  			- Throws any SQL Exceptions
	 */
	private ResultSet getResults(Connection con, String query) throws SQLException {
		Statement statement = con.createStatement();
		return statement.executeQuery(query);
	}
	
	/**
	 * This method takes the ResultSet objects generated in the getResults method
	 * and prints them to the screen in a pre-determined format.
	 * 
	 * @param results
	 * @throws SQLException
	 */
	private void printResults(ResultSet results) throws SQLException {
		
		System.out.printf("Name\t\tPosition\tSalary\n");
		System.out.printf("-----------\t---------\t------\n");
			// Printing results
			while (results.next()) {
				String name = results.getString("name");
				String position = results.getString("position");
				String salary = results.getString("salary");
				System.out.println(name + " \t" + position + " \t" + salary);
			}
			//New line to display separate results neatly
			System.out.println();
	}
}