实验七 数据库应用

数据库中表的属性：姓名 学号 java成绩 实验课成绩 ，自己对应修改代码 改成自己的也可

实验目的：了解数据库系统、关系模型、客户-服务器模式等基本概念，掌握SQL的数据定义、数据操纵和数据查询等语句的语法；了解JDBC各种数据库驱动程序类型，熟悉JDBC提供的接口和类，掌握指定驱动类型、连接数据库、执行SQL语句、处理结果集等操作方法。
实验内容：

import java.sql.*;
import java.util.Scanner;

public class Final {
    /**
     * 变量定义
     */
    private static Connection con;
    private static Scanner scan;
    private static PreparedStatement pst;
    private final static String user = "root"; //自己的数据库名称
    private final static String password = ""; //自己的数据库密码
    private final static String url = "jdbc:mysql://localhost:3306/**"; // ** 是自己的数据库名字，也就是模式名字
    private static String order;


    public static void main(String[] args) {
        /**
         * 加载驱动类
         * 创建连接对象con
         * 创建输入流
         */
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");

            con = DriverManager.getConnection(url,user,password);

            scan = new Scanner(System.in);
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }

        /**
         * 人机交互，满足用户需求
         */
        int tag = 0;

        while (tag < 7) {
            menu();
            System.out.print("请输入您要进行的操作：");
            tag = scan.nextInt();
            switch (tag) {
                case 1 : {
                    insert();
                    break;
                }
                case 2 : {
                    modify();
                    break;
                }
                case 3 : {
                    delete();
                    break;
                }
                case 4 : {
                    System.out.print("请输入您要查询的学生的学号:");
                    String sno = scan.next();
                    if (query(sno)) {
                        System.out.println("查找成功！学生信息如下：");

                        try {
                            /**
                             * 输出学生信息
                             */
                            order = "select * from score where (sno = ?)";
                            pst = con.prepareStatement(order);
                            pst.setString(1,sno);
                            ResultSet rs = pst.executeQuery();
                            while (rs.next()) {
                                System.out.println("姓名：" + rs.getString(1) + "\t学号：" + rs.getString(2)
                                + "\tJava成绩：" + rs.getFloat(3) + "\tJava实验成绩：" + rs.getFloat(4)
                                + "\t总分：" + rs.getFloat(5));
                            }
                        } catch (SQLException throwables) {
                            throwables.printStackTrace();
                        }
                    }
                    else {
                        System.out.println("查找失败，该学生不在改系统中！");
                    }
                    break;
                }
                case 5 : {
                    scoreInfo();
                    break;
                }
                case 6 : {
                    sort();
                    break;
                }
                default:{
                    return;
                }
            }
        }

        /**
         * 关闭数据库连接对象
         */
        try {
            con.close();
            System.out.println("成功关闭数据库！");
        } catch (SQLException e) {
            System.out.println("数据库关闭异常！");
        }

    }

    public static void menu() {
        System.out.println("\t\t\t\t\t成绩表管理操作");
        System.out.println("\t\t\t\t\t1、插入学生成绩");
        System.out.println("\t\t\t\t\t2、修改学生成绩");
        System.out.println("\t\t\t\t\t3、删除学生成绩");
        System.out.println("\t\t\t\t\t4、查询学生成绩信息");
        System.out.println("\t\t\t\t\t5、统计学生分数信息");
        System.out.println("\t\t\t\t\t6、打印学生成绩单");
        System.out.println("\t\t\t\t\t7、退出");
    }

    public static boolean query(String sno){
        order = "select * from score where (sno = ?)";
        try {
            pst = con.prepareStatement(order);
            pst.setString(1,sno);
            //获取集合
            ResultSet rs = pst.executeQuery();
            if (rs.next()) {
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return false;
    }

    public static void insert() {
        String name, sno;
        Float j,js;

        System.out.println("请输入学生的姓名、学号、java成绩、实验课成绩：");
        name = scan.next();
        sno = scan.next();
        j = scan.nextFloat();
        js = scan.nextFloat();

        if (query(sno)) {
            System.out.println("学生已在表中！");
        }
        else {
            try {
                order = "insert into score  values(?, ?, ?, ?, ?)";
                pst = con.prepareStatement(order);
                pst.setString(1,name);
                pst.setString(2,sno);
                pst.setFloat(3,j);
                pst.setFloat(4,js);
                pst.setFloat(5,j + js);
                pst.executeUpdate();
                pst.close();
                System.out.println("插入成功！");
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    public static void modify() {
        String sno;
        System.out.print("请输入需要修改的学生的学号:");
        sno = scan.next();
        if (query(sno)) {
            Float java, javae;
            System.out.print("请输入修改后学生的java、实验课成绩:");
            java = scan.nextFloat();
            javae = scan.nextFloat();
            try {
                order = "update score set JavaScore = ? , ExperimentalScore = ?, TotalScore = ? where (sno = ?)";
                pst = con.prepareStatement(order);
                pst.setFloat(1,java);
                pst.setFloat(2,javae);
                pst.setFloat(3,java + javae);
                pst.setString(4,sno);
                pst.executeUpdate();
                pst.close();
                System.out.println("修改成功！");
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        else {
            System.out.println("该系统中查无此人！");
        }
    }

    public static void delete() {
        String sno;
        System.out.print("请输入需要修改的学生的学号:");
        sno = scan.next();
        if (query(sno)) {
            order = "delete from score where (sno = ? )";
            try {
                pst = con.prepareStatement(order);
                pst.setString(1,sno);
                pst.executeUpdate();
                pst.close();
                System.out.println("删除成功！");
            } catch (SQLException e) {
                e.printStackTrace();
            }

        } else {
            System.out.println("系统中查无此人！");
        }
    }

    public static void scoreInfo() {
        float maxNum = 0;
        order = "select max(JavaScore) from score";
        try {
            pst = con.prepareStatement(order);
            ResultSet rs = pst.executeQuery();
            if (rs.next()) {
                System.out.println("Java课程最高分：" + rs.getFloat(1));
                maxNum = rs.getFloat(1);
            }
            else {
                System.out.println("系统错误");
            }
            rs.close();
            pst.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        if (maxNum == 0) {
            return;
        }
        order = "select Name, Sno, TotalScore from score where (Javascore = ?)";
        try {
            pst = con.prepareStatement(order);
            pst.setFloat(1,maxNum);
            ResultSet rs = pst.executeQuery();
            while (rs.next()) {
                System.out.println("姓名:" + String.format("%-3s",rs.getString(1)) + "\t学号:" + rs.getString(2)
                + "\t总分:" + rs.getFloat(3));
            }
            pst.close();
            rs.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void sort() {
        order = "select * from score order by TotalScore desc, JavaScore desc, ExperimentalScore desc";
        try {
            pst = con.prepareStatement(order);
            ResultSet rs = pst.executeQuery();
            while (rs.next()) {
                System.out.println("姓名:" + String.format("%-3s",rs.getString(1)) + "\t学号:" + rs.getString(2)
                        + "\tJavaScore = " + rs.getFloat(3) + "\tExperimentalScore = " + rs.getFloat(4)
                        + "\t总分 = " + rs.getFloat(5));
            }
            pst.close();
            rs.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}