数据库课程设计-JAVA实现学生信息管理系统

/* * GetCouInfo.java * * Created on __DATE__, __TIME__ */ package sims.view.getsc; import java.awt.BorderLayout; import java.awt.Color; import java.awt.Dimension; import java.awt.Toolkit; import java.awt.event.ActionEvent; import java.awt.event.ActionListener; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import javax.swing.JButton; import javax.swing.JFrame; import javax.swing.JLabel; import javax.swing.JOptionPane; import javax.swing.JPanel; import javax.swing.JScrollPane; import javax.swing.JTable; import javax.swing.JTextField; import javax.swing.table.DefaultTableModel; import sims.model.Course; import sims.model.SC; import sims.model.Student; import sims.util.jdbc.DatabaseConnection; public class GetSC extends JFrame implements ActionListener { private static final long serialVersionUID = 1L; private JScrollPane jScrollPane1; private JTable jTable1; private DefaultTableModel tableModel1; private JPanel jPanel1; private JLabel jlable1; private JLabel jlable3; private JLabel jlable2; private JTextField jTextField1; private JTextField jTextField2; private JButton jbutton1; private JButton jbutton2; private JButton jbutton3; private JButton jbutton4; private Object SCInfo[][]; private String th[]; private List<Student> all1 = null; private List<Course> all2 = null; private List<SC> all3 = null; private DatabaseConnection dbconn = null; private Connection conn = null; private Course course = null; private Student stu = null; private SC sc = null; public GetSC() { initComponents(); } private void initComponents() { this.setTitle("查询成绩基本信息"); this.setLayout(new BorderLayout()); jScrollPane1 = new JScrollPane(); jTable1 = new JTable(); jPanel1 = new JPanel(); jlable1 = new JLabel("学号："); jlable3 = new JLabel("课程号："); jTextField1 = new JTextField(); jTextField2 = new JTextField(); jbutton1 = new JButton("学号查询"); jbutton2 = new JButton("课程号查询"); jbutton3 = new JButton("精确查询"); jbutton4 = new JButton("模糊查询"); jPanel1.add(jlable1); jPanel1.add(jTextField1); jTextField1.setPreferredSize(new Dimension(130, 28)); jPanel1.add(jlable3); jPanel1.add(jTextField2); jTextField2.setPreferredSize(new Dimension(130, 28)); jPanel1.add(jbutton1); jPanel1.add(jbutton2); jPanel1.add(jbutton3); jPanel1.add(jbutton4); jlable2 = new JLabel(); this.getContentPane().add(jScrollPane1, BorderLayout.CENTER); jScrollPane1.getViewport().add(jTable1); this.getContentPane().add(jPanel1, BorderLayout.NORTH); this.getContentPane().add(jlable2, BorderLayout.SOUTH); int width = Toolkit.getDefaultToolkit().getScreenSize().width; // 得到屏幕的宽度 int height = Toolkit.getDefaultToolkit().getScreenSize().height; // 得到屏幕的高度 setLocation((width - 800) / 2, (height - 450) / 2); setSize(800, 450); this.setVisible(true); jbutton1.addActionListener(this); jbutton2.addActionListener(this); jbutton3.addActionListener(this); jbutton4.addActionListener(this); } public void actionPerformed(ActionEvent e) { if (e.getSource() == jbutton1) { getBySno();//按学号查询 } if (e.getSource() == jbutton2) { getByCno();//按课程号查询 } if (e.getSource() == jbutton3) { getBySnoCno();//按课程号和学号查询 } if (e.getSource() == jbutton4) { getByKey();//通过关键字进行模糊查询 } } private void getBySno() { dbconn = new DatabaseConnection(); conn = dbconn.getConnection(); // 获得连接 String snostr = jTextField1.getText(); if (snostr.length()==0) { JOptionPane.showMessageDialog(this, "进行使用学号查询的时学号不能为空！"); } else { stu = new Student(); stu.setSno(snostr); String sql1 = "SELECT sno,sname,cno,cname,credit,grade,point,tel " + "FROM Stu_Cou_SC" + " WHERE sno=? "; PreparedStatement pstmt = null; try { pstmt = conn.prepareStatement(sql1); pstmt.setString(1, stu.getSno()); ResultSet rs = pstmt.executeQuery(); all1 = new ArrayList<Student>(); all2 = new ArrayList<Course>(); all3 = new ArrayList<SC>(); int n=0; while (rs.next()) { n++; course = new Course(); stu = new Student(); sc = new SC(); String sno = rs.getString(1); String sname=rs.getString(2); String cno=rs.getString(3); String cname = rs.getString(4); Float credit = rs.getFloat(5); Float grade = rs.getFloat(6); Float point = rs.getFloat(7); String tel = rs.getString(8); stu.setSno(sno); stu.setSname(sname); course.setCno(cno); course.setCname(cname); course.setCredit(credit); sc.setGrade(grade); sc.setPoint(point); stu.setTel(tel); all1.add(stu); all2.add(course); all3.add(sc); } if (n==0) { jScrollPane1.setVisible(false); jlable2.setVisible(false); JOptionPane.showMessageDialog(this, "没有查询到相关的数据！\n"); jScrollPane1.setVisible(true); } else if (n > 0) { th = new String[] { "编号","学号","姓名","课程号","课程名","成绩","学分","绩点","联系电话" }; SCInfo = new Object[all2.size()][th.length]; for (int k = 0; k < n; k++) { SCInfo[k][0] = k + 1; SCInfo[k][1] = all1.get(k).getSno(); SCInfo[k][2] =all1.get(k).getSname(); SCInfo[k][3]=all2.get(k).getCno(); SCInfo[k][4] = all2.get(k).getCname(); SCInfo[k][5] = all3.get(k).getGrade(); SCInfo[k][6] = all2.get(k).getCredit(); SCInfo[k][7] = all3.get(k).getPoint(); SCInfo[k][8] = all1.get(k).getTel(); } jScrollPane1.setVisible(true); jlable2.setVisible(true); tableModel1 = new DefaultTableModel(SCInfo, th); jTable1.setModel(tableModel1); jTable1.setRowHeight(24); jlable2.setText("一共查询到了" + all2.size() + "条成绩信息记录！"); jlable2.setForeground(Color.RED); } } catch (SQLException e) { JOptionPane.showMessageDialog(this, "查询成绩信息失败！查询异常：" + e.getMessage()); } finally { if (conn != null) { dbconn.close(); } } } } private void getByCno() { dbconn = new DatabaseConnection(); conn = dbconn.getConnection(); // 获得连接 String cnostr = jTextField2.getText(); if (cnostr.length() == 0) { JOptionPane.showMessageDialog(this, "进行课程号查询的时课程号不能为空！"); } else { course = new Course(); course.setCno(cnostr); String sql1 = "SELECT sno,sname,cno,cname,credit,grade,point,tel " + "FROM Stu_Cou_SC" + " WHERE cno=?"; PreparedStatement pstmt = null; try { pstmt = conn.prepareStatement(sql1); pstmt.setString(1, course.getCno()); ResultSet rs = pstmt.executeQuery(); all1 = new ArrayList<Student>(); all2 = new ArrayList<Course>(); all3 = new ArrayList<SC>(); int n=0; while (rs.next()) { n++; course = new Course(); stu = new Student(); sc = new SC(); String sno = rs.getString(1); String sname=rs.getString(2); String cno=rs.getString(3); String cname = rs.getString(4); Float credit = rs.getFloat(5); Float grade = rs.getFloat(6); Float point = rs.getFloat(7); String tel = rs.getString(8); stu.setSno(sno); stu.setSname(sname); course.setCno(cno); course.setCname(cname); course.setCredit(credit); sc.setGrade(grade); sc.setPoint(point); stu.setTel(tel); all1.add(stu); all2.add(course); all3.add(sc); } if (n==0) { jScrollPane1.setVisible(false); jlable2.setVisible(false); JOptionPane.sho