defaultdao.java

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

package tarena.dao.pages;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Vector;

import org.hibernate.Query;

import tarena.dao.BaseHibernateDAO;
import tarena.data.AbractAnnounce;
import tarena.data.AbractBrand;
import tarena.data.AbractCategory;
import tarena.data.AbractProduct;
import tarena.global.GetPhotoAddress;

@SuppressWarnings("unchecked")
public class DefaultDAO extends BaseHibernateDAO{
	/**
	 * 查询商品购买次数最多的3级分类。按降序排列。
	 * @return 查询到的分类集合
	 */
	public List<AbractCategory> ListHotCategorys(int num){
		StringBuffer hql=new StringBuffer();
		hql.append("select new tarena.data.AbractCategory(c.id,c.cname) ");
		hql.append("from Product p , Category c where p.category.id=c.id  group by c.id order by sum(p.viewpopluarity) desc limit 1,"+num);
		return getSession().createQuery(hql.toString())
							.setFirstResult(0)
							.setMaxResults(num)
							.list();
	}
	/**
	 * 查询商品品牌的购买排行。按降序排列。
	 * @return 查询到的分类集合
	 */
	public List<AbractBrand> ListHotBrand(int num){
		StringBuffer hql=new StringBuffer();
		hql.append("select new tarena.data.AbractBrand(b.id,b.enName,b.cnName) ");
		hql.append("from Brand b , Product p where p.brand.id=b.id  group by b.id order by sum(p.viewpopluarity) desc");		
		return getSession().createQuery(hql.toString())
							.setFirstResult(0)
							.setMaxResults(num)
							.list();
	}
	
	/**
	 * 列出给定日期之间的热销商品排行
	 * @param start - 开始的日期
	 * @param end - 结束日期
	 * @param num - 显示结果的条数
	 * @return - 查询到的集合
	 */
	public List<AbractProduct> listHotProductBetween(String start,String end,int num){
		StringBuffer hql=new StringBuffer();
		SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
		Date s=null,e=null;
		try {
			s = sdf.parse(start);
			e = sdf.parse(end);
		} catch (ParseException e1) {
			e1.printStackTrace();
		}
		
		hql.append("select p.id,p.pname,p.price,count(t.id) ");
		hql.append("from Transaction t,Product p,Orders o ");
		hql.append("where (o.ordertime between ? and ?) ");
		hql.append("and (p.id=t.product.id) ");
		hql.append("and (t.orders.id=o.id)");
		hql.append("group by t.product.id ");
		hql.append("order by count(t.id) desc");
		
		List<Object[]> list = getSession().createQuery(hql.toString())
							.setParameter(0, s)
							.setParameter(1, e)
							.setFirstResult(0)
							.setMaxResults(num)
							.list();
		List<AbractProduct> products = new Vector<AbractProduct>();
		for(Object[] o:list){
			try {
				products.add(new AbractProduct((Integer)o[0],(String)o[1],(Double)o[2],(Integer)o[3]));
			} catch (RuntimeException e1) {
				products.add(new AbractProduct(((Long)o[3]).intValue(),(String)o[1],(Double)o[2],((Long)o[3]).intValue()));
			}
		}
		return products;
	}
	
	/**
	 * 列出给定日期之间的热点关注商品排行
	 * @param start - 开始的日期
	 * @param end - 结束日期
	 * @param num - 显示结果的条数
	 * @return - 查询到的集合
	 */
	public List<AbractProduct> listHotAttentionBetween(String start,String end,int num){
		
		StringBuffer hql=new StringBuffer();
		SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
		Date s=null,e=null;
		try {
			s = sdf.parse(start);
			e = sdf.parse(end);
		} catch (ParseException e1) {
			e1.printStackTrace();
		}
		
		//String sql = "select p.id,p.pname,count(pa.id) from product p,productattention pa where p.id=pa.pid and pa.attentiontime between date '2008-10-31' and date '2008-11-07' group by pa.pid order by count(pa.id) desc;";
		//System.out.println(getSession().createSQLQuery(sql).list().size());
		
		hql.append("select p.id,p.pname,p.price,count(pa.id) ");
		hql.append("from Productattention pa,Product p ");
		hql.append("where (pa.attentiontime between ? and ?) ");
		hql.append("and (p.id=pa.product.id) ");
		hql.append("group by pa.product.id ");
		hql.append("order by count(pa.id) desc");
		
		List<Object[]> list = getSession().createQuery(hql.toString())
							.setParameter(0, s)
							.setParameter(1, e)
							.setFirstResult(0)
							.setMaxResults(num)
							.list();
		List<AbractProduct> products = new Vector<AbractProduct>();
		for(Object[] o:list){
			try {
				products.add(new AbractProduct((Integer)o[0],(String)o[1],(Double)o[2],(Integer)o[3]));
			} catch (RuntimeException e1) {
				products.add(new AbractProduct((Integer)o[0],(String)o[1],(Double)o[2],((Long)o[3]).intValue()));
			}
		}
		return products;
	}
	
	
	
