/** @inheritDoc */ public List<WeblogEntryComment> getComments(CommentSearchCriteria csc) throws WebloggerException { List<Object> params = new ArrayList<Object>(); int size = 0; StringBuilder queryString = new StringBuilder(); queryString.append("SELECT c FROM WeblogEntryComment c "); StringBuilder whereClause = new StringBuilder(); if (csc.getEntry() != null) { params.add(size++, csc.getEntry()); whereClause.append("c.weblogEntry = ?").append(size); } else if (csc.getWeblog() != null) { params.add(size++, csc.getWeblog()); whereClause.append("c.weblogEntry.website = ?").append(size); } if (csc.getSearchText() != null) { params.add(size++, "%" + csc.getSearchText().toUpperCase() + "%"); appendConjuctionToWhereclause(whereClause, "upper(c.content) LIKE ?").append(size); } if (csc.getStartDate() != null) { Timestamp start = new Timestamp(csc.getStartDate().getTime()); params.add(size++, start); appendConjuctionToWhereclause(whereClause, "c.postTime >= ?").append(size); } if (csc.getEndDate() != null) { Timestamp end = new Timestamp(csc.getEndDate().getTime()); params.add(size++, end); appendConjuctionToWhereclause(whereClause, "c.postTime <= ?").append(size); } if (csc.getStatus() != null) { params.add(size++, csc.getStatus()); appendConjuctionToWhereclause(whereClause, "c.status = ?").append(size); } if (whereClause.length() != 0) { queryString.append(" WHERE ").append(whereClause); } if (csc.isReverseChrono()) { queryString.append(" ORDER BY c.postTime DESC"); } else { queryString.append(" ORDER BY c.postTime ASC"); } TypedQuery<WeblogEntryComment> query = strategy.getDynamicQuery(queryString.toString(), WeblogEntryComment.class); if (csc.getOffset() != 0) { query.setFirstResult(csc.getOffset()); } if (csc.getMaxResults() != -1) { query.setMaxResults(csc.getMaxResults()); } for (int i = 0; i < params.size(); i++) { query.setParameter(i + 1, params.get(i)); } return query.getResultList(); }
/** @inheritDoc */ public boolean getTagComboExists(List tags, Weblog weblog) throws WebloggerException { if (tags == null || tags.size() == 0) { return false; } StringBuilder queryString = new StringBuilder(); queryString.append("SELECT DISTINCT w.name "); queryString.append("FROM WeblogEntryTagAggregate w WHERE w.name IN ("); // Append tags as parameter markers to avoid potential escaping issues // The IN clause would be of form (?1, ?2, ?3, ..) List<Object> params = new ArrayList<Object>(tags.size() + 1); final String paramSeparator = ", "; int i; for (i = 0; i < tags.size(); i++) { queryString.append('?').append(i + 1).append(paramSeparator); params.add(tags.get(i)); } // Remove the trailing paramSeparator queryString.delete(queryString.length() - paramSeparator.length(), queryString.length()); // Close the brace of IN clause queryString.append(')'); if (weblog != null) { queryString.append(" AND w.weblog = ?").append(i + 1); params.add(weblog); } else { queryString.append(" AND w.weblog IS NULL"); } TypedQuery<String> q = strategy.getDynamicQuery(queryString.toString(), String.class); for (int j = 0; j < params.size(); j++) { q.setParameter(j + 1, params.get(j)); } List<String> results = q.getResultList(); // TODO: DatamapperPort: Since we are only interested in knowing whether // results.size() == tags.size(). This query can be optimized to just fetch COUNT // instead of objects as done currently return (results != null && results.size() == tags.size()); }
/** @inheritDoc */ public List<TagStat> getTags( Weblog website, String sortBy, String startsWith, int offset, int limit) throws WebloggerException { Query query; List queryResults; boolean sortByName = sortBy == null || !sortBy.equals("count"); List<Object> params = new ArrayList<Object>(); int size = 0; StringBuilder queryString = new StringBuilder(); queryString.append("SELECT w.name, SUM(w.total) FROM WeblogEntryTagAggregate w WHERE "); if (website != null) { params.add(size++, website.getId()); queryString.append(" w.weblog.id = ?").append(size); } else { queryString.append(" w.weblog IS NULL"); } if (startsWith != null && startsWith.length() > 0) { params.add(size++, startsWith + '%'); queryString.append(" AND w.name LIKE ?").append(size); } if (sortBy != null && sortBy.equals("count")) { sortBy = "w.total DESC"; } else { sortBy = "w.name"; } queryString.append(" GROUP BY w.name, w.total ORDER BY ").append(sortBy); query = strategy.getDynamicQuery(queryString.toString()); for (int i = 0; i < params.size(); i++) { query.setParameter(i + 1, params.get(i)); } if (offset != 0) { query.setFirstResult(offset); } if (limit != -1) { query.setMaxResults(limit); } queryResults = query.getResultList(); List<TagStat> results = new ArrayList<TagStat>(); if (queryResults != null) { for (Object obj : queryResults) { Object[] row = (Object[]) obj; TagStat ce = new TagStat(); ce.setName((String) row[0]); // The JPA query retrieves SUM(w.total) always as long ce.setCount(((Long) row[1]).intValue()); results.add(ce); } } if (sortByName) { Collections.sort(results, TAG_STAT_NAME_COMPARATOR); } else { Collections.sort(results, TAG_STAT_COUNT_REVERSE_COMPARATOR); } return results; }
/** @inheritDoc */ public List<WeblogEntry> getWeblogEntries(WeblogEntrySearchCriteria wesc) throws WebloggerException { WeblogCategory cat = null; if (StringUtils.isNotEmpty(wesc.getCatName()) && wesc.getWeblog() != null) { cat = getWeblogCategoryByName(wesc.getWeblog(), wesc.getCatName()); } List<Object> params = new ArrayList<Object>(); int size = 0; StringBuilder queryString = new StringBuilder(); if (wesc.getTags() == null || wesc.getTags().size() == 0) { queryString.append("SELECT e FROM WeblogEntry e WHERE "); } else { queryString.append("SELECT e FROM WeblogEntry e JOIN e.tags t WHERE "); queryString.append("("); for (int i = 0; i < wesc.getTags().size(); i++) { if (i != 0) { queryString.append(" OR "); } params.add(size++, wesc.getTags().get(i)); queryString.append(" t.name = ?").append(size); } queryString.append(") AND "); } if (wesc.getWeblog() != null) { params.add(size++, wesc.getWeblog().getId()); queryString.append("e.website.id = ?").append(size); } else { params.add(size++, Boolean.TRUE); queryString.append("e.website.visible = ?").append(size); } if (wesc.getUser() != null) { params.add(size++, wesc.getUser().getUserName()); queryString.append(" AND e.creatorUserName = ?").append(size); } if (wesc.getStartDate() != null) { Timestamp start = new Timestamp(wesc.getStartDate().getTime()); params.add(size++, start); queryString.append(" AND e.pubTime >= ?").append(size); } if (wesc.getEndDate() != null) { Timestamp end = new Timestamp(wesc.getEndDate().getTime()); params.add(size++, end); queryString.append(" AND e.pubTime <= ?").append(size); } if (cat != null) { params.add(size++, cat.getId()); queryString.append(" AND e.category.id = ?").append(size); } if (wesc.getStatus() != null) { params.add(size++, wesc.getStatus()); queryString.append(" AND e.status = ?").append(size); } if (wesc.getLocale() != null) { params.add(size++, wesc.getLocale() + '%'); queryString.append(" AND e.locale like ?").append(size); } if (StringUtils.isNotEmpty(wesc.getText())) { params.add(size++, '%' + wesc.getText() + '%'); queryString.append(" AND ( e.text LIKE ?").append(size); queryString.append(" OR e.summary LIKE ?").append(size); queryString.append(" OR e.title LIKE ?").append(size); queryString.append(") "); } if (wesc.getSortBy() != null && wesc.getSortBy().equals(WeblogEntrySearchCriteria.SortBy.UPDATE_TIME)) { queryString.append(" ORDER BY e.updateTime "); } else { queryString.append(" ORDER BY e.pubTime "); } if (wesc.getSortOrder() != null && wesc.getSortOrder().equals(WeblogEntrySearchCriteria.SortOrder.ASCENDING)) { queryString.append("ASC "); } else { queryString.append("DESC "); } TypedQuery<WeblogEntry> query = strategy.getDynamicQuery(queryString.toString(), WeblogEntry.class); for (int i = 0; i < params.size(); i++) { query.setParameter(i + 1, params.get(i)); } if (wesc.getOffset() != 0) { query.setFirstResult(wesc.getOffset()); } if (wesc.getMaxResults() != -1) { query.setMaxResults(wesc.getMaxResults()); } return query.getResultList(); }
public List getNextPrevEntries( WeblogEntry current, String catName, String locale, int maxEntries, boolean next) throws WebloggerException { if (current == null) { LOG.debug("current WeblogEntry cannot be null"); return Collections.emptyList(); } TypedQuery<WeblogEntry> query; WeblogCategory category; List<Object> params = new ArrayList<Object>(); int size = 0; String queryString = "SELECT e FROM WeblogEntry e WHERE "; StringBuilder whereClause = new StringBuilder(); params.add(size++, current.getWebsite()); whereClause.append("e.website = ?").append(size); params.add(size++, PubStatus.PUBLISHED); whereClause.append(" AND e.status = ?").append(size); if (next) { params.add(size++, current.getPubTime()); whereClause.append(" AND e.pubTime > ?").append(size); } else { // pub time null if current article not yet published, in Draft view if (current.getPubTime() != null) { params.add(size++, current.getPubTime()); whereClause.append(" AND e.pubTime < ?").append(size); } } if (catName != null) { category = getWeblogCategoryByName(current.getWebsite(), catName); if (category != null) { params.add(size++, category); whereClause.append(" AND e.category = ?").append(size); } else { throw new WebloggerException("Cannot find category: " + catName); } } if (locale != null) { params.add(size++, locale + '%'); whereClause.append(" AND e.locale like ?").append(size); } if (next) { whereClause.append(" ORDER BY e.pubTime ASC"); } else { whereClause.append(" ORDER BY e.pubTime DESC"); } query = strategy.getDynamicQuery(queryString + whereClause.toString(), WeblogEntry.class); for (int i = 0; i < params.size(); i++) { query.setParameter(i + 1, params.get(i)); } query.setMaxResults(maxEntries); return query.getResultList(); }