Many time we need SQL query which return data page by page i.e. 30 or 40
records at a time, which can be specified as page size. In fact Database
pagination is common requirement of Java web developers, especially dealing with largest data sets. In this article we
will see how to query Oracle 10g database for pagination or how to retrieve
data using paging from Oracle. Many Java programmer also use display
tag for paging in JSP which supports both internal and external paging. In
case of internal paging all data is loaded in to memory in one shot and display
tag handles pagination based upon page size but it only suitable for small data
where you can afford those many objects in memory. If you have hundreds of row
to display than its best to use external pagination by asking database
to do pagination. In pagination, ordering is another important aspect which can
not be missed. It’s virtually impossible to sort large collection in Java using
Comparator or Comparable
because of limited memory available to Java program, sorting data in database
using ORDER BY clause itself is good solution while doing paging in web
application.
Pagination SQL query in Oracle 10g database with Example:
In database paging we only query data which we required to show or may be
up to 3 page just to prefetch some data in advance for performance reason.
Thankfully Oracle database provides a convenient method row_number() which can
be used to provide a unique row number to each row in result set. By including row_number() in query
you can produce a result set which is numbered and than its just a job to
retrieve data from specified indexes or pages. Here is an example of pagination query in Oracle 10g database:
SELECT *
FROM (
SELECT
ord.*,
row_number() over (ORDER BY ord.order_id ASC) line_number
FROM Orders ord
) WHERE line_number BETWEEN 0 AND 5 ORDER BY line_number;
SELECT
ord.*,
row_number() over (ORDER BY ord.order_id ASC) line_number
FROM Orders ord
) WHERE line_number BETWEEN 0 AND 5 ORDER BY line_number;
This will print result of query including an additional column called line_number which will
automatically be populated by Oracle because of row_number() function.
By using this line_number column now we can get result page
by page based upon size of page or more specially from one row to another like
from 1 to 30 or 5th to 30 whatever since you can pass
starting row and ending row from Java
program to Oracle database. Though I know MySQL database has inbuilt paging
support using LIMIT keyword , this row_number() function
of Oracle is equally useful for paging in Oracle database. I have also found
that SQL Server also supports row_number() function,
which can be used to get data page by page in SQL Server.
Other Java and SQL tutorials from Learn About Linux Blog
No comments:
Post a Comment