如何用SQL语句控制结果集的数目？

nuanshuidai 2003-08-19 12:38:40

目的是想分页显示数据，而数据不是一次的取出，先取出一页，转到下一页时再取后面的记录！
该如何做呢？！

...全文

40 2 打赏收藏转发到动态举报

写回复

用AI写文章

2 条回复

切换为时间正序

请发表友善的回复…

发表回复

shaokun305 2003-08-19

打赏
举报

<%@ page language="java" import="java.sql.*" %>
<%@ page contentType="text/html;charset=gb2312"%>
<%@ page import="java.lang.Math"%>
<jsp:useBean id="rencai2" scope="page" class="rencaixuqiu.database.rencai"/>
<%@ page errorPage="error.jsp"%>
<%!
String getStr(String str)
{
try
{
String temp=new String(str.getBytes("ISO8859_1"));
return temp;
}
catch(Exception e)
{
return null;
}
}
%>
<%
int quanxian=Integer.parseInt((String)session.getValue("quanxian"));
String bumen=(String)session.getValue("department");
%>
<%
String offset=null; //从URL中获得页面读取数据库记录的偏移量
int count=0; //总共的记录数
int off=0; //本页面的偏移量
int page_cnt; //总共的页面总数
int pre_offset,page_offset,back_offset;
String id=null;
String sql=null;
String msg=null;//得到返回的没有选择记录的提示消息，
msg=getStr(request.getParameter("msg"));
if(msg==null)
msg="";

rencai2.connect();

ResultSet RS=null;
sql="select count(*)as count from nianzhongkaohebiao";
try
{
RS=rencai2.executeQuery(sql);
RS.next();
count=Integer.parseInt(RS.getString("count"));
}
catch(SQLException e)
{
throw new SQLException("数据库读取错误");
}
//从URL中读取offset参数
offset=request.getParameter("offset");
if(offset==null)
off=0;
else
off=Integer.parseInt(offset);

%>
<html>

<HEAD><TITLE>北京科锐星办公自动化信息系统</TITLE>
<META http-equiv=Content-Type content="text/html; charset=gb2312">
</HEAD>
<BODY bgColor=#ffffff leftMargin=2 topMargin=2 marginwidth="2" marginheight="2">
<SCRIPT language=JavaScript>

</SCRIPT>

<%@ include file="head.jsp" %>

<TABLE cellSpacing=0 cellPadding=0 width=773 border=0>
<TBODY>
<TR>
<TD vAlign=top width=155>

<%@ include file="daohang.jsp" %>

</TD>
<TD width=10 height="467"> </TD>
<TD vAlign=top align=middle width=815 height="467">
<TABLE cellSpacing=0 cellPadding=0 width="810" border=0 height="411">
<TBODY>
<TR>
<TD height=14 width="879"></TD></TR>
<TR>
<TD height="25" width="879"><font color="#FF0000" size="5" face="楷体_GB2312"><b>人事管理</b></font></TD></TR>
<TR>
<TD background=dot6.gif height=14 width="879">
<hr style="border-style: dotted" color="#000080">
</TD></TR>
<TR>
<TD height=30 width="879"><FONT color=#828383>首页>人事管理>
</FONT><font color="#828383">公司培训信息></font><font color="#828383">年度年终考核表</font></TD></TR>
<TR>
<TD height="20" width="879">
<b><%=msg%></TD>
</TR>
<TR>
<TD height="25" width="879">
<P align="center"><b><font face="楷体_GB2312" size="5" color="#808000">公司员工年度年终考核表</font></b></P></TD>
</TR>

<tr>
<TD height="317" width="879">
<form method="POST" action="nzkh_control.jsp">


<p align="center">

<%
if((quanxian>40)||((quanxian>20)&&(bumen.equals("综合管理部")))){%>
<input type="submit" name="add" value=" 填写 ">
<input type="submit" name="delete" value=" 删除 ">
<%}%>
</p>

<div align="left">
<table border="0" width="882" height="33" bgcolor="#338899">
<tr>
<td width="146" height="25" align="center">
<font color="#FFFFFF" face="楷体_GB2312"><b>序号</b></font>
</td>
<td width="146" height="25" align="center">
<font color="#FFFFFF" face="楷体_GB2312"><b>姓名</b></font>
</td>
<td width="147" height="25" align="center">
<font color="#FFFFFF" face="楷体_GB2312"><b>部门</b></font>
</td>
<td width="147" height="25" align="center">
<font color="#FFFFFF" face="楷体_GB2312"><b>职务</b></font>
</td>
<td width="147" height="25" align="center">
<font color="#FFFFFF" face="楷体_GB2312"><b>考核时间</b></font>
</td>

<td width="147" height="25" align="center">
<font color="#FFFFFF" face="楷体_GB2312"><b>详细信息</b></font>
</td>

</tr>
<%
sql="SELECT * FROM nianzhongkaohebiao order by bianhao DESC LIMIT "+off+",10";

RS=rencai2.executeQuery(sql);

while(RS.next())
{ id=RS.getString("bianhao");
%>
<tr>
<td align="center" width="146">
<input type="checkbox" name="pkeys" value=<%=id%>>
</td>
<td align="center" width="146"><%=RS.getString("xingming")%></td>
<td align="center" width="147"><%=RS.getString("bumen")%></td>
<td align="center" width="147"><%=RS.getString("zhiwu")%></td>
<td align="center" width="142"><%=RS.getString("kaoheshijian")%></td>
<td align="center" width="142"><a href="nzkh_view_xx.jsp?id=<%=id%>"/>详细信息</td>
</tr>

<%}rencai2.disconnect();%>

</table>
<p>
<p>

<center>
<%
//使用分页技术，先判断是否是第一页，如是，则不显示上一页的连接
if(off!=0)
{
pre_offset=off-10;
out.println("<a href=\"nzkh_view.jsp?offset="+pre_offset+"\">上一页</a> \n");
}
page_cnt=(int)Math.ceil((double)count/10);
for(int i=1;i<=page_cnt;i++)
{
page_offset=10*i-10;
if(page_offset==off)
out.println(i+"\n");
else
out.println("<a href=\"nzkh_view.jsp?offset="+page_offset+"\">"+i+"</a>\n");
}
//判断是否是最后一页，如是，则不显示下一页的连接
if(page_cnt!=0 &&((off+10)/10)!=page_cnt)
{
back_offset=off+10;
out.println("<a href=\"nzkh_view.jsp?offset="+back_offset+"\">下一页</a> \n");
}
%><font color="#ff00bb" face="楷体_GB2312"><b>共有：<%=count%>条记录。</b></font>
</center>
</div>
</form> </TD>
</tr>
<tr>
<TD height="12" width="879">
</TD>
</tr>
<tr>
<TD height="14" width="879">
</TD>
</tr>
</TBODY></TABLE></TD></TR></TBODY></TABLE>
<%@ include file="copyright.jsp"%>