dcategory.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 javax.servlet.http.HttpSession;

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

/**
 * 针对分类信息的数据处理类
 * @author ShenYK
 * @version 1.0
 */
public class DCategory extends DCommon
{
    //获得所有分类信息
    public Vector getAllCategoryInfo()
        throws Exception
    {
        //获得数据库连接
        Connection conn = this.getDBConnection();
        if ( conn == null )
        {
            throw new Exception("数据库连接获得失败！");
        }
        Statement stmt = null;
        ResultSet rs = null;
        
        try
        {
            //放入一个大的Vector中
            Vector vCategorys = new Vector();
            
            stmt = conn.createStatement();
            //执行SQL语句
            String sQuery = "select * from category order by category_id";
            rs = stmt.executeQuery( sQuery );
            while ( rs.next() )
            {
                Category oCategory = new Category(rs);
                vCategorys.add( oCategory );
            }
            return vCategorys;
        }
        catch(Exception e)
        {
            e.printStackTrace();
            throw e;
        }
        finally
        {
            try
            {
                rs.close();
                stmt.close();
                conn.close();
            }catch(Exception ex)
            {
            }
        }
    }
    
    //获得对应分类信息
    public Category getCategoryById( String sCategoryId )
        throws Exception
    {
        //获得数据库连接
        Connection conn = this.getDBConnection();
        if ( conn == null )
        {
            throw new Exception("数据库连接获得失败！");
        }
        Statement stmt = null;
        ResultSet rs = null;
        
        try
        {
            //返回的分类对象
            Category oCategory = null;
            
            stmt = conn.createStatement();
            //执行SQL语句
            String sQuery = "select * from category where category_id='" + sCategoryId + "'";
            rs = stmt.executeQuery( sQuery );
            if( rs.next() )
            {
                oCategory = new Category(rs);
            }
            return oCategory;
        }
        catch(Exception e)
        {
            e.printStackTrace();
            throw e;
        }
        finally
        {
            try
            {
                rs.close();
                stmt.close();
                conn.close();
            }catch(Exception ex)
            {
            }
        }
    }
    
    //删除指定的分类
    public void deleteCategoryById(  String sCategoryId )
        throws Exception
    {
        //获得数据库连接
        Connection conn = this.getDBConnection();
        if ( conn == null )
        {
            throw new Exception("数据库连接获得失败！");
        }
        Statement stmt = null;
        
        try
        {
            stmt = conn.createStatement();
            //执行SQL语句，删除ID
            String sUpdateQuery = "delete from category where category_id='" + sCategoryId + "'";
            stmt.executeUpdate( sUpdateQuery );
            return;
        }
        catch(Exception e)
        {
            e.printStackTrace();
            throw e;
        }
        finally
        {
            try
            {
                stmt.close();
                conn.close();
            }catch(Exception ex)
            {
            }
        }
    }
    
    //修改指定的分类
    public void modifyCategory ( Category categoryObj )
        throws Exception
    {
        //获得数据库连接
        Connection conn = this.getDBConnection();
        if ( conn == null )
        {
            throw new Exception("数据库连接获得失败！");
        }
        Statement stmt = null;
        
        try
        {
            stmt = conn.createStatement();
            //执行SQL语句，修改题目
            String sUpdateQuery = "update category set "
                                + "category_name = '" + categoryObj.getCategoryName() + "', "
                                + "question_number = " + categoryObj.getQuestionNumber() + ", "
                                + "test_time = " + categoryObj.getTestTime() + " "
                                + "where category_id = '" + categoryObj.getCategoryId() + "'";
            stmt.executeUpdate( sUpdateQuery );
            return;
        }
        catch(Exception e)
        {
            e.printStackTrace();
            throw e;
        }
        finally
        {
            try
            {
                stmt.close();
                conn.close();
            }catch(Exception ex)
            {
            }
        }
    }
    
    //添加一个新分类
    public void addCategory ( Category categoryObj )
        throws Exception
    {
        //获得数据库连接
        Connection conn = this.getDBConnection();
        if ( conn == null )
        {
            throw new Exception("数据库连接获得失败！");
        }
        Statement stmt = null;
        ResultSet rs = null;
        try
        {
            stmt = conn.createStatement();
            
            //执行SQL语句生成最大的categoryId
            String sCategoryId = "";
            String sQuery = "select max(category_id) from category";
            rs = stmt.executeQuery( sQuery );
            rs.next();
            String sCurrentMaxId = rs.getString(1);
            //当前是第一次登录
            if ( sCurrentMaxId == null )
            {
                sCategoryId = "01";
            }
            else
            {
                int iMaxCd = Integer.parseInt(sCurrentMaxId);
                sCategoryId = String.valueOf(iMaxCd+1);
                int iLength = sCategoryId.length();
                for(int i=2; i>iLength; i--)
                {
                    sCategoryId = "0" + sCategoryId;
                }
            }
            //执行SQL语句，添加分类
            String sInsertQuery = "insert into category set "
                                + "category_id = '" + sCategoryId + "', "
                                + "category_name = '" + categoryObj.getCategoryName() + "', "
                                + "question_number = " + categoryObj.getQuestionNumber() + ", "
                                + "test_time = " + categoryObj.getTestTime();
            stmt.executeUpdate( sInsertQuery );
            return;
        }
        catch(Exception e)
        {
            e.printStackTrace();
            throw e;
        }
        finally
        {
            try
            {
                rs.close();
                stmt.close();
                conn.close();
            }catch(Exception ex)
            {
            }
        }
    }
}