xueshengcaoxingzipingbean.java

JSP开发的学生信息管理系统

package caoxingPingding;
import conn.DataBaseConnection;
import java.sql.*;
import java.util.*;
import java.io.*;
public class XueshengCaoXingZiPingBean
{
	private Connection con=null;
	public XueshengCaoXingZiPingBean()
	{
		this.con=DataBaseConnection.getConnection();
	}
	
	//操行评定分类
	public Collection getCaoXingFenLei()throws Exception
	{
			Statement stmt=null;
			ResultSet rst=null;
			Collection collet=null;
			try{
				stmt=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
				rst=stmt.executeQuery("select * from caoxingFenLei");
				collet=new ArrayList();
				while(rst.next())
				{
					CaoxingFenLei caoxingFenLei=new CaoxingFenLei();
					caoxingFenLei.setSortId(rst.getString("sortId"));
					caoxingFenLei.setSortName(rst.getString("sortName"));
					collet.add(caoxingFenLei);
				}
			}catch (SQLException ex) {
		      ex.printStackTrace();
		    }
  	    	return collet;
	}
	
	//获得学年
	 public String getXueNian(String studentId)
	  {
	  	PreparedStatement pstmt = null;
	    ResultSet rst = null;
	    StringBuffer sBuf=new StringBuffer();
	    try {
	      pstmt = con.prepareStatement("select grade from banji where classId=(select stuClass from xuesheng where studentId='"+studentId+"')");
	      rst = pstmt.executeQuery();
	      if(rst.next()) {
	      	int grade= Integer.parseInt(new String(rst.getString("grade")).trim());
	        sBuf.append("<option value='"+grade+"'>第一学年</option>\n");
	        sBuf.append("<option value='"+(grade+1)+"'>第二学年</option>\n");
	        sBuf.append("<option value='"+(grade+2)+"'>第三学年</option>\n");
	        sBuf.append("<option value='"+(grade+3)+"'>第四学年</option>\n");
	      }
	    }
	    catch (SQLException ex) {
	      ex.printStackTrace();
	      return "";
	    }
	    return sBuf.toString();
	  }
	  
	  //学年操行评定
	  public String getXueNianCXPD(String className)
	  {
	  	PreparedStatement pstmt = null;
	    ResultSet rst = null;
	    StringBuffer sBuf=new StringBuffer();
	    try {
	      pstmt = con.prepareStatement("select grade from banji where className='"+className+"'");
	      rst = pstmt.executeQuery();
	      if(rst.next()) {
	      	int grade= Integer.parseInt(new String(rst.getString("grade")).trim());
	        sBuf.append("<option value='"+grade+"'>第一学年</option>\n");
	        sBuf.append("<option value='"+(grade+1)+"'>第二学年</option>\n");
	        sBuf.append("<option value='"+(grade+2)+"'>第三学年</option>\n");
	        sBuf.append("<option value='"+(grade+3)+"'>第四学年</option>\n");
	      }
	    }
	    catch (SQLException ex) {
	      ex.printStackTrace();
	      return "";
	    }
	    return sBuf.toString();
	  }
	  
	  
	  //获得一个班的学生学号
	  public String getStudentID(String className)
	  {
	  	PreparedStatement pstmt = null;
	    ResultSet rst = null;
	    StringBuffer sBuf=new StringBuffer();
	    try {
	      pstmt = con.prepareStatement("select studentId from xuesheng where stuClass=(select classId from banji where className='"+className+"')");
	      rst = pstmt.executeQuery();
	      while(rst.next()) {
	      	String studentId= rst.getString("studentId").trim();
	        sBuf.append("<option value='"+studentId+"'>"+studentId+"</option>\n");
	      }
	    }
	    catch (SQLException ex) {
	      ex.printStackTrace();
	      return "";
	    }
	    return sBuf.toString();
	  }
	  
	  //更新项目得分
	  public String update(String itemId,String sortScore)
	{
		PreparedStatement pstmt=null;
		String str="";
		try
		{
			pstmt=con.prepareStatement("update banganHeDing set sortScore=? where itemId=?"); 
			pstmt.setString(1,sortScore);
			pstmt.setString(2,itemId);
			pstmt.execute();
			str="sucess";
		}
		catch(SQLException ex)
  	    	{
  	    		System.err.println(ex.getMessage());
  	    		str="failer";
  	    	}
  	    	finally
  	    	{
  	    		try{	
  	    		 if(pstmt!=null){
  	    		 	pstmt.close();
  	    		 }
  	    		 if(con!=null){
  	    		 	con.close();
  	    		 }
  	    		}catch(Exception e)
  	    		{
  	    			e.printStackTrace();
  	    		}
  	    	
  	    	}
  	    return str;	
	}
	
