JSP连接数据库分页(首页,上一页,下一页,尾页)

时间:2022-03-30 17:12:42

 

 

JSP连接数据库分页(首页,上一页,下一页,尾页)

 

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 }

JSP连接数据库分页(首页,上一页,下一页,尾页)