dbboardfactory.java

基于struct结构的jsp

package com.ntsky.bbs.service.db;

import java.util.*;
import java.sql.*;
import org.apache.log4j.Logger ;

import com.ntsky.util.Timer;
import com.ntsky.util.TimerExpress;
import com.ntsky.bbs.service.Board;
import com.ntsky.bbs.service.Manager;
import com.ntsky.bbs.service.CommonDTO;
import com.ntsky.bbs.service.BoardFactory;
import com.ntsky.bbs.form.AdminActionForm;
import com.ntsky.datasource.DBConnectionManager;
/**
 * <p>Title: Ntsky OpenSource BBS</p>
 * <p>Description: </p>
 * <p>Copyright: Copyright (c) 2004</p>
 * <p>Company: WWW.FM880.COM</p>
 * @author 姚君林
 * @version 1.0
 */

public class DBBoardFactory extends BoardFactory{
    private final static Logger logger = Logger.getLogger(DBBoardFactory.class);
    private final static String MAX_BOARDID = "SELECT MAX(boaId) FROM t_board;";
    private final static String MAX_ROOTID =
        "SELECT MAX(boaRootId) FROM t_board;";
    private final static String INSERT_BOARD =
        "INSERT INTO t_board(boaId,boaType,boaParId,"
        + "boaParENUM,boaDepth,boaRootId,boaOrders,boaInfo,boaMaster,boaState,boaPasswd,boaCreTime) "
        + "VALUES(?,?,?,?,?,?,?,?,?,?,?,?);";
    private final static String SELECT_BOARD = "SELECT boaId,boaType,boaParId,boaParENUM,boaDepth,boaRootId,boaChild,boaOrders,boaInfo,boaMaster,boaState,boaPasswd FROM t_board WHERE boaId=?;";
    private final static String ENUM_BOARD_PARENT = "SELECT boaId,boaType,boaParId,boaParENUM,boaDepth FROM t_board ORDER BY boaRootId,boaOrders;";
    private final static String UPDATE_BOARD_CHILDID =
        "UPDATE t_board SET boaChild=boaChild+1 where boaId=?";
    private final static String SELECT_PARENDID =
        "SELECT boaParId,boaMaster FROM t_board WHERE boaId=?;";
    private final static String SHOW_BOARDINFO = "SELECT boaId,boaType,boaParId,boaParENUM,boaDepth,boaRootId,boaChild,boaMaster FROM t_board ORDER BY boaRootId,boaOrders;";
    private final static String DELETE_BOARD =
        "DELETE FROM t_board WHERE boaId=?;";
    private final static String SELECT_CHILDID =
        "SELECT boaId FROM t_board WHERE boaParId=?;";
    private final static String UPDATE_BOARD = "UPDATE t_board SET boaType=?,boaInfo=?,boaMaster=?,boaState=?,boaPasswd=? WHERE boaId=?;";
    private final static String DECREASE_CHILD =
        "UPDATE t_board SET boaChild=boaChild-1 WHERE boaId=?;";
    private final static String WRITE_XML = "SELECT * FROM t_board;";
    private final static String IS_EXIST_DATA = "SELECT boaId FROM t_board;";
    private final static String INS_IMPORT_XMLINFO =
        "INSERT INTO t_board(boaId,boaType,boaParId,"
        + "boaParENUM,boaDepth,boaRootId,boaChild,boaOrders,boaInfo,boaMaster,boaState,boaPasswd,boaCreTime) "
        + "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?);";
    private final static String PAGE_SHOW_INDEX =
        "SELECT boaId,boaType,boaParId FROM t_board WHERE boaDepth=?;";
    private final static String PAGE_SHOW_BOARD = "SELECT boaId,boaType,boaParId,boaParENUM,boaDepth,boaRootId,boaChild,boaInfo,boaMaster,boaTodayNum,boaTopicNum,boaReNum,boaState,boaPasswd FROM t_board WHERE boaParId=? ORDER BY boaRootId,boaOrders;";
    private static final String UPBOARD_TOPICNUM =
        "UPDATE t_board SET boaTopicNum = boaTopicNum+1 WHERE boaId=?;";
    private static final String UPBOARD_RENUM = "UPDATE t_board SET boaReNum=boaReNum+1 WHERE boaId=?;";
    private static final String TOTAL_TOPICNUM =
        "SELECT topicId,topicReNum FROM t_topic WHERE topicBoaId=?;";
    private static final String UP_TOPIC_NUM = "UPDATE t_board SET boaTopicNum=?,boaReNum=? WHERE boaId=?;";
    private static final String UP_BOARD_TODAY = "UPDATE t_board SET boaTodayNum=? WHERE boaId=?;";

