JSP连接数据库分页（首页，上一页，下一页，尾页）
分页实例：
1 <%@page import="com.jr.messageboard.model.User"%> 2 <%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%> 3 <% 4 String path = request.getContextPath(); 5 String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/"; 6 %> 7 <% 8 String name; 9 @SuppressWarnings("unchecked") 10 List<User> list=(List<User>)request.getAttribute("users");//获取后台传过来的用户的list集合 11 /* request.setAttribute("users", list); 12 request.getRequestDispatcher("/messageBoard/addMessage.jsp").forward(request, response); */ 13 14 %> 15 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> 16 <html> 17 <head> 18 <base href="<%=basePath%>"> 19 <title>My JSP 'userlist.jsp' starting page</title> 20 21 <meta http-equiv="pragma" content="no-cache"> 22 <meta http-equiv="cache-control" content="no-cache"> 23 <meta http-equiv="expires" content="0"> 24 <meta http-equiv="keywords" content="keyword1,keyword2,keyword3"> 25 <meta http-equiv="description" content="This is my page"> 26 <!-- 27 <link rel="stylesheet" type="text/css" href="styles.css"> 28 --> 29 <meta charset="UTF-8"> 30 <script src="JQuery/jquery.min.js"></script> 31 <script src="JQuery/jquery.easyui.min.js"></script> 32 <link rel="stylesheet" href="EasyUI/themes/default/easyui.css"/> 33 <link rel="stylesheet" href="EasyUI/themes/icon.css"/> 34 <script src="JQuery/easyui-lang-zh_CN.js"></script> 35 <script type="text/javascript" src="EasyUI/datagrid-detailview.js"></script> 36 <script type="text/javascript" src="EasyUI/datagrid-bufferview.js"></script> 37 </head> 38 <style> 39 #div{ 40 width: 1530px; 41 height: 765px; 42 background-image: url("img/message.jpg"); 43 background-size: cover; 44 } 45 *{ 46 margin: 0; 47 padding:0; 48 } 49 </style> 50 <body> 51 <% 52 //获取当前页面 53 int pageNum=Integer.valueOf((String)request.getAttribute("pageNum")); 54 //获取单个页面显示的个数 55 int pageSize=Integer.valueOf((String)request.getAttribute("pageSize")); 56 //获取总个数 57 int totalSize=(Integer)request.getAttribute("totalSize"); 58 //获取总页面数 59 int totalPage=totalSize%pageSize==0?totalSize/pageSize:totalSize/pageSize+1; 60 //上一页 61 int front=pageNum==1?1:pageNum-1; 62 //下一页 63 int next=pageNum==totalPage?totalPage:pageNum+1; 64 %> 65 <div id="div"> 66 <div style="margin-left: 600px;padding-top: 100px"> 67 <% 68 name=(String) request.getAttribute("uname"); 69 for(int i=0;i<list.size();i++){ 70 User user=list.get(i); 71 72 %> 73 <!--开始循环的部分 --> 74 <div style="margin-top: 10px"></div> 75 76 <div class="easyui-panel" title="分享喜悦，留住感动 " 77 style="width:500px;height:130px;padding:10px;background:plum;border-radius: 0px 0px 10px 10px "; 78 data-options="closable:true, collapsible:true,minimizable:true,maximizable:true" 79 > 80 <div style="width: 130px;height: 80px;background-color:red;float: left; background-image: url('messageBoard/img/<%=user.getUrl() %>');background-size: cover"></div> 81 <div style="float: left;height: 60px;margin-left: 5px"> 82 <div style="height: 20%;font-size: 14px;color: white">昵称：<%=user.getName() %></div> 83 <div style="height: 65%;font-size: 12px;margin-top: 9px;color: white"><%=user.getContent() %></div> 84 </div> 85 86 </div> 87 <% if(name.equals(user.getName())){ 88 %> 89 <a href="deleteMessage?name=<%=user.getName() %>&content=<%=user.getContent() %>& 90 pageNum=<%=front %>&pageSize=<%=pageSize %>&name=<%=(String)request.getAttribute("uname") %>&url=<%= (String)request.getAttribute("url")%>">删除</a> 91 <% 92 } 93 %> 94 95 96 <% } %> 97 98 <br> 99 100 101 <a style="margin-left: 120px" href="userList?pageNum=1&pageSize=<%=pageSize %>&name=<%=(String)request.getAttribute("uname") %>">首页</a> 102 <a style="margin-left: 30px" href="userList?pageNum=<%=front %>&pageSize=<%=pageSize %>&name=<%=(String)request.getAttribute("uname") %>">上一页</a> 103 <a style="margin-left: 30px" href="userList?pageNum=<%=next %>&pageSize=<%=pageSize %>&name=<%=(String)request.getAttribute("uname") %>">下一页</a> 104 <a style="margin-left: 30px" href="userList?pageNum=<%=totalPage %>&pageSize=<%=pageSize %>&name=<%=(String)request.getAttribute("uname") %>">尾页</a> 105 106 <br> 107 108 <a href="messageBoard/addMessage.jsp?name=<%=(String)request.getAttribute("uname") %>&url=<%= (String)request.getAttribute("url")%>">快来添加你的留言吧</a> 109 110 </div> 111 </div> 112 113 </body> 114 </html>
1 package com.jr.messageboard.servlet; 2 3 import java.io.IOException; 4 import java.sql.Connection; 5 import java.sql.DriverManager; 6 import java.sql.PreparedStatement; 7 import java.sql.ResultSet; 8 import java.util.ArrayList; 9 import java.util.List; 10 11 import javax.servlet.ServletException; 12 import javax.servlet.http.HttpServlet; 13 import javax.servlet.http.HttpServletRequest; 14 import javax.servlet.http.HttpServletResponse; 15 16 import com.jr.messageboard.model.User; 17 18 public class UserList extends HttpServlet { 19 private static final long serialVersionUID = 1L; 20 String name=null; 21 String url = null; 22 23 protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { 24 // TODO Auto-generated method stub 25 doPost(request, response); 26 } 27 28 protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { 29 // TODO Auto-generated method stub 30 String pageSize=request.getParameter("pageSize"); 31 if(pageSize==null||"".equals(pageSize)){ 32 pageSize=(String) request.getAttribute("pageSize"); 33 if(pageSize==null||"".equals(pageSize)){ 34 pageSize="3"; 35 } 36 37 } 38 request.setAttribute("pageSize", pageSize); 39 40 String pageNum=request.getParameter("pageNum"); 41 if(pageNum==null||"".equals(pageNum)){ 42 pageNum=(String) request.getAttribute("pageNum"); 43 if(pageNum==null||"".equals(pageNum)){ 44 pageNum="1"; 45 } 46 47 } 48 request.setAttribute("pageNum", pageNum); 49 50 51 52 name=(String) request.getAttribute("uname"); 53 if(name==null||"".equals(name)){ 54 name=request.getParameter("name"); 55 } 56 request.setAttribute("uname", name); 57 58 List<User> list=getAllUsers(Integer.valueOf(pageSize),Integer.valueOf(pageNum)); 59 request.setAttribute("users", list); 60 61 int totalSize=getTotalSize(); 62 request.setAttribute("totalSize", totalSize); 63 64 if(url==null||"".equals(url)){ 65 url=(String) request.getAttribute("url"); 66 } 67 68 request.setAttribute("url", url); 69 request.getRequestDispatcher("/messageBoard/userlist.jsp").forward(request, response); 70 } 71 72 public int getTotalSize(){ 73 int count =0; 74 try { 75 //1.加载驱动 76 Class.forName("oracle.jdbc.driver.OracleDriver"); 77 //2.连接数据库 78 Connection conn= DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl","message","tiger"); 79 if(conn==null){ 80 System.out.println("连接失败"); 81 return 0; 82 } 83 //3.通过连接获取句柄 84 String sql="select count(*) num from content"; 85 86 PreparedStatement pstmt = conn.prepareStatement(sql) ; 87 //获取结果集 88 ResultSet res=pstmt.executeQuery(); 89 90 while(res.next()){ 91 count=res.getInt("num"); 92 System.out.println(count); 93 94 } 95 96 //5.关闭连接 97 res.close(); 98 pstmt.close(); 99 conn.close(); 100 101 102 } catch (Exception e) { 103 // TODO Auto-generated catch block 104 e.printStackTrace(); 105 } 106 return count; 107 } 108 109 110 public List<User> getAllUsers(int pageSize,int pageNum){ 111 112 int start=(pageNum-1)*pageSize; 113 int end=pageNum*pageSize; 114 List<User> list=new ArrayList<User>(); 115 116 try { 117 //1.加载驱动 118 Class.forName("oracle.jdbc.driver.OracleDriver"); 119 //2.连接数据库 120 Connection conn= DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl","message","tiger"); 121 if(conn==null){ 122 System.out.println("连接失败"); 123 return list; 124 } 125 //3.通过连接获取句柄 126 String sql="select * from (select rownum r,u.* from content u) where r<=? and r>?";//数据库分页的重要语句 127 128 PreparedStatement pstmt = conn.prepareStatement(sql) ; 129 pstmt.setInt(1,end); 130 pstmt.setInt(2,start); 131 132 //获取结果集 133 ResultSet res=pstmt.executeQuery(); 134 while(res.next()){ 135 String username=res.getString("uname"); 136 String content=res.getString("content"); 137 String url2=res.getString("url"); 138 if(username.equals(name)){ 139 url=url2; 140 }else{ 141 url=""; 142 } 143 if(url==null||"".equals(url)){ 144 String sql2="select url from t_user where uname=?"; 145 PreparedStatement pstmt2 = conn.prepareStatement(sql2) ; 146 pstmt2.setString(1,name); 147 ResultSet res2=pstmt2.executeQuery(); 148 while(res2.next()){ 149 url=res2.getString("url"); 150 System.out.println(url); 151 } 152 153 } 154 User user=new User(username, content,url2); 155 list.add(user); 156 157 } 158 159 160 //5.关闭连接 161 res.close(); 162 pstmt.close(); 163 conn.close(); 164 165 166 } catch (Exception e) { 167 // TODO Auto-generated catch block 168 e.printStackTrace(); 169 } 170 return list; 171 } 172 173 }
