drecord.java

在线模拟选课系统

/*
 * This product includes software developed by the
 * Apache Software Foundation (http://www.apache.org/).
 */
package ch07.database;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.*;
import java.text.*;

import javax.servlet.http.HttpSession;

import ch07.*;
import ch07.object.unit.*;

/**
 * 针对答题历史记录的数据处理类
 * @author ShenYK
 * @version 1.0
 */
public class DRecord extends DCommon
{
    //更新用户考试记录，并计算用户名次
    public int registerRecord( String sUsername, String sCategoryId, int iRecord, Vector allQuestions )
        throws Exception
    {
        //获得数据库连接
        Connection conn = this.getDBConnection();
        if ( conn == null )
        {
            throw new Exception("数据库连接获得失败！");
        }
        Statement stmt = null;
        ResultSet rs = null;
        
        try
        {
            Vector vQuestions = new Vector();
            stmt = conn.createStatement();
            
            //执行SQL语句生成最大的recordId
            String sRecordId = "";
            String sQuery = "select max(record_id) from test_record";
            rs = stmt.executeQuery( sQuery );
            rs.next();
            String sCurrentMaxId = rs.getString(1);
            //当前是第一次登录
            if ( sCurrentMaxId == null )
            {
                sRecordId = "0000000001";
            }
            else
            {
                int iMaxCd = Integer.parseInt(sCurrentMaxId);
                sRecordId = String.valueOf(iMaxCd+1);
                int iLength = sRecordId.length();
                for(int i=10; i>iLength; i--)
                {
                    sRecordId = "0" + sRecordId;
                }
            }
            
            //得到当前系统时间
            String sDate = (new SimpleDateFormat("yyyyMMddHHmmss")).format(new Date(System.currentTimeMillis()));
            
            //执行SQL语句，插入数据库
            String sUpdateQuery = "insert into test_record set record_id='" + sRecordId 
                                + "', username = '" + sUsername
                                + "', category_id = '" + sCategoryId
                                + "', test_time = '" + sDate
                                + "', test_result=" + iRecord
                                + ", use_time=0";
            stmt.executeUpdate( sUpdateQuery );
            
            //通过检索得出用户当前的名次
            sQuery = "select count(*) from test_record where category_id='" + sCategoryId 
                   + "' and test_result>" + iRecord ;
            rs = stmt.executeQuery( sQuery );
            rs.next();
            int iOrder = rs.getInt(1);

            return iOrder+1;
        }
        catch(Exception e)
        {
            e.printStackTrace();
            throw e;
        }
        finally
        {
            try
            {
                rs.close();
                stmt.close();
                conn.close();
            }catch(Exception ex)
            {
            }
        }
    }
    
    //得到某一用户在某一分类下的所有记录
    public Vector getAllRecords( String sUsername, String sCategoryId )
        throws Exception
    {
        //获得数据库连接
        Connection conn = this.getDBConnection();
        if ( conn == null )
        {
            throw new Exception("数据库连接获得失败！");
        }
        Statement stmt = null;
        ResultSet rs = null;
        
        try
        {
            Vector vRecords = new Vector();
            stmt = conn.createStatement();
            
            //执行SQL语句
            String sQuery = "select * from test_record where username='" + sUsername 
                          + "' and category_id = '" + sCategoryId + "'";
            rs = stmt.executeQuery( sQuery );
            while( rs.next() )
            {
                TestRecord testRecordObj = new TestRecord(rs);
                vRecords.add( testRecordObj );
            }
            
            return vRecords;
        }
        catch(Exception e)
        {
            e.printStackTrace();
            throw e;
        }
        finally
        {
            try
            {
                rs.close();
                stmt.close();
                conn.close();
            }catch(Exception ex)
            {
            }
        }
    }
    
    //得到某一分类下的所有记录
    public Vector getAllRecords( String sCategoryId )
        throws Exception
    {
        //获得数据库连接
        Connection conn = this.getDBConnection();
        if ( conn == null )
        {
            throw new Exception("数据库连接获得失败！");
        }
        Statement stmt = null;
        ResultSet rs = null;
        
        try
        {
            Vector vRecords = new Vector();
            stmt = conn.createStatement();
            
            //执行SQL语句
            String sQuery = "select * from test_record where category_id = '" + sCategoryId + "'";
            rs = stmt.executeQuery( sQuery );
            while( rs.next() )
            {
                TestRecord testRecordObj = new TestRecord(rs);
                vRecords.add( testRecordObj );
            }
            
            return vRecords;
        }
        catch(Exception e)
        {
            e.printStackTrace();
            throw e;
        }
        finally
        {
            try
            {
                rs.close();
                stmt.close();
                conn.close();
            }catch(Exception ex)
            {
            }
        }
    }
}