devbox
小小林熬夜学编程
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

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

作者：小小林熬夜学编程 | 2024-03-01 02:04:35

赞

踩

利用java编程实现一个学生成绩管理系统,数据库自定。自行设计数据库结构,并实现以

1 需求分析

1.1 需求分析概述

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

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

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

1.2 系统需求分析

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

按姓名或学号查询

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

2 设计思路

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

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

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

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

f9bfc7354ff645e4d573e322845e3a63.png

3、详细设计

3.1 登录界面 Login 类

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

  1. public class Login extends JFrame {
  2.     Login father = this;
  3.     JTextField user = null;
  4.     JPasswordField password = null;
  5.     JButton button1 = null;
  6.     String flag = "teacher";
  7.     Login() {
  8.         this.setSize(500, 420);
  9.         this.setLocationRelativeTo(null);
  10.         this.setTitle("学生成绩管理系统");
  11.         this.setResizable(false);
  12.         init();
  13.         this.setVisible(true);
  14.         password.requestFocus();//让密码输入框获得焦点
  15.     }
  16.     public void login() {//进入主菜单事件String password_true = null; String where = " where T_ID = "; if (flag.equals("teacher")) {
  17.         where = " where T_ID = ";
  18.     } else {
  19.         where = " where S_ID = ";
  20.     }
  21.     try {
  22.         String password_sql = "select passcode from ssms." + flag + where + Integer.parseInt(user.getText()) + ";";
  23.         Connection conn = Main.sendConnection();
  24.         PreparedStatement stmt =
  25.             conn.prepareStatement(password_sql);
  26.         ResultSet result = stmt.executeQuery();
  27.         result.next();
  28.         password_true = result.getString("passcode");
  29.     } catch (SQLException e) {
  30.         e.printStackTrace();单界面
  31.          
  32. }
  33. char[] pswd = password.getPassword();
  34. if (String.valueOf(pswd).equals(password_true)) {//密码JOptionPane.showMessageDialog(father, "登陆成功！"); if (flag.equals("teacher")) {
  35.     TeacherMenu teacherMenu = new TeacherMenu();//创建主菜
  36.     teacherMenu.sendObject(father);//传递主窗口引用teacherMenu.sendID(user.getText());//传递输入的账号ID 用于数据库的操作等
  37.    teacherMenu.init();
  38. } else {
  39.     StudentMenu studentMenu = new StudentMenu();
  40.     studentMenu.sendObject(father);
  41.     studentMenu.sendID(user.getText());
  42.     studentMenu.init();
  43. }
  44. father.setVisible(false);//进入主菜单界面时隐藏登录界面
  45. } else {
  46.     JOptionPane.showMessageDialog(father, "账号或密码错误！", " 错误", JOptionPane.WARNING_MESSAGE);
  47. }
  48. }
  49. public void init() {
  50.     setLayout(new GridLayout(2, 1, 0, 0));
  51.     setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
  52.     JPanel panel1 = new JPanel() {//登录界面上半部分private static final long serialVersionUID = 1L;
  53.         ImageIcon image = new ImageIcon("Image\\up.png");//登录界面上部图片
  54.  
  55.  
  56.  public void paintComponent(Graphics g) {
  57.     super.paintComponent(g);
  58.     g.drawImage(image.getImage(), 0, 0, this.getWidth(),
  59.                 this.getHeight(), this);
  60. }
  61. };
  62. panel1.setLayout(null);
  63. JPanel head = new JPanel() {//头像
  64.     private static final long serialVersionUID = 1L;
  65.     ImageIcon icon = new ImageIcon("Image\\flower.jpg");
  66.     public void paintComponent(Graphics g) {
  67.         super.paintComponent(g);
  68.         g.drawImage(icon.getImage(), 0, 0, this.getWidth(),
  69.                     this.getHeight(), this);
  70.     }
  71. };
  72. head.setBounds(200, 70, 100, 100);
  73. panel1.add(head);
  74. JPanel panel2 = new JPanel(new GridLayout(2, 1, 0, 0));//登录界面下半部分
  75. add(panel1);
  76. add(panel2);
  77. JLabel label1 = new JLabel("账  号：");
  78. JLabel label2 = new JLabel("密  码：");
  79. user = new JTextField("10001", 15);//账号输入框password = new JPasswordField(15);//密码输入框
  80. password.addActionListener(e -> login());//密码框添加回车登录事件，进入主菜单界面
  81. JPanel panel3 = new JPanel(new GridLayout(2, 1, 0, 0));//放置用户名和密码及其输入框
  82. JPanel panel4 = new JPanel(new FlowLayout(FlowLayout.CENTER, 0, 16));//放置用户名及其输入框
  83. JPanel panel5 = new JPanel(new FlowLayout(FlowLayout.CENTER, 0, 16));//放置密码及其输入框
  84. panel3.add(panel4);
  85. panel3.add(panel5);
  86. panel4.add(label1);
  87. panel4.add(user);//添加用户名输入框panel5.add(label2); panel5.add(password);//添加密码输入框panel2.add(panel3);
  88. button1 = new JButton("登  录");
  89. JButton button2 = new JButton("退  出");
  90. button1.setFocusPainted(false);//不绘制焦点button2.setFocusPainted(false);
  91. try {//改变成 win10风格UIManager.setLookAndFeel(UIManager.getSystemLookAndFeelClassName());
  92. } catch (ClassNotFoundException | InstantiationException | IllegalAccessException
  93.              | UnsupportedLookAndFeelException e) {
  94.     e.printStackTrace();
  95. }
  96. button1.addActionListener(e -> login());
  97. button2.addActionListener(e -> System.exit(0));//lambda 表达式JPanel panel_choice = new JPanel();
  98. JRadioButton radioButton_1 = new JRadioButton("教师", true);// 默认选择教师登录
  99. JRadioButton radioButton_2 = new JRadioButton("学生");
  100. radioButton_1.setFocusPainted(false);
  101. radioButton_2.setFocusPainted(false);
  102. radioButton_1.addActionListener(e -> flag = "teacher");
  103. radioButton_2.addActionListener(e -> flag = "student");
  104. ButtonGroup group = new ButtonGroup();
  105. group.add(radioButton_1);
  106. group.add(radioButton_2);
  107. panel_choice.add(radioButton_1);
  108. panel_choice.add(radioButton_2);
  109. JPanel panel6 = new JPanel(new GridLayout(3, 1, 0, 0));
  110. panel2.add(panel6);
  111. panel6.add(panel_choice);
  112. JPanel panel_button = new JPanel(new FlowLayout(FlowLayout.CENTER, 30, 5));
  113. panel_button.add(button1);
  114. panel_button.add(button2);
  115. panel6.add(panel_button);
  116. panel6.add(new JPanel());
  117. }
  118. }

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

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

  1. public class Main {
  2.     static Connection sendConnection() {
  3.         String driver = "com.mysql.cj.jdbc.Driver";
  4. //URL 指向要访问的数据库名String url =
  5.         "jdbc:mysql://localhost:3306/world?serverTimezone=UTC";
  6. //MySQL 配置时的用户名String user = "root";
  7. //MySQL 配置时的密码
  8.         String password = "n3483226";
  9.         Connection conn = null;
  10.         try {
  11.             Class.forName(driver);
  12.             conn = DriverManager.getConnection(url, user, password);
  13.         } catch (ClassNotFoundException e) {
  14.             e.printStackTrace();
  15.         } catch (SQLException e) {
  16.             e.printStackTrace();
  17.         }
  18.         return conn;
  19.     }
  20.     public static void main(String[] args) {
  21.         new Login();
  22.     }
  23. }

