Paging in Java Server Pages


In this article we will learn how to do paging in JSP. In this paging, on each page, 10 records will be shown, if there are more then other records will be shown in the second link when the user clicks that link.

Here we use a Type-1 Driver (JDBC:ODBC bridge driver) for connection to an Oracle database

Creation of dsn(database source name) for Oracle

Start-Control panel- Administrative Tools- Data Sources (ODBC)-go to system dsn tab-click add button-select a driver for which you want to set up data source (for Oracle- Oracle in XE)-select it and click finish-give any name in data source name textbox-then click ok button.

Note: - Here Username=system, Password=pintu and Dsn name=dsn1

Table Creation with data

Create table emp(rownumber int,empno varchar(50),ename varchar(50),eaddress varchar(50),sal int)

insert into emp values(1,'e001','Raj','Delhi',1000)
insert into emp values(2,'e002','Raju','Mumbai',2000)
insert into emp values(3,'e003','Rajesh','Pune',3000)
insert into emp values(4,'e004','Ravi','Noida',4000)
insert into emp values(5,'e005','Rahul','Oslo',5000)
insert into emp values(6,'e006','Harry','Puri',6000)
insert into emp values(7,'e007','Potter','Cuttack',7000)
insert into emp values(8,'e008','Tim','Kolkata',8000)
insert into emp values(9,'e009','Jack','Newyork',9000)
insert into emp values(10,'e010','Rohit','London',10000)
insert into emp values(11,'e011','Barry','Sydney',110000)
insert into emp values(12,'e012','Larry','Perth',120000)

paging.JSP

<!--paging sample code for SQL server/Oracle/MySQL -->
<%@ page import="java.sql.*" %>
<html>
<head>
<body>
<%! int no_of_page,row_per_page=10,pages,start_row_count,end_row_count;String page_name="paging.JSP"; Connection con=null; %>

<%
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con=DriverManager.getConnection("jdbc:odbc:dsn1","system","pintu");
Statement stmt=con.createStatement();
ResultSet rs=stmt.executeQuery("Select count(*) from emp");
int total=0;
if(rs.next())
total=rs.getInt(1);

if(total%10==0)
no_of_page=total/10;
else
no_of_page=total/10+1;


%>
<table width="351" border="1">
<tr>
<th>
<h2>
<%
for(int i=1;i<=no_of_page;i++)
{
out.println("<a href='"+page_name+"?pages="+i+"'>"+i+"</a>");
}
%>
</h2></th>
</tr>
</table>

<%
String pages1=request.getParameter("pages");
if(pages1!=null)
{
pages=Integer.parseInt(pages1);
}
if(pages==0) pages=1;
if(pages==1)
{
start_row_count=1;
}
else
{
start_row_count=(pages-1)*10;
}

end_row_count=pages*10;

String query="Select (Select count(*) from emp counter where counter.empno<=emp.empno) as rownumber,empno,ename,eaddress,sal from emp where (Select count(*) from emp counter where counter.empno<=emp.empno) between "+start_row_count+" and "+end_row_count +" order by empno";
ResultSet rs1=stmt.executeQuery(query);

/*
for mysql
String query="Select * from emp limit "+start_row_count +",10";
*/
%>
<table width="351" border="1">
<tr>
<th width="79"><font color='#663300'>Row No </font></th>
<th width="137"><font color='#663300'>Emp Number</font></th>
<th width="113"><font color='#663300'>Name</font></th>
<th width="113"><font color='#663300'>Address</font></th>
<th width="113"><font color='#663300'>Salary</font></th>
</tr>
<%
int rowno=0;
while(rs1.next())
{

rowno =rs1.getInt("rownumber");
String eno =rs1.getString("empno");
String name =rs1.getString("ename");
String address =rs1.getString("eaddress");
int sal =rs1.getInt("sal");

%>

<tr>
<td><font color='red'><%=rowno%></font></td>
<td><font color='red'><%=eno%></font></td>
<td><font color='red'><%=name%></font></td>
<td><font color='red'><%=address%></font></td>
<td><font color='red'><%=sal%></font></td>
</tr>

<%
}
%>
</table>
</body>
</html>

Running the application

Run the tomcat then write the below link in the URL

http://localhost:8081/JSP/

Here JSP is the Context path, which we mentioned in the server.xml file, which is present in (E:\Program Files\Apache Software Foundation\Tomcat 6.0\conf) directory.

After giving the url a set a listing will come, here only one appears As paging.JSP.

PgJSP1.gif

Thanks for reading


Similar Articles