DatabaseSelectHelper Comments

package com.bank.databasehelper;

import com.bank.accounts.Account;
import com.bank.accounts.ChequingAccount;
import com.bank.accounts.SavingsAccount;
import com.bank.accounts.TaxFreeSavingsAccount;
import com.bank.database.DatabaseSelector;
import com.bank.roles.Admin;
import com.bank.roles.Customer;
import com.bank.roles.Teller;
import com.bank.roles.User;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


/**
 * The Class DatabaseSelectHelper.
 */
public class DatabaseSelectHelper extends DatabaseSelector {

  /**
   * Gets the name of the role for a particular roleID.
   *
   * @param id the role id
   * @return the name of the role
   */
  public static String getRole(int id) {
    String role;
    Connection connection = DatabaseDriverHelper.connectOrCreateDataBase();
    try {
      // Attempt to get role from database
      role = DatabaseSelector.getRole(id, connection);
    } catch (SQLException e) {
      // If attempt to get role fails, role will be returned as null
      role = null;
    }
    try {
      connection.close();
    } catch (SQLException e) {
      System.err.println("Failed to close connection to database");
    }
    return role;
  }

  /**
   * Gets the hashed password of a user in the database.
   *
   * @param userId the user id
   * @return the hashed password for the user
   */
  public static String getPassword(int userId) {
    Connection connection = DatabaseDriverHelper.connectOrCreateDataBase();
    String hashPassword;
    try {
      // Attempt to get password from database
      hashPassword = DatabaseSelector.getPassword(userId, connection);
    } catch (SQLException e) {
      // If attempt to get password fails, password will be returned as null
      hashPassword = null;
    }
    try {
      connection.close();
    } catch (SQLException e) {
      System.err.println("Failed to close connection to database");
    }
    return hashPassword;

  }

  /**
   * Gets the user details (returned as a user object).
   *
   * @param userId the user id
   * @return a user object holding user information
   */
  public static User getUserDetails(int userId) {
    User user = null;
    ResultSet results;
    Connection connection = DatabaseDriverHelper.connectOrCreateDataBase();
    try {
      // Attempt to get user details from the database
      results = DatabaseSelector.getUserDetails(userId, connection);
    } catch (SQLException e) {
      // If attempt to get user details fails, it will be returned as null
      results = null;
    }
    // Initialize variables to store user details
    String name = "";
    int age = -1;
    String address = "";
    int roleid = -1;

    // Parse the resultSet for the user details
    try {
      if (!results.isClosed()) {
        while (results.next()) {
          name = results.getString("NAME");
          age = results.getInt("AGE");
          address = results.getString("ADDRESS");
          roleid = results.getInt("ROLEID");
        }
        // Determine which role type the user is
        String role = getRole(roleid);
        // Call a helper that creates a user object according to the user details
        user = createUserOfRole(role, userId, name, age, address);
      }
    } catch (SQLException e) {
      System.err.println("Error while parsing data from database.");
    }
    try {
      connection.close();
    } catch (SQLException e) {
      System.err.println("Failed to close connection to database");
    }
    return user;
  }

  /**
   * Gets the account ids belonging to a particular user.
   *
   * @param userId the user id
   * @return the account ids (owned by the user)
   */
  public static List<Integer> getAccountIds(int userId) {
    // Create an arrayList to store all account ids
    List<Integer> accountIds = new ArrayList<Integer>();
    Connection connection = DatabaseDriverHelper.connectOrCreateDataBase();
    try {
      // Attempt to get account ids from the database
      ResultSet results = DatabaseSelector.getAccountIds(userId, connection);
      // Parse through table of account ids and store them in the list
      while (results.next()) {
        accountIds.add(results.getInt("ACCOUNTID"));
      }
    } catch (SQLException e) {
      // If attempt to get account ids fails, the list will be returned empty
      e.printStackTrace();
    }

    try {
      connection.close();
    } catch (SQLException e) {
      System.err.println("Failed to close connection to database");
    }
    return accountIds;
  }

  /**
   * Gets the account details for a specifics account.
   *
   * @param accountId the account id
   * @return the account details
   * @throws SQLException the SQL exception
   */
  public static Account getAccountDetails(int accountId) {
    Account account = null;
    ResultSet results;
    Connection connection = DatabaseDriverHelper.connectOrCreateDataBase();
    try {
      // Attempt to get account details from the database
      results = DatabaseSelector.getAccountDetails(accountId, connection);
    } catch (SQLException e) {
      System.err.println("Failed to get details for the given userId.");
      results = null;
    }
    String name = "";
    String balance = "";
    int type = -1;
    try {
      // try to get the name, type and balance of the account
      if (!results.isClosed()) {
        while (results.next()) {
          name = results.getString("NAME");
          balance = results.getString("BALANCE");
          type = results.getInt("TYPE");
        }
        String typeName = getAccountTypeName(type);
        // create account using the retrieved attributes
        account = createAccountOfType(typeName, accountId, name, new BigDecimal(balance));
      }
    } catch (SQLException e) {
      System.err.println("Error while parsing data from database.");
    }
    try {
      connection.close();
    } catch (SQLException e) {
      System.err.println("Failed to close connection to database");
    }
    return account;
  }


  /**
   * Gets the balance.
   *
   * @param accountId the account id
   * @return the balance of the account
   * @throws SQLException the SQL exception
   */
  public static BigDecimal getBalance(int accountId) {
    Connection connection = DatabaseDriverHelper.connectOrCreateDataBase();
    BigDecimal balance;
    try {
      // attempt to get balance
      balance = DatabaseSelector.getBalance(accountId, connection);
    } catch (SQLException e) {
      balance = null;
    }
    try {
      connection.close();
    } catch (SQLException e) {
      System.err.println("Failed to close connection to database");
    }
    return balance;
  }