    /**
     * 更新一天的访问数
     * @param boaTodayNum int
     * @param boaId int
     */
    public void upBoardToday(int boaTodayNum,int boaId) {
        Connection conn = DBConnectionManager.getInstance().getConnection(
            );
        PreparedStatement pstm = null;
        ResultSet rs = null;
        try {
            //stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            pstm = conn.prepareStatement(UP_BOARD_TODAY);
            pstm.setInt(1,boaTodayNum);
            pstm.setInt(2,boaId);
            pstm.executeUpdate();
        }
        catch (Exception ex) {
            logger.error("更新一天的访问数错误 : " + ex.getMessage());
        }
        finally {
            try {
                pstm.close();
            }
            catch (Exception ex) {
                logger.error("关闭 pstm 失败 error : " + ex.getMessage());
            }
            try {
                conn.close();
            }
            catch (Exception ex) {
                logger.error("关闭数据库连接失败 error : " + ex.getMessage());
            }
        }
    }

    /**
     * 前台page页面显示(大模块)
     * @return Collection
     */
    public Iterator getPageBoardInfo(int boaDepth) {
        Connection conn = DBConnectionManager.getInstance().getConnection(
            );
        PreparedStatement pstm = null;
        ResultSet rs = null;
        ArrayList arrayList = new ArrayList();
        try {
            //stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            pstm = conn.prepareStatement(PAGE_SHOW_INDEX);
            pstm.setInt(1,boaDepth);
            rs = pstm.executeQuery();
            while (rs.next()) {
                Board board = new DBBoard();
                board.setBoaId(rs.getInt("boaId"));
                board.setBoaType(rs.getString("boaType"));
                board.setBoaParId(rs.getInt("boaParId"));
                //board.setArrayList(getPageBoardChildInfo(rs.getInt("boaParId")));
                arrayList.add(board);
            }
        }
        catch (Exception ex) {
            logger.error("获取父亲节点 : " + ex.getMessage());
        }
        finally {
            try {
                rs.close();
            }
            catch (Exception ex) {
                logger.info("关闭指针失败 error : " + ex.getMessage());
            }
            try {
                pstm.close();
            }
            catch (Exception ex) {
                logger.error("关闭 pstm 失败 error : " + ex.getMessage());
            }
            try {
                conn.close();
            }
            catch (Exception ex) {
                logger.error("关闭数据库连接失败 error : " + ex.getMessage());
            }
        }
        return arrayList.iterator();
    }

    public ArrayList getPageBoardChildInfo(int boaParId) {
        Connection conn = DBConnectionManager.getInstance().getConnection(
            );
        PreparedStatement pstm = null;
        ResultSet rs = null;
        ArrayList arrayList = new ArrayList();
        try {
            pstm = conn.prepareStatement(PAGE_SHOW_BOARD);
            pstm.setInt(1, boaParId);
            rs = pstm.executeQuery();
            while (rs.next()) {
                Board board = new DBBoard();
                board.setBoaId(rs.getInt("boaId"));
                board.setBoaType(rs.getString("boaType"));
                board.setBoaParId(rs.getInt("boaParId"));
                board.setBoaInfo(rs.getString("boaInfo"));
                board.setBoaMaster(rs.getString("boaMaster"));
                board.setBoaTodayNum(rs.getInt("boaTodayNum"));
                board.setBoaTopicNum(rs.getInt("boaTopicNum"));
                board.setBoaReNum(rs.getInt("boaReNum"));
                int boaState = rs.getInt("boaState");
                switch(boaState){
                    case 1 :
                        board.setBoaStatePic("/images/common.gif");
                        break;
                    case 2 :
                        board.setBoaStatePic("/images/member.gif");
                        break;
                    case 3 :
                        board.setBoaStatePic("/images/guest.gif");
                        break;
                    case 4 :
                        board.setBoaStatePic("/images/lock.gif");
                        break;
                }
                board.setBoaPasswd(rs.getString("boaPasswd"));
                arrayList.add(board);
            }
        }
        catch (Exception ex) {
            logger.error("获取子类信息错误 : " + ex.getMessage());
        }
        finally {
            try {
                rs.close();
            }
            catch (Exception ex) {
                logger.info("关闭指针失败 error : " + ex.getMessage());
            }
            try {
                pstm.close();
            }
            catch (Exception ex) {
                logger.error("关闭 pstm 失败 error : " + ex.getMessage());
            }
            try {
                conn.close();
            }
            catch (Exception ex) {
                logger.error("关闭数据库连接失败 error : " + ex.getMessage());
            }
        }
        return arrayList;
    }

