/**
* Get all transportation routes
*
* @return List
*/
public List<TransportationRoute> getAllTransportationRoutes() throws Exception {
// Get all transportation types
TransportationTypeDAO transportationTypeDAO = new TransportationTypeDAO();
HashMap<String, TransportationType> transportationTypes =
ListConverter.getEntityListAsHashMap(transportationTypeDAO.getAllTransportationTypes());
// Fetch transportation routes
ArrayList<TransportationRoute> transportationRoutes = new ArrayList<>();
try {
String query = String.format("Select * from %s", table);
ResultSet result = ConnectionManager.getInstance().createStatement().executeQuery(query);
while (result.next()) {
TransportationRoute transportationRoute = new TransportationRoute();
transportationRoute.setId(UUID.fromString(result.getString(id)));
transportationRoute.setName(result.getString(name));
transportationRoute.setValidFrom(result.getTimestamp(validFrom));
transportationRoute.setValidUntil(result.getTimestamp(validUntil));
transportationRoute.setType(transportationTypes.get(result.getString(transportationID)));
transportationRoute.setOperator(result.getString(operator));
transportationRoute.setNetwork(result.getString(network));
transportationRoute.setExtRef(result.getString(extRef));
transportationRoute.setDescriptionFrom(result.getString(descriptionFrom));
transportationRoute.setDescriptionTo(result.getString(descriptionTo));
transportationRoute.setDescription(result.getString(description));
transportationRoute.setRouteNo(result.getString(routeNo));
transportationRoutes.add(transportationRoute);
}
ScheduleDAO scheduleDAO = new ScheduleDAO();
for (TransportationRoute transportationRoute : transportationRoutes) {
transportationRoute.setSchedules(scheduleDAO.getValidSchedules(transportationRoute));
}
} catch (Exception e) {
ConnectionManager.getInstance().closeConnection(true);
throw new Exception("Transportation route fetch failed: " + e.getMessage());
}
return transportationRoutes;
}
/**
* Insert transportation routes
*
* @param transportationRouteList List of transportation routes to insert (id == null)
* @throws Exception
*/
public void insertTransportationRoutes(Collection<TransportationRoute> transportationRouteList)
throws Exception {
try {
String query =
String.format(
"INSERT INTO %s (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
table,
id,
name,
validFrom,
validUntil,
transportationID,
operator,
network,
extRef,
description,
descriptionFrom,
descriptionTo,
routeNo);
PreparedStatement ps = ConnectionManager.getInstance().prepareStatement(query);
for (TransportationRoute transportationRoute : transportationRouteList) {
if (transportationRoute.getId() != null || transportationRoute.getType() == null) {
continue;
}
transportationRoute.setId(UUID.randomUUID());
PGobject toInsertUUID = new PGobject();
toInsertUUID.setType("uuid");
toInsertUUID.setValue(String.valueOf(transportationRoute.getId()));
ps.setObject(1, toInsertUUID);
ps.setString(2, transportationRoute.getName());
ps.setTimestamp(3, transportationRoute.getValidFrom());
ps.setTimestamp(4, transportationRoute.getValidUntil());
ps.setObject(5, transportationRoute.getType().getId());
ps.setString(6, transportationRoute.getOperator());
ps.setString(7, transportationRoute.getNetwork());
ps.setString(8, transportationRoute.getExtRef());
ps.setString(9, transportationRoute.getDescription());
ps.setString(10, transportationRoute.getDescriptionFrom());
ps.setString(11, transportationRoute.getDescriptionTo());
ps.setString(12, transportationRoute.getRouteNo());
ps.addBatch();
}
ps.executeBatch();
ps.close();
ScheduleDAO scheduleDAO = new ScheduleDAO();
scheduleDAO.insertOrUpdateSchedules(transportationRouteList);
} catch (Exception e) {
ConnectionManager.getInstance().closeConnection(true);
throw new Exception("Transportation route insert failed: " + e.getMessage());
}
}
/**
* Update transportation routes
*
* @param transportationRouteList List of transportation routes to insert (id != null)
* @throws Exception
*/
public void updateTransportationRoutes(Collection<TransportationRoute> transportationRouteList)
throws Exception {
try {
String query =
String.format(
"UPDATE %s SET %s=?, %s=?, %s=?, %s=?, %s=?, %s=?, %s=?, %s=?, %s=?, %s=?, %s=? where %s=?",
table,
name,
validFrom,
validUntil,
transportationID,
operator,
network,
extRef,
description,
descriptionFrom,
descriptionTo,
routeNo,
id);
PreparedStatement ps = ConnectionManager.getInstance().prepareStatement(query);
for (TransportationRoute transportationRoute : transportationRouteList) {
if (transportationRoute.getId() == null || transportationRoute.getType() == null) {
continue;
}
ps.setString(1, transportationRoute.getName());
ps.setTimestamp(2, transportationRoute.getValidFrom());
ps.setTimestamp(3, transportationRoute.getValidUntil());
ps.setObject(4, transportationRoute.getType().getId());
ps.setString(5, transportationRoute.getOperator());
ps.setString(6, transportationRoute.getNetwork());
ps.setString(7, transportationRoute.getExtRef());
ps.setString(8, transportationRoute.getDescription());
ps.setString(9, transportationRoute.getDescriptionFrom());
ps.setString(10, transportationRoute.getDescriptionTo());
ps.setString(11, transportationRoute.getRouteNo());
ps.setObject(12, transportationRoute.getId());
ps.addBatch();
}
ps.executeBatch();
ps.close();
ScheduleDAO scheduleDAO = new ScheduleDAO();
scheduleDAO.insertOrUpdateSchedules(transportationRouteList);
} catch (Exception e) {
ConnectionManager.getInstance().closeConnection(true);
throw new Exception("Transportation route update failed: " + e.getMessage());
}
}