itemmapper.java

一个带界面的在线测试系统

/**
 * ItemMapper.java
 * create by ZZ, 2007.12.16
 */

package olts.persistent;
import java.util.*;
import olts.application.*;
import olts.database.*;
import olts.exception.*;
import java.sql.*;

/**
 * 该类用于用数据库持久化Item试题类
 * 和从数据库里取得一个Item
 * @author ZZ
 * @version 1.1
 */
public class ItemMapper {
	
	private DatabaseUtil dbUtil;
	private String tableName;
	private Map<String ,Integer> fieldId;
	
	private Item getItemFromResultSet(ResultSet rs)throws SQLException{
		
		int oid = rs.getInt("oid");
		int id = rs.getInt("id");
		int d = rs.getInt("difficulty");
		String c = rs.getString("content");
		String a = rs.getString("answer");
		int score = rs.getInt("score");
		int timelimit = rs.getInt("timelimit");
		String type = rs.getString("type");
		
		return new PersistentItem(oid, id, d,timelimit, c, a, score, type);
	}
	
	private int getNewOid()throws SQLException,PersistItemException{
		String sql = "select max(oid) from ";
		sql += this.tableName;
		ResultSet rs = this.dbUtil.query(sql);
		while (rs.next()){
			return rs.getInt(1) + 1;
		}
                rs.close();
                this.dbUtil.close();
		throw new PersistItemException();
	}
	
	public ItemMapper(DatabaseUtil db){
		this.dbUtil = db;
		
		this.fieldId = new HashMap<String ,Integer>();
		//设置字段的序号
		this.fieldId.put("oid", 1);
		this.fieldId.put("id", 2);
		this.fieldId.put("difficulty", 3);
		this.fieldId.put("content", 4);
		this.fieldId.put("answer", 5);
		this.fieldId.put("score", 6);
		this.fieldId.put("timelimit", 7);
		this.fieldId.put("type", 8);
		
		this.tableName = "Item";
			
	}
	
	/**
	 * 该方法返回在数据库里持久化的所有试题Item
	 */
	public List getAllItems()throws SQLException{
		
		String sql = "select * from ";
		sql += this.tableName;
		ResultSet rs = this.dbUtil.query(sql);
		
		List buf = new LinkedList();
			
		while (rs.next()){
			buf.add(this.getItemFromResultSet(rs));
		}
                rs.close();
		this.dbUtil.close();
		return buf;
	}
	
	/**
	 * 该方法用于持久化一个Item类
	 * @param i 需要持久化的试题
	 */
	public void persistItem(Item i)throws SQLException,PersistItemException{
		
		int oid = this.getNewOid() + 1;
                
		PersistentItem pi = new PersistentItem(oid,i);
		
		String sql = "insert into ";
		sql += this.tableName ;
		sql += " ";
		sql += "values(?,?,?,?,?,?,?,?)";
		
		this.dbUtil.prepare(sql);
		this.dbUtil.setPreparePara(this.fieldId.get("oid"), pi.getOid());
		this.dbUtil.setPreparePara(this.fieldId.get("id"), pi.getId());
		this.dbUtil.setPreparePara(this.fieldId.get("difficulty"), pi.getDifficulty());
		this.dbUtil.setPreparePara(this.fieldId.get("timelimit"), pi.getTimeLimit());
		this.dbUtil.setPreparePara(this.fieldId.get("score"), pi.getScore());
		this.dbUtil.setPreparePara(this.fieldId.get("content"), pi.getContent());
		this.dbUtil.setPreparePara(this.fieldId.get("answer"), pi.getAnswer());
		this.dbUtil.setPreparePara(this.fieldId.get("type"), pi.getType());
		//System.out.println(pi.getType());
		this.dbUtil.preparedUpdate();
                this.dbUtil.close();

	}
	
	/**
	 * 该方法用于更新了一条试题之后，在持久层同步这个改变
	 * @param oldId 旧的试题
	 * @param newItem 新的试题
	 */
	public void updateItem(int oldId, Item newItem)
	       throws SQLException,PersistItemException{
		
		this.removeItem(oldId);
                this.dbUtil.close();
		this.persistItem(newItem);
                this.dbUtil.close();
		
	}
	
	/**
	 * 该方法用于在删除一条试题之后，在持久层同步这个改变
	 * @param id 被删除的试题
	 */
	public void removeItem(int id)throws SQLException{
		
		String sql = "delete from ";
		sql += this.tableName;
		sql += " ";
		sql += "where id=?";
		
		this.dbUtil.prepare(sql);
		this.dbUtil.setPreparePara(1, id);
		this.dbUtil.preparedUpdate();
                this.dbUtil.close();
	}
	
	/**
	 * 得到存储在试题库里的所有试题的数目
	 */
	public int getItemNumber()throws SQLException{
		String sql = "select count(*) from ";
		sql += this.tableName;
		ResultSet rs = this.dbUtil.query(sql);
		while (rs.next()){
			return rs.getInt(1);
		}
                rs.close();
                this.dbUtil.close();
		return 0;
	}
	
	public void close()throws SQLException{
		this.dbUtil.close();
	}
	


	public static void main(String[] args) {
		// 测试该类
		
		String driver = "sun.jdbc.odbc.JdbcOdbcDriver";
		String url = "jdbc:odbc:driver={Microsoft Access Driver (*.mdb)};";
		url += "DBQ=./db.mdb";
		DatabaseUtil du = null;
		ItemMapper im = null;
		
		try{
			
			//测试连接
			du = new DatabaseUtil(driver,url,"","");
		    im = new ItemMapper(du);
			
		    int id = 2;
		    int diff = 1;
		    String content = "你知道我在等你吗？";
		    String answer = "true";
		    int score = 100;
		    int timelimit = 10;
	/*	    
		    Item item = new TrueFalseItem(id, diff, timelimit,
		    		content, answer, score);
		    

			//测试插入命令
		    //im.persistItem(item);
			//im.removeItem(item);
			
		    Item item2 = new SingleChoiceItem(item);
		    im.updateItem(item, item2);


		    List l = im.getAllItems();
		    for(int i =0; i < l.size(); i++){
		    	Item temp =(Item)l.get(i);
		    	System.out.println(temp);
		    }
			*/
		}catch(Exception e){
			
			System.out.println(e);
			
		}finally {
			if (du != null ){
				try{
					du.close();
				}catch(Exception e){
					System.out.println(e);
				}
			}
		}


	}

}

                /*
                String sql = "insert into ";
		sql += this.tableName ;
                sql += " ";
                sql += "values(";
                sql += (new Integer(pi.getOid())).toString();
                sql += ",";
                sql += (new Integer(pi.getId())).toString();
                sql += ",";
                sql += (new Integer(pi.getDifficulty())).toString();
                sql += ",";
                sql += pi.getContent();
                sql += ",";
                sql += pi.getAnswer();
                sql += ",";
                sql += (new Integer(pi.getScore())).toString();
                sql += ",";
                sql += (new Integer(pi.getTimeLimit())).toString();
                sql += ",";
                sql += pi.getType();
                sql += ")";*/