	 /**
	 * 列出促销商品
	 * @param num -指定列出促销商品的个数
	 * @return Vector<AbractProduct>
	 * @roseuid 4902A9DE029F
	 */
	public Vector<AbractProduct> ListPromotionProduct(int num) {
		Vector<AbractProduct> products = new Vector<AbractProduct>();
		String hql = "select new tarena.data.AbractProduct(p.id,p.pname,p.price) from Product p where p.promotion=1 order by p.addtime desc";
		Query query = getSession().createQuery(hql);
		if(query!=null){
			query.setFirstResult(0);
			query.setMaxResults(num);
			Iterator iterator = query.iterate();
			while(iterator!=null && iterator.hasNext()){
				AbractProduct product = (AbractProduct)iterator.next();
				product.setImage(GetPhotoAddress.getProductPhotoAddress(product.getId(), 0));
				products.add(product);
			}
		}
		return products;
	}

	/**
	 * 列出店长推荐商品
	 * @param num - 指定列出推荐商品的个数
	 * @return Vector<AbractProduct>
	 * @roseuid 4902B99101D4
	 */
	public Vector<AbractProduct> ListRecommendProduct(int num) {
		Vector<AbractProduct> products = new Vector<AbractProduct>();
		String hql = "select new tarena.data.AbractProduct(p.id,p.pname,p.price) from Product p where p.recommend=1 order by p.addtime desc";
		Query query = getSession().createQuery(hql);
		if(query!=null){
			query.setFirstResult(0);
			query.setMaxResults(num);
			Iterator iterator = query.iterate();
			while(iterator!=null && iterator.hasNext()){
				AbractProduct product = (AbractProduct)iterator.next();
				product.setImage(GetPhotoAddress.getProductPhotoAddress(product.getId(), 0));
				products.add(product);
			}
		}
		return products;
	}

	/**
	 * 列出最新上架的商品
	 * @param number -指定商品的个数
	 * @return Vector<AbractProduct>
	 * @roseuid 4902B9EA029F
	 */
	public Vector<AbractProduct> ListNewProduct(int number) {
		Vector<AbractProduct> products = new Vector<AbractProduct>();
		String hql = "select new tarena.data.AbractProduct(p.id,p.pname,p.price) from Product p order by p.addtime desc";
		Query query = getSession().createQuery(hql);
		if(query!=null){
			query.setFirstResult(0);
			query.setMaxResults(number);
			Iterator iterator = query.iterate();
			while(iterator!=null && iterator.hasNext()){
				AbractProduct product = (AbractProduct)iterator.next();
				product.setImage(GetPhotoAddress.getProductPhotoAddress(product.getId(), 0));
				products.add(product);
			}
		}
		return products;
	}
	
	/**
	 * 列出最近的n条公告
	 * @param number -公告条数
	 * @return Vector<AbractAnnounce>
	 * @roseuid 4902C66A005D
	 */
	public List<AbractAnnounce> listTopAnnounce(int number) {
		Vector<AbractAnnounce> announces = new Vector<AbractAnnounce>();
		String hql = "select new tarena.data.AbractAnnounce(a.id,a.atype,a.title,a.titlecolor) from Announce a order by a.sendtime desc";
		Query query = getSession().createQuery(hql);
		query.setFirstResult(0);
		query.setMaxResults(number);
		if(query!=null){
			Iterator iterator = query.iterate();
			while(iterator!=null && iterator.hasNext()){
				AbractAnnounce announce = (AbractAnnounce)iterator.next();
				announces.add(announce);
			}
		}
		return announces;
	}
}