fetcher.java

openfans 0.1 using: eclipse, maven, spring, hibernate, mysql,common-validator,sitemesh.....

	}

	public Page getPagedRootTags(String order, int pageNum, int pageSize) {
		String sql = "from RootTag";
		return getPagedObject(sql, order, pageNum, pageSize);
	}

	/**
	 * 根据传入的from及之后的语句生成Page分页对象
	 * 
	 * @param countSql
	 * @param listSql
	 * @param order
	 * @param pageNum
	 * @param pageSize
	 * @return
	 */
	private Page getPagedObject(String sql, String order, int pageNum,
			int pageSize) {
		Page p = new Page();
		String countSql = "select count(*) " + sql;
		int i = getCount(countSql);
		List l = getPagedList(sql + " order by " + order, pageNum, pageSize);
		p.setCount(i);
		p.setList(l);
		p.setPageNum(pageNum);
		p.setPageSize(pageSize);
		p.cacPageCount();
		return p;
	}

	public Page getPagedFeed(int pageNum, int pageSize) {
		return getPagedObject("from Feed","subscribeCount desc", pageNum,
				pageSize);
	}

	public List getPagedTags(int pageNum, int pageSize, String order) {
		return getPagedList("from Tag  where type=0 order by " + order,
				pageNum, pageSize);
	}

	/**
	 * 取最后的几个公告，使用seq实现置顶功能
	 * 
	 * @param i
	 * @return
	 */
	public List getRecentBoardItem(int i) {
		return getList("from BoardItem order by seq desc", 0, i);
	}

	public List getRecommendRootTags() {
		return getHibernateTemplate().find(
				"from RootTag where recommendated is true");
	}

	public List getRelArticles(Article article) {
		final int articleId = article.getArticleId();
		final String s = "select * from T_ARTICLE where ARTICLE_ID in (SELECT ARTICLE_ID from T_ARTICLE_TAG where TAG_ID in (select TAG_ID from T_ARTICLE_TAG where article_id="
				+ articleId + ")) && ARTICLE_ID<>" + articleId;
		return getListByNativeSQL(Article.class, s);
	}

	private List getListByNativeSQL(final Class cls, final String sql) {
		return (List) getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException {
				return session.createSQLQuery(sql).addEntity(cls).list();

			}
		});
	}

	private Integer getCountByNativeSQL(final String sql) {
		return (Integer) getHibernateTemplate().execute(
				new HibernateCallback() {
					public Object doInHibernate(Session session)
							throws HibernateException {
						return (Integer) (session.createSQLQuery(sql)
								.addScalar("count", Hibernate.INTEGER)
								.uniqueResult());
					}
				});
	}

	private List getPagedListByNativeSQL(final Class cls, String sql,
			String order, int pageNum, int pageSize) {
		final String s = sql + " order by " + order + " limit " + (pageNum - 1)
				* pageSize + "," + pageSize;
		return getListByNativeSQL(cls, s);
	}

	public Tag getTag(int id) {
		return (Tag) getHibernateTemplate().get(Tag.class, new Integer(id));

	}

	public Tag getTagByTitle(String title) {
		List l = getHibernateTemplate().find(
				"from Tag where title='" + title + "'");
		if (l != null && l.size() > 0) {
			return (Tag) l.get(0);
		}
		return null;
	}

	public List getTagRelRootTag(Tag tag) {
		return getHibernateTemplate()
				.find(
						"select tag from RootTag tag,TagRelation tr where tr.tag=tag and tr.rtag.tagId="
								+ tag.getTagId());
	}

	public List getUserArticle(String name) {
		return getHibernateTemplate().find(
				"from Article article where article.fans.name ='" + name
						+ "' order by submit_date desc");

	}

	public List listFans() {
		return getHibernateTemplate().find("from Fans");

	}

	/**
	 * 
	 * Author:pesome<br>
	 * Time:2006-12-20 下午09:17:53<br>
	 * 
	 * @param pageNum
	 * @param pageSize
	 * @param fansId
	 * @param order
	 * @return
	 */
	public List getPagedPostMessages(int pageNum, int pageSize, int fansId,
			String order) {
		return getPagedList("from Message  m where m.fromFans.fansId=" + fansId
				+ " order by " + order, pageNum, pageSize);
	}

	public List getPagedReceiveMessages(int pageNum, int pageSize, int fansId,
			String order) {
		return getPagedList("from Message  m where m.toFans.fansId=" + fansId
				+ " order by " + order, pageNum, pageSize);
	}

	public int getNewMessagesCount(int fansId) {
		return getCount("select count(*) from Message m where m.status="
				+ Message.UNREAD + " and m.toFans.fansId=" + fansId);
	}

	public List listGroup() {
		return getHibernateTemplate().find("from Group");
	}

	public List searchTags(String title) {
		return getHibernateTemplate().find(
				"from Tag tag where tag.title ='" + title + "'");
	}

	/**
	 * 获得同一个feed下的其它feedItem
	 * 
	 * @param feedItem
	 * @return
	 */
	public List getRelFeedItems(FeedItem feedItem) {
		final int feedId = feedItem.getFeed().getFeedId();
		final String sql = "select * from T_FeedItem where FEED_ID =" + feedId;
		return getPagedListByNativeSQL(FeedItem.class, sql, " id desc", 1, 20);
	}

	public List getAllRootTags() {
		return getHibernateTemplate().find("from RootTag");
	}

	public List getTagFeedItem(Tag tag) {
		return getHibernateTemplate().find(
				"from TagFeedItem t where t.tag.tagId=" + tag.getTagId()
						+ " order by id desc");
	}

	public Snippet getSnippetByUrl(String url) {
		List l = getHibernateTemplate().find(
				"from Snippet where url='" + url + "'");
		if (l != null && l.size() > 0) {
			return (Snippet) l.get(0);
		}
		return null;
	}

	public List getRecentRootTags(int i, String order) {
		return getPagedList("from RootTag" + " order by " + order, 1, i);
	}

	public List getRecentFans(int i, String order) {
		return getPagedList("from Fans" + " order by " + order, 1, i);
	}

	public Page getPagedFansSnippet(Fans fans, String query, String order,
			int pageNum, int pageSize) {
		StringBuffer sb = new StringBuffer();
		sb.append(" from T_FANS_SNIPPET where fans_id=");
		sb.append(fans.getFansId());
		String sql = generateSearchFansSnippetSql(query, sb);
		return getPagedObjectByNativeSql(FansSnippet.class, sql, order,
				pageNum, pageSize);
	}

	public Page getPagedSnippet(String query, String order, int pageNum,
			int pageSize) {
		StringBuffer sb = new StringBuffer();
		sb.append(" from T_SNIPPET where privat=false");
		String sql = generateSearchSnippetSql(query, sb);
		return getPagedObjectByNativeSql(Snippet.class, sql, order, pageNum,
				pageSize);
	}

	private String generateSearchSnippetSql(String query, StringBuffer sb) {
		// 不指定query则代表全部;
		if (null != query && query.length() > 0) {
			sb.append(" and id in (SELECT snippet_id from T_SNIPPET_TAG ");

			generateTagSearchSql("snippet_id", query, sb);
		}
		return sb.toString();
	}

	private String generateSearchFansSnippetSql(String query, StringBuffer sb) {
		// 不指定query则代表全部;
		if (null != query && query.length() > 0) {
			sb
					.append(" and id in (SELECT fans_snippet_id from T_FANS_SNIPPET_TAG ");

			generateTagSearchSql("fans_snippet_id", query, sb);
		}
		return sb.toString();
	}

	/**
	 * 在Tag搜索中需与tag表联合查询的相同逻辑
	 * 
	 * @param column
	 * @param query
	 * @param sb
	 */
	private void generateTagSearchSql(String column, String query,
			StringBuffer sb) {
		sb.append("where tag_id in (");
		String[] tags = query.split(" ");
		int i = 0;
		for (int j = 0; j < tags.length; j++) {
			if (!" ".equals(tags[j])) {
				sb.append("(select tag_id from T_TAG where title='");
				sb.append(tags[j]);
				sb.append("'),");
				i++;
			}
		}
		sb.deleteCharAt(sb.length() - 1);
		sb.append(")");
		sb.append(" group by ");
		sb.append(column);
		sb.append(" having count(*)>=");
		sb.append(i);
		sb.append(")");
	}

	public Snippet getSnippet(int id) {
		return (Snippet) getHibernateTemplate().get(Snippet.class,
				new Integer(id));

	}
}