关于hibernate用DetachedCriteria实现查询的一点解决方案

        当我们用DetachedCriteria实现动态查询的时候，发现在使用它的时候有一点小小的缺陷，经过认真分析找出一点解决方法希望能帮助正在思考中的人。
       因为在分页查询的时候我们一般要做两步操作，一是返回满足条件的记录数，二是返回满足条件的记录。
返回满足条件的记录数很简单，一条语句即可搞定
count=(Integer)criteria.setProjection(Projections.rowCount()).uniqueResult();
但该条语句执行后，我发现DetachedCriteria对象的Projection就被改变从而在查询记录的时候得到的结果集已被改变。网上有也说将criteria.setProjection(null);设置后即可，但对于多表关联查询却不管用，查询一两次后又出现了上面所说的问题。
下面，我列出我的解决方法以供参考

得到查询条件的DetachedCriteria对象：
private DetachedCriteria getDetachedCriteria(HttpServletRequest request){
   String discountTypeKey = Util.isNull(request.getParameter("discountTypeKey"));
   String configLibKey = Util.isNull(request.getParameter("configLibKey"));
   String baseUnit = Util.isNull(request.getParameter("baseUnit"));
   String beginDate = Util.isNull(request.getParameter("beginDate"));
   String endDate = Util.isNull(request.getParameter("endDate"));
   String options = Util.isNull(request.getParameter("options"));
   String temp = Util.isNull(request.getParameter("money"));
   temp=temp.equals("")?"0":temp;
   double money =  Double.parseDouble(temp);

   final DetachedCriteria detachedCriteria = DetachedCriteria.forClass(ChargeProcess.class);
   detachedCriteria.createAlias("discountInfoKey", "di");

   if(!discountTypeKey.equals("0")){
    detachedCriteria.add(Restrictions.eq("di.discountTypeKey.discountTypeID",discountTypeKey));
   }
   if(!configLibKey.equals("0")){
    detachedCriteria.add(Restrictions.eq("di.configLibKey.configLibID",configLibKey));
   }
   if(!baseUnit.equals("0")){
    detachedCriteria.add(Restrictions.eq("di.baseUnit",baseUnit));
   }
   if(!beginDate.equals("")){
    detachedCriteria.add(Restrictions.ge("operDate",Util.toDate(beginDate)));
   }
   if(!endDate.equals("")){
    detachedCriteria.add(Restrictions.lt("operDate",Util.toDate(endDate)));
   }
   if(!options.equals("0") && money>0){
    if(options.equals("1")){//=
     detachedCriteria.add(Restrictions.eq("money",new Double(money)));
    }
    if(options.equals("2")){//>
     detachedCriteria.add(Restrictions.gt("money",new Double(money)));
    }
    if(options.equals("3")){//>=
     detachedCriteria.add(Restrictions.ge("money",new Double(money)));
    }
    if(options.equals("4")){//<
     detachedCriteria.add(Restrictions.lt("money",new Double(money)));
    }
    if(options.equals("5")){//<=
     detachedCriteria.add(Restrictions.le("money",new Double(money)));
    }
   }
   detachedCriteria.add(Restrictions.eq("di.discountUserKey.discountUserID",discountUserKey));
   detachedCriteria.add(Restrictions.eq("this.status","0"));
  }
  return dcondition;
 }

 根据detachedCriteria对象返回该条件的记录数:
 public int getChargeProcessCount(final DetachedCriteria detachedCriteria) throws Exception{
  Integer count =new Integer(0);
  if(detachedCriteria!=null){
    count=(Integer) getHibernateTemplate().execute(new HibernateCallback() {
          public Object doInHibernate(Session session) throws HibernateException {
              Criteria criteria = detachedCriteria.getExecutableCriteria(session);
              CriteriaImpl impl = (CriteriaImpl) criteria;
              Projection projection = impl.getProjection();//取出projection
              List orderEntries = new ArrayList();//用来存放排序字段
              Field field = null;
              Integer count=new Integer(0);
              try {
                 field = CriteriaImpl.class.getDeclaredField("orderEntries");
                     field.setAccessible(true);
                     orderEntries = (List) field.get(impl);//将对象中的排序字段存入数组中
                     field.set(criteria,new ArrayList());//将排序字段设置为空

                     count=(Integer)criteria.setProjection(Projections.rowCount()).uniqueResult();

                     criteria.setProjection(projection);//重新设置回projection
                 if (projection == null) criteria.setResultTransformer(CriteriaSpecification.ROOT_ENTITY);
                 field.set(criteria,orderEntries);//重新设置回排序字段
              }catch (Exception e) {}

              return count;
          }
   },true);
  }
  return count.intValue();
 }

根据detachedCriteria对象返回结果集:
经过上面的查询后，我们的projection与排序字段都没有改变，接下来我们查询结果集时就没阻碍了
public List getChargeProcessReport(int startNumber,int maxNumber,final DetachedCriteria detachedCriteria,String field,String order){
   final int beginNumber=startNumber,endNumber=maxNumber;
   final String fd=Util.isNull(field),or=Util.isNull(order);
   return (List) getHibernateTemplate().execute(new HibernateCallback() {
         public Object doInHibernate(Session session) throws HibernateException {
             Criteria criteria = detachedCriteria.getExecutableCriteria(session);

             if(!fd.equals("") && !or.equals("")){
               try{
                     Field field = CriteriaImpl.class.getDeclaredField("orderEntries");
                     field.setAccessible(true); 

                    field.set(criteria,new ArrayList());

                 }catch(Exception ex){
                  System.out.println(ex.getMessage());
                 }
                 if(or.equals("desc")) criteria.addOrder(Order.desc(fd));//重新设置排序条件
                 if(or.equals("asc")) criteria.addOrder(Order.asc(fd));
                }

              List list =null;
              if(beginNumber==0 && endNumber==0){
               list = criteria.list();//不分页返回结果集
              }else{
               list = criteria.setFirstResult(beginNumber).setMaxResults(endNumber).list();
              }
              criteria.setFirstResult(0).setMaxResults(1000);//这里必须重新设置一次，否下次调用时会出错，至于什么原因我也还没有弄清楚，希望搞定的朋友能回复一下
             return list;
         }
     }, true);
 }
 到此已全部完成，希望对朋友们有所帮助，呵呵！

评论

3 楼 xb12369 2013-03-05  

有没有办法结果集合 和 数量一次查询就返回呢 ..?

2 楼 csjxing 2008-01-29  

有没有办法结果集合 和 数量一次查询就返回呢 ..?

1 楼 csjxing 2008-01-29  

有没有办法结果集合 和 数量一次查询就返回呢 ..?