javaweb｜分页查询
在javaweb中，分页查询可以通过jsp来调用java代码实现实时更新查询结果并且展示的功能。
数据控制层ManagerDao.java
import com.css.domain.ManagerBean; import java.sql.*; import java.util.ArrayList; import java.util.List; /** * 管理员数据操作类 * @author noTalent * @since 2018/5/18 */ public class ManagerDao { private final String url = "jdbc:mysql://localhost:3306/xxx?useUnicode=true&characterEncoding=utf-8&useSSL=false"; private final String username = "xxx"; private final String password = "xxx"; private Connection conn = null; private ResultSet rs = null; private PreparedStatement ps = null; private Statement stmt = null; /** * 连接数据库 */ public void getConn() { try { Class.forName("com.mysql.jdbc.Driver"); conn = DriverManager.getConnection(url, username, password); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } } /** * 关闭数据库连接 */ public void closeConn() { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } /** * 注册管理员账号 * @param manager */ public void addManager(ManagerBean manager) { try { getConn(); String sql = "insert into manager values (?, ?, ?)"; ps = conn.prepareStatement(sql); ps.setString(1, manager.getUsername()); ps.setString(2, manager.getPassword()); ps.setInt(3, manager.getPermission()); ps.executeUpdate(); if (ps != null) { ps.close(); } closeConn(); } catch (SQLException e) { e.printStackTrace(); } } /** * 分页查询 * @param page * @return */ public List<ManagerBean> find(int page, String sql) { List<ManagerBean> list = new ArrayList<ManagerBean>(); /* 分页查询sql语句 String sql="select * from manager limit ?, ?"; */ try { getConn(); ps = conn.prepareStatement(sql); ps.setInt(1, (page - 1) * 8); ps.setInt(2, 8); rs = ps.executeQuery(); while (rs.next()) { ManagerBean manager = new ManagerBean(); manager.setUsername(rs.getString("username")); manager.setPassword(rs.getString("password")); manager.setPermission(rs.getInt("permission")); list.add(manager); } rs.close(); ps.close(); closeConn(); }catch (SQLException e) { e.printStackTrace(); } return list; } /** * 查询管理员总记录数目 * @return */ public int findCount(String sql) { int count = 0; /* String sql = "select count(*) from manager"; */ try { getConn(); stmt = conn.createStatement(); rs = stmt.executeQuery(sql); if (rs.next()) { count = rs.getInt(1); } rs.close(); closeConn(); } catch (SQLException e) { e.printStackTrace(); } return count; } /** * 删除管理员操作 * @param username */ public void delete(String username) { String sql = "delete from manager where username=?"; try { getConn(); ps = conn.prepareStatement(sql); ps.setString(1, username); ps.executeUpdate(); ps.close(); closeConn(); } catch (SQLException e) { e.printStackTrace(); } } }
在查询代码中，可以通过mysql内置的limit来实现获取某一段数据。limit ?, ? 要放在sql语句的末尾（可以再加查询条件），limit第一个参数是从第几个数据开始获取，第二个参数是获取的数据项数目。所谓分页，就是通过jsp传参获取到具体的页面数来调用数据控制层的查询方法。
例如：https://mp.****.net/postedit?page=x
上面的url中，page假设是第几页的参数名，xxx是获取到的第几页对应的参数值，即request.getParameter("condition");
所以我们可以根据给页面跳转传递跳转到的页面来实现分页查询。看下面代码：
<div class="manager-list"> <table class="tableDecoration"> <thead> <tr> <td>用 户 名</td> <td>密 码</td> <td>管 理 权 限</td> <td>操 作</td> </tr> </thead> <% /* 查询 */ ManagerDao managerDao = new ManagerDao(); int currPage = 1; String sql = null; String sqlCount = null; /* 获取页面参数 */ if (request.getParameter("page") != null) { currPage = Integer.parseInt(request.getParameter("page")); } /* 判断是否有条件参数而更改查询语句 */ if (request.getParameter("condition") == null) { sql = "select * from manager limit ?, ?"; sqlCount = "select count(*) from manager"; } if (request.getParameter("condition") != null) { sql = "select * from manager where username like \"%" + request.getParameter("condition") + "%\" limit ?, ?"; sqlCount = "select count(*) from manager where username like \"%" + request.getParameter("condition") + "%\""; } /* 删除操作 */ if (request.getParameter("delete") != null) { if (!request.getParameter("delete").equals("")) { String delete = request.getParameter("delete"); if (delete != null) { if (!delete.equals("")) { managerDao.delete(delete); } } } } /* 获取查询结果 */ List<ManagerBean> list = managerDao.find(currPage, sql); int pages; int count = managerDao.findCount(sqlCount); if (count % 8 == 0) { pages = count / 8; } else { pages = count / 8 + 1; } StringBuffer sb = new StringBuffer(); /* 通过循环构建分页条 */ for (int i = 1; i <= pages; i++) { if (i == currPage) { sb.append("[" + i + "]"); } else if (i != currPage && request.getParameter("condition") != null && !request.getParameter("condition").equals("")) { sb.append("<a href='manager.jsp?condition=" + request.getParameter("condition") + "&page=" + i + "'>" + i + "</a>"); } else { sb.append("<a href='manager.jsp?page=" + i + "'>" + i + "</a>"); } sb.append(" "); } %> <% for(ManagerBean m:list) { %> <tr class="differTr"> <td><%=m.getUsername()%></td> <td><%=m.getPassword()%></td> <td><%=m.getPermission()%></td> <%-- jsp方法嵌入java代码参数必须加单引号 --%> <td><a id="delete" onclick="deleteManager('<%=m.getUsername()%>');" href="javascript:void(0)">×</a></td> </tr> <% } %> <tr> <td></td> <td></td> <td></td> <td id="differPage"><%=sb.toString()%></td> </tr> </table> </div>
最后展示的页面如下图所示：