Flight information management system

• Now, in order to improve the user experience, an airline hopes to build a flight information system, and users can operate the flight information according to their needs. The team leader assigned this task to programmer Zhao Dan. Zhao Dan found that the database needs to be operated through Java code, and the user can do the corresponding operation on the console. JDBC can help her solve this problem. Learn and try to implement this system.
• Objectives:
  • Using JDBC to operate MySql database
• Requirements:
  • Display the main menu of flight information system
  • List all flight information
  • Query by departure time
  • Query by destination
  • Delete flight
  • Update flight
  • Exit the system
• Tips:
  • Create database table airinfo and add at least 4 test data. The primary key is required to be self incremented.
  • Create the entity class AirInfo and provide the construction method and setter/getter method required according to the business.
  • Create a BaseDao class to realize the functions of database connection and closing.
  • Create DAO interface AirInfoDao and define the methods of querying all flights, querying flights by date and destination, deleting flights and updating flights.
  • Create DAO implementation class AirInfoDaoImpl, inherit BaseDao class, implement AirInfoDao interface, and use JDBC to complete corresponding database operations.
  • Create the Main class to start and run the system.

realization

• Before starting the design, first introduce the general structure of the project, as shown in the figure below.
  
  • AirInfo: entity class
  • DBUtils: JDBC tool class, which realizes the functions of database connection, addition, deletion, modification and closing. In this project, Druid database connection pool is added.
  • AirInfoDaoImpl: Dao is used as the interface and Impl is used as the implementation class to define the methods to query all flights, query flights by date, destination and flight number, delete flights and update flights.
  • AirView: view class
  • db.properties: configuration information of database and connection pool.
  • OutNumberBoundException: check whether the user input operation does not meet the requirements.
  • Main: startup class

1. Create data table

2. Database tool class implementation

db.properties

driver = com.mysql.cj.jdbc.Driver
url = jdbc:mysql://localhost:3306/AirInfo?serverTimezone=UTC&characterEncoding=utf-8&useSSL=false
username = root
password = 123456
filters = stat
initialSize = 2
maxActive = 300
maxWait = 60000
timeBetweenEvictionRunsMillis = 60000
minEvictableIdleTimeMillis = 300000
validationQuery = SELECT 1
testWhileIdle = true
testOnBorrow = false
testOnReturn = false
poolPreparedStatements = false
maxPoolPreparedStatementPerConnectionSize = 200

DBUtils.java

/**
 * Database connection pool + tool class
 */
public class DBUtils {
    // 1. Define variables
    private Connection conn = null;
    private PreparedStatement ps = null;
    private ResultSet rs = null;
    // Define the number of rows affected
    private int count;

    private static String driver;
    private static String url;
    private static String userName;
    private static String passWord;
    private static DruidDataSource druid = new DruidDataSource();


