defaulttypedaoimpl.java

基于struts框架编写的在线考试系统

package examonline.service.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Random;

import examonline.beans.Type;
import examonline.service.DBManager;
import examonline.service.ManagerFactory;
import examonline.service.TypeDAO;
import examonline.utils.Constants;

public class DefaultTypeDAOImpl implements TypeDAO
{
	  DBManager dm = ManagerFactory.getDBManager();
	  private String findByIdSql = "select * from type where id =?";
	  private String findByLeveSql = "select * from type where level =?";
	  private String findByLeveSqlCount =
	      "select count(1) as count from type where level =?";

	  private String updateSql =
	      "update type set name = ? where id=?";
	  private String deleteSql = "delete from type where id =?";
	  private String saveSql = "insert into type(name) values(?)";
	  private String maxIdSql = "select max(id) as max from type";
	  private String countSql = "select count(1) as count from type where name like ?";
	  public DefaultTypeDAOImpl()
	  {
	  }

	  /**
	   * saveType
	   *
	   * @param Type Type
	   * @todo Implement this examonline.models.TypeManager method
	   */
	  public int saveType(Type type)
	  {
	    Connection conn = dm.openConnection();
	    int result = 0;
	    try
	    {
	      PreparedStatement stat = conn.prepareStatement(saveSql);
	    
	      stat.setString(1, type.getName());
	     
	      result = stat.executeUpdate();
	    }
	    catch (SQLException ex)
	    {
	      ex.printStackTrace();
	    }
	    finally
	    {
	      try
	      {
	        if (conn != null)
	          conn.close();
	      }
	      catch (SQLException ex1)
	      {
	      }
	    }
	    return result;

	  }

	  /**
	   * deleteTypeById
	   *
	   * @param id String
	   * @return int
	   */
	  public int deleteTypeById(int id)
	  {
	    Connection conn = dm.openConnection();
	    int result = 0;
	    try
	    {
	      PreparedStatement stat = conn.prepareStatement(deleteSql);
	      stat.setInt(1, id);
	      result = stat.executeUpdate();
	      conn.commit();
	    }
	    catch (SQLException ex)
	    {
	      ex.printStackTrace();
	    }
	    finally
	    {
	      try
	      {
	        if (conn != null)
	          conn.close();
	      }
	      catch (SQLException ex1)
	      {
	      }
	    }
	    return result;

	  }

	  /**
	   * findTypeById
	   *
	   * @param id String
	   * @return Type
	   * @todo Implement this examonline.models.TypeManager method
	   */
	  public Type findTypeById(int id)
	  {
	    Connection conn = dm.openConnection();
	    Type type = null;
	    try
	    {
	      PreparedStatement stat = conn.prepareStatement(findByIdSql);
	      stat.setInt(1, id);
	      ResultSet rs = stat.executeQuery();
	      if (rs.next())
	      {
	        type = new Type();
	        type.setId(rs.getInt("id"));
	        type.setName(rs.getString("name"));
	      }
	    }
	    catch (SQLException ex)
	    {
	      ex.printStackTrace();
	    }
	    finally
	    {
	      try
	      {
	        if (conn != null)
	          conn.close();
	      }
	      catch (SQLException ex1)
	      {
	      }
	    }

	    return type;

	  }

	  /**
	   * findAllTypes
	   *
	   * @return List
	   * @todo Implement this examonline.models.TypeManager method
	   */
	  public List findAllTypes(int pageNumber)
	  {
	    return findTypeByName("", pageNumber);
	  }

	  /**
	   * updateType
	   *
	   * @param Type Type
	   * @return int
	   * @todo Implement this examonline.models.TypeManager method
	   */
	  public int updateType(Type type)
	  {
	    Connection conn = dm.openConnection();
	    int result = 0;
	    try
	    {
	      PreparedStatement stat = conn.prepareStatement(updateSql);
	      stat.setString(1, type.getName());
	     
	      stat.setInt(2, type.getId());
	      
	      result = stat.executeUpdate();      
	      
	    }
	    catch (SQLException ex)
	    {
	      ex.printStackTrace();
	    }
	    finally
	    {
	      try
	      {
	        if (conn != null)
	          conn.close();
	      }
	      catch (SQLException ex1)
	      {
	      }
	    }
	    return result;

	  }

