基于MySQL的真分页和假分页,使用JavaScript实现

本文探讨了如何使用MySQL的分页语句实现真分页,并介绍了利用JavaScript进行基于算法的假分页方法,旨在优化数据库查询效率和用户体验。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

真分页(使用分页语句):

<%@ page contentType="text/html" pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<html>
<head><title>分页操作</title>
<style type="text/css">
<!--
.STYLE6 {font-size: 12px}
.STYLE10 {font-size: 14px; font-weight: bold; }
-->
</style>
</head>
	<script language="javascript">
		function changeColor(obj,color){
			obj.bgColor = color ;
		}
	</script>
<body>
<%!
	public static final String URL = "emp_list_true.jsp" ;
%>
<%! //全局变量
	public static final String DBDRIVER = "com.mysql.cj.jdbc.Driver" ;
	public static final String DBURL = "jdbc:mysql://localhost:3306/test?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true" ;
	public static final String DBUSER = "root" ;
	public static final String DBPASSWORD = "123456" ;
%>
<%
	Connection conn = null ;
	PreparedStatement pstmt = null ;
	ResultSet rs = null ;
%>
<%
	int currentPage = 1 ;	// 为当前所在的页,默认在第1页
	int lineSize = 3 ;		// 每次显示的记录数
	int allRecorders = 0 ;	// 表示全部的记录数
	int pageSize = 1 ;		// 表示全部的页数(尾页)
	int lsData[] = {1,3,5,7,9,10,15,20,25,30,50,100} ;
%>
<%
	try{
		currentPage = Integer.parseInt(request.getParameter("cp")) ;
	} catch(Exception e) {}
	try{
		lineSize = Integer.parseInt(request.getParameter("ls")) ;
	} catch(Exception e) {}
%>
<%
	Class.forName(DBDRIVER) ;
	conn = DriverManager.getConnection(DBURL,DBUSER,DBPASSWORD) ;
	String sql = "SELECT COUNT(empno) FROM emp" ;
	pstmt = conn.prepareStatement(sql) ;
	rs = pstmt.executeQuery() ;
	if(rs.next()){	// 取得全部的记录数
		allRecorders = rs.getInt(1) ;
	}
%>
<center> 
	<h1>雇员列表</h1>
<script language="javascript">
	function go(num){
		document.getElementById("cp").value = num ;
		document.spform.submit() ;	// 表单提交
	}
</script>
<%	//总页数计算
	pageSize = (allRecorders + lineSize -1) / lineSize ;
%>
<%	//分页查询语句
	sql =" SELECT empno,ename,job,hiredate,sal,comm " +
			" FROM emp ORDER BY empno limit ?,?" ;
	pstmt = conn.prepareStatement(sql) ;
	pstmt.setInt(1,(currentPage-1) * lineSize) ;
	pstmt.setInt(2,lineSize) ;
	rs = pstmt.executeQuery() ;
%>

<form name="spform" action="<%=URL%>" method="post">
	<input type="button" value="首页" onclick="go(1)" <%=currentPage==1?"DISABLED":""%>>
	<input type="button" value="上一页" onclick="go(<%=currentPage-1%>)" <%=currentPage==1?"DISABLED":""%>>
	<input type="button" value="下一页" onclick="go(<%=currentPage+1%>)" <%=currentPage==pageSize?"DISABLED":""%>>
	<input type="button" value="尾页" onclick="go(<%=pageSize%>)" <%=currentPage==pageSize?"DISABLED":""%>>
	跳转到第<select name="selcp" onchange="go(this.value)">
		<%
			for(int x=1;x<=pageSize;x++){
		%>
				<option value="<%=x%>" <%=x==currentPage?"SELECTED":""%>><%=x%></option>
		<%
			}
		%>
	</select>页
	每页显示
		<select name="ls" onchange="go(1)">
		<%
			for(int x=0;x<lsData.length;x++){
		%>
			<option value="<%=lsData[x]%>" <%=lsData[x]==lineSize?"SELECTED":""%>><%=lsData[x]%></option>
		<%
			}
		%>
		</select>
	条
	<input type="hidden" id="cp" name="cp" value="1">
