title: Oracle and Pagination
body: <p>In MySql, the concept of pagination can easily be implemented with a single SQL statement using the <code>LIMIT</code> clause something like the following.</p>
  
  <pre><code>SELECT country_id, country_name 
  FROM country c 
  ORDER BY country_id DESC 
  LIMIT 4, 5;
  </code></pre>
  
  <p>It would retrieve the rows starting from <code>5</code> to <code>10</code> in the result set which the SQL query retrieves.</p>
  
  <hr>
  
  <p>In Oracle, the same thing can be achieved using <em>row numbers</em> with a subquery making the task somewhat tedious as follows.</p>
  
  <pre><code>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 &lt;= 10
    )
  WHERE row_num &gt;=5;
  </code></pre>
  
  <p>In Oracle 10g (or higher, I'm not sure about the higher versions though), this can be made somewhat easy such as,</p>
  
  <pre><code>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;
  </code></pre>
  
  <hr>
  
  <p>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. </p>
  
  <p>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,</p>
  
  <pre><code>List&lt;Country&gt;countryList=session.createQuery("from Country order by countryId desc")
  .setFirstResult(4).setMaxResults(5).list();
  </code></pre>
  
  <p>but when I simply use JDBC connectivity with Oracle like,</p>
  
  <pre><code>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");
  </code></pre>
  
  <p>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 <code>setFirstResult()</code> and <code>setMaxResults()</code>? or the only way to achieve this is by using those subqueries as specified.</p>