iteye_2370
2011-05-19 13:55
浏览 222
已采纳

大家帮忙看看我这个查询该怎样优化一下

[code="java"]
public Pager findPage(int pageNo, int pageSize, final Integer pfunctionsId,
final Integer pclazzId, final Integer psubclazzId,
final String pcolor, final Integer pbrandId, final Integer pprice,
final double lowerPrice, final double upperPrice,
final int sortFlag, String pkeyword) {
Productdetail productdetail = new Productdetail();
List criterions = new ArrayList();

    //功能编号
    if (pfunctionsId != null && pfunctionsId.intValue() != 0) {
        Criterion criterion = Restrictions.eq(
                FIELD_PFUNCTIONSID, pfunctionsId);
        criterions.add(criterion);
    }
    //大类编号
    if (pclazzId != null && pclazzId.intValue() != 0) {
        Criterion criterion = Restrictions.eq(
                FIELD_PCLAZZID, pclazzId);
        criterions.add(criterion);
    }//小类编号
    if (psubclazzId != null && psubclazzId.intValue() != 0) {
        Criterion criterion = Restrictions.eq(
                FIELD_PSUBCLASSID, psubclazzId);
        criterions.add(criterion);
    }
    //颜色
    if (pcolor != null && !pcolor.equals("0")) {
        Criterion criterion = Restrictions.like(
                FIELD_PCOLOR, pcolor, MatchMode.ANYWHERE);
        criterions.add(criterion);
    }
    //品牌
    if (pbrandId != null && pbrandId.intValue() != 0) {
        Criterion criterion = Restrictions.eq(
                FIELD_PBRANDID, pbrandId);
        criterions.add(criterion);
    }
    //价格
    if (pprice != null && pprice.intValue() != 0){
        if (1 == pprice.intValue()) {
            Criterion criterion = Restrictions.ge(
                    FIELD_LOWERPRICE, Double.valueOf(0.0D));
            criterions.add(criterion);
            Criterion criterion2 = Restrictions.le(
                    FIELD_UPPERPRICE, Double.valueOf(100D));
            criterions.add(criterion2);
        } else if (2 == pprice.intValue()) {
            Criterion criterion = Restrictions.ge(
                    FIELD_LOWERPRICE, Double.valueOf(100D));
            criterions.add(criterion);
            Criterion criterion2 = Restrictions.le(
                    FIELD_UPPERPRICE, Double.valueOf(299D));
            criterions.add(criterion2);
        } else if (3 == pprice.intValue()) {
            Criterion criterion = Restrictions.ge(
                    FIELD_LOWERPRICE, Double.valueOf(300D));
            criterions.add(criterion);
            Criterion criterion2 = Restrictions.le(
                    FIELD_UPPERPRICE, Double.valueOf(499D));
            criterions.add(criterion2);
        } else if (4 == pprice.intValue()) {
            Criterion criterion = Restrictions.ge(
                    FIELD_LOWERPRICE, Double.valueOf(500D));
            criterions.add(criterion);
            Criterion criterion2 = Restrictions.le(
                    FIELD_UPPERPRICE, Double.valueOf(999D));
            criterions.add(criterion2);
        } else if (5 == pprice.intValue()) {
            Criterion criterion = Restrictions.ge(
                    FIELD_LOWERPRICE, Double.valueOf(1000D));
            criterions.add(criterion);
        } else if (6 == pprice.intValue()) {
            Criterion criterion = Restrictions.ge(
                    FIELD_LOWERPRICE, Double.valueOf(lowerPrice));
            criterions.add(criterion);
            Criterion criterion2 = Restrictions.le(
                    FIELD_UPPERPRICE, Double.valueOf(upperPrice));
            criterions.add(criterion2);
        }
    }

    if (pkeyword != null) {
        if(pkeyword.equals("nokw")){
            pkeyword="";
        }
        String pkeywordtmp = pkeyword.trim();
        String keyWords[] = pkeywordtmp.split(" ");
        if (keyWords.length > 0) {
            for (int i = 0; i < keyWords.length; i++)
                if (!keyWords[i].equals(" ")) {
                    Criterion criterion = Restrictions.or(Restrictions.like(FIELD_PNAME, keyWords[i],MatchMode.ANYWHERE),
                        Restrictions.or(Restrictions.like(FIELD_PMODEL,keyWords[i],MatchMode.ANYWHERE),
                            Restrictions.or(Restrictions.like(FIELD_PDESCRIBE,keyWords[i],MatchMode.ANYWHERE),
                                Restrictions.or(Restrictions.like(FIELD_CNAME,keyWords[i],MatchMode.ANYWHERE),
                                    Restrictions.or(Restrictions.like(FIELD_SNAME,keyWords[i],MatchMode.ANYWHERE),
                                        Restrictions.or(Restrictions.like(FIELD_BNAME,keyWords[i],MatchMode.ANYWHERE),
                                            Restrictions.or(Restrictions.like(FIELD_PCOLOR,keyWords[i],MatchMode.ANYWHERE),
                                                Restrictions.or(Restrictions.like(FIELD_FNAME,keyWords[i],MatchMode.ANYWHERE),
                                                    Restrictions.or(Restrictions.like(FIELD_PTRAIT,keyWords[i],MatchMode.ANYWHERE),
                                                        Restrictions.or(Restrictions.like(FIELD_PSTUFF,keyWords[i],MatchMode.ANYWHERE),
                                                            Restrictions.like(FIELD_PKEYWORD,keyWords[i],MatchMode.ANYWHERE)))))))))));
                    criterions.add(criterion);
                }

        }
    }

    List<Order> orders = new ArrayList<Order>();
    //价格排序
    if(0!=sortFlag){
        if(1==sortFlag){
            orders.add(Order.asc(FIELD_LOWERPRICE));
        }else if(2==sortFlag){
            orders.add(Order.desc(FIELD_LOWERPRICE));
        }
    }

    // 类别排序
    orders.add(Order.desc(FIELD_PCLAZZIDX));
    // 总排序
    orders.add(Order.desc(FIELD_PALLIDX));

    Pager pager = productdetailDAO.selectPageByCriterias(pageNo, pageSize,
            productdetail, criterions, orders);
    return pager;
}

