//會話Bean類

package selectstudent;

import javax.ejb.*;
import java.sql.*;
import java.util.*;
import javax.naming.*;

public class SelectBean implements SessionBean{
	SessionContext sessionContext;
	
	public void ejbCreate()throws CreateException{
	}
	
	public void ejbRemove(){
	}
	
	public void ejbActive(){
	}
	
	public void ejbPassivate(){
	}
	
	public void setSessionContext (SessionContext sessionContext){
		this.sessionContext=sessionContext;
	}
	public Collection selectStudent()throws SQLException,NamingException{
		Connection conn=null;
		Collection c=new ArrayList();
		
		InitialContext ctx=new InitialContext();
		DateSource ds=(DataSource)ctx.lookup("sqlserver");
		conn=ds.getConnection();

		Statement stmt=conn.createStatement();
		ResultSet rs=stmt.excuteQuery("SELECT * FROM student");
		
		while(rs.next()){
			Student student=new Student();
			student.setId(rs.getString("id");
			student.setName(rs.getString("name"));
			student.setAge(rs.getString("age"));
			student.setSex(rs.getString("sex"));
			student.setGrade(rs.getString("grade"));
			student.setMath(Integer.parseInt(rs.getString("mathimatics").trim()));
			student.setPhysics(Integer.parseInt(rs.getString("physics").trim()));
			student.setChemistry(Integer.parseInt(rs.getString("chemistry").trim()));
			student.setChinese(Integer.parseInt(rs.getString("chinese").trim()));
			student.setEnglish(Integer.parseInt(rs.getString("english").trim()));
			
			if check(student)){
				c.add(student);
			}
		}
		
		stmt.close();
		conn.close();
		return c;
	}
	
	private boolean Check(Student student){
		int math=student.getMath();
		int physics=student.getPhysics();
		int chemistry=student.getChemistry();
		int english=student.getEnglish();
		int chinese=student.getChinese();
		
		double avg=(math+physics+chemistry+english+chinese)/5.0;
		if(math<75||physics<75||chemistry<75||english<75||chinese<75){
			return false;
		}
		else{
			if(avg<85){
				return false;
			}
			else{
				return true;
			}
		}
	}
}