threaddaoimpljdbc.java

解觖java技术中后台无法上传数给的情况

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

    /**
     * Note: this is a customized method
     */
    public int getNumberOfEnableThreads_inForum(int forumID)
        throws AssertionException, DatabaseException {

        return getNumberOfBeans_inForum(forumID, true);
    }

    public int getNumberOfDisableThreads_inForum(int forumID)
        throws AssertionException, DatabaseException {

        return getNumberOfBeans_inForum(forumID, false);
    }

    private int getNumberOfBeans_inForum(int forumID, boolean enable)
        throws AssertionException, DatabaseException {

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        StringBuffer sql = new StringBuffer(512);
        sql.append("SELECT Count(*)");
        sql.append(" FROM " + TABLE_NAME);
        sql.append(" WHERE ForumID = ?");
        if (enable) {
            sql.append(" AND ThreadStatus <> 1 ");
        } else {//disable
            sql.append(" AND ThreadStatus = 1 ");
        }
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());
            statement.setInt(1, forumID);
            resultSet = statement.executeQuery();
            if (!resultSet.next()) {
                throw new AssertionException("Assertion in ThreadDAOImplJDBC.getNumberOfBeans_inForum.");
            }
            return resultSet.getInt(1);
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in ThreadDAOImplJDBC.getNumberOfBeans_inForum.");
        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    public int getNumberOfNormalEnableThreads_inForum(int forumID)
        throws AssertionException, DatabaseException {

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        StringBuffer sql = new StringBuffer(512);
        sql.append("SELECT Count(*)");
        sql.append(" FROM " + TABLE_NAME);
        sql.append(" WHERE ForumID = ? AND ThreadType = ").append(ThreadBean.THREAD_TYPE_DEFAULT);
        sql.append(" AND ThreadStatus <> 1 ");//mean enable thread
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());
            statement.setInt(1, forumID);
            resultSet = statement.executeQuery();
            if (!resultSet.next()) {
                throw new AssertionException("Assertion in ThreadDAOImplJDBC.getNumberOfNormalEnableThreads_inForum.");
            }
            return resultSet.getInt(1);
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in ThreadDAOImplJDBC.getNumberOfNormalEnableThreads_inForum.");
        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    public int getNumberOfEnableThreads()
        throws AssertionException, DatabaseException {

        return getNumberOfBeans(true);
    }

    public int getNumberOfDisableThreads()
        throws AssertionException, DatabaseException {

        return getNumberOfBeans(false);
    }

    private int getNumberOfBeans(boolean enable)
        throws AssertionException, DatabaseException {

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        StringBuffer sql = new StringBuffer(512);
        sql.append("SELECT Count(*)");
        sql.append(" FROM " + TABLE_NAME);
        if (enable) {
            sql.append(" WHERE ThreadStatus <> 1 ");
        } else {//disable
            sql.append(" WHERE ThreadStatus = 1 ");
        }
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());
            resultSet = statement.executeQuery();
            if (!resultSet.next()) {
                throw new AssertionException("Assertion in ThreadDAOImplJDBC.getNumberOfBeans.");
            }
            return resultSet.getInt(1);
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in ThreadDAOImplJDBC.getNumberOfBeans.");
        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }



    /************************************************
     * Customized methods come below
     ************************************************/


    /**
     * This is a customized method
     */
    private int findThreadID(int forumID, String memberName, Timestamp threadCreationDate)
        throws ObjectNotFoundException, DatabaseException {

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        StringBuffer sql = new StringBuffer(512);
        sql.append("SELECT ThreadID");
        sql.append(" FROM " + TABLE_NAME);
        sql.append(" WHERE ForumID = ? AND MemberName = ? AND ThreadCreationDate = ? ");
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());
            statement.setInt(1, forumID);
            statement.setString(2, memberName);
            statement.setTimestamp(3, threadCreationDate);
            resultSet = statement.executeQuery();
            if(!resultSet.next()) {
                throw new ObjectNotFoundException("Cannot find the ThreadID in table Thread.");
            }

            return resultSet.getInt("ThreadID");
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in ThreadDAOImplJDBC.findThreadID.");
        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    /*
     * Included columns: ThreadID, ForumID, MemberName, LastPostMemberName, ThreadTopic,
     *                   ThreadBody, ThreadVoteCount, ThreadVoteTotalStars, ThreadCreationDate, ThreadLastPostDate,
     *                   ThreadType, ThreadOption, ThreadStatus, ThreadHasPoll, ThreadViewCount,
     *                   ThreadReplyCount, ThreadIcon, ThreadDuration
     * Excluded columns:
     */
    public Collection getThreads_inFavorite_inMember(int memberID)
        throws DatabaseException {

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        Collection retValue = new ArrayList();
        StringBuffer sql = new StringBuffer(512);
        sql.append("SELECT thread.ThreadID, thread.ForumID, MemberName, LastPostMemberName, ThreadTopic, ThreadBody, ThreadVoteCount, ThreadVoteTotalStars, ThreadCreationDate, ThreadLastPostDate, ThreadType, ThreadOption, ThreadStatus, ThreadHasPoll, ThreadViewCount, ThreadReplyCount, ThreadIcon, ThreadDuration, ThreadAttachCount");
        sql.append(" FROM " + TABLE_NAME + " thread, " + FavoriteThreadDAO.TABLE_NAME + " favorite ");
        sql.append(" WHERE thread.ThreadID = favorite.ThreadID AND favorite.MemberID = ? ");
        //sql.append(" ORDER BY " + sort + " " + order);// ColumnName, ASC|DESC
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());
            statement.setInt(1, memberID);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                ThreadBean bean = new ThreadBean();
                bean.setThreadID(resultSet.getInt("ThreadID"));
                bean.setForumID(resultSet.getInt("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"));
                bean.setThreadAttachCount(resultSet.getInt("ThreadAttachCount"));
                retValue.add(bean);
            }
            return retValue;
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in ThreadDAOImplJDBC.getThreads_inFavorite_inMember.");
        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    public Collection getEnableThreads_withSortSupport_limit(int offset, int rowsToReturn, String sort, String order)
        throws IllegalArgumentException, DatabaseException {

        if (DBUtils.getDatabaseType() == DBUtils.DATABASE_MYSQL) {
            return getBeans_withSortSupport_limit_mysql(offset, rowsToReturn, sort, order, true);
        } else if (DBUtils.getDatabaseType() == DBUtils.DATABASE_NOSCROLL) {
            return getBeans_withSortSupport_limit_noscroll(offset, rowsToReturn, sort, order, true);
        }
        return getBeans_withSortSupport_limit_general(offset, rowsToReturn, sort, order, true);
    }

    public Collection getDisableBeans_withSortSupport_limit(int offset, int rowsToReturn, String sort, String order)
        throws IllegalArgumentException, DatabaseException {

        if (DBUtils.getDatabaseType() == DBUtils.DATABASE_MYSQL) {
            return getBeans_withSortSupport_limit_mysql(offset, rowsToReturn, sort, order, false);
        } else if (DBUtils.getDatabaseType() == DBUtils.DATABASE_NOSCROLL) {
            return getBeans_withSortSupport_limit_noscroll(offset, rowsToReturn, sort, order, false);
        }
        return getBeans_withSortSupport_limit_general(offset, rowsToReturn, sort, order, false);
    }