Oracle and Pagination

Question

In MySql, the concept of pagination can easily be implemented with a single SQL statement using the LIMIT clause something like the following.

SELECT country_id, country_name 
FROM country c 
ORDER BY country_id DESC 
LIMIT 4, 5;

It would retrieve the rows starting from 5 to 10 in the result set which the SQL query retrieves.

---

In Oracle, the same thing can be achieved using row numbers with a subquery making the task somewhat tedious as follows.

SELECT country_id, country_name
FROM
  (SELECT rownum as row_num, country_id, country_name
   FROM
    (SELECT country_id, country_name
     FROM country
     ORDER BY country_id desc)
  WHERE rownum <= 10
  )
WHERE row_num >=5;

In Oracle 10g (or higher, I'm not sure about the higher versions though), this can be made somewhat easy such as,

SELECT country_id, country_name
  FROM (SELECT country_id, country_name, row_number() over (order by country_id desc) rank
      FROM country)
 WHERE rank BETWEEN 6 AND 10;

---

Regarding an application like a web application, the concept of pagination is required to implement almost everywhere and writing such SQL statements every time a (select) query is executed is sometimes a tedious job.

Suppose, I have a web application using Java. If I use the Hibernate framework then there is a direct way to do so using some methods supported by Hibernate like,

List<Country>countryList=session.createQuery("from Country order by countryId desc")
.setFirstResult(4).setMaxResults(5).list();

but when I simply use JDBC connectivity with Oracle like,

String connectionURL = "jdbc:oracle:thin:@localhost:1521:xe";
Connection connection = null;
Statement statement = null;
ResultSet rs = null;

Class.forName("oracle.jdbc.OracleDriver").newInstance();
connection = DriverManager.getConnection(connectionURL, "root", "root");
statement = connection.createStatement();
rs = statement.executeQuery("SELECT * from country");

My question in this case, is there a precise way to retrieve a specified range of rows using this code? Like in the preceding case using the methods something like setFirstResult() and setMaxResults()? or the only way to achieve this is by using those subqueries as specified.

Answer 1

Because 'No' is an answer too:

Unfortunately, you will have to use the subquery approach. I would personally use the one with the rank (the second one).