1
2

curpage=Integer.parseInt(request.getParameter("page"));//顯示的頁
ResultSet rs=cn.rsexecuteQuery("select top "+page_record+" * from tablename where id not in (select top "+(curpage*page_record)+" id from tablename order by id desc) order by id desc");

1

SELECT * FROM table WHERE ROWNUM<10; 

1
2
3

SELECT * FROM table WHERE ROWNUM<101; 
minus 
SELECT * FROM table WHERE ROWNUM<91;

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
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57

import java.io.File;
import net.sf.hibernate.dialect.*;
 
public class HibernatePagingQueries {
 
    private static final String _package        = "net.sf.hibernate.dialect.";
    private static final String _querySelect    = "SELECT * FROM STUDENT ORDER BY SCORE";
    private static final int    _limit          = 10;
 
    private HibernatePagingQueries() { }
 
    private void checkLimitSupported(String dbDialect) {
        try {
            Dialect dialect = (Dialect)Class.forName(_package + dbDialect).newInstance();
 
            String myDbName = dbDialect.substring(0, dbDialect.indexOf("Dialect"));
            System.out.println(myDbName);
            if (dialect.supportsLimit()) {
                System.out.println("\t" + dialect.getLimitString(_querySelect, false, _limit));
                System.out.println("\t" + dialect.getLimitString(_querySelect, true,  _limit));
                System.out.println("\t" + dialect.getLimitString(_querySelect, false));
                System.out.println("\t" + dialect.getLimitString(_querySelect, true));
            } else {
                System.out.println("\t" + myDbName + " paged queries not supported");
            }
 
        } catch (ClassNotFoundException e) {
            System.out.println("ClassNotFoundException Occured when fileName = " + dbDialect);
        } catch (InstantiationException e) {
            System.out.println("InstantiationException Occured when fileName = " + dbDialect);
        } catch (IllegalAccessException e) {
            System.out.println("IllegalAccessException Occured when fileName = " + dbDialect);
        } catch (UnsupportedOperationException e) {
            System.out.println("UnsupportedOperationException Occured when fileName = " + dbDialect);
        }
    }
 
    public static void main(String[] args) {
        HibernatePagingQueries pagingqueries = new HibernatePagingQueries();
        String srcDirectory = "C:/hibernate-2.1/src/net/sf/hibernate/dialect";
        String[] files = new File(srcDirectory).list();
 
        for (int i = 0, len = files.length; i < len; i++) {
            if (!files[i].endsWith("Dialect.java")) {
                continue;
            }
            if (files[i].equals("Dialect.java")) { // IllegalAccessException, abstract class
                continue;
            }
 
            String fileName = files[i].substring(0, files[i].lastIndexOf("."));
            pagingqueries.checkLimitSupported(fileName);
 
        }
    }
 
}

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
31
32
33
34

DB2  
    1.1. SELECT * FROM STUDENT ORDER BY SCORE DESC fetch first 10 rows only
    1.2. select * from ( SELECT rownumber() over(ORDER BY SCORE DESC) as row_, * FROM STUDENT ORDER BY SCORE DESC ) as temp_ where row_ <= 10
    2.   select * from ( SELECT rownumber() over(ORDER BY SCORE DESC) as row_, * FROM STUDENT ORDER BY SCORE DESC ) as temp_ where row_ between 11 and 20
　
Firebird
    1. SELECT first 10 * FROM student ORDER BY score DESC
    2. SELECT first 10 SKIP 10 * FROM student ORDER BY score DESC
　
HypersonicSQL(HSQL)
    1. SELECT TOP 10 * FROM student ORDER BY score DESC
    2. SELECT LIMIT 10 10 FROM student ORDER BY score DESC
　
Interbase
    1. SELECT * FROM student ORDER BY score DESC ROWS 10
    2. SELECT * FROM student ORDER BY score DESC ROWS 10 TO 10
　
MySQL  
    1. SELECT * FROM student ORDER BY score DESC LIMIT 10
    2. SELECT * FROM student ORDER BY score DESC LIMIT 10, 10
　
Oracle  
    1. select * from ( SELECT * FROM STUDENT ORDER BY SCORE DESC ) where rownum <= 10
    2. select * from ( select row_.*, rownum rownum_ from ( SELECT * FROM STUDENT ORDER BY SCORE DESC ) row_ where rownum <= 10) where rownum_ > 20
　
PostgreSQL
    1. SELECT * FROM student ORDER BY score DESC limit 10
    2. SELECT * FROM student ORDER BY score DESC limit 10 OFFSET 10
　
SQLServer
    1. SELECT top 10 * FROM STUDENT ORDER BY SCORE DESC
　
paged queries not supported
FrontBase, Informix, Ingres, Mckoi, NoArgSQL, Pointbase, Progress, SAPDB, StandardSQL, Sysbase

1
2
3

Starting with MySQL 4.1, all subquery forms and operations 
that the SQL standard requires are supported, 
as well as a few features that are MySQL-specific.