questionresultdbdao.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.QuestionResultDao;
import org.fangsoft.testcenter.model.Question;
import org.fangsoft.testcenter.model.QuestionResult;

public class QuestionResultDBDao implements QuestionResultDao {
	private static final QuestionResultDBDao qrdao=new QuestionResultDBDao();
	public static final QuestionResultDBDao getInstance(){
		return qrdao;
	}
	private void close(Connection conn){
		if(conn!=null) 
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
	}
	
	//implement findByTRid
	private static final String sql_findByTRid="select * from QUESTIONRESULT where TR_ID=?";
	public List<QuestionResult> findByTRid(int TRid){
		Connection conn=null;
		List<QuestionResult> qrList=new ArrayList<QuestionResult>();
		try {
			conn=ConnectionFactory.getConnection();
			PreparedStatement ps=conn.prepareStatement(sql_findByTRid);
			ps.setInt(1, TRid);
			ResultSet rs=ps.executeQuery();
			while(rs.next()){
				QuestionResult qr=new QuestionResult();
				qr.setId(rs.getInt("QR_ID"));
				qr.setTR_ID(rs.getInt("TR_ID"));
				Question q=new Question();
				q.setId(rs.getInt("Q_ID"));
				qr.setQuestion(q);
				qr.setAnswer(rs.getString("ANSWER"));
				qr.setScore(rs.getInt("SCORE"));
				qr.setResult(rs.getInt("RESULT")>0?true:false);
				qrList.add(qr);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			this.close(conn);
		}
		return qrList;
	}
	
	//implement insert
	private static final String sql_insert="insert into QUESTIONRESULT(QR_ID,TR_ID,Q_ID,ANSWER,SCORE,RESULT) values(?,?,?,?,?,?)";
	public void insert(QuestionResult qr){
		Connection conn=null;
		try {
			conn=ConnectionFactory.getConnection();
			conn.setAutoCommit(false);
			PreparedStatement stmt=conn.prepareStatement("select SEQ_QUESTIONRESULT.nextval from dual");
			ResultSet rs=stmt.executeQuery();
			int id=0;
			if(rs.next()) id=rs.getInt(1);
			PreparedStatement ps=conn.prepareStatement(sql_insert);
			ps.setInt(1,id);
			ps.setInt(2, qr.getTR_ID());
			ps.setInt(3,qr.getQuestion().getId());
			ps.setString(4, qr.getAnswer());
			ps.setInt(5, qr.getScore());
			ps.setInt(6, qr.getResult()==true?1:0);
			ps.executeUpdate();
			qr.setId(id);
			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 QUESTIONRESULT set TR_ID=?,Q_ID=?,ANSWER=?,SCORE=?,RESULT=? where QR_ID=?";
	public void update(QuestionResult qr){
		Connection conn=null;
		try {
			conn=ConnectionFactory.getConnection();
			PreparedStatement ps=conn.prepareStatement(sql_update);
			ps.setInt(1, qr.getTR_ID());
			ps.setInt(2, qr.getQuestion().getId());
			ps.setString(3, qr.getAnswer());
			ps.setInt(4, qr.getScore());
			ps.setInt(5, qr.getResult()==true?1:0);
			ps.setInt(6, qr.getId());
			ps.executeUpdate();
			qr.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 QUESTIONRESULT where TR_ID=?";
	public void delete(int TR_id){
		Connection conn=null;
		try {
			conn=ConnectionFactory.getConnection();
			PreparedStatement ps=conn.prepareStatement(sql_delete);
			ps.setInt(1, TR_id);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		}finally{
			this.close(conn);
		}
	}
}