paging in Jsp: jsp code

View Answers

August 29, 2008 at 12:48 PM

Hi friend,

<%@ page language="java" import="java.sql.*;"%>

<html>

<head>
<title>pagination using jsp with database</title>

</head>

<body>

<br><br><br>

<%

int offset=0;

int ofset = 0;

if(request.getParameter("offset")!=null) {
offset= Integer.parseInt(request.getParameter("offset").toString());

}

int total_count = 0;

int total_page = 0;

int per_page =1;

String name="";

if(offset>1){

ofset = offset*per_page-per_page;

}

if((request.getParameter("userid")!=null) && request.getParameter("userid")!="")

{

String user = session.getAttribute("userid").toString();

String userid = request.getParameter("userid");

String sd[] = request.getParameter("sdate").toString().split("-");

String ed[] = request.getParameter("edate").toString().split("-");

String sdate = sd[2]+"-"+sd[0]+"-"+sd[1] ;

String edate = ed[2]+"-"+ed[0]+"-"+ed[1];

System.out.println("MySQL Connect Example.");

Connection con = null;
String url = "jdbc:mysql://192.168.10.211:3306/";;
String db = "amar";
String driver = "com.mysql.jdbc.Driver";
String userName ="amar";
String password="amar123";

String color = "#F9EBB3";

try {

Class.forName(driver);
con = DriverManager.getConnection(url+db,userName,password);

Statement stcount = con.createStatement();

String strQuerycount = "select count(*) from Userdetail";

ResultSet rscount = stcount.executeQuery(strQuerycount);

if(rscount.next()){
total_count=rscount.getInt(1);
}
if(total_count%per_page==0){
total_page = total_count/per_page;
}
else{
total_page = total_count/per_page+1;
}
out.println(total_count);
out.println(total_page);
out.println(ofset);

if(offset>1)

{

int previous = offset-1;

%>

<td>

</td>

<%

}

if(total_page>0)

{

for(int i=1;i<=total_page;i++)

{

if(i==offset)

{

%>

<td>

<%=i%></td>

<%

}

else{

%>

<td>

</td>

<%

}

}

}

if(offset<total_page)

{

int next = offset+1;

%>

<td>

<a href="viewTransaction.jsp?userid=<%=userid%>&username=<%=request.getParameter("username")%>&lastname=<%=request.getParameter("lastname")%>&offset=<%=next%>">Next</a></td>

<%

}

Statement st1 = con.createStatement();

String strQuery1 = "select * from Userdetail";

ResultSet rs1 = st1.executeQuery(strQuery1);

if(rs1.next()) {
name =rs1.getString(1);

}

Statement st = con.createStatement();

String strQuery = "select * from Userdetail";

// out.println(strQuery);

ResultSet rs = st.executeQuery(strQuery);

int count=offset-1;

%>

August 29, 2008 at 12:50 PM

<table style="padding-left:150px;">

</table>

<br><br><br>

<table width="700px" align="center" style="border:1px solid #000000;">

<tr>

<td colspan=8 align="center" style="background-color:ffeeff"><b>Transaction</b></td>

</tr>

<tr style="background-color:efefef;">

<td><b>SNo</b></td>

<td><b>UserName</b></td>

<td><b>LastName</b></td>

<td><b>E-mail</b></td>
<td><b>Date Of Birth</b></td>
<td><b>Country</b></td>
<td><b>State</b></td>

</tr>

<%

while(rs.next())

{

if((count%2)==0){

color = "#eeffee";

}

else

{

color = "#F9EBB3";

}

count++;

%>

<tr style="background-color:<%=color%>;">

<td><%=count%></td>

<td><%=rs.getInt(1)%></td>

<td><%=rs.getString(2)%></td>

<td><%=rs.getString(3)%></td>
<td><%=rs.getString(4)%></td>
<td><%=rs.getString(5)%></td>
<td><%=rs.getString(6)%></td>
<td><%=rs.getString(7)%></td>

</tr>

<%

}

if(count==0)

{

%>

<tr style="background-color:<%=color%>;">

<td colspan=8 align="center">No Record</td>

</tr>

<%

}

System.out.println("Connected to the database");

con.close();

System.out.println("Disconnected from database");

} catch (Exception e) {

e.printStackTrace();

}

}

