Searching an element

Dear sir,
sir i have an employee table ,in that i have attributes like empId name ,salary,loc etc.I have one jsp in that i am doing a searching an element by empId and displaying a all the employee table with pagination.I have a 2 forms in one jsp.I have a one input text field for searching an element by the empId.and one submit button for submitting this form.And another form for displaying a all employee table ,it will display all employees.If i dont enter any thing in a text field ,then the table should show a all employees details with pagination.If i give a any empId in a text field then submitted ,the table show only a identical empid records with pagination .so how to do this sir please help me..

Thanks and Regards
Harini Veerapur.

View Answers

June 19, 2010 at 11:36 AM

Hi Friend,

Try the following code:

<%@ page language="java" %>
<%@ page import="java.sql.*" %>
<form method="post" action="pagination.jsp">
Enter ID: <input type="text" name="id"><input type="submit">
</form>

<%
String query1="",query2="";
String ide=request.getParameter("id");

Connection conn = null;
Class.forName("com.mysql.jdbc.Driver").newInstance();
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root";, "root");
ResultSet rs1 = null;
ResultSet rs2 = null;
PreparedStatement ps1=null;
PreparedStatement ps2=null;

int showRows=10;
int totalRecords=10;
int totalRows=nullIntconvert(request.getParameter("totalRows"));
int totalPages=nullIntconvert(request.getParameter("totalPages"));
int iPageNo=nullIntconvert(request.getParameter("iPageNo"));
int cPageNo=nullIntconvert(request.getParameter("cPageNo"));

int startResult=0;
int endResult=0;
if(iPageNo==0){
iPageNo=0;
}
else{
iPageNo=Math.abs((iPageNo-1)*showRows);
}
if(ide==null){
query1="SELECT SQL_CALC_FOUND_ROWS * FROM employee limit "+iPageNo+","+showRows+"";
query2="SELECT FOUND_ROWS() as cnt";
}
else if(ide!=null){
query1="SELECT SQL_CALC_FOUND_ROWS * FROM employee where userid='"+ide+"' limit "+iPageNo+","+showRows+"";
query2="SELECT FOUND_ROWS() as cnt";
}
ps1=conn.prepareStatement(query1);
rs1=ps1.executeQuery();
ps2=conn.prepareStatement(query2);
rs2=ps2.executeQuery();
if(rs2.next()) {
totalRows=rs2.getInt("cnt");
System.out.println(totalRows);
}
%>
<html>
<h3>Pagination of JSP page</h3>
<body>
<form>
<input type="hidden" name="iPageNo" value="<%=iPageNo%>">
<input type="hidden" name="cPageNo" value="<%=cPageNo%>">
<input type="hidden" name="showRows" value="<%=showRows%>">
<table width="100%" cellpadding="0" cellspacing="0" border="1" >
<tr>
<td>First Name</td>
<td>Last Name</td>
<td>Email</td>
<td>Address</td>
</tr>
<%
while(rs1.next()){
%>
<tr>
<td><%=rs1.getString("firstname")%></td>
<td><%=rs1.getString("lastname")%></td>
<td><%=rs1.getString("email")%></td>
<td><%=rs1.getString("address")%></td>
</tr>
<%
}
%>
<%
try{
if(totalRows<(iPageNo+showRows)) {
endResult=totalRows;
}
else{
endResult=(iPageNo+showRows);
}
startResult=(iPageNo+1);
totalPages=((int)(Math.ceil((double)totalRows/showRows)));
}
catch(Exception e){
e.printStackTrace();
}
%>
<tr>
<td colspan="3">
<div>

June 19, 2010 at 11:36 AM

continue..

<%
int i=0;
int cPage=0;
if(totalRows!=0) {
cPage=((int)(Math.ceil((double)endResult/(totalRecords*showRows))));
int prePageNo=(cPage*totalRecords)-((totalRecords-1)+totalRecords);
if((cPage*totalRecords)-(totalRecords)>0){
%>
<a href="pagination.jsp?iPageNo=<%=prePageNo%>&cPageNo=<%=prePageNo%>"> << Previous</a>
<%
}
for(i=((cPage*totalRecords)-(totalRecords-1));i<=(cPage*totalRecords);i++){
if(i==((iPageNo/showRows)+1)){%>
<a href="pagination.jsp?iPageNo=<%=i%>" style="cursor:pointer;color: red"><b><%=i%></b></a>
<%
}
else if(i<=totalPages){
%>
<a href="pagination.jsp?iPageNo=<%=i%>"><%=i%></a>
<%
}
}
if(totalPages>totalRecords && i<totalPages){
%>
<a href="pagination.jsp?iPageNo=<%=i%>&cPageNo=<%=i%>"> >> Next</a>
<%
}
}
%>
<b>Rows <%=startResult%>-<%=endResult%>Total Rows<%=totalRows%> </b>
</div>
</td>
</tr>
</table>
</form>
</body>
</html>
<%
try{
if(ps1!=null){
ps1.close();
}
if(rs1!=null){
rs1.close();
}

if(ps2!=null){
ps2.close();
}
if(rs2!=null){
rs2.close();
}
if(conn!=null){
conn.close();
}
}
catch(Exception e)
{
e.printStackTrace();
}
%>
<%!
public int nullIntconvert(String str){
int num=0;
if(str==null) {
str="0";
}
else if((str.trim()).equals("null")) {
str="0";
}
else if(str.equals("")) {
str="0";
}
try{
num=Integer.parseInt(str);
}
catch(Exception e) { }
return num;
}
%>

Thanks