hibernate 对 sql server 2005 分页改进(二)
StringBuffer sql = new StringBuffer(querySelect.length()+100);
sql.append("select * from (")
.append(selectFld)
.append(",ROW_NUMBER() OVER(").append(orderby).append(") as _page_row_num_hb ")
.append(selectFromTableAndWhere).append(" ) temp ")
.append(" where _page_row_num_hb BETWEEN ")
.append(offset+1).append(" and ").append(limit);
return sql.toString();
}
}
//使offset 参数生效
public boolean supportsLimitOffset(){
}
最后再 hibernate.cfg.xml 配置 dialect
org.jac.common.JacSQLServerDialect
测试结果如下：
Query q = session.createQuery("from Cat as c order by c.id asc"); //注意要加 order by 才能用到 ROW_NUMBER分页
q.setFirstResult(10000);
q.setMaxResults(20);
List l = q.list();
生成的sql 语句为
select * from (select ....,ROW_NUMBER() OVER(order by cat0_.id asc) as _page_row_num_hb from
cat as cat0_) temp where _page_row_num_hb BETWEEN 10001 and 10020