/* create database */
drop database if exists AIRLINE_1;
drop database if exists AIRLINE_2;
drop database if exists AIRLINE_3;
drop database if exists ABS;
create database AIRLINE_1;
create database AIRLINE_2;
create database AIRLINE_3;
create database ABS;
/* create table of database Airline*/
use AIRLINE_1;
create table airline(
  code    varchar(3)  not null  primary key,
    name    varchar(20) not null,
    discount  float(2,2)  not null
);
create table airport(
  code    varchar(4)  not null  primary key,
    name    varchar(20) not null,
    city    varchar(20) not null,
    country   varchar(20) not null,
    connTime  int(3)    not null  /* minute */
);
create table airplane(
    name    varchar(20) not null  primary key,
    type    varchar(10) not null
);
create table seat(
  id      int(3)    not null  primary key auto_increment, 
    relativeID  int(3)    not null, /* 飞机上作为的相对编号 */
    row     int(3)    not null,
    num     int(3)    not null,
    type    varchar(12) not null, /* windowSeat middleSeat aisleSeat*/
    flightID  int(10)   not null, /* Table:airplane Item:id */
    passport  varchar(20)       /* passenger passport */
);
create table flight(
  id        int(10)   not null  primary key,
    airlineCode   varchar(3)  not null,
    number      int(6)    not null,
    depatureDate  Date    not null,
    depatureTime  time    not null,
    arrivalDate   Date    not null,
    arrivalTime   time    not null,
    fare      float(6,2)  not null,
    depatureAirport varchar(4)  not null, /* Table:airport Item:code */
    arrivalAirport  varchar(4)  not null, /* Table:airport Item:code */
  airplaneName  varchar(20) not null, /* Table:airplane Item:name */
    airplaneType  varchar(10) not null  /* Table:airplane Item:type */
);
use AIRLINE_2;
create table airline(
  code    varchar(3)  not null  primary key,
    name    varchar(20) not null,
    discount  float(2,2)  not null
);
create table airport(
  code    varchar(4)  not null  primary key,
    name    varchar(20) not null,
    city    varchar(20) not null,
    country   varchar(20) not null,
    connTime  int(3)    not null  /* minute */
);
create table airplane(
    name    varchar(20) not null  primary key,
    type    varchar(10) not null
);
create table seat(
  id      int(3)    not null  primary key auto_increment, 
    relativeID  int(3)    not null, /* 飞机上作为的相对编号 */
    row     int(3)    not null,
    num     int(3)    not null,
    type    varchar(12) not null, /* windowSeat middleSeat aisleSeat*/
    flightID  int(10)   not null, /* Table:airplane Item:id */
    passport  varchar(20)       /* passenger passport */
);
create table flight(
  id        int(10)   not null  primary key,
    airlineCode   varchar(3)  not null,
    number      int(6)    not null,
    depatureDate  Date    not null,
    depatureTime  time    not null,
    arrivalDate   Date    not null,
    arrivalTime   time    not null,
    fare      float(6,2)  not null,
    depatureAirport varchar(4)  not null, /* Table:airport Item:code */
    arrivalAirport  varchar(4)  not null, /* Table:airport Item:code */
  airplaneName  varchar(20) not null, /* Table:airplane Item:name */
    airplaneType  varchar(10) not null  /* Table:airplane Item:type */
);
use AIRLINE_3;
create table airline(
  code    varchar(3)  not null  primary key,
    name    varchar(20) not null,
    discount  float(2,2)  not null
);
create table airport(
  code    varchar(4)  not null  primary key,
    name    varchar(20) not null,
    city    varchar(20) not null,
    country   varchar(20) not null,
    connTime  int(3)    not null  /* minute */
);
create table airplane(
    name    varchar(20) not null  primary key,
    type    varchar(10) not null
);
create table seat(
  id      int(3)    not null  primary key auto_increment, 
    relativeID  int(3)    not null, /* 飞机上作为的相对编号 */
    row     int(3)    not null,
    num     int(3)    not null,
    type    varchar(12) not null, /* windowSeat middleSeat aisleSeat*/
    flightID  int(10)   not null, /* Table:airplane Item:id */
    passport  varchar(20)       /* passenger passport */
);
create table flight(
  id        int(10)   not null  primary key,
    airlineCode   varchar(3)  not null,
    number      int(6)    not null,
    depatureDate  Date    not null,
    depatureTime  time    not null,
    arrivalDate   Date    not null,
    arrivalTime   time    not null,
    fare      float(6,2)  not null,
    depatureAirport varchar(4)  not null, /* Table:airport Item:code */
    arrivalAirport  varchar(4)  not null, /* Table:airport Item:code */
  airplaneName  varchar(20) not null, /* Table:airplane Item:name */
    airplaneType  varchar(10) not null  /* Table:airplane Item:type */
);
/* create table of database ABS*/
use ABS;
create table airline(
  code    varchar(3)  not null  primary key,
    name    varchar(20) not null,
    discount  float(2,2)  not null
);
create table flightInfo(
  id        int(10)   not null  primary key  auto_increment,
  airlineCode   varchar(3)  not null, /* Table:airline Item:code */
    flightID    int(10)   not null, /* Database:Airline Table:flight Item: */
    number      int(6)    not null, /* Database:Airline Table:flight Item:number */
    depatureDate  Date    not null, /* Database:Airline Table:flight Item:depatureDate */
    depatureTime  time    not null, /* Database:Airline Table:flight Item:depatureTime */
    arrivalDate   Date    not null, /* Database:Airline Table:flight Item:arrivalDate */
    arrivalTime   time    not null, /* Database:Airline Table:flight Item:arrivalTime */
    fare      float(6,2)  not null, /* Database:Airline Table:flight Item:fare */
    depatureAirport varchar(4)  not null, /* Database:Airline Table:flight Item:depatureAirport */
    arrivalAirport  varchar(4)  not null, /* Database:Airline Table:flight Item:arrivalAirport */
  airlineName   varchar(20) not null, /* Table:airline Item:name */
    airplaneName  varchar(20) not null, /* Database:Airline Table:airplane Item:name */
  airplaneType  varchar(10) not null, /* 大：240 中：160 小：80 */
  depatureAirportName   varchar(20) not null,
    arrivalAirportName    varchar(20) not null,
    depatureAirportCity   varchar(20) not null,
    arrivalAirportCity    varchar(20) not null,
    airplaneEmptySeats  int(3)  not null  /* Database:Airline Table:airplane Item:conunt of empty seats */
);
create table airport(
  code    varchar(4)  not null  primary key,
    name    varchar(20) not null,
    city    varchar(20) not null,
    country   varchar(20) not null,
    connTime  int(3)    not null  /* minute */
);
create table passenger(
  passport  varchar(20) not null, /* no primary key for test */
    name    varchar(20) not null  
);
create table trip(
  id        int(10)   not null  primary key,
    flightInfoID  int(10)   not null, /* Database:Airline Table:flight Item: */
    fare      float(6,2)  not null, /* fare不同于 flightInfo中的 fare */
    passport    varchar(20) not null, /* Table:passenger Item:passport */
    seatID      int(3)    not null  /* Database:Airline Table:seat Item:relativeID */
);
create table orders(
  id        int(10)   not null, /* id 相同的为同一个订单 */
    tripID      int(10)   not null, /* Table:tirp Item:id */
    createDate    date    not null,
    createTime    time    not null,
    totalFare   float(6,2)  not null,
    contactName   varchar(20) not null,
    contactPhone  varchar(20) not null
);

