基于javaweb的管理系统-航空管理系统源码+数据库（课程设计大作业）.zip

package edu.wtbu.dao; import edu.wtbu.utils.JDBCUtils; import java.util.ArrayList; import java.util.HashMap; import java.util.List; public class ScheduleDao { /** * 对start表联表查询 * * @param startDate 查询日期开始 * @param endDate 查询日期结束 * @param startPage 当前页面 * @param pageSize 每次分页抽取数量 * @return */ public static List<HashMap<String, Object>> findScheduleListByDate(String startDate, String endDate, Integer startPage, Integer pageSize) { List<HashMap<String, Object>> list = null; String sql = """ SELECT `schedule`.ScheduleId, `schedule`.FlightNumber, `schedule`.Gate, route.FlightTime, `schedule`.Time, `schedule`.Date, DepartureCity.CityName AS DepartCityName, DepartureAirport.IATACode AS DepartureAirportIATA, ArrivalCity.CityName AS ArriveCityName, ArrivalAirport.IATACode AS DepartureAirportIATA FROM `schedule` LEFT JOIN route ON route.RouteId = `schedule`.RouteId LEFT JOIN flightstatus ON flightstatus.ScheduleId = `schedule`.ScheduleId LEFT JOIN airport AS DepartureAirport ON route.ArrivalAirportIATA = DepartureAirport.IATACode LEFT JOIN airport AS ArrivalAirport ON route.ArrivalAirportIATA = ArrivalAirport.IATACode LEFT JOIN city AS DepartureCity ON DepartureCity.CityCode = DepartureAirport.CityCode LEFT JOIN city AS ArrivalCity ON ArrivalCity.CityCode = ArrivalAirport.CityCode\s WHERE `schedule`.Date BETWEEN ?\s AND ?\s LIMIT ?, ? """; list = JDBCUtils.executeQuery(sql, new Object[]{startDate, endDate, startPage, pageSize}); return list; } public static HashMap<String, Object> findScheduleListByDateTotal(String startDate, String endDate) { List<HashMap<String, Object>> list = null; String sql = """ SELECT COUNT(1) AS total FROM `schedule` LEFT JOIN route ON route.RouteId = `schedule`.RouteId LEFT JOIN flightstatus ON flightstatus.ScheduleId = `schedule`.ScheduleId LEFT JOIN airport AS DepartureAirport ON route.ArrivalAirportIATA = DepartureAirport.IATACode LEFT JOIN airport AS ArrivalAirport ON route.ArrivalAirportIATA = ArrivalAirport.IATACode LEFT JOIN city AS DepartureCity ON DepartureCity.CityCode = DepartureAirport.CityCode LEFT JOIN city AS ArrivalCity ON ArrivalCity.CityCode = ArrivalAirport.CityCode\s WHERE `schedule`.Date BETWEEN ?\s AND ?\s """; list = JDBCUtils.executeQuery(sql, new Object[]{startDate, endDate}); return list.get(0); } /** * 根据日期和城市查询机票列表 * * @param startDate 出发日期 * @param endDate 到达日期 * @param fromCity 出发城市 * @param toCity 到达城市 * @return List<HashMap < String, Object>> */ public static List<HashMap<String, Object>> findScheduleListByDateAndCity(String startDate, String endDate, String fromCity, String toCity) { List<HashMap<String, Object>> list = null; String sql = """ SELECT `schedule`.ScheduleId, `schedule`.Date, `schedule`.Time, DepartureCity.CityName AS DepartCityName, DepartureAirport.IATACode AS DepartureAirportIATA, ArrivalCity.CityName AS ArriveCityName, ArrivalAirport.IATACode AS DepartureAirportIATA, aircraft.`Name`, `schedule`.EconomyPrice, `schedule`.FlightNumber, `schedule`.Gate, `schedule`.`Status`\s FROM `schedule` LEFT JOIN aircraft ON `schedule`.AircraftId = aircraft.AircraftId\s LEFT JOIN route ON route.RouteId = `schedule`.RouteId LEFT JOIN airport AS DepartureAirport ON route.DepartureAirportIATA = DepartureAirport.IATACode LEFT JOIN airport AS ArrivalAirport ON route.ArrivalAirportIATA = ArrivalAirport.IATACode LEFT JOIN city AS DepartureCity ON DepartureCity.CityCode = DepartureAirport.CityCode LEFT JOIN city AS ArrivalCity ON ArrivalCity.CityCode = ArrivalAirport.CityCode WHERE DepartureCity.CityName = ?\s AND ArrivalCity.CityName = ?\s AND `schedule`.Date BETWEEN ?\s AND ? """; list = JDBCUtils.executeQuery(sql, new Object[]{fromCity, toCity, startDate, endDate}); return list; } public static HashMap<String, Object> findScheduleInfoById(Integer id) { String sql = """ SELECT `schedule`.ScheduleId, `schedule`.Date, `schedule`.Time, `schedule`.AircraftId, `schedule`.RouteId, `schedule`.EconomyPrice, `schedule`.FlightNumber, `schedule`.Gate, `schedule`.`Status`, route.DepartureAirportIATA, route.ArrivalAirportIATA, route.Distance, route.FlightTime, aircraft.`Name`, aircraft.FirstSeatsLayout, aircraft.FirstSeatsAmount, aircraft.BusinessSeatsLayout, aircraft.BusinessSeatsAmount, aircraft.EconomySeatsLayout, aircraft.EconomySeatsAmount\s FROM `schedule` LEFT JOIN route ON `schedule`.RouteId = route.RouteId LEFT JOIN aircraft ON `schedule`.AircraftId = aircraft.AircraftId\s WHERE `schedule`.ScheduleId = ? """; List<HashMap<String, Object>> list = JDBCUtils.executeQuery(sql, new Object[]{id}); return list.get(0); } public static List<HashMap<String, Object>> findTicketInfoListById(Integer id) { String sql = """ SELECT\s flightreservation.CabinTypeId, COUNT(1) AS SoldCounts, COUNT(SeatLayoutId) AS SelectedCounts FROM flightreservation WHERE flightreservation.ScheduleId = ? GROUP BY CabinTypeId """; List<HashMap<String, Object>> list = JDBCUtils.executeQuery(sql, new Object[]{id}); return list; } public static List<HashMap<String, Object>> findSelectedSeatListById(Integer id) { String sql = """ SELECT\s flightreservation.CabinTypeId, seatlayout.RowNumber, seatlayout.ColumnName FROM flightreservation LEFT JOIN seatlayout ON seatlayout.Id = flightreservation.SeatLayoutId WHERE flightreservation.ScheduleId = ? """; List<HashMap<String, Object>> list = JDBCUtils.executeQuery(sql, new Object[]{id}); return list; } public static List<HashMap<String, Object>> findSeatLayoutListByAircraftId(Integer aircraftId) { String sql = """ SELECT * FROM seatlayout WHERE seatlayout.AircraftId = ? """; List<HashMap<String, Object>> list = JDBCUtils.executeQuery(sql, new Object[]{aircraftId}); return list; } }