航班信息管理系统(JDBC)

目录

航班信息管理系统

  • 前言:现在有一家航空公司为了提高用户体验,希望做一个航班信息系统,用户可以根据需求去对航班信息进行操作。组长把这个任务安排给了程序员赵丹,赵丹发现这里需要通过Java代码操作数据库,并且用户是可以在控制台做对应的操作,JDBC可以帮她解决这个问题。学习起来,试着把这个系统实现出来。
  • 目标
    • 使用 JDBC 操作 MySql 数据库
  • 需求
    • 显示航班信息系统主菜单
    • 列出所有的航班信息
    • 按起飞时间查询
    • 按目的地查询
    • 删除航班
    • 更新航班
    • 退出系统
  • 提示
    • 创建数据库表 airinfo,添加测试数据不少于 4 条。要求主键自增。
    • 创建实体类 AirInfo,根据业务提供需要的构造方法和 setter/getter方法。
    • 创建 BaseDao 类,实现数据库连接和关闭功能。
    • 创建 DAO 接口 AirInfoDao,定义查询所有航班,按日期和目的地查询航班,删除航班,更新航班的方法。
    • 创建 DAO 实现类 AirInfoDaoImpl,继承 BaseDao 类,实现 AirInfoDao接口,使用 JDBC 完成相应数据库操作。
    • 创建 Main 类,启动和运行系统。

实现

  • 在开始设计前,先介绍一下本项目的大体结构,如下图所示。
    航班信息管理系统(JDBC)
    • AirInfo:实体类
    • DBUtils:JDBC工具类,实现数据库连接、增删改查和关闭功能,并且在本项目中,新增了Druid数据库连接池。
    • AirInfoDaoImpl:Dao作为接口,Impl作为实现类,定义查询所有航班,按日期、目的地和航班号查询航班,删除航班,更新航班的方法。
    • AirView:视图类
    • db.properties:数据库及连接池的配置信息。
    • OutNumberBoundException:检验用户输入操作是否不符合要求。
    • Main:启动类

1. 创建数据表

航班信息管理系统(JDBC)

2. 数据库工具类实现

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
/**
 * 数据库连接池 + 工具类
 */
public class DBUtils {
    // 1. 定义变量
    private Connection conn = null;
    private PreparedStatement ps = null;
    private ResultSet rs = null;
    // 定义受影响的行数
    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. 加载驱动信息
        ResourceBundle bundle = ResourceBundle.getBundle("db");
        driver = bundle.getString("driver");
        url = bundle.getString("url");
        userName = bundle.getString("username");
        passWord = bundle.getString("password");

