In MySql, the concept of pagination can be easily implemented with a single SQL statement using the LIMIT , something like the following.
SELECT country_id, country_name FROM country c ORDER BY country_id DESC LIMIT 4, 5;
It will retrieve rows starting from 5 to 10 in the result set that retrieves the SQL query.
In Oracle, the same thing can be achieved by using line numbers with a subquery, making the task somewhat tedious, as shown below.
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), this can be made somewhat simpler, for example,
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;
For an application such as a web application, the concept of pagination needs to be implemented almost everywhere and writing such SQL statements every time a query is executed (select), sometimes it is 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 this 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 just use a JDBC connection with Oracle, for example,
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, is there an exact way to get a given range of strings using this code? As in the previous case, using methods like setFirstResult() and setMaxResults() ? or the only way to achieve this is to use the subqueries that are specified.