以下分页技术均来自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的游标存储过程分页) 效率最差,但是最为通用。