public void insertTour(Tour tour) throws Exception {
try {
Statement statement = connection.createStatement();
statement.executeUpdate(
String.format(
"INSERT INTO tour (location_id, start_date, count_days, price) "
+ "VALUES(%s, '%s', %s, %s)",
tour.getLocationId(),
tour.getStartDate().toString(),
tour.getCountDays(),
tour.getPrice()));
} finally {
closeConnention();
}
}
public void updateTour(Tour tour, String id) throws SQLException {
try {
Statement statement = connection.createStatement();
statement.executeUpdate(
String.format(
"UPDATE tour SET location_id=%s AND start_date='%s' "
+ "AND count_days=%s AND price=%s WHERE id=%s",
tour.getLocationId(),
tour.getStartDate().toString(),
tour.getCountDays(),
tour.getPrice(),
id));
} finally {
closeConnention();
}
}
public List<Tour> filterTours(String date, int price, String country) throws SQLException {
List<Tour> tours = new LinkedList<Tour>();
String whereCondition = getFilterWhereCondition(date, price, country);
try {
Statement statement = connection.createStatement();
ResultSet resultSet =
statement.executeQuery(
String.format(
"SELECT tour.id, tour.start_date, tour.count_days, tour.price, location.country FROM tour JOIN location ON tour.location_id = location.id WHERE start_date<'2015-06-01' AND price<1000 \n"
+ "AND country='Turkey'",
whereCondition));
while (resultSet.next()) {
Tour tour = new Tour();
tour.setId(resultSet.getInt("id"));
tour.setLocationId(resultSet.getInt("location_id"));
tour.setStartDate(resultSet.getDate("start_date"));
tour.setCountDays(resultSet.getInt("count_days"));
tour.setPrice(resultSet.getInt("price"));
tours.add(tour);
}
} finally {
closeConnention();
}
return tours;
}
public List<Tour> getTours() throws Exception {
List<Tour> tours = new LinkedList<Tour>();
try {
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery("SELECT * FROM tour");
while (resultSet.next()) {
Tour tour = new Tour();
tour.setId(resultSet.getInt("id"));
tour.setLocationId(resultSet.getInt("location_id"));
tour.setStartDate(resultSet.getDate("start_date"));
tour.setCountDays(resultSet.getInt("count_days"));
tour.setPrice(resultSet.getInt("price"));
tours.add(tour);
}
} finally {
closeConnention();
}
return tours;
}