manager_user.java

利用数据库做了一个小小的学生考试成绩查询管理系统

package classfile;
import java.awt.*;
import java.awt.event.*;
import java.sql.*;
import java.util.*;
import javax.swing.*;
import java.io.*;
public class Manager_user extends JFrame //implements ActionListener
{
	JMenuBar mb=new JMenuBar();
	JMenu m1=new JMenu("查询");//调用老师的查询
	JMenu m2=new JMenu("录入");
	JMenu m3=new JMenu("修改");
	JMenu m4=new JMenu("删除");
	JMenu m5=new JMenu("退出");
	JMenuItem mb1=new JMenuItem("按课程查询");
	JMenuItem mb2=new JMenuItem("按班级查询");
	JMenuItem mb3=new JMenuItem("单个录入");
	JMenuItem mb4=new JMenuItem("按学号删除");
	JMenuItem mb5=new JMenuItem("按班级删除");
	JMenuItem mb6=new JMenuItem("按课程删除");
	JMenuItem mb7=new JMenuItem("修改");
	JMenuItem mb8=new JMenuItem("退出");
	
	
	JTextArea txt=new JTextArea();
	JDialog g1=new JDialog(this,"录入成绩");
	JDialog g2=new JDialog(this,"修改");
	JDialog g3=new JDialog(this,"按学号删除");
	JDialog g4=new JDialog(this,"按班级删除");
	JDialog g5=new JDialog(this,"按课程删除");
	JDialog gd1=new JDialog(this,"按课程查询成绩");
	JDialog gd2=new JDialog(this,"按班级查询成绩");

	JLabel lab1=new JLabel("课程号");
	JLabel lab2=new JLabel("学号");
	JLabel lab3=new JLabel("成绩");
	JLabel lab4=new JLabel("课程号");
	JLabel lab5=new JLabel("学号");
	JLabel lab6=new JLabel("成绩");
	JLabel lab7=new JLabel("学号");
	JLabel lab8=new JLabel("班级");
	JLabel lab9=new JLabel("课程号");
	
	JLabel la1=new JLabel("课程名");
	JLabel la2=new JLabel("操作符");
	JLabel la3=new JLabel("待查询值");
	JLabel la4=new JLabel("课程名");
	JLabel la5=new JLabel("班级");
	JLabel la6=new JLabel("操作符");
	JLabel la7=new JLabel("待查询值");
	
	JComboBox com=new JComboBox();
	JComboBox oper=new JComboBox();
	JComboBox com1=new JComboBox();
	JComboBox com2=new JComboBox();
	JComboBox oper1=new JComboBox();
	
	JTextField txt1=new JTextField(" ");
	JTextField txt2=new JTextField(" ");
	JTextField txt3=new JTextField(" ");
	JTextField txt4=new JTextField(" ");
	JTextField txt5=new JTextField(" ");
	JTextField txt6=new JTextField(" ");
	JTextField txt7=new JTextField(" ");
	JTextField txt8=new JTextField(" ");
	JTextField txt9=new JTextField(" ");
	JTextField text1=new JTextField(" ");
	JTextField text2=new JTextField(" ");
	
	JButton b1=new JButton("录入");
	JButton b2=new JButton("取消");
	JButton b3=new JButton("修改");
	JButton b4=new JButton("取消");
	JButton b5=new JButton("删除");
	JButton b6=new JButton("取消");
	JButton b7=new JButton("删除");
	JButton b8=new JButton("取消");
	JButton b9=new JButton("删除");
	JButton b10=new JButton("取消");
	JButton btn1=new JButton("查询");
	JButton btn2=new JButton("取消");
	JButton btn3=new JButton("查询");
	JButton btn4=new JButton("取消");
	
	JPanel p=new JPanel();
	JPanel p1=new JPanel();
	JPanel p2=new JPanel();
	JPanel p3=new JPanel();
	