    /**
     * 显示小模块
     * @return Collection
     */


    /**
     * 读取信息(分层显示)
     */
    public Collection getBorParENUM() {
        Connection conn = DBConnectionManager.getInstance().getConnection(
            );
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList arrayList = new ArrayList();
        try {
            stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                                        ResultSet.CONCUR_READ_ONLY);
            rs = stmt.executeQuery(ENUM_BOARD_PARENT);
            arrayList.add(new CommonDTO(0, "版块从属类别"));
            while (rs.next()) {
                int boaId = rs.getInt("boaId");
                logger.info("boaId 的值为 ：" + boaId);
                int boaResideId = rs.getInt("boaParId");
                int boaDepth = rs.getInt("boaDepth");
                String boaType = rs.getString("boaType");
                if (boaResideId != 0) {
                    logger.info("该节点是" + boaResideId + "的子节点　！");
                    String line = "－ ";
                    for (int i = 1; i < boaDepth; i++) {
                        line = "－ " + line;
                    }
                    boaType = line + boaType;
                    logger.info("boaType 的值为　: " + boaType);
                }
                arrayList.add(new CommonDTO(rs.getInt("boaId"), boaType));
            }
        }
        catch (Exception ex) {
            logger.error("获取父亲节点 : " + ex.getMessage());
        }
        finally {
            try {
                rs.close();
            }
            catch (Exception ex) {
                logger.info("关闭指针失败 error : " + ex.getMessage());
            }
            try {
                stmt.close();
            }
            catch (Exception ex) {
                logger.error("关闭 pstm 失败 error : " + ex.getMessage());
            }
            try {
                conn.close();
            }
            catch (Exception ex) {
                logger.error("关闭数据库连接失败 error : " + ex.getMessage());
            }
        }
        return arrayList;
    }

    /**
     * show 单个模板数据
     * @return String
     */
    public Board showBoaInfo(int boaId) {
        Connection conn = DBConnectionManager.getInstance().getConnection(
            );
        PreparedStatement pstm = null;
        ResultSet rs = null;
        DBBoard board = null;
        try {
            //SELECT_BOARD和添加公用但参数值不一样
            pstm = conn.prepareStatement(SELECT_BOARD);
            pstm.setInt(1, boaId);
            rs = pstm.executeQuery();
            rs.next();
            board = new DBBoard();
            board.setBoaId(rs.getInt("boaId"));
            board.setBoaType(rs.getString("boaType"));
            board.setBoaParId(rs.getInt("boaParId"));
            board.setBoaDepth(rs.getInt("boaDepth"));
            board.setBoaRootId(rs.getInt("boaRootId"));
            board.setBoaChild(rs.getInt("boaChild"));
            board.setBoaInfo(rs.getString("boaInfo"));
            board.setBoaMaster(rs.getString("boaMaster"));
            board.setBoaState(rs.getInt("boaState"));
            board.setBoaPasswd(rs.getString("boaPasswd"));
        }
        catch (Exception ex) {
            logger.error(" 显示模板信息失败 : " + ex.getMessage());
        }
        finally {
            try {
                rs.close();
            }
            catch (Exception ex) {
                logger.info("showBoaInfo 关闭指针失败 error : " + ex.getMessage());
            }
            try {
                pstm.close();
            }
            catch (Exception ex) {
                logger.error("showBoaInfo 关闭 pstm 失败 error : " + ex.getMessage());
            }
            try {
                conn.close();
            }