  /**
   * Gets the interest rate.
   *
   * @param accountType the account type
   * @return the interest rate of the account
   * @throws SQLException the SQL exception
   */
  public static BigDecimal getInterestRate(int accountType) {
    BigDecimal interestRate;
    Connection connection = DatabaseDriverHelper.connectOrCreateDataBase();
    try {
      // attempt to get the interest rate
      interestRate = DatabaseSelector.getInterestRate(accountType, connection);
    } catch (SQLException e) {
      interestRate = null;
    }
    try {
      connection.close();
    } catch (SQLException e) {
      System.err.println("Failed to close connection to database");
    }
    return interestRate;
  }

  /**
   * Gets the account types ids.
   *
   * @return the account types ids
   * @throws SQLException the SQL exception
   */
  public static List<Integer> getAccountTypesIds() {
    List<Integer> ids = new ArrayList<>();
    ResultSet results;
    Connection connection = DatabaseDriverHelper.connectOrCreateDataBase();
    try {
      // attempt to get list of account type id's
      results = DatabaseSelector.getAccountTypesId(connection);
      while (results.next()) {
        ids.add(results.getInt("ID"));
      }
    } catch (SQLException e) {
      System.err.println("Failed to fetch account type data.");
    }
    try {
      connection.close();
    } catch (SQLException e) {
      System.err.println("Failed to close connection to database");
    }
    return ids;
  }

  /**
   * Gets the account type name.
   *
   * @param accountTypeId the account type id
   * @return the account type name
   * @throws SQLException the SQL exception
   */
  public static String getAccountTypeName(int accountTypeId) {
    String typeName;
    Connection connection = DatabaseDriverHelper.connectOrCreateDataBase();
    try {
      // attempt to get account type name
      typeName = DatabaseSelector.getAccountTypeName(accountTypeId, connection);
    } catch (SQLException e) {
      typeName = null;
    }
    try {
      connection.close();
    } catch (SQLException e) {
      System.err.println("Failed to close connection to database");
    }
    return typeName;
  }

  /**
   * Gets the roles.
   *
   * @return the roles
   * @throws SQLException the SQL exception
   */
  public static List<Integer> getRoles() {
    List<Integer> ids = new ArrayList<>();
    Connection connection = DatabaseDriverHelper.connectOrCreateDataBase();
    try {
      // attempt to get list of roles
      ResultSet results = DatabaseSelector.getRoles(connection);
      while (results.next()) {
        ids.add(results.getInt("ID"));
      }
    } catch (SQLException e) {
      System.err.println("Failed to fetch role data.");
    }
    try {
      connection.close();
    } catch (SQLException e) {
      System.err.println("Failed to close connection to database");
    }
    return ids;

  }

  /**
   * Gets the account type.
   *
   * @param accountId the account type id
   * @return the account type
   * @throws SQLException the SQL exception
   */
  public static int getAccountType(int accountId) {
    int accountType;
    Connection connection = DatabaseDriverHelper.connectOrCreateDataBase();
    try {
      // attempt to get account type
      accountType = DatabaseSelector.getAccountType(accountId, connection);
    } catch (SQLException e) {
      accountType = -1;
    }
    try {
      connection.close();
    } catch (SQLException e) {
      System.err.println("Failed to close connection to database");
    }
    return accountType;

  }

  /**
   * Gets the user role.
   *
   * @param userId the user id
   * @return the user role
   * @throws SQLException the SQL exception
   */
  public static int getUserRole(int userId) {
    int userRole;
    Connection connection = DatabaseDriverHelper.connectOrCreateDataBase();
    try {
      // attempt to get user role
      userRole = DatabaseSelector.getUserRole(userId, connection);
    } catch (SQLException e) {
      userRole = -1;
    }
    try {
      connection.close();
    } catch (SQLException e) {
      System.err.println("Failed to close connection to database");
    }
    return userRole;
  }

  /**
   * Creates the user of role.
   *
   * @param role the user role
   * @param userId the user id
   * @param name the user name
   * @param age the user age
   * @param address the user address
   * @return the user
   */
  private static User createUserOfRole(String role, int userId, String name, int age,
      String address) {
    User newUser = null;
    // check if role is Admin
    if (role.equalsIgnoreCase("Admin")) {
      newUser = new Admin(userId, name, age, address);
    // check if role is Teller
    } else if (role.equalsIgnoreCase("Teller")) {
      newUser = new Teller(userId, name, age, address);
    // check if role is Customer
    } else if (role.equalsIgnoreCase("Customer")) {
      Customer newCustomer = new Customer(userId, name, age, address);
      List<Integer> accountIds = getAccountIds(userId);
      for (int accountId : accountIds) {
        newCustomer.addAccount(getAccountDetails(accountId));
      }
      newUser = newCustomer;
    }
    return newUser;
  }

  /**
   * Creates the account of type.
   *
   * @param type the account type
   * @param id the account id
   * @param name the account name
   * @param balance the account balance
   * @return the account
   */
  private static Account createAccountOfType(String type, int id, String name, BigDecimal balance) {
    Account newAccount = null;
    // check if account is chequing
    if (type.equalsIgnoreCase("Chequing")) {
      newAccount = new ChequingAccount(id, name, balance);
    // check if account is saving
    } else if (type.equalsIgnoreCase("Saving")) {
      newAccount = new SavingsAccount(id, name, balance);
    // check if account is TFSA
    } else if (type.equalsIgnoreCase("TFSA")) {
      newAccount = new TaxFreeSavingsAccount(id, name, balance);
    }
    return newAccount;
  }

}