package dao.impl;
import dao.IDAO;
import dao.IFlightDAO;
import dao.tro.Airport;
import dao.tro.Flight;
import org.apache.log4j.Logger;
import sky.exc.DBException;
import java.sql.*;
import java.util.ArrayList;
import java.util.Collections;
import java.util.LinkedList;
import java.util.List;
public class HSQLDBFlightDAO extends IDAO implements IFlightDAO{
private Connection connection=null;
private final static Logger logs = Logger.getLogger(HSQLDBFlightDAO.class);
public HSQLDBFlightDAO() {
connection = HSQLDBDAOFactory.getConnection();
}
public void insert (Flight flight) throws DBException{
try {
//подтягиваем ид аэропорта отбытия по полученному имени
String query = "SELECT Id FROM Airports WHERE name = ?";
PreparedStatement statement = connection.prepareStatement(query);
statement.setString(1, flight.getDepartureAirport().getName());
ResultSet resultSet = statement.executeQuery(query);
resultSet.next();
Long departureAirport = resultSet.getLong("Id");
//подтягиваем ид аэропорта прибытия по имени
statement.setString(1, flight.getDestinationAirport().getName());
resultSet = statement.executeQuery(query);
resultSet.next();
Long destinationAirport = resultSet.getLong("Id");
//вставляем полет в бд
query = "INSERT INTO Flights VALUES (?, ?, ?, ?, ?)";
statement = connection.prepareStatement(query);
statement.setLong(1, flight.getId());
statement.setLong(2, departureAirport);
statement.setLong(3, destinationAirport);
statement.setInt(4, flight.getGate());
statement.setTimestamp(5, flight.getT());
statement.executeUpdate(query);
} catch (SQLException e) {
logs.error("Ошибка вставки " + e);
}
}
public void update (Flight flight) throws DBException{
try {
//подтягиваем ид аэропорта отбытия по имени
String query = "SELECT Id FROM Airports WHERE Name=?";
PreparedStatement statement = connection.prepareStatement(query);
statement.setString(1, flight.getDepartureAirport().getName());
ResultSet resultSet = statement.executeQuery();
resultSet.next();
Long departureAirportId = resultSet.getLong("Id");
//подтягиваем ид аэропорта прибытия по имени
query = "SELECT Id FROM Airports WHERE Name=?";
statement = connection.prepareStatement(query);
statement.setString(1, flight.getDepartureAirport().getName());
resultSet = statement.executeQuery();
resultSet.next();
Long destinationAirportId = resultSet.getLong("Id");
//обновляем рейс
query = "UPDATE Flights SET DepartureAirport = ?, DestinationAirport = ?, Gate = ?, t = ? WHERE Id = ?";
statement = connection.prepareStatement(query);
statement.setLong(5, flight.getId());
statement.setLong(1, departureAirportId);
statement.setLong(2, destinationAirportId);
statement.setInt(3, flight.getGate());
statement.setTimestamp(4, flight.getT());
statement.executeUpdate(query);
} catch (SQLException e) {
logs.error("Ошибка изменения "+e);
}
}
public void delete (long id) {
super.delete(id);
}
public List<Flight> searchForFlightsFromAirport(String departureAirportName){
List<Flight> flightList = new ArrayList<Flight>();
try {
//подтягиваем имя аэропорта отбытия
String query = "SELECT Id FROM Airports WHERE Airports.Name = ?";
PreparedStatement statement = connection.prepareStatement(query);
statement.setString(1, departureAirportName);
ResultSet resultSet = statement.executeQuery();
resultSet.next();
Long departureAirportId = resultSet.getLong("Id");
//вытягиваем необходимые нам рейсы
query = "SELECT Flights.Id, Flights.destinationAirport, Flights.Gate, Flights.t, Airports.Name AS AName " +
"FROM Flights INNER JOIN Airports ON Flights.destinationAirport=Airports.Id WHERE Flights.departureAirport= ?";
PreparedStatement st = connection.prepareStatement(query);
st.setLong(1, departureAirportId);
resultSet = st.executeQuery();
//заносим их в список
int listIterator = 0;
while (resultSet.next()) {
flightList.add(new Flight());
flightList.get(listIterator).setId(resultSet.getLong("Id"));
flightList.get(listIterator).setDepartureAirport(new Airport());
flightList.get(listIterator).getDepartureAirport().setId(departureAirportId);
flightList.get(listIterator).getDepartureAirport().setName(departureAirportName);
flightList.get(listIterator).setDestinationAirport(new Airport());
flightList.get(listIterator).getDestinationAirport().setId(resultSet.getLong("destinationAirport"));
flightList.get(listIterator).getDestinationAirport().setName(resultSet.getString("AName"));
flightList.get(listIterator).setGate(resultSet.getInt("Gate"));
listIterator++;
}
} catch (SQLException e) {
logs.error("Ошибка выборки из БД(из) "+e);
}
return flightList;
}
public List<Flight> searchForFlightsToAirport(String destinationAirportName){
List<Flight> flightList = new ArrayList<Flight>();
try {
//подтягиваем имя аэропорта прибытия
String query = "SELECT Id FROM Airports WHERE Airports.Name = ?";
PreparedStatement statement = connection.prepareStatement(query);
statement.setString(1, destinationAirportName);
ResultSet resultSet = statement.executeQuery();
resultSet.next();
Long destinationAirportId = resultSet.getLong("Id");
//вытягиваем необходимые нам рейсы
query = "SELECT Flights.Id, Flights.departureAirport, Flights.Gate, Flights.t, Airports.Name AS AName " +
"FROM Flights INNER JOIN Airports ON Flights.departureAirport=Airports.Id WHERE Flights.destinationAirport= ?";
PreparedStatement st = connection.prepareStatement(query);
st.setLong(1, destinationAirportId);
resultSet = st.executeQuery();
//заносим их в список
int listIterator =0;
while (resultSet.next()) {
flightList.add(new Flight());
flightList.get(listIterator).setId(resultSet.getLong("Id"));
flightList.get(listIterator).setDepartureAirport(new Airport());
flightList.get(listIterator).getDepartureAirport().setId(resultSet.getLong("departureAirport"));
flightList.get(listIterator).getDepartureAirport().setName(resultSet.getString("AName"));
flightList.get(listIterator).setDestinationAirport(new Airport());
flightList.get(listIterator).getDestinationAirport().setId(destinationAirportId);
flightList.get(listIterator).getDestinationAirport().setName(destinationAirportName);
flightList.get(listIterator).setGate(resultSet.getInt("Gate"));
listIterator++;
}
} catch (SQLException e) {
logs.error("Ошибка выборки из БД(В) "+e);
}
return flightList;
}
public Flight showInformation(Long id) {
Flight flight = new Flight();
try {
String query = "SELECT Airports.Name AS Name FROM Flights INNER JOIN Airports ON Flights.DepartureAirport=Airports.Id WHERE Flights.Id=?";
PreparedStatement statement = connection.prepareStatement(query);
statement.setLong(1, id);
ResultSet resultSet = statement.executeQuery();
resultSet.next();
String departureAirportName = resultSet.getString("Name");
query = "SELECT Flights.departureAirport AS departureAirport, Flights.destinationAirport AS destinationAirport, Flights.Id, Flights.t, Flights.Gate, Airports.Name AS Name FROM Flights INNER JOIN Airports" +
" ON Airports.Id=Flights.destinationAirport WHERE Flights.Id=?";
statement = connection.prepareStatement(query);
statement.setLong(1, id);
resultSet = statement.executeQuery();
resultSet.next();
flight.setDepartureAirport(new Airport());
flight.getDepartureAirport().setId(resultSet.getLong("departureAirport"));
flight.getDepartureAirport().setName(departureAirportName);
flight.setId(resultSet.getLong("id"));
flight.setDestinationAirport(new Airport());
flight.getDestinationAirport().setId(resultSet.getLong("destinationAirport"));
flight.getDestinationAirport().setName(resultSet.getString("Name"));
flight.setGate(resultSet.getInt("Gate"));
flight.setT(resultSet.getTimestamp("t"));
} catch(SQLException e) {
logs.info("Ошибка выборки "+e);
}
return flight;
}
public List<Flight> flightList(int flightCount) {
List<Flight> flightList = new ArrayList<Flight>();
try {
//получим список аэропотов отбытия для всех рейсов
String query = "SELECT Flights.Id, Airports.Name, Airports.Id AS AID FROM Flights INNER JOIN Airports " +
"ON Flights.departureAirport=Airports.Id";
PreparedStatement statement = connection.prepareStatement(query);
ResultSet departureResultSet = statement.executeQuery();
//а тут уже список аэропортов прибытия и параметры каждого рейса
query = "SELECT Flights.Id, Flights.destinationAirport, Flights.Gate, Flights.t, Airports.Name, Airports.Id AS AID " +
"FROM Flights INNER JOIN Airports ON Flights.destinationAirport=Airports.Id";
PreparedStatement st = connection.prepareStatement(query);
ResultSet destinationResultSet = st.executeQuery();
//создаем список из [flightCount] аэропортов
int listIterator =0;
while ((departureResultSet.next())&&(destinationResultSet.next())&&(listIterator<flightCount)) {
flightList.add(new Flight());
flightList.get(listIterator).setId(destinationResultSet.getLong("Id"));
flightList.get(listIterator).setDepartureAirport(new Airport());
flightList.get(listIterator).getDepartureAirport().setId(departureResultSet.getLong("AID"));
flightList.get(listIterator).getDepartureAirport().setName(departureResultSet.getString("Name"));
flightList.get(listIterator).setDestinationAirport(new Airport());
flightList.get(listIterator).getDestinationAirport().setId(destinationResultSet.getLong("AID"));
flightList.get(listIterator).getDestinationAirport().setName(destinationResultSet.getString("Name"));
flightList.get(listIterator).setGate(destinationResultSet.getInt("Gate"));
flightList.get(listIterator).setT(destinationResultSet.getTimestamp("t"));
listIterator++;
}
} catch (SQLException e) {
logs.error("Ошибка в составлении списка аэропортов "+e);
}
return flightList;
}
public String getTableName() {
return "Flights";
}
}