国庆作业进阶版

package MySQL.src;

import com.mysql.cj.jdbc.Driver;

import javax.naming.Name;
import java.sql.*;
import java.util.Scanner;

public class zuoye {
    Connection connection;

    public static void main(String[] args) throws SQLException {
        zuoye z = new zuoye();
        z.getConnection();

        loop:while(true){
            System.out.println("--------欢迎来到学生管理系统数据库版----------");
            System.out.println("请输入你的选择");
            System.out.println("1.增加数据");
            System.out.println("2.修改数据");
            System.out.println("3.删除数据");
            System.out.println("4.查询数据");
            System.out.println("5.退出");
            Scanner sc = new Scanner(System.in);
            int a = sc.nextInt();
            switch (a){
                case 1 :
                    z.add();
                    break;
                case 2 :
                    z.update();
                    break ;
                case 3 :
                    z.delete();
                    break ;
                case 4 :
                    z.select();
                    break ;
                case 5 :
                    System.out.println("退出成功");
                    break loop;
            }
        }
    }


    public void getConnection() throws SQLException {

        //第一步:注册驱动

        DriverManager.registerDriver(new Driver());

        //驱动管理器，

        //第二步:获取连接
        connection = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/school_java","root","123456");
        //		技术	 什么数据库	主机		端口号	数据库名
    }

    //增加
    public void add() throws SQLException {
        System.out.println("要增加几位学生:");
        Scanner sc = new Scanner(System.in);
        int a = sc.nextInt();
        int i = 0;
        while (i < a){
            System.out.println("请输入id");
            int id = sc.nextInt();
            System.out.println("请输入姓名");
            String name = sc.next();
            System.out.println("请输入电话");
            int tel = sc.nextInt();
            System.out.println("请输入性别");
            String sex = sc.next();

            //第三步:获取statement对象
            PreparedStatement preparedStatement = connection.prepareStatement("insert into student values(?,?,?,?);");
            preparedStatement.setInt(1, id);
            preparedStatement.setString(2, name);
            preparedStatement.setInt(3, tel);
            preparedStatement.setString(4, sex);

            //第四步: 执行SQL语句
            preparedStatement.executeUpdate();
            System.out.println("添加成功");
            i++;
        }
    }

    //删除
    public void delete() throws SQLException {
        System.out.println("--------------删除界面------------------");
        System.out.println("1.根据id删除");
        System.out.println("2.根据姓名删除");
        System.out.println("3.根据电话删除");
        Scanner sc = new Scanner(System.in);
        int a = sc.nextInt();

        PreparedStatement preparedStatement = null;
        switch (a){
            case 1 :
                System.out.println("请输入id");
                int id = sc.nextInt();
                preparedStatement = connection.prepareStatement("delete from student where id = ? ;");
                preparedStatement.setInt(1, id);
                break;
            case 2 :
                System.out.println("请输入名字");
                String name = sc.next();
                preparedStatement = connection.prepareStatement("delete from student where name = ? ;");
                preparedStatement.setString(1, name);
                break;
            case 3:
                System.out.println("请输入电话");
                int tel = sc.nextInt();
                preparedStatement = connection.prepareStatement("delete from student where tel = ? ;");
                preparedStatement.setInt(1, tel);
                break;
        }
        preparedStatement .executeUpdate();
        System.out.println("删除完成");
    }

    //修改
    public void update() throws SQLException {
        System.out.println("-------欢迎来到修改界面--------");
        System.out.println("1.修改id");
        System.out.println("2.修改姓名");
        System.out.println("3.修改电话");
        System.out.println("4.修改性别");
        Scanner sc = new Scanner(System.in);
        int a = sc.nextInt();

        int id,tel;String name,sex;
        PreparedStatement preparedStatement = null;
        switch (a){
            case 2:
                System.out.println("请输入id");
                id = sc.nextInt();
                System.out.println("修改为：（姓名）");
                name = sc.next();
                preparedStatement = connection.prepareStatement("update student set name=? where id=?");
                preparedStatement.setString(1,name);
                preparedStatement.setInt(2,id);
                preparedStatement.executeUpdate();
                break;
            case 3:
                System.out.println("请输入id");
                id = sc.nextInt();
                System.out.println("修改为：（电话）");
                tel = sc.nextInt();
                preparedStatement = connection.prepareStatement("update student set tel=? where id=?");
                preparedStatement.setInt(1,tel);
                preparedStatement.setInt(2,id);
                preparedStatement.executeUpdate();
                break;
            case 4:
                System.out.println("请输入id");
                id = sc.nextInt();
                System.out.println("修改为：（性别）");
                sex = sc.next();
                preparedStatement = connection.prepareStatement("update student set sex=? where id=?");
                preparedStatement.setString(1,sex);
                preparedStatement.setInt(2,id);
                preparedStatement.executeUpdate();
                break;
        }
        if (a == 1){
            System.out.println("id无法修改");
        }else {
            System.out.println("修改成功");
        }
    }

    //查找
    public void select() throws SQLException {
        System.out.println("-----------欢迎来到查找界面---------");
        System.out.println("1.查找全部");
        System.out.println("2.id查找");
        System.out.println("3.姓名查找");
        System.out.println("4.电话查找");
        System.out.println("5.性别查找");
        Scanner sc = new Scanner(System.in);
        int a = sc.nextInt();

        PreparedStatement preparedStatement = null;
        ResultSet resultSet1 = null;
        switch (a){
            case 1:
                //第三步:获取statement对象
                preparedStatement = connection.prepareStatement("select * from student;");
                //可以传入sql语句

                break;
            case 2:
                System.out.println("请输入id");
                int id = sc.nextInt();
                preparedStatement = connection.prepareStatement("select * from student where id = ?;");
                preparedStatement.setInt(1,id);
                break;
            case 3:
                System.out.println("请输入姓名");
                String name = sc.next();
                preparedStatement = connection.prepareStatement("select * from student where name = ?;");
                preparedStatement.setString(1,name);
                break;
            case 4:
                System.out.println("请输入电话");
                int tel = sc.nextInt();
                preparedStatement = connection.prepareStatement("select * from student where tel = ?;");
                preparedStatement.setInt(1,tel);
                break;
            case 5:
                System.out.println("请输入性别");
                String sex = sc.next();
                preparedStatement = connection.prepareStatement("select * from student where sex = ?;");
                preparedStatement.setString(1,sex);
                break;
        }
        //第四步: 执行SQL语句返回结果集
        resultSet1 = preparedStatement.executeQuery();
        //结果集来储存结果							查询

        //第五步:遍历结果集
        while(resultSet1.next()) {
            System.out.print(resultSet1.getInt("id") + " ");
            System.out.print(resultSet1.getString("name") + " ");
            System.out.print(resultSet1.getInt("tel") + " ");
            System.out.print(resultSet1.getString("sex"));
            System.out.println();
        }

        //6.关闭
        resultSet1.close();
        preparedStatement.close();
    }

    public void close() throws SQLException {
        //第六步:关闭连接释放资源
        connection.close();
    }
}