	public Manager_user()
	{
		m1.add(mb1);
		m1.add(mb2);
		m2.add(mb3);
		m3.add(mb7);
		m4.add(mb4);
		m4.add(mb5);
		m4.add(mb6);
		m5.add(mb8);
		
		mb.add(m1);
		mb.add(m2);
		mb.add(m3);
		mb.add(m4);
		mb.add(m5);
		
		mb.setBackground(Color.yellow);
		txt.setBackground(Color.GREEN);
		txt.setFont(new Font("Dialog",Font.BOLD,24));
		txt.setEditable(false);
		this.setJMenuBar(mb);
		this.add(txt);
		oper.addItem("=");
		oper.addItem(">");
		oper.addItem("<");
		oper1.addItem("=");
		oper1.addItem(">");
		oper1.addItem("<");
		combo1_actionPerformed();
		combo2_actionPerformed();
		
		p.setLayout(new GridLayout(2,3));
		p.add(la1);
		p.add(la2);
		p.add(la3);
		p.add(com);
		p.add(oper);
		p.add(text1);
		p1.setLayout(new GridLayout(1,2));
		p1.add(btn1);
		p1.add(btn2);
		p2.setLayout(new GridLayout(2,4));
		p2.add(la4);
		p2.add(la5);
		p2.add(la6);
		p2.add(la7);
		p2.add(com1);
		p2.add(com2);
		p2.add(oper1);
		p2.add(text2);
		p3.setLayout(new GridLayout(1,2));
		p3.add(btn3);
		p3.add(btn4);
		
		gd1.setBounds(90,90,500,130);
		gd1.setBackground(Color.blue);
		gd1.add(p,BorderLayout.CENTER);
		gd1.add(p1,BorderLayout.SOUTH);
	
		gd2.setBounds(90,90,500,130);
		gd2.setBackground(Color.blue);
		gd2.add(p2,BorderLayout.CENTER);
		gd2.add(p3,BorderLayout.SOUTH);
		
		g1.setLayout(new GridLayout(2,4));
		g1.add(lab1);
		g1.add(lab2);
		g1.add(lab3);
		g1.add(b1);
		g1.add(txt1);
		g1.add(txt2);
		g1.add(txt3);
		g1.add(b2);
		g1.setBounds(100,100,300,150);
		
		
		g2.setLayout(new GridLayout(2,4));
		g2.add(lab4);
		g2.add(lab5);
		g2.add(lab6);
		g2.add(b3);
		g2.add(txt4);
		g2.add(txt5);
		g2.add(txt6);
		g2.add(b4);
		g2.setBounds(100,100,300,150);
		
		
		g3.setLayout(new GridLayout(2,2));
		g3.add(lab7);
		g3.add(b5);
		g3.add(txt7);
		g3.add(b6);
		g3.setBounds(130,130,270,100);
		
		g4.setLayout(new GridLayout(2,2));
		g4.add(lab8);
		g4.add(b7);
		g4.add(txt8);
		g4.add(b8);
		g4.setBounds(130,130,270,100);
		
		g5.setLayout(new GridLayout(2,2));
		g5.add(lab9);
		g5.add(b9);
		g5.add(txt9);
		g5.add(b10);
		g5.setBounds(130,130,270,100);
		
		btn1.addActionListener(new ActionListener(){
			public void actionPerformed(ActionEvent e)
			{
				course_query_actionPerformed(e);
			}
		});
		btn2.addActionListener(new ActionListener(){
			public void actionPerformed(ActionEvent e)
			{
				can1_actionPerformed(e);
			}
		});
		btn3.addActionListener(new ActionListener(){
			public void actionPerformed(ActionEvent e)
			{
				class_query_actionPerformed(e);
			}
		});
		btn4.addActionListener(new ActionListener(){
			public void actionPerformed(ActionEvent e)
			{
				can2_actionPerformed(e);
			}
		});
		
		
		
		b1.addActionListener(new ActionListener(){
			public void actionPerformed(ActionEvent e)
			{
				in_actionPerformed();
			}
		});
		b2.addActionListener(new ActionListener(){
			public void actionPerformed(ActionEvent e)
			{
				cancel1_actionPerformed();
			}
		});
		b3.addActionListener(new ActionListener(){
			public void actionPerformed(ActionEvent e)
			{
				modify_actionPerformed();
			}
		});
		b4.addActionListener(new ActionListener(){
			public void actionPerformed(ActionEvent e)
			{
				cancel2_actionPerformed();
			}
		});
		b5.addActionListener(new ActionListener(){
			public void actionPerformed(ActionEvent e)
			{
				sno_delete_actionPerformed();
			}
		});
		b6.addActionListener(new ActionListener(){
			public void actionPerformed(ActionEvent e)
			{
				cancel3_actionPerformed();
		
			}
		});
		b7.addActionListener(new ActionListener(){
			public void actionPerformed(ActionEvent e)
			{
				class_delete_actionPerformed();
			}
		});
		b8.addActionListener(new ActionListener(){ 
			public void actionPerformed(ActionEvent e)
			{
				cancel4_actionPerformed();
			}
		});
		b9.addActionListener(new ActionListener(){
			public void actionPerformed(ActionEvent e)
			{
				course_delete_actionPerformed();
			}
		});
		b10.addActionListener(new ActionListener(){
			public void actionPerformed(ActionEvent e)
			{
				cancel5_actionPerformed();
			}
		});
		
		mb1.addActionListener(new ActionListener(){
			public void actionPerformed(ActionEvent e)
			{
				gd1.setVisible(true);
				
			}
		});
		mb2.addActionListener(new ActionListener(){
			public void actionPerformed(ActionEvent e)
			{
				gd2.setVisible(true);
			}
		});
		mb3.addActionListener(new ActionListener(){
			public void actionPerformed(ActionEvent e)
			{
				g1.setVisible(true);
			}
		});
		mb4.addActionListener(new ActionListener(){
			public void actionPerformed(ActionEvent e)
			{
				g3.setVisible(true);
			}
		});
		mb5.addActionListener(new ActionListener(){
			public void actionPerformed(ActionEvent e)
			{
				g4.setVisible(true);
			}
		});
		mb6.addActionListener(new ActionListener(){
			public void actionPerformed(ActionEvent e)
			{
				g5.setVisible(true);
			}
		});
		mb7.addActionListener(new ActionListener(){
			public void actionPerformed(ActionEvent e)
			{
				g2.setVisible(true);
			}
		});
		mb8.addActionListener(new ActionListener(){
			public void actionPerformed(ActionEvent e)
			{
				exit();
			}
		});
		this.setBounds(200,200,800,600);
		this.setVisible(true);
	}
	void exit()
	{
		this.setVisible(false);
	}
	void can1_actionPerformed(ActionEvent e)
	{
		gd1.setVisible(false);
	}
	void can2_actionPerformed(ActionEvent e)
	{
		
		gd2.setVisible(false);
	}
	void cancel1_actionPerformed()
	{
		g1.setVisible(false);
	}
	void cancel2_actionPerformed()
	{
		g2.setVisible(false);
	}
	void cancel3_actionPerformed()
	{
		g3.setVisible(false);
	}
	void cancel4_actionPerformed()
	{
		g4.setVisible(false);
	}
	void cancel5_actionPerformed()
	{
		g5.setVisible(false);
	}
	void combo1_actionPerformed()//查找课程名
	{
		dataBase db=new dataBase();
		String sql="select DISTINCT cname from course";
		ResultSet rs=db.getResult(sql);
		try
		{
			String rq=" ";
			while(rs.next())
			{
				rq=rs.getString(1);
				com.addItem(rq);
				com1.addItem(rq);
			}
		}
		catch(Exception ex)
		{
			ex.printStackTrace();
		}
	}
	void combo2_actionPerformed()//查找班级
	{
		dataBase db=new dataBase();
		String sql="select DISTINCT class from student";
		ResultSet rs=db.getResult(sql);
		try
		{
			String t=" ";
			while(rs.next())
			{
				t=rs.getString(1);
				com2.addItem(t);
			}
		}
		catch(Exception ex)
		{
			ex.printStackTrace();
		}
	}
	void course_query_actionPerformed(ActionEvent e)
	{
		dataBase db=new dataBase();
		if(text1.getText().equals(" "))
		{
			JOptionPane.showMessageDialog(null,"待查询值不能为空！");
		}
		else
		{
			String sql="select * from sc where cno=(select cno from course where cname='"+com.getSelectedItem().toString()+"')"
			+"and sno in(select sno from student)"
			+"and grade"+oper.getSelectedItem().toString()+"'"+Float.parseFloat(text1.getText())+"'";
			ResultSet rs=db.getResult(sql);
			bothQuery(rs);
		}
		
	}
	
