Some questions on servlet connected to mysql

[Suvojit Chakraborty]

Hi all,

I am making web application which is more or less simple but at few areas a bit difficult. In the database I only have a customer table which has 6 fields. there will be 100 odd entries.
Now I need to access it through the servlet and display 10 records per page along with the page number in every page & individual page connectors and next and previous page connectors.
Its like the way we get a search result in google, which shows us page numbers like 1 2 3 4 5....
So the resultset elements must be divided into 10 elements per page.

So it should be like this:
col 1 col2 .... col 6
1
2
3
4
5
6
7
8
9
10

Page 1 Goto 2 3 4 5 6 Next ( in case we are not in Page 1 then we also must have previous page connector)

How do I do this. I have connected with database and records are getting displayed on webpage but all of them toghether.

Thanks for suggestions....

[David Newton]

Search the web for "paging"; there are a number of ways this can be implemented, depending on your needs and requirements.

[Bear Bibeault]

There is also a JSP FAQ entry that discusses this. Please be sure to check the FAQs before posting.

[Suvojit Chakraborty]

thanks for giving me the Pagination concept. I have understood it somewhat conceptually. But I dont find any source which describe pagination in context with servlet.
Lot of material is available for mysql-php/jsp/javascript. I am not very good with jsp so I am using servlets. Can You provide me some source for pagination and servlet, or may be keywords....
I tried combinations like pagination in java, pagination in servlets etc....

Thanks

[David Newton]

The concepts are exactly the same.

[Suvojit Chakraborty]

I found this article :pagination

This proposed 2 solution, One through the pagination tag and other through the use of keywords in select. I prefer the 2nd option as I dont know how to integrate custom tags in servlet. I found that Limit and Offset could be useful. I could restrict result to 1st 10 records. But how do I get the links thing and page numbers at bottom. Please help I am clueless.
If helping is too much then at least give me some keywords combination which I can search. All I find is part of forums where people with pre knowledge of this concept ask their queries. I am no proffesional, I am a student.Infact I heard this thing today for the first time. Please give some link to good tutorials for this which teaches from scratch.

thanks

[David Newton]

What, specifically, are you having issues with?

How might pagination work? Well, we need to know how many pages of results there are... this is figured out using the total number of results, and the number of results per page.

We also need to know which results to show on the page: this is probably done by passing in the page number, and using that plus the number of results per page (already mentioned) to figure out the offset into the results.

Maybe try writing some code, or continue searching for "pagination" or "server pagination" if you're still having a problem getting started.

[Suvojit Chakraborty]

David Newton wrote:What, specifically, are you having issues with?

How might pagination work? Well, we need to know how many pages of results there are... this is figured out using the total number of results, and the number of results per page.

We also need to know which results to show on the page: this is probably done by passing in the page number, and using that plus the number of results per page (already mentioned) to figure out the offset into the results.

Maybe try writing some code, or continue searching for "pagination" or "server pagination" if you're still having a problem getting started.

I have gained some knowledge of pagination and I think pagination using repeated access from database suits my requirement. I have found about limit and offset in select clause of mysql. I have found a logic of controlling the number of data extract from database.
If, say, Page variable represent my current page then I could have a query like this:

select * from employee limit 10 offset 10*(page-1);

As said, I will have a page in html like following:

 
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <title></title>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body  bgcolor="Yellow">
      <table border="0" >
          <thead align="center">
              <tr> <td><h1><font face='Verdana' size='9' color="green">Employee Details System</font></h1></td></tr>
          </thead>
          <tbody>
          </tbody>
      </table>
      <table border="1"  >
          <thead align="center">
              <tr>
                  <td ><h1>
                      <font face='Verdana' size='4' color="green">Details Of All Employees :</font>
                     </h1></td>
              </tr>
          </thead>
          <tbody align="center">
              <tr>
                  <td><b>Employee ID</b></td>
                  <td><b>Employee Name</b></td>
                  <td><b>Street</b></td>
                  <td><b>City</b></td>
                  <td><b>State</b></td>
                  <td><b>Pin Code</b></td>
              </tr>
              <tr>
                  <td>E001</td>
                  <td>Suvojit</td>
                  <td>New Delhi</td>
                  <td>CR PARK</td>
                  <td>Delhi</td>
                  <td>110019</td>
              </tr>
          </tbody>
      </table>

     <br><br><br><br>
         <b>Goto : </b> <a >1</a>  <a >2</a>
           <a >3</a>  <a >4</a>
           <a >Next</a>  <a >Previous</a>
           <a >Home</a>

     
  </body>
</html>
   

Now, I have also learnt from this forum that no of rows in database can be found by following:

rs.last();
int i= rs.getRow();
rs.beforeFirst();

where rs=ResultSet obj.

So I can now get the no of pages required like this:

if (i/10==0) then numPage=i/10
else if (i/10!=0) then numPage=i/10+1

Now I can display page links at bottom by having a loop which starts from 1 to numPage

Also I plan to use a static variable page which will keep track of current page.
So the Page number can be displayed by displaying value of page.

But I have following issues:

How do I detect the links clicked's corresponding number. I mean how do I set Page variable to 3 when I click on 3(link) at bottom of page.

Am I moving in right direction? Please give feedback? Any improvement suggestion....

thanks