teacher_user.java

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

		{
			while(rs.next())
			{
				com4.addItem(rs.getString(1));
			}
		}
		catch(Exception ex)
		{
			ex.printStackTrace();
		}
	}

	void mySetText()
	{
		dataBase db=new dataBase();
		String sno[]=new String[30];
		String sname[]=new String[30];//一个班最多30个人
		String sql="select sno,name from student where class='"+com5.getSelectedItem()+"'";
		ResultSet rs=db.getResult(sql);
		try
		{
			int i=0;
			while(rs.next())
			{
				sno[i]=rs.getString(1);
				sname[i]=rs.getString(2);
				i++;
			}
			txt.setText("该班学生：\n");
			txt.append("	学号	姓名\n");
			for(int j=0;j<30;j++)
			{
				if((sno[j]!=null)&&sname[j]!=null)
				{
					txt.append("	"+sno[j]+"	"+sname[j]+"\n");
				}
			}
			text2.setText(sno[0]);
			text3.setText(sname[0]);
			text5.setText(sno[1]);
			text6.setText(sname[1]);
			text8.setText(sno[2]);
			text9.setText(sname[2]);
			text11.setText(sno[3]);
			text12.setText(sname[3]);
			text14.setText(sno[4]);
			text15.setText(sname[4]);
		}
		catch(Exception ex)
		{
			ex.printStackTrace();
		}
	}
	void course_query_actionPerformed(ActionEvent e)
	{
		dataBase db=new dataBase();
		if(txt1.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 where class in(select class from teacher1 where id='"+Login.name+"'))"
			+"and grade"+oper.getSelectedItem().toString()+"'"+Float.parseFloat(txt1.getText())+"'";
			ResultSet rs=db.getResult(sql);
			bothQuery(rs);
		}
		
	}
	
	void class_query_actionPerformed(ActionEvent e)
	{
		dataBase db=new dataBase();
		if(txt2.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(txt2.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 in_based_on_course_actionPerformed(ActionEvent e)
	{
		String s=txt3.getText().trim();
		String ss=cnumber(s);
		boolean w=(!txte1.getText().equals(" "))&&(!txte2.getText().equals(" "))&&(!txte3.getText().equals(" "))&&(!txte4.getText().equals(" "))
			&&(!txte5.getText().equals(" "))&&(!txte6.getText().equals(" "))&&(!txte7.getText().equals(" "))&&(!txte8.getText().equals(" "))
			&&(!txte9.getText().equals(" "))&&(!txte10.getText().equals(" "));
		if(!w)
		{
			JOptionPane.showMessageDialog(null,"文本不能为空！");
		}
		else if(ss==null)
		{
			JOptionPane.showMessageDialog(null,"课程不存在！");
		}
			
		if(w&(ss!=null))
		{
				dataBase db=new dataBase();
				String sql=" ";
				String []sno=new String[5];
				Float []grade=new Float[5];
				sno[0]=new String(txte1.getText().trim());
				sno[1]=new String(txte3.getText().trim());
				sno[2]=new String(txte5.getText().trim());
				sno[3]=new String(txte7.getText().trim());
				sno[4]=new String(txte9.getText().trim());
				grade[0]=new Float(txte2.getText().trim());
				grade[1]=new Float(txte4.getText().trim());
				grade[2]=new Float(txte6.getText().trim());
				grade[3]=new Float(txte8.getText().trim());
				grade[4]=new Float(txte10.getText().trim());
				boolean l=false;
				for(int i=0;i<5;i++)
				{
					sql="insert into sc values('"+sno[i]+"','"+ss+"','"+grade[i]+"')";
					if(db.executeSql(sql))
					{
						l=true;
					}
				}
				if(l)
				{
					JOptionPane.showMessageDialog(null,"提交成功！");
				}
				else
				{
					JOptionPane.showMessageDialog(null,"提交失败！");
				}
				 
			}
	}
	String cnumber(String cname)//根据课程名查出课程号，看某个老师是否教某课程
	{
		dataBase db=new dataBase();
		String sql="select cno from teacher1 where id='"+Login.name+"'and cno=(select cno from course where cname='"+cname+"')";
		ResultSet rs=db.getResult(sql);
		String s=" ";
		try
		{
			while(rs.next())
			{
				s=rs.getString(1);
			}
			System.out.println(s);
			return s;
		}
		catch(SQLException ex)
		{
			ex.printStackTrace();
			return null;
		}
	}
	void in_sigle_actionPerformed(ActionEvent e)
	{
		boolean w=(!text1.getText().equals(" "));
		if(!w)
		{
			JOptionPane.showMessageDialog(null,"文本不能为空！");
		}
		else
		{
			dataBase db=new dataBase();
			String cno=cno(com3.getSelectedItem().toString().trim());
			try
			{
				String sql="insert into sc values('"+com4.getSelectedItem().toString()+"','"+cno+"','"+Float.parseFloat(text1.getText())+"')";
				if(db.executeSql(sql))
				{
					JOptionPane.showMessageDialog(null,"提交成功！");
				}
				else
				{
					JOptionPane.showMessageDialog(null,"提交失败！");
				}
			}
			catch(Exception ex)
			{
				ex.printStackTrace();
			}
		}	
	}
	String cno(String cname)//根据课程名查出课程号
	{
		dataBase db=new dataBase();
		String cno=" ";
		String s="select cno from course where cname='"+cname+"'";
		ResultSet r=db.getResult(s);
		try
		{
			while(r.next())
			{
				cno=r.getString(1);
				return cno;
			}
		}
		catch(SQLException ex)
		{
			System.out.println(ex.getMessage());
			ex.printStackTrace();
			return null;
		}
		return null;
	}
	void in_based_on_class_actionPerformed()
	{
			dataBase db=new dataBase();
			try
			{
				String cno=cno(com6.getSelectedItem().toString().trim());
				String sql=" ";
				String []sno=new String[5];
				Float []grade=new Float[5];
				sno[0]=new String(text2.getText().trim());
				sno[1]=new String(text5.getText().trim());
				sno[2]=new String(text8.getText().trim());
				sno[3]=new String(text11.getText().trim());
				sno[4]=new String(text14.getText().trim());
				grade[0]=new Float(text4.getText().trim());
				grade[1]=new Float(text7.getText().trim());
				grade[2]=new Float(text10.getText().trim());
				grade[3]=new Float(text13.getText().trim());
				grade[4]=new Float(text16.getText().trim());
				boolean l=false;
				for(int i=0;i<5;i++)
				{
					sql="insert into sc values('"+sno[i]+"','"+cno+"','"+grade[i]+"')";
					if(db.executeSql(sql))
					{
						l=true;
					}
				}
				if(l)
				{
					JOptionPane.showMessageDialog(null,"提交成功！");
				}
				else
				{
					JOptionPane.showMessageDialog(null,"提交失败！");
				}
				
			}
			catch(Exception ex)
			{
				ex.printStackTrace();
			}
	}

	int optionNumber(String cname)//得出选择某课程的总人数
	{
		dataBase db=new dataBase();
		String sql="select count(*) from sc where  cno=(select cno from course where cname='"+com7.getSelectedItem().toString()+"')";
		ResultSet rs=db.getResult(sql);
		int s=0;
		try
		{
			while(rs.next())
			{
				s=rs.getInt(1);
				return s;	
			}
		}
		catch(SQLException ex)
		{
			ex.printStackTrace();
			return 0;
		}
		return 0;
	}
class monitor1 implements ActionListener
{
	public void actionPerformed(ActionEvent e)
	{
		dataBase db=new dataBase();
		int s=optionNumber(com7.getSelectedItem().toString().trim());
		String sql="select count(*) from sc where grade>=60 and cno=(select cno from course where cname='"+com7.getSelectedItem().toString()+"')";
		ResultSet rs=db.getResult(sql);
		int t=0;
		try
		{
			while(rs.next())
			{
				t=rs.getInt(1);	
			}
		}
		catch(SQLException ex)
		{
			ex.printStackTrace();
		}
		if(s!=0)
		{
			double f=(double)t/(double)s;
			txt.append("该课程的及格率是："+f+"\n");
		}
		else
		{
			JOptionPane.showMessageDialog(null,"没有选择该课的学生！");
		}
		
		
	}
}

class monitor2 implements ActionListener
{
	public void actionPerformed(ActionEvent e)
	{
		dataBase db=new dataBase();
		int s=optionNumber(com7.getSelectedItem().toString().trim());
		String sql="select count(*) from sc where grade>=90 and cno=(select cno from course where cname='"+com7.getSelectedItem().toString()+"')";
		ResultSet rs=db.getResult(sql);
		int t=0;
		try
		{
			while(rs.next())
			{
				t=rs.getInt(1);	
			}
		}
		catch(SQLException ex)
		{
			ex.printStackTrace();
		}
		if(s!=0)
		{
			double f=(double)t/(double)s;
			txt.append("该课程的优秀率是："+f+"\n");
		}
		else
		{
			JOptionPane.showMessageDialog(null,"没有选择该课的学生！");
		}
	}
}
class monitor3 implements ActionListener
{
	public void actionPerformed(ActionEvent e)
	{
		dataBase db=new dataBase();
		int s=optionNumber(com7.getSelectedItem().toString().trim());	
		String sql="select count(*) from sc where grade>=80 and cno=(select cno from course where cname='"+com7.getSelectedItem().toString()+"')";
		ResultSet rs=db.getResult(sql);
		int t=0;
		try
		{
			while(rs.next())
			{
				t=rs.getInt(1);	
			}
		}
		catch(SQLException ex)
		{
			ex.printStackTrace();
		}
		if(s!=0)
		{
			double f=(double)t/(double)s;
			txt.append("该课程的良好率是："+f+"\n");
		}
		else
		{
			JOptionPane.showMessageDialog(null,"没有选择该课的学生！");
		}
		
	}
}

class monitor4 implements ActionListener
{
	public void actionPerformed(ActionEvent e)
	{
		g7.setVisible(false);
	}
}
class monitor5 implements ActionListener
{
	public void actionPerformed(ActionEvent e)
	{
		g8.setVisible(false);
	}
}
int classOptionNumber(String cname,String clas)//某班选择某课程的总人数
{
		dataBase db=new dataBase();
		String sql="select count(*) from sc where cno=(select cno from course where cname='"+cname+"') and sno in (select sno from student where class='"
		+clas+"')";
		ResultSet rs=db.getResult(sql);
		int s=0;
		try
		{
			while(rs.next())
			{
				s=rs.getInt(1);	
				return s;
			}
		}
		catch(SQLException ex)
		{
			ex.printStackTrace();
			return 0;
		}
	return 0;
}
class monitor6 implements ActionListener
{
	public void actionPerformed(ActionEvent e)
	{
		dataBase db=new dataBase();
		int s=classOptionNumber(com8.getSelectedItem().toString().trim(),com9.getSelectedItem().toString().trim());
		String sql="select count(*) from sc where cno=(select cno from course where cname='"+com8.getSelectedItem().toString()+"') and grade>=60";
		ResultSet rs=db.getResult(sql);
		int t=0;
		try
		{
			while(rs.next())
			{
				t=rs.getInt(1);	
			}
		}
		catch(SQLException ex)
		{
			ex.printStackTrace();
		}
		if(s!=0)
		{
			double f=(double)t/(double)s;
			txt.append("该班课程的及格率是："+f+"\n");
		}
		else
		{
			JOptionPane.showMessageDialog(null,"没有选择该课的学生！");
		}
		
	}
}

class monitor7 implements ActionListener
{
	public void actionPerformed(ActionEvent e)
	{
		dataBase db=new dataBase();
		int s=classOptionNumber(com8.getSelectedItem().toString().trim(),com9.getSelectedItem().toString().trim());
		String sql="select count(*) from sc where grade>=90 and cno=(select cno from course where cname='"+com7.getSelectedItem().toString()+"')";
		ResultSet rs=db.getResult(sql);
		int t=0;
		try
		{
			while(rs.next())
			{
				t=rs.getInt(1);	
			}
		}
		catch(SQLException ex)
		{
			ex.printStackTrace();
		}
		if(s!=0)
		{
			double f=(double)t/(double)s;
			txt.append("该班课程的优秀率是："+f+"\n");
		}
		else
		{
			JOptionPane.showMessageDialog(null,"没有选择该课的学生！");
		}
		
	}
}

class monitor8 implements ActionListener
{
	public void actionPerformed(ActionEvent e)
	{
		dataBase db=new dataBase();
		int s=classOptionNumber(com8.getSelectedItem().toString().trim(),com9.getSelectedItem().toString().trim());

		String sql="select count(*) from sc where grade>=80 and cno=(select cno from course where cname='"+com7.getSelectedItem().toString()+"')";
		ResultSet rs=db.getResult(sql);
		int t=0;
		try
		{
			while(rs.next())
			{
				t=rs.getInt(1);	
			}
		}
		catch(SQLException ex)
		{
			ex.printStackTrace();
		}
		if(s!=0)
		{
			double f=(double)t/(double)s;
			txt.append("该班课程的良好率是："+f+"\n");
		}
		else
		{
			JOptionPane.showMessageDialog(null,"没有选择该课的学生！");
		}
		
	}
}

}