defaultquestiondaoimpl.java

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

package examonline.service.impl;

import examonline.service.QuestionDAO;
import examonline.beans.Question;
import java.util.List;
import java.util.Map;

import examonline.service.ManagerFactory;
import examonline.service.DBManager;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import examonline.beans.User;
import java.util.*;
import java.sql.Statement;
import examonline.utils.*;


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

  private String updateSql =
      "update question set [note] = ?, detail = ?, standardAnswer=?, choices=?, [level]=? where id=?";
  private String deleteSql = "delete from question where id =?";
  private String saveSql = "insert into question(id,[note],detail,standardAnswer,choices,[level],type,score) values(?,?,?,?,?,?,?,?)";
  private String maxIdSql = "select max(id) as max from question";
  private String countSql = "select count(1) as count from question where detail like ?";
  public DefaultQuestionDAOImpl()
  {
  }

  /**
   * saveQuestion
   *
   * @param Question Question
   * @todo Implement this examonline.models.QuestionManager method
   */
  public int saveQuestion(Question question)
  {
    Connection conn = dm.openConnection();
    int result = 0;
    int id = getMaxId() + 1;
    if (id == 0)return result;
    try
    {
      PreparedStatement stat = conn.prepareStatement(saveSql);
      stat.setInt(1, id);
      stat.setString(2, question.getNote());
      stat.setString(3, question.getDetail());
      stat.setString(4, question.getStandardAnswer());
      stat.setString(5, question.getChoices());
      stat.setInt(6, question.getLevel());
      stat.setString(7, question.getType());
      stat.setInt(8, question.getScore());
      result = stat.executeUpdate();
    }
    catch (SQLException ex)
    {
      ex.printStackTrace();
    }
    finally
    {
      try
      {
        if (conn != null)
          conn.close();
      }
      catch (SQLException ex1)
      {
      }
    }
    return result;

  }

  /**
   * deleteQuestionById
   *
   * @param id String
   * @return int
   */
  public int deleteQuestionById(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;

  }

  /**
   * findQuestionById
   *
   * @param id String
   * @return Question
   * @todo Implement this examonline.models.QuestionManager method
   */
  public Question findQuestionById(int id)
  {
    Connection conn = dm.openConnection();
    Question question = null;
    try
    {
      PreparedStatement stat = conn.prepareStatement(findByIdSql);
      stat.setInt(1, id);
      ResultSet rs = stat.executeQuery();
      if (rs.next())
      {
        question = new Question();
        question.setId(rs.getInt("id"));
        question.setNote(rs.getString("note"));
        question.setDetail(rs.getString("detail"));
        question.setStandardAnswer(rs.getString("standardAnswer"));
        question.setChoices(rs.getString("choices"));
        question.setLevel(rs.getInt("level"));
      }
    }
    catch (SQLException ex)
    {
      ex.printStackTrace();
    }
    finally
    {
      try
      {
        if (conn != null)
          conn.close();
      }
      catch (SQLException ex1)
      {
      }
    }

    return question;

  }

  /**
   * findAllQuestions
   *
   * @return List
   * @todo Implement this examonline.models.QuestionManager method
   */
  public List findAllQuestions(int pageNumber)
  {
    return findQuestionByName("", pageNumber);
  }

  /**
   * updateQuestion
   *
   * @param Question Question
   * @return int
   * @todo Implement this examonline.models.QuestionManager method
   */
  public int updateQuestion(Question question)
  {
    Connection conn = dm.openConnection();
    int result = 0;
    try
    {
      PreparedStatement stat = conn.prepareStatement(updateSql);
      stat.setString(1, question.getNote());
      stat.setString(2, question.getDetail());
      stat.setString(3, question.getStandardAnswer());
      stat.setString(4, question.getChoices());
      stat.setInt(5, question.getLevel());
      stat.setInt(6, question.getId());
      
      result = stat.executeUpdate();      
      
    }
    catch (SQLException ex)
    {
      ex.printStackTrace();
    }
    finally
    {
      try
      {
        if (conn != null)
          conn.close();
      }
      catch (SQLException ex1)
      {
      }
    }
    return result;

  }

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

    Connection conn = dm.openConnection();
    List list = new ArrayList();
    Question question = 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())
      {
        question = new Question();
        question.setId(rs.getInt("id"));
        question.setNote(rs.getString("note"));
        question.setDetail(rs.getString("detail"));
        question.setStandardAnswer(rs.getString("standardAnswer"));
        question.setChoices(rs.getString("choices"));
        question.setLevel(rs.getInt("level"));
        list.add(question);
      }
    }
    catch (SQLException ex)
    {
      ex.printStackTrace();
    }
    finally
    {
      try
      {
        if (conn != null)
          conn.close();
      }
      catch (SQLException ex1)
      {
      }
    }

    return list;

  }

  public Question findQuestionByLevelRandom(int level)
  {
    Connection conn = dm.openConnection();
    Question question = null;
    try
    {
      PreparedStatement stat = conn.prepareStatement(findByLeveSqlCount);
      stat.setInt(1, level);
      ResultSet rs = stat.executeQuery();
      int count = 0;
      if (rs.next())
        count = rs.getInt(1);
      if(count==0)
      {
        return findQuestionRandom();
      }
      int random = new Random().nextInt(count);
      random=(random==0)?1:random;
      stat = conn.prepareStatement(findByLeveSql,
                                   ResultSet.TYPE_SCROLL_SENSITIVE,
                                   ResultSet.CONCUR_UPDATABLE);
      stat.setInt(1, level);
      rs = stat.executeQuery();
      rs.absolute(random);
      question = new Question();
      question.setId(rs.getInt("id"));
      question.setNote(rs.getString("note"));
      question.setDetail(rs.getString("detail"));
      question.setStandardAnswer(rs.getString("standardAnswer"));
      question.setChoices(rs.getString("choices"));
      question.setLevel(rs.getInt("level"));

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

    return question;

  }

  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 Question findQuestionRandom()
   {
     Connection conn = dm.openConnection();
     Question question = null;
     String selectAll="select * from question";
     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);
       question = new Question();
       question.setId(rs.getInt("id"));
       question.setNote(rs.getString("note"));
       question.setDetail(rs.getString("detail"));
       question.setStandardAnswer(rs.getString("standardAnswer"));
       question.setChoices(rs.getString("choices"));
       question.setLevel(rs.getInt("level"));

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

     return question;

   }



  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 Question getQuestionDetail(int id){
	  Connection conn = dm.openConnection();
	     Question question = null;
	     String selectAll="select * from question 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()){
		       question = new Question();
		       question.setId(rs.getInt("id"));
		       question.setNote(rs.getString("note"));
		       question.setDetail(rs.getString("detail"));
		       question.setStandardAnswer(rs.getString("standardAnswer"));
		       question.setChoices(rs.getString("choices"));
		       question.setLevel(rs.getInt("level"));
	       }
	     }
	     catch (SQLException ex)
	     {
	       ex.printStackTrace();
	     }
	     finally
	     {
	       try
	       {
	         if (conn != null)
	           conn.close();
	       }
	       catch (SQLException ex1)
	       {
	       }
	     }

	     return question;

  }
}