页面代码为：
<%@ page language="java" contentType="text/html; charset=GB18030"
pageEncoding="GB18030" import="java.sql.*"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=GB18030">
<title>实现不带查询的真分页功能</title>
<script type="text/javascript">
function openPage(curPage) {
document.paginate.cp.value = curPage;
document.paginate.selPage.value = curPage;//为了url中两个参数同步
document.paginate.submit();
}
function selOpenPage() {
document.paginate.cp.value = document.paginate.selPage.value;
document.paginate.submit();
}
</script>
</head>
<body>
<%
//解决乱码问题
request.setCharacterEncoding("GB18030");
%>
<%!
final String jspUrl = "person08.jsp" ;
%>
<%
//每页显示的最大记录数
int lineSize = 10;
//当前页数
int currentPage = 1;
//总页数
int pageSize = 0;
//总记录数
int allRecoders = 30;
//查询关键字
String keyWord = null;
%>
<%
try {
//设置当前页
currentPage = Integer.parseInt(request.getParameter("cp"));
} catch (Exception e) {
}
//接受查询关键字
keyWord = request.getParameter("kw");
%>
<%
final String DRIVER = "com.mysql.jdbc.Driver";
final String URL = "jdbc:mysql://localhost:3306/test";
final String USER = "root";
final String PASSWORD = "123";
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
%>
<%
try {
Class.forName(DRIVER);
conn = DriverManager.getConnection(URL, USER, PASSWORD);
//最好使用StringBuffer，但这里为了直观性，所以使用String
String sql = null;
//根据查询关键字有无来选择执行的sql语句(只是为了获得表中是否有数据，所以不需要加入limit关键字)
if (keyWord == null || "".equals(keyWord)) {
sql = "select count(id) from person limit " + ((currentPage - 1) * lineSize) + "," + lineSize;
} else {
sql = "select count(id) from person where uid like ? or name like ? limit " + ((currentPage - 1) * lineSize) + "," + lineSize;
}
pstmt = conn.prepareStatement(sql);
//如果有查询关键字则加入查询的条件
if (keyWord != null) {
StringBuffer key = new StringBuffer();
key.append("%").append(keyWord).append("%");
pstmt.setString(1, key.toString());
pstmt.setString(2, key.toString());
}
rs = pstmt.executeQuery();
if (rs.next()) {
//allRecoders = rs.getInt("id");//java.sql.SQLException: Column 'id' not found.
allRecoders = rs.getInt(1);
}
rs.close();
pstmt.close();
//计算总页数的算法
//pageSize = (allRecoders + lineSize - 1) / lineSize;
pageSize = ((allRecoders % lineSize) == 0) ? allRecoders / lineSize : (allRecoders / lineSize) + 1;
//加入了limit关键字，实现了真分页
if (keyWord == null || "".equals(keyWord)) {
sql = "select id, uid, name, password from person limit " + ((currentPage - 1) * lineSize) + "," + lineSize;
} else {
sql = "select id, uid, name, password from person where uid like ? or name like ? limit " + ((currentPage - 1) * lineSize) + "," + lineSize;
}
pstmt = conn.prepareStatement(sql);
if (!(keyWord == null || "".equals(keyWord))) {
StringBuffer key = new StringBuffer();
key.append("%").append(keyWord).append("%");
pstmt.setString(1, key.toString());
pstmt.setString(2, key.toString());
}
rs = pstmt.executeQuery();
%>
<h1 align="center">人员列表</h1>
<div align="right">
<a href="#">添加人员信息</a>
<a href="<%=jspUrl %>">重置当前页</a> <a href="index.jsp">返回index页面</a>
</div>
<hr>
<div align="center">
<form action="<%=jspUrl %>" name="paginate">
输入查询关键字：<input type="text" name="kw" value="<%=keyWord == null ? "" : keyWord %>">
<input type="submit" value="查询"><br>
<%
//查询有数据时才显示下面的内容
if (allRecoders > 0) {
%>
<input type="button" value="首页" onclick="openPage(1)" <%=(currentPage == 1) ? "disabled" : "" %>>
<input type="button" value="上一页" onclick="openPage(<%=currentPage - 1 %>)" <%=(currentPage == 1) ? "disabled" : "" %>>
<input type="button" value="下一页" onclick="openPage(<%=currentPage + 1 %>)" <%=(currentPage == pageSize) ? "disabled" : "" %>>
<input type="button" value="尾页" onclick="openPage(<%=pageSize %>)" <%=(currentPage == pageSize) ? "disabled" : "" %>>
<input type="hidden" value="" name="cp">
<font color="red" size="5"><%=currentPage%></font>
/
<font color="red" size="5"><%=pageSize%></font>
跳转到：
<select name="selPage" onchange="selOpenPage()">
<%
for (int k = 1; k <= pageSize; k++) {
%>
<option value="<%=k %>" <%=currentPage == k ? "selected" : "" %>><%=k %></option>
<%
}
%>
</select>
页
<%
}
%>
</form>
</div>
<br>
<table border="1" width="80%" align="center">
<tr>
<th>ID</th>
<th>用户ID</th>
<th>用户姓名</th>
<th>用户密码</th>
<th colspan="2">操作</th>
</tr>
<%
boolean flag = false;
while (rs.next()) {
flag = true;
%>
<tr>
<td><%=rs.getInt("id") %></td>
<td><%=rs.getString("uid") %></td>
<td><%=rs.getString("name") %></td>
<td><%=rs.getString("password") %></td>
<td><a href="#">更新</a></td>
<td><a href="#">删除</a></td>
</tr>
<%
}
rs.close();
pstmt.close();
if (!flag) {
%>
<tr>
<td colspan="6" style="text-align: center;">没有相关的数据！！！</td>
</tr>
<%
}
%>
</table>
<%
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (Exception ex) {
ex.printStackTrace();
} finally {
conn.close();
}
%>
</body>
</html>
报错信息为：
java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0). at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:910) at com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:2796) at com.mysql.jdbc.PreparedStatement.setString(PreparedStatement.java:3627) at org.apache.jsp.person08_jsp._jspService(person08_jsp.java:129) at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:98) at javax.servlet.http.HttpServlet.service(HttpServlet.java:729) at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:331) at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:329) at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:265) at javax.servlet.http.HttpServlet.service(HttpServlet.java:729) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:269) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:172) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:117) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:108) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:174) at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:873) at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:665) at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:528) at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:81) at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:689) at java.lang.Thread.run(Thread.java:595)
问题自己解决了，下面是解答
这个例子里虽然没有做查询功能，但自己没有去除掉做jsp实现假分页是的输入框。
输入查询关键字：<input type="text" name="kw" value="<%=keyWord == null ? "" : keyWord %>">
在里面的value属性中做了null值处理。
而我在为了获取数据库表中有没有数据的操作中的判断是这样的：
//如果有查询关键字则加入查询的条件
if (keyWord != null) {
StringBuffer key = new StringBuffer();
key.append("%").append(keyWord).append("%");
pstmt.setString(1, key.toString());
pstmt.setString(2, key.toString());
}
没有加入对空字符串的判断，所以在输入查询关键字的输入框里如果没有填入内容的话，
//根据查询关键字有无来选择执行的sql语句(只是为了获得表中是否有数据，所以不需要加入limit关键字)
if (keyWord == null || "".equals(keyWord)) {
sql = "select count(id) from person";
} else {
sql = "select count(id) from person where uid like ? or name like ?";
}
执行的是第一个sql语句，这个时候是不需要加入查询条件的，但是
//如果有查询关键字则加入查询的条件
if (keyWord != null) {
StringBuffer key = new StringBuffer();
key.append("%").append(keyWord).append("%");
pstmt.setString(1, key.toString());
pstmt.setString(2, key.toString());
}
这句话还是会执行到。
PreparedStatement中不需要加入查询条件却加入了查询条件，所以mysql就报错了。
1 楼
Javakeith
2010-12-11
没明白：java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0).
到底是哪错了！
到底是哪错了！