之前写过关于查询结果的分页显示,后来在其它页面应用的时候居然出现错误。查了许多资料后终于将问题解决。在google的过程中发现,有不少人跟我一样,遇到相同的问题。现在把我的实例代码贴出来供大家参考~~
问题说明 :页面提供模糊查询,并将多个查询条件赋予status变量。在查询结果中分页显示时,页面传递传递两个变量,一个是页数,一个是查询条件即status 。因为status包含% ,所以在传递过程中无法传递。解决方法为在status传递前,把% 替换成* ,当页面接收到status后,再把* 替换回% 。这样在查询结果中传递就没问题了~~~~
页面代码如下:
<form action="lanyhzl.jsp" method="post">
<table width="760" align="center" cellpadding="0" cellspacing="1" class="table" >
<tr class="upTd" >
<td height="22" colspan="7" align="center">LAN用户查询</td>
</tr>
<tr>
<td align="center">账户名</td>
<td align="left"><input name="username" type="text" size="15" value="<%=request.getParameter("username")%>" ></td>
<td align="center">用户名</td>
<td align="left"><input name="fullName" type="text" size="15" value="<%=request.getParameter("fullName")%>" ></td>
<td align="center">身份证号</td>
<td align="left"><input name="idNumber" type="text" size="15" value="<%=request.getParameter("idNumber")%>"></td>
<td align="center"><input type="submit" name="query" value="查询"></td>
</tr>
</table>
</form>
<br>
<%
Connection conn=Database.getConnection();//获取数据库连接
int perpg=15; //每页显示条数
int pg=1; //当前页数记录
int count=0; //取得数据数目
int count2=0; //记录条数
int pagenum; //总页数
String pgsql="";
String gdsql="";
String status = "";
if(request.getParameter("status")!=null && !request.getParameter("status").equals(""))...... {
status = request.getParameter("status").replace("*","%"); //查询条件
}
if(request.getParameter("username")!=null && !request.getParameter("username").equals(""))...... {
status = status+ " and username like ' %"+new String(request.getParameter("username").getBytes("iso-8859-1"),"utf-8")+"% '";
}
if(request.getParameter("fullName")!=null && !request.getParameter("fullName").equals(""))...... {
status = status+ " and fullName like ' %"+new String(request.getParameter("fullName").getBytes("iso-8859-1"),"utf-8")+"% '";
}
if(request.getParameter("idNumber")!=null && !request.getParameter("idNumber").equals(""))...... {
status = status+ " and idNumber like ' %"+new String(request.getParameter("idNumber").getBytes("iso-8859-1"),"utf-8")+"% '";
}
pgsql="select count(username) as num from lan_AcctParam where username<>'' "+status;
//System.out.println(pgsql);
ResultSet res= Database.SQLQuery(conn,pgsql);
if(res.next())......{ //查得数据条数
count=res.getInt("num");
count2=count;
}
res.close();
if(count2==0)...... {%>
<h2><center>没有数据显示,请重新查询!</center></h2>
<%}
else...... {
for(pagenum=0;count>0;pagenum++)......{ //分页
count=count-perpg;
}
if (request.getParameter("page")!=null && !request.getParameter("page").equals(""))...... {
pg=Integer.parseInt(request.getParameter("page"));
if (pg>pagenum)...... {
pg=pagenum;
}
else if(pg<=1)...... {
pg=1;
}
}
gdsql = "select top "+perpg+" username,fullName,idNumber,address,email,feeType,openPayAmount,nasPort,framedIp from"
+" lan_AcctParam where username not in"
+" (select top "+(pg-1)*perpg+" username from lan_AcctParam"
+" where username<>'' "+status+" order by username desc) and username<>'' "+status+" order by username desc";
res=Database.SQLQuery(conn,gdsql);
%>
<table width="760" border="0" align="center" cellpadding="0" cellspacing="1" class="table_B">
<tr class="upTd">
<td height="22" align="center">账户名</td>
<td align="center">用户名</td>
<td align="center">证件号</td>
<td align="center">联系方式</td>
</tr>
<%while(res.next())...... {%>
<tr>
<td align="center" height="22"><c:out value="<%=res.getString("username")%>"/></td>
<td align="center"><c:out value="<%=res.getString("fullName")%>"/></td>
<td align="center"><c:out value="<%=res.getString("idNumber")%>"/></td>
<td align="center"><c:out value="<%=res.getString("address")%>"/></td>
</tr>
<%
}
res.close();
conn.close();
status = status.replace("%","*");
%>
</table>
<table width="760" border="0" align="center" cellpadding="0" cellspacing="1">
<tr>
<td>
<% if (pagenum>1 && pg!=1) ...... {%><a href="lanyhzl.jsp?page=1&status=<%=status%>">首页</a><% } %>
<% if (pagenum>1 && pg>1) ......{%><a href="lanyhzl.jsp?page=<%=pg-1%>& status=<%=status%> ">前一页</a> <% } %>
<% if (pagenum>1 && pg<pagenum) ......{%><a href="lanyhzl.jsp?page=<%=pg+1%>& status=<%=status%> ">下一页</a> <% } %>
<% if (pagenum>1 && pg!=pagenum) ......{%><a href="lanyhzl.jsp?page=<%=pagenum%>& status=<%=status%> ">尾页</a><% } %>
共<%=count2%>条记录 / <%= pagenum %>页,当前是<%= pg %>页</td>
</tr>
</table>
<%}%>
<table width="760" align="center" cellpadding="0" cellspacing="1" class="table" >
<tr class="upTd" >
<td height="22" colspan="7" align="center">LAN用户查询</td>
</tr>
<tr>
<td align="center">账户名</td>
<td align="left"><input name="username" type="text" size="15" value="<%=request.getParameter("username")%>" ></td>
<td align="center">用户名</td>
<td align="left"><input name="fullName" type="text" size="15" value="<%=request.getParameter("fullName")%>" ></td>
<td align="center">身份证号</td>
<td align="left"><input name="idNumber" type="text" size="15" value="<%=request.getParameter("idNumber")%>"></td>
<td align="center"><input type="submit" name="query" value="查询"></td>
</tr>
</table>
</form>
<br>
<%
Connection conn=Database.getConnection();//获取数据库连接
int perpg=15; //每页显示条数
int pg=1; //当前页数记录
int count=0; //取得数据数目
int count2=0; //记录条数
int pagenum; //总页数
String pgsql="";
String gdsql="";
String status = "";
if(request.getParameter("status")!=null && !request.getParameter("status").equals(""))...... {
status = request.getParameter("status").replace("*","%"); //查询条件
}
if(request.getParameter("username")!=null && !request.getParameter("username").equals(""))...... {
status = status+ " and username like ' %"+new String(request.getParameter("username").getBytes("iso-8859-1"),"utf-8")+"% '";
}
if(request.getParameter("fullName")!=null && !request.getParameter("fullName").equals(""))...... {
status = status+ " and fullName like ' %"+new String(request.getParameter("fullName").getBytes("iso-8859-1"),"utf-8")+"% '";
}
if(request.getParameter("idNumber")!=null && !request.getParameter("idNumber").equals(""))...... {
status = status+ " and idNumber like ' %"+new String(request.getParameter("idNumber").getBytes("iso-8859-1"),"utf-8")+"% '";
}
pgsql="select count(username) as num from lan_AcctParam where username<>'' "+status;
//System.out.println(pgsql);
ResultSet res= Database.SQLQuery(conn,pgsql);
if(res.next())......{ //查得数据条数
count=res.getInt("num");
count2=count;
}
res.close();
if(count2==0)...... {%>
<h2><center>没有数据显示,请重新查询!</center></h2>
<%}
else...... {
for(pagenum=0;count>0;pagenum++)......{ //分页
count=count-perpg;
}
if (request.getParameter("page")!=null && !request.getParameter("page").equals(""))...... {
pg=Integer.parseInt(request.getParameter("page"));
if (pg>pagenum)...... {
pg=pagenum;
}
else if(pg<=1)...... {
pg=1;
}
}
gdsql = "select top "+perpg+" username,fullName,idNumber,address,email,feeType,openPayAmount,nasPort,framedIp from"
+" lan_AcctParam where username not in"
+" (select top "+(pg-1)*perpg+" username from lan_AcctParam"
+" where username<>'' "+status+" order by username desc) and username<>'' "+status+" order by username desc";
res=Database.SQLQuery(conn,gdsql);
%>
<table width="760" border="0" align="center" cellpadding="0" cellspacing="1" class="table_B">
<tr class="upTd">
<td height="22" align="center">账户名</td>
<td align="center">用户名</td>
<td align="center">证件号</td>
<td align="center">联系方式</td>
</tr>
<%while(res.next())...... {%>
<tr>
<td align="center" height="22"><c:out value="<%=res.getString("username")%>"/></td>
<td align="center"><c:out value="<%=res.getString("fullName")%>"/></td>
<td align="center"><c:out value="<%=res.getString("idNumber")%>"/></td>
<td align="center"><c:out value="<%=res.getString("address")%>"/></td>
</tr>
<%
}
res.close();
conn.close();
status = status.replace("%","*");
%>
</table>
<table width="760" border="0" align="center" cellpadding="0" cellspacing="1">
<tr>
<td>
<% if (pagenum>1 && pg!=1) ...... {%><a href="lanyhzl.jsp?page=1&status=<%=status%>">首页</a><% } %>
<% if (pagenum>1 && pg>1) ......{%><a href="lanyhzl.jsp?page=<%=pg-1%>& status=<%=status%> ">前一页</a> <% } %>
<% if (pagenum>1 && pg<pagenum) ......{%><a href="lanyhzl.jsp?page=<%=pg+1%>& status=<%=status%> ">下一页</a> <% } %>
<% if (pagenum>1 && pg!=pagenum) ......{%><a href="lanyhzl.jsp?page=<%=pagenum%>& status=<%=status%> ">尾页</a><% } %>
共<%=count2%>条记录 / <%= pagenum %>页,当前是<%= pg %>页</td>
</tr>
</table>
<%}%>
后话 :之所以说是初级查询,是因为将查询代码与显示代码放在同一页面中,既不方便也不安全。最好的办法还是装查询方法封装在Bean中,调用时只传递相应的参数,一举多得。最后,如欲转载,请说明出处~~哈哈~