dbboardfactory.java

基于struct结构的jsp

            }
            catch (Exception ex) {
                logger.error("关闭 pstm 失败 error : " + ex.getMessage());
            }
            try {
                conn.close();
            }
            catch (Exception ex) {
                logger.error("关闭数据库连接失败 error : " + ex.getMessage());
            }
        }
        return isSuccess;
    }

    /**
     * 将纪录写入xml备份
     * @param board Board
     * @return boolean
     */
    public Iterator exportXMLInfo() {
        Connection conn = DBConnectionManager.getInstance().getConnection(
            );
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList arrayList = new ArrayList();
        try {
            stmt = conn.createStatement();
            rs = stmt.executeQuery(WRITE_XML);
            while (rs.next()) {
                DBBoard board = new DBBoard();
                board.setBoaId(rs.getInt("boaId"));
                board.setBoaType(rs.getString("boaType"));
                board.setBoaParId(rs.getInt("boaParId"));
                board.setBoaParENUM(rs.getString("boaParENUM"));
                board.setBoaDepth(rs.getInt("boaDepth"));
                board.setBoaRootId(rs.getInt("boaRootId"));
                board.setBoaChild(rs.getInt("boaChild"));
                board.setBoaOrders(rs.getInt("boaOrders"));
                board.setBoaInfo(rs.getString("boaInfo"));
                board.setBoaMaster(rs.getString("boaMaster"));
                board.setBoaState(rs.getInt("boaState"));
                board.setBoaPasswd(rs.getString("boaPasswd"));
                board.setBoaCreTime(rs.getString("boaCreTime"));
                arrayList.add(board);
                logger.info("读取全部数据成功.......");
            }
        }
        catch (Exception ex) {
            logger.error("读取要写入xml的数据失败 : " + ex.getMessage());
        }
        finally {
            try {
                rs.close();
            }
            catch (Exception ex) {
                logger.info("关闭 rs 失败 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.iterator();
    }

    /**
     * 判断库中有无数据
     * @param board Board
     * @return boolean
     */
    public boolean isExistBoard() {
        boolean isExist = false;
        Connection conn = DBConnectionManager.getInstance().getConnection(
            );
        Statement stmt = null;
        ResultSet rs = null;
        try {
            stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                                        ResultSet.CONCUR_READ_ONLY);
            rs = stmt.executeQuery(IS_EXIST_DATA);
            if (rs.next()) {
                isExist = true;
                logger.info("数据库中有数据不需要导入数据 ...");
            }
        }
        catch (Exception ex) {
            logger.error("导入数据错误 : " + ex.getMessage());
        }
        finally {
            try {
                stmt.close();
            }
            catch (Exception ex) {
                logger.error("关闭 stmt 失败 error : " + ex.getMessage());
            }
            try {
                conn.close();
            }
            catch (Exception ex) {
                logger.error("关闭数据库连接失败 error : " + ex.getMessage());
            }
        }
        return isExist;
    }

    /**
     * 倒入XML数据
     * @param board Board
     * @return boolean
     */
    public void importXMLInfo(Iterator iterator) {
        while (iterator.hasNext()) {
            Board board = (DBBoard) iterator.next();
            importXMLData(board);
            logger.info("成功导入了模板 " + board.getBoaId() + "对应的数据 ！");
        }
    }

    private void importXMLData(Board board) {
        Connection conn = DBConnectionManager.getInstance().getConnection(
            );
        PreparedStatement pstm = null;
        try {
            pstm = conn.prepareStatement(INS_IMPORT_XMLINFO);
            int boaId = board.getBoaId();
            pstm.setInt(1, board.getBoaId());
            pstm.setString(2, board.getBoaType());
            pstm.setInt(3, board.getBoaParId());
            pstm.setString(4, board.getBoaParENUM());
            pstm.setInt(5, board.getBoaDepth());
            pstm.setInt(6, board.getBoaRootId());
            pstm.setInt(7, board.getBoaChild());
            pstm.setInt(8, board.getBoaOrders());
            pstm.setString(9, board.getBoaInfo());
            pstm.setString(10, board.getBoaMaster());
            pstm.setInt(11, board.getBoaState());
            pstm.setString(12, board.getBoaPasswd());
            pstm.setString(13, board.getBoaCreTime());
            pstm.executeUpdate();
            /**
             * 更新数据(topicNum 和 repNum)
             */
            upBoardTopicNum(totalTopicNum(boaId),boaId);

            logger.info("导入模板数据成功");
        }
        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());
            }
        }
    }
    /**
     * 更新board对应的数据(主题和回复总和)---导入数据时候执行
     */
    private void upBoardTopicNum(Integer[] integer,int boaId) {
        Connection conn = DBConnectionManager.getInstance().getConnection(
            );
        PreparedStatement pstm = null;
        try {
            pstm = conn.prepareStatement(UP_TOPIC_NUM);
            pstm.setInt(1, integer[0].intValue());
            pstm.setInt(2, integer[1].intValue());
            pstm.setInt(3,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());
            }
        }
    }

    /**
     * 主题和回复总数
     */
    private Integer[] totalTopicNum(int boaId) {
        Connection conn = DBConnectionManager.getInstance().getConnection(
            );
        PreparedStatement pstm = null;
        ResultSet rs = null;
        Integer[] integer = new Integer[2];
        try {
            pstm = conn.prepareStatement(TOTAL_TOPICNUM);
            pstm.setInt(1,boaId);
            rs = pstm.executeQuery();
            if(!rs.next()){
                integer[0] = Integer.valueOf("0");
                integer[1] = Integer.valueOf("0");
            }
            else{
                //指针到最后获取主题总数
                rs.last();
                integer[0] = Integer.valueOf(String.valueOf(rs.getRow()));
                rs.first();
                int repNum = rs.getInt("topicReNum");
                while(rs.next()){
                    repNum = repNum + rs.getInt("topicReNum");
                    logger.info("指针第一次取值，此时的repNum的值为 : " + repNum);
                }
                logger.info("倒入数据时获取回复主题的总数！: " + repNum);
                integer[1] = Integer.valueOf(String.valueOf(repNum));
            }
        }
        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());
            }
        }
        return integer;
    }


    //-------------外部调用
    /**
     * 主题数增加一
     */
    public void incrementTopicnum(int boaId) {
        Connection conn = DBConnectionManager.getInstance().getConnection(
            );
        PreparedStatement pstm = null;
        try {
            pstm = conn.prepareStatement(UPBOARD_TOPICNUM);
            pstm.setInt(1, 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());
            }
        }
    }
    /**
     * 增加回复
     * @param boaId int
     */
    public void incrementRepNum(int boaId) {
        Connection conn = DBConnectionManager.getInstance().getConnection(
            );
        PreparedStatement pstm = null;
        try {
            pstm = conn.prepareStatement(UPBOARD_RENUM);
            pstm.setInt(1, 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());
            }
        }
    }

}