        // 使用Druid连接池
        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. 建立连接
    protected Connection getConn(){
        try {
            // Druid
            conn = druid.getConnection();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return conn;
    }

    // 4. 获得预处理通道
    protected PreparedStatement getPs(String sql){
        try {
            ps = getConn().prepareStatement(sql);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return ps;
    }

    // 5. 绑定参数
    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. 执行操作(增删改)
    protected int update(String sql, List list){
        getPs(sql);
        param(list);
        try {
            count = ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return count;
    }

    // 7. 执行操作(查询)
    protected ResultSet query(String sql, List list){
        getPs(sql);
        param(list);
        try {
            rs = ps.executeQuery();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return rs;
    }

    // 8. 关闭连接
    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 类

AirInfo.java
public class AirInfo {
    // 编号
    private int id;
    // 航班号
    private String number;
    // 目的地
    private String destination;
    // 出发时间
    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 类

AirInfoDao.java
/**
 * 航班功能接口
 */
public interface AirInfoDao {
    /**
     * 查询所有航班
     * @return
     */
    public List<AirInfo> findAll();

    /**
     * 根据起飞时间查询航班
     * @param flight_date
     * @return
     */
    public List<AirInfo> findByDate(String flight_date);

    /**
     * 根据目的地查询航班
     * @param destination
     * @return
     */
    public List<AirInfo> findByDestination(String destination);

    /**
     * 根据航班号查询航班
     * @param number
     * @return
     */
    public AirInfo findByNumber(String number);

    /**
     * 删除航班
     * @param id
     * @return
     */
    public boolean deleteFlight(int id);

    /**
     * 更新航班
     * @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 {
    /**
     * 列出所有航班
     * @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;
    }

    /**
     * 根据起飞时间查找
     * @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;
    }

    /**
     * 根据目的地查找
     * @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;
    }

    /**
     * 根据航班号查找
     * @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;
    }

    /**
     * 删除航班
     * @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;
    }

    /**
     * 更新航班
     * @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. 自定义 Exception

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

6. View 类

AirView.java
/**
 * 视图
 */
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("********** 欢迎使用航班信息管理系统 **********" + "\n");
            System.out.println("请选择操作(1. 列出所有航班,2. 按起飞时间查询,3. 按目的地查询,4. 按航班号查询" +
                    ",5. 删除航班,6." +
                    " 更新航班,7. 离开系统):");
            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("欢迎下次使用~");
                System.exit(0);
                break;
        }
    }

    /**
     * 验证用户输入是否合法
     * @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("数字的范围必须在" + begin + "和" + end + "之间!");
            }
            return num;
        } catch (NumberFormatException e){
            throw new NumberFormatException("输入的必须是数字!");
        }
    }

    /**
     * 展示所有航班信息的方法
     */
    public void doFindAll(){
        dao = new AirInfoDaoImpl();
        List<AirInfo> airInfos = dao.findAll();
        printAll(airInfos);
    }

    /**
     * 根据航班起飞日期寻找
     */
    public void doFindByDate(){
        System.out.println("请输入日期:");
        String day = input.next();
        List<AirInfo> airInfos = dao.findByDate(day);
        printAll(airInfos);
    }

    /**
     * 根据目的地查找
     */
    public void doFindByDes(){
        System.out.println("请输入目的地:");
        String des = input.next();
        List<AirInfo> airInfos = dao.findByDestination(des);
        printAll(airInfos);
    }

    /**
     * 根据航班号查找
     */
    public void doFindByNumber(){
        System.out.println("请输入航班号:");
        String number = input.next();
        AirInfo airInfo = dao.findByNumber(number);
        System.out.println("编号\t\t航班号\t\t\t目的地\t\t\t\t\t\t起飞日期");
        System.out.println(airInfo.getId()+"\t\t"+airInfo.getNumber()
                +"\t\t\t"+airInfo.getDestination()+"\t\t\t\t"+airInfo.getFlight_date());
    }

    /**
     * 打印信息
     * @param airInfos
     */
    public void printAll(List<AirInfo> airInfos){
        System.out.println("编号\t\t航班号\t\t\t目的地\t\t\t\t\t\t起飞日期");
        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());
        }
    }

    /**
     * 执行删除操作
     */
    public void doDelete(){
        System.out.println("请输入需要删除的航班编号:");
        int id1 = input.nextInt();
        boolean flag = dao.deleteFlight(id1);
        if (flag == true){
            System.out.println("删除成功!");
        } else {
            System.out.println("不存在该航班信息!请检查您的输入");
        }
    }

    /**
     * 执行更新操作
     */
    public void doUpdate(){
        while (true) {
            System.out.println("请输入需要更新的航班编号:");
            int id2 = input.nextInt();
            if (doFindById(id2) == 0){
                System.out.println("不存在该航班信息!请检查您的输入");
                break;
            }
            System.out.println("请输入新的航班号:");
            String newNumber = input.next();
            System.out.println("请输入新的目的地:");
            String newDes = input.next();
            System.out.println("请输入新的起飞日期:");
            String newDate = input.next();
            boolean flag = dao.updateFlight(id2, newNumber, newDes, newDate);
            if (flag == true){
                System.out.println("更新成功!");
                break;
            } else {
                System.out.println("操作失败!请检查您的输入");
            }
            break;
        }
    }

    /**
     * 根据编号查找
     * @param id
     * @return
     */
    public int doFindById(int id){
        AirInfo airInfo = dao.findById(id);
        int num = airInfo.getId();
        return num;
    }
}

7. Main 类

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


8. 小结

  • 至此,基本实现根据上文需求所述的基于Java的航班信息管理系统,若出现BUG,还请批评指正,谢谢!
上一篇:LAMP(8.0编译)


下一篇:LAMP源码包搭建