cforumdao.java

基于struts+hibernate的电子商务网站。可运行。数据库mysql

package tarena.dao.pages;

import java.math.BigInteger;
import java.sql.Date;
import java.sql.Timestamp;

import java.util.LinkedList;
import java.util.List;

import org.hibernate.Hibernate;

import tarena.dao.BaseHibernateDAO;
import tarena.data.AbractTopic;
import tarena.data.SearchTopic;
import tarena.entity.Post;
import tarena.form.SearchForm;

@SuppressWarnings("unchecked")
public class CForumDAO extends BaseHibernateDAO {
	
	/**
	 * 某板块下的主题个数。
	 * @param sectionid
	 * @return
	 */
	public Integer countTopic(){
		StringBuffer sql = new StringBuffer();
		sql.append("select count(*) re from topic");
		Integer re =(Integer)getSession()
					.createSQLQuery(sql.toString())
					.addScalar("re",Hibernate.INTEGER)
					.uniqueResult();
		return re;
	}
	
	
	/**
	 * 某板块下的主题个数。
	 * @param sectionid
	 * @return
	 */
	public Integer countTopic(Integer sectionID){
		StringBuffer sql = new StringBuffer();
		sql.append("select count(*) re from topic where sid="+sectionID);
		Integer re =(Integer)getSession()
					.createSQLQuery(sql.toString())
					.addScalar("re",Hibernate.INTEGER)
					.uniqueResult();
		return re;
	}
	/**
	 * 某板块下帖子的个数。
	 * @param sectionID
	 * @return
	 */
	public Integer countPostBySectionId(Integer sectionID){
		StringBuffer sql = new StringBuffer();
		sql.append("select count(*) n from post p,topic t where p.tid=t.id and t.sid = ?");
		return (Integer)getSession()
			   .createSQLQuery(sql.toString())
			   .addScalar("n",Hibernate.INTEGER)
			   .setParameter(0, sectionID)
			   .uniqueResult();
	}
	/**
	 * 某板块下某个时间段内帖子的个数。
	 * @param sectionID
	 * @param start
	 * @param end
	 * @return
	 */
	public Integer countPostBySectionId(Integer sectionID,Date start,Date end){
		StringBuffer sql = new StringBuffer();
		sql.append("select count(*) n from post p,topic t where p.tid=t.id  and t.sid = ? and (p.sendtime between ? and ?)");
		return (Integer)getSession()
			   .createSQLQuery(sql.toString())
			   .addScalar("n",Hibernate.INTEGER)
			   .setParameter(0, sectionID)
			   .setParameter(1, start)
			   .setParameter(2, end)
			   .uniqueResult();
	}
	
	/**
	 * 某主题下帖子的个数。
	 * @param topicId
	 * @return
	 */
	public Integer countPost(Integer topicId){
		StringBuffer sql = new StringBuffer();
		sql.append("select count(*) n from post group by tid having tid = ?");
		return (Integer)getSession()
			   .createSQLQuery(sql.toString())
			   .addScalar("n",Hibernate.INTEGER)
			   .setParameter(0, topicId)
			   .uniqueResult();
	}
	
	/**
	 * 分页查询某板块下的抽象主题
	 * SQL语句原型：
	 * select temp.topicid,      	--主题ID
	 * 		  temp.topictitle,		--主题标题
	 * 		  temp.pubuser			--主题发帖人
	 * 		  temp.backamt,			--主题回复个数
	 * 		  temp.num,				--主题浏览次数
	 * 		  temp.mintime,			--主题的发布时间
	 * 		  temp.maxfloor,		--主题的楼层数
	 * 		  u.username,			--主题最近回复的用户名
	 * 		  temp.maxtime  		--主题最近回复的时间
	 * from (  
	 * 	select t.id topicid,
	 * 		   t.title topictitle,
	 * 		   count(*)-1 backamt,
	 * 		   t.viewnum num,
	 * 		   min(p.sendtime) mintime,
	 * 		   max(floor) maxfloor,
	 * 		   max(p.sendtime) maxtime 
	 *  from topic t,post p 
	 *  where p.tid = t.id and t.sid = ?
	 *  group by p.tid 
	 *  order by max(sendtime) desc   
	 * ) temp ,post pp ,user u 
	 * where pp.floor = temp.maxfloor and pp.tid=temp.topicid and u.id=pp.uid;
	 * 
	 * @param first 从第几条开始显示
	 * @param maxre 要显示结果的个数
	 * @param sid	板块id。
	 * @param topiclettop 主题的类型，有三类：置顶、非置顶、全部
	 * 
	 * @return 查询到的结果。
	 */
	public List<AbractTopic> listTopic(Integer first,Integer maxre,Integer sid,int topiclettop){
		StringBuffer sql = new StringBuffer();
		sql.append(" select temp.topicid,");
		sql.append(" temp.topictitle, ");
		sql.append(" temp.pubuser,");
		sql.append(" temp.backamt,");
		sql.append(" temp.num,");
		sql.append(" temp.mintime,");
		sql.append(" u.username,");
		sql.append(" temp.maxtime");
		sql.append(" from ( ");
		sql.append(" select t.id as topicid,t.title as topictitle,t.publisher as pubuser, count(*)-1 as backamt,t.viewnum as num,");
		sql.append(" min(p.sendtime) as mintime, max(floor) as maxfloor,max(p.sendtime) as maxtime ");
		sql.append(" from topic as t,post as p where p.tid = t.id and t.sid = ? ");
		if(topiclettop == 0 || topiclettop == 1){
			sql.append(" and t.lettop ="+topiclettop);
		}
		sql.append(" group by p.tid order by max(p.sendtime) desc ");
		sql.append(" ) temp ,post pp ,user u");
		sql.append(" where pp.floor = temp.maxfloor and pp.tid=temp.topicid and u.id=pp.uid");
		
		List<Object[]> temp = getSession()
							 .createSQLQuery(sql.toString())
//							 .addScalar("backamt", Hibernate.INTEGER)
							 .setParameter(0, sid)
							 .setFirstResult(first)
							 .setMaxResults(maxre)
							 .list();
		
		List<AbractTopic> re = new LinkedList<AbractTopic>();
		for(Object[] objs:temp){			
			re.add(new AbractTopic((Integer)objs[0],
								   (String)objs[1],
								   (String)objs[2],
								   ((BigInteger)objs[3]).intValue(),
								   (Integer)objs[4],
								   (Timestamp)objs[5],
								   (String)objs[6],
								   (Timestamp)objs[7]));
		}		
		return re;
	}
		
