分页在任何系统中都是非常头疼的事情,有的数据库在语法上支持分页,而有的数据库则需要使用可滚动游标来实现,并且在不支持可滚动游标的系统上只能使用单向游标逐步接近要取得的数据。
Hibernate提供了一个支持跨系统的分页机制,这样无论底层是什么样的数据库都能用统一的接口进行分页操作。比如下面的代码就是从第500条开始取出100条记录:
Query q = session.createQuery("from FooBar as f");
q.setFirstResult(500);
q.setMaxResults(100);
List l = q.list();
那么Hibernate底层如何实现分页的呢?Hibernate根据Query拼装SQL语句的地方是在org.hibernate.loader.Loader类的prepareQueryStatement方法中,对分页支持的代码在这一段中可以发现:
if (useLimit)
{
sql = dialect.getLimitString(
sql.trim(), //use of trim() here is ugly?
useOffset ? getFirstRow(selection) : 0,
getMaxOrLimit(selection, dialect)
);
}
此处调用Dialect的getLimitString方法来得到不同平台的分页语句。
在MySQLDialect中是如下实现getLimitString方法的:
public String getLimitString(String sql, boolean hasOffset)
{
return new StringBuffer( sql.length()+20 )
.append(sql)
.append( hasOffset ? " limit ?, ?" : " limit ?")
.toString();
}
这是MySQL的专用分页语句,再来看Oracle9Dialect:
public String getLimitString(String sql, boolean hasOffset) {
sql = sql.trim();
boolean isForUpdate = false;
if ( sql.toLowerCase().endsWith(" for update") ) {
sql = sql.substring( 0, sql.length()-11 );
isForUpdate = true;
}
StringBuffer pagingSelect = new StringBuffer( sql.length()+100 );
if (hasOffset) {
pagingSelect.append("select * from ( select row_.*, rownum rownum_ from ( ");
}
else {
pagingSelect.append("select * from ( ");
}
pagingSelect.append(sql);
if (hasOffset) {
pagingSelect.append(" ) row_ where rownum <= ?) where rownum_ > ?");
}
else {
pagingSelect.append(" ) where rownum <= ?");
}
if ( isForUpdate ) {
pagingSelect.append( " for update" );
}
return pagingSelect.toString();
}
Oracle采用嵌套3层的查询语句结合rownum来实现分页,这在Oracle上是最好的方式,因为如果只是一层或者两层的查询语句的rownum不能支持order by。
此外Interbase,PostgreSQL,HSQL等也在语法级别上支持分页,具体实现可以查看相应的Dialect实现。如果数据库不支持分页的SQL语句,那么如果数据库支持可滚动游标,那么Hibernate就会采使用ResultSet的absolute方法直接移到查询起点;否则使用循环语句,通过rs.next一步步移动到要查询的数据处:
final int firstRow = getFirstRow( selection );
if ( firstRow != 0 )
{
if ( getFactory().getSettings().isScrollableResultSetsEnabled() )
{
// we can go straight to the first required row
rs.absolute( firstRow );
}
else
{
// we need to step through the rows one row at a time (slow)
for ( int m = 0; m < firstRow; m++ ) rs.next();
}
}
可见使用Hibernate,在进行查询分页的操作上,是具有非常大的灵活性,Hibernate会首先尝试用特定数据库的分页sql,如果没用,再尝试Scrollable,如果不支持Scrollable再采用rset.next()移动的办法。这样既兼顾了查询分页的性能,同时又保证了代码在不同的数据库之间的可移植性。
下面两篇是从别处摘来的,非常不错,结合DetachedCriteria
package com.trs.web.page;
import java.util.ArrayList;
import java.util.List;
import com.trs.web.util.AttributesString;
public class PaginationSupport {
/** *//** 默认显示的页面Item数 */
public final static int PAGESIZE = 1;
/** *//** 默认显示在页面上的页数 */
public final static int PAGEDISPLAYNUM = 10;
/** *//** 可设置的页面显示数 */
private int pageSize = PAGESIZE;
/** *//** 查询的当前页面记录 */
private List items;
/** *//** 查询记录总数 */
private int totalCount;
/** *//** 页面的索引 */
private int[] indexes = new int[0];
/** *//** 查询开始索引 */
private int startIndex = 0;
/** *//** 查询的web地址 */
private String actionURL;
/** *//** 分页的附加参数 */
private AttributesString parameter;
public PaginationSupport(List items, int totalCount) {
setPageSize(PAGESIZE);
setTotalCount(totalCount);
setItems(items);
setStartIndex(0);
}
public PaginationSupport(List items, int totalCount, int startIndex) {
setPageSize(PAGESIZE);
setTotalCount(totalCount);
setItems(items);
setStartIndex(startIndex);
}
public PaginationSupport(List items, int totalCount, int pageSize,
int startIndex) {
setPageSize(pageSize);
setTotalCount(totalCount);
setItems(items);
setStartIndex(startIndex);
}
public List getItems() {
return items;
}
public void setItems(List items) {
this.items = items;
}
public int getPageSize() {
return pageSize;
}
public void setPageSize(int pageSize) {
this.pageSize = pageSize;
}
public int getTotalCount() {
return totalCount;
}
public int[] getIndexes() {
return indexes;
}
public void setIndexes(int[] indexes) {
this.indexes = indexes;
}
public String getActionURL() {
return actionURL;
}
public void setActionURL(String actionURL) {
this.actionURL = actionURL;
}
public AttributesString getParameter() {
return parameter;
}
public void setParameter(AttributesString parameter) {
this.parameter = parameter;
}
/** *//**
* 设置查询总数,并设置结果页面索引
*
* @param 结果总数
*/
public void setTotalCount(int totalCount) {
if (totalCount > 0) {
this.totalCount = totalCount;
int count = totalCount / pageSize;
if (totalCount % pageSize > 0)
count++;
indexes = new int[count];
for (int i = 0; i < count; i++) {
indexes[i] = pageSize * i;
}
} else {
this.totalCount = 0;
}
}
public int getStartIndex() {
return startIndex;
}
/** *//**
* 设置查询开始索引值
*
* @param 开始索引值
*/
public void setStartIndex(int startIndex) {
if (totalCount <= 0)
this.startIndex = 0;
else if (startIndex >= totalCount)
this.startIndex = getLastPageIndex();
else if (startIndex < 0)
this.startIndex = 0;
else {
this.startIndex = indexes[startIndex / pageSize];
}
}
/** *//**
* 获得下一页索引
*
* @return 下一页索引
*/
public int getNextIndex() {
int nextIndex = getStartIndex() + pageSize;
if (nextIndex >= totalCount)
return getStartIndex();
else
return nextIndex;
}
/** *//**
* 获得上一页索引
*
* @return 上一页索引
*/
public int getPreviousIndex() {
int previousIndex = getStartIndex() - pageSize;
if (previousIndex < 0)
return 0;
else
return previousIndex;
}
/** *//**
* 获得URL参数的字符串
*
* @return 上一页索引
*/
public String getParamStr(int startIndex){
String sURL=actionURL+"?startIndex="+startIndex;
if(parameter==null)
return sURL;
return sURL+"&"+parameter.toString();
}
public void setParameter(String sKey,String sValue){
if(parameter==null)
parameter=new AttributesString();
parameter.putAttributeValue(sKey, sValue);
}
public String getParamStr(){
return getParamStr(0);
}
/** *//**
* 获得最后一页的开始索引
*
* @return 最后一页索引
*/
public int getLastPageIndex() {
return indexes[indexes.length - 1];
}
/** *//**
* 获取客户端显示的分页格式
*
* @return 客户端显示的分页格式
*/
public String toString() {
if(items==null||items.isEmpty())
return "";
StringBuffer sbHtml = new StringBuffer();
sbHtml
.append("<TABLE cellSpacing='0' cellPadding='0' width='95%' border='0'>\n");
sbHtml.append("<TR >\n");
sbHtml.append("<TD align='right' height='1' width='35%' NOWRAP>\n");
if (getStartIndex() > 0) {
sbHtml
.append("<a href='" + getParamStr()
+ "'>[<< 首页]</a>");
sbHtml.append(" <a href='" +getParamStr(getPreviousIndex())
+ "'> [<< 上一页]</a>");
}
for (int i = 0; i < indexes.length; i++) {
if (getStartIndex() == indexes[i])
sbHtml.append(" " + (i + 1) + " ");
else
sbHtml.append(" <a href='" + getParamStr(indexes[i])
+ "'>" + (i + 1) + "</a>");
}
if (getStartIndex() != getLastPageIndex()) {
sbHtml.append(" <a href='" +getParamStr(getNextIndex())
+ "'>[下一页 >>]</a>");
sbHtml.append(" <a href='" + getParamStr(getLastPageIndex())
+ "'> [末页 >>]</a>");
}
sbHtml.append(" 共" + indexes.length + "页" + totalCount + "条记录");
sbHtml.append("\n</TD>\n</TR>\n</TABLE>");
return sbHtml.toString();
}
public static void main(String args[]) {
PaginationSupport ps = new PaginationSupport(new ArrayList(), 137, 66);
AttributesString aAttributesString = new AttributesString("a=1&b=2&c=3");
ps.setParameter(aAttributesString);
System.out.println(ps);
}
}
AttributesString
package com.trs.web.util;
//====================================================
// AttributesString:用字符串表示的属性及其值序列
//
// 属性的表示:属性名=属性值
// 说明:属性名和属性值均用字符串表示
// 属性序列中属性的分割:使用分割符ATTRIBUTE_DELIM
//
// 设计参考:HTTP_CONTENT
//====================================================
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.StringTokenizer;
public class AttributesString {
/** *//** 属性之间的分割符 */
public final static String ATTRIBUTE_DELIM = "&";
private HashMap m_hAttributesMap = null;
private ArrayList m_arNameList = null;
private String m_sDelim = null;
//======================================================
//构造函数
public AttributesString() {
}
/** *//**
* 直接使用属性字符串构造对象
*
* @param _attributes
* 属性字符串(可忽略)
*/
public AttributesString(String _attributes) {
this.setAttributes(_attributes);
}
//======================================================
//属性存取
//取得属性和属性值序列。
/** *//**
* 获得属性字符串
*
* @return 属性字符串(String对象)
*/
public String toString() {
return this.getAttributes();
}
/** *//**
* 获得属性字符串
*
* @return 属性字符串(String对象)
*/
public String getAttributes() {
int nCount = getNameList().size();
if (nCount == 0)
return null;
String sRet = "";
for (int i = 0; i < nCount; i++) {
if (i > 0) {
sRet += getDelim();
}
sRet += getAttribute((String) getNameList().get(i));
}
return sRet;
}
/** *//**
* 直接用属性字符串设置属性
*
* @param _attributes
* 属性字符串
*/
public void setAttributes(String _attributes) {
if(_attributes == null)
return;
clear();
StringTokenizer stAttributes = null;
stAttributes = new StringTokenizer(_attributes, getDelim());
while (stAttributes.hasMoreElements()) {
String sTemp = stAttributes.nextToken();
int nPose = sTemp.indexOf("=");
if (nPose <= 0)
continue;
String sName = sTemp.substring(0, nPose);
String sValue = sTemp.substring(nPose + 1);
putAttributeValue(sName, sValue, true);
}//endwhile
}
//======================================================
/** *//**
* 取指定名称的属性值 若没有找到该属性,则返回null.
*
* @param _sName
* 属性名称
* @return 属性值
*/
public String getAttributeValue(String _sName) {
_sName = _sName.toUpperCase();
Object oValue = getValue(_sName);
if (oValue == null) {
return null;
}
if (oValue instanceof String) {
return (String) oValue;
}
if (oValue instanceof String[]) {
String[] arTemp = (String[]) oValue;
return arTemp[0];
}
return null;
}
public String[] getAttributeValues(String _sName) {
_sName = _sName.toUpperCase();
Object oValue = getValue(_sName);
if (oValue == null) {
return null;
}
String[] arTemp = null;
if (oValue instanceof String) {
arTemp = new String[1];
arTemp[0] = (String) oValue;
}
if (oValue instanceof String[]) {
arTemp = (String[]) oValue;
}
return arTemp;
}
private String getAttribute(String _sName) {
String sName = _sName.toUpperCase();
Object oValue = getValue(sName);
if (oValue == null) {
return null;
}
if (oValue instanceof String) {
return _sName + "=" + (String) oValue;
}
if (oValue instanceof String[]) {
String[] arTemp = (String[]) oValue;
String sTemp = "";
for (int i = 0; i < arTemp.length; i++) {
if (i > 0) {
sTemp += getDelim();
}
sTemp += _sName + "=" + arTemp[i];
}
return sTemp;
}
return null;
}
/** *//**
* 设置指定名称的属性值
*
* @param _name
* 属性名(不区分大小写);
* @param _value
* 属性值(字符串)
*/
public void putAttributeValue(String _name, String _value) {
putAttributeValue(_name, _value, false);
}
//===========================================================
//test
/** *//**
* @return
*/
protected HashMap getAttributesMap() {
if (m_hAttributesMap == null)
m_hAttributesMap = new HashMap(5);
return m_hAttributesMap;
}
protected void clear() {
if (m_hAttributesMap == null)
return;
m_hAttributesMap.clear();
m_hAttributesMap = null;
}
private Object getValue(String _sName) {
_sName = _sName.toUpperCase();
return getAttributesMap().get(_sName);
}
public void putAttributeValue(String _sName, String _sValue,
boolean _bAllowMultValue) {
String sName = _sName.toUpperCase();
Object oValue = getValue(sName);
if (oValue == null) {
this.getAttributesMap().put(sName, _sValue);
getNameList().add(_sName);
return;
}
if (!_bAllowMultValue) {
this.getAttributesMap().put(sName, _sValue);
return;
}
String[] arValue = null;
if (oValue instanceof String) {
arValue = new String[2];
arValue[0] = (String) oValue;
arValue[1] = _sValue;
}
if (oValue instanceof String[]) {
String[] arTemp = (String[]) oValue;
arValue = new String[arTemp.length + 1];
for (int i = 0; i < arTemp.length; i++)
arValue[i] = arTemp[i];
arValue[arTemp.length] = _sValue;
}
if (arValue != null)
this.getAttributesMap().put(sName, arValue);
}
/** *//**
* @return
*/
public List getNameList() {
if (m_arNameList == null)
m_arNameList = new ArrayList(5);
return m_arNameList;
}
/** *//**
* @return
*/
public String getDelim() {
if (m_sDelim == null)
return ATTRIBUTE_DELIM;
return m_sDelim;
}
/** *//**
* @param delim
*/
public void setDelim(String delim) {
m_sDelim = delim;
}
public int size() {
if (m_arNameList == null)
return 0;
return getNameList().size();
}
public boolean containsName(String _sName){
if(_sName == null)
return false;
return getAttributesMap().containsKey(_sName.toUpperCase());
}
public String getNameAt(int _nIndex) {
return (String) getNameList().get(_nIndex);
}
public static void main(String[] args) {
AttributesString aAttributesString = new AttributesString("a=1&b=2&c=3");
System.out.println("a=" + aAttributesString.getAttributeValue("a"));
System.out.println("b=" + aAttributesString.getAttributeValue("b"));
System.out.println("c=" + aAttributesString.getAttributeValue("c"));
System.out
.println("Attributelist:" + aAttributesString.getAttributes());
System.out.println("Test for pub attribute value:");
aAttributesString.putAttributeValue("b", "22");
aAttributesString.putAttributeValue("d", "4");
System.out
.println("Attributelist:" + aAttributesString.getAttributes());
}
}
底层DaoImpl代码:
public PaginationSupport findPageByCriteria(
final DetachedCriteria detachedCriteria) {
return findPageByCriteria(detachedCriteria, PaginationSupport.PAGESIZE,
0);
}
public PaginationSupport findPageByCriteria(
final DetachedCriteria detachedCriteria, final int startIndex) {
return findPageByCriteria(detachedCriteria, PaginationSupport.PAGESIZE,
startIndex);
}
public PaginationSupport findPageByCriteria(
final DetachedCriteria detachedCriteria, final int pageSize,
final int startIndex) {
return (PaginationSupport) getHibernateTemplate().execute(
new HibernateCallback() {
public Object doInHibernate(Session session)
throws HibernateException {
Criteria criteria = detachedCriteria
.getExecutableCriteria(session);
int totalCount = ((Integer) criteria.setProjection(
Projections.rowCount()).uniqueResult())
.intValue();
criteria.setProjection(null);
List items = criteria.setFirstResult(startIndex)
.setMaxResults(pageSize).list();
PaginationSupport ps = new PaginationSupport(items,
totalCount, pageSize, startIndex);
return ps;
}
}, true);
}
struts层获取数据并设置URL
PaginationSupport ps = logManager.findDefaultPage(form.getStartIndex());
ps.setActionURL(request.getRemoteAddr());
request.setAttribute("ps", ps);
return mapping.findForward("list");
|
|
|
| 日 | 一 | 二 | 三 | 四 | 五 | 六 |
---|
27 | 28 | 29 | 30 | 31 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 21 | 22 | 23 | 24 | 25 | 26 | 27 | 28 | 29 | 30 | 1 | 2 | 3 | 4 | 5 | 6 | 7 |
|
导航
统计
- 随笔: 32
- 文章: 427
- 评论: 144
- 引用: 0
常用链接
留言簿(5)
随笔档案
文章分类
文章档案
java
工具
朋友
搜索
积分与排名
最新评论
阅读排行榜
评论排行榜
|
|