Java课程设计——学生成绩管理系统

1 需求分析

1.1 需求分析概述

需求分析是开发软件系统的重要环节，是系统开发的第一步和基础环节。通过需求分析充分认识系统的目标、系统的各个组成部分、各部分的任务职责、工作流程、工作中使用的各种数据及数据结构、各部门的业务关系和数据流程等， 为系统设计打下基础。

而在一些学校机构，随着学生数量的不断增加，学生的信息不断增多，人工管理信息的难度也越来越大。而且效率也是很低的。所以如何自动高效地管理信息是这些年来许多人所研究的。

随着这些年电脑计算机的速度质的提高，成本的下降，IT 互联网大众趋势的发展。我们使用电脑的高效率才处理数据信息成为可能。学生学籍管理系统的出现，正是管理人员与信息数据，计算机的进入互动时代的体现。友好的人机交互模式，清晰简明的图形界面，高效安全的操作使得我们对成千上万的信息的管理得心应手。

1.2 系统需求分析

实现数据的录入（添加）、删除、修改

按姓名或学号查询

按照成绩排序，按照成绩段统计汇总

2 设计思路

系统通过窗口显示并以菜单方式工作，；

能查询、修改和删除文件中考生信息；

能够显示全部的考生信息；

根据上述系统功能分析，按照结构化程序设计的要求，得到系统的功能结构图， 如图所示。

3、详细设计

3.1 登录界面 Login 类

通过选择教师或学生登录，从两张表查找各自的密码检查是否正确，从而进入不同的客户端。实现代码如下：

