testresultdbdao.java

纯JAVA代码的考试系统

package org.fangsoft.testcenter.dao.db;

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

import org.fangsoft.testcenter.dao.TestResultDao;
import org.fangsoft.testcenter.model.Customer;
import org.fangsoft.testcenter.model.TestResult;

public class TestResultDBDao implements TestResultDao {
	private static final TestResultDBDao trdao = new TestResultDBDao();

	public static final TestResultDBDao getInstance() {
		return trdao;
	}

	public static final String sql_findByUesID = "select * from TestResult where UESID=?";

	public List<TestResult> findTestResultByUseID(String uesid) {
		List<TestResult> UesIDTestResult = new ArrayList<TestResult>();
		Connection con = null;
		try {
			con = ConnectionFactory.getConnection();
			PreparedStatement ps = con.prepareStatement(sql_findByUesID);
			ps.setString(1, uesid);
			ResultSet rs = ps.executeQuery();
			Customer ct = new Customer();
			ct.setUserId(uesid);
			while (rs.next()) {
				//int userId=rs.getString("UESID");
				TestResult tr = new TestResult();
				tr.setId(rs.getInt("TR_ID"));
				tr.setCustomer(ct);
				//tr.setUesid(rs.getString("UESID"));
				tr.setScore(rs.getInt("SCORE"));
				tr.setPass(rs.getInt("PASS"));
				tr.setStartTime(rs.getDate("STARTTIME"));
				tr.setEndTime(rs.getDate("ENDTIME"));
				UesIDTestResult.add(tr);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.close(con);
		}
		return UesIDTestResult;
	}

	public static final String sql_findByID = "select * from TESTRESULT where TR_ID=?";

	public TestResult findTestResultByTrID(int trid) {
		TestResult tr = new TestResult();
		Connection con = null;
		try {
			con = ConnectionFactory.getConnection();
			PreparedStatement ps = con.prepareStatement(sql_findByID);
			ps.setInt(1, trid);
			ResultSet rs = ps.executeQuery();
			Customer ct = new Customer();
			if (rs.next()) {
				ct.setUserId(rs.getString("UESID"));
				tr.setCustomer(ct);
				tr.setId(rs.getInt("TR_ID"));
				//tr.setUesid(rs.getString("UESID"));
				tr.setScore(rs.getInt("SCORE"));
				//tr.setResult(rs.getInt("PASS"));
				tr.setPass(rs.getInt("PASS"));
				tr.setStartTime(rs.getDate("STARTTIME"));
				tr.setEndTime(rs.getDate("ENDTIME"));
				return tr;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.close(con);

		}
		return null;
	}

	public static final String sql_save = "insert into TESTRESULT(T_ID,UESID,SCORE,PASS,STARTTIME,ENDTIME,TR_ID) values(?,?,?,?,?,?,?)";

	public void save(TestResult testResult) {
		Connection con = null;
		try {
			con = ConnectionFactory.getConnection();
			con.setAutoCommit(false);
			PreparedStatement stmt = con
					.prepareStatement("select SEQ_TESTRESULT.nextval from dual");
			ResultSet rs = stmt.executeQuery();
			int id = 0;
			if (rs.next())
				id = rs.getInt(1);
			PreparedStatement ps = con.prepareStatement(sql_save);
			ps.setInt(1, testResult.getTest().getId());
			ps.setString(2, testResult.getCustomer().getUserId());
			ps.setFloat(3, testResult.getScore());
			ps.setInt(4, testResult.Pass());
			ps.setTimestamp(5, (Timestamp) testResult.getStartTime());
			ps.setTimestamp(6, (Timestamp) testResult.getEndTime());
			ps.setInt(7, id);
			ps.executeUpdate();
			testResult.setId(id);
			con.commit();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			con.rollback();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.close(con);
		}
	}

	public static final String sql_update = "update TESTRESULT set T_ID=?,UESID=?,SCORE=?,PASS=?,STARTTIME=?,ENDTIME=? where TR_ID=?";

	public void update(TestResult testResult) {
		Connection con = null;
		try {
			con = ConnectionFactory.getConnection();
			PreparedStatement ps = con.prepareStatement(sql_update);
			ps.setInt(1, testResult.getTest().getId());
			ps.setString(2, testResult.getCustomer().getUserId());
			ps.setFloat(3, testResult.getScore());
			ps.setInt(4, testResult.Pass());
			ps.setTimestamp(5, (Timestamp) testResult.getStartTime());
			ps.setTimestamp(6, (Timestamp) testResult.getEndTime());
			ps.setInt(7,testResult.getId());
			ps.executeUpdate();
			testResult.setId(0);
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				con.rollback();
			} catch (SQLException ex) {
				ex.printStackTrace();
			}
		} finally {
			this.close(con);
		}
	}

	public static final String sql_deleteuesid = "delete TESTRESULT where UESID=?";

	public void deleteByuseID(String uesid) {
		Connection con = null;
		try {
			con = ConnectionFactory.getConnection();
			PreparedStatement ps = con.prepareStatement(sql_deleteuesid);
			ps.setString(1, uesid);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				con.rollback();
			} catch (SQLException ex) {
				ex.printStackTrace();
			}
		} finally {
			this.close(con);
		}

	}

	public static final String sql_deletetrid = "delete TESTRESULT where TR_ID=?";

	public void deleteByTrID(int trid) {
		Connection con = null;
		try {
			con = ConnectionFactory.getConnection();
			PreparedStatement ps = con.prepareStatement(sql_deletetrid);
			ps.setInt(1, trid);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				con.rollback();
			} catch (SQLException ex) {
				ex.printStackTrace();
			}
		} finally {
			this.close(con);
		}
	}

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

/*	public static void main(String[] args) {
		List<TestResult> tr = new ArrayList<TestResult>();
		TestResultDBDao c = new TestResultDBDao();
		tr = c.findTestResultByUseID("mashui215");
		c.deleteByTrID(2);
		for (int i = 0; i < tr.size(); i++) {
			System.out.println(tr.get(i).getId() + "," + tr.get(i).getScore()
					+ "," + tr.get(i).getCustomer().getUserId());
		}

	}*/

	@Override
	public TestResult findTestResultByTrID(String trid) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public void deleteByTrID(String trid) {
		// TODO Auto-generated method stub

	}
}