各种数据库分页Dialect的方法

以下分页技术均来自hibernate-core-3.3.1.GA-sources中的数据库方言总（Dialect）中。
H2:
    
	public String getLimitString(String sql, boolean hasOffset) {
        return new StringBuffer(sql.length() + 20).
            append(sql).
            append(hasOffset ? " limit ? offset ?" : " limit ?").
            toString();
    }
HSQL:
	public String getLimitString(String sql, boolean hasOffset) {
		return new StringBuffer( sql.length() + 10 )
				.append( sql )
				.insert( sql.toLowerCase().indexOf( "select" ) + 6, hasOffset ? " limit ? ?" : " top ?" )
				.toString();
	}
	
	
DB2:
/**
	 * Render the <tt>rownumber() over ( .... ) as rownumber_,</tt> 
	 * bit, that goes in the select list
	 */
	private String getRowNumber(String sql) {
		StringBuffer rownumber = new StringBuffer(50)
			.append("rownumber() over(");

		int orderByIndex = sql.toLowerCase().indexOf("order by");

		if ( orderByIndex>0 && !hasDistinct(sql) ) {
			rownumber.append( sql.substring(orderByIndex) );
		}

		rownumber.append(") as rownumber_,");

		return rownumber.toString();
	}

	public String getLimitString(String sql, boolean hasOffset) {

		int startOfSelect = sql.toLowerCase().indexOf("select");

		StringBuffer pagingSelect = new StringBuffer( sql.length()+100 )
					.append( sql.substring(0, startOfSelect) ) //add the comment
					.append("select * from ( select ") //nest the main query in an outer select
					.append( getRowNumber(sql) ); //add the rownnumber bit into the outer query select list

		if ( hasDistinct(sql) ) {
			pagingSelect.append(" row_.* from ( ") //add another (inner) nested select
				.append( sql.substring(startOfSelect) ) //add the main query
				.append(" ) as row_"); //close off the inner nested select
		}
		else {
			pagingSelect.append( sql.substring( startOfSelect + 6 ) ); //add the main query
		}

		pagingSelect.append(" ) as temp_ where rownumber_ ");

		//add the restriction to the outer select
		if (hasOffset) {
			pagingSelect.append("between ?+1 and ?");
		}
		else {
			pagingSelect.append("<= ?");
		}

		return pagingSelect.toString();
	}
	
	
MySQL:
	public String getLimitString(String sql, boolean hasOffset) {
		return new StringBuffer( sql.length()+20 )
			.append(sql)
			.append( hasOffset ? " limit ?, ?" : " limit ?")
			.toString();
	}
	
Oracle8i:
	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_ <= ? and rownum_ > ?");
		}
		else {
			pagingSelect.append(" ) where rownum <= ?");
		}

		if ( isForUpdate ) {
			pagingSelect.append( " for update" );
		}

		return pagingSelect.toString();
	}
Oracle 9I:
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();
	}
	
	
PostgreSQL:
	public String getLimitString(String sql, boolean hasOffset) {
		return new StringBuffer( sql.length()+20 )
			.append(sql)
			.append(hasOffset ? " limit ? offset ?" : " limit ?")
			.toString();
	}

DB2390/DB2400:

	public String getLimitString(String sql, int offset, int limit) {
		return new StringBuffer(sql.length() + 40)
			.append(sql)
			.append(" fetch first ")
			.append(limit)
			.append(" rows only ")
			.toString();
	}

	
Interbase:
	public String getLimitString(String sql, boolean hasOffset) {
		return new StringBuffer( sql.length()+15 )
			.append(sql)
			.append(hasOffset ? " rows ? to ?" : " rows ?")
			.toString();
	}
针对部分数据库没有伪列(rownum，rowid)之类：
在网上MSSQL分页如下：
SQL常用分页的办法～～ 
表中主键必须为标识列，[ID] int IDENTITY (1,1) 
1.分页方案一：(利用Not In和SELECT TOP分页) 
语句形式： 
SELECT TOP 页记录数量 * 
FROM 表名 
WHERE (ID NOT IN 
(SELECT TOP (每页行数*(页数-1)) ID 
FROM 表名 
ORDER BY ID)) 
ORDER BY ID 
//自己还可以加上一些查询条件 
例: 
select top 2 * 
from Sys_Material_Type 
where (MT_ID not in 
(select top (2*(3-1)) MT_ID from Sys_Material_Type order by MT_ID)) 
order by MT_ID 
2.分页方案二：(利用ID大于多少和SELECT TOP分页） 
语句形式： 
SELECT TOP 每页记录数量 * 
FROM 表名 
WHERE (ID > 
(SELECT MAX(id) 
FROM (SELECT TOP 每页行数*页数 id FROM 表 
ORDER BY id) AS T) 
) 
ORDER BY ID 
例: 
SELECT TOP 2 * 
FROM Sys_Material_Type 
WHERE (MT_ID > 
(SELECT MAX(MT_ID) 
FROM (SELECT TOP (2*(3-1)) MT_ID 
FROM Sys_Material_Type 
ORDER BY MT_ID) AS T)) 
ORDER BY MT_ID 
3.分页方案三：(利用SQL的游标存储过程分页) 
create procedure SqlPager 
@sqlstr nvarchar(4000), --查询字符串 
@currentpage int, --第N页 
@pagesize int --每页行数 
as 
set nocount on 
declare @P1 int, --P1是游标的id 
@rowcount int 
exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1, @rowcount=@rowcount output 
select ceiling(1.0*@rowcount/@pagesize) as 总页数--,@rowcount as 总行数,@currentpage as 当前页 
set @currentpage=(@currentpage-1)*@pagesize+1 
exec sp_cursorfetch @P1,16,@currentpage,@pagesize 
exec sp_cursorclose @P1 
set nocount off 
4.总结: 
其它的方案：如果没有主键，可以用临时表，也可以用方案三做，但是效率会低。 
建议优化的时候，加上主键和索引，查询效率会提高。 
通过SQL 查询分析器，显示比较：我的结论是: 
分页方案二：(利用ID大于多少和SELECT TOP分页）效率最高，需要拼接SQL语句 
分页方案一：(利用Not In和SELECT TOP分页) 效率次之，需要拼接SQL语句 
分页方案三：(利用SQL的游标存储过程分页) 效率最差，但是最为通用。