	//
	 public String update(String itemId,String itemName,String sortScore)
	{
		PreparedStatement pstmt=null;
		String str="";
		try
		{
			pstmt=con.prepareStatement("update xueshengCaoXingZiPing set itemName=?,sortScore=? where itemId=?"); 
			pstmt.setString(1,itemName);
			pstmt.setString(2,sortScore);
			pstmt.setString(3,itemId);
			pstmt.execute();
			str="sucess";
		}
		catch(SQLException ex)
  	    	{
  	    		System.err.println(ex.getMessage());
  	    		str="failer";
  	    	}
  	    	finally
  	    	{
  	    		try{	
  	    		 if(pstmt!=null){
  	    		 	pstmt.close();
  	    		 }
  	    		 if(con!=null){
  	    		 	con.close();
  	    		 }
  	    		}catch(Exception e)
  	    		{
  	    			e.printStackTrace();
  	    		}
  	    	
  	    	}
  	    return str;	
	}
	  //更新
	  public void executeUpdate(String sql)
  	    {
  	    	Statement stmt=null;

  	    	try{
  	    		stmt=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
  	    		stmt.executeUpdate(sql);

  	    	}catch(SQLException ex)
  	    	{
  	    		System.err.println(ex.getMessage());
  	    	}
         }
         
	  //查询
	  public ResultSet executeQuery(String sql)
  	{
  	     Statement stmt=null;
  	     ResultSet rst=null;
  	     try{
	  	      stmt=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
	  	      rst=stmt.executeQuery(sql);
  	    	}catch(SQLException ex)
  	    	{
  	    		System.err.println(ex.getMessage());
  	    	}
  	    	return rst;
  	 }
  	 
  	 //删除
	public String deleteCxpd(String itemId)
	{
		PreparedStatement pstmt=null;
		String str="";
		try
		{
			pstmt=con.prepareStatement("delete from xueshengCaoXingZiPing where itemId=?");
			pstmt.setString(1,itemId);
			pstmt.execute();
			str="sucess";
		}
		catch(SQLException ex)
  	    	{
  	    		System.err.println(ex.getMessage());
  	    		str="failer";
  	    	}
  	    	finally
  	    	{
  	    		try{
  	    		 if(pstmt!=null){
  	    		 	pstmt.close();
  	    		 }
  	    		 if(con!=null){
  	    		 	con.close();
  	    		 }
  	    		}catch(Exception e)
  	    		{
  	    			e.printStackTrace();
  	    		}

  	    	}
  	    return str;
	}
  	 //调用存储过程(班干核定用)
	  public ResultSet getCaoXingPingDing(String studentId,String xueNian)
	  {
	  	ResultSet rst=null;
	  	CallableStatement calStmt=null;
	  	String strSQL="{call pro_xueshengCaoXingZiPing(?,?)}";
	  	try{
	  		calStmt=con.prepareCall(strSQL);			
			calStmt.setString(1,studentId);
			calStmt.setString(2,xueNian);
			
			rst=calStmt.executeQuery();
	  	}catch(SQLException ex)
  	    	{
  	    		System.err.println(ex.getMessage());
  	    	}
  	    return rst;
	  }
	  
	  
	  //操行排名
	  public ResultSet getCaoXingPaiming(String className,String xueNian)
	  {
	  	ResultSet rst=null;
	  	CallableStatement calStmt=null;
	  	String strSQL="{call pro_xueshengCaoxingPaiming(?,?)}";
	  	try{
	  		calStmt=con.prepareCall(strSQL);			
			calStmt.setString(1,className);
			calStmt.setString(2,xueNian);
			
			rst=calStmt.executeQuery();
	  	}catch(SQLException ex)
  	    	{
  	    		System.err.println(ex.getMessage());
  	    	}
  	    return rst;
	  }
	  public ResultSet getCaoXingPaiming1(String className,String xueNian)
	  {
	  	ResultSet rst=null;
	  	CallableStatement calStmt=null;
	  	String strSQL="{call pro_xueshengCaoxingPaiming1(?,?)}";
	  	try{
	  		calStmt=con.prepareCall(strSQL);			
			calStmt.setString(1,className);
			calStmt.setString(2,xueNian);
			
			rst=calStmt.executeQuery();
	  	}catch(SQLException ex)
  	    	{
  	    		System.err.println(ex.getMessage());
  	    	}
  	    return rst;
	  }
	  
