productcategorydao.java

工厂版本管理系统,STRUTS2框架,用于管理商品的版本,便于有效的控制版本

package com.bluesky.elecall.dao.hibernate;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.hibernate.Session;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.bluesky.elecall.dao.IProductCategoryDao;
import com.bluesky.elecall.domain.CategoryItem;
import com.bluesky.elecall.domain.Manufactory;
import com.bluesky.elecall.domain.Product;
import com.bluesky.elecall.domain.ProductAttribute;

public class ProductCategoryDao extends HibernateDaoSupport implements
		IProductCategoryDao {

	public CategoryItem getCategoryItem(long id) {
		return (CategoryItem) getHibernateTemplate()
				.get(CategoryItem.class, id);
	}

	public List<CategoryItem> getItemByLevel(int level) {

		return getHibernateTemplate().find("from CategoryItem where level=?",
				level);

	}

	public void save(CategoryItem item) {
		getHibernateTemplate().save(item);
	}

	public void update(CategoryItem item) {
		getHibernateTemplate().update(item);
	}

	public void deleteCategoryItem(long id) {
		// TODO Auto-generated method stub
		CategoryItem item = getCategoryItem(id);
		getHibernateTemplate().delete(item);
	}

	public Product getProduct(String id) {
		//return (Product)getHibernateTemplate().find("from Product where id=?",id).get(0);
		return (Product) getHibernateTemplate().get(Product.class, id);
	}

	public List getAttributeNameList(CategoryItem subsection) {
		List rslt = getHibernateTemplate()
				.find(
						"select pa.name, pa.value, count(pa) from ProductAttribute pa where pa.product.categoryItem =? group by pa.name, pa.value",
						subsection);
		List nameList = new ArrayList();
		for (int i = 0; i < rslt.size(); i++) {
			String name = (String) ((Object[]) rslt.get(i))[0];
			nameList.add(name);
		}
		return nameList;
	}

	public List findProduct(CategoryItem subsection, CategoryItem family,
			Manufactory manufactory, Map productAttributes, String keywords) {
		String selectHql = "select p from Product p ";
		String groupHql = "";

		return search(subsection, family, manufactory, productAttributes,
				selectHql, groupHql, keywords);
	}

	public List getAttributeStatistics(CategoryItem subsection,
			CategoryItem family, Manufactory manufactory, Map productAttributes, String keywords) {
		String selectHql = "select pa.name,pa.value,count(p) from Product p join p.attributes pa ";
		String groupHql = " group by pa.name, pa.value";

		return search(subsection, family, manufactory, productAttributes,
				selectHql, groupHql, keywords);
	}

	public List getFamilyStatistics(CategoryItem subsection,
			CategoryItem family, Manufactory manufactory, Map productAttributes, String keywords) {
		String selectHql = "select f.id, f.name,count(p) from Product p join p.family f ";
		String groupHql = " group by f.id, f.name";

		return search(subsection, family, manufactory, productAttributes,
				selectHql, groupHql, keywords);
	}

	public List getManufactoryStatistics(CategoryItem subsection,
			CategoryItem family, Manufactory manufactory, Map productAttributes, String keywords) {
		String selectHql = "select m.id, m.name, count(p) from Product p join p.manufactory m ";
		String groupHql = " group by m.id, m.name";

		return search(subsection, family, manufactory, productAttributes,
				selectHql, groupHql, keywords);
	}
	
	private List search(CategoryItem subsection, CategoryItem family,
			Manufactory manufactory, Map productAttributes, String selectHql,
			String groupHql, String keywords) {
		/*
		 * 查找产品和按属性统计产品的实现有点类似，所以，做在一起了。
		 */

		List params = new ArrayList();

		String whereHql = "where p.categoryItem=? ";
		params.add(subsection);

		if (family != null) {
			whereHql += " and p.family=? ";
			params.add(family);
		}

		if (manufactory != null) {
			whereHql += " and p.manufactory=? ";
			params.add(manufactory);
		}

		String joinHql = "";
		String wherePa = "";

		if (productAttributes != null && productAttributes.size() > 0) {
			Iterator itAttribute = productAttributes.keySet().iterator();
			for (int i = 0; i < productAttributes.size(); i++) {
				String name = (String) itAttribute.next();
				String value = (String) productAttributes.get(name);

				ProductAttribute pa = (ProductAttribute) productAttributes
						.get(i);
				joinHql += String.format(" join p.attributes pa%d ", i, i);
				wherePa += String.format(" and pa%d.name=? and pa%d.value=? ",
						i, i);
				params.add(name);
				params.add(value);
			}

			whereHql += wherePa;
		}

		if (keywords != null && keywords.trim().length() > 0) {
			// subselect search
			StringBuilder sb = new StringBuilder();
			sb.append("select p.id from Product p");
			sb.append(" join p.manufactory m");
			sb.append(" left join p.family family");
			sb.append(" join p.attributes attribute");
			sb.append(" join p.categoryItem c4");
			sb.append(" join c4.parent c3");
			sb.append(" join c3.parent c2");
			sb.append(" join c2.parent c1");

			sb.append(" where ");
			sb.append(" (c4=?) and ");

			sb.append(" (p.id like ?");
			sb.append(" or p.name like ?");
			sb.append(" or p.detail like ?");
			sb.append(" or m.name like ?");
			sb.append(" or family.name like ?");
			sb.append(" or attribute.name like ?");
			sb.append(" or attribute.value like ?");
			sb.append(" or c4.name like ?");
			sb.append(" or c3.name like ?");
			sb.append(" or c2.name like ?");
			sb.append(" or c1.name like ?)");

			String subSelect = sb.toString();

			whereHql += " and p.id in (" + subSelect + ")";
			params.add(subsection);

			String likeKeyWord = keywords.replace(" ", "%");
			likeKeyWord = "%" + likeKeyWord + "%";

			for (int i = 0; i < 11; i++)
				params.add(likeKeyWord);
		}

		String hql = selectHql + joinHql + whereHql + groupHql;

		List rslt = getHibernateTemplate().find(hql, params.toArray());

		// sample HQL
		// List rslt = getHibernateTemplate().find(
		// "select p from Product p join p.attributes pa join p.attributes pa2 where p.categoryItem=? and pa.name=? and pa.value=? and pa2.name=? and pa2.value=?"
		// ,new Object[]{subsection,"颜色","黑色","size","39cm"});

		/*
		 * DetachedCriteria criteria = DetachedCriteria.forClass(Product.class)
		 * .createAlias("categoryItem", "ci")
		 * .add(Property.forName("id").isNotNull() );
		 * 
		 * List list = getHibernateTemplate().findByCriteria( criteria, start,
		 * numResults);
		 */

		return rslt;
	}

	public List getCategoryStatistics(CategoryItem level2CategoryItem) {
		// for Browse, no keywords
		// return level 3,4 statistics
		String hql = "select c3.id,c3.name,c4.id,c4.name, count(p) from Product p join p.categoryItem c4 join c4.parent c3 join c3.parent c2 where c2=? group by c3.id,c3.name,c4.id,c4.name";
		return getHibernateTemplate().find(hql,
				new Object[] { level2CategoryItem });
	}

	public List getCategoryStatistics(CategoryItem categoryItem, String keyWords) {
		// return 2 levels of its sub-category statistics
		// if categoryItem=null or root, return level 2 and 3
		// if categoryitem!=null and its level=3, return level 4 statistics
		StringBuilder sb = new StringBuilder();

		if(categoryItem != null && categoryItem.getLevel() == 0)//is root
			categoryItem = null;
		
		String fields = "";
		if (categoryItem == null)
			fields = " c2.id, c2.name, c3.id, c3.name ";
		else if (categoryItem.getLevel() == 3)
			fields = " c3.id, c3.name, c4.id, c4.name ";
		else
			throw new RuntimeException(
					"the levelof categoryItem must be 0 or 3");

		sb.append("select " + fields + ", count(p) from Product p");
		sb.append(" join p.manufactory m");
		sb.append(" left join p.family family");
		sb.append(" join p.attributes attribute");
		sb.append(" join p.categoryItem c4");
		sb.append(" join c4.parent c3");
		sb.append(" join c3.parent c2");
		sb.append(" join c2.parent c1");

		sb.append(" where ");
		if (categoryItem != null)
			sb.append(" (c3=?) and ");

		sb.append(" (p.id like ?");
		sb.append(" or p.name like ?");
		sb.append(" or p.detail like ?");
		sb.append(" or m.name like ?");
		sb.append(" or family.name like ?");
		sb.append(" or attribute.name like ?");
		sb.append(" or attribute.value like ?");
		sb.append(" or c4.name like ?");
		sb.append(" or c3.name like ?");
		sb.append(" or c2.name like ?");
		sb.append(" or c1.name like ?)");

		sb.append(" group by " + fields);

		String likeKeyWord = keyWords.replace(" ", "%");
		likeKeyWord = "%" + likeKeyWord + "%";

		List params = new ArrayList();
		if (categoryItem != null)
			params.add(categoryItem);

		for (int i = 0; i < 11; i++)
			params.add(likeKeyWord);

		return getHibernateTemplate().find(sb.toString(), params.toArray());
	}

	
}