0

I have my classic paginated query already in a properties file, which deals with approx 50000-100000 rows and i have decided that every page i will fetch no more than 5000 rows, now I need help writing a JAVA JDBC Snippet, where I can use the multiple pages of query result returned by the query and handle it using result set -- may be a loop which I am not able to write.

Additionally, Is fetch size the other better alternative to this instead of using the classic pagination? My query:

SELECT *
FROM  (
   SELECT id, col1, col2, rownum rn
   FROM (
      SELECT /*+ first_rows(5000) */ id, col1, col2
      FROM   table1
      ORDER  BY id DESC
   )
   WHERE   rownum <= 5000
)
WHERE  rn >= 1;

Eventaully i need help with java snippet in core java JDBC flavour.

BlackPearl
  • 1,662
  • 1
  • 8
  • 16
Ronson Calvin Fernandes
  • 1
  • 2
  • Have you tried the Oracle trail on JDBC (https://docs.oracle.com/javase/tutorial/jdbc/) from memory it is quite good. Also the JOOQ blog has a couple of excellent articles on pagination sql. – Gavin Apr 17 '19 at 17:27
  • I didnt find any useful built in methods on ResultSet or Statement interfaces in java docs which can help write the result set from a paginated query in a loop manner. Manually I can declare ROWNUMfrom and ROWNUMto variables to track and increment the paginated result and calling the result set in loop so that I can print it to a loop, but is there any better built-in or standard method or interface for this? – Ronson Calvin Fernandes Apr 17 '19 at 18:40

2 Answers2

1

If you intend to process all the rows in your java code, then there is no need to use a pagination query. Instead, you should issue a query for all the records you want to retrieve.

In your java code, process each record one at a time. Do not try to store them all in an in-memory collection (unless you have reason to believe they will fit).

Oracle's JDBC driver will not put all the rows in memory itself. It will receive chunks of rows from the server and pass them to your code as you iterate on the resultset.

The reason you should not be issuing multiple paginated queries is because when you ask Oracle for the second page, it will need to figure out what is on the first page. When you ask for the third page, it will need to figure out pages 1 and 2 first. So this ends up being slower and slower as you work through the pages.

WW.
  • 23,793
  • 13
  • 94
  • 121
0

There are various ways to perform pagination. JDBC Pagination explains a few ways.

Most ORM libraries offer pagination. Sormula library uses PaginatedListSelector to perform pagination (example: http://www.sormula.org/pagination/). I am the author.

Jeff Miller
  • 1,424
  • 1
  • 10
  • 19