scoredao.java

只做是单选题！数据库表有三张：EUser,EItem,Score

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.exam.model;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;
import javax.sql.DataSource;
import javax.naming.Context;
import javax.naming.InitialContext;

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

    private static final String GET_All_SCORE = "SELECT * FROM Score WHERE UID=?";
    private static final String INSERT_SCORE = "INSERT INTO Score (UID,date,score,answer,s_answer) " + "VALUES (?, ?, ?, ?,?)";
    private DataSource ds;

    public ScoreDAO() {
        try {
            Context ctx = new InitialContext();
            if (ctx == null) {
                throw new RuntimeException("JNDI Context could not be found.");
            }
            ds = (DataSource) ctx.lookup("jdbc/examDB");
            if (ds == null) {
                throw new RuntimeException("DataSource could not be found.");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public List retrieveAll(Integer uid) {
        Connection connection = null;
        PreparedStatement stmt = null;
        ResultSet results = null;
        // Domain variables
        List scorelist = new LinkedList();
        Score score = null;
        try {
            connection = ds.getConnection();

            // Create SQL SELECT statement
            stmt = connection.prepareStatement(GET_All_SCORE);

            stmt.setInt(1, uid);

            // Execute the query
            results = stmt.executeQuery();

            // Iterator over the query results
            while (results.next()) {
                // Create and fill-in the League object
                score = new Score(uid,
                        results.getInt("score"),
                        results.getDate("date"),
                        results.getString("answer"),
                        results.getString("s_answer"));
                // Store it in the collection
                scorelist.add(score);
            }

            // Return the collection
            return scorelist;

        // Handle any SQL errors
        } catch (SQLException se) {
            throw new RuntimeException("A database error occured. " + se.getMessage());
        // Clean up JDBC resources
        } finally {
            if (results != null) {
                try {
                    results.close();
                } catch (SQLException se) {
                    se.printStackTrace(System.err);
                }
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException se) {
                    se.printStackTrace(System.err);
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (Exception e) {
                    e.printStackTrace(System.err);
                }
            }
        }
    }

    void insert(Score score) {
        Connection connection = null;
        PreparedStatement insert_stmt = null;

        try {
            // Get a database connection
            connection = ds.getConnection();

            // Create SQL INSERT statement
            insert_stmt = connection.prepareStatement(INSERT_SCORE);
            // Get the next League object ID
            // Add the object ID as the firs tfield to be entered.
            // Add data fields
            Long time = score.getDate().getTime();
            insert_stmt.setInt(1,score.getObjectID());
            insert_stmt.setDate(2, new Date(time));
            insert_stmt.setInt(3, score.getScore());
            insert_stmt.setString(4, score.getAnswer());
            insert_stmt.setString(5, score.getS_answer());

            // Execute SQL INSERT statement
            insert_stmt.executeUpdate();
        // Handle any SQL errors
        } catch (SQLException se) {
            throw new RuntimeException("A database error occured. " + se.getMessage());
        // Clean up JDBC resources
        } finally {
            if (insert_stmt != null) {
                try {
                    insert_stmt.close();
                } catch (SQLException se) {
                    se.printStackTrace(System.err);
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (Exception e) {
                    e.printStackTrace(System.err);
                }
            }
        }
    }
}