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