任务概述
现在有一家航空公司为了提高用户体验，希望做一个航班信息系统，用户可以根据需求去对航班信息进行操作。组长把这个任务安排给了程序员赵丹，赵丹发现这里需要通过 java 代码操作数据库，并且用户是可以在控制台做对应的操作，JDBC 可以帮她解决这个问题。学习起来，试着把这个系统实现出来。

模块分析

数据库操作

代码实现

db.properties文件配置

driver=com.mysql.cj.jdbc.Driver
name=root
pass=123456
url=jdbc:mysql://localhost:3306/air?serverTimezone=UTC

导入jar包

AirInfo类

package bean;

import java.util.Date;


public class AirInfo {
    private int id; //编号
    private String number;  //航班号
    private String destination; //目的地
    private Date date;  //起飞日期

    public AirInfo() {
    }

    public AirInfo(int id, String number, String destination, Date date) {
        this.id = id;
        this.number = number;
        this.destination = destination;
        this.date = date;
    }
    @Override
    public String toString() {
        return id +" "+'t'+'t'+ number +" "+'t'+ destination +" "+'t'+'t'+ 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 Date getDate() {
        return date;
    }

    public void setDate(Date date) {
        this.date = date;
    }
}

AirInfoDao接口

package dao;

import bean.AirInfo;

import java.util.List;

public interface AirInfoDao {
    //列出所有航班
    public List getAll();

    //按起飞时间查询
    public List getByDate(String date);

    //按目的地查询
    public List getByDestination(String destination);

    //删除航班
    public int delete(String number);