	/**
	 * 分页查询某主题下的所有帖子，按发布时间(楼层)排列
	 * @param first	显示第几页，从0开始。
	 * @param maxre	每页显示的个数
	 * @param tid 	主题id
	 * @param desc  是否按发表时间进行降序排列
	 * @return
	 */
	public List<Post> listPosts(Integer first,Integer maxre,Integer tid,boolean desc){ 
		StringBuffer sql = new StringBuffer();
		sql.append("from Post p where p.topic.id = ? order by p.sendtime ");
		sql.append(desc?"desc":"asc");
		return (List<Post>)getSession()
			   .createQuery(sql.toString())			  
			   .setParameter(0, tid)
			   .setFirstResult(first)
			   .setMaxResults(maxre)
			   .list();		
	}
	
	/**
	 * 分页查询主题，按发布时间(楼层)排列
	 * @param condition
	 * @return
	 */
//	public List<SearchTopic> listAbstractTopics(){
//		StringBuffer sql = new StringBuffer(); 
//		sql.append("select t.id,u.username from user u,post p,topic t where p.uid=u.id and p.floor=0 and p.tid=t.id");			
//		
//		
//		List<Object[]> re =  getSession().createSQLQuery(sql.toString())				
//							.list();
//		for(Object[] aa:re){
//			System.out.println("update topic set publisher='"+aa[1]+"' where id="+aa[0]+";");
//		}
//		System.out.println(re.size());
//		return null;
//	}
	
	/**
	 * 分页查询主题，按发布时间(楼层)排列
	 * @param condition
	 * @param first 显示第几页，从0开始。
	 * @param maxre 每页显示的个数
	 * @return
	 */
	public List<SearchTopic> listAbstractTopics(SearchForm condition,Integer first,Integer maxre){
		StringBuffer sql = new StringBuffer(); 
		sql.append("select new tarena.data.SearchTopic(t.id,t.title,t.section.sectionname,minelement(p) as mine,maxelement(p) as maxe,count(p),t.viewnum) ");	
		sql.append("from Topic t left outer join t.posts p  ");		
		sql.append("where 1=1 ");//加上1=1条件是为了方便后面SQL语句条件的拼接		
		if(condition.getAuthor()!=null&&!condition.getAuthor().equals("")){			
			sql.append(" and t.publisher='"+condition.getAuthor()+"' ");
		}
		if(condition.getTitle()!=null&&!condition.getTitle().equals("")){			
			sql.append(" and t.title like '%"+condition.getTitle()+"%' ");
		}
		if(condition.getDigest()!=null){			
			sql.append(" and t.digest="+condition.getDigest());
		}
		sql.append(" group by t.id ");
//		System.out.println(sql.toString());
		return  getSession().createQuery(sql.toString())
				.setFirstResult(first)
				.setMaxResults(maxre)
				.list();		
	}
	
	/**
	 * 查询主题的个数。
	 * @param condition
	 * @param first 显示第几页，从0开始。
	 * @param maxre 每页显示的个数
	 * @return
	 */
	public Integer countAbstractTopics(SearchForm condition){
		StringBuffer sql = new StringBuffer(); 
		sql.append("select count(distinct t.id) re ");	
		sql.append("from topic t ");		
		sql.append("where 1=1 ");//加上1=1条件是为了方便后面SQL语句条件的拼接		
		if(condition.getAuthor()!=null&&!condition.getAuthor().equals("")){			
			sql.append(" and t.publisher='"+condition.getAuthor()+"' ");
		}
		if(condition.getTitle()!=null&&!condition.getTitle().equals("")){			
			sql.append(" and t.title like '%"+condition.getTitle()+"%' ");
		}
		if(condition.getDigest()!=null){			
			sql.append(" and t.digest="+condition.getDigest());
		}
//		sql.append(" group by t.id ");
		System.out.println(sql.toString());
		return  (Integer)getSession()
				.createSQLQuery(sql.toString())
				.addScalar("re", Hibernate.INTEGER)
				.uniqueResult();		
	}
	
	
}