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