hql例子

/**
* 根据搜索条件查询商品(带缓存)
*/
public List<ResultInfo> getSearchGoodsList(GoodsTypeCondtionBizBean condition, QueryPage queryPage){
StringBuffer sb = new StringBuffer();
List<Object> paramList = new ArrayList<Object>();
sb.append(" from ResultInfo rt where rt.markDelete='N' and nvl(rt.isClose,'N') <>'Y' and rt.goodsHeadState= 'PUBLISHED' ");
if(condition != null){
if(condition.getGoodsTypeIds() != null && condition.getGoodsTypeIds().size()>0){
String pas = "";
for(int i=0;i<condition.getGoodsTypeIds().size();i++){
if(i == 0){
pas = pas + "?";
}else{
pas = pas + "," + "?";
}
paramList.add(condition.getGoodsTypeIds().get(i));
}
sb.append(" and rt.goodsTypeId in("+pas+") ");
}
if(condition.getBrandId()!=null){
sb.append(" and rt.goodsBrandId="+condition.getBrandId() );
}

if(!StringUtils.isEmpty(condition.getOrderby())){
//按价格排序
if(condition.getOrderby().equals("goodsHeadMalltPriceUp")){
sb.append(" order by goodsHeadMalltPrice desc ");
}
if(condition.getOrderby().equals("goodsHeadMalltPriceDown")){
sb.append(" order by goodsHeadMalltPrice asc ");
}
//新品（上线时间排序）
if(condition.getOrderby().equals("goodsCreateDate")){
sb.append(" order by goodsHeadOnLineDate desc ");
}
//按收藏量排序
if(condition.getOrderby().equals("collectNum")){
sb.append(" order by goodsHeadOnLineDate desc ");
}
//按销量排序
if(condition.getOrderby().equals("goodsHeadSellSum")){
sb.append(" order by goodsHeadSellSum desc ");
}
//按人气排序
if(condition.getOrderby().equals("popularity")){
sb.append(" order by goodsHeadOnLineDate desc ");
}
}
}
getHibernateTemplate().setCacheQueries(true);
if (queryPage != null) {
return super.findByHQL(queryPage, sb.toString(), paramList.toArray());
} else {
return super.findByHQL(sb.toString(), paramList.toArray());
}
}


/**
* 查询当前搜索的商品有哪些品牌
*/
public List<GoodsBrandBizBean> findGoodsBrands(GoodsTypeCondtionBizBean condition){

StringBuffer sb = new StringBuffer();
List<Object> paramList = new ArrayList<Object>();
if(condition.getParam()==null){
throw new ApplicationException("请传入参数");
}
sb.append("select brand.* from t_goods_brand brand join (select resultinfo0_.goods_brand_id ");
sb.append(" from T_GOODS_HEAD resultinfo0_ where resultinfo0_.MARK_DELETE = 'N' and nvl(resultinfo0_.IS_CLOSE, 'N') <> 'Y' ");
sb.append(" and resultinfo0_.GOODS_HEAD_STATE ='PUBLISHED' ");
if(condition.getGoodsTypeIds() != null && condition.getGoodsTypeIds().size()>0){
String pas = "";
for(int i=0;i<condition.getGoodsTypeIds().size();i++){
if(i == 0){
pas = pas + "?";
}else{
pas = pas + "," + "?";
}
paramList.add(condition.getGoodsTypeIds().get(i));
}
sb.append(" and resultinfo0_.GOODS_TYPE_ID in("+pas+") ");
}

sb.append(" group by resultinfo0_.goods_brand_id) goods on brand.goods_brand_id=goods.goods_brand_id ");
List<Object[]> list = new ArrayList<Object[]>();
list = super.findBySQL(sb.toString(), paramList.toArray());
List<GoodsBrandBizBean> rs=new ArrayList();
GoodsBrandBizBean resultBean = null;
for(Object[] object:list){
resultBean = new GoodsBrandBizBean();
if(object[0]!=null)
resultBean.setGoodsBrandId(Long.parseLong(object[0].toString()));
if(object[1]!=null)
resultBean.setGoodsBrandName(object[1].toString());

rs.add(resultBean);
}
return rs;
}
/**
* 查询当前搜索的商品有哪些 类别
*/
public List<GoodsTypeInfo> findGoodsSort(GoodsTypeCondtionBizBean condition){

StringBuffer sb = new StringBuffer();
List<Object> paramList = new ArrayList<Object>();
if(condition.getParam()==null){
throw new ApplicationException("请传入参数");
}
sb.append("from "+condition.getParam());

sb.append(" t where t.goodsTypeId ");
sb.append(" in (select rt.goodsTypeId");

sb.append(" from GoodsHeadInfo rt where rt.markDelete='N' and nvl(rt.isClose,'N') <>'Y' ");
sb.append(" and rt.goodsHeadState = 'PUBLISHED' ");
if(condition.getGoodsTypeIds() != null && condition.getGoodsTypeIds().size()>0){
String pas = "";
for(int i=0;i<condition.getGoodsTypeIds().size();i++){
if(i == 0){
pas = pas + "?";
}else{
pas = pas + "," + "?";
}
paramList.add(condition.getGoodsTypeIds().get(i));
}
sb.append(" and rt.goodsTypeId in("+pas+") ");
}

sb.append(" group by rt.goodsTypeId )");

getHibernateTemplate().setCacheQueries(true);
return super.findByHQL(sb.toString(), paramList.toArray());

}

