threaddaoimpljdbc.java

java servlet著名论坛源代码

                bean.setThreadIcon(resultSet.getString("ThreadIcon"));
                bean.setThreadDuration(resultSet.getInt("ThreadDuration"));
                retValue.add(bean);
            }
            return retValue;
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in ThreadDAOImplJDBC.getBeans_inForum_withSortSupport_limit_mysql.");
        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    /*
     * Included columns: ThreadID, MemberName, LastPostMemberName, ThreadTopic, ThreadBody,
     *                   ThreadVoteCount, ThreadVoteTotalStars, ThreadCreationDate, ThreadLastPostDate, ThreadType,
     *                   ThreadOption, ThreadStatus, ThreadHasPoll, ThreadViewCount, ThreadReplyCount,
     *                   ThreadIcon, ThreadDuration
     * Excluded columns: ForumID
     */
    /**
     * Note: This is a customized method
     */
    private Collection getBeans_inForum_withSortSupport_limit_noscroll(int forumID, int offset, int rowsToReturn, String sort, String order, boolean enable)
        throws IllegalArgumentException, DatabaseException {

        if (offset < 0) throw new IllegalArgumentException("The offset < 0 is not allowed.");
        if (rowsToReturn <= 0) throw new IllegalArgumentException("The rowsToReturn <= 0 is not allowed.");

        if ((!sort.equals("ThreadLastPostDate")) &&
            (!sort.equals("ThreadCreationDate")) &&
            (!sort.equals("MemberName")) &&
            (!sort.equals("ThreadReplyCount")) &&
            (!sort.equals("ThreadViewCount")) ) {
            throw new IllegalArgumentException("Cannot sort, reason: dont understand the criteria '" + sort + "'.");
        }

        if ((!order.equals("ASC")) &&
            (!order.equals("DESC")) ) {
            throw new IllegalArgumentException("Cannot sort, reason: dont understand the order '" + order + "'.");
        }

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        Collection retValue = new ArrayList();
        StringBuffer sql = new StringBuffer(512);
        sql.append("SELECT ThreadID, MemberName, LastPostMemberName, ThreadTopic, ThreadBody, ThreadVoteCount, ThreadVoteTotalStars, ThreadCreationDate, ThreadLastPostDate, ThreadType, ThreadOption, ThreadStatus, ThreadHasPoll, ThreadViewCount, ThreadReplyCount, ThreadIcon, ThreadDuration");
        sql.append(" FROM " + TABLE_NAME);
        sql.append(" WHERE ForumID = ?");
        if (enable) {
            sql.append(" AND ThreadStatus <> 1 ");
        } else {//disable
            sql.append(" AND ThreadStatus = 1 ");
        }
        sql.append(" ORDER BY " + sort + " " + order);// ColumnName, ASC|DESC
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());
            statement.setMaxRows(offset + rowsToReturn);
            statement.setInt(1, forumID);
            resultSet = statement.executeQuery();
            int rowIndex = -1;
            while (resultSet.next()) {
                rowIndex++;
                if (rowIndex < offset) continue;
                ThreadBean bean = new ThreadBean();
                bean.setThreadID(resultSet.getInt("ThreadID"));
                bean.setForumID(forumID);
                bean.setMemberName(resultSet.getString("MemberName"));
                bean.setLastPostMemberName(resultSet.getString("LastPostMemberName"));
                bean.setThreadTopic(resultSet.getString("ThreadTopic"));
                bean.setThreadBody(resultSet.getString("ThreadBody"));
                bean.setThreadVoteCount(resultSet.getInt("ThreadVoteCount"));
                bean.setThreadVoteTotalStars(resultSet.getInt("ThreadVoteTotalStars"));
                bean.setThreadCreationDate(resultSet.getTimestamp("ThreadCreationDate"));
                bean.setThreadLastPostDate(resultSet.getTimestamp("ThreadLastPostDate"));
                bean.setThreadType(resultSet.getInt("ThreadType"));
                bean.setThreadOption(resultSet.getInt("ThreadOption"));
                bean.setThreadStatus(resultSet.getInt("ThreadStatus"));
                bean.setThreadHasPoll(resultSet.getInt("ThreadHasPoll"));
                bean.setThreadViewCount(resultSet.getInt("ThreadViewCount"));
                bean.setThreadReplyCount(resultSet.getInt("ThreadReplyCount"));
                bean.setThreadIcon(resultSet.getString("ThreadIcon"));
                bean.setThreadDuration(resultSet.getInt("ThreadDuration"));
                retValue.add(bean);
            }
            return retValue;
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in ThreadDAOImplJDBC.getBeans_inForum_withSortSupport_limit_noscroll.");
        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.resetStatement(statement);
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    /*
     * Included columns: ThreadID, MemberName, LastPostMemberName, ThreadTopic, ThreadBody,
     *                   ThreadVoteCount, ThreadVoteTotalStars, ThreadCreationDate, ThreadLastPostDate, ThreadType,
     *                   ThreadOption, ThreadStatus, ThreadHasPoll, ThreadViewCount, ThreadReplyCount,
     *                   ThreadIcon, ThreadDuration
     * Excluded columns: ForumID
     */
    /**
     * Note: This is a customized method
     */
    private Collection getBeans_inForum_withSortSupport_limit_general(int forumID, int offset, int rowsToReturn, String sort, String order, boolean enable)
        throws IllegalArgumentException, DatabaseException {

        if (offset < 0) throw new IllegalArgumentException("The offset < 0 is not allowed.");
        if (rowsToReturn <= 0) throw new IllegalArgumentException("The rowsToReturn <= 0 is not allowed.");

        if ((!sort.equals("ThreadLastPostDate")) &&
            (!sort.equals("ThreadCreationDate")) &&
            (!sort.equals("MemberName")) &&
            (!sort.equals("ThreadReplyCount")) &&
            (!sort.equals("ThreadViewCount")) ) {
            throw new IllegalArgumentException("Cannot sort, reason: dont understand the criteria '" + sort + "'.");
        }

        if ((!order.equals("ASC")) &&
            (!order.equals("DESC")) ) {
            throw new IllegalArgumentException("Cannot sort, reason: dont understand the order '" + order + "'.");
        }

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        Collection retValue = new ArrayList();
        StringBuffer sql = new StringBuffer(512);
        sql.append("SELECT ThreadID, MemberName, LastPostMemberName, ThreadTopic, ThreadBody, ThreadVoteCount, ThreadVoteTotalStars, ThreadCreationDate, ThreadLastPostDate, ThreadType, ThreadOption, ThreadStatus, ThreadHasPoll, ThreadViewCount, ThreadReplyCount, ThreadIcon, ThreadDuration");
        sql.append(" FROM " + TABLE_NAME);
        sql.append(" WHERE ForumID = ?");
        if (enable) {
            sql.append(" AND ThreadStatus <> 1 ");
        } else {//disable
            sql.append(" AND ThreadStatus = 1 ");
        }
        sql.append(" ORDER BY " + sort + " " + order);// ColumnName, ASC|DESC
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            statement.setInt(1, forumID);
            statement.setMaxRows(offset + rowsToReturn);
            try {
                statement.setFetchSize(rowsToReturn);
            } catch (SQLException sqle) {
                //do nothing, postgreSQL doesnt support this method
            }
            resultSet = statement.executeQuery();
            boolean loop = resultSet.absolute(offset + 1);// the absolute method begin with 1 instead of 0 as in the LIMIT clause
            while (loop) {
                ThreadBean bean = new ThreadBean();
                bean.setThreadID(resultSet.getInt("ThreadID"));
                bean.setForumID(forumID);
                bean.setMemberName(resultSet.getString("MemberName"));
                bean.setLastPostMemberName(resultSet.getString("LastPostMemberName"));
                bean.setThreadTopic(resultSet.getString("ThreadTopic"));
                bean.setThreadBody(resultSet.getString("ThreadBody"));
                bean.setThreadVoteCount(resultSet.getInt("ThreadVoteCount"));
                bean.setThreadVoteTotalStars(resultSet.getInt("ThreadVoteTotalStars"));
                bean.setThreadCreationDate(resultSet.getTimestamp("ThreadCreationDate"));
                bean.setThreadLastPostDate(resultSet.getTimestamp("ThreadLastPostDate"));
                bean.setThreadType(resultSet.getInt("ThreadType"));
                bean.setThreadOption(resultSet.getInt("ThreadOption"));
                bean.setThreadStatus(resultSet.getInt("ThreadStatus"));
                bean.setThreadHasPoll(resultSet.getInt("ThreadHasPoll"));
                bean.setThreadViewCount(resultSet.getInt("ThreadViewCount"));
                bean.setThreadReplyCount(resultSet.getInt("ThreadReplyCount"));
                bean.setThreadIcon(resultSet.getString("ThreadIcon"));
                bean.setThreadDuration(resultSet.getInt("ThreadDuration"));
                retValue.add(bean);
                if (retValue.size() == rowsToReturn) break;// Fix the Sybase bug
                loop = resultSet.next();
            }//while
            return retValue;
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in ThreadDAOImplJDBC.getBeans_inForum_withSortSupport_limit_general.");
        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.resetStatement(statement);
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    /*
     * Included columns: ForumID
     */
    public void update_ForumID(int threadID, // primary key
                               int forumID)
        throws ObjectNotFoundException, DatabaseException, ForeignKeyNotFoundException {

        try {
            // @todo: modify the parameter list as needed
            // If this method does not change the foreign key columns, you can comment this block of code.
            DAOFactory.getForumDAO().findByPrimaryKey(forumID);
        } catch(ObjectNotFoundException e) {
            throw new ForeignKeyNotFoundException("Foreign key refers to table 'Forum' does not exist. Cannot update table 'Thread'.");
        }

        Connection connection = null;
        PreparedStatement statement = null;
        StringBuffer sql = new StringBuffer(512);
        sql.append("UPDATE " + TABLE_NAME + " SET ForumID = ?");
        sql.append(" WHERE ThreadID = ?");
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());

            // // column(s) to update
            statement.setInt(1, forumID);

            // primary key column(s)
            statement.setInt(2, threadID);

            if (statement.executeUpdate() != 1) {
                throw new ObjectNotFoundException("Cannot update table Thread where primary key = (" + threadID + ").");
            }
            m_dirty = true;
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in ThreadDAOImplJDBC.update_ForumID.");
        } finally {
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    /*
     * Included columns: ThreadStatus
     */
    public void update_ThreadStatus(int threadID, // primary key
                                    int threadStatus)
        throws ObjectNotFoundException, DatabaseException {

        ThreadBean.validateThreadStatus(threadStatus);

        Connection connection = null;
        PreparedStatement statement = null;
        StringBuffer sql = new StringBuffer(512);
        sql.append("UPDATE " + TABLE_NAME + " SET ThreadStatus = ?");
        sql.append(" WHERE ThreadID = ?");
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());

            // // column(s) to update
            statement.setInt(1, threadStatus);