package dao.impl;
import dao.IAirportDAO;
import dao.IDAO;
import dao.tro.Airport;
import dao.tro.Country;
import org.apache.log4j.Logger;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class HSQLDBAirportDAO extends IDAO implements IAirportDAO{
private Connection connection=null;
private static final Logger Logs = Logger.getLogger(HSQLDBAirportDAO.class);
public HSQLDBAirportDAO() {
connection = HSQLDBDAOFactory.getConnection();
}
public void insert(Airport airport) {
try {
//подтягиваем ид страны по имени
String query = "SELECT Id FROM Countries WHERE Name=?";
PreparedStatement statement = connection.prepareStatement(query);
statement.setString(1, airport.getInCountry().getName());
ResultSet resultSet = statement.executeQuery();
resultSet.next();
Long countryId = resultSet.getLong("Id");
//вставляем аэропорт
query = "INSERT INTO Airports VALUES (?, ?, ?, ?)";
statement = connection.prepareStatement(query);
statement.setLong(1, airport.getId());
statement.setString(2, airport.getName());
statement.setLong(3, countryId);
statement.setInt(4, airport.getGatesNumber());
statement.executeUpdate(query);
} catch (SQLException e) {
Logs.info("Ошибка вставки " + e);
}
}
public void update(Airport airport) {
try {
//подтягиваем ид страны по имени
String query = "SELECT Id FROM Countries WHERE Name=?";
PreparedStatement statement = connection.prepareStatement(query);
statement.setString(1, airport.getInCountry().getName());
ResultSet resultSet = statement.executeQuery();
resultSet.next();
Long countryId = resultSet.getLong("Id");
//вставляем аэропорт
query = "UPDATE Airports SET Airports.Name = ?, InCountry = ?, GatesNumber = ? WHERE id = ?";
statement = connection.prepareStatement(query);
statement.setLong(4, airport.getId());
statement.setString(1, airport.getName());
statement.setLong(2, countryId);
statement.setInt(3, airport.getGatesNumber());
statement.executeUpdate(query);
} catch (SQLException e) {
Logs.info("Ошибка изменения " + e);
}
}
public void delete (long id) {
super.delete(id);
}
public Airport showInformation(Long id) {
Airport airport = new Airport();
try {
String query = "SELECT Countries.Name, Countries.Id FROM Airports INNER JOIN Countries ON Airports.InCountry=Countries.Id WHERE Airports.Id=?";
PreparedStatement statement = connection.prepareStatement(query);
statement.setLong(1, id);
ResultSet resultSet = statement.executeQuery();
resultSet.next();
Long countryId = resultSet.getLong("Id");
String countryName = resultSet.getString("Name");
query = "SELECT Airports.Id, Airports.Name, Airports.GatesNumber FROM Airports WHERE Id=?";
statement = connection.prepareStatement(query);
statement.setLong(1, id);
resultSet = statement.executeQuery();
while(resultSet.next()) {
airport.setId(id);
airport.setGatesNumber(resultSet.getInt("GatesNumber"));
Country country = new Country();
country.setId(countryId);
country.setName(countryName);
airport.setInCountry(country);
airport.setName(resultSet.getString("Name"));
}
} catch(SQLException e) {
Logs.info("Ошибка выборки " + e);
}
return airport;
}
public Airport loadCountryName(Airport airport) {
String query = "SELECT Countries.Name FROM Countries INNER JOIN Airports ON Airports.InCountry=Countries.id WHERE Airports.id=?";
try {
PreparedStatement statement = connection.prepareStatement(query);
statement.setLong(1, airport.getId());
ResultSet resultSet = statement.executeQuery();
resultSet.next();
Country country = new Country();
country.setName(resultSet.getString("Name"));
airport.setInCountry(country);
} catch (SQLException e) {
Logs.info("Ошибка подгрузки имени страны " + e);
}
return airport;
}
@Override
public List<Airport> allAirportList() {
List<Airport> airportList = new ArrayList<Airport>();
try {
String query = "SELECT Id, Name FROM Airports";
PreparedStatement statement = connection.prepareStatement(query);
ResultSet resultSet = statement.executeQuery();
int listIterator = 0;
while (resultSet.next()) {
airportList.add(new Airport());
airportList.get(listIterator).setName(resultSet.getString("Name"));
airportList.get(listIterator).setId(resultSet.getLong("Id"));
listIterator++;
}
} catch (SQLException e) {
Logs.info("Ошибка загрузки списка аэропортов");
}
return airportList;
}
@Override
public List<Airport> airportList(int airportCount) {
List<Airport> airportList = new ArrayList<>();
String query = "SELECT Airports.id, Airports.Name, Airports.GatesNumber, Countries.Name AS CName " +
"FROM Airports INNER JOIN Countries ON Airports.InCountry=Countries.Id";
try {
PreparedStatement statement = connection.prepareStatement(query);
ResultSet resultSet = statement.executeQuery();
int listIterator = 0;
while((resultSet.next())&&(listIterator<airportCount)) {
airportList.add(new Airport());
airportList.get(listIterator).setName(resultSet.getString("Name"));
airportList.get(listIterator).setId(resultSet.getLong("Id"));
airportList.get(listIterator).setGatesNumber(resultSet.getInt("GatesNumber"));
airportList.get(listIterator).setInCountry(new Country());
airportList.get(listIterator).getInCountry().setName(resultSet.getString("CName"));
listIterator++;
}
} catch (SQLException e) {
Logs.info("Ошибка загрузки списка аэропортов (полный список)");
}
return airportList;
}
public String getTableName() {
return "Airports";
}
}