item.java

基于java swing的联机考试系统

package olts.logicoperation;
import java.sql.*;
import java.util.ArrayList;
import java.util.Random;
import olts.data.DatabaseAccess;
import olts.presentation.DatabaseTableColumn;

/**
 * 负责完成试题的插入、删除、修改和查找等功能的类
 * @author Xia Wei
 * @author 2007.12.18
 * @version 1.0
 */
public class Item {
	private final static String TABLE_NAME = "Item";
	private DatabaseAccess databaseAccess = null;
	private DatabaseTableColumn[] columns = null;  //DB表列信息

	/**
	 * 建立与数据库的连接
	 */
	public void open() throws Exception {
		try {
			databaseAccess = new DatabaseAccess("OnlineTestSystem");
		} catch (SQLException exc) {
			throw new Exception("不能打开试题数据库!");
		}
		// 初始化表列信息
		initializeColumns();
	}
	
	/**
	 * 获取有关数据库表的列信息
	 */
	public DatabaseTableColumn[] getColumns() { return columns; }
	
	/**
	 * 获取关键字段的信息
	 */
	public String getKeyField() { return "id"; }
	
	/**
	 * 获取难度系数字段的信息
	 * @return 难度系数字段的信息
	 */
	public String getDifficultyField() { return "difficulty"; }
	
	/**
	 * 添加一个试题记录
	 * @param aItem 试题记录对象，添加的试题记录从此载入
	 */
	public void add(ItemRecord aItem) throws Exception {
		// 查找是否已经存在相同编号的试题，如果已经存在则不能再插入
		if (findById(aItem.id) != null) 
			throw new Exception ("代码为" + aItem.id + "的试题已经存在!");
		String sql = "INSERT INTO " + TABLE_NAME + 
				" (id, itemkinds, difficulty, testtime, content, answer, score) " +
				" VALUES (?, ?, ?, ?, ?, ?, ?)";
		try {
			databaseAccess.prepare(sql);
			databaseAccess.setParameter(1, aItem.id);
			databaseAccess.setParameter(2, aItem.itemkinds);
			databaseAccess.setParameter(3, aItem.difficulty);
			databaseAccess.setParameter(4, aItem.testtime);
			databaseAccess.setParameter(5, aItem.content);
			databaseAccess.setParameter(6, aItem.answer);
			databaseAccess.setParameter(7, aItem.score);
			databaseAccess.preparedUpdate();
		} catch (SQLException exc) {
			exc.printStackTrace();
			throw new Exception("插入编号为" + aItem.id + "的试题失败!您输入的试题编号已被使用,请更换试题编号重新输入."); 
		}
	}
	
	/**
	 * 修改试题的信息，试题信息从试题记录对象参数中载入
	 * @param aItem 试题的信息
	 * @throws Exception
	 */
	public void update(ItemRecord aItem) throws Exception {
		String sql = "UPDATE " + TABLE_NAME + 
				" SET itemkinds = ?, difficulty = ?, testtime = ?, " + 
				"content = ?, answer = ?, score = ? WHERE id = ?";
		try {
			databaseAccess.prepare(sql);
			databaseAccess.setParameter(1, aItem.id);
			databaseAccess.setParameter(2, aItem.itemkinds);
			databaseAccess.setParameter(3, aItem.difficulty);
			databaseAccess.setParameter(4, aItem.testtime);
			databaseAccess.setParameter(5, aItem.content);
			databaseAccess.setParameter(6, aItem.answer);
			databaseAccess.setParameter(7, aItem.score);
			databaseAccess.preparedUpdate();
		} catch (SQLException exc) {
			exc.printStackTrace();
			throw new Exception("修改代码为" + aItem.id + "的试题失败!");
		}
	}
	
	/**
	 * 通过试题编号、字段名称、新值，修改更新试题记录某字段值，即某单元格的值。
	 * @param itmeID 要修改的试题的编号
	 * @param modifyField 要修改的字段
	 * @param value 该字段的新值
	 * @throws Exception
	 */
	public void update(String itmeID, String modifyField, Object value) 
			throws Exception {
		// 获取字段在SQL中的编码类型
		int sqlType = getFieldSqlType(modifyField);
		String sql = "UPDATE " + TABLE_NAME + " SET " + modifyField + " = ? " + 
				" WHERE id = ?";
		try {
			databaseAccess.prepare(sql);
			databaseAccess.setParameter(1, value, sqlType);
			databaseAccess.setParameter(2, itmeID);
			databaseAccess.preparedUpdate();
		} catch (SQLException exc) {
			exc.printStackTrace();
			throw new Exception("修改编号为" + itmeID + "的试题信息失败!");
		}
	}
	
