Java Web—数据库分页操作

系统管理员 2022-07-14 02:09 224阅读 0赞

pages.jsp

<%@ page language="java" import="java.util.*,java.sql.*"
    	pageEncoding="gb2312"%>
    <%@include file="pagescount.jsp"%>
    
    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
    <html>
    <head>
    <base href="<%=basePath%>">
    
    <title>分页显示</title>
    </head>
    
    <body bgcolor="cce8cf">
    	<center>
    		<%
    			String ToPage = request.getParameter("ToPage");
    			//判断是否取得ToPage参数
    			if (ToPage != null) {
    				//取得指定显示的分页页数
    				ShowPage = Integer.parseInt(ToPage);
    
    				//下面的语句判断用户输入的页数是否正确
    				if (ShowPage >= PageCount) {
    					ShowPage = PageCount;
    				} else if (ShowPage <= 0) {
    					ShowPage = 1;
    				}
    			}
    			//计算欲显示页的第一笔记录位置
    			rs.absolute((ShowPage - 1) * PageSize + 1);
    		%>
    		<h3>
    			当前在第 <font size="4" color="red"><%=ShowPage%></font>页,共<%=PageCount%>页
    		</h3>
    		<p>
    			<%
    				//利用for循环配合PageSize属性取出一页中的数据
    				for (int i = 1; i <= PageSize; i++) {
    			%>
    		
    		<table border="1" width="90%">
    			<tr>
    				<td>书名ID</td>
    				<td><%=rs.getString("pub_id")%></td>
    
    				<td>书名</td>
    				<td><%=rs.getString("title")%></td>
    
    				<td>类型</td>
    				<td><%=rs.getString("type")%></td>
    
    				<td>价格</td>
    				<td><%=rs.getFloat("price")%></td>
    			</tr>
    			<tr>
    				<td>备注</td>
    				<td colspan="7"><%=rs.getString("notes")%></td>
    			</tr>
    		</table>
    		</p>
    
    			<%
    				//下面的语句用于输出最后一条记录时,将指针移到最后一笔记录之后
    					if (!rs.next()) {
    						//跳出for循环
    						break;
    					}
    				}
    			%>
    		
    		<table>
    			<tr>
    				<%
    					//判断当前是否在第一页,不是第一页，则显示到第一页与下一页的连接
    					if (ShowPage != 1) {
    				%>
    				<td width=150><a href="pages.jsp?ToPage=<%=1%>">第一页</a></td>
    				<td width=150><a href="pages.jsp?ToPage=<%=ShowPage - 1%>">上一页</a>
    				</td>
    				<%
    					}
    					//判断是否在最后一页，是，则显示到最后一页和下一页
    					if (ShowPage != PageCount) {
    				%>
    				<td width=150><a href="pages.jsp?ToPage=<%=ShowPage + 1%>">下一页</a>
    				</td>
    				<td width=150><a href="pages.jsp?ToPage=<%=PageCount%>">最后一页</a>
    				</td>
    				<%
    					}
    				%>
    			</tr>
    			<tr>
    				<td colspan="4" align="center">
    					<form action="pages.jsp" method="post" name="form1">
    						<input type="text" name="ToPage" value="<%=ShowPage%>"
    							style="height: 25px; width: 40px"> 页 <a
    							href="javascript:window.document.form1.submit();">GO</a>
    					</form></td>
    			</tr>
    		</table>
    	</center>
    	<script language="javascript">
    		function go() {
    			window.document.form1.submit();
    		}
    	</script>
    </body>
    </html>

pagescount.jsp

<%@ page language="java" import="java.util.*,java.sql.*"
    	pageEncoding="gb2312"%>
    <%
    	String path = request.getContextPath();
    	String basePath = request.getScheme() + "://"
    			+ request.getServerName() + ":" + request.getServerPort()
    			+ path + "/";
    %>
    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
    <html>
    <head>
    <base href="<%=basePath%>">
    <title>pagescount</title>
    </head>
    
    <body>
    	<%!//设置每张网页显示三笔记录(每页显示的记录数)
    	int PageSize = 3;
    	//设置欲显示的页数(初始页)
    	int ShowPage = 1;
    	//ResultSet的记录笔数(总记录数)
    	int RowCount = 0;
    	//ResultSet分页后的总数(总页数)
    	int PageCount = 0;
    	Connection con = null;
    	Statement stmt = null;
    	ResultSet rs = null;%>
    	<%!//连接数据库并初始数据
    	public void jspInit() {
    		try {
    			Class.forName("com.mysql.jdbc.Driver").newInstance();// 加载mysql驱动程序
    			String url = "jdbc:mysql://localhost:3306/user?user=root&password=root";
    			System.out.println("连接成功");
    			con = DriverManager.getConnection(url, "root", "root");// 获取连接
    			stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
    					ResultSet.CONCUR_READ_ONLY);
    			rs = stmt.executeQuery("select * from book");
    			//将指标移至最后一条记录
    			rs.last();
    			//获取记录总数
    			RowCount = rs.getRow();
    
    			//计算显示的页数(关键)
    			PageCount = ((RowCount % PageSize) == 0 ? (RowCount / PageSize)
    					: (RowCount / PageSize) + 1);
    		} catch (Exception e) {
    			System.out.println(e.getMessage());
    		}
    	}%>
    	<%!public void jspDestroy() {
    		//执行关闭各个对象的操作
    		try {
    			rs.close();
    			stmt.close();
    			con.close();
    			System.out.println("数据库已关闭");
    		} catch (Exception e) {
    			System.out.println(e.getMessage());
    		}
    	}%>
    </body>
    </html>

数据库：

CREATE  TABLE `user`.`book` (  
    `pub_id` INT NOT NULL ,  
    `title` VARCHAR(15) NULL ,  
    `type` VARCHAR(15) NULL ,
     `price` FLOAT NULL ,  
    `notes` VARCHAR(45) NULL ,
     PRIMARY KEY (`pub_id`) );
    INSERT INTO `user`.`book` (`pub_id`, `title`, `type`, `price`, `notes`) VALUES (01, '高等数学', '数学', 23.0, '高等数学出版社');
    INSERT INTO `user`.`book` (`pub_id`, `title`, `type`, `price`, `notes`) VALUES (02, '数据库', '专业课', 39.8, '高等教育出版社');
    INSERT INTO `user`.`book` (`pub_id`, `title`, `type`, `price`, `notes`) VALUES (03, 'Java Web', '专业', 99.9, '清华大学出版社');
    INSERT INTO `user`.`book` (`pub_id`, `title`, `type`, `price`, `notes`) VALUES (04, 'Android', '专业', 78.0, '电子工业出版社');
    INSERT INTO `user`.`book` (`pub_id`, `title`, `type`, `price`, `notes`) VALUES (05, '软件工程', '专业', 34.0, '高等教育出版社');
    INSERT INTO `user`.`book` (`pub_id`, `title`, `type`, `price`, `notes`) VALUES (06, '组合数学', '数学', 32.5, '机械工业出版社');
    INSERT INTO `user`.`book` (`pub_id`, `title`, `type`, `price`, `notes`) VALUES (07, '计算机英语', '英语', 28.9, '机械工业出版社');

![Center][]

运行结果：

![Center 1][]

[Center]: /images/20220714/c76d618d91c143219db850cfb7c5a366.png
[Center 1]: /images/20220714/725a704af95c4b7fa672670cee2be5e7.png