operate_db.java

7、学生学籍管理系统 调查所在学校学生处、教务处

	{
		ArrayList al=new ArrayList();
		sql="select math from s_score";
		try
		{
			rs=st.executeQuery(sql);
			while(rs.next())
			{
				al.add(rs.getInt("math"));
			}
		}
		catch(Exception e)
		{
			
		}
		return al;
	}
	
	public ArrayList getALLname()
	{
		ArrayList al=new ArrayList();
		sql="select name from s_information";
		try
		{
			rs=st.executeQuery(sql);
			while(rs.next())
			{
				al.add(rs.getString("name"));
			}
		}
		catch(Exception e)
		{
			
		}
		return al;
	}
	
	public void getAvg()
	{
		int math=0,english=0,chinese=0;
		sql="select avg(math) math_avg,avg(chinese) chinese_avg,avg(english) english_avg from s_score";
		try
		{
			rs=st.executeQuery(sql);
			if(rs.next())
			{
				math=rs.getInt("math_avg");
				chinese=rs.getInt("chinese_avg");
				english=rs.getInt("english_avg");
			}
		}
		catch(Exception e)
		{
			
		}
		JOptionPane.showMessageDialog(null,"语文平均分："+chinese+".\n数学平均分："+math+".\n英语平均分："+english+"." );
	}
	
	public void getSum()
	{
		int math=0,english=0,chinese=0;
		sql="select sum(chinese) chinese_sum,sum(math) math_sum,sum(english) english_sum from s_score";
		try
		{
			rs=st.executeQuery(sql);
			if(rs.next())
			{
				chinese=rs.getInt("chinese_sum");
				math=rs.getInt("math_sum");
				english=rs.getInt("english_sum");
			}
		}
		catch(Exception e)
		{
			
		}
		JOptionPane.showMessageDialog(null,"语文总分："+chinese+".\n数学总分："+math+".\n英语总分："+english+"." );
	}
	
	public void getPerson1()
	{
		sql="select top 1 number,sum(chinese+math+english) sum_score from s_score group by number";
		String number="";
		int sum=0;
		try
		{
			rs=st.executeQuery(sql);
			if(rs.next())
			{
				number=rs.getString("number");
				sum=rs.getInt("sum_score");
			}
		}
		catch(Exception e)
		{
			
		}
		JOptionPane.showMessageDialog(null,"个人总分最高分为:"+sum+".\n该学生学号为:"+number );
	}
	
	public int getStudentCount()
	{
		int count=0;
		sql="select count(*) s_count from s_score";
		try
		{
			rs=st.executeQuery(sql);
			if(rs.next())
			{
				count=rs.getInt("s_count");
			}
		}
		catch(Exception e)
		{
			
		}
		return count;
	}
	
	
	
	public ArrayList getScoreOrderBychinese()
	{
		ArrayList al=new ArrayList();
		sql="select * from s_score order by chinese desc";
		try
		{
			rs=st.executeQuery(sql);
			while(rs.next())
			{
				S_score temp=new S_score();
				temp.setChinese(rs.getInt("chinese"));
				//temp.setMath(rs.getInt("math"));
				temp.setEnglish(rs.getInt("english"));
				//temp.setNumber(rs.getString("number"));
				al.add(temp);
			}
		}
		catch(Exception e)
		{
			
		}
		return al;
	}
	
	public ArrayList getNumberOrderBychinese()
	{
		ArrayList al=new ArrayList();
		sql="select number from s_score order by chinese desc";
		try
		{
			rs=st.executeQuery(sql);
			while(rs.next())
			{
				al.add((String)rs.getString("number"));
			}
		}
		catch(Exception e)
		{
			
		}
		return al;
	}
	
	public ArrayList getNameOrderByChinese()
	{
		ArrayList al=new ArrayList();
		sql="select  name from s_information where number in (select top 999999999 number from s_score order by chinese desc)";
		//int count=this.getStudentCount();
		
		try
		{
			rs=st.executeQuery(sql);
			while(rs.next())
			{
				al.add((String)rs.getString("name"));
			}
		}
		catch(Exception e)
		{
			
		}
		return al;
	}
	
	public ArrayList getMathOrderByChinese()
	{
		ArrayList al=new ArrayList();
		sql="select math from s_score order by chinese desc ";
		try
		{
			rs=st.executeQuery(sql);
			while(rs.next())
			{
				al.add((new Integer(rs.getInt("math")).toString()));
			}
		}
		catch(Exception e)
		{
			
		}
		return al;
	}
	
	public ArrayList getMathOrderBymath()
	{
		ArrayList al=new ArrayList();
		sql="select math from s_score order by math desc ";
		try
		{
			rs=st.executeQuery(sql);
			while(rs.next())
			{
				al.add((new Integer(rs.getInt("math")).toString()));
			}
		}
		catch(Exception e)
		{
			
		}
		return al;
	}
	
	
	public ArrayList getNameOrderBymath()
	{
		ArrayList al=new ArrayList();
		sql="select  name from s_information where number in (select top 999999999 number from s_score order by math desc)";
		//int count=this.getStudentCount();
		
		try
		{
			rs=st.executeQuery(sql);
			while(rs.next())
			{
				al.add((String)rs.getString("name"));
			}
		}
		catch(Exception e)
		{
			
		}
		return al;
	}
	
	public ArrayList getNumberOrderBymath()
	{
		ArrayList al=new ArrayList();
		sql="select number from s_score order by math desc";
		try
		{
			rs=st.executeQuery(sql);
			while(rs.next())
			{
				al.add((String)rs.getString("number"));
			}
		}
		catch(Exception e)
		{
			
		}
		return al;
	}
	
	public ArrayList getScoreOrderBymath()
	{
		ArrayList al=new ArrayList();
		sql="select * from s_score order by math desc";
		try
		{
			rs=st.executeQuery(sql);
			while(rs.next())
			{
				S_score temp=new S_score();
				temp.setChinese(rs.getInt("chinese"));
				//temp.setMath(rs.getInt("math"));
				temp.setEnglish(rs.getInt("english"));
				//temp.setNumber(rs.getString("number"));
				al.add(temp);
			}
		}
		catch(Exception e)
		{
			
		}
		return al;
	}
	
	public ArrayList getScoreOrderByenglish()
	{
		ArrayList al=new ArrayList();
		sql="select * from s_score order by english desc";
		try
		{
			rs=st.executeQuery(sql);
			while(rs.next())
			{
				S_score temp=new S_score();
				temp.setChinese(rs.getInt("chinese"));
				//temp.setMath(rs.getInt("math"));
				temp.setEnglish(rs.getInt("english"));
				//temp.setNumber(rs.getString("number"));
				al.add(temp);
			}
		}
		catch(Exception e)
		{
			
		}
		return al;
	}
	
	public ArrayList getNumberOrderByenglish()
	{
		ArrayList al=new ArrayList();
		sql="select number from s_score order by english desc";
		try
		{
			rs=st.executeQuery(sql);
			while(rs.next())
			{
				al.add((String)rs.getString("number"));
			}
		}
		catch(Exception e)
		{
			
		}
		return al;
	}
	public ArrayList getNameOrderByenglish()
	{
		ArrayList al=new ArrayList();
		sql="select  name from s_information where number in (select top 999999999 number from s_score order by english desc)";
		//int count=this.getStudentCount();
		
		try
		{
			rs=st.executeQuery(sql);
			while(rs.next())
			{
				al.add((String)rs.getString("name"));
			}
		}
		catch(Exception e)
		{
			
		}
		return al;
	}
	
	public ArrayList getMathOrderByenglish()
	{
		ArrayList al=new ArrayList();
		sql="select math from s_score order by english desc ";
		try
		{
			rs=st.executeQuery(sql);
			while(rs.next())
			{
				al.add((new Integer(rs.getInt("math")).toString()));
			}
		}
		catch(Exception e)
		{
			
		}
		return al;
	}
	
	public ArrayList getMathOrderByALL()
	{
		ArrayList al=new ArrayList();
		sql="select math from s_score  order by chinese+english+math desc  ";
		try
		{
			rs=st.executeQuery(sql);
			while(rs.next())
			{
				al.add((new Integer(rs.getInt("math")).toString()));
			}
		}
		catch(Exception e)
		{
			
		}
		return al;
	}
	
	public ArrayList getNumberOrderByALL()
	{
		ArrayList al=new ArrayList();
		sql="select number from s_score  order by chinese+english+math desc ";
		try
		{
			rs=st.executeQuery(sql);
			while(rs.next())
			{
				al.add((String)rs.getString("number"));
			}
		}
		catch(Exception e)
		{
			
		}
		return al;
	}
	
	public ArrayList getNameOrderByALL()
	{
		ArrayList al=new ArrayList();
		sql="select name from s_information where number in (select top 999999999 number from s_score  order by chinese+english+math desc)";
		//int count=this.getStudentCount();
		
		try
		{
			rs=st.executeQuery(sql);
			while(rs.next())
			{
				al.add((String)rs.getString("name"));
			}
		}
		catch(Exception e)
		{
			
		}
		return al;
	}
	
	public ArrayList getScoreOrderByALL()
	{
		ArrayList al=new ArrayList();
		sql="select * from s_score order by chinese+english+math desc";
		try
		{
			rs=st.executeQuery(sql);
			while(rs.next())
			{
				S_score temp=new S_score();
				temp.setChinese(rs.getInt("chinese"));
				//temp.setMath(rs.getInt("math"));
				temp.setEnglish(rs.getInt("english"));
				//temp.setNumber(rs.getString("number"));
				al.add(temp);
			}
		}
		catch(Exception e)
		{
			
		}
		return al;
	}
}