Implementing dynamic paged queries - POJOs in Action: Developing Enterprise Applications with Lightweight Frameworks - page 424

The innermost SELECT statement that uses ROWNUM returns startingIndex+page-
Size+1 rows. The outer statement, which is used only if start is greater than 0,
skips over the first pageSize rows.
As you can see, you only need to write a few lines of Java code when using i BA-
TIS to execute a dynamically constructed query that returns a page of results. Let's
now look at how to do the same thing with JDO and Hibernate.
11.3 Implementing paged queries with JDO and Hibernate
i BATIS certainly simplifies the task of executing SQL statements. But using an ORM
framework such as JDO and Hibernate has many benefits. For example, an ORM
framework significantly reduces the amount of database access code you must
write and increases your application's portability. Consequently, it's extremely
desirable to use one to implement dynamic paged queries. But how well do JDO
and Hibernate handle the issues discussed in section 11.1?
As we have seen, when implementing dynamic paged queries in a JDBC or i BA-
TIS application, you must do three things. First, you must use carefully tuned SQL
SELECT statements that sometimes make use of vendor-specific features such as
optimizer hints in order to achieve good performance. Second, you must effi-
ciently select a page of rows by either navigating through the result set or using
queries that implement a ROWNUM -like feature. Finally, you must dynamically gener-
ate queries in a maintainable way. As you might expect, you have to do similar
things when using Hibernate or JDO , but because the persistence framework exe-
cutes SQL on behalf of the application, implementing dynamic paged queries effi-
ciently with JDO and Hibernate object queries can be tricky.
To see why it can be difficult, let's consider how a repository such as Order-
Repository uses the persistence framework to execute a query. As figure 11.3
shows, OrderRepository uses the search criteria entered by the user to generate
an object query and calls the persistence framework to execute it. The persistence
framework translates the object query into a SELECT statement and calls JDBC to
execute it. The persistence framework then iterates through the JDBC ResultSet ,
creating Java objects
The only control you have over the SQL STATEMENT used by the persistence
framework is to use features such as eager loading. You cannot execute object
queries that use database-specific features such as optimizer hints. If you need to
use these kinds of features to achieve good performance, then you must use SQL
native queries instead of object queries. However, the drawback of using native
SQL queries is that they don't make full use of the capabilities of the persistence