Using hibernate to realize paged query

For example:
Remove 100 records starting from No. 20000
　　
Code:
Query q = Session.createquery ("From Cat as C");
Q.setfirstresult (20000);
Q.setmaxresults (100);
List L = q.list ();
　　
So how does hibernate bottom-up make paging? In fact, Hibernate's query is defined in the Net.sf.hibernate.loader.Loader class, and by reading the code carefully, you can fully understand the problem.
　　
Hibernate2.0.3 the loader source code line NO. 480 below:
　　
Code:
if (uselimit) sql = dialect.getlimitstring (SQL);
PreparedStatement st = Session.getbatcher (). preparequerystatement (SQL,
scrollable);
　　
　　
If the corresponding database defines the SQL statement that qualifies the query record, the SQL statement for the specific database is used directly.
　　
Then see 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 ();
}
　　
This is the dedicated paging statement for MySQL, and then look at Net.sf.hibernate.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 nested 3-tier query statements combined with RowNum for paging, which is the quickest way to Oracle, and if only one or two layers of query statements rownum not support order by.
　　
In addition, INTERBASE,POSTGRESQL,HSQL also support paging SQL statements, in the corresponding dialect inside, we refer to their own.
　　
If the database does not support paging of SQL statements, then according to the config file inside #hibernate.jdbc.use_scrollable_resultset true defaults to True if you do not specify false, Then hibernate will use JDBC2.0 's scrollableresult to achieve paging, see Loader line No. 430 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<firstrow; m++) Rs.next ();
}
　　
If scrollable result is supported, use ResultSet's absolute method to move directly to the starting point of the query and, if not supported, use a looping statement to rs.next a little bit of the move over.
　　
Can be seen using Hibernate, in the query paging operation, is very flexible, hibernate will first try to use a specific database paging SQL, if not, then try to scrollable, if not, and finally adopt the Rset.next () mobile approach.
　　
One of the great benefits of using Hibernate in query paging code is that it combines the performance of query paging with the portability of code across different databases.

