Servlet+JSP使用JDBC连接数据库实现分页

使用jdbc对mysql查询数据实现分页并且显示在前段页面。在开始前创建一个访问数据库的工具类DBUtil

/** *数据库工具类 * @author 13468 * */ public class DBUtil { private static DBUtil dbUtil=new DBUtil(); private DBUtil() { try { Class.forName("com.mysql.jdbc.Driver"); } catch (ClassNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } } public static Connection getConnection() throws SQLException { String url="jdbc:mysql://localhost:3306/booksys?useUnicode=true&characterEncoding=utf-8"; return DriverManager.getConnection(url,"root","root"); } }

  在dao层建立数据库jdbc访问层接口和访问接口的实现类访问接口，建立三个方法

//获得用户总数 int getUserCount(); //获取总页数 int getUserPage(int PageeSize); //获取分页数据 List<UserInfo> getUsersPages(int currentPage,int pageSize);

访问接口实现，来实现上面接口的方法

/** * 获取数据总条数 */ @Override public int getUserCount() { Connection connection=null; PreparedStatement preparedStatement=null; ResultSet resultSet=null; int count=0; try { connection=DBUtil.getConnection(); String sql="select count(*) from user"; preparedStatement=(PreparedStatement) connection.prepareStatement(sql); resultSet=preparedStatement.executeQuery(); if(resultSet.next()) { count=resultSet.getInt(1); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally { if(resultSet!=null) { try { resultSet.close(); } catch (SQLException e) { e.printStackTrace(); } } if(preparedStatement!=null) { try { preparedStatement.close(); } catch (SQLException e) { e.printStackTrace(); } } if(connection!=null) { try { connection.close(); } catch (SQLException e) { e.printStackTrace(); } } } return count; } /** * 算出总页数 */ @Override public int getUserPage(int PageeSize) { int count=getUserCount();//获取总条数 int pages=(count+PageeSize-1)/PageeSize; return pages; } //获取分页数据 @Override public List<UserInfo> getUsersPages(int currentPage, int pageSize) { Connection connection=null; PreparedStatement preparedStatement=null; ResultSet resultSet=null; List<UserInfo>list=new ArrayList<UserInfo>(); try { connection=DBUtil.getConnection(); String sql="select * from user limit ?,?"; preparedStatement=(PreparedStatement) connection.prepareStatement(sql); preparedStatement.setInt(1, (currentPage-1)*pageSize); preparedStatement.setInt(2, pageSize); resultSet=preparedStatement.executeQuery(); while (resultSet.next()) { int id=resultSet.getInt(1); String username=resultSet.getString(2); String password=resultSet.getString(3); String name=resultSet.getString(4); String email=resultSet.getString(5); String phone=resultSet.getString(6); list.add(new UserInfo(id,username,password,name,email,phone)); } }catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally { if(resultSet!=null) { try { resultSet.close(); } catch (SQLException e) { e.printStackTrace(); } } if(preparedStatement!=null) { try { preparedStatement.close(); } catch (SQLException e) { e.printStackTrace(); } } if(connection!=null) { try { connection.close(); } catch (SQLException e) { e.printStackTrace(); } } } return list; }

service层省略，但是这个例子是创建了service层的，下面再创建控制层的servlet来调用上面的方法

protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { /** * 分页显示用户信息 */ if (action.equals("showusers")) { String cupage=request.getParameter("currentPage"); //获取总页数 int pages=service.getUserPage(pageSize); //获取当前页码 currentPage=(cupage==null)?1:Integer.parseInt(cupage); //如果当前页码小于1，则赋值为1，表示为首页 if(currentPage<1) { currentPage=1; } //如果当前页数大于总页数，则赋值为总页数值，表示为尾页 if(currentPage>pages) { currentPage=pages; } //将当前的页码保存在session中 request.getSession().setAttribute("currentPage", currentPage); //将总页数保存在session中 request.getSession().setAttribute("pages", pages); //准备分页的user数据 List<UserInfo> userInfo=service.getUsersPages(currentPage, pageSize); //将user放入到session中 request.getSession().setAttribute("userInfo", userInfo); //跳转到userman.jsp页面 response.sendRedirect("admin/userman.jsp"); } }

通过前面的准备，现在在前段页面JSP中接收页码

<div id="page" class="page_div"> <a href="/BookSystem/UserServlet?action=showusers&currentPage=1" id="firstpage">首页</a> <a href="/BookSystem/UserServlet?action=showusers&currentPage=${currentPage-1 }" id="prevpage">上一页</a> <span>${currentPage }/${pages }</span> <a href="/BookSystem/UserServlet?action=showusers&currentPage=${currentPage+1 }" id="nextpage">下一页</a> <a href="/BookSystem/UserServlet?action=showusers&currentPage=${pages }" id="lastpage">末页</a> </div>