	/**
	 * 通过试题编号，删除编号所在的试题记录
	 * @param itemId 试题编号
	 * @throws Exception
	 */
	public void remove(String itemId) throws Exception {
		String sql = "DELETE FROM " + TABLE_NAME + " WHERE id = ?";
		try {
			databaseAccess.prepare(sql);
			databaseAccess.setParameter(1, itemId);
			databaseAccess.preparedUpdate();
		} catch (SQLException exc) {
			exc.printStackTrace();
			throw new Exception("删除代码为" + itemId + "的试题信息失败!");
		}
	}
	// 功能：。调用数据层的功能将SQL查询语句返回的查询结果集处理成类似二维数组的形式。
	//      此二维数组形式的结果作为Swing表格的数据模型
	// 返回：以列表形式返回所有试题，试题信息放在ArrayList中，其中每一个元素为一个Object[]数组，
	//		该数组的长度与columns.length相同
	/**
	 * 查找所有试题
	 * @return 以列表形式返回所有试题，试题信息放在ArrayList中，其中每一个元素为一个Object[]数组
	 * @throws Exception
	 */
	public ArrayList findAll() throws Exception {
		String sql = "SELECT * FROM " + TABLE_NAME;
		ResultSet result = null;
		try {
			result = databaseAccess.query(sql);
		} catch (SQLException exc) {
			exc.printStackTrace();
			throw new Exception("查找所有的试题信息失败!");
		}
		// 将数据库中的数据装入ArrayList类型的data
		ArrayList data = new ArrayList();
		while (result.next()) {
			Object[] record = new Object[columns.length];
			for (int i = 0; i < columns.length; i++) {
				record[i] = result.getObject(columns[i].getField());
			}
			data.add(record);
		}
		result.close();
		return data;
	}
	
	/**
	 * 按难度系数取题
	 * @param difficulty 难度系数
	 * @return 以列表形式返回所有试题，试题信息放在ArrayList中，其中每一个元素为一个Object[]数组
	 * @throws Exception
	 */
	public ArrayList findItemByDifficulty(String difficulty) throws Exception {
		ArrayList data = null;
		if (difficulty.equalsIgnoreCase("0.1")||difficulty.equalsIgnoreCase("0.2")||difficulty.equalsIgnoreCase("0.3")){
			String sql = "SELECT * FROM " + TABLE_NAME + " WHERE difficulty = ?";
			ResultSet result = null;
			try {
				databaseAccess.prepare(sql);
				databaseAccess.setParameter(1, difficulty);
				result = databaseAccess.preparedQuery();
			} catch (SQLException exc) {
				exc.printStackTrace();
				throw new Exception("按试题难度查找试题失败!");
			}
			// 将数据库中的数据装入ArrayList类型的data
			data = new ArrayList();
			while (result.next()) {
				Object[] record = new Object[columns.length];
				for (int i = 0; i < columns.length; i++) {
					record[i] = result.getObject(columns[i].getField());
				}
				data.add(record);
			}
			result.close();
		} 
		return data;
	}
	
	/**
	 * 随机查找若干道试题
	 * @param amount 试题数量
	 * @return 查找的若干道试题
	 * @throws Exception
	 */
	public ArrayList findSomeItemRandom(int amount) throws Exception {
		Random ran = new Random(); 
		int num=ran.nextInt(100);
		String sql="select top " + amount + " * from " + TABLE_NAME + " order by rnd(-"+num+"*id)";
		ResultSet result = null;
		try {
			result = databaseAccess.query(sql);
		} catch (SQLException exc) {
			exc.printStackTrace();
			throw new Exception("随机查找的试题信息失败!");
		}
		// 将数据库中的数据装入ArrayList类型的data
		ArrayList data = new ArrayList();
		while (result.next()) {
			Object[] record = new Object[columns.length];
			for (int i = 0; i < columns.length; i++) {
				record[i] = result.getObject(columns[i].getField());
			}
			data.add(record);
		}
		result.close();
		return data;
	}
	
	/**
	 * 根据试题编号查找某一试题
	 * @param itemId 试题编号
	 * @return 以列表形式返回，每个列表元素为一个Object[]数组，该数组的长度与columns.length相同
	 * @throws Exception
	 */
	public ArrayList findByIdVersion2(String itemId) throws Exception {
		String sql = "SELECT * FROM " + TABLE_NAME + " WHERE id = ?";
		ResultSet result = null;
		try {
			databaseAccess.prepare(sql);
			databaseAccess.setParameter(1, itemId);
			result = databaseAccess.preparedQuery();
		} catch (SQLException exc) {
			exc.printStackTrace();
			throw new Exception("随机查找的试题信息失败!");
		}
		// 将数据库中的数据装入ArrayList类型的data
		ArrayList data = new ArrayList();
		while (result.next()) {
			Object[] record = new Object[columns.length];
			for (int i = 0; i < columns.length; i++) {
				record[i] = result.getObject(columns[i].getField());
			}
			data.add(record);
		}
		result.close();
		return data;
	}
	
