Discussions
Categories
- 196.7K All Categories
- 2.2K Data
- 235 Big Data Appliance
- 1.9K Data Science
- 449.8K Databases
- 221.5K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 549 MySQL Community Space
- 477 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 532 SQLcl
- 4K SQL Developer Data Modeler
- 186.8K SQL & PL/SQL
- 21.3K SQL Developer
- 295.4K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.1K Development Tools
- 104 DevOps
- 3.1K QA/Testing
- 645.9K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 153 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 17 Java Essentials
- 158 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 203 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 394 LiveLabs
- 37 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.6K Other Languages
- 2.3K Chinese
- 170 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 230 Portuguese
pagination

838529
Member Posts: 6
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>
<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>
Answers
-
Mysql has a LIMIT option with which you can get a specific subset of a resultset. I suggest you investigate it.
-
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
-
Mikado wrote: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.
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
- 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. -
Thanks gimbal2 for your advice am grateful, please help me with the url parameter. please just be my Hero!
-
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. -
pls gimbal i need a sample code
This discussion has been closed.