	void class_query_actionPerformed(ActionEvent e)
	{
		dataBase db=new dataBase();
		if(text2.getText().equals(" "))
		{
			JOptionPane.showMessageDialog(null,"待查询值不能为空！");
		}
		else
		{
			String sql="select * from sc where cno=(select cno from course where cname='"+com1.getSelectedItem().toString()+"')"
			+"and sno in(select sno from student where class ='"+com2.getSelectedItem().toString()+"')"
			+"and grade"+oper1.getSelectedItem().toString()+"'"+Float.parseFloat(text2.getText())+"'";
			ResultSet rs=db.getResult(sql);
			bothQuery(rs);
			
		}
		
	}
	
	void bothQuery(ResultSet rs)
	{
			try
			{
				txt.setText("	学号	课程号	成绩\n");
				while(rs.next())
				{
					txt.append("	"+rs.getString(1)+"	"+rs.getString(2)+"	"+rs.getString(3)+"\n");
				}
			}
			catch(Exception ex)
			{
				ex.printStackTrace();
			}
	}
	void modify_actionPerformed()
	{
		String cno=txt4.getText().trim();
		String sno=txt5.getText().trim();
		String g=txt6.getText().trim();
		if(cno!=null&&sno!=null&&g!=null)
		{
			Float grade=Float.parseFloat(g);
			dataBase db=new dataBase();
			String s="select count(*) from sc where sno='"+sno+"'and cno='"+cno+"'";
			ResultSet r=db.getResult(s);
			int count=0;
			try
			{
				while(r.next())
				{
					count=r.getInt(1);
				}
			}
			catch(SQLException ex)
			{
				System.out.println(ex.getMessage());
				ex.printStackTrace();
			}
			if(count==0)
			{
				JOptionPane.showMessageDialog(null,"没有该学生选该课的选课信息！");
			}
			else
			{
				s="update sc set grade="+grade+"where sno='"+sno+"' and cno='"+cno+"'";
				if(db.executeSql(s))
				{
					JOptionPane.showMessageDialog(null,"修改成功！");
				}
				else
				{
					JOptionPane.showMessageDialog(null,"修改失败！");
				}
			}
		}
		else
		{
			JOptionPane.showMessageDialog(null,"文本不能为空！");
		}	
	}
	void in_actionPerformed()
	{
		String cno=txt1.getText().trim();
		String sno=txt2.getText().trim();
		String g=txt3.getText().trim();
		if(cno!=null&&sno!=null&&g!=null)
		{
			Float grade=Float.parseFloat(g);
			dataBase db=new dataBase();
			String s="select count(*) from sc where sno='"+sno+"'and cno='"+cno+"'";
			ResultSet r=db.getResult(s);
			int count=0;
			try
			{
				while(r.next())
				{
					count=r.getInt(1);
				}
			}
			catch(SQLException ex)
			{
				System.out.println(ex.getMessage());
				ex.printStackTrace();
			}
			if(count==0)
			{
				s="insert into sc values('"+sno+"','"+cno+"',"+grade+")";
				if(db.executeSql(s))
				{
					JOptionPane.showMessageDialog(null,"录入成功！");
				}
				else
				{
					JOptionPane.showMessageDialog(null,"录入失败！");
				}
			}
			else
			{
				JOptionPane.showMessageDialog(null,"该学生的信息已存在！");
			}
		}
		else
		{
			JOptionPane.showMessageDialog(null,"文本不能为空！");
		}	
	}
	void sno_delete_actionPerformed()
	{
		String sno=txt7.getText().trim();
		if(sno==null)
		{
			JOptionPane.showMessageDialog(null,"文本不能为空！");
		}
		else
		{
			dataBase db=new dataBase();
			String s="select count(*) from sc where sno='"+sno+"'";
			ResultSet r=db.getResult(s);
			int count=0;
			try
			{
				while(r.next())
				{
					count=r.getInt(1);
				}
			}
			catch(SQLException ex)
			{
				System.out.println(ex.getMessage());
				ex.printStackTrace();
			}
			if(count!=0)
			{
				s="delete from sc where sno='"+sno+"'";
				if(db.executeSql(s))
				{
					JOptionPane.showMessageDialog(null,"删除成功！");
				}
				else
				{
					JOptionPane.showMessageDialog(null,"删除失败！");
				}
			}
			else
			{
				JOptionPane.showMessageDialog(null,"没有该学生的信息！");
			}
		}
	}
	void class_delete_actionPerformed()
	{
		String cla=txt8.getText().trim();
		if(cla==null)
		{
			JOptionPane.showMessageDialog(null,"文本不能为空！");
		}
		else
		{
			dataBase db=new dataBase();
			String s="delete from sc where sno=(select sno from student where class='"+cla+"')";
			if(db.executeSql(s))
			{
				JOptionPane.showMessageDialog(null,"删除成功！");
			}
			else
			{
				JOptionPane.showMessageDialog(null,"删除失败！");
			}
		}
	}
	void course_delete_actionPerformed()
	{
		String cno=txt9.getText().trim();
		if(cno==null)
		{
			JOptionPane.showMessageDialog(null,"文本不能为空！");
		}
		else
		{
			dataBase db=new dataBase();
			String s="delete from sc where cno='"+cno+"'";
			if(db.executeSql(s))
			{
				JOptionPane.showMessageDialog(null,"删除成功！");
			}
			else
			{
				JOptionPane.showMessageDialog(null,"删除失败！");
			}
		}
	}
}