JDBC Pagination

Question

Hi,

I want to implement pagination using JDBC. The actual thing I want to know is "How can i get first 50 and then next 50 records from database for page 1 and 2 respectively"

My Query is Select * from data [data table contains 20,000 rows]

For page #1 I get 50 records and for page #2 I want to get next 50 records. How can I implement it efficiently in JDBC?

I have searched and found that rs.absolute(row) is the way to skip first page records but it takes some amount of time on large result sets and I don't want to bear this amount of time. Also, I don't want to use rownum and limit + offset in query because these are not good to use in query, I dont know why, still I don't want to use it in query.

Can anyone help me how to get limited ResultSet for pagination or is there any way JDBC is giving us?

Answer 1

I understand implicitly that you do not want the JDBC connection to have a single gigantic resultset which you keep open for a very long time and navigate when required.

The usual approach is to add the SQL needed to only get a subset of the full request, which unfortunately is different from database to database, and will make your SQL statements vendor specific. If I recall correctly, LIMIT is used with MySQL. Ask for the appropriate range for every request.

I also believe that Hibernate contains functionality which allows you to do this for HQL, but I am unfamiliar with it.

Answer 2

If you are using MySQL or PostgreSQL limit and offset are your keywords. MSSqlServer and Oracle have similar features, but I seems to be a bit more painful.

For MySQL and PostgreSQL have a look here:

http://www.petefreitag.com/item/451.cfm

For Oracle have a look here:

http://www.oracle-base.com/forums/viewtopic.php?f=2&t=8635

Answer 3

Are you using some kind of ORM Framework like hibernate or even Java Persistence API or just plain SQL?

My Answer then: use LIMIT and OFFSET http://www.petefreitag.com/item/451.cfm

Or go via ROWNUM Operator You need a wrapper arround your SQL then, but basicaly it's

  select * from (select bla.*, ROWNUM rn from (
  <your sql here>
  ) bla where rownum < 200) where rn >= 150'

Answer 4

There is no efficient way of doing this by simply using JDBC. You have to formulate the limit to n rows and start from i-th item clauses directly to the SQL for it to be efficient. Depending on the database this might actually be quite easy (see MySQL's LIMIT -keyword), on other databases such as Oracle it can be a little trickier (involves subquery and using rownum pseudo column).

Answer 5

You should query only the data you actually need to display on the current page. Do not haul the entire dataset into Java's memory and then filter it there. It would only make things unnecessarily slower.

If you actually have a hard time in implementing this properly and/or figuring the SQL query for the specific database, then have a look at my answer here.

Update: since you're using Oracle, here's an Oracle-targeted extract from the aforementioned answer:

In Oracle you need a subquery with rownum clause which should look like:

private static final String SQL_SUBLIST = "SELECT id, username, job, place FROM"
    + " (SELECT id, username, job, place FROM contact ORDER BY id)"
    + " WHERE ROWNUM BETWEEN %d AND %d";

public List<Contact> list(int firstrow, int rowcount) {
    String sql = String.format(SQL_SUBLIST, firstrow, firstrow + rowcount);

    // Implement JDBC.
    return contacts;
}

Answer 6

Oracle supports the standard ROW_NUMBER() window function since 8i so you can use that. You can do it as a parameterized query so you just need to set the start and end row numbers. E.g.

SELECT * 
   FROM ( SELECT *, ROW_NUMBER() ORDER BY (sort key) AS rowNumber FROM <your table name> ) AS data
WHERE 
   rowNumber>=:start AND
   rowNumber<:end

(If you're not using named parameters, replace :start/:end with the positional parameter placeholder '?')

See SELECT SQL, Window Functions on wikipedia. The article also lists the other DBs that support the ROW_NUMBER() standard windowing function.

Answer 7

This is link to a hibernate solution for paginating results: http://stackoverflow.com/questions/489360/hql-row-identifier-for-pagination/840108#840108

Answer 8

PreparedStatement pStmt = // ... however you make it
pStmt.setFetchSize( /* desired number of records to get into memory */ ); 

Note, setFetchSize(int) is only a hint - last time I was using it with MySQL for example, it wasn't supported. Looking around briefly at the Oracle documentation, it looks like their JDBC does support it. I wouldn't quote me on that, but it's worth trying at least; and yes, this answer is fragile, but it might be enough less headache than implementing the robust solution.

Essentially, you can issue the request for everything, and you only get the fetch size into memory at a time (providing you're not holding onto the previous results). So you'd set your fetch size to 50, make your connection/query, display the first 50 results (causing the another fetch for the next bite of your query) and so on.