日期:2014-05-16 浏览次数:20376 次
<%@ 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 ty