/** @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();
}
