JDBC酱紫简单啊！ JDBC练习(航班管理系统)

 题目：

---------------------------华丽的分割线-------------------------------------------------

效果：

。。。。下面的就不演示了反正差不多哈哈

---------------------------华丽的分割线-------------------------------------------------

 实现：

JDBC肯定要先创建数据库然后创建表，然后在IDEA导入connector.jar包然后连接数据库(博客里面有)，这里我用的是三个类和一个接口实现的：

数据库：

 AirInfoDAO接口预先写下那5个方法，BaseDao用来连接数据库，AirInfoDaoImp继承前面的接口和类，Main用来显示提示信息和启动

AirInfoDAO

(先写方法然后再实现)

import java.sql.SQLException;

public interface AirInfoDAO {
    /*
    * 查询所有航班，按日期和目的地查询航班，删除航班，更新航班*/
    //查询所有航班
    public void searchAll() throws SQLException;
    //按日期查询航班
    public void searchByDate() throws SQLException;
    //按目的地查询航班
    public void searchByDestination() throws SQLException;
    //删除航班
    public void delete() throws SQLException;
    //更新(修改)航班
    public void updatee() throws SQLException;
}

 BaseDao

(连接数据库需要的操作)

import java.sql.*;

public class BaseDao {
    public Connection getConn() {
        String driver = "com.mysql.cj.jdbc.Driver";
        String url = "jdbc:mysql://localhost:3306/airinfo?serverTimezone=UTC";
        String username = "root";
        String password = "aa5153222";
        Connection conn = null;
        try {
            Class.forName(driver); //classLoader,加载对应驱动
            conn = (Connection) DriverManager.getConnection(url, username, password);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }
}

AirInfoDaoImp

(接口方法的实现和数据库的连接)

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;

public class AirInfoDaoImpl extends BaseDao implements AirInfoDAO{
    //查询所有航班
    @Override
    public void searchAll() throws SQLException {
        //连接数据库
        Connection connection = getConn();
        String sql = "select * from airinfo";
        PreparedStatement pstmt = connection.prepareStatement(sql);
        //执行查询语句并返回结果集
        ResultSet resultSet = pstmt.executeQuery();
        System.out.println("编号\t航班\t目的地     航班日期");
        while(resultSet.next()){
            System.out.println(resultSet.getInt("flightId")+"\t"+resultSet.getString("number")+
                    "\t"+resultSet.getString("destination")+"     "+resultSet.getDate("flydate"));
        }

    }
    //根据日期查询航班
    @Override
    public void searchByDate() throws SQLException {
        //获取输入
        Scanner input = new Scanner(System.in);
        System.out.println("请输入航班日期(格式：20xx-xx-xx):");
        String inputDate = input.next();
        //建立sql连接
        Connection connection = getConn();
        //sql语句
        String sql = "select * from airinfo where flydate = ?";
        //预状态通道
        PreparedStatement pstmt = connection.prepareStatement(sql);
        pstmt.setString(1,inputDate);
        //执行sql语句
        ResultSet resultSet = pstmt.executeQuery();

        System.out.println("编号\t航班\t目的地     航班日期");
        while(resultSet.next()){
            System.out.println(resultSet.getInt("flightId")+"\t"+resultSet.getString("number")+
                    "\t"+resultSet.getString("destination")+"     "+resultSet.getDate("flydate"));
        }
    }
    //根据目的地查询航班
    @Override
    public void searchByDestination() throws SQLException {
        //获取输入
        Scanner input = new Scanner(System.in);
        System.out.println("请输入目的地:");
        String inputDestination = input.next();
        //建立sql连接
        Connection connection = getConn();
        //sql语句
        String sql = "select * from airinfo where destination = ?";
        //预状态通道
        PreparedStatement pstmt = connection.prepareStatement(sql);
        pstmt.setString(1,inputDestination);
        //执行sql语句
        ResultSet resultSet = pstmt.executeQuery();

        System.out.println("编号\t航班\t目的地     航班日期");
        while(resultSet.next()){
            System.out.println(resultSet.getInt("flightId")+"\t"+resultSet.getString("number")+
                    "\t"+resultSet.getString("destination")+"     "+resultSet.getDate("flydate"));
        }
    }
    //删除航班信息
    @Override
    public void delete() throws SQLException {
        //获取输入
        Scanner input = new Scanner(System.in);
        System.out.println("请输入航班的编号(ID)");
        int inputID = input.nextInt();
        //获取sql连接
        Connection connection = getConn();
        //默认为false
        connection.setAutoCommit(false);
        //sql语句
        String sql = "delete from airinfo where flightId = ?";
        //预状态通道
        PreparedStatement pstmt = connection.prepareStatement(sql);
        pstmt.setInt(1,inputID);

        pstmt.executeUpdate();
        connection.commit();
        connection.close();
    }
    //修改航班(根据ID更改航班信息)
    @Override
    public void updatee() throws SQLException {
        //获取输入
        Scanner input = new Scanner(System.in);
        System.out.print("请输入航班的编号(ID):");
        int inputID = input.nextInt();
        System.out.print("请输入新的航班号:");
        String inputNumber = input.next();
        System.out.print("请输入新的目的地:");
        String inputDestination = input.next();
        System.out.print("请输入新的起飞日期:");
        String inputDate = input.next();
        //获取连接
        Connection connection = getConn();
        //默认为false
        connection.setAutoCommit(false);
        String sql = "update airinfo set number = ?, destination = ?, flydate = ? where flightId = ?";
        //预状态通道
        PreparedStatement pstmt = connection.prepareStatement(sql);
        pstmt.setString(1,inputNumber);
        pstmt.setString(2,inputDestination);
        pstmt.setString(3,inputDate);
        pstmt.setInt(4,inputID);

        pstmt.executeUpdate();
        connection.commit();
        connection.close();
    }
}

Main

(这里我private了一个方法然后通过输入异常反复调用这个方法就可以形成就算输入错误也能重新输入了。)

import java.sql.SQLException;
import java.util.InputMismatchException;
import java.util.Scanner;

public class Main {
    public static void main(String[] args) throws SQLException {
        //获取用户输入
        Scanner input = new Scanner(System.in);

        System.out.println("----------欢迎使用航班信息管理系统----------");

        a:while(true){
            try{
                switch (systemInfo()){
                    case 1:
                        new AirInfoDaoImpl().searchAll();
                        break;
                    case 2:
                        new AirInfoDaoImpl().searchByDate();
                        break;
                    case 3:
                        new AirInfoDaoImpl().searchByDestination();
                        break;
                    case 4:
                        new AirInfoDaoImpl().delete();
                        break;
                    case 5:
                        new AirInfoDaoImpl().updatee();
                        break;
                    case 6:
                        System.out.println("退出成功。");
                        break a;
                }
            }catch (InputMismatchException e){
                System.out.println("输入有误，请重新输入");

            }
        }
    }
    private static int systemInfo(){
        Scanner input = new Scanner(System.in);
        System.out.println("请选择操作(1.列出所有航班，2.按起飞时间查询，3.按目的地查询，4.删除航班，5.更改航班，6退出系统)");
        int selectMode = input.nextInt();
        return selectMode;
    }
}

确实是酱紫！