JSP连接数据库分页（首页，上一页，下一页，尾页）

深藏阁楼爱情的钟 2021-09-29 11:22 834阅读 0赞

![1274498-20180305102321258-2092234864.png][]

![1274498-20180305102548874-1379589416.png][]

**分页实例：**

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     
     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     }

![1274498-20180305103504808-1506748209.png][]

转载于:https://www.cnblogs.com/ztt0918/p/8507695.html

[1274498-20180305102321258-2092234864.png]: /images/20210725/604492b81ea04e3ab59973a0ac4ecdd3.png
[1274498-20180305102548874-1379589416.png]: /images/20210725/fd156ec400794610afaf1cf81f34a0cd.png
[1274498-20180305103504808-1506748209.png]: /images/20210725/56119911a49b4b278f7e7e0a41eab04d.png