pagination

838529

Please guys am new to J2EE I want to paginate a large result set from an SQL query am using MySQL for eg. if i have 50 records in my DB and i need only 10 records at a time then i will have a next and previous links to go through the records my sql query looks like this

<sql:query var="employees" datasource="jdbc/myDB" >

SELECT * FROM employee

</sql:query>

<c:forEach var="employee" items="${employees.rows}">
<table>
<td>
${employee.firstname}
</td>
</table>
</c:forEach>

gimbal2

Mysql has a LIMIT option with which you can get a specific subset of a resultset. I suggest you investigate it.

838529

is that all the help you can offer me? i know it already i.e(LIMIT 0, 10) but my problem is how to add next and previous links to go the next record in the database

gimbal2

Mikado wrote:
is that all the help you can offer me? i know it already i.e(LIMIT 0, 10) but my problem is how to add next and previous links to go the next record in the database

What is so difficult about it? I believe you think too much from code, like there is a standard solution to every little problem you can encounter which you don't know yet. Think from functional specs for a moment.

- you want to display results in pages of 10 results
- you have a previous link which should show the previous 10 results
- you have a next link which should show the next 10 results
- if there are no previous or next results, the respective link should be disabled.

That probably sums it up as they are very basic pagination requirements. From these specs you can distill a few characteristics just from the terminology used.

- you have PAGES of data. Page 1, page 2, etc.
- each page contains at most 10 results (but there could be less)

Now lets jump to the technical part for a moment. The LIMIT command accepts a starting record and an ending record. There is a relationship between the two: the ending record is always the starting record + 10. This means you don't have to keep track of it, you can calculate it. So you only need to be able to determine the starting record. Might it be as simple as:

starting_record = (current_page * 10) + 1;
ending_record = (starting_record+10) - 1;

? (to make this work, current_page has to be 0-based. So the first page is actually page 0). The +1 and -1 are because resultsets are 1-based, so the first record is actually record 1. This means page 0 is record 1-10, page 1 is record 11-20, etc.

With this start, can you fill in the rest? Forget about disabling links for now, how would you make the previous and next page links work? I can give you a hint: you need only one url parameter.

838529

Thanks gimbal2 for your advice am grateful, please help me with the url parameter. please just be my Hero!

gimbal2

I'll give you one final hint: the url parameter is named 'page', because that is all you need to figure out the starting and ending record.

If your next question is to provide "sample code", that is where I have to disappoint you. To be your 'hero', in the end, I have to not help you with that. You have to figure this out for yourself, because that will be the learning moment. Not only that, I also don't want to rob you of that buzz when you finally do figure it out on your own.

838529

pls gimbal i need a sample code