dbforum.java

java开发的一套非常好用的oa系统

        if (!countQuery && sortField == JiveGlobals.THREAD_NAME) {
            query.append(" AND jiveThread.threadID=jiveMessage.threadID");
        }
        // Sort on properties
        if (!countQuery && sortField == JiveGlobals.EXTENDED_PROPERTY) {
            query.append(" AND jiveThread.threadID=propTable.threadID");
        }

        // Creation date range
        if (filterCreationDate) {
            if (resultFilter.getCreationDateRangeMin() != null) {
                query.append(" AND jiveThread.creationDate >= '");
                query.append(StringUtils.dateToMillis(
                        resultFilter.getCreationDateRangeMin()));
                query.append("'");
            }
            if (resultFilter.getCreationDateRangeMax() != null) {
                query.append(" AND jiveThread.creationDate <= '");
                query.append(StringUtils.dateToMillis(
                        resultFilter.getCreationDateRangeMax()));
                query.append("'");
            }
        }

        // Modified date range
        if (filterModifiedDate) {
            if (resultFilter.getModifiedDateRangeMin() != null) {
                query.append(" AND jiveThread.modifiedDate >= '");
                query.append(StringUtils.dateToMillis(
                        resultFilter.getModifiedDateRangeMin()));
                query.append("'");
            }
            if (resultFilter.getModifiedDateRangeMax() != null) {
                query.append(" AND jiveThread.modifiedDate <= '");
                query.append(StringUtils.dateToMillis(
                        resultFilter.getModifiedDateRangeMax()));
                query.append("'");
            }
        }

        // Moderation range
        int moderationRangeMin = resultFilter.getModerationRangeMin();
        // If the minimum moderation value was not set, it should default
        // to the minimum threshold for the forum.
        if (moderationRangeMin == ResultFilter.NULL_INT) {
            moderationRangeMin = getModerationMinThreadValue();
        }
        int moderationRangeMax = resultFilter.getModerationRangeMax();
        if (moderationRangeMin == moderationRangeMax) {
            query.append(" AND jiveThread.modValue = ");
            query.append(moderationRangeMin);
        }
        else {
            // Ignore any min less than negative 1 million.
            if (moderationRangeMin > -1000000) {
                query.append(" AND jiveThread.modValue >= ");
                query.append(moderationRangeMin);
            }
            // Use a max value if it's defined.
            if (moderationRangeMax != ResultFilter.NULL_INT) {
                query.append(" AND jiveThread.modValue <= ");
                query.append(moderationRangeMax);
            }
        }

        // ORDER BY
        if (!countQuery) {
            query.append(" ORDER BY ");
            switch(sortField) {
                case JiveGlobals.THREAD_NAME:
                    query.append("jiveMessage.subject");
                    break;
                case JiveGlobals.MODIFIED_DATE:
                    query.append("jiveThread.modifiedDate");
                    break;
                case JiveGlobals.CREATION_DATE:
                    query.append("jiveThread.creationDate");
                    break;
                case JiveGlobals.EXTENDED_PROPERTY:
                    query.append("propTable.propValue");
                    break;
            }
            if (resultFilter.getSortOrder() == ResultFilter.DESCENDING) {
                query.append(" DESC");
            }
            else {
                query.append(" ASC");
            }
        }
        return query.toString();
    }

    /**
     * Returns the SQL statement corresponding to a ResultFilter for messages.
     */
    protected String getMessageListSQL(ResultFilter resultFilter, boolean countQuery) {
        int sortField = resultFilter.getSortField();
        // Make sure the sort field is valid.
        if (!countQuery && !( sortField == JiveGlobals.MODIFIED_DATE ||
                sortField == JiveGlobals.CREATION_DATE ||
                sortField == JiveGlobals.MESSAGE_SUBJECT ||
                sortField == JiveGlobals.MESSAGE_BODY ||
                ( sortField == JiveGlobals.EXTENDED_PROPERTY &&
                  resultFilter.getSortPropertyName() != null
                )
              )
            )
        {
            throw new IllegalArgumentException("The specified sort field is not valid.");
        }

        // We'll accumlate the query in a StringBuffer.
        StringBuffer query = new StringBuffer(80);
        if (!countQuery) {
            query.append("SELECT jiveMessage.messageID");
        }
        else {
            query.append("SELECT count(1)");
        }

        boolean filterUser = resultFilter.getUserID() != ResultFilter.NULL_INT;
        boolean filterCreationDate = resultFilter.getCreationDateRangeMin() != null ||
                                     resultFilter.getCreationDateRangeMax() != null;
        boolean filterModifiedDate = resultFilter.getModifiedDateRangeMin() != null ||
                                     resultFilter.getModifiedDateRangeMax() != null;
        int propertyCount = resultFilter.getPropertyCount();

        // SELECT -- need to add value that we sort on
        if (!countQuery) {
            switch(sortField) {
                case JiveGlobals.MESSAGE_SUBJECT:
                    query.append(", subject");
                    break;
                case JiveGlobals.MODIFIED_DATE:
                    query.append(", modifiedDate");
                    break;
                case JiveGlobals.CREATION_DATE:
                    query.append(", creationDate");
                    break;
                case JiveGlobals.EXTENDED_PROPERTY:
                    query.append(", propTable.propValue");
                    break;
            }
        }

        // FROM -- values (in addition to jiveThread)
        query.append(" FROM jiveMessage");
        for (int i=0; i<propertyCount; i++) {
            query.append(", jiveMessageProp p").append(i);
        }
        if (!countQuery &&
                resultFilter.getSortField() == JiveGlobals.EXTENDED_PROPERTY)
        {
            query.append(", jiveMessageProp propTable");
        }

        // WHERE BLOCK
        query.append(" WHERE forumID=").append(this.id);
        // User
        if (filterUser) {
            query.append(" AND userID=").append(resultFilter.getUserID());
        }
        // Properties
        for (int i=0; i<propertyCount; i++) {
            query.append(" AND jiveMessage.messageID=p").append(i).append(".messageID");
            query.append(" AND p").append(i).append(".name='");
            query.append(resultFilter.getPropertyName(i));
            query.append("' AND p").append(i).append(".propValue='");
            query.append(resultFilter.getPropertyValue(i)).append("'");
        }
        // Sort on properties
        if (!countQuery && sortField == JiveGlobals.EXTENDED_PROPERTY) {
            query.append(" AND jiveMessage.messageID=propTable.messageID");
        }

        // Creation date range
        if (filterCreationDate) {
            if (resultFilter.getCreationDateRangeMin() != null) {
                query.append(" AND creationDate >= '");
                query.append(StringUtils.dateToMillis(
                        resultFilter.getCreationDateRangeMin()));
                query.append("'");
            }
            if (resultFilter.getCreationDateRangeMax() != null) {
                query.append(" AND creationDate <= '");
                query.append(StringUtils.dateToMillis(
                        resultFilter.getCreationDateRangeMax()));
                query.append("'");
            }
        }

        // Modified date range
        if (filterModifiedDate) {
            if (resultFilter.getModifiedDateRangeMin() != null) {
                query.append(" AND modifiedDate >= '");
                query.append(StringUtils.dateToMillis(
                        resultFilter.getModifiedDateRangeMin()));
                query.append("'");
            }
            if (resultFilter.getModifiedDateRangeMax() != null) {
                query.append(" AND modifiedDate <= '");
                query.append(StringUtils.dateToMillis(
                        resultFilter.getModifiedDateRangeMax()));
                query.append("'");
            }
        }

        // Moderation range
        int moderationRangeMin = resultFilter.getModerationRangeMin();
        // If the minimum moderation value was not set, it should default
        // to the minimum threshold for the forum.
        if (moderationRangeMin == ResultFilter.NULL_INT) {
            moderationRangeMin = getModerationMinMessageValue();
        }
        int moderationRangeMax = resultFilter.getModerationRangeMax();
        if (moderationRangeMin == moderationRangeMax) {
            query.append(" AND modValue = ");
            query.append(moderationRangeMin);
        }
        else {
            // Ignore any min less than negative 1 million.
            if (moderationRangeMin > -1000000) {
                query.append(" AND modValue >= ");
                query.append(moderationRangeMin);
            }
            // Use a max value if it's defined.
            if (moderationRangeMax != ResultFilter.NULL_INT) {
                query.append(" AND modValue <= ");
                query.append(moderationRangeMax);
            }
        }

        // ORDER BY
        if (!countQuery) {
            query.append(" ORDER BY ");
            switch(sortField) {
                case JiveGlobals.MESSAGE_SUBJECT:
                    query.append("subject");
                    break;
                case JiveGlobals.MODIFIED_DATE:
                    query.append("modifiedDate");
                    break;
                case JiveGlobals.CREATION_DATE:
                    query.append("creationDate");
                    break;
                case JiveGlobals.EXTENDED_PROPERTY:
                    query.append("propTable.propValue");
                    break;
            }
            if (resultFilter.getSortOrder() == ResultFilter.DESCENDING) {
                query.append(" DESC");
            }
            else {
                query.append(" ASC");
            }
        }
        return query.toString();
    }

    /**
     * Returns a block of threadID's from a query and performs transparent
     * caching of those blocks. The two parameters specify a database query
     * and a startIndex for the results in that query.
     *
     * @param query the SQL thread list query to cache blocks from.
     * @param startIndex the startIndex in the list to get a block for.
     */
    protected long[] getThreadBlock(String query, int startIndex) {
        // First, discover what block number the results will be in.
        int blockID = startIndex / THREAD_BLOCK_SIZE;
        int blockStart = blockID * THREAD_BLOCK_SIZE;
        // Now, check cache to see if the block is already cached. The key is
        // simply the query plus the blockID.
        String key = query + blockID;
        CacheableLongArray longArray = (CacheableLongArray)threadListCache.get(key);
        //If already in cache, return the block.
        if (longArray != null) {
            /**
             * The actual block may be smaller than THREAD_BLOCK_SIZE. If that's
             * the case, it means two things:
             *  1) We're at the end boundary of all the results.
             *  2) If the start index is greater than the length of the current
             *     block, than there aren't really any results to return.
             */
            long [] threads = longArray.getLongArray();
            if (startIndex >= blockStart + threads.length) {
                // Return an empty array
                return EMPTY_BLOCK;
            }
            else {
                return threads;
            }
        }
        // Otherwise, we have to load up the block from the database.
        else {
            LongList threadsList = new LongList(THREAD_BLOCK_SIZE);
            Connection con = null;
            Statement stmt = null;
            try {
                con = ConnectionManager.getConnection();
                stmt = con.createStatement();
                // Set the maxium number of rows to end at the end of this block.
                ConnectionManager.setMaxRows(stmt, THREAD_BLOCK_SIZE * (blockID+1));
                ResultSet rs = stmt.executeQuery(query);
                // Grab THREAD_BLOCK_ROWS rows at a time.
                ConnectionManager.setFetchSize(rs, THREAD_BLOCK_SIZE);
                // Many JDBC drivers don't implement scrollable cursors the real
                // way, but instead load all results into memory. Looping through
                // the results ourselves is more efficient.
                for (int i=0; i<blockStart; i++) {
                    rs.next();
                }
                // Keep reading results until the result set is exaughsted or
                // we come to the end of the block.
                int count = 0;
                while (rs.next() && count < THREAD_BLOCK_SIZE) {
                    threadsList.add(rs.getLong(1));
                    count++;
                }
            }
            catch( SQLException sqle ) {
                sqle.printStackTrace();
            }
            finally {
                try {  stmt.close(); }
                catch (Exception e) { e.printStackTrace(); }
                try {  con.close();   }