Hibernate中使用detachedCriteria.addOrder(Order.desc 错误 hibernate排序

文章转载自:http://blog.csdn.net/newfox/article/details/762447

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

public PaginationSupportgetBlogsByCategoryByPage( final StringcategoryId, final int startIndex, final int pageSize){
return (PaginationSupport)getHibernateTemplate().execute( new HibernateCallback(){
public ObjectdoInHibernate(Sessionsession) throws HibernateException{
Criteriacriteria = session.createCriteria(Blog. class );
CriteriacateCriteria = 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 );

Listitems = criteria.setFirstResult(startIndex).setMaxResults(pageSize).list();
Listblogs = new ArrayList();
for (Iteratorite = items.iterator();ite.hasNext();){
Object[]objs = (Object[])ite.next();
blogs.add(objs[ 1 ]);
}
PaginationSupportps = 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 PaginationSupportgetBlogsByCategoryByPage( final StringcategoryId, final int startIndex, final int pageSize){
return (PaginationSupport)getHibernateTemplate().execute( new HibernateCallback(){
public ObjectdoInHibernate(Sessionsession) throws HibernateException{
Criteriacriteria = session.createCriteria(Blog. class );
CriteriacateCriteria = criteria.createCriteria( " categories " );
cateCriteria.add(Expression.eq( " id " ,categoryId));
int totalCount = ((Integer)criteria.setProjection(Projections.rowCount())
.uniqueResult()).intValue();
criteria.setProjection( null );

/*
*Fixabug,OrdermustaddaftergetthetotalCount,
*beacuseSqlServernotsupportorderbyintheselectcount(*).
*/
criteria.addOrder(Order.desc( " postTime " ));
Listitems = criteria.setFirstResult(startIndex).setMaxResults(pageSize).list();
Listblogs = new ArrayList();
for (Iteratorite = items.iterator();ite.hasNext();){
Object[]objs = (Object[])ite.next();
blogs.add(objs[ 1 ]);
}
PaginationSupportps = new PaginationSupport(blogs,totalCount,pageSize,startIndex);
return ps;
}
}, true );
}

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

select count ( * ) as y0_ from myblog_Blogthis_ inner join myblog_Blog_Categorycategories3_ on this_.id = categories3_.blogId inner join myblog_Categorycategory1_ on categories3_.categoryId = category1_.id where category1_.id = ?

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

感谢分享