Positions.java

package com.example.scheduler_server;

import java.sql.*;
import java.util.Locale;

/*
    Name: Positions
    Description: Responsible for sending queries and receiving responses from the database that deal with the
    Positions table.
 */
public class Positions {

    // Object that facilitates the connection to the database
    private final Connection dbConnection;

    /*
    Name: Positions Constructor
    Parameters:
        Connection c: The connection to the database.
    Description: Instantiates a Positions object and sets the global connection for the methods.
    Return: Positions Object
     */
    protected Positions(Connection c) {
        this.dbConnection = c;
    }

    /*
    Name: addPosition
    Parameters:
        String position: A position to add.
        float wage: The wage of the position.
    Description: Adding a new position and wage to the table of possible positions.
    Return:
        String: If the positions was successfully added then the position and the wage separated by a slash is returned,
        otherwise an empty string is returned.
     */
    protected String addPosition(String position, float wage){
        String newPosition = "";
        try {
            Statement myStatement = dbConnection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            myStatement.executeUpdate("insert into Positions (position, wage) " +
                    "values ('" + position.toLowerCase(Locale.ROOT) +"', "+ wage + ")");
            ResultSet myRs = myStatement.executeQuery("select * from Positions where position = '" + position + "'");
            myRs.next();
            newPosition = myRs.getString("position") + "/" + myRs.getString("wage");
        } catch (SQLException exception) {
            exception.printStackTrace();
            return newPosition;
        }
        return newPosition;
    }

    /*
    Name: editPosition
    Parameters:
        String position: A position.
        float wage: The new wage of the position.
    Description: Changes the wage of an existing position.
    Return:
        String: If the positions was successfully edited then the position and the wage separated by a slash is returned,
        otherwise an empty string is returned.
     */
    protected String editPosition(String position, float newWage){
        String editedPosition = "";
        try {
            Statement myStatement = dbConnection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            myStatement.executeUpdate("update Positions set wage=" + newWage +
                    "where position='" + position.toLowerCase(Locale.ROOT) +"'");
            ResultSet myRs = myStatement.executeQuery("select * from Positions where position = '" + position + "'");
            myRs.next();
            editedPosition = myRs.getString("position") + "/" + myRs.getString("wage");
        } catch (SQLException exception) {
            exception.printStackTrace();
            return editedPosition;
        }
         return editedPosition;
    }

    /*
    Name: removePosition
    Parameters:
        String position: A position.
    Description: Removes a position and wage from the table of possible positions.
    Return:
        String: None
     */
    protected String removePosition(String position){
        try {
            Statement myStatement = dbConnection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            myStatement.executeUpdate("delete from Positions " +
                    "where position = '" + position.toLowerCase(Locale.ROOT) + "'");
        } catch (SQLException exception) {
            exception.printStackTrace();
            return "";
        }
        return position;
    }

    /*
    Name: allPositions
    Parameters:
    Description: Gets all the positions and wages.
    Return:
        String: Each position was separated by slashes and each detail is separated by commas.
     */
    protected String allPositions(){
        StringBuilder response = new StringBuilder("allPositions");
        try {
            Statement myStatement = dbConnection.createStatement();
            ResultSet myRs = myStatement.executeQuery("select * from Positions");
            while (myRs.next()) {
                response.append("/").append(myRs.getString("position"))
                        .append(",").append(myRs.getString("wage"));
            }
        } catch (SQLException exception) {
            exception.printStackTrace();
        }
        return response.toString();
    }
}