</form>
<TABLE BORDER="1" cellpadding="5" cellspacing="0" bgcolor="F2F2F2" width="100%">
	<TR onMouseOver="changeColor(this,'white')" onMouseOut="changeColor(this,'F2F2F2')">
		<td align="center" valign="middle"><span class="STYLE10">编号</span></td>
		<td align="center" valign="middle"><span class="STYLE10">姓名</span></td>
		<td align="center" valign="middle"><span class="STYLE10">职位</span></td>
		<td align="center" valign="middle"><span class="STYLE10">雇佣日期</span></td>
		<td align="center" valign="middle"><span class="STYLE10">工资</span></td>
		<td align="center" valign="middle"><span class="STYLE10">奖金</span></td>
	</TR>
<%
	while(rs.next()){
		int empno = rs.getInt(1) ;
		String ename = rs.getString(2) ;
		String job = rs.getString(3) ;
		Date hiredate = rs.getDate(4) ;
		double sal = rs.getDouble(5) ;
		double comm = rs.getDouble(6) ;
%>
	<TR onMouseOver="changeColor(this,'white')" onMouseOut="changeColor(this,'F2F2F2')">
		<td align="center" valign="middle"><span class="STYLE6"><%=empno%></span></td>
		<td align="center" valign="middle"><span class="STYLE6"><%=ename%></span></td>
		<td align="center" valign="middle"><span class="STYLE6"><%=job%></span></td>
		<td align="center" valign="middle"><span class="STYLE6"><%=hiredate%></span></td>
		<td align="center" valign="middle"><span class="STYLE6"><%=sal%></span></td>
		<td align="center" valign="middle"><span class="STYLE6"><%=comm%></span></td>
	</TR>
<%
	}
%>
</table>
<%
	conn.close() ;
%>
</center>
</body>
</html>

假分页(基于算法的分页):

<%@ page contentType="text/html" pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<html>
<head><title>分页操作(假分页)</title>
<style type="text/css">
<!--
.STYLE6 {font-size: 12px}
.STYLE10 {font-size: 14px; font-weight: bold; }
-->
</style>
</head>
	<script language="javascript">
		function changeColor(obj,color){
			obj.bgColor = color ;
		}
	</script>
<body>
<%!
	public static final String URL = "emp_list_false.jsp" ;
%>
<%!
	public static final String DBDRIVER = "com.mysql.cj.jdbc.Driver" ;
	public static final String DBURL = "jdbc:mysql://localhost:3306/test?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true" ;
	public static final String DBUSER = "root" ;
	public static final String DBPASSWORD = "123456" ;
%>
<%
	Connection conn = null ;
	PreparedStatement pstmt = null ;
	ResultSet rs = null ;
%>
<%
	int currentPage = 1 ;	// 为当前所在的页,默认在第1页
	int lineSize = 3 ;		// 每次显示的记录数
	int allRecorders = 0 ;	// 表示全部的记录数
	int pageSize = 1 ;		// 表示全部的页数(尾页)
	int lsData[] = {1,3,5,7,9,10,15,20,25,30,50,100} ;
%>
<%
	try{
		currentPage = Integer.parseInt(request.getParameter("cp")) ;
	} catch(Exception e) {}
	try{
		lineSize = Integer.parseInt(request.getParameter("ls")) ;
	} catch(Exception e) {}
%>
<%
	Class.forName(DBDRIVER) ;
	conn = DriverManager.getConnection(DBURL,DBUSER,DBPASSWORD) ;
	String sql = "SELECT COUNT(empno) FROM emp" ;
	pstmt = conn.prepareStatement(sql) ;
	rs = pstmt.executeQuery() ;
	if(rs.next()){	// 取得全部的记录数
		allRecorders = rs.getInt(1) ;
	}
