Example #1
0
  /**
   * retrieve ALL transactions
   *
   * @return an ArrayList of Transaction
   */
  public ArrayList<Transaction> retrieveAll() {
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    String sql = "";

    try {
      conn = ConnectionManager.getConnection();
      sql = "SELECT * FROM " + TABLE + ";";
      ps = conn.prepareStatement(sql);
      rs = ps.executeQuery();

      while (rs.next()) {
        // Retrieve by column name
        String rec_no = rs.getString("REC_NO");
        String project_name = rs.getString("PROJECT_NA");
        String address = rs.getString("ADDRESS");
        int no_of_units = rs.getInt("NO_OF_UNIT");
        int area_sqm = rs.getInt("AREA_SQM");
        String type_of_area = rs.getString("TYPE_OF_AR");
        int transacted_price = rs.getInt("TRANSACTED");
        int unit_price_psm = rs.getInt("UNIT_PRICE");
        int unit_price_psf = rs.getInt("UNIT_PRI_1");
        String contract_date = rs.getString("CONTRACT_D");
        String property_type = rs.getString("PROPERTY_T");
        String tenure = rs.getString("TENURE");
        String completion_date = rs.getString("COMPLETION");
        String type_of_sale = rs.getString("TYPE_OF_SA");
        String purchaser_address_indicator = rs.getString("PURCHASER_");
        int postal_district = rs.getInt("POSTAL_DIS");
        int postal_sector = rs.getInt("POSTAL_SEC");
        int postal_code = rs.getInt("POSTAL_COD");
        String planning_region = rs.getString("PLANNING_R");
        String planning_area = rs.getString("PLANNING_A");
        float x = rs.getFloat("X");
        float y = rs.getFloat("Y");

        Transaction newTransaction =
            new Transaction(
                rec_no,
                project_name,
                address,
                no_of_units,
                area_sqm,
                type_of_area,
                transacted_price,
                unit_price_psm,
                unit_price_psf,
                contract_date,
                property_type,
                tenure,
                completion_date,
                type_of_sale,
                purchaser_address_indicator,
                postal_district,
                postal_sector,
                postal_code,
                planning_region,
                planning_area,
                x,
                y);

        transactionList.add(newTransaction);
      }

    } catch (SQLException e) {
      System.out.print(e.getMessage());
    } finally {
      ConnectionManager.close(conn, ps, rs);
    }
    return transactionList;
  }
Example #2
0
  /**
   * retrieve by planning area
   *
   * @return an ArrayList of Transaction
   */
  public ArrayList<Transaction> retrieveByCriteria(String region_select) {
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    String sql = "";

    try {
      conn = ConnectionManager.getConnection();
      sql = "SELECT * FROM " + TABLE;
      // "(PLANNING_A = '"+planning_area+"') AND ";
      // + "(TRANSACTED BETWEEN "+" AND "+") AND "
      // + "(AREA_SQM BETWEEN "+" AND "+")"
      // +";";
      if (!region_select.equals("all")) {
        sql += " where ";
        if (region_select.equals("ccr")) {
          // must be "OR"
          sql += " postal_dis IN (9, 10, 11) OR " + " planning_a IN ('Downtown Core', 'Sentosa') ";
        } else if (region_select.equals("rcr")) {
          sql +=
              " planning_r = 'Central Region' AND "
                  + " postal_dis NOT IN (9, 10, 11) AND"
                  + " planning_a NOT IN ('Downtown Core', 'Sentosa') ";
        } else if (region_select.equals("ocr")) {
          sql += " planning_r != 'Central Region' ";
        }
      }

      sql += " ; ";

      ps = conn.prepareStatement(sql);
      rs = ps.executeQuery();

      while (rs.next()) {
        // Retrieve by column name
        String rec_no = rs.getString("REC_NO");
        String project_name = rs.getString("PROJECT_NA");
        String address = rs.getString("ADDRESS");
        int no_of_units = rs.getInt("NO_OF_UNIT");
        int area_sqm = rs.getInt("AREA_SQM");
        String type_of_area = rs.getString("TYPE_OF_AR");
        int transacted_price = rs.getInt("TRANSACTED");
        int unit_price_psm = rs.getInt("UNIT_PRICE");
        int unit_price_psf = rs.getInt("UNIT_PRI_1");
        String contract_date = rs.getString("CONTRACT_D");
        String property_type = rs.getString("PROPERTY_T");
        String tenure = rs.getString("TENURE");
        String completion_date = rs.getString("COMPLETION");
        String type_of_sale = rs.getString("TYPE_OF_SA");
        String purchaser_address_indicator = rs.getString("PURCHASER_");
        int postal_district = rs.getInt("POSTAL_DIS");
        int postal_sector = rs.getInt("POSTAL_SEC");
        int postal_code = rs.getInt("POSTAL_COD");
        String planning_region = rs.getString("PLANNING_R");
        String planning_area = rs.getString("PLANNING_A");
        float x = rs.getFloat("X");
        float y = rs.getFloat("Y");

        Transaction newTransaction =
            new Transaction(
                rec_no,
                project_name,
                address,
                no_of_units,
                area_sqm,
                type_of_area,
                transacted_price,
                unit_price_psm,
                unit_price_psf,
                contract_date,
                property_type,
                tenure,
                completion_date,
                type_of_sale,
                purchaser_address_indicator,
                postal_district,
                postal_sector,
                postal_code,
                planning_region,
                planning_area,
                x,
                y);

        transactionList.add(newTransaction);
      }

    } catch (SQLException e) {
      System.out.print(e.getMessage());
    } finally {
      ConnectionManager.close(conn, ps, rs);
    }
    return transactionList;
  }