question.java

J2EE指南

				pr_SplitPage存储过程共有6个参数:
				1-总页数/int/输出参数
				2-查询所用的表名/String/输入参数(必须)
				3-需要查看的页码(页码为88888888时只返回总页数,默认为1)/int/输入
				4-每页显示的记录数(默认为10条)/int/输入
				5-分页时用到的where子句(完整的where子句,默认为空)/String/输入
				6-分页时用到的排序字符串(完整的order by子句,默认为空,只查看总页数时不用输入，但要返回结果集时必须输入)/String/输入
				
				返回0表示成功,返回-1表示排序字符串order by子句为空
				警告：此存储过程使用临时表，并在临时表中产生新的标识字段，如果原表中有标识字段，
            则不能正常工作，可以做一个原表的视图再使用此存储过程
			*/			
			
			ps=con.prepareCall("{call pr_splitPage(?,?,?,?,?)}");
			ps.registerOutParameter(1,java.sql.Types.INTEGER);
			ps.setString(2,"View_Question");
			ps.setInt(3,88888888);//用于返回总页数
			ps.setInt(4,pageSizes);
			ps.setString(5,whereStr);
			ps.executeUpdate();//首先返回总页数,存储过程不能即返回总页数又返回记录集，具体用法还要再研究
			this.totalPage=ps.getInt(1);
						
			ps=con.prepareCall("{call pr_splitPage(?,?,?,?,?,?)}");
			ps.registerOutParameter(1,java.sql.Types.INTEGER);//用于返回记录集，此参数可不带
			ps.setString(2,"View_Question");
			ps.setInt(3,pageNumber);
			ps.setInt(4,pageSizes);
			ps.setString(5,whereStr);
			ps.setString(6,orderByStr);
			
			/*old01
			ps.setString(1,(this.Classes==null||this.Classes.equals(""))?"'%'":("'%"+this.Classes+"%'"));
			ps.setString(2,(this.Mark==null||this.Mark.equals(""))?"'%'":("'%"+this.Mark+"%'"));
			ps.setString(3,(this.QuestText==null||this.QuestText.equals(""))?"'%'":("'%"+this.QuestText+"%'"));
			if(this.TestTypeID!=0) ps.setInt(4,this.TestTypeID);
			*/
			
			rs=ps.executeQuery();//返回记录集
			bResult=rs.next();
			if(bResult)
			{
				do
				{
					vResults.add(new QuestionData(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getString(6),rs.getString(7),rs.getString(8),rs.getString(9),rs.getString(10),rs.getString(11),rs.getInt(12)));
				}while(rs.next());
			}
		}
		catch(Exception ex)
		{
			System.out.println("FindAllDeleted Question failed:"+ex.getMessage());
			throw new SQLException("FindAllDeleted Question failed!");
		}
		finally
		{
			try{con.close();ps.close();}
			catch(SQLException sqlex){System.out.println("FindAllDeleted Close Question Error:"+sqlex.getMessage());}
		}
		if(bResult)
		{return vResults;}
		else
		{return null;}
	}
	
	public void insert()throws SQLException
	{
		sql="insert into Question (Classes,QuestText,QuestTextA,QuestTextB,QuestTextC,QuestTextD,Answer,TestTypeID,Mark) values(?,?,?,?,?,?,?,?,?)";
		Connection con;
		PreparedStatement ps=null;
		
		try{con=db.connectToDB();}
		catch(Exception ex){throw new SQLException("Connect database failed!");}
		boolean bTemp;
		try
		{

			ps=con.prepareStatement(sql);
			ps.setString(1,Classes);
			ps.setString(2,QuestText);
			ps.setString(3,QuestTextA);
			ps.setString(4,QuestTextB);
			ps.setString(5,QuestTextC);
			ps.setString(6,QuestTextD);
			ps.setString(7,Answer);
			ps.setInt(8,TestTypeID);
			ps.setString(9,Mark);
			int rowCount=ps.executeUpdate();
			if(rowCount==0)
			{
				throw new SQLException("Insert Question failed!");
			}
		}
		catch(SQLException sqlex)
		{
			System.out.println("Insert Question Error:"+sqlex.getMessage());
			throw new SQLException("Insert Question Error!");
		}
		finally
		{
			try{con.close();ps.close();}
			catch(SQLException sqlex){System.out.println("Insert Close Question Error:"+sqlex.getMessage());}
		}
	}
	
	public void update()throws SQLException,Exception
	{
		sql="update Question set Classes=?,QuestText=?,QuestTextA=?,QuestTextB=?,QuestTextC=?,QuestTextD=?,Answer=?,TestTypeID=?,Mark=? where ID=?";
		Connection con;
		PreparedStatement ps=null;
		try{con=db.connectToDB();}
		catch(Exception ex){throw new SQLException("Connect database failed!");}
		try
		{
			ps=con.prepareStatement(sql);
			ps.setString(1,Classes);
			ps.setString(2,QuestText);
			ps.setString(3,QuestTextA);
			ps.setString(4,QuestTextB);
			ps.setString(5,QuestTextC);
			ps.setString(6,QuestTextD);
			ps.setString(7,Answer);
			ps.setInt(8,TestTypeID);
			ps.setString(9,Mark);
			ps.setInt(10,ID);
			int rowCount=ps.executeUpdate();
			if(rowCount==0)
			{
				throw new SQLException("Update Question failed!");
			}
		}
		catch(SQLException sqlex)
		{
			System.err.println("Update Question Error:"+sqlex.getMessage());
			throw new SQLException("Update Question Error:"+sqlex.getMessage());
		}
		catch(Exception e)
		{
			System.out.println("Other Of Update Question Error:"+e.getMessage());
			throw new Exception("Other Of Update Question Error");
		}
		finally
		{
			try{con.close();ps.close();}
			catch(SQLException sqlex){System.out.println("Update Close Question Error:"+sqlex.getMessage());}
		}
	}
	
	public void delete()throws SQLException
	{
		sql="update Question set Del='1' where ID=?";
		Connection con;
		PreparedStatement ps=null;
		try{con=db.connectToDB();}
		catch(Exception ex){throw new SQLException("Connect database failed!");}
		try
		{
			ps=con.prepareStatement(sql);
			ps.setInt(1,ID);
			int rowCount=ps.executeUpdate();
			if(rowCount==0)
			{
				throw new SQLException("Delete Question failed!");
			}
		}
		catch(SQLException sqlex)
		{
			System.out.println("Delete Question Error:"+sqlex.getMessage());
			throw new SQLException("Delete Question Error:"+sqlex.getMessage());
		}
		finally
		{
			try{con.close();ps.close();}
			catch(SQLException sqlex){System.out.println("Delete Close Question Error:"+sqlex.getMessage());}
		}
	}
	
	public void realDelete()throws SQLException
	{
		sql="delete Question where ID=?";
		Connection con;
		PreparedStatement ps=null;
		try{con=db.connectToDB();}
		catch(Exception ex){throw new SQLException("Connect database failed!");}
		try
		{
			ps=con.prepareStatement(sql);
			ps.setInt(1,ID);
			int rowCount=ps.executeUpdate();
			if(rowCount==0)
			{
				throw new SQLException("realDelete Question failed!");
			}
		}
		catch(SQLException sqlex)
		{
			System.out.println("realDelete Question Error:"+sqlex.getMessage());
			throw new SQLException("realDelete Question Error");
		}
		finally
		{
			try{con.close();ps.close();}
			catch(SQLException sqlex){System.out.println("realDelete Close Question Error:"+sqlex.getMessage());}
		}
	}
	
	public void restor()throws SQLException
	{
		sql="update Question set Del='0' where ID=?";
		Connection con;
		PreparedStatement ps=null;
		try{con=db.connectToDB();}
		catch(Exception ex){throw new SQLException("Connect database failed!");}
		try
		{
			ps=con.prepareStatement(sql);
			ps.setInt(1,ID);
			int rowCount=ps.executeUpdate();
			if(rowCount==0)
			{
				throw new SQLException("Restor Question failed!");
			}
		}
		catch(SQLException sqlex)
		{
			System.out.println("Restor Question Error:"+sqlex.getMessage());
			throw new SQLException("Restor Question Error");
		}
		finally
		{
			try{con.close();ps.close();}
			catch(SQLException sqlex){System.out.println("Restor Close Question Error:"+sqlex.getMessage());}
		}
	}
}