資料庫分頁

為了在程式中加上一個基本的分頁功能,找了些資料後發現許多的實作方式都是將所有付合條件的資料select出來後,再於程式中計算顯示。但這麼一來,資料量一大時,勢必會浪費許多不必要的記憶體空間與網路頻寬,但如果資料庫本身有支援offset的話,其實從資料庫層來實作是比較好的方法。剛好Masu提醒了我,其實Hibernate裡就有實作出分頁的功能,所以我就trace了一下Hibernate的source code,整理出常用資料庫實作分頁的語法。

在org.hibernate.loader.Loader中有這樣一段程式碼(1486行)會處理分頁的動作

 
if ( useLimit ) {
    sql = dialect.getLimitString(
        sql.trim(), //use of trim() here is ugly?
        useOffset ? getFirstRow(selection) : 0,
        getMaxOrLimit(selection, dialect)); 
} 

org.hibernate.dialect.Dialect這個interface提供了各種不同的Database一個相同的介面,各Database則跟據本身功能來實作對應的method,而其中有關分頁功能的method是getLimitString

 
public String getLimitString(String querySelect, boolean hasOffset) {
    throw new UnsupportedOperationException("paged queries not supported" );
}

public String getLimitString(String querySelect, int offset, int limit) {
    return getLimitString( querySelect, offset>0 );
} 

以org.hibernate.dialect.MySQLDialect為例

 
public String getLimitString(String sql, boolean hasOffset) {
    return new StringBuffer( sql.length()+20 )
        .append(sql)
        .append( hasOffset ? " limit ?, ?" : " limit ?").toString();
}

很容易就可以看出MySQL可以用 select * from tbl limit n, m
來做到取出第 n~m 筆資料,也可以用 select * from tbl limit n
來取出前 n 筆(top n)資料

同理也可依此得到其他資料庫的語法

 
/*Oracle*/
select * from (
    select row_.*, rownum rownum_ from (
        {SELECT_Statement} ) row_
    where rownum < = m)
where rownum_ > n

/*Top n*/
select * from (
    {SELECT_Statement} )
where rownum >= n

而有些像DB2 AS400或MS SQL Server等不支援offset的資料庫,就只能做到TOP n的功能

 
/*DB2 AS400*/
{ SELECT_Statement } fetch first n rows only

/*SQL Server*/
select TOP n col1, col2....

在Hibernate中,如果DB直接就可以做分頁,就會直接用其分頁;若不行,則利用scrollable result來達成;如果連scrollable result都不支援,那Hibernate最後就會使用result.next()來實現。透過Hibernate的共同介面,user不用了解database底層的實作方式,這就是使用Hibernate的好處之一,不過這就不是這個的主題了。

作者: yanni

14, Feb 1977

發表迴響

這個網站採用 Akismet 服務減少垃圾留言。進一步瞭解 Akismet 如何處理網站訪客的留言資料