itemmapper.java

利用JAVA开发的简单考试系统

package model.persistency;
import model.exception.*;
import model.connection.*;
import java.sql.*;
import java.util.*;
import model.condition.test.TestByDifficulty;
/**
 * 用于与数据库的相关操作，
 * 包括添加题目，
 * 删除题目，
 * 修改题目，
 * 返回某一难度的题目，
 * 根据标识取题
 * @author Cynthia
 */
public class ItemMapper {
   //属性
    private HashMap cache;
    private Statement stmt;
    private PreparedStatement psm;
    private Connectable conn;

	/**
	 * 构造函数
	 */
    public ItemMapper(Connectable c){
    	conn = c;
    	cache = new HashMap();
        cache.put(" ",null);
    }

  /**   private static ItemMapper uniqueInstance;
     public static ItemMapper getInstance(Connectable c){
    	 if(uniqueInstance == null)
    		 uniqueInstance = new ItemMapper(c);
    	 return uniqueInstance;
     }
   */
    /**添加至缓存
     * @param t  题目信息
     */
    public void addCache(PersistentItem t){
    	cache.put(t.getId(), t);
    }
    /**
     * 用于寻找数据库中是否有oid的题目
     * @param oid            随机生成的数据库标识
     * @return               boolean类型 存在oid标识的题目返回true，不存在返回false
     * @throws SQLException  执行SQL语句时发生异常
     */
    public boolean findItem(int oid) throws SQLException{
    	int number = 0;
    	try{
                //寻找等于oid的sql语句
                stmt = conn.connectionDatabase().createStatement();
               // System.out.println("find");
              //  System.out.println("find2");
                String sql = "SELECT oid FROM ItemDatabase WHERE oid = "  + oid ;
                ResultSet rs = stmt.executeQuery(sql);
                rs.next();
                //返回找到的行数
                number = rs.getRow();
             //  System.out.println(number);
            }catch(SQLException e){
                System.out.println("createStatement failed in finding");
        }
        stmt.close();
    	if(number == 0) return  false;
    	else return true;
      }
    /**
     * 用于寻找数据库中是否有id的题目
     * @param id             题目标识
     * @return               boolean类型 存在id标识的题目返回true，不存在返回false
     * @throws SQLException  执行SQL语句时发生异常
     */
   public boolean findItem(String id) throws SQLException{
    	int number = 0;
        ResultSet rs = null;
           System.out.println(rs);
    	try{
           stmt = conn.connectionDatabase().createStatement();
         //  System.out.println("find");
         //  System.out.println(id);
           String sql = "SELECT * FROM ItemDatabase "+ "WHERE id =" + "'" + id + "'";
          // System.out.println(sql);
           rs = stmt.executeQuery(sql);
           rs.next();
           number = rs.getRow();
         //  System.out.println("find");
         //  System.out.println(rs);
         }
    	catch(SQLException e){
    		System.out.println("createStatement failed!");
    	}
    	if(number == 0)
               {  stmt.close();
                  return  false;}
    	else  {stmt.close();return true;}
      }
    /**
     * 将题目添加到数据库，同时添加到缓存中
     * @param item                 题目信息
     * @return                     int类型 题目在数据库中的id
     * @throws AddFailedException  添加时发生错误
     * @throws SQLException        连接SQL的错误
     */
    public int add(PersistentItem item)throws AddFailedException,SQLException{
       System.out.println("add");
    	int newOid = -1;
    	//随机产生一个0~99的id
    	do{
        System.out.println("随机");
    	Random random = new Random();
    	newOid = random.nextInt(100);
        System.out.println(newOid);
    	}while(findItem(newOid));//如果产生的id存在则再随机产生一个
    	// 如果产生的id不存在，则插入
        if((!findItem(newOid)) && (!findItem(item.getId()))){
        //  System.out.println("kai shi cha ru");
          stmt = conn.connectionDatabase().createStatement();
          String sql = "INSERT INTO ItemDatabase VALUES"  + "("  + newOid  + ","  + "'" + item.getId() +"'" + "," + "'" +  item.getDifficulty() + "'" + "," + item.getTime()  + "," + "'" + item.getContent() + "'" + ","  + "'"+ item.getAnswer() + "'" + ","  +  item.getScore()  + ")";
          stmt.executeUpdate(sql);
          stmt.close();
          // 添加到缓存
           addCache(item);
         }
        else  throw new AddFailedException("could not add!");
        return newOid;
    }
     /**修改题目，同时将修改的题目存入缓存
      *
      * @param item                      要修改的信息
      * @throws SQLException             sql语言连接错误
      * @throws UpdataFailedException    更新时出错
      * @throws FindFailedException      查找时出错
      */
 public void update(PersistentItem item)throws SQLException,UpdataFailedException,FindFailedException{
    	PersistentItem it;
    	//先从缓存中找看有没有要修改的项
    	if(cache.containsKey(item.getId())){
    	   it = (PersistentItem)cache.get(item.getId());
    	}
    	//如果缓存没有，就从数据库中搜索
    	else{
    	  if(findItem(item.getId())){
              //  System.out.println("findggg");
    		 //将找到的添加到缓存中
    		// addCache(it);
                {
                    //conn.connectionDatabase().setAutoCommit(false);
                     stmt = conn.connectionDatabase().createStatement();
                     String sql = "UPDATE ItemDatabase SET" + " difficulty = " + "'" + item.getDifficulty() + "'" + "," ;
                     sql = sql +  " [time] = " + item.getTime() + "," + " content = " + "'" + item.getContent() + "'" + "," ;
                     sql = sql + " answer = " + "'" + item.getAnswer() + "'" + "," + " score = " + item.getScore() + " WHERE id = " + "'" + item.getId() + "'";
                     System.out.println(sql);
                      stmt.executeUpdate(sql);
                      stmt.close();
                      conn.connectionDatabase().close();
                  }
            }
    	  else  throw new FindFailedException("could not find in update");
   	}
    	// 将修改的题目放入缓存中,将覆盖已有的it，因为题目的标识一样
    	addCache(item);

    }
  /**返回某一难度的题目，存入缓存,和一个ArrayList中
     *
     * @param dif            难度
     * @return               ArrayList类型 所有这一难度的题目集合
     * @throws SQLException  sql语句执行错误
     */
  public ArrayList getItemByDifficulty(String dif)throws SQLException{
    	ArrayList list = new ArrayList();
    	//先在缓存中找,找到后将它放入list中
    	 Iterator itr = cache.entrySet().iterator();
 	     Map.Entry e = (Map.Entry) itr.next();
          while(itr.hasNext()){
                e = (Map.Entry) itr.next();
         	String ID = (String)e.getKey();
         	PersistentItem it = (PersistentItem)cache.get(ID);
         	if(it.getDifficulty().compareTo(dif)==0){list.add(it);}
          }
          //在数据库中找，找到后添加到缓存和list中
         try{
            ResultSet rs = null;
            stmt = conn.connectionDatabase().createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
            String sql = "SELECT * FROM ItemDatabase "+ "WHERE difficulty =" + "'" + dif + "'";
            System.out.println(sql);
            rs = stmt.executeQuery(sql);
        while(rs.next()){
         //   System.out.println("kkrs");
          PersistentItem ite = new PersistentItem(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getInt(4),rs.getString(5),rs.getString(6),rs.getFloat(7));
          list.add(ite);
          addCache(ite);
          }
         }catch(SQLException l){
        	 System.out.println("something wrong!");
         }
        return list;
    }
 /**按标识取一道试题
    *
    * @param ID                   试题标识
    * @return                     PersistentItem类型 返回这个标识的题目
    * @throws SQLException        连接sql错误
    * @throws FindFailedException 查找不到出错
    */
 public PersistentItem getItemById(String ID)throws SQLException,FindFailedException{
    	PersistentItem it;
        //首先在缓存中查找
    	if(cache.containsKey(ID)){
    	   it = (PersistentItem)cache.get(ID);
    	   return it;
    	}

       // 缓存中没有，在数据库寻找
    	if(findItem(ID)){
    		   ResultSet rs = null;
                   stmt = conn.connectionDatabase().createStatement();
                   String sql = "SELECT * FROM ItemDatabase "+ "WHERE id =" + "'" + ID + "'";
                   System.out.println(sql);
                   rs = stmt.executeQuery(sql);
                   rs.next();
                   it = new PersistentItem(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getInt(4),rs.getString(5),rs.getString(6),rs.getFloat(7));
   		    return it;
    	}
    	else throw new FindFailedException("could not find!");
    }
 /**
  * 按oid标识返回一道试题
  * @param OID                  数据库中的oid标识
  * @return                     一道题目信息
  * @throws FindFailedException 查找不到出错
  * @throws SQLException        sql连接出错
  * @throws FindFailedException   查找不到
  */
    public  PersistentItem getItemById(int OID)throws FindFailedException, SQLException{
    	PersistentItem it;
    	if(findItem(OID)){
                   ResultSet rs = null;
                   stmt = conn.connectionDatabase().createStatement();
                   String sql = "SELECT * FROM ItemDatabase "+ "WHERE oid =" + OID ;
                //   System.out.println(sql);
                   rs = stmt.executeQuery(sql);
               //    System.out.println("kaishi");
                   rs.next();
                //   System.out.println("kaishi22");
                   it = new PersistentItem(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getInt(4),rs.getString(5),rs.getString(6),rs.getFloat(7));
               //    System.out.println(it.getContent());
                   rs.close();
                   stmt.close();

                 //  System.out.println(it.getContent());
                   return it;
    	}
    	else throw new FindFailedException("could not find!");
    }
    /**  根据id删除一道题目
     *
     * @param ID     题目标识
     * @throws SQLException        sql连接出错
     * @throws FindFailedException   查找不到
     */
   public void remove(String ID)throws SQLException,FindFailedException{
	   PersistentItem it;
	   int OID;
           System.out.println("jdif");
           it = getItemById(ID);
           System.out.println(it.getAnswer());
       //先在数据库中将这道题目删除
    //   OID = it.getOid();
     if(findItem(ID)){
       try{
            System.out.println("gggjdif");
           stmt = conn.connectionDatabase().createStatement();
    	   String sql = "DELETE * FROM ItemDatabase WHERE id = " + "'" + ID + "'";
           System.out.println(sql);
           stmt.executeUpdate(sql);
           conn.connectionDatabase().close();
       }catch(SQLException e){
    	   System.out.println("sql exception");
       }
     }
     else throw new FindFailedException("CAN'T FIND THE ITEM"); //在缓存中删除
      if(cache.containsKey(ID)) cache.remove(it);
     }
     /** 得到数据库的题目总数
      * @throws SQLException        sql连接出错
      */
     public int getItemCount()throws SQLException{
         stmt = conn.connectionDatabase().createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
         ResultSet rs = stmt.executeQuery("SELECT * FROM ItemDatabase");
         rs.last();
         int count = rs.getRow();
         return count;
     }
  }//~~