package com.abs.action;
import java.sql.Date;
import java.sql.Time;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.*;
import com.abs.db.DBName;
import com.abs.factory.DaoFactory;
import com.abs.model.*;
public class SearchAction {
  public static List<FlightInfo> searchFlightDirectly(String departureCity, String arrivalCity, String departureDateString) throws Exception {
    List<FlightInfo> list = null;
    Date departureDate = null;
    if (checkDate(departureDateString)) { //  测试Date格式是否合格
      departureDate = Date.valueOf(departureDateString);
    }
    list = DaoFactory.getFlightInfoDaoInstance(DBName.ABS).findByAirport(departureCity, arrivalCity, departureDate);
    return list;
  }
  public static List<List<FlightInfo>> searchFlightTransfer(String departureCity, String arrivalCity, String departureDateString) throws Exception {
    List<List<FlightInfo>> list = new ArrayList<List<FlightInfo>>();
    Date departureDate = null;
    if (checkDate(departureDateString)) { //  测试Date格式是否合格
      departureDate = Date.valueOf(departureDateString);
    }
    List<FlightInfo> departureList = DaoFactory.getFlightInfoDaoInstance(DBName.ABS).findByDepatureAirport(departureCity, departureDate);
    for (FlightInfo depatureFlight : departureList) {
      Airport transferAirport = DaoFactory.getAirportDaoInstance(DBName.ABS).findByCode(depatureFlight.getArrivalAirport());
      if(null != transferAirport){
        int connTime = transferAirport.getConnTime(); 
        Time depatureTime = new Time(depatureFlight.getArrivalTime().getTime() + connTime * 60 * 1000);
        String transferCity = depatureFlight.getArrivalAirportCity();
        List<FlightInfo> arrivalList = DaoFactory.getFlightInfoDaoInstance(DBName.ABS).findByTransferArrivalAirport(transferCity, arrivalCity, departureDate, depatureTime);
        for (FlightInfo arrivalFlight : arrivalList) {
          List<FlightInfo> item = new ArrayList<FlightInfo>();
          if(depatureFlight.getAirlineCode().equals(arrivalFlight.getAirlineCode())){
            double discount = DaoFactory.getAirlineDaoInstance(DBName.ABS).findByCode(depatureFlight.getAirlineCode()).getDiscount();
            arrivalFlight.setFare(arrivalFlight.getFare() * discount); 
          }
          item.add(depatureFlight);
          item.add(arrivalFlight);
          list.add(item);
        }
      }else {
        System.err.println(depatureFlight.getArrivalAirportCity() + depatureFlight.getArrivalAirport() + ":没有找到机场信息");
      }
    }
    return list;
  }
  public static boolean checkDate(String date) {
    String regex= "^((\\d{2}(([02468][048])|([13579][26]))[\\-\\/\\s]?((((0?[13578])|(1[02]))[\\-\\/\\s]?((0?[1-9])|([1-2][0-9])|(3[01])))|(((0?[469])|(11))[\\-\\/\\s]?((0?[1-9])|([1-2][0-9])|(30)))|(0?2[\\-\\/\\s]?((0?[1-9])|([1-2][0-9])))))|(\\d{2}(([02468][1235679])|([13579][01345789]))[\\-\\/\\s]?((((0?[13578])|(1[02]))[\\-\\/\\s]?((0?[1-9])|([1-2][0-9])|(3[01])))|(((0?[469])|(11))[\\-\\/\\s]?((0?[1-9])|([1-2][0-9])|(30)))|(0?2[\\-\\/\\s]?((0?[1-9])|(1[0-9])|(2[0-8]))))))";   
        return Pattern.compile(regex).matcher(date).matches(); 
  }
}