	 /* //德育基础分(存储过程)
	  public ResultSet getDeyuJichuFen(String className,String xuenian)
	  {
	  	ResultSet rst=null;
	  	CallableStatement calStmt=null;
	  	String strSQL="{call pro_deyufen(?,?)}";
	  	try{
	  		calStmt=con.prepareCall(strSQL);			
			calStmt.setString(1,className);
			calStmt.setString(2,xuenian);
			
			rst=calStmt.executeQuery();
	  	}catch(SQLException ex)
  	    	{
  	    		System.err.println(ex.getMessage());
  	    	}
  	    return rst;
	  }
	  
	  //社会实践基础分和互评分(存储过程)
	  public ResultSet getShijianJichuFen(String className,String xuenian)
	  {
	  	ResultSet rst=null;
	  	CallableStatement calStmt=null;
	  	String strSQL="{call pro_shijianfen(?,?)}";
	  	try{
	  		calStmt=con.prepareCall(strSQL);			
			calStmt.setString(1,className);
			calStmt.setString(2,xuenian);
			
			rst=calStmt.executeQuery();
	  	}catch(SQLException ex)
  	    	{
  	    		System.err.println(ex.getMessage());
  	    	}
  	    return rst;
	  }
	  */
	  //德育基础分更改
	  public String updateDeyuJichuFen(String studentId,String xuenian,String jichufen)
	  {
	  	PreparedStatement pstmt = null;
	    String str="";
	    try {
	      	pstmt = con.prepareStatement("update deyufen set jichufen=? where studentId=? and xuenian=?");
	      	pstmt.setString(1,jichufen);
	      	pstmt.setString(2,studentId);
			pstmt.setString(3,xuenian);
			pstmt.execute();
			str="sucess";
		}
		catch(SQLException ex)
  	    	{
  	    		System.err.println(ex.getMessage());
  	    		str="failer";
  	    	}
  	    	finally
  	    	{
  	    		try{	
  	    		 if(pstmt!=null){
  	    		 	pstmt.close();
  	    		 }
  	    		 if(con!=null){
  	    		 	con.close();
  	    		 }
  	    		}catch(Exception e)
  	    		{
  	    			e.printStackTrace();
  	    		}
  	    	
  	    	}
  	    return str;	
	  }
	  
	  //社会实践基础分和互评分更改
	  public String updateShijianJichuFen(String studentId,String xuenian,String jichufen,String hupingfen)
	  {
	  	PreparedStatement pstmt = null;
	    String str="";
	    try {
	      	pstmt = con.prepareStatement("update shijianfen set jichufen=?,hupingfen=? where studentId=? and xuenian=?");
	      	pstmt.setString(1,jichufen);
	      	pstmt.setString(2,hupingfen);
	      	pstmt.setString(3,studentId);
			pstmt.setString(4,xuenian);
			pstmt.execute();
			str="sucess";
		}
		catch(SQLException ex)
  	    	{
  	    		System.err.println(ex.getMessage());
  	    		str="failer";
  	    	}
  	    	finally
  	    	{
  	    		try{	
  	    		 if(pstmt!=null){
  	    		 	pstmt.close();
  	    		 }
  	    		 if(con!=null){
  	    		 	con.close();
  	    		 }
  	    		}catch(Exception e)
  	    		{
  	    			e.printStackTrace();
  	    		}
  	    	
  	    	}
  	    return str;	
	  }
	  //关闭
  	 public void close() 
        {
        	try{
        		if(con!=null){
        			con.close();
        		}
        	}
        	catch(SQLException e)
        	{
        	   e.printStackTrace();	
        	}
        	
        }
        
  	/*  
	public static void main(String[] args)throws Exception{
		XueshengCaoXingZiPingBean test=new XueshengCaoXingZiPingBean();
		String str=test.getStudentID("03级软件1班");
		System.out.println(str);
		
		Collection collet=test.getCaoXingFenLei();
		Iterator it=collet.iterator();
		while(it.hasNext())
		{
		  CaoxingFenLei caoxingFenLei=(CaoxingFenLei)it.next();
		  String sortId=caoxingFenLei.getSortId();
    	  String sortName=caoxingFenLei.getSortName();
    	  System.out.print(sortId);
    	  System.out.println(sortName);
    	} 
    	//String str=test.updateDeyuJichuFen("0368110051","1","47");
    	//System.out.print(str);
    	String str1=test.update("217","2");
    	System.out.print(str1);
	} */
       
}