%>

</table>

<br>

<table width="800px">

<tr><td width="100%" align="right">Go Back</a></td></tr>

</table>

</body>

</html>

-----------------------------

Read for more information.

http://www.roseindia.net/jsp/

Thanks.

amardeep

August 29, 2008 at 12:51 PM

Hi friend,

Complete code for paging

Do some changes

1.tablename,filename,databasename,
per_page : no of records to show per page:

<%@ page language="java" import="java.sql.*;"%>
<html>
<head>
<style>
A:hover {

text-decoration: none;
font-family:arial;
font-size:12px;
color: #000000;
BORDER: none;

}
A {

text-decoration: underline;
font-family:arial;
font-size:12px;
color: #000000;
BORDER: none;

}

</style>
</head>
<body>
<br><br><br>

<%
int offset=0;
int ofset = 0;
if(request.getParameter("offset")!=null) {
offset= Integer.parseInt(request.getParameter("offset").toString());
}

int total_count = 0;
int total_page = 0;
int per_page =10;
String name="";
if(offset>1){
ofset = offset*per_page-per_page;
}

System.out.println("MySQL Connect Example.");
Connection conn = null;
String url = "jdbc:mysql://localhost:3306/";;
String dbName = "databasename";
String driver = "com.mysql.jdbc.Driver";
String username = "root";
String userPassword = "root";
String color = "#F9EBB3";

try {
Class.forName(driver).newInstance();
conn = DriverManager.getConnection(url+dbName,username,userPassword);

Statement stcount = conn.createStatement();
String strQuerycount = "select count(*) from tablename ";
ResultSet rscount = stcount.executeQuery(strQuerycount);
if(rscount.next())
{
total_count=rscount.getInt(1);
}

if(total_count%per_page==0)
{
total_page = total_count/per_page;
}
else
{
total_page = total_count/per_page+1;
}
//out.println(total_count);
//out.println(total_page);
//out.println(ofset);

Statement st = conn.createStatement();
String strQuery = "select * from tablename limit "+ofset+","+per_page;
// out.println(strQuery);

ResultSet rs = st.executeQuery(strQuery);
// out.println("offset " + offset);
int count=0;
if(offset>1)
count=offset*per_page-per_page;

%>

<br><br><br>
<table width="700px" align="center" style="border:1px solid #000000;" >
<tr>
<td colspan=8 align="center" style="background-color:ffeeff"><b>Transaction Report</b></td>
</tr>
<tr style="background-color:efefef;">
<td><b>SNo</b></td>
<td><b>Fieldname</b></td>

</tr>
<%

while(rs.next()){

if((count%2)==0){
color = "#eeffee";

}
else{
color = "#F9EBB3";
}
count++;

%>
<tr style="background-color:<%=color%>;">
<td><%=count%></td>
<td>fieldvalue</td>

</tr>

August 29, 2008 at 12:52 PM

<%
}
%>
</table>
<br><br>
<table width="100px" align="center" border=0><tr>
<%
if(count==0)
{
%>
<tr style="background-color:<%=color%>;">
<td colspan=8 align="center">No Record</td>

</tr>
<%
}

System.out.println("Connected to the database");
conn.close();
System.out.println("Disconnected from database");
} catch (Exception e) {
e.printStackTrace();
}

if(offset>1){
int previous = offset-1;
%>
<td>
<a href="filename?offset=<%=previous%>">Previous</a></td>
<%
}

if(total_page>0)
{
for(int i=1;i<=total_page;i++)
{
if(request.getParameter("offset")==null)
{
offset=1;
}
if(i==offset)
{
%>
<td>
<%=i%></td>
<%
}
else{
%>
<td>
<a href="filename?offset=<%=i%>"><%=i%></a></td>
<%
}

}
}

if(offset<total_page)
{
int next = offset+1;
%>
<td>
<a href="filename?offset=<%=next%>">Next</a></td>
<%
}

%>
</tr>
</table>

</body>
</html>

paging in Jsp: jsp code

Questions by Category