1 package com.wenr.dao; 2  3 import java.sql.Connection; 4 import java.sql.PreparedStatement; 5 import java.sql.ResultSet; 6 import java.sql.SQLException; 7 import java.util.ArrayList; 8  9 import com.wenr.model.Course;10 import com.wenr.model.Student;11 import com.wenr.util.DBUtil;12 13 public class CourseDao {14     public ArrayList<Student> getStudent(Connection con, Course course) {15         ArrayList<Student> student = new ArrayList<>();16         String sql = "SELECT stu.sid,stu.sname,stuco.score FROM stu,co,stuco "17                      + "WHERE stu.sid=stuco.sid AND co.cid=stuco.cid AND co.cname=?";18         19         PreparedStatement pstmt = null;20         try {21             pstmt = con.prepareStatement(sql);22             pstmt.setString(1, course.getName());23         } catch (SQLException e) {24             e.printStackTrace();25         }26         27         ResultSet resultSet = null;28         try {29             resultSet = pstmt.executeQuery();30             while (resultSet.next()) {31                 student.add(new Student(resultSet.getString(1), resultSet.getString(2), resultSet.getDouble(3)));32             }33         } catch (SQLException e) {34             e.printStackTrace();35         }36         37         return student;38     }39     40     public static void main(String[] args) throws Exception {41         DBUtil dbUtil = new DBUtil();42         CourseDao dao = new CourseDao();43         Connection con = dbUtil.getCon();44         Course course = new Course();45         course.setName("中国现代史纲要");46         ArrayList<Student> student = dao.getStudent(con, course);47         for (Student s: student) {48             System.out.println(s.getSid() + " " + s.getSname() + " " + s.getScore());49         }50     }51     52 }

 1 package com.wenr.dao; 2  3 import java.sql.Connection; 4 import java.sql.PreparedStatement; 5 import java.sql.ResultSet; 6 import java.sql.SQLException; 7 import java.util.ArrayList; 8  9 import com.wenr.model.Course;10 import com.wenr.model.Student;11 import com.wenr.util.DBUtil;12 13 public class CourseDao {14     public ArrayList<Student> getStudent(Connection con, Course course) {15         ArrayList<Student> student = new ArrayList<>();16         String sql = "SELECT stu.sid,stu.sname,stuco.score FROM stu,co,stuco "17                      + "WHERE stu.sid=stuco.sid AND co.cid=stuco.cid AND co.cname=?";18         19         PreparedStatement pstmt = null;20         try {21             pstmt = con.prepareStatement(sql);22             pstmt.setString(1, course.getName());23         } catch (SQLException e) {24             e.printStackTrace();25         }26         27         ResultSet resultSet = null;28         try {29             resultSet = pstmt.executeQuery();30             while (resultSet.next()) {31                 student.add(new Student(resultSet.getString(1), resultSet.getString(2), resultSet.getDouble(3)));32             }33         } catch (SQLException e) {34             e.printStackTrace();35         }36         37         return student;38     }39     40     public static void main(String[] args) throws Exception {41         DBUtil dbUtil = new DBUtil();42         CourseDao dao = new CourseDao();43         Connection con = dbUtil.getCon();44         Course course = new Course();45         course.setName("中国现代史纲要");46         ArrayList<Student> student = dao.getStudent(con, course);47         for (Student s: student) {48             System.out.println(s.getSid() + " " + s.getSname() + " " + s.getScore());49         }50     }51     52 }

 1 package com.wenr.model; 2 /* 3 CREATE TABLE co ( 4     cid int PRIMARY KEY auto_increment, 5     cname varchar(20) NOT NULL 6 ); 7 */ 8 public class Course { 9     private int id;10     private String name;11     private double score;12     13     public Course(String name) {14         super();15         this.name = name;16     }17     public Course() {18         super();19     }20     public Course(int id, String name, double score) {21         super();22         this.id = id;23         this.name = name;24         this.score = score;25     }26     public int getId() {27         return id;28     }29     public void setId(int id) {30         this.id = id;31     }32     public String getName() {33         return name;34     }35     public void setName(String name) {36         this.name = name;37     }38     public double getScore() {39         return score;40     }41     public void setScore(double score) {42         this.score = score;43     }44 }

 1 package com.wenr.model; 2 /* 3 create table stu ( 4     sid char(10) PRIMARY KEY, 5     sname varchar(20) NOT NULL, 6     spwd varchar(20) NOT NULL 7 );     8 */ 9 public class Student {10     private String sid;11     private String sname;12     private String spwd;13     private double score;14     15     public Student(String sid, String sname, double score) {16         super();17         this.sid = sid;18         this.sname = sname;19         this.score = score;20     }21     22     public Student(String sname) {23         super();24         this.sname = sname;25     }26     public Student(String sid, String spwd) {27         super();28         this.sid = sid;29         this.spwd = spwd;30     }31     public Student() {32         super();33     }34     public String getSid() {35         return sid;36     }37     public void setSid(String sid) {38         this.sid = sid;39     }40     public String getSname() {41         return sname;42     }43     public void setSname(String sname) {44         this.sname = sname;45     }46     public String getSpwd() {47         return spwd;48     }49     public void setSpwd(String spwd) {50         this.spwd = spwd;51     }52     public double getScore() {53         return score;54     }55     public void setScore(double score) {56         this.score = score;57     }58 }

 1 package com.wenr.util; 2  3 import java.sql.Connection; 4 import java.sql.DriverManager; 5 import java.sql.SQLException; 6  7 public class DBUtil { 8     private String dbUrl = "jdbc:mysql://localhost:3306/db_student"; 9     private String dbUserName = "root";10     private String dbPassword = "123456";11     private String jdbcName = "com.mysql.jdbc.Driver";12     /**13      * 获取数据库连接14      * @return15      * @throws Exception16      */17     public Connection getCon() throws Exception {18         Class.forName(jdbcName);19         Connection con = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);20         return con;21     }22     /**23      * 关闭数据库连接24      * @param con25      * @throws SQLException26      */27     public void closeCon(Connection con) throws SQLException {28         if (con != null)29             con.close();30     }31     32     public static void main(String[] args) {33         DBUtil dbUtil = new DBUtil();34         try {35             dbUtil.getCon();36             System.out.println("连接成功");37         } catch (Exception e) {38             System.out.println("连接失败");39             e.printStackTrace();40         }41     }42     43 }

