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