	  public List findTypeByName(String namelike, int pageNumber)
	  {
	  /*  String findNameLikeSql = "select top " + Constants.PAGE_SHOW_MAX +
	        " * from Type where id not in(select top " +
	        (Constants.PAGE_SHOW_MAX * pageNumber) + "  id from Type where detail like ? order by id) and detail like ? order by id";
	    String findFirstNameLikeSql =
	        "select top " + Constants.PAGE_SHOW_MAX +
	        "  * from Type where detail like ? order by id";*/
		  
		  String sql = "select * from type";

	    Connection conn = dm.openConnection();
	    List list = new ArrayList();
	    Type type = null;
	 //   String sql = findNameLikeSql;
	    if (pageNumber == 0)
	   //   sql = findFirstNameLikeSql;
	    try
	    {
	      PreparedStatement stat = conn.prepareStatement(sql);
	    //  stat.setString(1, "%" + namelike + "%");
	      if (pageNumber != 0)
	      {
	       // stat.setString(2, "%" + namelike + "%");
	      }
	      System.out.println(stat.toString());
	      ResultSet rs = stat.executeQuery();
	      while (rs.next())
	      {
	        type = new Type();
	        type.setId(rs.getInt("id"));
	        type.setName(rs.getString("name"));
	       
	        list.add(type);
	      }
	    }
	    catch (SQLException ex)
	    {
	      ex.printStackTrace();
	    }
	    finally
	    {
	      try
	      {
	        if (conn != null)
	          conn.close();
	      }
	      catch (SQLException ex1)
	      {
	      }
	    }

	    return list;

	  }

	

	  public int getRecordCount(String key)
	  {
	    Connection conn = dm.openConnection();
	    int count = -1;
	    try
	    {
	      PreparedStatement pstat = conn.prepareStatement(countSql);
	      pstat.setString(1,"%"+ key+"%");
	      ResultSet rs = pstat.executeQuery();
	      if (rs.next())
	        count = rs.getInt(1);

	    }
	    catch (SQLException ex)
	    {
	      ex.printStackTrace();
	    }
	    finally
	    {
	      try
	      {
	        if (conn != null)
	          conn.close();
	      }
	      catch (SQLException ex1)
	      {
	      }
	    }
	    return count;
	  }

	  public Type findTypeRandom()
	   {
	     Connection conn = dm.openConnection();
	     Type type = null;
	     String selectAll="select * from type";
	     try
	     {
	       PreparedStatement stat;
	       int count =getRecordCount("");
	       if(count==0)
	       {
	         return null;
	       }
	       int random = new Random().nextInt(count);
	       stat = conn.prepareStatement(selectAll,
	                                    ResultSet.TYPE_SCROLL_SENSITIVE,
	                                    ResultSet.CONCUR_READ_ONLY);
	       ResultSet rs = stat.executeQuery();
	       rs.absolute(random);
	       type = new Type();
	       type.setId(rs.getInt("id"));
	       type.setName(rs.getString("name"));
	     }
	     catch (SQLException ex)
	     {
	       ex.printStackTrace();
	     }
	     finally
	     {
	       try
	       {
	         if (conn != null)
	           conn.close();
	       }
	       catch (SQLException ex1)
	       {
	       }
	     }

	     return type;

	   }



	  private int getMaxId()
	  {
	    Connection conn = dm.openConnection();
	    int maxid = -1;
	    try
	    {
	      Statement stat = conn.createStatement();
	      ResultSet rs = stat.executeQuery(maxIdSql);
	      if (rs.next())
	        maxid = rs.getInt("max");

	    }
	    catch (SQLException ex)
	    {
	      ex.printStackTrace();
	    }
	    finally
	    {
	      try
	      {
	        if (conn != null)
	          conn.close();
	      }
	      catch (SQLException ex1)
	      {
	      }
	    }
	    return maxid;
	  }
	  public Type getTypeDetail(int id){
		  Connection conn = dm.openConnection();
		     Type type = null;
		     String selectAll="select * from type where id="+id;
		     try
		     {
		       PreparedStatement stat;
		       stat = conn.prepareStatement(selectAll,
		                                    ResultSet.TYPE_SCROLL_SENSITIVE,
		                                    ResultSet.CONCUR_READ_ONLY);
		       ResultSet rs = stat.executeQuery();
		       if(rs.next()){
			       type = new Type();
			       type.setId(rs.getInt("id"));
			       type.setName(rs.getString("name"));
		       }
		     }
		     catch (SQLException ex)
		     {
		       ex.printStackTrace();
		     }
		     finally
		     {
		       try
		       {
		         if (conn != null)
		           conn.close();
		       }
		       catch (SQLException ex1)
		       {
		       }
		     }

		     return type;

	  }
	}