    //更新航班
    public int update(int num,String newDestination,String newDate);

}

AirInfoDaoImpl实现类

package dao;

import bean.AirInfo;
import util.baseDao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


public class AirInfoDaoImpl extends baseDao implements AirInfoDao {

    
    @Override
    public List getAll() {
        ArrayList arrayList = new ArrayList();
        try {
            String sql = "select * from airinfo";
            ResultSet resultSet = query(sql, null);
            while (resultSet.next()) {
                AirInfo airInfo = new AirInfo();
                airInfo.setId(resultSet.getInt("id"));
                airInfo.setNumber(resultSet.getString("number"));
                airInfo.setDestination(resultSet.getString("destination"));
                airInfo.setDate(resultSet.getDate("date"));
                arrayList.add(airInfo);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }

        return arrayList;
    }

    
    @Override
    public List getByDate(String date) {
        ArrayList list = new ArrayList();
        try {
            String sql = "select * from airinfo where date=?";
            ArrayList arrayList = new ArrayList();
            arrayList.add(date);
            ResultSet resultSet = query(sql, arrayList);
            if (resultSet.next() == false) {
                return null;
            }else {
                do{
                    AirInfo airInfo = new AirInfo();
                    airInfo.setId(resultSet.getInt("id"));
                    airInfo.setNumber(resultSet.getString("number"));
                    airInfo.setDestination(resultSet.getString("destination"));
                    airInfo.setDate(resultSet.getDate("date"));
                    list.add(airInfo);
                }while (resultSet.next());
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }

        return list;
    }

    
    @Override
    public List getByDestination(String destination) {
        ArrayList arrayList = new ArrayList();
        try {
            String sql = "select * from airinfo where destination like ?";
            ArrayList list = new ArrayList();
            list.add("%" + destination + "%");
            ResultSet resultSet = query(sql, list);
            if (resultSet.next() == false) {
                return null;
            }else {
                do {
                    AirInfo airInfo = new AirInfo();
                    airInfo.setId(resultSet.getInt("id"));
                    airInfo.setNumber(resultSet.getString("number"));
                    airInfo.setDestination(resultSet.getString("destination"));
                    airInfo.setDate(resultSet.getDate("date"));
                    arrayList.add(airInfo);
                }while (resultSet.next());
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return arrayList;
    }

    
    @Override
    public int delete(String number) {
        int count = -1;
        try {
            String sql = "delete from airinfo where number=?";
            ArrayList list = new ArrayList();
            list.add(number);
            count = update(sql,list);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return count;
    }

    
    public List getById(String number) {
        ArrayList arrayList = new ArrayList();
        try {
            String sql = "select * from airinfo where number=?";
            ArrayList list = new ArrayList();
            list.add(number);
            ResultSet resultSet = query(sql, list);
            if (resultSet.next() == false) {//查询结果为空
                return null;
            } else {
                do {
                    AirInfo airInfo = new AirInfo();
                    airInfo.setId(resultSet.getInt("id"));
                    airInfo.setNumber(resultSet.getString("number"));
                    airInfo.setDestination(resultSet.getString("destination"));
                    airInfo.setDate(resultSet.getDate("date"));
                    arrayList.add(airInfo);
                }while (resultSet.next());
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return arrayList;
    }
    
    @Override
    public int update(int num, String newDestination, String newDate) {
        int count = -1;
        try {
            String sql = "update airinfo set destination=?,date=? where number=?";
            ArrayList list = new ArrayList();
            list.add(newDestination);
            list.add(newDate);
            list.add(num);
            count = update(sql, list);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return count;
    }

    
    public AirInfo getByNumber(int number) {
        AirInfo airInfo = new AirInfo();
        try {
            String sql = "select * from airinfo where number=?";
            ArrayList list = new ArrayList();
            list.add(number);
            ResultSet resultSet = query(sql, list);
            if (resultSet.next() == false) {//查询结果为空
                return null;
            } else {
                airInfo.setId(resultSet.getInt("id"));
                airInfo.setNumber(resultSet.getString("number"));
                airInfo.setDestination(resultSet.getString("destination"));
                airInfo.setDate(resultSet.getDate("date"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return airInfo;
    }
}

Main类

package test;

import bean.AirInfo;
import dao.AirInfoDaoImpl;
import util.baseDao;
import view.AirView;

import java.util.List;
import java.util.Scanner;

public class Main {

    private static baseDao b = new baseDao();
    private static AirView v = new AirView();
    private static AirInfoDaoImpl dao = new AirInfoDaoImpl();
    private static Scanner input = new Scanner(System.in);

    public static void main(String[] args) {
        v.welcome();
        p: while (true) {
            int m = v.menu();
            switch (m){
                case 1:
                    //1.列出所有航班
                    check();
                    break;
                case 2:
                    //2.按起飞时间查询
                    findByAirDate();
                    break;
                case 3:
                    //3.按目的地查询
                    findByAirDestination();
                    break;
                case 4:
                    //4.删除航班
                    cut();
                    break;
                case 5:
                    //5.更新航班
                    modify();
                    break;
                case 6:
                    //6.离开系统
                    v.bye();
                    break p;
            }
        }
    }

    
    private static void check() {
        v.printAll(dao.getAll());

    }

    
    private static void findByAirDate() {
        String date = v.findByDate();//得到起飞时间
        List infoList = dao.getByDate(date);
        if (infoList != null) {
            v.printAll(infoList);
        }else {
            v.printNull();
        }
    }

    
    private static void findByAirDestination() {
        String destination = v.findByDestination();
        List infoList = dao.getByDestination(destination);
        if (infoList != null) {
            v.printAll(infoList);
        }else {
            v.printNull();
        }
    }

    
    private static void cut() {
        String id = v.getId();
        List airInfo = dao.getById(id);
        if (airInfo != null) {
            v.printAll(airInfo);
            if (v.isDelete() == 1) {
                dao.delete(id);
                v.success();
            }else {
                v.success();
            }
        }else {
            v.printNull();
        }
    }

    
    private static void modify() {
            int num = v.getById();
            AirInfo daoById = dao.getByNumber(num);
            if (daoById != null) {
                v.printAirInfo(daoById);
                String newAddress = v.getAddress();
                String newDate = v.getDate();
                dao.update(num,newAddress,newDate);
                v.success();
            }else {
                v.fail();
            }
    }


}

baseDao工具类

package util;

import com.alibaba.druid.pool.DruidDataSource;

import java.sql.*;
import java.util.List;
import java.util.ResourceBundle;


public class baseDao {
    
    private Connection connection;
    private PreparedStatement pps;
    private ResultSet resultSet;
    private int count;  //存储受影响行数

    private static String userName;
    private static String password;
    private static String url;
    private static String driverName;

    //德鲁伊
    private static DruidDataSource dataSource = new DruidDataSource();

    
    static {
        ResourceBundle bundle = ResourceBundle.getBundle("db");
        driverName = bundle.getString("driver");
        url = bundle.getString("url");
        userName = bundle.getString("name");
        password = bundle.getString("pass");

        dataSource.setUsername(userName);
        dataSource.setPassword(password);
        dataSource.setUrl(url);
        dataSource.setDriverClassName(driverName);
        dataSource.setTestWhileIdle(false);
    }

    
    protected Connection getConnection(){//受保护，能被子类调用(防止其他类调用)
        try {
            connection = dataSource.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }

    
    protected PreparedStatement getPps(String sql){
        try {
            getConnection();
            pps = getConnection().prepareStatement(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return pps;
    }

    
    protected void param(List list){
        try {
            if (list != null && list.size() > 0) {  //集合里面要有数据
                for (int i = 0; i < list.size(); i++) {   //遍历
                    pps.setObject(i+1,list.get(i));    //赋值 (list.get(i)取出对应数据)
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    
    protected int update(String sql,List list){
        try {
            getPps(sql);//得到预状态通道
            param(list);//绑定参数
            count = pps.executeUpdate();//得到受影响行数
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }
    //查询
    protected ResultSet query(String sql,List list){
        try {
            getPps(sql);//得到预状态通道
            param(list);//绑定参数
            resultSet = pps.executeQuery();
        } catch (SQLException e) {
        }
        return resultSet;
    }

    
    protected void closeAll(){
        try {
            if (connection != null) {
                connection.close();
            }
            if (pps != null) {
                pps.close();
            }
            if (resultSet != null) {
                resultSet.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

AirView

package view;

import bean.AirInfo;
import dao.AirInfoDaoImpl;

import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;

public class AirView {
    
    public void welcome(){
        System.out.println(" * * * * * * 欢迎使用航班信息管理系统 * * * * * * ");
    }

    private List airInfoList = new ArrayList<>();
    private AirInfoDaoImpl airInfoDao = new AirInfoDaoImpl();
    private Scanner input = new Scanner(System.in);
    private int count = -1;

    
    public int menu() {
        System.out.println("请选择操作:");
        System.out.println("1.列出所有航班,2.按起飞时间查询,3.按目的地查询,4.删除航班,5.更新航班6.离开系统");
        String text = input.next();
        try {
            count = Integer.parseInt(text);
        } catch (NumberFormatException e) {
        }
        if (count < 1 || count > 6) {
            System.out.println("亲,请按提示操作! 需要输入操作序号");
            return menu();
        }
        return count;
    }

    
    public void printAll(List airInfoList){
        System.out.println("航班信息如下:");
        System.out.println("编号 t航班号 t目的地tt起飞日期");
        for (AirInfo a : airInfoList) {
            System.out.println(a.toString());
        }
    }

    
    public String findByDate() {
        System.out.println("请输入起飞时间:(yyyy-MM-dd)");
        String text = input.next();
        return text;
    }

    
    public String findByDestination(){
        System.out.println("请输入目的地:");
        String text = input.next();
        return text;
    }

    
    public int isDelete(){
        System.out.println("是否确认删除?");
        System.out.println("1.确认");
        System.out.println("0.取消");
        String text = input.next();
        try {
            count = Integer.parseInt(text);
        } catch (NumberFormatException e) {
            e.printStackTrace();
        }
        if (count < 0 || count > 1) {
            System.out.println("亲,请按提示操作! 需要输入操作序号");
            return isDelete();
        }
        return count;
    }

    
    public String getId(){
        System.out.println("请输入需要操作的航班号:");
        String id = input.next();
        return id;
    }

    
    public int getById(){
        System.out.println("请输入要更新的航班号:");
        String s = input.next();
        count = Integer.parseInt(s);
        return count;
    }

    public String getAddress(){
        System.out.println("请输入新的目的地:");
        String s = input.next();
        return s;
    }

    public String getDate(){
        System.out.println("请输入新的航班起飞时间:");
        String s = input.next();
        return s;
    }

    public void printAirInfo(AirInfo a) {
        System.out.println("航班信息如下:");
        System.out.println("编号 t航班号 t目的地tt起飞日期");
        System.out.println(a.toString());
    }


    public void printNull(){
        System.out.println("暂无该航班信息！");
    }

    public void success(){
        System.out.println("操作成功！");
    }

    public void fail(){
        System.out.println("操作失败！");
    }

    public void bye(){
        System.out.println("欢迎下次使用!");
    }
}

航班信息系统（JDBC）

模块分析

数据库操作

代码实现

db.properties文件配置
driver=com.mysql.cj.jdbc.Driver name=root pass=123456 url=jdbc:mysql://localhost:3306/air?serverTimezone=UTC

导入jar包

结果展示

Java相关栏目本月热门文章

航班信息系统（JDBC）

模块分析

数据库操作

代码实现

db.properties文件配置 driver=com.mysql.cj.jdbc.Driver name=root pass=123456 url=jdbc:mysql://localhost:3306/air?serverTimezone=UTC

导入jar包

结果展示

Java相关栏目本月热门文章

db.properties文件配置
driver=com.mysql.cj.jdbc.Driver name=root pass=123456 url=jdbc:mysql://localhost:3306/air?serverTimezone=UTC