itemdao.java

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

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package com.eonline.model;

/**
 *
 * @author Administrator
 */

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

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

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


public class ItemDAO {

    private static final String GET_Exam_ITEMS = "SELECT qid,question, option_a, option_b,option_c,option_d,answer FROM EItem";
    private static final String CREATE_Exam_ITEM = "INSERT INTO EItem (\"qid\", question, option_a, option_b,option_c,option_d,answer) VALUES (?, ?, ?, ?, ?)";
    
    private static final String GET_NEXT_ID = "SELECT ID_number FROM ObjectIDs WHERE table_name=?";
    private static final String UPDATE_ID = "UPDATE ObjectIDs SET id=? WHERE table_name=?";
    private static final String CREATE_ID = "INSERT INTO ObjectIDs (table_name, id) VALUES (?, ?)";
    
    private DataSource datasource;

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

    /**
     * return a ItemList  from ExamLibrary
     *
     */
    public List getItemList() {
        List itemList = new ArrayList();
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            con = datasource.getConnection();
            ps = con.prepareStatement(GET_Exam_ITEMS);
            //ps.setString(1, username);
            rs = ps.executeQuery();
            while(rs.next()) {
                Item1 item = new Item1(
                        rs.getInt("qid"),
                        rs.getString("question"),
                        rs.getString("option_a"),
                        rs.getString("option_b"),
                        rs.getString("option_c"),
                        rs.getString("option_d"),
                        rs.getString("answer"));
                itemList.add(item);
            }
        } 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 itemList;
    }

    public void addItem(String username, Item1 item) {
        Connection con = null;
        PreparedStatement ps = null;
        try {
            int id = getNextID("EItem");
            con = datasource.getConnection();
            ps = con.prepareStatement(CREATE_Exam_ITEM);
            ps.setInt(0, id);
            ps.setString(1, item.getQuestion());
            ps.setString(2, item.getOption_a());
            ps.setString(3, item.getOption_b());
            ps.setString(4, item.getOption_c());
            ps.setString(5, item.getOption_d());
            ps.setString(6, item.getAnswer());
            //ps.setString(2, username);  //添加人
            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(); }
            }
        }
    }

    public int getNextID(String tableName) {
        int id = 1;
        Connection con = null;
        PreparedStatement queryStmt = null;
        PreparedStatement updateStmt = null;
        ResultSet rs = null;
        try {
            con = datasource.getConnection();
            queryStmt = con.prepareStatement(GET_NEXT_ID);
            queryStmt.setString(1, tableName);
            rs = queryStmt.executeQuery();
            if(rs.next()) {
                id = rs.getInt("id");
                updateStmt = con.prepareStatement(UPDATE_ID);
                updateStmt.setInt(1, id+1);
                updateStmt.setString(2, tableName);
                updateStmt.executeUpdate();
            } else {
                updateStmt = con.prepareStatement(CREATE_ID);
                updateStmt.setString(1, tableName);
                updateStmt.setInt(2, id+1);
                updateStmt.executeUpdate();
            }
        } catch (SQLException se) {
            se.printStackTrace();
        } finally {
            if(rs != null) {
                try { rs.close(); } catch (Exception e) { e.printStackTrace(); }
            }
            if(queryStmt != null) {
                try { queryStmt.close(); } catch (Exception e) { e.printStackTrace(); }
            }
            if(updateStmt != null) {
                try { updateStmt.close(); } catch (Exception e) { e.printStackTrace(); }
            }
            if(con != null) {
                try { con.close(); } catch (Exception e) { e.printStackTrace(); }
            }
        }

        return id;
    }

}