3.3 返回查询结果 ReturnQueryResult 类

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

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

  1. class ReturnQueryResult {
  2. public static String[][] send(String sql) {
  3.     String[][] result = null;
  4.     Connection conn = null;
  5.     Statement statement = null;
  6.     try {
  7.         conn = Main.sendConnection();
  8.         statement = conn.createStatement();
  9.         ResultSet rs = statement.executeQuery(sql);
  10.         ResultSetMetaData rsmd = rs.getMetaData();
  11.         int column = rsmd.getColumnCount();//获取列数rs.last();
  12.         int row = rs.getRow();//获取行数rs.beforeFirst();
  13.         result = new String[row][column];
  14.         int count = 0;
  15.         while (rs.next()) {
  16.             for (int i = 1; i <= column; i++) {
  17.                 result[count][i - 1] = rs.getString(i);
  18.             }
  19.             count++;
  20.         }
  21.     } catch (SQLException e) {
  22.         e.printStackTrace();
  23.     }
  24.     return result;
  25. }
  26. }

3.4 教师客户端 TeacherMenu 类

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

  1. public class TeacherMenu extends JFrame {
  2. TeacherMenu father = this;
  3. Login send = null;
  4. JPanelRight flag = null;
  5. String input_ID = null;
  6. JPanel panel_top = null;
  7. JPanelLeft panel_left = null;
  8. JPanelRight panel_right = null;
  9. JPanelLeft showmessage = null;
  10. static final CardLayout cl = new CardLayout();
  11. public TeacherMenu() {
  12.     this.setLayout(new GridBagLayout());//主菜单界面为网格包布局管理
  13.      
  14. }
  15. this.setSize(1200, 800);
  16. this.setLocationRelativeTo(null);
  17. this.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
  18. //获取登录界面对象的引用void sendObject(Login o) {
  19. send = o;
  20. }
  21. //获取登录时用户输入的账号void sendID(String ID) {
  22. input_ID = ID;
  23. }
  24. public void init() {//在主菜单界面添加 panel panel_top = new JPanel();
  25.     JLabel label_title = new JLabel("欢迎使用学生成绩管理系统");
  26.     ;
  27.     label_title.setFont(new Font("宋体", Font.ROMAN_BASELINE, panel_top.add(label_title);
  28.                                  panel_top.setBackground(new Color(0xAFEEEE)); this.add(panel_top, new MyGridBagConstraints(0, 0, 2, 1).
  29.                                          setFill(GridBagConstraints.BOTH).setIpad(200, 50).setWeight(100, 0));
  30.                                  panel_right = new JPanelRight();
  31.                                  this.add(panel_right, new MyGridBagConstraints(1, 1, 1, 1).setFill(GridBagConstraints.BOTH));
  32.                                  panel_left = new JPanelLeft();
  33.                                  this.add(panel_left, new MyGridBagConstraints(0, 1, 1, 1). setFill(GridBagConstraints.BOTH).setWeight(0,
  34.                                           setIpad(100, 300));
  35.                                           this.setVisible(true);
  36. }
  37. class JPanelLeft extends JPanel {//放置左侧按钮面板JPanelLeft dad = this;
  38.     public JPanelLeft() {
  39.         showmessage = this;
  40.         this.setLayout(new GridBagLayout());
  41.         init();
  42.     }
  43.     JPanel panel_head = new JPanel();
  44.     JPanel panel_tail = new JPanel();
  45.     JButton button_search = new JButton("主页");
  46.     JButton button_add = new JButton("添加");
  47.     JButton button_score = new JButton("成绩统计");
  48.     JButton button_message = new JButton("个人信息");
  49.     JButton button_quit = new JButton("退出系统");
  50.     void init() {
  51.         button_search.addActionListener(e -> cl.show(panel_right,
  52.                                         "home"));
  53.         "insert"
  54.                                              ));
  55.         button_add.addActionListener(e -> cl.show(panel_right, button_score.addActionListener(e -> {
  56.             flag.init();
  57.             cl.show(panel_right, "score");
  58.         }); button_message.addActionListener(e -> cl.show(panel_right,
  59.                                              "message"));
  60.         add(panel_head, new MyGridBagConstraints(0, 0, 1,
  61.                 setInset(new Insets(5, 0, 5, 0)).
  62.                 setFill(GridBagConstraints.BOTH).setWeight(10,
  63.                         setIpad(1, 1));
  64.                 add(button_search, new MyGridBagConstraints(0, 2, 1,
  65.                         setInset(new Insets(5, 0, 5, 0)).
  66.                         setFill(GridBagConstraints.BOTH).setIpad(20,
  67.                                 ;
  68.                                 add(button_add, new MyGridBagConstraints(0, 3, 1,
  69.                                         setInset(new Insets(5, 0, 5, 0)).
  70.                                         setFill(GridBagConstraints.BOTH).setIpad(20,
  71.                                                 ;
  72.                                                 add(button_score, new MyGridBagConstraints(0, 4, 1,
  73.                                                         setInset(new Insets(5, 0, 5, 0)).
  74.                                                         setFill(GridBagConstraints.BOTH).setIpad(20,
  75.                                                                 ;
  76.                                                                 add(button_message, new MyGridBagConstraints(0, 6, 1,
  77.                                                                         setInset(new Insets(5, 0, 5, 0)).
  78.                                                                         setFill(GridBagConstraints.BOTH).setIpad(20,
  79.                                                                                 ;
  80.                                                                                 add(panel_tail, new MyGridBagConstraints(0, 7, 1,
  81.                                                                                         setInset(new Insets(5, 0, 5, 0)).
  82.                                                                                         setFill(GridBagConstraints.BOTH).setWeight(10,
  83.                                                                                                 setIpad(1, 1));
  84.     }
  85. }
  86. class JPanelRight extends JPanel {//放置右侧 主页面板，个人信息面板等
  87.     JPanelRight parent = this;
  88.     public JPanelRight() {
  89.         flag = this;
  90.         this.setBackground(Color.MAGENTA);
  91.         this.setLayout(cl);//CardLayout init();
  92.     }
  93.     void init() {
  94.         JPanelHome panel_home = new JPanelHome();
  95.         JPanelInsert panel_insert = new JPanelInsert();
  96.         JPanelScore panel_score = new JPanelScore();
  97.         PanelMessage panel_message = new PanelMessage();
  98.         add(panel_home, "home");
  99.         add(panel_insert, "insert");
  100.         add(panel_score, "score");
  101.         add(panel_message, "message");
  102.     }
  103. //添加学生信息面板类
  104.     class JPanelInsert extends JPanelHome {
  105.         JPanelInsert() {
  106.             this.setLayout(new GridLayout(7, 2));
  107.         }
  108.         void init() {
  109.             JPanel[] panels = new JPanel[7];
  110.             for (int i = 0; i < panels.length; i++) {
  111.                 panels[i] = new JPanel();
  112.                 "电话"
  113.             };
  114.         }
  115.         JLabel title = new JLabel("添加学生信息：");
  116.         panels[0].setLayout(new FlowLayout(FlowLayout.LEFT));
  117.         panels[0].add(title);
  118.         JLabel[] jLabels = new JLabel[5];
  119.         JTextField[] jTextFields = new JTextField[5];
  120.         String[] label_text = {"学号", "姓名", "性别", "年龄",
  121.         for (int i = 0; i < 5; i++) {
  122.         jLabels[i] = new JLabel(label_text[i]);
  123.             jTextFields[i] = new JTextField(20);
  124.             panels[i + 1].add(jLabels[i]);
  125.             panels[i + 1].add(jTextFields[i]);
  126.             ;
  127.         }
  128.         JButton button_confirm = new JButton("添加");
  129.         button_confirm.setPreferredSize(new Dimension(100,
  130.         button_confirm.addActionListener(e -> { Connection conn = Main.sendConnection(); try {
  131.             String sql_student = "insert into ssms.student
  132.                                  values (" + jTextFields[0].getText() + ", '"
  133.                                          + jTextFields[1].getText() + "', '" +
  134.                                          jTextFields[2].getText() + "', "
  135.                                          + jTextFields[3].getText() + ", '" + jTextFields[4].getText() + "', '123456'); ";
  136.                                          PreparedStatement pstmt = conn.prepareStatement(sql_student);
  137.                                          pstmt.executeUpdate();
  138.             for (int i = 1; i <= 4; i++) {//在 score 表添加默认成绩 0 分
  139.    String sql_score = "insert into ssms.score
  140.                    values(" + jTextFields[0].getText() + ", 1000" + i + ", 0); ";
  141. pstmt = conn.prepareStatement(sql_score);
  142. pstmt.executeUpdate();
  143. }
  144. JOptionPane.showMessageDialog(this, "添加成功！
  145.                               ",
  146.                               "Succeed",
  147.                               JOptionPane.INFORMATION_MESSAGE);
  148. } catch (Exception ex) {
  149.     ex.printStackTrace();
  150.     JOptionPane.showMessageDialog(this, "格式有误，请重新输入！",
  151.   JOptionPane.WARNING_MESSAGE);
  152. }
  153. "Error",
  154. });
  155. panels[6].add(button_confirm);
  156. for (int i = 0; i < panels.length; i++) {
  157. this.add(panels[i]);
  158. }
  159. }
  160. }
  161. //统计学生信息面板类
  162. class JPanelScore extends JPanelHome {
  163.     JPanelShow panel_show = new JPanelShow();
  164.     CardLayout layout = (CardLayout)panel_show.getLayout();
  165.     JPanelScore() {
  166.         this.setLayout(new BorderLayout());
  167. //  this.removeAll();
  168.         JLabel title = new JLabel("成绩统计:");
  169.         title.setFont(new Font("楷体", Font.BOLD, 30));
  170.         this.add(title,BorderLayout.NORTH);
  171.         this.add(panel_show, BorderLayout.CENTER);
  172.         JPanelButton panel_button = new JPanelButton();
  173.         panel_button.setPreferredSize(new Dimension(0, 100));
  174.         this.add(panel_button,BorderLayout.SOUTH);
  175.     }
  176.     , 40));
  177.     class JPanelButton extends JPanel {
  178.         JPanelButton() {
  179.             this.setLayout(new FlowLayout(FlowLayout.CENTER,
  180.                                           String[] button = {"总成绩", "Java", "数据库原理",
  181.                                                              "算法与数据结构", "C++"
  182.                                                             };
  183.             JButton[] button_all = new JButton[5]; for(int i=0; i<button.length; i++) {
  184.             button_all[i] = new JButton(button[i]);
  185.                 this.add(button_all[i]);
  186.             }
  187.             button_all[0].addActionListener(e->layout.show(panel_show, "0"));
  188.             button_all[1].addActionListener(e->layout.show(panel_show, "1"));
  189.             button_all[2].addActionListener(e->layout.show(panel_show, "2"));
  190.             button_all[3].addActionListener(e->layout.show(panel_show, "3"));
  191.             button_all[4].addActionListener(e->layout.show(panel_show, "4"));
  192.         }
  193.     }
  194.     class JPanelShow extends JPanel {
  195.         JPanel[] panels = new JPanel[5];
  196.         JPanelShow() {
  197.             this.setLayout(new CardLayout());
  198.             for(int i=0; i<panels.length; i++) {
  199.                 panels[i] = new JPanel(new BorderLayout());
  200.                 this.add(panels[i], i + "");
  201.             }
  202.             show_all();
  203.             show_single(1, "10001");
  204.             show_single(2, "10002");
  205.             show_single(3, "10003");
  206.             show_single(4, "10004");
  207.         }
  208.         void show_single(int no, String t_no) {
  209.             String[] head = {"学号", "姓名", "成绩", "排名"};
  210.             String sql = "select r.*, row_number() over(order by 'subject') as row_rank\n" +
  211.                          score.T_ID = " + t_no +
  212.                                       "from(\n" +
  213.                                       "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
  214.                                       " group by score.S_ID\n" + "order by score.grade desc) r";
  215.             String[][]  result = ReturnQueryResult.send(sql);
  216.             MyTable table = new MyTable(result, head);
  217.             JScrollPane jsp = new JScrollPane(table);
  218.             panels[no].add(jsp, BorderLayout.CENTER);
  219.             String[] head_1 = {"分数段", "优秀(90~100)", "良好(80~90)", "中等(70~80)","及格(60~70)", "不及格(0~60)"};
  220.             String[][] result_1 = new String[1][6];
  221.             result_1[0][0] = "人数";
  222.             String sql_1 = "select count(case when  
  223.             as '不及格'\n" +
  224. "count(case when score.grade < 60 then 1 end)
  225. "from ssms.score\n" +
  226. "where score.T_ID = " + t_no;
  227. String[][] resultSet =
  228.     ReturnQueryResult.send(sql_1);
  229. for(int i = 1; i<6; i++) {
  230.     result_1[0][i] = resultSet[0][i-1];
  231. }
  232. MyTable table_1 = new MyTable(result_1, head_1);
  233. JScrollPane jsp_1 = new JScrollPane(table_1);
  234. jsp_1.setPreferredSize(new Dimension(0, 100));
  235. panels[no].add(jsp_1, BorderLayout.SOUTH);
  236. }
  237. void show_all() {
  238.     String[] head = {"学号", "姓名", "Java", "数据库原理", "算法与数据结构", "C++", "总分", "平均分", "排名"};
  239. String sql = "select r.*, row_number() over(order by '平均分') as row_rank\n" +
  240.              else 0 end) as 'java',\n" +
  241.     "from(\n" +
  242.     "select score.S_ID, student.name,\n" + "sum(case when T_ID = 10001 then score.grade
  243.     "sum(case when T_ID = 10002 then score.grade
  244.     else 0 end) as '数据库',\n" +
  245.         "sum(case when T_ID = 10003 then score.grade
  246.         else 0 end) as '算法',\n" + else 0 end) as 'C++',\n" +平均分'\n" +
  247.       "sum(case when T_ID = 10004 then score.grade
  248. "sum(score.grade) as '总分',\n" + "format(sum(score.grade)/count(*), 2) as '
  249. "from ssms.score, ssms.student\n" + "where score.S_ID = student.S_ID\n" + "group by score.S_ID\n" +
  250. "order by 平均分 desc) r";
  251. String[][]  result = ReturnQueryResult.send(sql);
  252. MyTable table = new MyTable(result, head);
  253. JScrollPane jsp = new JScrollPane(table);
  254. panels[0].add(jsp, BorderLayout.CENTER);
  255. String[] head_1 = {"分数段", "优秀(90~100)", "良好(80~90)", "中等(70~80)","及格(60~70)", "不及格(0~60)"};
  256. String[][] result_1 = new String[1][6];
  257. result_1[0][0] = "人数";
  258. String sql_1 = "select count(case when ave >= 90 then 1 end) as '优秀',\n" +
  259.                end) as '良好',\n" +
  260. end) as '中等',\n" +
  261. end) as '及格',\n" + 不及格'\n" +
  262. +
  263.     else 0 end) as 'java',\n" +
  264.     "count(case when ave < 90 and ave >= 80 then "count(case when ave < 80 and ave >= 70 then "count(case when ave < 70 and 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
  265.                 "sum(case when T_ID = 10002 then score.grade else 0 end) as '数据库',\n" +
  266.                 "sum(case when T_ID = 10003 then score.grade
  267.                 else 0 end) as '算法',\n" + else 0 end) as 'C++',\n" +
  268.                     ave,\n" +
  269.                     @r :
  270.                     = 0) alias\n" +
  271.                     "sum(case when T_ID = 10004 then score.grade
  272.                     "sum(score.grade) as '总分',\n" + "format(sum(score.grade)/count(*), 2) as
  273.                     "format(@r := @r + 1, 0) as '排名' \n" + "from ssms.score, ssms.student, (select
  274.                     "where score.S_ID = student.S_ID\n" + "group by score.S_ID) t;";
  275.                     String[][] resultSet = ReturnQueryResult.send(sql_1);
  276.                     for(int i = 1; i<6; i++) {
  277.                 result_1[0][i] = resultSet[0][i-1];
  278.                 }
  279. MyTable table_1 = new MyTable(result_1, head_1);
  280. JScrollPane jsp_1 = new JScrollPane(table_1);
  281. jsp_1.setPreferredSize(new Dimension(0, 100));
  282. panels[0].add(jsp_1, BorderLayout.SOUTH);
  283. }
  284. }
  285. }
  286. //个人信息面板类
  287. class PanelMessage extends JPanel {//个人信息面板
  288.     PanelMessage() {
  289.         this.setLayout(new GridBagLayout());
  290.         init();
  291.     }
  292.     void init() {
  293.         JPanel panel_top = new JPanel(new GridLayout(10, 1));
  294.         JLabel l_message = new JLabel("个人信息:");
  295.         JLabel[] labels = new JLabel[8];
  296.         String[] label_name = {"教工号:", "姓名:", "性别:", "年龄:", "电话:", "课程号:", "密码:", "课程名:"};  
  297.         String sql_message = "SELECT teacher.*, course.Cname FROM ssms.teacher, ssms.course where T_ID = " + input_ID
  298.                      + " and teacher.subject = course.Cno;";
  299. String[][] result = ReturnQueryResult.send(sql_message);
  300. for (int i = 0; i < labels.length; i++) {
  301.     labels[i] = new JLabel("  " + label_name[i] + "  " + result[0][i]);
  302. }
  303. panel_top.add(l_message);
  304. for (JLabel l : labels) {
  305.     if (l == labels[6])//不显示密码continue;
  306.         panel_top.add(l);
  307. }
  308. TPanelBottom panel_bottom = new TPanelBottom();
  309. this.add(panel_top, new MyGridBagConstraints(0, 0, 10,
  310.          setFill(GridBagConstraints.BOTH)
  311.          .setWeight(10, 100));
  312.          this.add(panel_bottom, new MyGridBagConstraints(0, 16, 10, 1).setFill(GridBagConstraints.BOTH)
  313.                   .setWeight(10, 1));
  314. }
  315. class TPanelBottom extends JPanel {//放置两个按钮
  316.     , 30));
  317.     TPanelBottom() {
  318.         this.setLayout(new FlowLayout(FlowLayout.CENTER,
  319.                                       init();
  320.     }
  321.                    ;
  322.     public void init() {
  323.         JButton button_alter = new JButton("修改信息");
  324.         JButton button_password = new JButton("修改密码");
  325.         button_alter.setFocusPainted(false);
  326.         button_password.setFocusPainted(false);
  327.         button_alter.setPreferredSize(new Dimension(100,
  328.                                       button_password.setPreferredSize(new
  329.                                               Dimension(100, 30));
  330.                                       button_alter.addActionListener(e -> new AlterDialog(father));//弹出修改个人信息对话框
  331.                                       button_password.addActionListener(e -> new PasswordDialog(father));
  332.                                       this.add(button_alter); this.add(button_password);
  333.     }
  334. }
  335. class AlterDialog extends JDialog {//修改个人信息对话框
  336. AlterDialog(JFrame parents) {
  337.     super(parents);
  338.     this.setSize(350, 460);
  339.     this.setLocationRelativeTo(parents);
  340.     init();
  341.     this.setVisible(true);
  342. }
  343. void init() {
  344.     Container c = this.getContentPane();
  345.     c.setLayout(new GridLayout(10, 1, 0, 15));
  346.     JLabel label_age = new JLabel("请输入年龄:");
  347.     this.add(label_age);
  348.     JTextField text_age = new JTextField();
  349.     this.add(text_age);
  350.     JLabel label_phone = new JLabel("请输入电话:");
  351.     this.add(label_phone);
  352.     JTextField text_phone = new JTextField();
  353.     this.add(text_phone);
  354.     JButton bt_save = new JButton("保  存");
  355.     Connection conn = Main.sendConnection();
  356.     String sql = "update ssms.teacher set teacher.age=?, teacher.phone=? where teacher.T_ID=?;";
  357.     bt_save.addActionListener(e -> { PreparedStatement pstmt = null; try {
  358.         pstmt = conn.prepareStatement(sql);
  359.         pstmt.setInt(1,
  360.                      Integer.parseInt(text_age.getText()));
  361.         pstmt.setString(2, text_phone.getText());
  362.         pstmt.setInt(3,
  363.                      Integer.parseInt(input_ID));
  364.         pstmt.executeUpdate();
  365.         parent.init();
  366.         cl.show(panel_right, "message");
  367.         showmessage.button_message.requestFocus();
  368.         JOptionPane.showMessageDialog(this, "修改成功！",
  369.         "Succeed",
  370. JOptionPane.INFORMATION_MESSAGE);
  371. ex) {有误，请重新输入！",
  372.  
  373. } catch (SQLException | NumberFormatException
  374.              ex.printStackTrace();
  375.              JOptionPane.showMessageDialog(this, "格式
  376.                                            "Error",
  377.                                            JOptionPane.WARNING_MESSAGE);
  378. }
  379. });
  380. this.add(bt_save);
  381. this.setDefaultCloseOperation(JDialog.HIDE_ON_CLOSE);
  382. }
  383. }
  384. class PasswordDialog extends JDialog {//修改密码对话框PasswordDialog(JFrame parents) {
  385.     super(parents);
  386.     this.setSize(300, 260);
  387.     this.setLocationRelativeTo(parents);
  388.     init();
  389.     this.setVisible(true);
  390. }
  391. void compare(String s1, String s2) throws Exception { if (!s1.equals(s2))
  392.         throw new Exception("两次输入的密码不同！");
  393. }
  394. void init() {
  395.     Container c = this.getContentPane();
  396.     c.setLayout(new GridLayout(5, 1, 0, 15));
  397.     JLabel label_1 = new JLabel("请输入你的新密码:");
  398.     this.add(label_1);
  399.     JTextField text_1 = new JTextField();
  400.     this.add(text_1);
  401.     JLabel label_2 = new JLabel("请再次输入你的密码:");
  402.     this.add(label_2);
  403.     JTextField text_2 = new JTextField();
  404.     this.add(text_2);
  405.     JButton bt_save = new JButton("保  存");
  406.     Connection conn = Main.sendConnection();
  407.     String sql = "update ssms.teacher set teacher.passcode =? where teacher.T_ID=?;";
  408.     bt_save.addActionListener(e -> { PreparedStatement pstmt = null; try {
  409.         pstmt = conn.prepareStatement(sql);
  410.         try {
  411.             compare(text_1.getText(),
  412.                     text_2.getText());
  413.             Integer.parseInt(input_ID));
  414.             pstmt.setString(1, text_1.getText());
  415.             pstmt.setInt(2,
  416.                          pstmt.executeUpdate(); JOptionPane.showMessageDialog(this, "修改成功！", "Succeed", JOptionPane.INFORMATION_MESSAGE);
  417.       } catch (Exception ex) {    JOptionPane.showMessageDialog(this, "

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

  1. }
  2. } catch (SQLException ex) {
  3.     ex.printStackTrace();
  4.     JOptionPane.showMessageDialog(this, "格式有误，请重新输入！", "Error", JOptionPane.WARNING_MESSAGE);
  5. }
  6. });
  7. this.add(bt_save);
  8. this.setDefaultCloseOperation(JDialog.HIDE_ON_CLOSE);
  9. }
  10. }
  11. }
  12. //主页面板类
  13. class JPanelHome extends JPanel {//主页面板
  14.     JPanel panel_top = null;
  15.     JPanelHome dd = this;
  16.     String sign_id = "";
  17.     DownPanel down = null;
  18.     public JPanelHome() {
  19.         this.setLayout(new BorderLayout());
  20.         init();
  21.     }
  22.     , 30));
  23.     , 30));
  24.     void init() {
  25.         panel_top = new JPanel(new GridLayout(1, 2));
  26.         panel_top.setPreferredSize(new Dimension(0, 80));
  27.         panel_top.setBackground(Color.pink);
  28.         JPanel id = new JPanel(new FlowLayout(FlowLayout.CENTER, JPanel name = new JPanel(new FlowLayout(FlowLayout.LEFT,
  29.                 JTextField input_id = new JTextField(20); id.add(input_id);
  30.                 JButton button_id = new JButton("按学号查询"); id.add(button_id);
  31.                 JTextField input_name = new JTextField(20); name.add(input_name);
  32.                 JButton button_name = new JButton("按姓名查询"); name.add(button_name);
  33.                 panel_top.add(id); panel_top.add(name);
  34.                 add(panel_top, BorderLayout.NORTH);
  35.         button_id.addActionListener(e -> {
  36.             String sql = "SELECT student.*, course.Cname, score.grade, teacher.name FROM" +
  37.             " ssms.student, ssms.course, ssms.score,
  38.             ssms.teacher" +
  39.             +
  40.             " where student.S_ID = " + input_id.getText() " and student.S_ID = score.S_ID and
  41.             score.T_ID = teacher.T_ID and course.Cno = teacher.subject; ";
  42.             try {
  43.                 search(sql);
  44.             } catch (Exception e1) {
  45.                 e1.printStackTrace();
  46.             }
  47.         });
  48.         button_name.addActionListener(e -> {
  49.             String sql = "SELECT student.*, course.Cname, score.grade, teacher.name FROM" +
  50.             " ssms.student, ssms.course, ssms.score,
  51.             ssms.teacher" +
  52.             input_name.getText() +
  53.             " where student.name = '" +
  54.             "' and student.S_ID = score.S_ID and
  55.             score.T_ID = teacher.T_ID and course.Cno = teacher.subject; ";
  56.             try {
  57.                 search(sql);
  58.             } catch (Exception e1) {
  59.                 e1.printStackTrace();
  60.             }
  61.         });
  62.     }
  63.     class DownPanel extends JPanel {//放置查询结果面板
  64.         DownPanel() {
  65.             this.setLayout(new GridLayout(3, 1));
  66.         }
  67.         void init(String sql) {
  68.             String[][] result = null;
  69.             try {
  70.                 result = ReturnQueryResult.send(sql);
  71.                 sign_id = result[0][0];
  72.             } catch (Exception e) {
  73.                 dd.removeAll();
  74.                 dd.init();
  75.             }
  76.             String[][] result_1 = new String[1][5];
  77.             try {
  78.                 for (int i = 0; i <= 4; i++) {
  79.                     result_1[0][i] = result[0][i];
  80.                 }
  81.             } catch (Exception e) {
  82.                 e.printStackTrace();
  83.                 dd.removeAll();
  84.                 dd.init();
  85.             }
  86.             "电话"
  87.         };
  88.         String[] head_1 = {"学号", "姓名", "性别", "年龄",
  89.                            MyTable table_1 = new MyTable(result_1, head_1);
  90.                            JScrollPane sp_1 = new JScrollPane(table_1);
  91.                            String[][] result_2 = null;
  92.         try {
  93.             result_2 = new String[result.length][3];
  94.             for (int i = 0; i < result_2.length; i++) {
  95.                 for (int j = 0; j < result_2[i].length; j++)
  96.                 {
  97.                     result_2[i][j] = result[i][j + 6];
  98.                 }
  99.             }
  100.         } catch (Exception e) {
  101.             e.printStackTrace();
  102.             result_2 = new String[0][0];
  103.         }
  104.         String[] head_2 = {"科目", "分数", "老师"};
  105.                           MyTable table_2 = new MyTable(result_2, head_2);
  106.                           JScrollPane sp_2 = new JScrollPane(table_2);
  107.                           this.add(sp_1);
  108.                           this.add(sp_2);
  109.                           JPanel panel_bottom = new JPanel(new FlowLayout(FlowLayout.CENTER, 30, 10));
  110.                           JButton button_alter = new JButton("修改信息");
  111.         button_alter.addActionListener(e -> {
  112.             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);
  113.             JTextField text_age = new JTextField(); alter.add(text_age);
  114.             JLabel label_phone = new JLabel("请输入电话:"); alter.add(label_phone);
  115.             JTextField text_phone = new JTextField(); alter.add(text_phone);
  116.             JButton bt_save = new JButton("保  存"); Connection conn = Main.sendConnection();
  117.             String sql_alter = "update ssms.student set student.age=?, student.phone=? where student.S_ID=?;";
  118.             bt_save.addActionListener(l -> {
  119.                 PreparedStatement pstmt = null; try {
  120.                     pstmt = conn.prepareStatement(sql_alter);
  121.                     pstmt.setInt(1, Integer.parseInt(text_age.getText()));
  122.                     pstmt.setString(2,
  123.                                     text_phone.getText());
  124.                     Integer.parseInt(sign_id));
  125.                     "修改成功！"
  126.                     , "Succeed",
  127.                     pstmt.setInt(3, pstmt.executeUpdate();
  128. //修改信息后刷新界面,更新数据update_page(); JOptionPane.showMessageDialog(alter,
  129.                     JOptionPane.INFORMATION_MESSAGE);
  130.                 } catch (SQLException |
  131.                              NumberFormatException ex) {
  132.                     "格式有误，请重新输入！", JOptionPane.WARNING_MESSAGE);
  133.                 }
  134.                 ex.printStackTrace(); JOptionPane.showMessageDialog(alter,
  135.                         "Error",
  136.             });
  137.             alter.add(bt_save);
  138.             alter.setDefaultCloseOperation(JDialog.HIDE_ON_CLOSE);
  139.             alter.setVisible(true);
  140.         });
  141.         ");
  142.         JButton button_alter_score = new JButton("修改成绩
  143.         button_alter_score.addActionListener(e -> {
  144.             String sql_cname = "SELECT course.Cname FROM
  145.             ssms.course where course.Cno = " +
  146.             "( select teacher.subject from ssms.teacher where teacher.T_ID = " + input_ID + " );";
  147.             String[][] Cname = ReturnQueryResult.send(sql_cname);
  148.             String flag = JOptionPane.showInputDialog(parent, "请输入" + Cname[0][0] + "成绩：",
  149.                     "修改成绩",
  150.                     JOptionPane.PLAIN_MESSAGE);
  151.             if (flag != null && flag != "") {
  152.                 String sql_write_score = "update ssms.score set score.grade = " + Integer.parseInt(flag)
  153.                 + " where score.S_Id = " + sign_id
  154.                 + " and score.T_ID = " + input_ID;
  155.                 Connection conn = Main.sendConnection();
  156.                 try {
  157.                     PreparedStatement pstmt = conn.prepareStatement(sql_write_score);
  158.                     pstmt.executeUpdate();
  159.                     update_page();
  160.                     JOptionPane.showMessageDialog(parent,
  161.                                                   "修改成功！",
  162.                                                   JOptionPane.INFORMATION_MESSAGE);
  163.                     "Succeed"
  164.                     ,
  165.                     "修改失败！",
  166.                 } catch (SQLException e1) {
  167.                     e1.printStackTrace();
  168.                     JOptionPane.showMessageDialog(parent,
  169.                                                   "Error",
  170.                                                   JOptionPane.WARNING_MESSAGE);
  171.                 }
  172.             }
  173.         });

有信息

  1. JButton button_delete = new JButton("彻底删除");
  2. button_delete.addActionListener(e -> {//删除学生所
  3.     Object[] options = {"确认", "取消"};
  4.     int flag = JOptionPane.showOptionDialog(parent,
  5.                                             "确认删除该生所有信息吗？", "标题",
  6.                                             JOptionPane.YES_NO_CANCEL_OPTION, JOptionPane.QUESTION_MESSAGE, null, options, options[0]);
  7.     if (flag == JOptionPane.YES_OPTION) {
  8.         Connection conn = Main.sendConnection();
  9.         String sql_score = "delete from ssms.score
  10.                            where S_ID = " + sign_id;
  11.                                         String sql_student = "delete from
  12.                                                 ssms.student where S_ID = " + sign_id;
  13.         try {
  14.             PreparedStatement pstmt = conn.prepareStatement(sql_score);
  15.             pstmt.executeUpdate();
  16.             pstmt =
  17.                 conn.prepareStatement(sql_student);
  18.             pstmt.executeUpdate();
  19.             update_page();
  20.             "删除成功！"
  21.             ,
  22.             JOptionPane.showMessageDialog(parent,
  23.                                           "Succeed",
  24.                                           JOptionPane.INFORMATION_MESSAGE);
  25.         } catch (SQLException ex) {
  26.             ex.printStackTrace();
  27.             JOptionPane.showMessageDialog(parent,
  28.                                           "删除错误！",
  29.                                           JOptionPane.WARNING_MESSAGE);
  30.         }
  31.         "Error",
  32.     }
  33. });
  34. panel_bottom.add(button_alter_score);
  35. panel_bottom.add(button_delete);
  36. panel_bottom.add(button_alter);
  37. this.add(panel_bottom);
  38. }
  39. }
  40. void update_page() {
  41. //修改信息后刷新界面,更新数据
  42.     String sql_updata = "SELECT student.*, course.Cname, score.grade, teacher.name FROM" +
  43.                         " ssms.student, ssms.course, ssms.score,
  44.                         ssms.teacher" +
  45.                         " where student.S_ID = " + sign_id + " and
  46.                         student.S_ID = score.S_ID " +
  47.                                        "and score.T_ID = teacher.T_ID and course.Cno =
  48.                                        teacher.subject; ";
  49.     try {
  50.         search(sql_updata);
  51.     } catch (Exception e1) {
  52.         e1.printStackTrace();
  53.         dd.removeAll();
  54.         dd.init();
  55.     }
  56. }
  57. void search(String sql) {//按学号查询事件this.removeAll();
  58.     this.init();
  59.     down = new DownPanel();
  60.     try {
  61.         down.init(sql);
  62.     } catch (NullPointerException e) {
  63.         e.printStackTrace();
  64.         this.removeAll();
  65.         this.init();
  66.     }
  67.     add(down, BorderLayout.CENTER);
  68.     cl.show(panel_right, "message");//先跳转到其他面板，再调回来，起到刷新的作用
  69.     cl.show(panel_right, "home");
  70. }
  71. }
  72. }
  73. }

3.5 学生客户端类

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

  1. public class StudentMenu extends JFrame {
  2. StudentMenu now = this;
  3. public StudentMenu() {
  4.     this.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
  5.     this.setLayout(new BorderLayout());
  6.     this.setSize(700, 600);
  7.     this.setLocationRelativeTo(null);
  8.     this.setResizable(false);
  9. }
  10. Login send = null;
  11. void sendObject(Login o) {
  12.     send = o;
  13. }
  14. String input_ID = null;
  15. void sendID(String ID) {
  16.     input_ID = ID;
  17. }
  18. public void init() {
  19.     JPanelUp panel_up = new JPanelUp();
  20.     JPanelBottom panel_bottom = new JPanelBottom();
  21.     this.add(panel_up, BorderLayout.NORTH);
  22.     this.add(panel_bottom, BorderLayout.CENTER);
  23.     this.setVisible(true);
  24. }
  25. class JPanelUp extends JPanel {
  26.     JPanelUp() {
  27.         this.setPreferredSize(new Dimension(800, 480));
  28.         this.setLayout(new GridLayout(1, 2, 0, 0));
  29.         init();
  30.     }
  31.     public void init() {
  32.         String sql_message = "select * from ssms.student where S_ID
  33.                              = " + input_ID + "; ";
  34.         Connection conn = Main.sendConnection();
  35.         PreparedStatement pstmt = null;
  36.         ResultSet resultSet = null;
  37.         String no = "", name = "", sex = "", age = "", phone = "";
  38.         try {
  39.             pstmt = conn.prepareStatement(sql_message);
  40.             resultSet = pstmt.executeQuery();
  41.             resultSet.next();
  42.             no = resultSet.getString(1);
  43.             name = resultSet.getString(2);
  44.             sex = resultSet.getString(3);
  45.             age = resultSet.getString(4);
  46.             phone = resultSet.getString(5);
  47.         } catch (SQLException e) {
  48.             e.printStackTrace();
  49.         }
  50.         JLabel l_message = new JLabel("个人信息:");
  51.         l_message.setFont(new Font("楷体", Font.BOLD, 20));
  52.         JLabel l_no = new JLabel("学  号:    " + no);
  53.         JLabel l_name = new JLabel("姓      名:      " + name);
  54.         JLabel l_sex = new JLabel("性    别:    " + sex);
  55.         JLabel l_age = new JLabel("年    龄:    " + age);
  56.         JLabel l_phone = new JLabel("电      话:        " + phone);
  57.         JPanel panel_left = new JPanel(new GridLayout(10, 1));
  58.         panel_left.add(l_message);
  59.         panel_left.add(l_no);
  60.         panel_left.add(l_name);
  61.         panel_left.add(l_sex);
  62.         panel_left.add(l_age);
  63.         panel_left.add(l_phone);
  64.         this.add(panel_left);
  65.         JLabel l_score = new JLabel("各科成绩:");
  66.         l_score.setFont(new Font("楷体", Font.BOLD, 20));
  67.         JPanel panel_right = new JPanel(new BorderLayout());
  68.         String sql_score = "select Cname, grade, teacher.name from ssms.student," +
  69.                            student.S_ID = "
  70.                                           +
  71.                                           course.Cno; ";
  72.         " ssms.teacher, ssms.course, ssms.score where
  73.         + input_ID + " and student.S_ID = score.S_ID and " "score.T_ID = teacher.T_ID and teacher.subject =
  74.             String[][] result = ReturnQueryResult.send(sql_score);
  75.         String[] head = {"科目", "分数", "老师"};
  76.         MyTable t_score = new MyTable(result, head);
  77.         JScrollPane sp = new JScrollPane(t_score);
  78.         panel_right.add(l_score, BorderLayout.NORTH);
  79.         panel_right.add(sp, BorderLayout.CENTER);
  80.         this.add(panel_right);
  81.     }
  82. }
  83. class JPanelBottom extends JPanel {
  84.     JPanelBottom() {
  85.         this.setLayout(new FlowLayout(FlowLayout.CENTER, 60, 10));
  86.         init();
  87.     }
  88.     public void init() {
  89.         JButton button_alter = new JButton("修改信息");
  90.         JButton button_password = new JButton("修改密码");
  91.         JButton button_quit = new JButton("退出系统");
  92.         button_alter.setFocusPainted(false);
  93.         button_password.setFocusPainted(false);
  94.         button_quit.setFocusPainted(false);
  95.         button_alter.setPreferredSize(new Dimension(100, 30));
  96.         button_password.setPreferredSize(new Dimension(100, 30));
  97.         button_quit.setPreferredSize(new Dimension(100, 30));
  98.         button_alter.addActionListener(e -> new
  99.                                        AlterDialog(now));//弹出修改个人信息对话框button_password.addActionListener(e -> new
  100.         PasswordDialog(now));
  101.         button_quit.addActionListener(e -> System.exit(0));
  102.         this.add(button_alter);
  103.         this.add(button_password);
  104.         this.add(button_quit);
  105.     }
  106. }
  107. class PasswordDialog extends JDialog {
  108.     PasswordDialog(JFrame parents) {
  109.         super(parents);
  110.         this.setSize(300, 260);
  111.         this.setLocationRelativeTo(parents);
  112.         init();
  113.         this.setVisible(true);
  114.     }
  115.     void compare(String s1, String s2) throws Exception { if (!s1.equals(s2))
  116.             throw new Exception("两次输入的密码不同！");
  117.     }
  118.     void init() {
  119.         Container c = this.getContentPane();
  120.         c.setLayout(new GridLayout(5, 1, 0, 15));
  121.         JLabel label_1 = new JLabel("请输入你的新密码:");
  122.         this.add(label_1);
  123.         JTextField text_1 = new JTextField();
  124.         this.add(text_1);
  125.         JLabel label_2 = new JLabel("请再次输入你的密码:");
  126.         this.add(label_2);
  127.         JTextField text_2 = new JTextField();
  128.         this.add(text_2);
  129.         JButton bt_save = new JButton("保  存");
  130.         Connection conn = Main.sendConnection();
  131.         String sql = "update ssms.student set student.passcode =? where student.S_ID=?;";
  132.         bt_save.addActionListener(e -> { PreparedStatement pstmt = null; try {
  133.             pstmt = conn.prepareStatement(sql);
  134.             try {
  135.                 compare(text_1.getText(), text_2.getText());
  136.                 pstmt.setString(1, text_1.getText());
  137.                 pstmt.setInt(2, Integer.parseInt(input_ID));
  138.                 pstmt.executeUpdate();
  139.                 JOptionPane.showMessageDialog(this, "修改成功！
  140.                                               ",
  141.                                               "Succeed",
  142.                                               JOptionPane.INFORMATION_MESSAGE);
  143.             } catch (Exception ex) {
  144.                 JOptionPane.showMessageDialog(this, "两次输入的密码不同！",
  145.                  
  146. }
  147. "Error", JOptionPane.WARNING_MESSAGE);
  148. } catch (SQLException ex) {
  149.     ex.printStackTrace();
  150. }
  151. });
  152. this.add(bt_save);
  153. this.setDefaultCloseOperation(JDialog.HIDE_ON_CLOSE);
  154. }
  155. }
  156. class AlterDialog extends JDialog {
  157.     AlterDialog(JFrame parents) {
  158.         super(parents);
  159.         this.setSize(350, 460);
  160.         this.setLocationRelativeTo(parents);
  161.         init();
  162.         this.setVisible(true);
  163.     }
  164.     void init() {
  165.         Container c = this.getContentPane();
  166.         c.setLayout(new GridLayout(10, 1, 0, 15));
  167.         JLabel label_age = new JLabel("请输入年龄:");
  168.         this.add(label_age);
  169.         JTextField text_age = new JTextField();
  170.         this.add(text_age);
  171.         JLabel label_phone = new JLabel("请输入电话:");
  172.         this.add(label_phone);
  173.         JTextField text_phone = new JTextField();
  174.         this.add(text_phone);
  175.         JButton bt_save = new JButton("保  存");
  176.         Connection conn = Main.sendConnection();
  177.         String sql = "update ssms.student set student.age=?, student.phone=? where student.S_ID=?;";
  178.         bt_save.addActionListener(e -> { PreparedStatement pstmt = null; try {
  179.             pstmt = conn.prepareStatement(sql);
  180.             pstmt.setInt(1,
  181.                          Integer.parseInt(text_age.getText()));
  182.             pstmt.setString(2, text_phone.getText());
  183.             pstmt.setInt(3, Integer.parseInt(input_ID));
  184.             pstmt.executeUpdate();
  185.             now.init();
  186.             JOptionPane.showMessageDialog(this, "修改成功！", "Succeed",
  187.                                           JOptionPane.INFORMATION_MESSAGE);
  188.         } catch (SQLException | NumberFormatException ex) {
  189.             ex.printStackTrace();
  190.             JOptionPane.showMessageDialog(this, "格式有误，请重新输入！",
  191.             
  192.              
  193. }
  194. });
  195. "Error"
  196. , JOptionPane.WARNING_MESSAGE);
  197. this.add(bt_save);
  198. this.setDefaultCloseOperation(JDialog.HIDE_ON_CLOSE);
  199. }
  200. }
  201. }

4、运行调试与分析讨论

（1）登录

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

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

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

  1. public static String[][] send(String sql) {
  2. String[][] result = null;
  3. Connection conn = null;
  4. Statement statement = null;
  5. try {
  6.     conn = Main.sendConnection();
  7.     statement = conn.createStatement();
  8.     ResultSet rs = statement.executeQuery(sql);
  9.     ResultSetMetaData rsmd = rs.getMetaData();
  10.     int column = rsmd.getColumnCount();//获取列数rs.last();
  11.     int row = rs.getRow();//获取行数rs.beforeFirst();
  12.     result = new String[row][column];
  13.     int count = 0;
  14.     while (rs.next()) {
  15.         for (int i = 1; i <= column; i++) {
  16.             result[count][i - 1] = rs.getString(i);
  17.         }
  18.         count++;
  19.     }
  20. } catch (SQLException e) {
  21.     e.printStackTrace();
  22. }
  23. return result;
  24. }

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

  1. void show_all() {//统计总成绩
  2.     String[] head = {"学号", "姓名", "Java", "数据库原理", "算法与数据结构", "C++", "总分", "平均分", "排名"};
  3.     String sql = "select r.*, row_number() over(order by '平均分') as row_rank\n" +
  4.              "from(\n" +
  5.              "select score.S_ID, student.name,\n" +
  6.              "sum(case when T_ID = 10001 then score.grade else 0 end) as 'java',\n" +
  7.              "sum(case when T_ID = 10002 then score.grade else 0 end) as '数据库',\n" +
  8.              "sum(case when T_ID = 10003 then score.grade else 0 end) as '算法',\n" +
  9.              "sum(case when T_ID = 10004 then score.grade else 0 end) as
  10.              'C++',\n" +
  11.              "sum(score.grade) as '总分',\n" + "format(sum(score.grade)/count(*), 2) as '平均分'\n" + "from ssms.score, ssms.student\n" +
  12.              "where score.S_ID = student.S_ID\n" + "group by score.S_ID\n" +
  13.              "order by 平均分 desc) r";
  14. String[][]  result = ReturnQueryResult.send(sql);
  15. MyTable table = new MyTable(result, head);
  16. JScrollPane jsp = new JScrollPane(table);
  17. panels[0].add(jsp, BorderLayout.CENTER);
  18. String[] head_1 = {"分数段", "优秀(90~100)", "良好(80~90)", "中等
  19.                    ~80)","及格(60~70)", "不及格(0~60)"};
  20.                   String[][] result_1 = new String[1][6];
  21. result_1[0][0] = "人数";
  22. String sql_1 = "select count(case when ave >= 90 then 1 end) as ' 优秀',\n" +良好',\n" +中等',\n" +及格',\n" +
  23. "count(case when ave < 90 and ave >= 80 then 1 end) as ' "count(case when ave < 80 and ave >= 70 then 1 end) as ' "count(case when ave < 70 and ave >= 60 then 1 end) as '
  24.     "count(case when ave < 60 then 1 end) as '不及格'\n" + "from(select score.S_ID, student.name,\n" +
  25.     "sum(case when T_ID = 10001 then score.grade else 0 end) as
  26.     'java',\n" +
  27.     "sum(case when T_ID = 10002 then score.grade else 0 end) as '数据库',\n" +
  28.     "sum(case when T_ID = 10003 then score.grade else 0 end) as '算法',\n" +
  29.     "sum(case when T_ID = 10004 then score.grade else 0 end) as
  30.     'C++',\n" +
  31.     "sum(score.grade) as '总分',\n" + "format(sum(score.grade)/count(*), 2) as ave,\n" + "format(@r := @r + 1, 0) as '排名' \n" +
  32.     "from ssms.score, ssms.student, (select @r := 0) alias\n" +
  33.     "where score.S_ID = student.S_ID\n" + "group by score.S_ID) t;";
  34. String[][] resultSet = ReturnQueryResult.send(sql_1);
  35. for(int i = 1; i<6; i++) {
  36.     result_1[0][i] = resultSet[0][i-1];
  37. }
  38. MyTable table_1 = new MyTable(result_1, head_1);
  39. JScrollPane jsp_1 = new JScrollPane(table_1);
  40. jsp_1.setPreferredSize(new Dimension(0, 100));
  41. panels[0].add(jsp_1, BorderLayout.SOUTH);
  42. }
  43. void show_single(int no, String t_no) {//统计每科成绩String[] head = {"学号", "姓名", "成绩", "排名"};
  44.     String sql = "select r.*, row_number() over(order by 'subject') as row_rank\n" +
  45.                  "from(\n" +
  46.                  "select score.S_ID, student.name,\n" + "sum(score.grade) as 'subject'\n" + "from ssms.score, ssms.student\n" +
  47.                  "where score.S_ID = student.S_ID and score.T_ID = " + t_no
  48.                  +
  49.                  " group by score.S_ID\n" + "order by score.grade desc) r";
  50.     String[][]  result = ReturnQueryResult.send(sql);
  51.     MyTable table = new MyTable(result, head);
  52.     JScrollPane jsp = new JScrollPane(table);
  53.     panels[no].add(jsp, BorderLayout.CENTER);
  54.     String[] head_1 = {"分数段", "优秀(90~100)", "良好(80~90)", "中等
  55.                        ~80)","及格(60~70)", "不及格(0~60)"};
  56.                       String[][] result_1 = new String[1][6];
  57.     result_1[0][0] = "人数";
  58.     String sql_1 = "select count(case when score.grade >= 90 then 1 end) as '优秀',\n" +
  59.                    "count(case when score.grade < 90 and score.grade >= 80 then 1 end) as '良好',\n" +
  60.                    "count(case when score.grade < 80 and score.grade >= 70 then 1 end) as '中等',\n" +
  61.                    "count(case when score.grade < 70 and score.grade >= 60 then 1 end) as '及格',\n" +
  62.                    "count(case when score.grade < 60 then 1 end) as '不及格'\n"
  63.                    +
  64.                    "from ssms.score\n" +
  65.                    "where score.T_ID = " + t_no;
  66.     String[][] resultSet = ReturnQueryResult.send(sql_1);
  67.     for(int i = 1; i<6; i++) {
  68.         result_1[0][i] = resultSet[0][i-1];
  69.     }
  70.     MyTable table_1 = new MyTable(result_1, head_1);
  71.     JScrollPane jsp_1 = new JScrollPane(table_1);
  72.     jsp_1.setPreferredSize(new Dimension(0, 100));
  73.     panels[no].add(jsp_1, BorderLayout.SOUTH);
  74.     }
声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/小小林熬夜学编程/article/detail/170203
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号