1 package com.wenr.util;2 3 public class StringUtil {4     public static Boolean isEmpty(String str) {5         if ("".equals(str) || str == null) return true;6         return false;7     }8 }

  1 package com.wenr.view;  2   3 import java.awt.EventQueue;  4   5 import javax.swing.JFrame;  6 import javax.swing.JPanel;  7 import javax.swing.border.EmptyBorder;  8   9 import com.wenr.dao.StudentDao; 10 import com.wenr.model.Student; 11 import com.wenr.util.DBUtil; 12 import com.wenr.util.StringUtil; 13  14 import javax.swing.JLabel; 15 import javax.swing.JOptionPane; 16 import javax.swing.JTextField; 17 import javax.swing.JButton; 18 import java.awt.Font; 19 import java.awt.Toolkit; 20 import javax.swing.ImageIcon; 21 import javax.swing.SwingConstants; 22 import javax.swing.UIManager; 23  24 import java.awt.Color; 25 import java.awt.event.ActionListener; 26 import java.sql.Connection; 27 import java.sql.SQLException; 28 import java.util.Enumeration; 29 import java.awt.event.ActionEvent; 30 import javax.swing.JPasswordField; 31  32 @SuppressWarnings("serial") 33 public class Logon extends JFrame { 34  35     private JPanel contentPane; 36     private JTextField tfID; 37     private JPasswordField passwordField; 38      39     DBUtil dbUtil = new DBUtil(); 40     StudentDao studentDao = new StudentDao(); 41  42     public static void main(String[] args) { 43         EventQueue.invokeLater(new Runnable() { 44             public void run() { 45                 try { 46                     Logon frame = new Logon(); 47                     frame.setVisible(true); 48                 } catch (Exception e) { 49                     e.printStackTrace(); 50                 } 51             } 52         }); 53     } 54  55     public Logon() { 56         setResizable(false); 57         setBackground(Color.LIGHT_GRAY); 58          59         // 改变默认字体 60         Font font = new Font("Dialog", Font.PLAIN, 12); 61         Enumeration<Object> keys = UIManager.getDefaults().keys(); 62         while (keys.hasMoreElements()) { 63             Object key = keys.nextElement(); 64             Object value =http://www.mamicode.com/ UIManager.get(key); 65             if (value instanceof javax.swing.plaf.FontUIResource) { 66                 UIManager.put(key, font); 67             } 68         } 69          70         setIconImage(Toolkit.getDefaultToolkit().getImage(".\\resources\\icon.png")); 71         setTitle("学生成绩管理系统"); 72         setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE); 73         setBounds(500, 200, 510, 358); 74         contentPane = new JPanel(); 75         contentPane.setBackground(new Color(135, 206, 235)); 76         contentPane.setBorder(new EmptyBorder(5, 5, 5, 5)); 77         setContentPane(contentPane); 78         contentPane.setLayout(null); 79          80         JLabel label = new JLabel("学生成绩管理系统"); 81         label.setIcon(new ImageIcon(".\\resources\\icon_student.png")); 82         label.setFont(new Font("黑体", Font.BOLD, 20)); 83         label.setBounds(124, 45, 259, 38); 84         contentPane.add(label); 85          86         JLabel label_1 = new JLabel("账号"); 87         label_1.setHorizontalAlignment(SwingConstants.RIGHT); 88         label_1.setIcon(new ImageIcon(".\\resources\\icon_users.png")); 89         label_1.setBounds(119, 138, 66, 21); 90         contentPane.add(label_1); 91          92         JLabel label_2 = new JLabel("密码"); 93         label_2.setHorizontalAlignment(SwingConstants.RIGHT); 94         label_2.setIcon(new ImageIcon(".\\resources\\icon_password.png")); 95         label_2.setBounds(119, 179, 66, 21); 96         contentPane.add(label_2); 97          98         tfID = new JTextField(); 99         tfID.setBounds(195, 138, 146, 21);100         contentPane.add(tfID);101         tfID.setColumns(10);102         103         JButton jbStuLogon = new JButton("学生登录");104         jbStuLogon.setIcon(new ImageIcon(".\\resources\\icon_stu.png"));105         jbStuLogon.addActionListener(new ActionListener() {106             public void actionPerformed(ActionEvent e) {107                 String id = tfID.getText();108                 String pwd = new String(passwordField.getPassword());109                 if (StringUtil.isEmpty(id)) {110                     JOptionPane.showMessageDialog(null, "账号不能为空！");111                     return ;112                 }113                 if (StringUtil.isEmpty(pwd)) {114                     JOptionPane.showMessageDialog(null, "密码不能为空！");115                     return ;116                 }117                 //System.out.println(id + "+" + pwd);118                 Student student = new Student(id, pwd);119                 Connection con = null;120                 Student resultStudent = null;121                 try {122                     con = dbUtil.getCon();123                     resultStudent = studentDao.logon(con, student);124                     if (resultStudent == null) {125                         JOptionPane.showMessageDialog(null, "账号或密码错误！");126                     } else {127                         dispose();128                         StudentView studentView = new StudentView(student);129                         studentView.setVisible(true);130                     }131                 } catch (Exception e1) {132                     System.out.println("登录失败！");133                     e1.printStackTrace();134                 } finally {135                     if (con != null) {136                         try {137                             con.close();138                         } catch (SQLException e1) {139                             e1.printStackTrace();140                         }141                     }142                 }143                 144             }145         });146         jbStuLogon.setBounds(82, 241, 135, 23);147         contentPane.add(jbStuLogon);148         149         JButton jbAdminLogon = new JButton("管理员登录");150         jbAdminLogon.setIcon(new ImageIcon(".\\resources\\icon_admin.png"));151         jbAdminLogon.addActionListener(new ActionListener() {152             public void actionPerformed(ActionEvent e) {153                 String id = tfID.getText();154                 String pwd = new String(passwordField.getPassword());155                 if (StringUtil.isEmpty(id)) {156                     JOptionPane.showMessageDialog(null, "账号不能为空！");157                     return ;158                 }159                 if (StringUtil.isEmpty(pwd)) {160                     JOptionPane.showMessageDialog(null, "密码不能为空！");161                     return ;162                 }163                 if ("admin".equals(id) && "nimda".equals(pwd)) {164                     new AdminView().setVisible(true);165                     dispose();166                 } else {167                     JOptionPane.showMessageDialog(null, "用户名或密码错误！");168                 }169             }170         });171         172         jbAdminLogon.setBounds(270, 241, 135, 23);173         contentPane.add(jbAdminLogon);174         175         passwordField = new JPasswordField();176         passwordField.setBounds(195, 179, 146, 21);177         contentPane.add(passwordField);178     }179 }

  1 package com.wenr.view;  2   3 import java.awt.EventQueue;  4   5 import javax.swing.JFrame;  6 import javax.swing.JPanel;  7 import javax.swing.border.EmptyBorder;  8   9 import com.wenr.dao.StudentDao; 10 import com.wenr.model.Course; 11 import com.wenr.model.Student; 12 import com.wenr.util.DBUtil; 13  14  15 import javax.swing.JLabel; 16 import javax.swing.JButton; 17 import java.awt.event.ActionListener; 18 import java.sql.Connection; 19 import java.util.ArrayList; 20 import java.awt.event.ActionEvent; 21 import javax.swing.JTextPane; 22 import java.awt.Color; 23 import javax.swing.ImageIcon; 24 import java.awt.Toolkit; 25  26 @SuppressWarnings("serial") 27 public class StudentView extends JFrame { 28  29     private JPanel contentPane; 30  31     public static void main(String[] args) { 32         EventQueue.invokeLater(new Runnable() { 33             public void run() { 34                 try { 35                     StudentView frame = new StudentView(null); 36                     frame.setVisible(true); 37                 } catch (Exception e) { 38                     e.printStackTrace(); 39                 } 40             } 41         }); 42     } 43  44     public StudentView(Student student) { 45         setResizable(false); 46         setIconImage(Toolkit.getDefaultToolkit().getImage(".\\resources\\icon_stu.png")); 47         setTitle("学生登录"); 48         setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE); 49         setBounds(500, 200, 510, 358); 50          51         contentPane = new JPanel(); 52         contentPane.setBackground(new Color(135, 206, 235)); 53         contentPane.setBorder(new EmptyBorder(5, 5, 5, 5)); 54         setContentPane(contentPane); 55         contentPane.setLayout(null); 56          57         JLabel label = new JLabel("欢迎，"); 58         label.setBounds(181, 23, 41, 15); 59         contentPane.add(label); 60          61         JLabel studentName = new JLabel("New label"); 62         studentName.setBounds(217, 23, 118, 15); 63         studentName.setText(student.getSname()); 64         contentPane.add(studentName); 65         // 66         DBUtil dbUtil = new DBUtil(); 67         StudentDao studentDao = new StudentDao(); 68         Connection con = null; 69         String scoreInfo = ""; 70         try { 71             con = dbUtil.getCon(); 72             ArrayList<Course> course = studentDao.getScrore(con, student); 73             for (Course c: course) { 74                 scoreInfo += String.format("%3d", c.getId()) + "\t"; 75                 scoreInfo += String.format("%20s", c.getName()) + "\t"; 76                 scoreInfo += String.format("%10.2f", c.getScore()) + "\n"; 77             } 78         } catch (Exception e1) { 79             e1.printStackTrace(); 80         } 81         System.out.println(scoreInfo.toString()); 82         JTextPane tpScore = new JTextPane(); 83         tpScore.setEditable(false); 84         tpScore.setText(scoreInfo); 85         tpScore.setBounds(64, 58, 360, 180); 86         contentPane.add(tpScore); 87          88         JButton jbExit = new JButton("退出登录"); 89         jbExit.setIcon(new ImageIcon(".\\resources\\icon_exit.png")); 90         jbExit.setBackground(Color.WHITE); 91         jbExit.addActionListener(new ActionListener() { 92             public void actionPerformed(ActionEvent e) { 93                 dispose(); 94                 new Logon().setVisible(true); 95             } 96         }); 97         jbExit.setBounds(172, 262, 140, 23); 98         contentPane.add(jbExit); 99     }100 }

  1 package com.wenr.view;  2   3 import java.awt.EventQueue;  4   5 import javax.swing.JFrame;  6 import javax.swing.JPanel;  7 import javax.swing.border.EmptyBorder;  8   9 import com.wenr.dao.CourseDao; 10 import com.wenr.dao.StudentDao; 11 import com.wenr.model.Course; 12 import com.wenr.model.Student; 13 import com.wenr.util.DBUtil; 14 import com.wenr.util.StringUtil; 15  16 import javax.swing.JButton; 17 import java.awt.Color; 18 import java.awt.Toolkit; 19 import javax.swing.ImageIcon; 20 import java.awt.event.ActionListener; 21 import java.sql.Connection; 22 import java.util.ArrayList; 23 import java.awt.event.ActionEvent; 24 import javax.swing.JLabel; 25 import javax.swing.JOptionPane; 26 import javax.swing.JTextField; 27 import javax.swing.JTextArea; 28  29 @SuppressWarnings("serial") 30 public class AdminView extends JFrame { 31  32     private JPanel contentPane; 33     private JTextField queryText; 34     private JLabel lblNewLabel; 35     private JTextArea queryResult; 36  37     public static void main(String[] args) { 38         EventQueue.invokeLater(new Runnable() { 39             public void run() { 40                 try { 41                     AdminView frame = new AdminView(); 42                     frame.setVisible(true); 43                 } catch (Exception e) { 44                     e.printStackTrace(); 45                 } 46             } 47         }); 48     } 49  50     public AdminView() { 51         setResizable(false); 52         setIconImage(Toolkit.getDefaultToolkit().getImage(".\\resources\\icon_admin.png")); 53         setBackground(new Color(144, 238, 144)); 54         setTitle("管理员登录"); 55         setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE); 56         setBounds(500, 200, 510, 358); 57         contentPane = new JPanel(); 58         contentPane.setBackground(new Color(135, 206, 235)); 59         contentPane.setBorder(new EmptyBorder(5, 5, 5, 5)); 60         setContentPane(contentPane); 61         contentPane.setLayout(null); 62          63         JButton btnExit = new JButton("退出登录"); 64         btnExit.addActionListener(new ActionListener() { 65             public void actionPerformed(ActionEvent e) { 66                 dispose(); 67                 new Logon().setVisible(true); 68             } 69         }); 70         btnExit.setIcon(new ImageIcon(".\\resources\\icon_exit.png")); 71         btnExit.setBackground(new Color(255, 255, 255)); 72         btnExit.setBounds(185, 273, 125, 28); 73         contentPane.add(btnExit); 74          75         queryText = new JTextField(); 76         queryText.setBounds(36, 48, 330, 28); 77         contentPane.add(queryText); 78         queryText.setColumns(10); 79          80         queryResult = new JTextArea(); 81         queryResult.setEditable(false); 82         queryResult.setBounds(46, 102, 405, 154); 83         contentPane.add(queryResult); 84          85         JButton btQuery = new JButton("查询"); 86         btQuery.addActionListener(new ActionListener() { 87             public void actionPerformed(ActionEvent e) { 88                 String info = queryText.getText(); 89                 if (StringUtil.isEmpty(info)) { 90                     JOptionPane.showMessageDialog(null, "查询信息不能为空!"); 91                     return ; 92                 } 93                 Student student = new Student(info); 94                 Course course = new Course(info); 95                  96                 DBUtil dbUtil = new DBUtil(); 97                 StudentDao studentDao = new StudentDao(); 98                 CourseDao courseDao = new CourseDao(); 99                 100                 Connection con = null;101                 try {102                     con = dbUtil.getCon();103                     ArrayList<Course> courseList = studentDao.getScrore(con, student);104                     ArrayList<Student> studentList = courseDao.getStudent(con, course);105                     String result = "";106                     for (Course c: courseList) {107                         result += String.format("%3d", c.getId()) + "\t";108                         result += String.format("%20s", c.getName()) + "\t";109                         result += String.format("%10.2f", c.getScore()) + "\n";110                     }111                     for (Student s: studentList) {112                         result += String.format("%10s", s.getSid()) + "\t";113                         result += String.format("%10s", s.getSname()) + "\t";114                         result += String.format("%10.2f", s.getScore()) + "\n";115                     }116                     queryResult.setText(result);117                 } catch (Exception e1) {118                     e1.printStackTrace();119                 }120             }121         });122         btQuery.setIcon(new ImageIcon(".\\resources\\icon_query.png"));123         btQuery.setBounds(389, 47, 93, 29);124         contentPane.add(btQuery);125         126         lblNewLabel = new JLabel("输入要查询的学生姓名或课程名：");127         lblNewLabel.setBounds(26, 10, 208, 28);128         contentPane.add(lblNewLabel);129     }130 }