package com.abs.action;
import java.sql.Date;
import java.sql.Time;
import java.util.List;
import com.abs.db.DBName;
import com.abs.factory.DaoFactory;
import com.abs.model.Flight;
import com.abs.model.FlightInfo;
import com.abs.model.Orders;
import com.abs.model.Passenger;
import com.abs.model.Seat;
import com.abs.model.Trip;
public class OrderAction {
  public static Orders createOrder(List<FlightInfo> flightInfos, List<Passenger> passengers, String[] seatTypeList, String contactName, String contactPhone) throws Exception {
    //  生成订单编号
    Orders.addIdCounter();
    Orders order = null;
    double total = 0;
    for (FlightInfo flightInfo : flightInfos) {
      total += flightInfo.getFare();
    }
    total = total * passengers.size();    //  计算总费用
    for (Passenger passenger : passengers) {
      DaoFactory.getPassengerDaoInstance(DBName.ABS).add(passenger);
    }
    for (FlightInfo flightInfo : flightInfos) {
      for (Passenger passenger : passengers) {
        Trip trip = new Trip();
        trip.setId(Trip.getIdCounter());
        trip.setFlightInfoID(flightInfo.getId());
        trip.setFare(flightInfo.getFare());
        trip.setPassport(passenger.getPassport());
        trip.setSeatID(OrderAction.orderSeat(flightInfo, passenger, seatTypeList[passengers.indexOf(passenger)]));  //  分配座位
        flightInfo.setAirplaneEmptySeats(flightInfo.getAirplaneEmptySeats() - 1);     //  空闲座位数 - 1
        DaoFactory.getFlightInfoDaoInstance(DBName.ABS).addPassenger(flightInfo.getId()); //  空闲座位数 - 1
        DaoFactory.getTripDaoInstance(DBName.ABS).add(trip);
        Orders orders = new Orders();
        orders.setId(Orders.getIdCounter());  //  同一个订单号，代表为同一个订单
        orders.setTripID(trip.getId());     //  每个Orders item 与一个 Trip 一一对应
        java.util.Date date = new java.util.Date();
        orders.setCreateDate(new Date(date.getTime()));
        orders.setCreateTime(new Time(date.getTime()));
        orders.setTotalFare(total);
        orders.setContactName(contactName);
        orders.setContactPhone(contactPhone);
        DaoFactory.getOrdersDaoInstance(DBName.ABS).add(orders);
        order = orders;   //  返回一个 Orders item用于显示
      }
    }
    return order;
  }
  public static int orderSeat(FlightInfo flightInfo, Passenger passenger, String seatType) throws Exception {
    int seatID = -1;
    String airlineCode = flightInfo.getAirlineCode();
    Flight flight = null; 
    //  airline_1  MU(东方航空), airline_2  CZ(南方航空), airline_3  CA(中国国航) 
    if(airlineCode.equals("MU")){     //  airline_1  MU(东方航空)
      flight = DaoFactory.getFlightDaoInstance(DBName.AIRLINE_1).findByID(flightInfo.getFlightID()); 
      List<Seat> emptySeats = DaoFactory.getSeatDaoInstance(DBName.AIRLINE_1).findEmptySeatByFlightID(flight.getId());
      if (emptySeats.size() > 0) {
        Seat seat = null;
        for (Seat emptySeat : emptySeats) {
          if(emptySeat.getType() == seatType){
            seat = emptySeat;
            break;
          }
        }
        if(seat == null){
          seat = emptySeats.get(0);
        }
        seat.setPassport(passenger.getPassport());
        DaoFactory.getSeatDaoInstance(DBName.AIRLINE_1).modify(seat);
        seatID = seat.getRelativeID();
      }
    }else if (airlineCode.equals("CZ")) { //  airline_2  CZ(南方航空)
      flight = DaoFactory.getFlightDaoInstance(DBName.AIRLINE_2).findByID(flightInfo.getFlightID()); 
      List<Seat> emptySeats = DaoFactory.getSeatDaoInstance(DBName.AIRLINE_2).findEmptySeatByFlightID(flight.getId());
      if (emptySeats.size() > 0) {
        Seat seat = null;
        for (Seat emptySeat : emptySeats) {
          if(emptySeat.getType() == seatType){
            seat = emptySeat;
          }
        }
        if(seat == null){
          seat = emptySeats.get(0);
        }
        seat.setPassport(passenger.getPassport());
        DaoFactory.getSeatDaoInstance(DBName.AIRLINE_2).modify(seat);
        seatID = seat.getRelativeID();
      }
    }else {               //  airline_3  CA(中国国航) 
      flight = DaoFactory.getFlightDaoInstance(DBName.AIRLINE_3).findByID(flightInfo.getFlightID()); 
      List<Seat> emptySeats = DaoFactory.getSeatDaoInstance(DBName.AIRLINE_3).findEmptySeatByFlightID(flight.getId());
      if (emptySeats.size() > 0) {
        Seat seat = null;
        for (Seat emptySeat : emptySeats) {
          if(emptySeat.getType() == seatType){
            seat = emptySeat;
          }
        }
        if(seat == null){
          seat = emptySeats.get(0);
        }
        seat.setPassport(passenger.getPassport());
        DaoFactory.getSeatDaoInstance(DBName.AIRLINE_3).modify(seat);
        seatID = seat.getRelativeID();
      }
    }
    return seatID;
  }
}

