public static ArrayList<Trip> findTripsByCities(City fromCity, City toCity, String lang) {
ArrayList<Trip> trips = new ArrayList<Trip>();
// DBWorker dbWorker = new DBWorker();
// int fromCityId = fromCity.selectId();
// int toCityId = toCity.selectId();
ResultSet resultSet = null;
String query =
"SELECT * FROM trips, routes WHERE\n"
+ " routes.Id = trips.route \n"
+ " AND\n"
+ " routes.from_station = (SELECT Id FROM cities WHERE name_"
+ lang
+ " = '"
+ fromCity.getName()
+ "')\n"
+ " AND\n"
+ " routes.to_station = (SELECT Id FROM cities WHERE name_"
+ lang
+ " = '"
+ toCity.getName()
+ "')";
DBWorker dbWorker = new DBWorker();
resultSet = dbWorker.executeQuery(query);
try {
while (resultSet.next()) {
// trips.add(new Trip(resultSet.getInt("Id")));
}
} catch (SQLException e) {
e.printStackTrace();
}
return trips;
}
public static ArrayList<TripViewer> findTripsViewersByCities(
City fromCity, City toCity, String lang) {
ArrayList<TripViewer> tripViewers = new ArrayList<TripViewer>();
ResultSet resultSet = null;
String query =
"SELECT trips.Id, s1.name_"
+ lang
+ ", s2.name_"
+ lang
+ ", trips.departure, trips.arrival FROM trips, routes, stations s1, stations s2 WHERE\n"
+ " routes.Id = trips.route \n"
+ " AND\n"
+ " routes.from_station = s1.Id\n"
+ " AND \n"
+ " routes.to_station = s2.Id \n"
+ " AND\n"
+ " s1.city = (SELECT Id FROM cities WHERE name = '"
+ fromCity.getName()
+ "')\n"
+ " AND\n"
+ " s2.city = (SELECT Id FROM cities WHERE name = '"
+ toCity.getName()
+ "')";
DBWorker dbWorker = new DBWorker();
resultSet = dbWorker.executeQuery(query);
try {
while (resultSet.next()) {
// trips.add(new Trip(resultSet.getInt("Id")));
tripViewers.add(
new TripViewer(
resultSet.getInt(1),
resultSet.getString(2),
resultSet.getString(3),
resultSet.getDate(4),
resultSet.getDate(5)));
}
} catch (SQLException e) {
e.printStackTrace();
}
return tripViewers;
}