scoredao.java

在线考试 考生登陆 查分 管理员管理后台等的 的的的的达到的达到的的的

package com.eonline.model;



import java.sql.*;
import javax.sql.DataSource;

import javax.naming.Context;
import javax.naming.InitialContext;

import java.util.ArrayList;
import java.util.List;
import java.util.*;


/**
 *
 * @author Administrator
 */
public class ScoreDAO {

    private static final String GET_USERSCORE = "SELECT UID, date, score, answer, s_answer FROM Score WHERE UID=?";
    private static final String CREATE_USERSCORE = "INSERT INTO Score (UID, date, score, answer, s_answer)  VALUES (?, ?, ?, ?, ?)";
    private static final String TEST="INSERT INTO SCORE (UID, DATE, SCORE, ANSWER, S_ANSWER) VALUES (1, \"2010-05-09 20:18:02\", 80, 'A,B,C,D,A', 'A,B,C,D,A')";

    private DataSource datasource;


    public ScoreDAO() {
        try {
            Context ctx = new InitialContext();
            datasource = (DataSource) ctx.lookup("jdbc/eonlineDB");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * Get the score's list.
     *
     * @param UID user id
     * @return   return the score's List.
     */
    public List getScoreList (int UID) {

        List scoreList = new ArrayList();
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            con = datasource.getConnection();
            ps = con.prepareStatement(GET_USERSCORE);
            ps.setInt(1, UID);
            rs = ps.executeQuery();
            while(rs.next()) {
                Score score = new Score(
                        rs.getInt("UID"),
                        rs.getTimestamp("date"),
                        rs.getInt("score"),
                        rs.getString("answer"),
                        rs.getString("s_answer"));
                scoreList.add(score);
            }
        } catch (SQLException se) {
            se.printStackTrace();
        } finally {
            if(rs != null) {
                try { rs.close(); } catch (Exception e) { e.printStackTrace(); }
            }
            if(ps != null) {
                try { ps.close(); } catch (Exception e) { e.printStackTrace(); }
            }
            if(con != null) {
                try { con.close(); } catch (Exception e) { e.printStackTrace(); }
            }
        }
        return scoreList;
    }

    /**
     * Add the uers' score to the Score.
     *
     * @param UID user id
     * @param score the new user's score
     */
    public void addScore (int UID, Score score) {

        Connection con = null;
        PreparedStatement ps = null;

        try {
            con = datasource.getConnection();
            ps = con.prepareStatement(CREATE_USERSCORE);
            ps.setInt(1, UID);
            ps.setTimestamp(2,score.getDate());
            ps.setInt(3, score.getScore());
            ps.setString(4, score.getAnswer());
            ps.setString(5, score.getS_answer());
            ps.executeUpdate();
        } catch (SQLException se) {
            se.printStackTrace();
        } finally {
            if(ps != null) {
                try { ps.close(); } catch (Exception e) { e.printStackTrace(); }
            }
            if(con != null) {
                try { con.close(); } catch (Exception e) { e.printStackTrace(); }
            }
        }
    }

}