Implement paging query using Hibernate

For example:
　Fetch 100 records starting from the 20,000th one
　
　　Code:
　　Query q = session.createQuery("from Cat as c");
　　q.setFirstResult(20000);
　　q.setMaxResults(100);
　　List l = q.list();
　
　So how does the bottom layer of Hibernate implement paging? In fact, Hibernate's query is defined in the net.sf.hibernate.loader.Loader class. If you read the code of this class carefully, you can completely understand the problem.
　
　 Line 480 of the Loader source code of Hibernate2.0.3 is as follows:
　
　　 Code:
　　if (useLimit) sql = dialect.getLimitString(sql);
　PreparedStatement st = session.getBatcher().prepareQueryStatement(sql,
　scrollable);
　
　
　If the corresponding database defines a sql statement that limits query records, then use the sql statement of the specific database directly.
　
　 Then look at net.sf.hibernate.dialect.MySQLDialect:
　
　　 Code:
　　 public boolean supportsLimit() {
　　 return true;
　　　}
　　 public String getLimitString(String sql) {
　　　StringBuffer pagingSelect = new StringBuffer(100) ;
　　 pagingSelect.append(sql);
　　pagingSelect.append(" limit ?, ?");
　　　 return pagingSelect.toString(); dialect.Oracle9Dialect:
　
　　　Code:
　　　public boolean supportsLimit() {
　　　return true;
　　　}
　　
　　　public String getLimitString(String sql) {
　　　StringBuffer pagingSelect = new StringBuffer (100);
　　pagingSelect.append("select * from ( select row_.*, rownum rownum_
　from ( ");
　　pagingSelect.append(sql);
　　pagingSelect.append(" ) row_ where rownum <= ?) where rownum_ > ?");
　　 return pagingSelect.toString ();
　　　}
　
　Oracle uses three levels of nested query statements combined with rownum to implement paging. This is the fastest way on Oracle. If the rownum of only one or two levels of query statements cannot support order by.
　
In addition, Interbase, PostgreSQL, and HSQL also support paging sql statements. You can refer to them in the corresponding Dialect.
　
　If the database does not support paging SQL statements, then according to #hibernate.jdbc.use_scrollable_resultset true in the configuration file, the default is true. If you do not specify it as false, then Hibernate will use the scrollableresult of JDBC2.0 to implement paging. See Loader Line 430 and below:
　
　　 Code:
　　if ( session.getFactory().useScrollableResultSets() ) {
　　　　 // we can go straight to the first required row
　　　rs.absolute(firstRow);
　　}
　　　　else {
　　　/ / we need to step through the rows one row at a time (slow)
　　for ( int m=0; m　　　}
　
　 If scrollable result is supported, use the absolute method of ResultSet Move directly to the starting point of the query. If this is not supported, use a loop statement and rs.next to move there bit by bit.
　
　 It can be seen that using Hibernate has great flexibility in query paging operations. Hibernate will first try to use the paging sql of a specific database. If it does not work, then try Scrollable. If it does not work, finally use rset.next( ) method of moving.
　
　One of the great benefits of using Hibernate in query paging code is that it not only takes into account the performance of query paging, but also ensures the portability of the code between different databases.