I want a solution for the following
Iam retriving data from a table and displaying the same in the JSP (using grid). There are nearly 500 regards and Iam dispalying 100 on first retrevial. If I click on next, it will hit Database and retrive next 100 records and so on. The same is applicable when I click on previous button.
To know how many records are there, Iam first writing a count query. this is used to dislay on the screen like " 100 of 505 records".
Then I will write the query to display the coloumns which get 100 records.
In summary Iam hitting DB 2 times ( First to get total count and second to get 100 records). This is hitting the performance.
Can't I write one query which counts the total records as well as get the first 100 rows whcih colown values ?
Pls let me know how to achieve this ??
B S Reddy
u can use the following sql:
select (select count(*) from customers) as count,c.* from customers c
but I think u better use some other technique like caching the results...
I would suggest you to take a look at Open Patterns library from OpenSubsystems project (www.opensubsystems.org). It provides List Data pattern, which solves exactly your problem. You do not have to specify or code any SQL and the library provides you the ability to retrieve your data in pages/chunks.