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

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

2022-05-26 887
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
RDS AI 助手，专业版
RDS MySQL DuckDB 分析主实例，集群系列 4核8GB
RDS MySQL DuckDB 分析主实例，基础系列 4核8GB
简介： 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 MYSQL
云数据库 RDS MySQL 版java
Java JDBC
servlet云数据库 RDS MySQL 版
相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL，然后进行数据表的CRUD操作。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。 &nbsp; 相关的阿里云产品：云数据库RDS MySQL 版 阿里云关系型数据库RDS（Relational Database Service）是一种稳定可靠、可弹性伸缩的在线数据库服务，提供容灾、备份、恢复、迁移等方面的全套解决方案，彻底解决数据库运维的烦恼。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/mysql&nbsp;
水坚石青
目录
相关文章
啦啦啦191
|
6月前
|
安全 Java API
Java Web 在线商城项目最新技术实操指南帮助开发者高效完成商城项目开发
本项目基于Spring Boot 3.2与Vue 3构建现代化在线商城，涵盖技术选型、核心功能实现、安全控制与容器化部署，助开发者掌握最新Java Web全栈开发实践。
啦啦啦191
630 1
啦啦啦191
|
6月前
|
存储 前端开发 Java
【JAVA】Java 项目实战之 Java Web 在线商城项目开发实战指南
本文介绍基于Java Web的在线商城技术方案与实现，涵盖三层架构设计、MySQL数据库建模及核心功能开发。通过Spring MVC + MyBatis + Thymeleaf实现商品展示、购物车等模块，提供完整代码示例，助力掌握Java Web项目实战技能。（238字）
啦啦啦191
734 0
啦啦啦191
|
7月前
|
前端开发 Java 数据库
Java 项目实战从入门到精通 ：Java Web 在线商城项目开发指南
本文介绍了一个基于Java Web的在线商城项目，涵盖技术方案与应用实例。项目采用Spring、Spring MVC和MyBatis框架，结合MySQL数据库，实现商品展示、购物车、用户注册登录等核心功能。通过Spring Boot快速搭建项目结构，使用JPA进行数据持久化，并通过Thymeleaf模板展示页面。项目结构清晰，适合Java Web初学者学习与拓展。
啦啦啦191
498 1
东方睿赢
|
Java 关系型数据库 数据库连接
JDBC：Java与数据库的“黄金搭档”，为何它如此重要？
JDBC：Java与数据库的“黄金搭档”，为何它如此重要？
东方睿赢
169 8
龙大吉
|
Java 数据库连接 API
JDBC：Java数据库连接的“黑科技”大揭秘
JDBC：Java数据库连接的“黑科技”大揭秘
龙大吉
133 7
Codelinghu
|
Java 容器
【学习笔记】Jsp与Servlet技术
【学习笔记】Jsp与Servlet技术
Codelinghu
345 0
游客kufrkwrbkmpsa
|
12月前
|
SQL Java 中间件
【YashanDB知识库】yasdb jdbc驱动集成BeetISQL中间件，业务(java)报autoAssignKey failure异常
在BeetISQL 2.13.8版本中，客户使用batch insert向yashandb表插入数据并尝试获取自动生成的sequence id时，出现类型转换异常。原因是beetlsql在prepareStatement时未指定返回列，导致yashan JDBC驱动返回rowid（字符串），与Java Bean中的数字类型tid不匹配。此问题影响业务流程，使无法正确获取sequence id。解决方法包括：1) 在batchInsert时不返回自动生成的sequence id；2) 升级至BeetISQL 3，其已修正该问题。
游客kufrkwrbkmpsa
254 1
【YashanDB知识库】yasdb jdbc驱动集成BeetISQL中间件，业务(java)报autoAssignKey failure异常
游客kufrkwrbkmpsa
|
12月前
|
SQL druid Oracle
【YashanDB知识库】yasdb jdbc驱动集成druid连接池，业务(java)日志中有token IDENTIFIER start异常
客户Java日志中出现异常，影响Druid的merge SQL功能（将SQL字面量替换为绑定变量以统计性能），但不影响正常业务流程。原因是Druid在merge SQL时传入null作为dbType，导致无法解析递归查询中的`start`关键字。
游客kufrkwrbkmpsa
310 3
蓝易云
|
Java 数据库连接 数据库
springboot java.lang.ClassNotFoundException: dm.jdbc.driver.DmDriver应该如何解决
通过上述步骤，可以有效解决Spring Boot项目中遇到的 `java.lang.ClassNotFoundException: dm.jdbc.driver.DmDriver`问题。确保在项目中正确添加达梦数据库的JDBC驱动依赖，并在配置文件中正确配置数据源信息，是解决此问题的关键。通过这些方法，可以确保Spring Boot项目能够正确连接达梦数据库并正常运行。
蓝易云
2469 31
潜意识Java
|
SQL Java 关系型数据库
使用 JDBC 实现 Java 数据库操作
JDBC（Java Database Connectivity）是 Java 提供的数据库访问技术，允许通过 SQL 语句与数据库交互。本文详细介绍了 JDBC 的使用方法，包括环境准备、编程步骤和完整示例。
潜意识Java
1138 7