今天把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=?
以后大家也要注意了。呵呵。
posted on 2006-05-21 22:49
莫多 閱讀(6440)
評論(4) 編輯 收藏 所屬分類:
Spring 、
Hibernate