Implement paging query using hibernate

Hibernate supports paging query,

For example:
Retrieve 2nd records from 100 records

Code:
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, Hibernate queries are defined in
Net. SF. hibernate. loader. loader. Read the code carefully to solve the problem thoroughly.
Make it clear.

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 SQL statements that limit query records, you can directly use the SQL statements of a specific database.
Sentence.

Next let's take a 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 ();
}

This is a dedicated paging statement for MySQL. Let's 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 _
From (");
Pagingselect. append (SQL );
Pagingselect. append (") Row _ Where rownum <= ?) Where rownum _>? ");
Return pagingselect. tostring ();
}

Oracle uses nested layer-3 query statements and rownum to implement paging, which is the fastest way in Oracle,
If only one or two layers of query statement rownum cannot support order.

In addition, Interbase, PostgreSQL, and hsql also support paging SQL statements in the corresponding Dialect
For your reference.

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 uses scrollable of jdbc2.0.
Result to implement paging. See the following lines of Loader:

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

It can be seen that hibernate provides great flexibility in querying pages.
First, try to use the paging SQL of the specific database. If it is useless, try scrollable again. If not
Use rset. Next () to move.

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