[/code]

数据是从视图里查询出来的

  • 写回答
  • 好问题 提建议
  • 追加酬金
  • 关注问题
  • 收藏
  • 邀请回答

4条回答 默认 最新

  • iteye_8576 2011-05-19 14:36
    已采纳

    发多了,重新发一次 :wink:

    [code="java"]public Pager findPage(int pageNo, int pageSize, final Integer pfunctionsId,
    final Integer pclazzId, final Integer psubclazzId,
    final String pcolor, final Integer pbrandId, final Integer pprice,
    final double lowerPrice, final double upperPrice,
    final int sortFlag, String pkeyword) {
    Productdetail productdetail = new Productdetail();
    List criterions = new ArrayList();

    //功能编号
    setCriterion(pfunctionsId,FIELD_PFUNCTIONSID,criterions);
    //大类编号
    setCriterion(pclazzId,FIELD_PCLAZZID,criterions);
    //小类编号
    setCriterion(psubclazzId,FIELD_PSUBCLASSID,criterions);
    //颜色
    if (!"0".equals(pcolor)) {
        Criterion criterion = Restrictions.like(
                FIELD_PCOLOR, pcolor, MatchMode.ANYWHERE);
        criterions.add(criterion);
    }
    //品牌
    setCriterion(pbrandId,FIELD_PBRANDID,criterions);
    
    //价格
    if (pprice != null) {
        int i=pprice.intValue();
        switch(i) {
        case 1:
            Criterion criterion = Restrictions.ge(
                    FIELD_LOWERPRICE, Double.valueOf(0.0D));
            criterions.add(criterion);
            Criterion criterion2 = Restrictions.le(
                    FIELD_UPPERPRICE, Double.valueOf(100D));
            criterions.add(criterion2);
            break;
        case 2:
            Criterion criterion = Restrictions.ge(
                    FIELD_LOWERPRICE, Double.valueOf(100D));
            criterions.add(criterion);
            Criterion criterion2 = Restrictions.le(
                    FIELD_UPPERPRICE, Double.valueOf(299D));
            criterions.add(criterion2);
            break;
        case 3:
            Criterion criterion = Restrictions.ge(
                    FIELD_LOWERPRICE, Double.valueOf(300D));
            criterions.add(criterion);
            Criterion criterion2 = Restrictions.le(
                    FIELD_UPPERPRICE, Double.valueOf(499D));
            criterions.add(criterion2);
            break;
        case 4:
            Criterion criterion = Restrictions.ge(
                    FIELD_LOWERPRICE, Double.valueOf(500D));
            criterions.add(criterion);
            Criterion criterion2 = Restrictions.le(
                    FIELD_UPPERPRICE, Double.valueOf(999D));
            criterions.add(criterion2);
            break;
        case 5:
            Criterion criterion = Restrictions.ge(
                    FIELD_LOWERPRICE, Double.valueOf(1000D));
            criterions.add(criterion);
            break;
        case 6:
            Criterion criterion = Restrictions.ge(
                    FIELD_LOWERPRICE, Double.valueOf(lowerPrice));
            criterions.add(criterion);
            Criterion criterion2 = Restrictions.le(
                    FIELD_UPPERPRICE, Double.valueOf(upperPrice));
            criterions.add(criterion2);
            break;
        default:
            break;
        }
    }
    
    
    if (pkeyword != null) {
        if("nokw".equals(pkeyword)){
            pkeyword="";
            String pkeywordtmp = pkeyword.trim();
            String keyWords[] = pkeywordtmp.split(" ");
            if (keyWords.length > 0) {
                for (int i = 0; i < keyWords.length; i++)
                    if (!" ".equals(keyWords[i])) {
                        Criterion criterion = Restrictions.or(Restrictions.like(FIELD_PNAME, keyWords[i],MatchMode.ANYWHERE),
                            Restrictions.or(Restrictions.like(FIELD_PMODEL,keyWords[i],MatchMode.ANYWHERE),
                                Restrictions.or(Restrictions.like(FIELD_PDESCRIBE,keyWords[i],MatchMode.ANYWHERE),
                                    Restrictions.or(Restrictions.like(FIELD_CNAME,keyWords[i],MatchMode.ANYWHERE),
                                        Restrictions.or(Restrictions.like(FIELD_SNAME,keyWords[i],MatchMode.ANYWHERE),
                                            Restrictions.or(Restrictions.like(FIELD_BNAME,keyWords[i],MatchMode.ANYWHERE),
                                                Restrictions.or(Restrictions.like(FIELD_PCOLOR,keyWords[i],MatchMode.ANYWHERE),
                                                    Restrictions.or(Restrictions.like(FIELD_FNAME,keyWords[i],MatchMode.ANYWHERE),
                                                        Restrictions.or(Restrictions.like(FIELD_PTRAIT,keyWords[i],MatchMode.ANYWHERE),
                                                            Restrictions.or(Restrictions.like(FIELD_PSTUFF,keyWords[i],MatchMode.ANYWHERE),
                                                                Restrictions.like(FIELD_PKEYWORD,keyWords[i],MatchMode.ANYWHERE)))))))))));
                        criterions.add(criterion);
                    }
            }
        }
    }
    
    List<Order> orders = new ArrayList<Order>();
    //价格排序
    if(1==sortFlag){
        orders.add(Order.asc(FIELD_LOWERPRICE));
    }else if(2==sortFlag){
        orders.add(Order.desc(FIELD_LOWERPRICE));
    }
    
    // 类别排序
    orders.add(Order.desc(FIELD_PCLAZZIDX));
    // 总排序
    orders.add(Order.desc(FIELD_PALLIDX));
    
    Pager pager = productdetailDAO.selectPageByCriterias(pageNo, pageSize,
            productdetail, criterions, orders);
    return pager;
    

    }

    private void setCriterion(Integer o,String fieldType,List criterions) {
    if (o != null && o.intValue() != 0) {
    Criterion criterion = Restrictions.eq(
    fieldType, o);
    criterions.add(criterion);
    }
    }[/code]

    评论
    解决 无用
    打赏 举报
查看更多回答(3条)

相关推荐 更多相似问题