AI 助理
备案控制台
开发者社区 数据库 文章 正文

Java实现Web航空订票系统（servlet+jdbc+jsp+mysql）（上）

2022-05-26 638
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云数据库 RDS MySQL，集群系列 2核4GB
推荐场景：
搭建个人博客
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
云数据库 RDS MySQL，高可用系列 2核4GB
简介： Java实现Web航空订票系统（servlet+jdbc+jsp+mysql）

一、系统介绍

1.软件环境


Java：jdk1.8

Mysql：8.0.11

Tomcat：8.0.28


2.系统功能


机票查询

1.航行类型

2.出发城市

3.到达城市

4.出发日期

5.返回日期

选择航班

1.航班信息

2.起飞时间

3.到达时间

4.机票价格

填写乘客信息

1.姓名

2.护照

3.座位偏好

4.添加乘客

5.联系人

6.订单信息确认

7.提交订单

订单详情页

1.订单详细信息

2.航班详细信息

3.乘机人详细信息

4.联系人详细信息


3.数据库

/* 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
);


二、系统展示

1.机票查询


20210623235554581.png

2.选择航班


20210623235650895.png

3.填写乘客和联系人信息、提交订单


20210623235846778.png

4.订单详细信息


20210623235929256.png


三、部分代码

SearchAction

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(); 
  }
}

OrderAction

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;
  }
}

AirlineDaoImpl

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);
  }
}

FlightDaoImpl

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);
  }
}

FlightInfoDaoImpl

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;
  }
}
文章标签：
云数据库 RDS MySQL 版
Java
应用服务中间件
关系型数据库
MySQL
数据库
关键词：
Java Web
Java jsp
Java MYSQL
云数据库 RDS MySQL 版jdbc
web servlet
相关实践学习
如何在云端创建MySQL数据库
开始实验后，系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
水坚石青
目录
相关文章
东方睿赢
|
3月前
|
缓存 安全 Java
Java服务器端技术：Servlet与JSP的集成与扩展
Java服务器端技术：Servlet与JSP的集成与扩展
东方睿赢
32 3
东方睿赢
|
3月前
|
存储 缓存 前端开发
Servlet与JSP在Java Web应用中的性能调优策略
Servlet与JSP在Java Web应用中的性能调优策略
东方睿赢
32 1
VipSoft
|
3月前
Caused by: java.lang.ClassNotFoundException: javax.servlet.Filter
Caused by: java.lang.ClassNotFoundException: javax.servlet.Filter
VipSoft
58 3
龙大吉
|
3月前
|
存储 Java 关系型数据库
基于Servlet和JSP的Java Web应用开发指南
基于Servlet和JSP的Java Web应用开发指南
龙大吉
41 0
东方睿赢
|
3月前
|
前端开发 安全 Java
在Java服务器端开发的浩瀚宇宙中，Servlet与JSP犹如两颗璀璨的明星，它们联袂登场，共同编织出动态网站的绚丽篇章。
在Java服务器端开发的浩瀚宇宙中，Servlet与JSP犹如两颗璀璨的明星，它们联袂登场，共同编织出动态网站的绚丽篇章。
东方睿赢
28 0
weixin_836869520
|
4月前
|
安全 Java API
Java中的Servlet编程详解
Java中的Servlet编程详解
weixin_836869520
54 2
weixin_836869520
|
4月前
|
Java 数据库连接 开发者
Java中的Servlet生命周期详解
Java中的Servlet生命周期详解
weixin_836869520
46 1
蓝易云
|
4月前
|
XML Java 数据格式
jsp和servlet有什么区别？
总的来说，JSP和Servlet都是创建动态Web应用程序的重要工具，但它们的使用依赖于特定的需求和上下文。
蓝易云
38 0
蓝易云
|
4月前
|
XML Java 数据格式
jsp和servlet有什么区别？
总的来说，JSP和Servlet都是创建动态Web应用程序的重要工具，但它们的使用依赖于特定的需求和上下文。
蓝易云
39 0
听风de歌
|
2天前
|
SQL 关系型数据库 MySQL
go语言数据库中mysql驱动安装
【11月更文挑战第2天】
听风de歌
14 4

热门文章

最新文章

  • 1
    13-TDengine使用JDBC-JNI连接报错：JNI connection is NULL
  • 2
    JDK6笔记（6）----JDBC4.0高级应用(2)
  • 3
    使用JDBC获取各数据库的Meta信息——表以及对应的列
  • 4
    利用JDBC-ODBC驱动访问SQL SERVER2008
  • 5
    报错：java.lang.ClassNotFoundException: com.mysql.cj.jdbc.Driver at java.base/jdk.internal.loader.Bui
  • 6
    logstash-input-jdbc实现mysql 与elasticsearch实时同步深入详解
  • 7
    OAF_JDBC系列2 - 通过JDBC连接SQLSERVER数据库DriverManager.getConnection
  • 8
    JDBC调用MySQL5存储过程
  • 9
    SoapUI Pro Project Solution Collection-DataSource(jdbc,excel)
  • 10
    使用JDBC连接MySQL数据库
  • 1
    连接和管理RDS
    224
  • 2
    购买和初始化阿里云RDS
    71
  • 3
    rds教程
    90
  • 4
    TiDB与MySQL、PostgreSQL等数据库的比较分析
    1051
  • 5
    OBCP实践 - 迁移 MySQL 数据到 OceanBase 集群
    120
  • 6
    Flink CDC产品常见问题之mysql整库同步到starrock时任务挂掉如何解决
    154
  • 7
    TIDB和MySQL的区别
    867
  • 8
    Flink CDC产品常见问题之mysql整库同步到starrock时任务挂掉如何解决
    343
  • 9
    [MySQL]事务原理之redo log,undo log
    180
  • 10
    [MySQL]SQL优化之索引的使用规则
    74

    • 相关课程

    更多
  • 高校精品课-西安交通大学-Web 编程技术
  • JDBC数据库开发入门
  • JDBC数据库开发进阶
  • Servlet入门
  • Java Web项目实战 - 图书商城
  • Java面试疑难点解析 - Java Web开发

    • 相关电子书

    更多
  • Web应用系统性能优化
  • 高性能Web架构之缓存体系
  • PWA:移动Web的现在与未来

    • 相关实验场景

    更多
  • MySQL基础-学生管理系统数据库设计
  • 基于ECS搭建Java Web开发环境
  • 使用Aliyun Java Intializr进行项目开发
  • 搭建Java Web开发环境
  • Internet Information Services（IIS）部署Web项目
  • 部署基于Dragonwell的Java运行环境

    • 推荐镜像

    更多
  • mysql
  • postgresql
  • mariadb
    • 下一篇
    无影云桌面