/** * 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; }
/** * 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; }