scoredao.java

本系统是用NetBeans IDE 6.1所写

package com.online.dao;
import java.sql.*;
import javax.sql.DataSource;
import com.online.pojo.*;
import javax.naming.Context;
import javax.naming.InitialContext;

import java.util.ArrayList;
import java.util.List;
import java.util.List;
public class ScoreDAO {
      String GET_SCORE = "SELECT * from score WHERE uid=?";
      String CREATE_SCORE_ITEM="INSERT INTO SCORE(UID,DATE,SCORE ,ANSWER ,S_ANSWER) VALUES (?, ?, ?, ?, ?)";
      String GET_NEXT_UID="SELECT id_number FROM ObjectIDs WHERE table_name=?";
      String UPDATE_UID="UPDATE ObjectIDs SET id_number=? WHERE table_name=?";
      String CREATE_UID="INSERT INTO ObjectIDs(table_name, id_number) VALUES (?, ?)";
      private DataSource datasource;
      
      public ScoreDAO(){
          try {
            Context ctx = new InitialContext();
            datasource = (DataSource) ctx.lookup("jdbc/OnlineExamDB");
        } catch (Exception e) {
            e.printStackTrace();
        }
      }
      
       public List getScore(int uid) {
       Score score = null;
       List scoreList = new ArrayList();
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            con = datasource.getConnection();
            ps = con.prepareStatement(GET_SCORE);
            ps.setInt(1,uid);
            rs = ps.executeQuery();
            while(rs.next()) {
                score = new Score(rs.getInt("SCORE"),rs.getTimestamp("DATE"));
                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;
   }
       
    public void addScore(int score, Score item) {
        Connection con = null;
        PreparedStatement ps = null;
        try {
            int id = getNextID("SCORE");
            con = datasource.getConnection();
            System.out.print(item.getDate());
            ps = con.prepareStatement(CREATE_SCORE_ITEM);
            ps.setInt(1, item.getUid());
            ps.setTimestamp(2,item.getDate());
            ps.setInt(3, score);
            ps.setString(4, item.getAnswer());
            ps.setString(5, item.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(); }
            }
        }
    }  
  
    public int getNextID(String tableName) {
        int id_number = 1;
        Connection con = null;
        PreparedStatement queryStmt = null;
        PreparedStatement updateStmt = null;
        ResultSet rs = null;
        try {
            con = datasource.getConnection();
            queryStmt = con.prepareStatement(GET_NEXT_UID);
            queryStmt.setString(1, tableName);
            rs = queryStmt.executeQuery();
            if(rs.next()) {
                id_number = rs.getInt("id_number");
                updateStmt = con.prepareStatement(UPDATE_UID);
                updateStmt.setInt(1, id_number+1);
                updateStmt.setString(2, tableName);
                updateStmt.executeUpdate();
            } else {
                updateStmt = con.prepareStatement(CREATE_UID);
                updateStmt.setString(1, tableName);
                updateStmt.setInt(2, id_number+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_number;
   }
}