questiondbdao.java

纯JAVA代码的考试系统

package org.fangsoft.testcenter.dao.db;

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

import org.fangsoft.testcenter.dao.QuestionDao;
import org.fangsoft.testcenter.model.ChoiceItem;
import org.fangsoft.testcenter.model.Question;
import org.fangsoft.testcenter.model.Test;
import org.fangsoft.testcenter.model.TestResult;

public class QuestionDBDao implements QuestionDao {
	private static final QuestionDBDao qdao = new QuestionDBDao();

	public static final QuestionDBDao getInstance() {
		return qdao;
	}

	private void close(Connection conn) {
		if (conn != null)
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
	}

	// implement findByName
	private static final String sql_findByName = "select * from QUESTION where TT_TYPE=?";

	public List<Question> findByType(String questionType) {
		Connection conn = null;
		List<Question> questionList = new ArrayList<Question>();
		try {
			conn = ConnectionFactory.getConnection();
			PreparedStatement ps = conn.prepareStatement(sql_findByName);
			ps.setString(1, questionType);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				Question q = new Question();
				q.setId(rs.getInt("Q_ID"));
				q.setName(rs.getString("NAME"));
				q.setAnswer(rs.getString("ANSWER"));
				q.setScore(rs.getInt("SCORE"));
				q.setType(rs.getString("TT_TYPE"));
				questionList.add(q);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.close(conn);
		}
		return questionList;
	}

	// implement insert
	private static final String sql_insert = "insert into QUESTION(Q_ID,NAME,ANSWER,SCORE,TT_TYPE) values(?,?,?,?,?)";

	public void insert(Question question) {
		Connection conn = null;
		try {
			conn = ConnectionFactory.getConnection();
			conn.setAutoCommit(false);
			PreparedStatement stmt = conn
					.prepareStatement("select SEQ_QUESTION.nextval from dual");
			ResultSet rs = stmt.executeQuery();
			int Q_id = 0;
			if (rs.next())
				Q_id = rs.getInt(1);
			PreparedStatement ps = conn.prepareStatement(sql_insert);
			ps.setInt(1, Q_id);
			ps.setString(2, question.getName());
			ps.setString(3, question.getAnswer());
			ps.setInt(4, question.getScore());
			ps.setString(5, question.getType());
			ps.executeUpdate();
			conn.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} finally {
			this.close(conn);
		}
	}

	// implement update
	private static final String sql_update = "update QUESTION set NAME=?,ANSWER=?,SCORE=?,TT_TYPE=? where Q_ID=?";

	public void update(Question question) {
		Connection conn = null;
		try {
			conn = ConnectionFactory.getConnection();
			PreparedStatement ps = conn.prepareStatement(sql_update);
			ps.setString(1, question.getName());
			ps.setString(2, question.getAnswer());
			ps.setInt(3, question.getScore());
			ps.setString(4, question.getType());
			ps.setInt(5, question.getId());
			ps.executeUpdate();
			question.setId(0);
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} finally {
			this.close(conn);
		}
	}

	// implement delete
	private static final String sql_delete = "delete from QUESTION where NAME=?";

	public void delete(String QuestionName) {
		Connection conn = null;
		try {
			conn = ConnectionFactory.getConnection();
			PreparedStatement ps = conn.prepareStatement(sql_delete);
			ps.setString(1, QuestionName);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} finally {
			this.close(conn);
		}
	}

	public boolean loadQuestion(Test test) {
		String questionType = test.getName();
		List<Question> questionList = findByType(questionType);
		int numQuestion = questionList.size();
		for (int i = 0; i < numQuestion; i++) {
			Question q = questionList.get(i);
			test.addQuestion(q);
			List<ChoiceItem> item = (ChoiceItemDBDao.getInstance().findByQid(q
					.getId()));
			for (int j = 0; j < item.size(); j++) {
				q.addChoiceItem(item.get(j));
			}
			// String qTextName=questionList.get(i).getName();
			// String txtQuestion=IOUtil.readFromTXTFile(qFileName);

		}
		return true;
	}

	/*
	 * public void addQuestionToTest(Test test,Question q) { String
	 * txtQuestion=File2Object.toXML(q);
	 * IOUtil.saveAsFile(txtQuestion,DaoIOConfig.getInstance().getQuestionFilePath(test)+DaoIOConfig.getInstance().getQuestionFileName(test,
	 * q)); }
	 */

}