package mypackage;
import mypackage.DataBaseConnection;
import mypackage.Vchengji;
import java.sql.*;
import java.util.*;
import java.io.*;
public class VchengjiBean
{
	private Connection con=null;
	private Statement stmt=null;
  	private ResultSet rst=null;
	//連接數(shù)據(jù)庫(kù)
	public VchengjiBean()
	{
		this.con=DataBaseConnection.getConnection();
	}
	//獲得班級(jí)單科成績(jī)
	public Collection getSingleChengji(String grade, String stuClass,String courseId)throws Exception
	{
			stmt=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
			rst=stmt.executeQuery("select * from v_chengji where grade='"+grade+"' and stuClass='"+stuClass+"' and courseId='"+courseId+"'");
			Collection collet=new ArrayList();
			while(rst.next())
			{
				Vchengji vchengji=new Vchengji();
				vchengji.setStudentId(rst.getString("studentId"));
				vchengji.setStuName(rst.getString("stuName"));
				vchengji.setStuSex(rst.getString("stuSex"));
				vchengji.setGrade(rst.getString("grade"));
				vchengji.setAcademe(rst.getString("academe"));
				vchengji.setSpeciality(rst.getString("speciality"));
				vchengji.setStuClass(rst.getString("stuClass"));
				vchengji.setCourseId(rst.getString("courseId"));
				vchengji.setCourseName(rst.getString("courseName"));
				vchengji.setXuefen(rst.getString("xuefen"));
				vchengji.setCourseType(rst.getString("courseType"));
				vchengji.setTeacher(rst.getString("teacher"));
				vchengji.setScore(rst.getString("score"));
				vchengji.setXueqi(rst.getString("xueqi"));
				collet.add(vchengji);
			}
    		 if(rst!=null){
    		 	rst.close();
    		 	}
    		 if(stmt!=null)	{
    		 	stmt.close();
    		 }
    		 if(con!=null){
    		 	con.close();
    		 }
    		 
  	    	return collet;
	}
	//獲得班級(jí)學(xué)期課程
	public Collection getCourseName(String grade, String stuClass,String xueqi)throws Exception
	{
			stmt=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
			rst=stmt.executeQuery("select courseName from v_chengji where grade='"+grade+"' and stuClass='"+stuClass+"' and xueqi='"+xueqi+"'");
			Collection collet=new ArrayList();
			while(rst.next())
			{
				Vchengji vchengji=new Vchengji();
				vchengji.setCourseName(rst.getString("courseName"));
				collet.add(vchengji);
			}
    		 if(rst!=null){
    		 	rst.close();
    		 	}
    		 if(stmt!=null)	{
    		 	stmt.close();
    		 }
  	    	return collet;
	}
	//獲得班級(jí)學(xué)年課程
	public Collection getXuenianCourse(String grade, String stuClass,String xueqi)throws Exception
	{
			stmt=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
			rst=stmt.executeQuery("select courseName from v_chengji where grade='"+grade+"' and stuClass='"+stuClass+"' and xueqi like '%"+xueqi+"%'");
			Collection collet=new ArrayList();
			while(rst.next())
			{
				Vchengji vchengji=new Vchengji();
				vchengji.setCourseName(rst.getString("courseName"));
				collet.add(vchengji);
			}
    		 if(rst!=null){
    		 	rst.close();
    		 	}
    		 if(stmt!=null)	{
    		 	stmt.close();
    		 }
  	    	return collet;
	}
	//查詢
	public Collection executeQuery(String sql)throws Exception
  	{
	  	    stmt=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
	  	    rst=stmt.executeQuery(sql);
  	    	Collection collet=new ArrayList();
			while(rst.next())
			{
				Vchengji vchengji=new Vchengji();
				vchengji.setStudentId(rst.getString("studentId"));
				vchengji.setStuName(rst.getString("stuName"));
				vchengji.setStuSex(rst.getString("stuSex"));
				vchengji.setGrade(rst.getString("grade"));
				vchengji.setAcademe(rst.getString("academe"));
				vchengji.setSpeciality(rst.getString("speciality"));
				vchengji.setStuClass(rst.getString("stuClass"));
				vchengji.setCourseId(rst.getString("courseId"));
				vchengji.setCourseName(rst.getString("courseName"));
				vchengji.setXuefen(rst.getString("xuefen"));
				vchengji.setCourseType(rst.getString("courseType"));
				vchengji.setTeacher(rst.getString("teacher"));
				vchengji.setScore(rst.getString("score"));
				vchengji.setXueqi(rst.getString("xueqi"));
				vchengji.setFlag(rst.getString("flag"));
				collet.add(vchengji);
			}
    		 if(rst!=null){
    		 	rst.close();
    		 	}
    		 if(stmt!=null)	{
    		 	stmt.close();
    		 }
    		 if(con!=null){
    		 	con.close();
    		 }
    		 
  	    	return collet;
  	 }
  	 //關(guān)閉
  	 public void close() 
        {
        	try{
        		rst.close();
        		stmt.close();
        		con.close();
        	}
        	catch(SQLException e)
        	{
        	   e.printStackTrace();	
        	}
        	
        } 
}