Hibernate paging technology

Paging is a headache in any system. Some databases support paging in syntax, while some databases need to use a scroll cursor, in addition, a system that does not support scroll cursors can only use one-way cursors to gradually approach the data to be obtained.
Hibernate provides a paging mechanism that supports cross-system operations, so that no matter what type of database is at the underlying layer, you can use a unified interface for paging operations. For example, the following code extracts 500th records from 100 records:
Query q = session. createquery ("from foobar as F ");
Q. setfirstresult (500 );
Q. setmaxresults (100 );
List L = Q. List ();
So how does hibernate implement paging at the underlying layer? Hibernate assembles SQL statements based on query in the preparequerystatement method of the org. hibernate. loader. loader class. The code supported by paging can be found in this section:
If (uselimit)
{
SQL = dialect. getlimitstring (
SQL. Trim (), // use of trim () Here is uugly?
Useoffset? Getfirstrow (selection): 0,
Getmaxorlimit (selection, dialect)
);
}
Call the getlimitstring method of dialect to obtain the paging statements of different platforms.
In mysqldialect, The getlimitstring method is implemented as follows:
Public String getlimitstring (string SQL, Boolean hasoffset)
{
Return new stringbuffer (SQL. Length () + 20)
. Append (SQL)
. Append (hasoffset? "Limit ?, ? ":" Limit? ")
. Tostring ();
}
This is a dedicated paging Statement of MySQL. Let's look at oracle9dialect:
Public String getlimitstring (string SQL, Boolean hasoffset ){

SQL = SQL. Trim ();
Boolean isforupdate = false;
If (SQL. tolowercase (). endswith ("For Update ")){
SQL = SQL. substring (0, SQL. Length ()-11 );
Isforupdate = true;
}

Stringbuffer pagingselect = new stringbuffer (SQL. Length () + 100 );
If (hasoffset ){
Pagingselect. append ("select * from (select row _. *, rownum _ from (");
}
Else {
Pagingselect. append ("select * from (");
}
Pagingselect. append (SQL );
If (hasoffset ){
Pagingselect. append (") Row _ Where rownum <= ?) Where rownum _>? ");
}
Else {
Pagingselect. append (") Where rownum <=? ");
}

If (isforupdate ){
Pagingselect. append ("For Update ");
}

Return pagingselect. tostring ();
}
Oracle uses nested 3-layer query statements combined with rownum to implement paging, which is the best way in Oracle, because order by is not supported if it is only one or two-layer query statement rownum.
In addition, Interbase, PostgreSQL, and hsql also support pagination at the syntax level. You can view the dialect implementation for specific implementations. If the database does not support paging SQL statements, if the database supports scroll cursors, Hibernate will use the absolute method of resultset to directly move to the query start point; otherwise, the circular statement is used through RS. next, move to the data to be queried step by step:
Final int firstrow = getfirstrow (selection );
If (firstrow! = 0)
{
If (getfactory (). getsettings (). isscrollableresultsetsenabled ())
{
// 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 ();
}
}

It can be seen that hibernate is very flexible in querying pages. hibernate will first try the paging SQL of a specific database. If it is useless, try scrollable again, if scrollable is not supported, use rset. next. This not only takes into account the query paging performance, but also ensures the portability of code between different databases.