A brief analysis of hibernate realization of paging query

This article to introduce hibernate query paging, may be a lot of people do not understand hibernate query paging, there is no relationship, the following has an example, starting from the No. 20000 to take out 100 records after reading this article you must have a lot of harvest, I hope this article can teach you more things.

Java code

Query　q　=　session.createQuery("from　Cat　as　c");;
q.setFirstResult(20000);;
q.setMaxResults(100);;
List　l　=　q.list();;

So how hibernate the bottom of the page to achieve it? In fact, Hibernate's query is defined in the class of Net.sf.hibernate.loader.Loader, read the code carefully, you can thoroughly understand the problem.

Hibernate2.0.3 's loader source code is below line NO. 480:

Java 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, then the SQL statement for the specific database is used directly.

Then look at Net.sf.hibernate.dialect.MySQLDialect:

Java 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, and then look at Net.sf.hibernate.dialect.Oracle9Dialect:

Java 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 to implement paging, which is the quickest way to Oracle, if only a rownum or two-level query statement cannot support ORDER by.

In addition, Interbase,postgresql,hsql also supports pagination of the SQL statement, in the corresponding dialect inside, we reference.

If the database does not support paging SQL statements, then #hibernate in the configuration file. Jdbc.use_scrollable_resultset true

The default is true, if you do not specify false, then hibernate will use JDBC2.0 's scrollable result to implement pagination, see Loader line No. 430 below:

Java 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 you support scrollable result, use the absolute method of resultset to move directly to the beginning of the query, if not, use the loop statement, Rs.next a little bit of the move past.

Visible use of Hibernate, in the query paging operation, is very flexible, hibernate will first attempt to use a specific database paging SQL, if no use, and then try to scrollable, if not, the final use of Rset.next () mobile approach.

One of the great benefits of using Hibernate query paging in query paging code is that it takes into account the performance of paging, while ensuring the portability of code across different databases.