	/**
	 * 根据试题编号查找某一试题
	 * @param itemId 试题编号
	 * @return 以试题记录结构返回，这些信息放在ItemRecord对象中，如果要查找的试题不存在则返回null
	 * @throws Exception
	 */
	public ItemRecord findById(String itemId) throws Exception {
		String sql = "SELECT * FROM " + TABLE_NAME + " WHERE id = ?";
		ResultSet result = null;
		try {
			databaseAccess.prepare(sql);
			databaseAccess.setParameter(1, itemId);
			result = databaseAccess.preparedQuery();
		} catch (SQLException exc) {
			exc.printStackTrace();
			throw new Exception("查找代码为" + itemId + "的试题信息失败!");
		}
		ItemRecord record = null;
		if (result.next()) {
			record = new ItemRecord();
			try {
				record.id = result.getString("id");
				record.itemkinds = result.getString("itemkinds");
				record.difficulty = result.getString("difficulty");
				record.testtime = result.getString("testtime");
				record.content = result.getString("content");
				record.answer = result.getString("answer");
				record.score = result.getInt("score");
			} catch (SQLException exc) {
				exc.printStackTrace();
				throw new Exception("装入编号为" + itemId + "的试题信息失败!");
			}
		}
		return record;
	}
	
	/**
	 * ItemRecord对象到数组的数据结构转化
	 * @param record ItemRecord对象
	 * @return Object[]数组，每个元素对应一个字段值，顺序与DB中表的相同
	 */
	public Object[] recordToArray(ItemRecord record) {
		Object[] array = new Object[columns.length];
		for (int i = 0; i < columns.length; i++) {
			String fieldName = columns[i].getField();
			if (fieldName.equals("id")) array[i] = record.id;
			else if (fieldName.equals("itemkinds")) array[i] = record.itemkinds;
			else if (fieldName.equals("difficulty")) array[i] =record.difficulty;
			else if (fieldName.equals("testtime")) array[i] = record.testtime;
			else if (fieldName.equals("content")) array[i] = record.content;
			else if (fieldName.equals("answer")) array[i] = record.answer;
			else if (fieldName.equals("score")) array[i] = record.score;
			else System.out.println("发生了内部错误，遇到了不可识别的字段名：" + fieldName);
		}
		return array;
	}
	
	/**
	 * 随机查找一道试题
	 * @return 以试题记录返回
	 * @throws Exception
	 */
	public ItemRecord findOneItemRandom() throws Exception {
		Random ran = new Random(); 
		int num=ran.nextInt(100);
		String sql="select top 1 * from " + TABLE_NAME + " order by rnd(-"+num+"*id)";
		ResultSet result = null;
		try {
			result = databaseAccess.query(sql);
		} catch (SQLException exc) {
			exc.printStackTrace();
			throw new Exception("查找所有的试题信息失败!");
		}
		ItemRecord record = null;
		if (result.next()) {
			record = new ItemRecord();
			try {
				record.id = result.getString("id");
				record.itemkinds = result.getString("itemkinds");
				record.difficulty = result.getString("difficulty");
				record.testtime = result.getString("testtime");
				record.content = result.getString("content");
				record.answer = result.getString("answer");
				record.score = result.getInt("score");
			} catch (SQLException exc) {
				exc.printStackTrace();
				throw new Exception("装入的试题信息失败!");
			}
		}
		return record;
		
	}
	
	/**
	 * 关闭数据库连接
	 * @throws Exception
	 */
	public void close() throws Exception {
		try {
			databaseAccess.close(); 
		} catch (Exception exc) {
			exc.printStackTrace();
			throw new Exception("关闭数据库" + TABLE_NAME + "失败!");
		}
	}
    
	/**
	 * 初始化数据表的字段信息
	 */
	private void initializeColumns() {
		columns = new DatabaseTableColumn[7];
		try {
			// 注意JAVA语言的数据类型与SQL里的类型的匹配！
			columns[0] = new DatabaseTableColumn("试题编号", "id", 
					Class.forName("java.lang.String"), 20, Types.CHAR);
			columns[1] = new DatabaseTableColumn("题型标识", "itemkinds", 
					Class.forName("java.lang.String"), 30, Types.VARCHAR);
			columns[2] = new DatabaseTableColumn("难度系数", "difficulty", 
					Class.forName("java.lang.String"), 20, Types.VARCHAR);
			columns[3] = new DatabaseTableColumn("答题时间", "testtime", 
					Class.forName("java.lang.String"), 20, Types.VARCHAR);
			columns[4] = new DatabaseTableColumn("题目内容", "content", 
					Class.forName("java.lang.String"), 100, Types.LONGVARCHAR);
			columns[5] = new DatabaseTableColumn("参考答案", "answer", 
					Class.forName("java.lang.String"), 20, Types.LONGVARCHAR);
			columns[6] = new DatabaseTableColumn("所占分值", "score", 
					Class.forName("java.lang.Integer"), 20, Types.INTEGER);
		} catch (ClassNotFoundException exc) {
			exc.printStackTrace();
		}
	}
	
	/**
	 * 由字段名获得字段的SQL类型
	 * @param fieldName 字段名
	 * @return 字段的SQL类型
	 */
	private int getFieldSqlType(String fieldName) {
		for (int i = 0; i < columns.length; i++) {
			if (fieldName.equals(columns[i].getField())) 
				return columns[i].getSqlType();
		}
		System.out.println("没有关于字段" + fieldName + "的类型信息!");
		return Types.VARCHAR;
	}
}