Explanation of hibernate paging query principles

Hibernate can implement paging query, for example:
Retrieve 2nd records from 100 records

Query q = session. createquery ("from cat as C ");
Q. setfirstresult (20000 );
Q. setmaxresults (100 );
List L = Q. List ();

So how does hibernate implement paging at the underlying layer? In fact, the Hibernate query is defined in the net. SF. hibernate. loader. loader class. Read the code carefully to thoroughly understand the problem.

The loader source code of hibernate2.0.3 is as follows:

If (uselimit) SQL = dialect. getlimitstring (SQL );
Preparedstatement ST = session. getbatcher (). preparequerystatement (SQL, scrollable );

If the corresponding database defines SQL statements that limit query records, the SQL statements of the specific database are used directly.

Next let's take a 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 a dedicated paging statement for MySQL. Let's look at net. SF. hibernate. dialect. oracle9dialect:

Public Boolean supportslimit (){
Return true;
}

Public String getlimitstring (string SQL ){
Stringbuffer pagingselect = new stringbuffer (100 );
Pagingselect. append ("select * from (select row _. *, rownum _ from (");
Pagingselect. append (SQL );
Pagingselect. append (") Row _ Where rownum <= ?) Where rownum _>? ");
Return pagingselect. tostring ();
}

Oracle uses nested 3-layer query statements combined with rownum to implement paging, which is the fastest way in Oracle. If rownum is only one or two-layer query statements, order by is not supported.

In addition, Interbase, PostgreSQL, and hsql also support paging SQL statements. In the corresponding dialect, you can refer to them on your own.

If the database does not support paging SQL statements

# Hibernate. JDBC. use_scrollable_resultset true

The default value is true. If you do not specify false, Hibernate will use scrollable result of jdbc2.0 to implement paging. See the following lines of Loader:

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 to directly move to the query start point. If not, use the loop statement and move Rs. Next a little bit.

It can be seen that hibernate is very flexible in querying pages. hibernate will first try the paging SQL of a specific database. If not, try scrollable again. If not, finally, rset is used. next.

One of the major advantages of using hibernate in querying paging code is that it not only takes into account the query paging performance, but also ensures the portability of code between different databases.