Pagination

According current dialect，use paginate method can translate a normal SQL to paginated SQL:

   DataSource dataSource= new HikariDataSource();//DataSource
   dataSource.setJdbcUrl("...");// Jdbc settings
   dataSource.setDriverClassName("...");
    ... 
   Dialect d=Dialect.guessDialect(dataSource);  //Guess dialect from DataSource 
   //Dialect d=Dialect.guessDialect(connection);  //From connection，does not close connection
   //Dialect d=Dialect.MySQL5Dialect;     //Or assign a dialect 
   String result=d.paginate(3, 10, "select * from users where age>?");  //Let's rock it！ 

paginate method, first parameter is page number, second parameter is page size.

Output:

     in MySQL5Dialect, result is: "select * from users  where age>? limit 20, 10"
     in Oracle8iDialect, result is: "select * from ( select row_.*, rownum rownum_ from ( select * from users  where  age>? ) row_ ) where rownum_ <= 30 and rownum_ > 20"
     in Oracle12cDialect, result is: "select * from users where  age>? offset 20 rows fetch next 10 rows only"
     in Sybase11Dialect, get a DialectExcepiton with message: "Sybase11Dialect" does not support physical pagination

Some databases do not support pagination, for detial see excel file DatabaseDialects.xlsin root folder.