I want to display results from database 10 per page in jsp

Question

I have the following code for my website, and I want to expand this result to display 10 results per page. If some one can help me I will be grateful.

java.sql.PreparedStatement p = servlet1.DB.query("select * from user where userdate like  ");
p.setString(1,userdate);
java.sql.ResultSet r = p.executeQuery();

Answer 1

Have a read at http://www.javaranch.com/journal/2008/08/pagination-using-JDBC-and-JSP.html. This talks about various ways of pagination. Choose which fits best in your case.

Answer 2

There are two basic approaches.

The most efficient is to do the pagination in the database. You will need to implement something like this:

public long countMyData(..query params..);

public Object[] loadMyDataPage(..query params.., long startIndex, long count);

Having those two methods, you then render the page controls on the page based on the number of results from countMyData(). When user selects a new page you get the data for that page only using loadMyDataPage() with correct startIndex and count.

You also want to make sure that the query is relatively "steady" - the result will be mostly same when it's called time after time. The simplest way to do that is to make sure you sort the result on something sensible - like topic date for forum software or something like that. Otherwise the items will "jump" around.

Second approach is to just load everything in one go and store it in some cache, and then display from there. The problem is that it is highly wasteful especially if it's unique per visitor, so you need to be careful not to waste all memory if you try to do it this way.

Answer 3

Have a look at DisplayTag. It is a JSP tag library that can take a result set and display it in a tabular fashion, including pagination:

<sql:query var="results">
  select * from table
</sql:query>

<display:table name="${results.rows}" pagesize="10"/>

Answer 4

Hi,

Take a look at this links.

The pattern (you need to know):

Value List Handler Pattern.

The implementation (you can use this):

ValueList: An implementation of Value List Handler Pattern.

Hope this will help you.