今天发现一个hibernate的bug,或者说一个应该注意的地方比较合适

今天把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=?

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