public TrafficCountRecord getTrafficCountRecordById(String id) throws Exception {
TrafficCountRecord trafficCountRecord = new TrafficCountRecord();
try {
Connection connection = getConnection();
Statement s = connection.createStatement();
String sql =
"select id, tag, countdate, daytype, starttime,"
+ "endtime, w2, w3, pc, tx, ldv, ldc, hdc, mdb, hdb from trafficcount"
+ "where id = '"
+ id
+ "' ORDER BY countdate";
ResultSet r = s.executeQuery(sql);
while (r.next()) {
String tag = r.getString(2);
Date date = r.getDate(3);
String dayType = r.getString(4);
Date startTime = r.getDate(5);
Date endTime = r.getDate(6);
int W2 = r.getInt(7);
int W3 = r.getInt(8);
int PC = r.getInt(9);
int TX = r.getInt(10);
int LDV = r.getInt(11);
int LDC = r.getInt(12);
int HDC = r.getInt(13);
int MDB = r.getInt(14);
int HDB = r.getInt(15);
trafficCountRecord.setId(id);
trafficCountRecord.setDate(date);
trafficCountRecord.setDayType(dayType);
trafficCountRecord.setEndTime(endTime);
trafficCountRecord.setHDB(HDB);
trafficCountRecord.setHDC(HDC);
trafficCountRecord.setLDC(LDC);
trafficCountRecord.setLDV(LDV);
trafficCountRecord.setMDB(MDB);
trafficCountRecord.setPC(PC);
trafficCountRecord.setStartTime(startTime);
trafficCountRecord.setTag(tag);
trafficCountRecord.setTX(TX);
trafficCountRecord.setW2(W2);
trafficCountRecord.setW3(W3);
}
connection.close(); // returns connection to pool
} catch (SQLException e) {
logger.error(e.getMessage());
throw new Exception("There was an error executing the SQL: " + e.getMessage());
} catch (Exception e) {
logger.error(e.getMessage());
throw new Exception("Unknown exception: " + e.getMessage());
}
return trafficCountRecord;
}
public ArrayList<TrafficCountRecord> getTrafficCountRecords(StudyRegion region) throws Exception {
ArrayList<TrafficCountRecord> trafficCountRecordList = new ArrayList<TrafficCountRecord>();
try {
Connection connection = getConnection();
Statement s = connection.createStatement();
String sql =
"SELECT "
+ "t1.id, "
+ "t2.name as tag, "
+ "t1.countdate, "
+ "t1.daytype, "
+ "t1.starttime,"
+ "t1.endtime, "
+ "t1.w2, "
+ "t1.w3, "
+ "t1.pc, "
+ "t1.tx, "
+ "t1.ldv, "
+ "t1.ldc, "
+ "t1.hdc, "
+ "t1.mdb, "
+ "t1.hdb "
+ "FROM "
+ " trafficcount t1, "
+ " tagdetails t2 "
+ "WHERE "
+ " t1.regionid = '"
+ region.getId()
+ "' "
+ "AND "
+ " t1.tagid = t2.id "
+ "ORDER BY t1.countdate ASC, t1.starttime ASC";
logger.debug("SQL for getTrafficCountRecords: " + sql);
ResultSet r = s.executeQuery(sql);
while (r.next()) {
String id = r.getString(1);
String tag = r.getString(2);
Date date = r.getDate(3);
String dayType = r.getString(4);
Date startTime = r.getTimestamp(5);
Date endTime = r.getTimestamp(6);
int W2 = r.getInt(7);
int W3 = r.getInt(8);
int PC = r.getInt(9);
int TX = r.getInt(10);
int LDV = r.getInt(11);
int LDC = r.getInt(12);
int HDC = r.getInt(13);
int MDB = r.getInt(14);
int HDB = r.getInt(15);
TrafficCountRecord trafficCountRecord = new TrafficCountRecord();
trafficCountRecord.setId(id);
trafficCountRecord.setDate(date);
trafficCountRecord.setDayType(dayType);
trafficCountRecord.setEndTime(endTime);
trafficCountRecord.setHDB(HDB);
trafficCountRecord.setHDC(HDC);
trafficCountRecord.setLDC(LDC);
trafficCountRecord.setLDV(LDV);
trafficCountRecord.setMDB(MDB);
trafficCountRecord.setPC(PC);
trafficCountRecord.setStartTime(startTime);
trafficCountRecord.setTag(tag);
trafficCountRecord.setTX(TX);
trafficCountRecord.setW2(W2);
trafficCountRecord.setW3(W3);
trafficCountRecordList.add(trafficCountRecord);
}
connection.close(); // returns connection to the pool
} catch (SQLException e) {
logger.error(e.getMessage());
throw new Exception("There was an error executing the SQL: " + e.getMessage());
} catch (Exception e) {
e.printStackTrace();
logger.error(e.getMessage());
throw new Exception("Unknown exception: " + e.getMessage());
}
return trafficCountRecordList;
}