    static {
        // 2. Load driver information
        ResourceBundle bundle = ResourceBundle.getBundle("db");
        driver = bundle.getString("driver");
        url = bundle.getString("url");
        userName = bundle.getString("username");
        passWord = bundle.getString("password");

        // Use Druid connection pool
        druid.setDriverClassName(driver);
        druid.setUrl(url);
        druid.setUsername(userName);
        druid.setPassword(passWord);

        try {
            druid.setFilters(bundle.getString("filters"));
            druid.setInitialSize(Integer.parseInt(bundle.getString("initialSize")));
            druid.setMaxActive(Integer.parseInt(bundle.getString("maxActive")));
            druid.setMaxWait(Long.parseLong(bundle.getString("maxWait")));
            druid.setTimeBetweenEvictionRunsMillis(Long.parseLong(bundle.getString("timeBetweenEvictionRunsMillis")));
            druid.setMinEvictableIdleTimeMillis(Long.parseLong(bundle.getString("minEvictableIdleTimeMillis")));
            druid.setValidationQuery(bundle.getString("validationQuery"));
            druid.setTestWhileIdle(Boolean.parseBoolean(bundle.getString("testWhileIdle")));
            druid.setTestOnBorrow(Boolean.parseBoolean(bundle.getString("testOnBorrow")));
            druid.setTestOnReturn(Boolean.parseBoolean(bundle.getString("testOnReturn")));
            druid.setPoolPreparedStatements(Boolean.parseBoolean(bundle.getString("poolPreparedStatements")));
            druid.setMaxPoolPreparedStatementPerConnectionSize(Integer.parseInt(bundle.getString("maxPoolPreparedStatementPerConnectionSize")));
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    // 3. Establish connection
    protected Connection getConn(){
        try {
            // Druid
            conn = druid.getConnection();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return conn;
    }

    // 4. Obtain preprocessing channel
    protected PreparedStatement getPs(String sql){
        try {
            ps = getConn().prepareStatement(sql);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return ps;
    }

    // 5. Binding parameters
    protected void param(List list){
        if (list != null && list.size() > 0){
            for (int i=0; i<list.size(); i++){
                try {
                    ps.setObject(i+1,list.get(i));
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }

    // 6. Execution (addition, deletion and modification)
    protected int update(String sql, List list){
        getPs(sql);
        param(list);
        try {
            count = ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return count;
    }

    // 7. Execute operation (query)
    protected ResultSet query(String sql, List list){
        getPs(sql);
        param(list);
        try {
            rs = ps.executeQuery();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return rs;
    }

    // 8. Close the connection
    protected void closeAll(){
        try {
            if (rs != null) {
                rs.close();
            }
            if (ps != null) {
                ps.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

}

3. Bean class

AirInfo.java

public class AirInfo {
    // number
    private int id;
    // flight number
    private String number;
    // destination
    private String destination;
    // Departure time
    private String flight_date;

    public AirInfo() {
    }

    public AirInfo(int id, String number, String destination, String flight_date) {
        this.id = id;
        this.number = number;
        this.destination = destination;
        this.flight_date = flight_date;
    }

    @Override
    public String toString() {
        return "AirInfo{" +
                "id=" + id +
                ", number='" + number + '\'' +
                ", destination='" + destination + '\'' +
                ", flight_date=" + flight_date +
                '}';
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getNumber() {
        return number;
    }

    public void setNumber(String number) {
        this.number = number;
    }

    public String getDestination() {
        return destination;
    }

    public void setDestination(String destination) {
        this.destination = destination;
    }

    public String getFlight_date() {
        return flight_date;
    }

    public void setFlight_date(String flight_date) {
        this.flight_date = flight_date;
    }
}

4. Dao class

AirInfoDao.java

/**
 * Flight function interface
 */
public interface AirInfoDao {
    /**
     * Query all flights
     * @return
     */
    public List<AirInfo> findAll();

    /**
     * Query flights according to departure time
     * @param flight_date
     * @return
     */
    public List<AirInfo> findByDate(String flight_date);

    /**
     * Query flights by destination
     * @param destination
     * @return
     */
    public List<AirInfo> findByDestination(String destination);

    /**
     * Query flights according to flight number
     * @param number
     * @return
     */
    public AirInfo findByNumber(String number);

    /**
     * Delete flight
     * @param id
     * @return
     */
    public boolean deleteFlight(int id);

    /**
     * Update flight
     * @param id
     * @param newNumber
     * @param newDestin
     * @param newDate
     * @return
     */
    public boolean updateFlight(int id, String newNumber, String newDestin, String newDate);

    public AirInfo findById(int id);
}

AirInfoDaoImpl.java

@SuppressWarnings("all")
public class AirInfoDaoImpl extends DBUtils implements AirInfoDao {
    /**
     * List all flights
     * @return
     */
    @Override
    public List<AirInfo> findAll() {
        try {
            String sql = "select * from airinfo";
            List<AirInfo> airInfos = new ArrayList<>();
            ResultSet rs = query(sql,airInfos);
            while (rs.next()){
                AirInfo airInfo = new AirInfo();
                airInfo.setId(rs.getInt(1));
                airInfo.setNumber(rs.getString(2));
                airInfo.setDestination(rs.getString(3));
                airInfo.setFlight_date(rs.getString(4));

                airInfos.add(airInfo);
            }
            return airInfos;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }

        return null;
    }

    /**
     * Search according to departure time
     * @param flight_date
     * @return
     */
    @Override
    public List<AirInfo> findByDate(String flight_date) {
        try {
            String sql = "select * from airinfo where flight_date = ?";
            List list = new ArrayList();
            list.add(flight_date);
            List<AirInfo> airInfos = new ArrayList<>();
            ResultSet rs = query(sql,list);

            while (rs.next()){
                AirInfo airInfo = new AirInfo();
                airInfo.setId(rs.getInt(1));
                airInfo.setNumber(rs.getString(2));
                airInfo.setDestination(rs.getString(3));
                airInfo.setFlight_date(rs.getString(4));

                airInfos.add(airInfo);
            }
            return airInfos;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }
        return null;
    }

    /**
     * Find by destination
     * @param destination
     * @return
     */
    @Override
    public List<AirInfo> findByDestination(String destination) {
        try {
            String sql = "select * from airinfo where destination = ?";
            List list = new ArrayList();
            list.add(destination);
            List<AirInfo> airInfos = new ArrayList<>();
            ResultSet rs = query(sql,list);

            while (rs.next()){
                AirInfo airInfo = new AirInfo();
                airInfo.setId(rs.getInt(1));
                airInfo.setNumber(rs.getString(2));
                airInfo.setDestination(rs.getString(3));
                airInfo.setFlight_date(rs.getString(4));

                airInfos.add(airInfo);
            }
            return airInfos;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }
        return null;
    }

    /**
     * Search by flight number
     * @param number
     * @return
     */
    @Override
    public AirInfo findByNumber(String number) {
        AirInfo airInfo = new AirInfo();
        try {
            String sql = "select * from airinfo where number = ?";
            List list = new ArrayList();
            list.add(number);
            ResultSet rs = query(sql,list);
            while (rs.next()){
                airInfo.setId(rs.getInt(1));
                airInfo.setNumber(rs.getString(2));
                airInfo.setDestination(rs.getString(3));
                airInfo.setFlight_date(rs.getString(4));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return airInfo;
    }

    /**
     * Delete flight
     * @param number
     * @return
     */
    @Override
    public boolean deleteFlight(int id) {
        String sql = "delete from airinfo where id = ?";
        List list = new ArrayList();
        list.add(id);
        int result = update(sql,list);
        if (result != 0){
            return true;
        }else
            return false;
    }

    /**
     * Update flight
     * @param number
     * @param airInfo
     * @return
     */
    @Override
    public boolean updateFlight(int id, String newNumber, String newDestin, String newDate) {
        String sql = "update airinfo set number = ?, destination = ?, flight_date = ? where id = ?";
        List list = new ArrayList();
        list.add(newNumber);
        list.add(newDestin);
        list.add(newDate);
        list.add(id);
        int result = update(sql,list);
        if (result == 1){
            return true;
        }else
            return false;
    }

    @Override
    public AirInfo findById(int id) {
        AirInfo airInfo = new AirInfo();
        try {
            String sql = "select * from airinfo where id = ?";
            List list = new ArrayList();
            list.add(id);
            ResultSet rs = query(sql,list);
            while (rs.next()){
                airInfo.setId(rs.getInt(1));
                airInfo.setNumber(rs.getString(2));
                airInfo.setDestination(rs.getString(3));
                airInfo.setFlight_date(rs.getString(4));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return airInfo;
    }
}

5. Custom Exception

OutNumberBoundException.java

public class OutNumberBoundException extends Throwable{
    public OutNumberBoundException(String s){
        super(s);
    }
}

6. View class

AirView.java

/**
 * view
 */
public class AirView {
    private Scanner input = new Scanner(System.in);
    private AirInfoDao dao = new AirInfoDaoImpl();

    public void menu(){
        int num = 0;
        do {
            System.out.println("********** Welcome to the flight information management system **********" + "\n");
            System.out.println("Please select action (1). List all flights, 2. Query by departure time, 3. Query by destination, 4. Query by flight number" +
                    "，5. Delete flight, 6." +
                    " Update flights, 7. Leave system):");
            String text = input.next();
            try{
                num = validateNum(text,1,7);
                break;
            } catch (OutNumberBoundException e) {
                System.err.println(e.getMessage());
            }
        } while (true);

        switch (num) {
            case 1:
                doFindAll();
                break;
            case 2:
                doFindByDate();
                break;
            case 3:
                doFindByDes();
                break;
            case 4:
                doFindByNumber();
                break;
            case 5:
                doDelete();
                break;
            case 6:
                doUpdate();
                break;
            case 7:
                System.out.println("Welcome to use next time～");
                System.exit(0);
                break;
        }
    }

    /**
     * Verify whether the user input is legal
     * @param strNum
     * @param begin
     * @param end
     * @return
     * @throws OutNumberBoundException
     */
    private int validateNum(String strNum, int begin, int end) throws OutNumberBoundException {
        try {
            int num = Integer.valueOf(strNum);
            if (num < begin || num > end){
                throw new OutNumberBoundException("The range of numbers must be" + begin + "and" + end + "between!");
            }
            return num;
        } catch (NumberFormatException e){
            throw new NumberFormatException("The input must be a number!");
        }
    }

    /**
     * Method of displaying all flight information
     */
    public void doFindAll(){
        dao = new AirInfoDaoImpl();
        List<AirInfo> airInfos = dao.findAll();
        printAll(airInfos);
    }

    /**
     * Search according to the flight departure date
     */
    public void doFindByDate(){
        System.out.println("Please enter date:");
        String day = input.next();
        List<AirInfo> airInfos = dao.findByDate(day);
        printAll(airInfos);
    }

    /**
     * Find by destination
     */
    public void doFindByDes(){
        System.out.println("Please enter the destination:");
        String des = input.next();
        List<AirInfo> airInfos = dao.findByDestination(des);
        printAll(airInfos);
    }

    /**
     * Search by flight number
     */
    public void doFindByNumber(){
        System.out.println("Please enter the flight number:");
        String number = input.next();
        AirInfo airInfo = dao.findByNumber(number);
        System.out.println("number\t\t flight number\t\t\t destination\t\t\t\t\t\t Departure date");
        System.out.println(airInfo.getId()+"\t\t"+airInfo.getNumber()
                +"\t\t\t"+airInfo.getDestination()+"\t\t\t\t"+airInfo.getFlight_date());
    }

    /**
     * Print information
     * @param airInfos
     */
    public void printAll(List<AirInfo> airInfos){
        System.out.println("number\t\t flight number\t\t\t destination\t\t\t\t\t\t Departure date");
        for (AirInfo airInfo : airInfos) {
            System.out.println(airInfo.getId()+"\t\t"+airInfo.getNumber()
                    +"\t\t\t"+airInfo.getDestination()+"\t\t\t\t"+airInfo.getFlight_date());
        }
    }

    /**
     * Perform delete operation
     */
    public void doDelete(){
        System.out.println("Please enter the flight number to be deleted:");
        int id1 = input.nextInt();
        boolean flag = dao.deleteFlight(id1);
        if (flag == true){
            System.out.println("Delete succeeded!");
        } else {
            System.out.println("The flight information does not exist! Please check your input");
        }
    }

    /**
     * Perform update operation
     */
    public void doUpdate(){
        while (true) {
            System.out.println("Please enter the flight number to be updated:");
            int id2 = input.nextInt();
            if (doFindById(id2) == 0){
                System.out.println("The flight information does not exist! Please check your input");
                break;
            }
            System.out.println("Please enter a new flight number:");
            String newNumber = input.next();
            System.out.println("Please enter a new destination:");
            String newDes = input.next();
            System.out.println("Please enter a new departure date:");
            String newDate = input.next();
            boolean flag = dao.updateFlight(id2, newNumber, newDes, newDate);
            if (flag == true){
                System.out.println("Update succeeded!");
                break;
            } else {
                System.out.println("Operation failed! Please check your input");
            }
            break;
        }
    }

    /**
     * Find by number
     * @param id
     * @return
     */
    public int doFindById(int id){
        AirInfo airInfo = dao.findById(id);
        int num = airInfo.getId();
        return num;
    }
}

7. Main class

Main.java

public class Main {
    /**
     * main program
     * @param args
     */
    public static void main(String[] args) {
        AirView airView = new AirView();
        while (true){
            airView.menu();
            System.out.println();
        }
    }
}

8. Summary

• So far, the Java based flight information management system described above has been basically realized. If there is a BUG, please criticize and correct it. Thank you!