package com.abs.dao.impl;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import com.abs.dao.AirlineDao;
import com.abs.db.DBConnection;
import com.abs.db.DBName;
import com.abs.model.Airline;
public class AirlineDaoImpl implements AirlineDao {
  private Connection conn = null;
  private PreparedStatement pstmt = null;
  public AirlineDaoImpl(Connection conn) {
    // TODO Auto-generated constructor stub
    this.conn = conn;
  }
  @Override
  public boolean add(Airline airline) throws Exception {
    // TODO Auto-generated method stub
    String sql = "insert into airline(code, name, discount) values(?,?,?)";
    this.pstmt = this.conn.prepareStatement(sql);
    this.pstmt.setString(1, airline.getCode());
    this.pstmt.setString(2, airline.getName());
    this.pstmt.setDouble(3, airline.getDiscount());
    int update = this.pstmt.executeUpdate();
    this.pstmt.close();
    if(update > 0){
      return true;
    }else{
      return false;
    }
  }
  @Override
  public Airline findByCode(String code) throws Exception {
    // TODO Auto-generated method stub
    String sql = "select * from airline where code=?";
    this.pstmt = this.conn.prepareStatement(sql);
    this.pstmt.setString(1, code);
    ResultSet resultSet = this.pstmt.executeQuery();
    Airline airline = null;
    if(resultSet.next()){
      airline = new Airline();
      airline.setCode(resultSet.getString(1));
      airline.setName(resultSet.getString(2));
      airline.setDiscount(resultSet.getDouble(3));
    }
    this.pstmt.close();
    return airline;
  }
  @Override
  public List<Airline> findAll() throws Exception {
    // TODO Auto-generated method stub
    String sql = "select * from airline";
    this.pstmt = this.conn.prepareStatement(sql);
    ResultSet resultSet = this.pstmt.executeQuery();
    List<Airline> list = new ArrayList<>();
    Airline airline = null;
    while(resultSet.next()){
      airline = new Airline();
      airline.setCode(resultSet.getString(1));
      airline.setName(resultSet.getString(2));
      airline.setDiscount(resultSet.getDouble(3));
      list.add(airline);
    }
    this.pstmt.close();
    return list;
  }
  public static void main(String args[]) throws Exception {
    Airline airline = new Airline();
    airline.setCode("MU");
    airline.setName("东方航空");
    airline.setDiscount(0.9);
    new AirlineDaoImpl(new DBConnection().getConnection(DBName.AIRLINE_1)).add(airline);
  }
}