%>
<center> 
	<h1>雇员列表</h1>
<script language="javascript">
	function go(num){
		document.getElementById("cp").value = num ;
		document.spform.submit() ;	// 表单提交
	}
</script>
<%
	pageSize = (allRecorders + lineSize -1) / lineSize ;
%>
<%
	sql = "SELECT empno,ename,job,hiredate,sal,comm FROM emp ORDER BY empno" ;
	pstmt = conn.prepareStatement(sql) ;
	rs = pstmt.executeQuery() ;
%>
<form name="spform" action="<%=URL%>" method="post">
	<input type="button" value="首页" onclick="go(1)" <%=currentPage==1?"DISABLED":""%>>
	<input type="button" value="上一页" onclick="go(<%=currentPage-1%>)" <%=currentPage==1?"DISABLED":""%>>
	<input type="button" value="下一页" onclick="go(<%=currentPage+1%>)" <%=currentPage==pageSize?"DISABLED":""%>>
	<input type="button" value="尾页" onclick="go(<%=pageSize%>)" <%=currentPage==pageSize?"DISABLED":""%>>
	跳转到第<select name="selcp" onchange="go(this.value)">
		<%
			for(int x=1;x<=pageSize;x++){
		%>
				<option value="<%=x%>" <%=x==currentPage?"SELECTED":""%>><%=x%></option>
		<%
			}
		%>
	</select>页
	每页显示
		<select name="ls" onchange="go(1)">
		<%
			for(int x=0;x<lsData.length;x++){
		%>
			<option value="<%=lsData[x]%>" <%=lsData[x]==lineSize?"SELECTED":""%>><%=lsData[x]%></option>
		<%
			}
		%>
		</select>
	条
	<input type="hidden" id="cp" name="cp" value="1">
</form>
<TABLE BORDER="1" cellpadding="5" cellspacing="0" bgcolor="F2F2F2" width="100%">
	<TR onMouseOver="changeColor(this,'white')" onMouseOut="changeColor(this,'F2F2F2')">
		<td align="center" valign="middle"><span class="STYLE10">编号</span></td>
		<td align="center" valign="middle"><span class="STYLE10">姓名</span></td>
		<td align="center" valign="middle"><span class="STYLE10">职位</span></td>
		<td align="center" valign="middle"><span class="STYLE10">雇佣日期</span></td>
		<td align="center" valign="middle"><span class="STYLE10">工资</span></td>
		<td align="center" valign="middle"><span class="STYLE10">奖金</span></td>
	</TR>
<%
for(int x=0;x<(currentPage-1)*lineSize;x++){
	rs.next() ;
}
for(int x=0;x<lineSize;x++){
	if(rs.next()){
		int empno = rs.getInt(1) ;
		String ename = rs.getString(2) ;
		String job = rs.getString(3) ;
		Date hiredate = rs.getDate(4) ;
		double sal = rs.getDouble(5) ;
		double comm = rs.getDouble(6) ;
%>
	<TR onMouseOver="changeColor(this,'white')" onMouseOut="changeColor(this,'F2F2F2')">
		<td align="center" valign="middle"><span class="STYLE6"><%=empno%></span></td>
		<td align="center" valign="middle"><span class="STYLE6"><%=ename%></span></td>
		<td align="center" valign="middle"><span class="STYLE6"><%=job%></span></td>
		<td align="center" valign="middle"><span class="STYLE6"><%=hiredate%></span></td>
		<td align="center" valign="middle"><span class="STYLE6"><%=sal%></span></td>
		<td align="center" valign="middle"><span class="STYLE6"><%=comm%></span></td>
	</TR>
<%
	}
}
%>
</table>
<%
	conn.close() ;
%>
</center>
</body>
</html>

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值