/**
* 根据搜索条件查询商品(不带缓存)(类型ID)
*/
public List<ResultBean> getSearchTypeGoodsList(GoodsTypeCondtionBizBean condition, QueryPage queryPage){
StringBuffer sb = new StringBuffer();
List<Object> paramList = new ArrayList<Object>();
sb.append(" select * from ( ");
sb.append(" select distinct t1.GOODS_HEAD_ID, t1.GOODS_HEAD_MALLPRICE, ");
sb.append(" t1.GOODS_HEAD_TITLE,t1.GOODS_HEAD_LABEL ,t1.GOODS_HEAD_PRICETYPE,t1.GOODS_HEAD_MARKETPRICE,t1.GOODS_HEAD_SHORTNAME,t1.GOODS_HEAD_POINT,t1.GOODS_HEAD_POINTMALL ");
sb.append(" ,t1.GOODS_BRAND_NAME,t1.GOODS_CATEGORY_NAME,t1.GOODS_TYPE_NAME,t1.GOODS_TYPE_ID,t1.GOODS_HEAD_TYPE_DISPLAY,t1.GODDS_HEAD_KEYS2 ");
sb.append(" ,t1.GOODS_HEAD_NAME,t1.GOODS_HEAD_SELLSUM,t1.GOODS_COLLECT_NUM,t1.GOODS_POPULARITY,t1.GOODS_HEAD_ONLINEDATE,t1.supplier_head_id ");
sb.append(" from T_GOODS_HEAD t1 ");
// sb.append(" inner join T_GOODS_PICTURE t2 on t1.GOODS_HEAD_ID = t2.GOODS_HEAD_ID and t2.goods_picture_type = 'MAIN' ");
// sb.append(" inner join T_GOODS_PICRELATED t3 on t2.goods_picrelated_id = t3.goods_picrelated_id ");
if(condition != null){
//根据属性查找
if(condition.getMap() != null && condition.getMap().size()>0){
int i = 2;
for(Map.Entry<String, Object> entry : condition.getMap().entrySet()){
sb.append(" inner join t_goods_expandvalue t"+i+" on t"+i+".goods_head_id = t1.goods_head_id ");
sb.append(" and t"+i+".goods_expand_name = ? ");
paramList.add(entry.getKey());
sb.append(" and t"+i+".goods_expandvalue_value = ? ");
paramList.add(entry.getValue());
i++;
}
}
if(!StringUtils.isEmpty(condition.getShopSpecialType())){
//是否有满送优惠
if(condition.getShopSpecialType().equals("SHOP_FULL_SEND")){
sb.append(" join t_red_envelope_head reh on t1.supplier_head_id = reh.supplier_head_id ");
sb.append(" and reh.mark_delete = 'N' and reh.grant_type = 'RED_ENVELOPE_MERCHANT' and reh.state = 'RED_ENVELOPE_USING' and reh.start_use_date <= sysdate and reh.end_use_date >= sysdate ");
}
//是否优惠券优惠
else if(condition.getShopSpecialType().equals("SHOP_SEND_COUPON")){
sb.append(" join t_coupon_head tch on t1.supplier_head_id = tch.supplier_head_id ");
sb.append(" and tch.mark_delete = 'N' and tch.grant_type = 'COUPON_MERCHANT' and tch.state = 'COUPON_NOT_USING' and tch.start_use_date <= sysdate and tch.end_use_date >= sysdate ");
}
//是否红包优惠
else if(condition.getShopSpecialType().equals("SHOP_SEND_RED_ENVELOPE")){
sb.append(" join t_full_send_head fsh on t1.supplier_head_id = fsh.supplier_head_id ");
sb.append(" and fsh.mark_delete = 'N' and fsh.state = 'FULL_SEND_STATE_USING' and fsh.start_use_date <= sysdate and fsh.end_use_date >= sysdate ");
}
//是否限时特价优惠
else if(condition.getShopSpecialType().equals("SHOP_LIMIT_PRICE")){
sb.append(" join t_special_limited_head slh on t1.supplier_head_id = slh.supplier_head_id ");
sb.append(" and slh.mark_delete = 'N' ");
}
}
sb.append(" where 1=1 ");
if(condition.getGoodsTypeIds() != null && condition.getGoodsTypeIds().size()>0){
String pas = "";
for(int i=0;i<condition.getGoodsTypeIds().size();i++){
if(i == 0){
pas = pas + "?";
}else{
pas = pas + "," + "?";
}
paramList.add(condition.getGoodsTypeIds().get(i));
}
sb.append(" and t1.GOODS_TYPE_ID in("+pas+") ");
}
}
sb.append(" and t1.mark_delete = 'N' and t1.GOODS_HEAD_STATE ='PUBLISHED' and t1.GOODS_HEAD_FLAG ='Y' ");
if(condition != null){
if(!StringUtils.isEmpty(condition.getOrderby())){
//按价格排序
if(condition.getOrderby().equals("goodsHeadMalltPrice")){
sb.append(" order by t1.GOODS_HEAD_MALLPRICE desc ");
}
//新品（上线时间排序）
else if(condition.getOrderby().equals("goodsCreateDate")){
sb.append(" order by t1.GOODS_HEAD_ONLINEDATE desc ");
}
//按收藏量排序
else if(condition.getOrderby().equals("collectNum")){
sb.append(" order by t1.GOODS_COLLECT_NUM desc ");
}
//按销量排序
else if(condition.getOrderby().equals("goodsHeadSellSum")){
sb.append(" order by t1.GOODS_HEAD_SELLSUM desc ");
}
//按人气排序
else if(condition.getOrderby().equals("popularity")){
sb.append(" order by t1.GOODS_POPULARITY desc ");
}
else{
sb.append(" order by t1.goods_head_id desc ");
}
}
}
sb.append(" ) ");
List<Object[]> list = new ArrayList<Object[]>();
if (queryPage != null) {
list = super.findBySQLNotRemoveOrderBy(queryPage, sb.toString(), paramList.toArray());
} else {
list = super.findBySQL(sb.toString(), paramList.toArray());
}
List<ResultBean> goodsList = new ArrayList<ResultBean>();
ResultBean resultBean = null;
for(Object[] object:list){
resultBean = new ResultBean();
if(object[0]!=null)
resultBean.setGoodsHeadID(Long.parseLong(object[0].toString()));
if(object[1]!=null)
resultBean.setGoodsHeadMalltPrice(object[1].toString());
if(object[2]!=null)
resultBean.setGoodsHeadTitle(object[2].toString());
if(object[3]!=null)
resultBean.setGoodsHeadLabel(object[3].toString());
if(object[4]!=null)
resultBean.setGoodsHeadPriceType(object[4].toString());
if(object[5]!=null)
resultBean.setGoodsHeadMarketPrice(object[5].toString());
if(object[6]!=null)
resultBean.setGoodsHeadShortName(object[6].toString());
if(object[7]!=null)
resultBean.setGoodsHeadPoint(object[7].toString());
if(object[8]!=null)
resultBean.setGoodsHeadPointMall(object[8].toString());
if(object[9]!=null)
resultBean.setBrandName(object[9].toString());
if(object[10]!=null)
resultBean.setGoodsCategoryName(object[10].toString());
if(object[11]!=null)
resultBean.setGoodsTypeName(object[11].toString());
if(object[12]!=null)
resultBean.setGoodsTypeId(Long.parseLong(object[12].toString()));
if(object[13]!=null)
resultBean.setGoodsHeadTypeDisplay(object[13].toString());
if(object[14]!=null)
resultBean.setGoodsHeadKeys(object[14].toString());
if(object[15]!=null)
resultBean.setGoodsHeadName(object[15].toString());
if(object[16]!=null)
resultBean.setGoodsHeadSellSum(Long.valueOf(object[16].toString()));
if(object[17]!=null)
resultBean.setCollectNum(Long.valueOf(object[17].toString()));
if(object[18]!=null)
resultBean.setPopularity(Long.valueOf(object[18].toString()));
if(object[19]!=null)
resultBean.setGoodsCreateDate(object[19].toString());
if(object[20]!=null)
resultBean.setSupplierId(Long.valueOf(object[20].toString()));
goodsList.add(resultBean);
}
return goodsList;
}