package com.abs.dao.impl;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.sql.*;
import java.util.List;
import com.abs.dao.FlightDao;
import com.abs.db.DBName;
import com.abs.factory.DaoFactory;
import com.abs.model.Flight;
public class FlightDaoImpl implements FlightDao {
  private Connection conn = null;
  private PreparedStatement pstmt = null;
  public FlightDaoImpl(Connection conn) {
    // TODO Auto-generated constructor stub
    this.conn = conn;
  }
  @Override
  public boolean add(Flight flight) throws Exception {
    // TODO Auto-generated method stub
    String sql = "insert into flight(id, airlineCode, number, depatureDate, depatureTime, arrivalDate, arrivalTime, fare, "
        + "depatureAirport, arrivalAirport, airplaneName, airplaneType) values(?,?,?,?,?,?,?,?,?,?,?,?)";
    this.pstmt = this.conn.prepareStatement(sql);
    this.pstmt.setInt(1, flight.getId());
    this.pstmt.setString(2, flight.getAirlineCode());
    this.pstmt.setInt(3, flight.getNumber());
    this.pstmt.setDate(4, flight.getDepatureDate());
    this.pstmt.setTime(5, flight.getDepatureTime());
    this.pstmt.setDate(6, flight.getArrivalDate());
    this.pstmt.setTime(7, flight.getArrivalTime());
    this.pstmt.setDouble(8, flight.getFare());
    this.pstmt.setString(9, flight.getDepatureAirport());
    this.pstmt.setString(10, flight.getArrivalAirport());
    this.pstmt.setString(11, flight.getAirplaneName());
    this.pstmt.setString(12, flight.getAirplaneType());
    int update = this.pstmt.executeUpdate();
    this.pstmt.close();
    if(update > 0){
      return true;
    }else {
      return false;
    }
  }
  @Override
  public Flight findByID(int id) throws Exception {
    // TODO Auto-generated method stub
    String sql = "select * from flight where id=?";
    this.pstmt = this.conn.prepareStatement(sql);
    this.pstmt.setInt(1, id);
    ResultSet resultSet = this.pstmt.executeQuery();
    Flight flight = null;
    if(resultSet.next()){
      flight = new Flight();
      flight.setId(resultSet.getInt(1));
      flight.setAirlineCode(resultSet.getString(2));
      flight.setNumber(resultSet.getInt(3));
      flight.setDepatureDate(resultSet.getDate(4));
      flight.setDepatureTime(resultSet.getTime(5));
      flight.setArrivalDate(resultSet.getDate(6));
      flight.setArrivalTime(resultSet.getTime(7));
      flight.setFare(resultSet.getDouble(8));
      flight.setDepatureAirport(resultSet.getString(9));
      flight.setArrivalAirport(resultSet.getString(10));
      flight.setAirplaneName(resultSet.getString(11));
      flight.setAirplaneType(resultSet.getString(12));
    }
    this.pstmt.close();
    return flight;
  }
  @Override
  public List<Flight> findAll() throws Exception {
    // TODO Auto-generated method stub
    String sql = "select * from flight";
    this.pstmt = this.conn.prepareStatement(sql);
    ResultSet resultSet = this.pstmt.executeQuery();
    List<Flight> list = new ArrayList<>();
    Flight flight = null;
    while(resultSet.next()){
      flight = new Flight();
      flight.setId(resultSet.getInt(1));
      flight.setAirlineCode(resultSet.getString(2));
      flight.setNumber(resultSet.getInt(3));
      flight.setDepatureDate(resultSet.getDate(4));
      flight.setDepatureTime(resultSet.getTime(5));
      flight.setArrivalDate(resultSet.getDate(6));
      flight.setArrivalTime(resultSet.getTime(7));
      flight.setFare(resultSet.getDouble(8));
      flight.setDepatureAirport(resultSet.getString(9));
      flight.setArrivalAirport(resultSet.getString(10));
      flight.setAirplaneName(resultSet.getString(11));
      flight.setAirplaneType(resultSet.getString(12));
    }
    this.pstmt.close();
    return list;
  }
  public static void main(String agrs[]) throws Exception {
    Flight flight = new Flight();
    flight.setNumber(123);
    flight.setDepatureDate(new Date(new java.util.Date().getTime()));
    flight.setDepatureTime(new Time(new java.util.Date().getTime()));
    flight.setArrivalDate(new Date(new java.util.Date().getTime()));
    flight.setArrivalTime(new Time(new java.util.Date().getTime()));
    flight.setFare(599.0);
    flight.setDepatureAirport("ecb");
    flight.setArrivalAirport("abc");
    flight.setAirplaneName("空客A320");
    flight.setAirplaneType("中");
    DaoFactory.getFlightDaoInstance(DBName.AIRLINE_1).add(flight);
  }
}