public class Login extends JFrame {Login father = this;JTextField user = null;JPasswordField password = null;JButton button1 = null;String flag = "teacher";Login() {this.setSize(500, 420);this.setLocationRelativeTo(null);this.setTitle("学生成绩管理系统");this.setResizable(false);init();this.setVisible(true);password.requestFocus();//让密码输入框获得焦点}public void login() {//进入主菜单事件String password_true = null; String where = " where T_ID = "; if (flag.equals("teacher")) {where = " where T_ID = ";} else {where = " where S_ID = ";}try {String password_sql = "select passcode from ssms." + flag + where + Integer.parseInt(user.getText()) + ";";Connection conn = Main.sendConnection();PreparedStatement stmt =conn.prepareStatement(password_sql);ResultSet result = stmt.executeQuery();result.next();password_true = result.getString("passcode");} catch (SQLException e) {e.printStackTrace();单界面 }char[] pswd = password.getPassword();if (String.valueOf(pswd).equals(password_true)) {//密码JOptionPane.showMessageDialog(father, "登陆成功！"); if (flag.equals("teacher")) {TeacherMenu teacherMenu = new TeacherMenu();//创建主菜teacherMenu.sendObject(father);//传递主窗口引用teacherMenu.sendID(user.getText());//传递输入的账号ID 用于数据库的操作等teacherMenu.init();} else {StudentMenu studentMenu = new StudentMenu();studentMenu.sendObject(father);studentMenu.sendID(user.getText());studentMenu.init();}father.setVisible(false);//进入主菜单界面时隐藏登录界面} else {JOptionPane.showMessageDialog(father, "账号或密码错误！", " 错误", JOptionPane.WARNING_MESSAGE);}}public void init() {setLayout(new GridLayout(2, 1, 0, 0));setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);JPanel panel1 = new JPanel() {//登录界面上半部分private static final long serialVersionUID = 1L;ImageIcon image = new ImageIcon("Image\\up.png");//登录界面上部图片publicvoidpaintComponent(Graphicsg){super.paintComponent(g);g.drawImage(image.getImage(), 0, 0, this.getWidth(),this.getHeight(), this);}};panel1.setLayout(null);JPanel head = new JPanel() {//头像private static final long serialVersionUID = 1L;ImageIcon icon = new ImageIcon("Image\\flower.jpg");public void paintComponent(Graphics g) {super.paintComponent(g);g.drawImage(icon.getImage(), 0, 0, this.getWidth(),this.getHeight(), this);}};head.setBounds(200, 70, 100, 100);panel1.add(head);JPanel panel2 = new JPanel(new GridLayout(2, 1, 0, 0));//登录界面下半部分add(panel1);add(panel2);JLabel label1 = new JLabel("账号：");JLabel label2 = new JLabel("密码：");user = new JTextField("10001", 15);//账号输入框password = new JPasswordField(15);//密码输入框password.addActionListener(e -> login());//密码框添加回车登录事件，进入主菜单界面JPanel panel3 = new JPanel(new GridLayout(2, 1, 0, 0));//放置用户名和密码及其输入框JPanel panel4 = new JPanel(new FlowLayout(FlowLayout.CENTER, 0, 16));//放置用户名及其输入框JPanel panel5 = new JPanel(new FlowLayout(FlowLayout.CENTER, 0, 16));//放置密码及其输入框panel3.add(panel4);panel3.add(panel5);panel4.add(label1);panel4.add(user);//添加用户名输入框panel5.add(label2); panel5.add(password);//添加密码输入框panel2.add(panel3);button1 = new JButton("登录");JButton button2 = new JButton("退出");button1.setFocusPainted(false);//不绘制焦点button2.setFocusPainted(false);try {//改变成 win10风格UIManager.setLookAndFeel(UIManager.getSystemLookAndFeelClassName());} catch (ClassNotFoundException | InstantiationException | IllegalAccessException | UnsupportedLookAndFeelException e) {e.printStackTrace();}button1.addActionListener(e -> login());button2.addActionListener(e -> System.exit(0));//lambda 表达式JPanel panel_choice = new JPanel();JRadioButton radioButton_1 = new JRadioButton("教师", true);// 默认选择教师登录JRadioButton radioButton_2 = new JRadioButton("学生");radioButton_1.setFocusPainted(false);radioButton_2.setFocusPainted(false);radioButton_1.addActionListener(e -> flag = "teacher");radioButton_2.addActionListener(e -> flag = "student");ButtonGroup group = new ButtonGroup();group.add(radioButton_1);group.add(radioButton_2);panel_choice.add(radioButton_1);panel_choice.add(radioButton_2);JPanel panel6 = new JPanel(new GridLayout(3, 1, 0, 0));panel2.add(panel6);panel6.add(panel_choice);JPanel panel_button = new JPanel(new FlowLayout(FlowLayout.CENTER, 30, 5));panel_button.add(button1);panel_button.add(button2);panel6.add(panel_button);panel6.add(new JPanel());}}

3.2 返回数据库连接的 Connection 的主类 Main 类

将连接数据库的操作封装在主类中，定义一个静态方法，该方法返回一个连接数据库的 Connection 类型的引用，以便随时方便连接数据库。

public class Main {static Connection sendConnection() {String driver = "com.mysql.cj.jdbc.Driver";//URL 指向要访问的数据库名String url ="jdbc:mysql://localhost:3306/world" />

3.3 返回查询结果 ReturnQueryResult 类

将数据库的查询操作封装成一个类，定义一个静态方法，通过传入不同的 SQL

语句，以字符串二维数组的方式返回查询结果。

class ReturnQueryResult {public static String[][] send(String sql) {String[][] result = null;Connection conn = null;Statement statement = null;try {conn = Main.sendConnection();statement = conn.createStatement();ResultSet rs = statement.executeQuery(sql);ResultSetMetaData rsmd = rs.getMetaData();int column = rsmd.getColumnCount();//获取列数rs.last();int row = rs.getRow();//获取行数rs.beforeFirst();result = new String[row][column];int count = 0;while (rs.next()) {for (int i = 1; i <= column; i++) {result[count][i - 1] = rs.getString(i);}count++;}} catch (SQLException e) {e.printStackTrace();}return result;}}

3.4 教师客户端 TeacherMenu 类

将教师的所有功能封装在一个类中。详细代码如下

public class TeacherMenu extends JFrame {TeacherMenu father = this;Login send = null;JPanelRight flag = null;String input_ID = null;JPanel panel_top = null;JPanelLeft panel_left = null;JPanelRight panel_right = null;JPanelLeft showmessage = null;static final CardLayout cl = new CardLayout();public TeacherMenu() {this.setLayout(new GridBagLayout());//主菜单界面为网格包布局管理 }this.setSize(1200, 800);this.setLocationRelativeTo(null);this.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);//获取登录界面对象的引用void sendObject(Login o) {send = o;}//获取登录时用户输入的账号void sendID(String ID) {input_ID = ID;}public void init() {//在主菜单界面添加 panel panel_top = new JPanel();JLabel label_title = new JLabel("欢迎使用学生成绩管理系统");;label_title.setFont(new Font("宋体", Font.ROMAN_BASELINE, panel_top.add(label_title); panel_top.setBackground(new Color(0xAFEEEE)); this.add(panel_top, new MyGridBagConstraints(0, 0, 2, 1). setFill(GridBagConstraints.BOTH).setIpad(200, 50).setWeight(100, 0)); panel_right = new JPanelRight(); this.add(panel_right, new MyGridBagConstraints(1, 1, 1, 1).setFill(GridBagConstraints.BOTH)); panel_left = new JPanelLeft(); this.add(panel_left, new MyGridBagConstraints(0, 1, 1, 1). setFill(GridBagConstraints.BOTH).setWeight(0,setIpad(100, 300));this.setVisible(true);}class JPanelLeft extends JPanel {//放置左侧按钮面板JPanelLeft dad = this;public JPanelLeft() {showmessage = this;this.setLayout(new GridBagLayout());init();}JPanel panel_head = new JPanel();JPanel panel_tail = new JPanel();JButton button_search = new JButton("主页");JButton button_add = new JButton("添加");JButton button_score = new JButton("成绩统计");JButton button_message = new JButton("个人信息");JButton button_quit = new JButton("退出系统");void init() {button_search.addActionListener(e -> cl.show(panel_right,"home"));"insert" ));button_add.addActionListener(e -> cl.show(panel_right, button_score.addActionListener(e -> {flag.init();cl.show(panel_right, "score");}); button_message.addActionListener(e -> cl.show(panel_right, "message"));add(panel_head, new MyGridBagConstraints(0, 0, 1,setInset(new Insets(5, 0, 5, 0)).setFill(GridBagConstraints.BOTH).setWeight(10,setIpad(1, 1));add(button_search, new MyGridBagConstraints(0, 2, 1,setInset(new Insets(5, 0, 5, 0)).setFill(GridBagConstraints.BOTH).setIpad(20,;add(button_add, new MyGridBagConstraints(0, 3, 1,setInset(new Insets(5, 0, 5, 0)).setFill(GridBagConstraints.BOTH).setIpad(20,;add(button_score, new MyGridBagConstraints(0, 4, 1,setInset(new Insets(5, 0, 5, 0)).setFill(GridBagConstraints.BOTH).setIpad(20,;add(button_message, new MyGridBagConstraints(0, 6, 1,setInset(new Insets(5, 0, 5, 0)).setFill(GridBagConstraints.BOTH).setIpad(20,;add(panel_tail, new MyGridBagConstraints(0, 7, 1,setInset(new Insets(5, 0, 5, 0)).setFill(GridBagConstraints.BOTH).setWeight(10,setIpad(1, 1));}}class JPanelRight extends JPanel {//放置右侧 主页面板，个人信息面板等JPanelRight parent = this;public JPanelRight() {flag = this;this.setBackground(Color.MAGENTA);this.setLayout(cl);//CardLayout init();}void init() {JPanelHome panel_home = new JPanelHome();JPanelInsert panel_insert = new JPanelInsert();JPanelScore panel_score = new JPanelScore();PanelMessage panel_message = new PanelMessage();add(panel_home, "home");add(panel_insert, "insert");add(panel_score, "score");add(panel_message, "message");}//添加学生信息面板类class JPanelInsert extends JPanelHome {JPanelInsert() {this.setLayout(new GridLayout(7, 2));}void init() {JPanel[] panels = new JPanel[7];for (int i = 0; i < panels.length; i++) {panels[i] = new JPanel();"电话"};}JLabel title = new JLabel("添加学生信息：");panels[0].setLayout(new FlowLayout(FlowLayout.LEFT));panels[0].add(title);JLabel[] jLabels = new JLabel[5];JTextField[] jTextFields = new JTextField[5];String[] label_text = {"学号", "姓名", "性别", "年龄",for (int i = 0; i  { Connection conn = Main.sendConnection(); try {String sql_student = "insert into ssms.student values (" + jTextFields[0].getText() + ", '" + jTextFields[1].getText() + "', '" + jTextFields[2].getText() + "', " + jTextFields[3].getText() + ", '" + jTextFields[4].getText() + "', '123456'); "; PreparedStatement pstmt = conn.prepareStatement(sql_student); pstmt.executeUpdate();for (int i = 1; i <= 4; i++) {//在 score 表添加默认成绩 0 分String sql_score = "insert into ssms.score values(" + jTextFields[0].getText() + ", 1000" + i + ", 0); ";pstmt = conn.prepareStatement(sql_score);pstmt.executeUpdate();}JOptionPane.showMessageDialog(this, "添加成功！","Succeed",JOptionPane.INFORMATION_MESSAGE);} catch (Exception ex) {ex.printStackTrace();JOptionPane.showMessageDialog(this, "格式有误，请重新输入！",JOptionPane.WARNING_MESSAGE);}"Error",});panels[6].add(button_confirm);for (int i = 0; i < panels.length; i++) {this.add(panels[i]);}}}//统计学生信息面板类class JPanelScore extends JPanelHome {JPanelShow panel_show = new JPanelShow();CardLayout layout = (CardLayout)panel_show.getLayout();JPanelScore() {this.setLayout(new BorderLayout());//this.removeAll();JLabel title = new JLabel("成绩统计:");title.setFont(new Font("楷体", Font.BOLD, 30));this.add(title,BorderLayout.NORTH);this.add(panel_show, BorderLayout.CENTER);JPanelButton panel_button = new JPanelButton();panel_button.setPreferredSize(new Dimension(0, 100));this.add(panel_button,BorderLayout.SOUTH);}, 40));class JPanelButton extends JPanel {JPanelButton() {this.setLayout(new FlowLayout(FlowLayout.CENTER,String[] button = {"总成绩", "Java", "数据库原理", "算法与数据结构", "C++"};JButton[] button_all = new JButton[5]; for(int i=0; ilayout.show(panel_show, "0"));button_all[1].addActionListener(e->layout.show(panel_show, "1"));button_all[2].addActionListener(e->layout.show(panel_show, "2"));button_all[3].addActionListener(e->layout.show(panel_show, "3"));button_all[4].addActionListener(e->layout.show(panel_show, "4"));}}class JPanelShow extends JPanel {JPanel[] panels = new JPanel[5];JPanelShow() {this.setLayout(new CardLayout());for(int i=0; i<panels.length; i++) {panels[i] = new JPanel(new BorderLayout());this.add(panels[i], i + "");}show_all();show_single(1, "10001");show_single(2, "10002");show_single(3, "10003");show_single(4, "10004");}void show_single(int no, String t_no) {String[] head = {"学号", "姓名", "成绩", "排名"};String sql = "select r.*, row_number() over(order by 'subject') as row_rank\n" + score.T_ID = " + t_no +"from(\n" +"select score.S_ID, student.name,\n" + "sum(score.grade) as 'subject'\n" + "from ssms.score, ssms.student\n" + "where score.S_ID = student.S_ID and" group by score.S_ID\n" + "order by score.grade desc) r";String[][]result = ReturnQueryResult.send(sql);MyTable table = new MyTable(result, head);JScrollPane jsp = new JScrollPane(table);panels[no].add(jsp, BorderLayout.CENTER);String[] head_1 = {"分数段", "优秀(90~100)", "良好(80~90)", "中等(70~80)","及格(60~70)", "不及格(0~60)"};String[][] result_1 = new String[1][6];result_1[0][0] = "人数";String sql_1 = "select count(case whenas '不及格'\n" +"count(case when score.grade < 60 then 1 end)"from ssms.score\n" +"where score.T_ID = " + t_no;String[][] resultSet =ReturnQueryResult.send(sql_1);for(int i = 1; i= 90 then 1 end) as '优秀',\n" + end) as '良好',\n" +end) as '中等',\n" +end) as '及格',\n" + 不及格'\n" ++else 0 end) as 'java',\n" +"count(case when ave = 80 then "count(case when ave = 70 then "count(case when ave = 60 then "count(case when ave < 60 then 1 end) as ' "from(select score.S_ID, student.name,\n" "sum(case when T_ID = 10001 then score.grade"sum(case when T_ID = 10002 then score.grade else 0 end) as '数据库',\n" +"sum(case when T_ID = 10003 then score.gradeelse 0 end) as '算法',\n" + else 0 end) as 'C++',\n" +ave,\n" +@r := 0) alias\n" +"sum(case when T_ID = 10004 then score.grade"sum(score.grade) as '总分',\n" + "format(sum(score.grade)/count(*), 2) as"format(@r := @r + 1, 0) as '排名' \n" + "from ssms.score, ssms.student, (select"where score.S_ID = student.S_ID\n" + "group by score.S_ID) t;";String[][] resultSet = ReturnQueryResult.send(sql_1);for(int i = 1; i<6; i++) {result_1[0][i] = resultSet[0][i-1];}MyTable table_1 = new MyTable(result_1, head_1);JScrollPane jsp_1 = new JScrollPane(table_1);jsp_1.setPreferredSize(new Dimension(0, 100));panels[0].add(jsp_1, BorderLayout.SOUTH);}}}//个人信息面板类class PanelMessage extends JPanel {//个人信息面板PanelMessage() {this.setLayout(new GridBagLayout());init();}void init() {JPanel panel_top = new JPanel(new GridLayout(10, 1));JLabel l_message = new JLabel("个人信息:");JLabel[] labels = new JLabel[8];String[] label_name = {"教工号:", "姓名:", "性别:", "年龄:", "电话:", "课程号:", "密码:", "课程名:"};String sql_message = "SELECT teacher.*, course.Cname FROM ssms.teacher, ssms.course where T_ID = " + input_ID + " and teacher.subject = course.Cno;";String[][] result = ReturnQueryResult.send(sql_message);for (int i = 0; i  new AlterDialog(father));//弹出修改个人信息对话框button_password.addActionListener(e -> new PasswordDialog(father));this.add(button_alter); this.add(button_password);}}class AlterDialog extends JDialog {//修改个人信息对话框AlterDialog(JFrame parents) {super(parents);this.setSize(350, 460);this.setLocationRelativeTo(parents);init();this.setVisible(true);}void init() {Container c = this.getContentPane();c.setLayout(new GridLayout(10, 1, 0, 15));JLabel label_age = new JLabel("请输入年龄:");this.add(label_age);JTextField text_age = new JTextField();this.add(text_age);JLabel label_phone = new JLabel("请输入电话:");this.add(label_phone);JTextField text_phone = new JTextField();this.add(text_phone);JButton bt_save = new JButton("保存");Connection conn = Main.sendConnection();String sql = "update ssms.teacher set teacher.age=?, teacher.phone=? where teacher.T_ID=?;";bt_save.addActionListener(e -> { PreparedStatement pstmt = null; try {pstmt = conn.prepareStatement(sql);pstmt.setInt(1, Integer.parseInt(text_age.getText()));pstmt.setString(2, text_phone.getText());pstmt.setInt(3, Integer.parseInt(input_ID));pstmt.executeUpdate();parent.init();cl.show(panel_right, "message");showmessage.button_message.requestFocus();JOptionPane.showMessageDialog(this, "修改成功！","Succeed",JOptionPane.INFORMATION_MESSAGE);ex) {有误，请重新输入！", } catch (SQLException | NumberFormatException ex.printStackTrace(); JOptionPane.showMessageDialog(this, "格式 "Error", JOptionPane.WARNING_MESSAGE);}});this.add(bt_save);this.setDefaultCloseOperation(JDialog.HIDE_ON_CLOSE);}}class PasswordDialog extends JDialog {//修改密码对话框PasswordDialog(JFrame parents) {super(parents);this.setSize(300, 260);this.setLocationRelativeTo(parents);init();this.setVisible(true);}void compare(String s1, String s2) throws Exception { if (!s1.equals(s2))throw new Exception("两次输入的密码不同！");}void init() {Container c = this.getContentPane();c.setLayout(new GridLayout(5, 1, 0, 15));JLabel label_1 = new JLabel("请输入你的新密码:");this.add(label_1);JTextField text_1 = new JTextField();this.add(text_1);JLabel label_2 = new JLabel("请再次输入你的密码:");this.add(label_2);JTextField text_2 = new JTextField();this.add(text_2);JButton bt_save = new JButton("保存");Connection conn = Main.sendConnection();String sql = "update ssms.teacher set teacher.passcode =? where teacher.T_ID=?;";bt_save.addActionListener(e -> { PreparedStatement pstmt = null; try {pstmt = conn.prepareStatement(sql);try {compare(text_1.getText(),text_2.getText());Integer.parseInt(input_ID));pstmt.setString(1, text_1.getText());pstmt.setInt(2, pstmt.executeUpdate(); JOptionPane.showMessageDialog(this, "修改成功！", "Succeed", JOptionPane.INFORMATION_MESSAGE);} catch (Exception ex) {JOptionPane.showMessageDialog(this, "

两次输入的密码不同！", "Error", JOptionPane.WARNING_MESSAGE);

}} catch (SQLException ex) {ex.printStackTrace();JOptionPane.showMessageDialog(this, "格式有误，请重新输入！", "Error", JOptionPane.WARNING_MESSAGE);}});this.add(bt_save);this.setDefaultCloseOperation(JDialog.HIDE_ON_CLOSE);}}}//主页面板类class JPanelHome extends JPanel {//主页面板JPanel panel_top = null;JPanelHome dd = this;String sign_id = "";DownPanel down = null;public JPanelHome() {this.setLayout(new BorderLayout());init();}, 30));, 30));void init() {panel_top = new JPanel(new GridLayout(1, 2));panel_top.setPreferredSize(new Dimension(0, 80));panel_top.setBackground(Color.pink);JPanel id = new JPanel(new FlowLayout(FlowLayout.CENTER, JPanel name = new JPanel(new FlowLayout(FlowLayout.LEFT,JTextField input_id = new JTextField(20); id.add(input_id);JButton button_id = new JButton("按学号查询"); id.add(button_id);JTextField input_name = new JTextField(20); name.add(input_name);JButton button_name = new JButton("按姓名查询"); name.add(button_name);panel_top.add(id); panel_top.add(name);add(panel_top, BorderLayout.NORTH);button_id.addActionListener(e -> {String sql = "SELECT student.*, course.Cname, score.grade, teacher.name FROM" +" ssms.student, ssms.course, ssms.score,ssms.teacher" ++" where student.S_ID = " + input_id.getText() " and student.S_ID = score.S_ID andscore.T_ID = teacher.T_ID and course.Cno = teacher.subject; ";try {search(sql);} catch (Exception e1) {e1.printStackTrace();}});button_name.addActionListener(e -> {String sql = "SELECT student.*, course.Cname, score.grade, teacher.name FROM" +" ssms.student, ssms.course, ssms.score,ssms.teacher" +input_name.getText() +" where student.name = '" +"' and student.S_ID = score.S_ID andscore.T_ID = teacher.T_ID and course.Cno = teacher.subject; ";try {search(sql);} catch (Exception e1) {e1.printStackTrace();}});}class DownPanel extends JPanel {//放置查询结果面板DownPanel() {this.setLayout(new GridLayout(3, 1));}void init(String sql) {String[][] result = null;try {result = ReturnQueryResult.send(sql);sign_id = result[0][0];} catch (Exception e) {dd.removeAll();dd.init();}String[][] result_1 = new String[1][5];try {for (int i = 0; i <= 4; i++) {result_1[0][i] = result[0][i];}} catch (Exception e) {e.printStackTrace();dd.removeAll();dd.init();}"电话"};String[] head_1 = {"学号", "姓名", "性别", "年龄", MyTable table_1 = new MyTable(result_1, head_1); JScrollPane sp_1 = new JScrollPane(table_1); String[][] result_2 = null;try {result_2 = new String[result.length][3];for (int i = 0; i < result_2.length; i++) {for (int j = 0; j  {JDialog alter = new JDialog(father); alter.setSize(new Dimension(400, 500)); alter.setLocationRelativeTo(father); Container c = alter.getContentPane(); c.setLayout(new GridLayout(10, 1, 0, 15)); JLabel label_age = new JLabel("请输入年龄:"); alter.add(label_age);JTextField text_age = new JTextField(); alter.add(text_age);JLabel label_phone = new JLabel("请输入电话:"); alter.add(label_phone);JTextField text_phone = new JTextField(); alter.add(text_phone);JButton bt_save = new JButton("保存"); Connection conn = Main.sendConnection();String sql_alter = "update ssms.student set student.age=?, student.phone=? where student.S_ID=?;";bt_save.addActionListener(l -> {PreparedStatement pstmt = null; try {pstmt = conn.prepareStatement(sql_alter);pstmt.setInt(1, Integer.parseInt(text_age.getText()));pstmt.setString(2,text_phone.getText());Integer.parseInt(sign_id));"修改成功！", "Succeed",pstmt.setInt(3, pstmt.executeUpdate();//修改信息后刷新界面,更新数据update_page(); JOptionPane.showMessageDialog(alter,JOptionPane.INFORMATION_MESSAGE);} catch (SQLException | NumberFormatException ex) {"格式有误，请重新输入！", JOptionPane.WARNING_MESSAGE);}ex.printStackTrace(); JOptionPane.showMessageDialog(alter,"Error",});alter.add(bt_save);alter.setDefaultCloseOperation(JDialog.HIDE_ON_CLOSE);alter.setVisible(true);});");JButton button_alter_score = new JButton("修改成绩button_alter_score.addActionListener(e -> {String sql_cname = "SELECT course.Cname FROMssms.course where course.Cno = " +"( select teacher.subject from ssms.teacher where teacher.T_ID = " + input_ID + " );";String[][] Cname = ReturnQueryResult.send(sql_cname);String flag = JOptionPane.showInputDialog(parent, "请输入" + Cname[0][0] + "成绩：","修改成绩",JOptionPane.PLAIN_MESSAGE);if (flag != null && flag != "") {String sql_write_score = "update ssms.score set score.grade = " + Integer.parseInt(flag)+ " where score.S_Id = " + sign_id+ " and score.T_ID = " + input_ID;Connection conn = Main.sendConnection();try {PreparedStatement pstmt = conn.prepareStatement(sql_write_score);pstmt.executeUpdate();update_page();JOptionPane.showMessageDialog(parent,"修改成功！",JOptionPane.INFORMATION_MESSAGE);"Succeed","修改失败！",} catch (SQLException e1) {e1.printStackTrace();JOptionPane.showMessageDialog(parent,"Error",JOptionPane.WARNING_MESSAGE);}}});

有信息

JButton button_delete = new JButton("彻底删除");button_delete.addActionListener(e -> {//删除学生所Object[] options = {"确认", "取消"};int flag = JOptionPane.showOptionDialog(parent,"确认删除该生所有信息吗？", "标题",JOptionPane.YES_NO_CANCEL_OPTION, JOptionPane.QUESTION_MESSAGE, null, options, options[0]);if (flag == JOptionPane.YES_OPTION) {Connection conn = Main.sendConnection();String sql_score = "delete from ssms.score where S_ID = " + sign_id;String sql_student = "delete fromssms.student where S_ID = " + sign_id;try {PreparedStatement pstmt = conn.prepareStatement(sql_score);pstmt.executeUpdate();pstmt =conn.prepareStatement(sql_student);pstmt.executeUpdate();update_page();"删除成功！",JOptionPane.showMessageDialog(parent,"Succeed",JOptionPane.INFORMATION_MESSAGE);} catch (SQLException ex) {ex.printStackTrace();JOptionPane.showMessageDialog(parent,"删除错误！",JOptionPane.WARNING_MESSAGE);}"Error",}});panel_bottom.add(button_alter_score);panel_bottom.add(button_delete);panel_bottom.add(button_alter);this.add(panel_bottom);}}void update_page() {//修改信息后刷新界面,更新数据String sql_updata = "SELECT student.*, course.Cname, score.grade, teacher.name FROM" +" ssms.student, ssms.course, ssms.score,ssms.teacher" +" where student.S_ID = " + sign_id + " andstudent.S_ID = score.S_ID " + "and score.T_ID = teacher.T_ID and course.Cno = teacher.subject; ";try {search(sql_updata);} catch (Exception e1) {e1.printStackTrace();dd.removeAll();dd.init();}}void search(String sql) {//按学号查询事件this.removeAll();this.init();down = new DownPanel();try {down.init(sql);} catch (NullPointerException e) {e.printStackTrace();this.removeAll();this.init();}add(down, BorderLayout.CENTER);cl.show(panel_right, "message");//先跳转到其他面板，再调回来，起到刷新的作用cl.show(panel_right, "home");}}}}

3.5 学生客户端类

将学生的所有功能封装在 StudentMenu 类中。详细代码如下：

public class StudentMenu extends JFrame {StudentMenu now = this;public StudentMenu() {this.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);this.setLayout(new BorderLayout());this.setSize(700, 600);this.setLocationRelativeTo(null);this.setResizable(false);}Login send = null;void sendObject(Login o) {send = o;}String input_ID = null;void sendID(String ID) {input_ID = ID;}public void init() {JPanelUp panel_up = new JPanelUp();JPanelBottom panel_bottom = new JPanelBottom();this.add(panel_up, BorderLayout.NORTH);this.add(panel_bottom, BorderLayout.CENTER);this.setVisible(true);}class JPanelUp extends JPanel {JPanelUp() {this.setPreferredSize(new Dimension(800, 480));this.setLayout(new GridLayout(1, 2, 0, 0));init();}public void init() {String sql_message = "select * from ssms.student where S_ID = " + input_ID + "; ";Connection conn = Main.sendConnection();PreparedStatement pstmt = null;ResultSet resultSet = null;String no = "", name = "", sex = "", age = "", phone = "";try {pstmt = conn.prepareStatement(sql_message);resultSet = pstmt.executeQuery();resultSet.next();no = resultSet.getString(1);name = resultSet.getString(2);sex = resultSet.getString(3);age = resultSet.getString(4);phone = resultSet.getString(5);} catch (SQLException e) {e.printStackTrace();}JLabel l_message = new JLabel("个人信息:");l_message.setFont(new Font("楷体", Font.BOLD, 20));JLabel l_no = new JLabel("学号:" + no);JLabel l_name = new JLabel("姓名:" + name);JLabel l_sex = new JLabel("性别:" + sex);JLabel l_age = new JLabel("年龄:" + age);JLabel l_phone = new JLabel("电话:" + phone);JPanel panel_left = new JPanel(new GridLayout(10, 1));panel_left.add(l_message);panel_left.add(l_no);panel_left.add(l_name);panel_left.add(l_sex);panel_left.add(l_age);panel_left.add(l_phone);this.add(panel_left);JLabel l_score = new JLabel("各科成绩:");l_score.setFont(new Font("楷体", Font.BOLD, 20));JPanel panel_right = new JPanel(new BorderLayout());String sql_score = "select Cname, grade, teacher.name from ssms.student," + student.S_ID = "+course.Cno; ";" ssms.teacher, ssms.course, ssms.score where+ input_ID + " and student.S_ID = score.S_ID and " "score.T_ID = teacher.T_ID and teacher.subject =String[][] result = ReturnQueryResult.send(sql_score);String[] head = {"科目", "分数", "老师"};MyTable t_score = new MyTable(result, head);JScrollPane sp = new JScrollPane(t_score);panel_right.add(l_score, BorderLayout.NORTH);panel_right.add(sp, BorderLayout.CENTER);this.add(panel_right);}}class JPanelBottom extends JPanel {JPanelBottom() {this.setLayout(new FlowLayout(FlowLayout.CENTER, 60, 10));init();}public void init() {JButton button_alter = new JButton("修改信息");JButton button_password = new JButton("修改密码");JButton button_quit = new JButton("退出系统");button_alter.setFocusPainted(false);button_password.setFocusPainted(false);button_quit.setFocusPainted(false);button_alter.setPreferredSize(new Dimension(100, 30));button_password.setPreferredSize(new Dimension(100, 30));button_quit.setPreferredSize(new Dimension(100, 30));button_alter.addActionListener(e -> new AlterDialog(now));//弹出修改个人信息对话框button_password.addActionListener(e -> newPasswordDialog(now));button_quit.addActionListener(e -> System.exit(0));this.add(button_alter);this.add(button_password);this.add(button_quit);}}class PasswordDialog extends JDialog {PasswordDialog(JFrame parents) {super(parents);this.setSize(300, 260);this.setLocationRelativeTo(parents);init();this.setVisible(true);}void compare(String s1, String s2) throws Exception { if (!s1.equals(s2))throw new Exception("两次输入的密码不同！");}void init() {Container c = this.getContentPane();c.setLayout(new GridLayout(5, 1, 0, 15));JLabel label_1 = new JLabel("请输入你的新密码:");this.add(label_1);JTextField text_1 = new JTextField();this.add(text_1);JLabel label_2 = new JLabel("请再次输入你的密码:");this.add(label_2);JTextField text_2 = new JTextField();this.add(text_2);JButton bt_save = new JButton("保存");Connection conn = Main.sendConnection();String sql = "update ssms.student set student.passcode =? where student.S_ID=?;";bt_save.addActionListener(e -> { PreparedStatement pstmt = null; try {pstmt = conn.prepareStatement(sql);try {compare(text_1.getText(), text_2.getText());pstmt.setString(1, text_1.getText());pstmt.setInt(2, Integer.parseInt(input_ID));pstmt.executeUpdate();JOptionPane.showMessageDialog(this, "修改成功！","Succeed",JOptionPane.INFORMATION_MESSAGE);} catch (Exception ex) {JOptionPane.showMessageDialog(this, "两次输入的密码不同！", }"Error", JOptionPane.WARNING_MESSAGE);} catch (SQLException ex) {ex.printStackTrace();}});this.add(bt_save);this.setDefaultCloseOperation(JDialog.HIDE_ON_CLOSE);}}class AlterDialog extends JDialog {AlterDialog(JFrame parents) {super(parents);this.setSize(350, 460);this.setLocationRelativeTo(parents);init();this.setVisible(true);}void init() {Container c = this.getContentPane();c.setLayout(new GridLayout(10, 1, 0, 15));JLabel label_age = new JLabel("请输入年龄:");this.add(label_age);JTextField text_age = new JTextField();this.add(text_age);JLabel label_phone = new JLabel("请输入电话:");this.add(label_phone);JTextField text_phone = new JTextField();this.add(text_phone);JButton bt_save = new JButton("保存");Connection conn = Main.sendConnection();String sql = "update ssms.student set student.age=?, student.phone=? where student.S_ID=?;";bt_save.addActionListener(e -> { PreparedStatement pstmt = null; try {pstmt = conn.prepareStatement(sql);pstmt.setInt(1, Integer.parseInt(text_age.getText()));pstmt.setString(2, text_phone.getText());pstmt.setInt(3, Integer.parseInt(input_ID));pstmt.executeUpdate();now.init();JOptionPane.showMessageDialog(this, "修改成功！", "Succeed",JOptionPane.INFORMATION_MESSAGE);} catch (SQLException | NumberFormatException ex) {ex.printStackTrace();JOptionPane.showMessageDialog(this, "格式有误，请重新输入！", }});"Error", JOptionPane.WARNING_MESSAGE);this.add(bt_save);this.setDefaultCloseOperation(JDialog.HIDE_ON_CLOSE);}}}

4、运行调试与分析讨论

（1）登录

先选择以教师或学生身份登录，输入账号和密码进入相应的客户端。账号分别为老师的职工号和学生的学号。

5、数据结构基本算法实现

• 利用 SQL 查询语句 select from 返回查询结果

public static String[][] send(String sql) {String[][] result = null;Connection conn = null;Statement statement = null;try {conn = Main.sendConnection();statement = conn.createStatement();ResultSet rs = statement.executeQuery(sql);ResultSetMetaData rsmd = rs.getMetaData();int column = rsmd.getColumnCount();//获取列数rs.last();int row = rs.getRow();//获取行数rs.beforeFirst();result = new String[row][column];int count = 0;while (rs.next()) {for (int i = 1; i <= column; i++) {result[count][i - 1] = rs.getString(i);}count++;}} catch (SQLException e) {e.printStackTrace();}return result;}

• 用 row_number() over(order by )实现按照分数排名，sum()实现求学生成绩总分, case when then end 统计学生各科成绩。

void show_all() {//统计总成绩String[] head = {"学号", "姓名", "Java", "数据库原理", "算法与数据结构", "C++", "总分", "平均分", "排名"};String sql = "select r.*, row_number() over(order by '平均分') as row_rank\n" + "from(\n" + "select score.S_ID, student.name,\n" + "sum(case when T_ID = 10001 then score.grade else 0 end) as 'java',\n" + "sum(case when T_ID = 10002 then score.grade else 0 end) as '数据库',\n" + "sum(case when T_ID = 10003 then score.grade else 0 end) as '算法',\n" + "sum(case when T_ID = 10004 then score.grade else 0 end) as 'C++',\n" + "sum(score.grade) as '总分',\n" + "format(sum(score.grade)/count(*), 2) as '平均分'\n" + "from ssms.score, ssms.student\n" + "where score.S_ID = student.S_ID\n" + "group by score.S_ID\n" + "order by 平均分 desc) r";String[][]result = ReturnQueryResult.send(sql);MyTable table = new MyTable(result, head);JScrollPane jsp = new JScrollPane(table);panels[0].add(jsp, BorderLayout.CENTER);String[] head_1 = {"分数段", "优秀(90~100)", "良好(80~90)", "中等 ~80)","及格(60~70)", "不及格(0~60)"};String[][] result_1 = new String[1][6];result_1[0][0] = "人数";String sql_1 = "select count(case when ave >= 90 then 1 end) as ' 优秀',\n" +良好',\n" +中等',\n" +及格',\n" +"count(case when ave = 80 then 1 end) as ' "count(case when ave = 70 then 1 end) as ' "count(case when ave = 60 then 1 end) as '"count(case when ave < 60 then 1 end) as '不及格'\n" + "from(select score.S_ID, student.name,\n" +"sum(case when T_ID = 10001 then score.grade else 0 end) as'java',\n" +"sum(case when T_ID = 10002 then score.grade else 0 end) as '数据库',\n" +"sum(case when T_ID = 10003 then score.grade else 0 end) as '算法',\n" +"sum(case when T_ID = 10004 then score.grade else 0 end) as'C++',\n" +"sum(score.grade) as '总分',\n" + "format(sum(score.grade)/count(*), 2) as ave,\n" + "format(@r := @r + 1, 0) as '排名' \n" +"from ssms.score, ssms.student, (select @r := 0) alias\n" +"where score.S_ID = student.S_ID\n" + "group by score.S_ID) t;";String[][] resultSet = ReturnQueryResult.send(sql_1);for(int i = 1; i= 90 then 1 end) as '优秀',\n" + "count(case when score.grade = 80 then 1 end) as '良好',\n" + "count(case when score.grade = 70 then 1 end) as '中等',\n" + "count(case when score.grade = 60 then 1 end) as '及格',\n" + "count(case when score.grade < 60 then 1 end) as '不及格'\n" + "from ssms.score\n" + "where score.T_ID = " + t_no;String[][] resultSet = ReturnQueryResult.send(sql_1);for(int i = 1; i<6; i++) {result_1[0][i] = resultSet[0][i-1];}MyTable table_1 = new MyTable(result_1, head_1);JScrollPane jsp_1 = new JScrollPane(table_1);jsp_1.setPreferredSize(new Dimension(0, 100));panels[no].add(jsp_1, BorderLayout.SOUTH);}