sztzcanzhaobean.java

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

package suzhiTuoZhan;
import conn.DataBaseConnection;
import java.sql.*;
import java.util.*;
import java.io.*;
public class SztzCanZhaoBean
{
	private Connection con=null;
	public SztzCanZhaoBean()
	{
		this.con=DataBaseConnection.getConnection();
	}
	
	//素质拓展参照是否存在
	public boolean existSztzCanZhao(SztzCanZhao sztzCanZhao) {
        try {
            String referItemId= sztzCanZhao.getReferItemId();
            PreparedStatement pstmt = con.prepareStatement(
                    "select referItemId from sztzCanZhao where referItemId='" +referItemId + "'");     
            ResultSet rst = pstmt.executeQuery();
            if (rst.next()) {
                return true;
            }
        } catch (Exception ex) {
            return false;
        }
        return false;
    }
    
	//插入
	public String addSztzCanZhao(SztzCanZhao sztzCanZhao)
	{
		PreparedStatement pstmt=null;
		String str="";
		try
		{
			if (existSztzCanZhao(sztzCanZhao)) {
                return "exist"; //存在
            }
			pstmt=con.prepareStatement("insert into SztzCanZhao values(?,?,?,?,?)");
			pstmt.setString(1,sztzCanZhao.getReferItemId());
			pstmt.setString(2,sztzCanZhao.getSortId());
			pstmt.setString(3,sztzCanZhao.getItemName());
			pstmt.setString(4,sztzCanZhao.getXuefenScore());
			pstmt.setString(5,sztzCanZhao.getItemContent());
			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 updateSztzCanZhao(SztzCanZhao sztzCanZhao)
	{
		PreparedStatement pstmt=null;
		String str="";
		try
		{
			pstmt=con.prepareStatement("update sztzCanZhao set sortId=?,itemName=?,xuefenScore=?,itemContent=? where referItemId=?");
			pstmt.setString(1,sztzCanZhao.getSortId());
			pstmt.setString(2,sztzCanZhao.getItemName());
			pstmt.setString(3,sztzCanZhao.getXuefenScore());
			pstmt.setString(4,sztzCanZhao.getItemContent());
			pstmt.setString(5,sztzCanZhao.getReferItemId());
			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 deleteSztzCanZhao(String referItemId)
	{
		PreparedStatement pstmt=null;
		String str="";
		try
		{
			pstmt=con.prepareStatement("delete from sztzCanZhao where referItemId=?"); 
			pstmt.setString(1,referItemId);
			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 updateXueshengSztz(String itemId,String itemName,String xuefenScore)
	{
		PreparedStatement pstmt=null;
		String str="";
		try
		{
			pstmt=con.prepareStatement("update xueshengSuZhiZiPing set itemName=?,xuefenScore=? where itemId=?"); 
			pstmt.setString(1,itemName);
			pstmt.setString(2,xuefenScore);
			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 String deleteXueshengSztz(String itemId)
	{
		PreparedStatement pstmt=null;
		String str="";
		try
		{
			pstmt=con.prepareStatement("delete from xueshengSuZhiZiPing 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 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 ResultSet getSztzCanZhao()
	  {
	  	ResultSet rst=null;
	  	CallableStatement calStmt=null;
	  	String strSQL="{call pro_sztzCanZhao}";
	  	try{
	  		calStmt=con.prepareCall(strSQL);
			
			rst=calStmt.executeQuery();
	  	}catch(SQLException ex)
  	    	{
  	    		System.err.println(ex.getMessage());
  	    	}
  	    return rst;
	  }
  	 //关闭
  	 public void close() 
        {
        	try{
        		if(con!=null){
        			con.close();
        		}	
        	}
        	catch(SQLException e)
        	{
        	   e.printStackTrace();	
        	}
        	
        }
     
         //所有素质拓展分类
     public String getAllCaoxingFenLei()
  {
  	PreparedStatement pstmt = null;
    ResultSet rst = null;
    StringBuffer sBuf=new StringBuffer();
    try {
      pstmt = con.prepareStatement("select distinct sortId,sortName from suzhiTuoZhanFenLei");
      rst = pstmt.executeQuery();
      while(rst.next()) {
      	String sortId= rst.getString("sortId");
      	String sortName= rst.getString("sortName");
        sBuf.append("<option value='"+sortId+"'>"+sortName+"</option>\n");
      }
    }
    catch (SQLException ex) {
      ex.printStackTrace();
      return "";
    }
    finally
    {
    	try{
    		if(rst!=null){
    			rst.close();
    			}		
  	    	if(pstmt!=null){
  	    		pstmt.close();
  	    		}
  	    	if(con!=null){
  	    		con.close();
  	    	}	
  	    	
  	    	}catch(Exception e)
    		{
    			e.printStackTrace();
    		}
    }
    return sBuf.toString();
  }
  
   //所有班级
     public String getAllClass()
  {
  	PreparedStatement pstmt = null;
    ResultSet rst = null;
    StringBuffer sBuf=new StringBuffer();
    try {
      pstmt = con.prepareStatement("select distinct classId,className from banji");
      rst = pstmt.executeQuery();
      while(rst.next()) {
      	String classId= rst.getString("classId");
      	String className= rst.getString("className");
        sBuf.append("<option value='"+classId+"'>"+className+"</option>\n");
      }
    }
    catch (SQLException ex) {
      ex.printStackTrace();
      return "";
    }
    return sBuf.toString();
  }
  /*
  public static void main(String[] args){
  	SztzCanZhaoBean test=new SztzCanZhaoBean();
  	String str=test.updateXueshengSztz("1","sdfkldsf","0");
  	System.out.println(str);
  }*/
           
}