Interpretation of the principle of hibernate paging query
Author: robbin (MSN: robbin_fan AT hotmail DOT com)
Hibernate can implement paging queries, for example:
Remove 100 records from the 20,000th
Query Q = Session.createQuery ("from cat as c");
Q.SetFirstResult (20000);
Q.SetMaxResults (100);
List l = q.list ();
So how do hibernate achieves paging? In fact, Hibernate's query is defined in Net.sf.hibernate.Loader.Loader, read the code carefully, you can completely figure out the problem.
Hibernate2.0.3 Loader Source Code Chapter 480 below:
IF (userimit) SQL = DIALECT.GETLIMITSTRING (SQL);
PreparedStatement St = session.getbatcher (). PrepareQueryStatement (SQL, SCROLLABLE);
If the corresponding database defines the SQL statement that qualifies the query record, then the SQL statement of a specific database is directly used.
Then look at Net.sf.Hibernate.Dialect.MysqldiaLect:
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 my dedicated page statement of MySQL, and then look at Net.sf.Hibernate.Dialect.Orcle9DiaLect:
Public Boolean Supportslimit () {
Return True;
}
Public String getLimitstring (String SQL) {
StringBuffer Pagingselect = New StringBuffer (100);
Pagingselect.Append ("Select Row _. *, Rownum Rownum_ from (");
Pagingselect.Append (SQL);
Pagingselect.Append (") ROW_ where rownum <=?) Where rownum_>?");
Return pagingselect.toString ();
}
Oracle uses a nesting 3-layer query statement to implement paging, which is the fastest way in Oracle, if only a layer or two layers of query statements cannot support Order By.
In addition, Interbase, PostgreSQL, HSQL also supports paging SQL statements, in the corresponding DIALECT, everyone's own reference.
If the database does not support the SQL statement of the paging, then according to the in the configuration file
# hibernate.jdbc.use_scrollable_Resultset True
The default is true. If you do not specify as false, Hibernate uses JDBC2.0 scroll result to implement pagination, see Loader 430 line below: 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 ) rs.next ();
}
If you support scrollable result, use the ResultSet's Absolute method to move directly to the starting point of the query, if you do not support, use the loop statement, RS.Next's point to move.
It can be seen that use hibernate, which is very flexible in the operation of the query paging, Hibernate will first try to use a specific database SQL, if not, try scrollable, if not, finally use Rset.Next () Move Method.
A great advantage that uses Hibernate in the query pagination code is that both the performance of query pagination, and guarantees the portability of the code between different databases.
