hibernate分页查询(pageSupport)注意问题

今天把myblog用sql server部署了一下，可是发现分页查询的时候出现错误，看控制台报错说语句有错,由来发现分页的时候先查询总记录数目的那条语句出错了

select   count ( * )  as  y0_  from  myblog_Blog this_  inner   join  myblog_Blog_Category categories3_  on  this_.id = categories3_.blogId  inner   join  myblog_Category category1_  on  categories3_.categoryId = category1_.id  where  category1_.id = ?  order   by  this_.postTime  desc

      原来开发的时候我是用的mysql，没有任何问题。原因就在最后面的order by 语句，sql server 在select count(*)里面不能用 order by。然后跟踪代码发现:

     public  PaginationSupport getBlogsByCategoryByPage( final  String categoryId,  final   int  startIndex,  final   int  pageSize) {
         return  (PaginationSupport) getHibernateTemplate().execute( new  HibernateCallback() {
             public  Object doInHibernate(Session session)  throws  HibernateException {
                Criteria criteria  =  session.createCriteria(Blog. class );
                Criteria cateCriteria  =  criteria.createCriteria( " categories " );
                cateCriteria.add(Expression.eq( " id " ,categoryId));
                criteria.addOrder(Order.desc( " postTime " ));
                 int  totalCount  =  ((Integer) criteria.setProjection(Projections.rowCount())
                        .uniqueResult()).intValue();
                criteria.setProjection( null );
                
                List items  =  criteria.setFirstResult(startIndex).setMaxResults(pageSize).list();
                List blogs  =   new  ArrayList();
                 for (Iterator ite  =  items.iterator(); ite.hasNext();) {
                    Object[] objs  =  (Object[])ite.next();
                    blogs.add(objs[ 1 ]);
                }
                PaginationSupport ps  =   new  PaginationSupport(blogs, totalCount, pageSize, startIndex);
                 return  ps;
            }
        },  true );
    }

原 来问题就在Criteria.addOrder(Order.desc("postTime"));这句话的位置上面，int totalCount = ((Integer) criteria.setProjection(Projections.rowCount())
      .uniqueResult()).intValue();
这句话的时候就会生成上面那句话，如果在这之前addOrder就会出现问题，如果你用mysql不会出现问题，如果你用sql server就会报错。解决方法就是把addOrder语句放到totalCount下面就可以了。

     public  PaginationSupport getBlogsByCategoryByPage( final  String categoryId,  final   int  startIndex,  final   int  pageSize) {
         return  (PaginationSupport) getHibernateTemplate().execute( new  HibernateCallback() {
             public  Object doInHibernate(Session session)  throws  HibernateException {
                Criteria criteria  =  session.createCriteria(Blog. class );
                Criteria cateCriteria  =  criteria.createCriteria( " categories " );
                cateCriteria.add(Expression.eq( " id " ,categoryId));
                 int  totalCount  =  ((Integer) criteria.setProjection(Projections.rowCount())
                        .uniqueResult()).intValue();
                criteria.setProjection( null );
                
                 /*
                 * Fix a bug ,Order must add after get the totalCount,
                 * beacuse SqlServer not support order by in the select count(*).
                  */
                criteria.addOrder(Order.desc( " postTime " ));
                List items  =  criteria.setFirstResult(startIndex).setMaxResults(pageSize).list();
                List blogs  =   new  ArrayList();
                 for (Iterator ite  =  items.iterator(); ite.hasNext();) {
                    Object[] objs  =  (Object[])ite.next();
                    blogs.add(objs[ 1 ]);
                }
                PaginationSupport ps  =   new  PaginationSupport(blogs, totalCount, pageSize, startIndex);
                 return  ps;
            }
        },  true );
    }

这样生成的sql语句就是这样的。

select   count ( * )  as  y0_  from  myblog_Blog this_  inner   join  myblog_Blog_Category categories3_  on  this_.id = categories3_.blogId  inner   join  myblog_Category category1_  on  categories3_.categoryId = category1_.id  where  category1_.id = ?

以后大家也要注意了。呵呵。