package com.abs.dao.impl;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Time;
import java.util.ArrayList;
import java.util.List;
import com.abs.dao.FlightInfoDao;
import com.abs.db.DBName;
import com.abs.factory.DaoFactory;
import com.abs.model.FlightInfo;
public class FlightInfoDaoImpl implements FlightInfoDao {
  private Connection conn = null;
  private PreparedStatement pstmt = null;
  public FlightInfoDaoImpl(Connection conn) {
    // TODO Auto-generated constructor stub
    this.conn = conn;
  }
  @Override
  public boolean add(FlightInfo flightInfo) throws Exception {
    // TODO Auto-generated method stub
    String sql = "insert into flightInfo(airlineCode, flightID, number, "
        + "depatureDate, depatureTime, arrivalDate, arrivalTime,"
        + "fare, depatureAirport, arrivalAirport, airlineName, airplaneName,"
        + "airplaneType, depatureAirportName, arrivalAirportName, depatureAirportCity, arrivalAirportCity, airplaneEmptySeats) "
        + "values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
    this.pstmt = this.conn.prepareStatement(sql);
    this.pstmt.setString(1, flightInfo.getAirlineCode());
    this.pstmt.setInt(2, flightInfo.getFlightID());
    this.pstmt.setInt(3, flightInfo.getNumber());
    this.pstmt.setDate(4, flightInfo.getDepatureDate());
    this.pstmt.setTime(5, flightInfo.getDepatureTime());
    this.pstmt.setDate(6, flightInfo.getArrivalDate());
    this.pstmt.setTime(7, flightInfo.getArrivalTime());
    this.pstmt.setDouble(8, flightInfo.getFare());
    this.pstmt.setString(9, flightInfo.getDepatureAirport());
    this.pstmt.setString(10, flightInfo.getArrivalAirport());
    this.pstmt.setString(11, flightInfo.getAirlineName());
    this.pstmt.setString(12, flightInfo.getAirplaneName());
    this.pstmt.setString(13, flightInfo.getAirplaneType());
    this.pstmt.setString(14, flightInfo.getDepatureAirportName());
    this.pstmt.setString(15, flightInfo.getArrivalAirportName());
    this.pstmt.setString(16, flightInfo.getDepatureAirportCity());
    this.pstmt.setString(17, flightInfo.getArrivalAirportCity());
    this.pstmt.setInt(18, flightInfo.getAirplaneEmptySeats());
    int update = this.pstmt.executeUpdate();
    this.pstmt.close();
    if(update > 0){
      return true;
    }else {
      return false;
    }
  }
  @Override
  public FlightInfo findByID(int id) throws Exception {
    // TODO Auto-generated method stub
    String sql = "select * from flightInfo where id=?";
    this.pstmt = this.conn.prepareStatement(sql);
    this.pstmt.setInt(1, id);
    ResultSet resultSet = this.pstmt.executeQuery();
    FlightInfo flightInfo = null;
    if(resultSet.next()){
      flightInfo = new FlightInfo();
      flightInfo.setId(resultSet.getInt(1));
      flightInfo.setAirlineCode(resultSet.getString(2));
      flightInfo.setFlightID(resultSet.getInt(3));
      flightInfo.setNumber(resultSet.getInt(4));
      flightInfo.setDepatureDate(resultSet.getDate(5));
      flightInfo.setDepatureTime(resultSet.getTime(6));
      flightInfo.setArrivalDate(resultSet.getDate(7));
      flightInfo.setArrivalTime(resultSet.getTime(8));
      flightInfo.setFare(resultSet.getDouble(9));
      flightInfo.setDepatureAirport(resultSet.getString(10));
      flightInfo.setArrivalAirport(resultSet.getString(11));
      flightInfo.setAirlineName(resultSet.getString(12));
      flightInfo.setAirplaneName(resultSet.getString(13));
      flightInfo.setAirplaneType(resultSet.getString(14));
      flightInfo.setDepatureAirportName(resultSet.getString(15));
      flightInfo.setArrivalAirportName(resultSet.getString(16));
      flightInfo.setDepatureAirportCity(resultSet.getString(17));
      flightInfo.setArrivalAirportCity(resultSet.getString(18));
      flightInfo.setAirplaneEmptySeats(resultSet.getInt(19));
    }
    this.pstmt.close();
    return flightInfo;
  }
  @Override
  public List<FlightInfo> findByAirport(String depatureAirportCity, String arrivalAirportCity, Date depatureDate) throws Exception {
    // TODO Auto-generated method stub
    String sql = "select * from flightInfo where depatureAirportCity=? and arrivalAirportCity=? and depatureDate=?";
    this.pstmt = this.conn.prepareStatement(sql);
    this.pstmt.setString(1, depatureAirportCity);
    this.pstmt.setString(2, arrivalAirportCity);
    this.pstmt.setDate(3, depatureDate);
    ResultSet resultSet = this.pstmt.executeQuery();
    List<FlightInfo> list = new ArrayList<>();
    FlightInfo flightInfo = null;
    while(resultSet.next()){
      flightInfo = new FlightInfo();
      flightInfo.setId(resultSet.getInt(1));
      flightInfo.setAirlineCode(resultSet.getString(2));
      flightInfo.setFlightID(resultSet.getInt(3));
      flightInfo.setNumber(resultSet.getInt(4));
      flightInfo.setDepatureDate(resultSet.getDate(5));
      flightInfo.setDepatureTime(resultSet.getTime(6));
      flightInfo.setArrivalDate(resultSet.getDate(7));
      flightInfo.setArrivalTime(resultSet.getTime(8));
      flightInfo.setFare(resultSet.getDouble(9));
      flightInfo.setDepatureAirport(resultSet.getString(10));
      flightInfo.setArrivalAirport(resultSet.getString(11));
      flightInfo.setAirlineName(resultSet.getString(12));
      flightInfo.setAirplaneName(resultSet.getString(13));
      flightInfo.setAirplaneType(resultSet.getString(14));
      flightInfo.setDepatureAirportName(resultSet.getString(15));
      flightInfo.setArrivalAirportName(resultSet.getString(16));
      flightInfo.setDepatureAirportCity(resultSet.getString(17));
      flightInfo.setArrivalAirportCity(resultSet.getString(18));
      flightInfo.setAirplaneEmptySeats(resultSet.getInt(19));
      list.add(flightInfo);
    }
    this.pstmt.close();
    return list;
  }
  @Override
  public List<FlightInfo> findByDepatureAirport(String depatureAirportCity, Date depatureDate) throws Exception {
    // TODO Auto-generated method stub
    String sql = "select * from flightInfo where depatureAirportCity=? and depatureDate=?";
    this.pstmt = this.conn.prepareStatement(sql);
    this.pstmt.setString(1, depatureAirportCity);
    this.pstmt.setDate(2, depatureDate);
    ResultSet resultSet = this.pstmt.executeQuery();
    List<FlightInfo> list = new ArrayList<>();
    FlightInfo flightInfo = null;
    while(resultSet.next()){
      flightInfo = new FlightInfo();
      flightInfo.setId(resultSet.getInt(1));
      flightInfo.setAirlineCode(resultSet.getString(2));
      flightInfo.setFlightID(resultSet.getInt(3));
      flightInfo.setNumber(resultSet.getInt(4));
      flightInfo.setDepatureDate(resultSet.getDate(5));
      flightInfo.setDepatureTime(resultSet.getTime(6));
      flightInfo.setArrivalDate(resultSet.getDate(7));
      flightInfo.setArrivalTime(resultSet.getTime(8));
      flightInfo.setFare(resultSet.getDouble(9));
      flightInfo.setDepatureAirport(resultSet.getString(10));
      flightInfo.setArrivalAirport(resultSet.getString(11));
      flightInfo.setAirlineName(resultSet.getString(12));
      flightInfo.setAirplaneName(resultSet.getString(13));
      flightInfo.setAirplaneType(resultSet.getString(14));
      flightInfo.setDepatureAirportName(resultSet.getString(15));
      flightInfo.setArrivalAirportName(resultSet.getString(16));
      flightInfo.setDepatureAirportCity(resultSet.getString(17));
      flightInfo.setArrivalAirportCity(resultSet.getString(18));
      flightInfo.setAirplaneEmptySeats(resultSet.getInt(19));
      list.add(flightInfo);
    }
    this.pstmt.close();
    return list;
  }
  @Override
  public List<FlightInfo> findByTransferArrivalAirport(String depatureAirportCity, String arrivalAirportCity, Date depatureDate, Time depatureTime) throws Exception {
    // TODO Auto-generated method stub
    String sql = "select * from flightInfo where depatureAirportCity=? and arrivalAirportCity=? and depatureDate>=? and depatureTime>=?";
    this.pstmt = this.conn.prepareStatement(sql);
    this.pstmt.setString(1, depatureAirportCity);
    this.pstmt.setString(2, arrivalAirportCity);
    this.pstmt.setDate(3, depatureDate);
    this.pstmt.setTime(4, depatureTime);
    ResultSet resultSet = this.pstmt.executeQuery();
    List<FlightInfo> list = new ArrayList<>();
    FlightInfo flightInfo = null;
    while(resultSet.next()){
      flightInfo = new FlightInfo();
      flightInfo.setId(resultSet.getInt(1));
      flightInfo.setAirlineCode(resultSet.getString(2));
      flightInfo.setFlightID(resultSet.getInt(3));
      flightInfo.setNumber(resultSet.getInt(4));
      flightInfo.setDepatureDate(resultSet.getDate(5));
      flightInfo.setDepatureTime(resultSet.getTime(6));
      flightInfo.setArrivalDate(resultSet.getDate(7));
      flightInfo.setArrivalTime(resultSet.getTime(8));
      flightInfo.setFare(resultSet.getDouble(9));
      flightInfo.setDepatureAirport(resultSet.getString(10));
      flightInfo.setArrivalAirport(resultSet.getString(11));
      flightInfo.setAirlineName(resultSet.getString(12));
      flightInfo.setAirplaneName(resultSet.getString(13));
      flightInfo.setAirplaneType(resultSet.getString(14));
      flightInfo.setDepatureAirportName(resultSet.getString(15));
      flightInfo.setArrivalAirportName(resultSet.getString(16));
      flightInfo.setDepatureAirportCity(resultSet.getString(17));
      flightInfo.setArrivalAirportCity(resultSet.getString(18));
      flightInfo.setAirplaneEmptySeats(resultSet.getInt(19));
      list.add(flightInfo);
    }
    this.pstmt.close();
    return list;
  }
  @Override
  public int addPassenger(int id) throws Exception {
    // TODO Auto-generated method stub
    String sql = "select airplaneEmptySeats from flightInfo where id=?";
    this.pstmt = this.conn.prepareStatement(sql);
    this.pstmt.setInt(1, id);
    ResultSet resultSet = this.pstmt.executeQuery();
    int emptySeats = 0;
    if(resultSet.next()){
      emptySeats = resultSet.getInt(1);
    }
    emptySeats--;
    sql = "update flightInfo set airplaneEmptySeats=? where id=?";
    this.pstmt = this.conn.prepareStatement(sql);
    this.pstmt.setInt(1, emptySeats);
    this.pstmt.setInt(2, id);
    this.pstmt.executeUpdate();
    this.pstmt.close();
    return emptySeats;
  }
}