1、 sybase的分页存储过程,这个是从网上搜的
CREATE PROCEDURE GetDataByPage
(
--创建一个分页读取过程
@SqlStr varchar(8000), --SQL语句
@PageSize int, --每页记录数
@CurrentPage int --当前页数
)
AS
DECLARE @FirstRec int, @LastRec int, @dt varchar(10) --页起始行,页结束行,生成临时表的随机数
BEGIN
SELECT @FirstRec = (@CurrentPage - 1) * @PageSize --计算页起始行
SELECT @LastRec = (@CurrentPage * @PageSize + 1) --计算页结束行
SELECT @dt= substring(convert(varchar,rand()),3,10) --一个字符型的随机数
--将搜索结果放入临时表中,表名随机生成,在' FROM '前插入'INTO '+随机临时表名
SELECT @SqlStr = stuff(@SqlStr, charindex(' FROM ',upper(@SqlStr)), 6 ,' INTO tempdb..Paging'+@dt+' FROM ')
EXECUTE (@SqlStr)
--为临时表增加id号
SELECT @SqlStr = 'ALTER TABLE tempdb..Paging'+@dt+' ADD TEMPDB_ID numeric(10) IDENTITY PRIMARY KEY'
EXECUTE (@SqlStr)
--计算临时表中的记录数
--SELECT @SqlStr = 'SELECT Count(*) From tempdb..Paging'+@dt
--EXECUTE (@SqlStr)
--选取记录号在起始行和结束行中间的记录
SELECT @SqlStr = 'SELECT * FROM tempdb..Paging' + @dt + ' WHERE TEMPDB_ID > ' + convert(varchar,@FirstRec) + ' and TEMPDB_ID < '+convert(varchar,@LastRec)
EXECUTE (@SqlStr)
--删除临时表
SELECT @SqlStr = 'DROP TABLE tempdb..Paging'+@dt
EXECUTE (@SqlStr)
END
直接在命令行执行即可。
2、action类,
public ActionForward perform(ActionMapping mapping,ActionForm form,HttpServletRequest request,HttpServletResponse response)
throws IOException, ServletException {
SelectForm selectForm = (SelectForm)form;
String start1 = request.getParameter("start");
String maxValue1 = request.getParameter("limit");
int start = 0;
int maxValue = 0;
int totalCount = 0;
if (validateNumber(start1)) {
start = new Double(start1).intValue();
} else {
start = 1;
}
if (validateNumber(maxValue1)) {
maxValue = new Double(maxValue1).intValue();
} else {
maxValue = 10;
}
StringBuffer sql = new StringBuffer();
sql.append("select a.EMP_ID,a.NAME,a.ID_CARD,a.START_ORG_ID from tb_employee a,tb_organization b where a.START_ORG_ID = b.ORG_ID ");
Connection c = null;
PreparedStatement ps = null;
ResultSet res = null;
list = new ArrayList();
//java调用存储过程代码,GetDataByPage是存储过程名称,有三个参数,
CallableStatement cs = c.prepareCall("{call GetDataByPage(?,?,?)}",ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
cs.setString(1,sql.toString());//查询的sql语句
cs.setInt(2,maxValue); //每页显示的数量
cs.setInt(3,start); //页数
res = cs.executeQuery();//获得存储过程返回的结果集
//遍历结果集,存储在集合中
while(res.next()){
SelectForm select = new SelectForm();
select.setEmpId(res.getInt(1));
select.setName(res.getString(2));
select.setIdCard(res.getString(3));
select.setStartOrgId(res.getInt(4));
list.add(select);
}
}
catch(Exception e){
e.printStackTrace();
}finally{
try {
// 关闭
res.close();
ps.close();
} catch (Exception e) {
e.printStackTrace();
}
}
//调用分页工厂类
PageFactory.makeNavigator(mapping, request, start, maxValue, totalCount, true);
request.setAttribute("list", list);
return mapping.findForward("success");
}
3、分页工厂类,可以不做任何修改直接用,
// COPYRIGHT (C) 2009 KOAL INTERNATIONAL INFORMATION CORPORATION
//
// ALL RIGHTS RESERVED BY KOAL INTERNATIONAL INFORMATION
// CORPORATION, THIS PROGRAM MUST BE USED SOLELY FOR THE
// PURPOSE FOR WHICH IT WAS FURNISHED BY KII CORPORATION ,
// NO PART OF THIS PROGRAM MAY BE REPRODUCED OR DISCLOSED
// TO OTHERS, IN ANY FORM WITHOUT THE PRIOR WRITTEN
// PERMISSION OF KII CORPORATION.USE OF COPYRIGHT NOTICE
// DOES NOT EVIDENCE PUBLICATION OF THE PROGRAM
//
//KOAL INTERNATIONAL INFORMATION CONFIDENTIAL AND PROPROETARY
/
package com.koal.ra.view;
import javax.servlet.http.HttpServletRequest;
import org.apache.struts.action.ActionMapping;
/**
* Page.java
*
* @CopyRight KOAL Co. Lmt 2009
* @author zhengxd
* @Since
* @version
* @Date: 2009-8-30
*/
public class PageFactory
{
private static final String SHOW_NAVIGATOR = "showNavigator";
private static final String TOTAL_RECORDS = "totalRecords";
private static final String LAST_PAGE = "lastPage";
private static final String NEXT_PAGE = "nextPage";
private static final String PRE_PAGE = "prePage";
private static final String FIRST_PAGE = "firstPage";
private static final String TOTAL_PAGES = "totalPages";
private static final String CURRENT_PAGE = "currentPage";
private static final String BASIC_URL = "basicURL"; //娌℃湁鍒嗛〉鍙傛暟鐨刄RL銆?
private static final String PAGE_NO = "pageNo";
private static final String URL_PATTERN = ".do";
public static void makeNavigator(ActionMapping mapping,HttpServletRequest request,int start,int maxValue,int totalCount, boolean showflag){
if(null == mapping || request == null){
return;
}
String basicUrl = mapping.getPath();
request.setAttribute(TOTAL_RECORDS, String.valueOf(totalCount));
request.setAttribute("start", String.valueOf(start));
int currentPage = start;
request.setAttribute(CURRENT_PAGE, String.valueOf(currentPage));
int totalPages = totalCount/maxValue;
if(0 != totalCount%maxValue){
totalPages = totalPages + 1;
}
if(0 == totalPages){
totalPages = totalPages + 1;
}
request.setAttribute(TOTAL_PAGES, String.valueOf(totalPages));
if(currentPage > 1){
request.setAttribute(FIRST_PAGE, CreateURL(basicUrl,0,maxValue));
request.setAttribute(PRE_PAGE, CreateURL(basicUrl,start-maxValue,maxValue));
}
if(totalPages > start){
request.setAttribute(NEXT_PAGE, CreateURL(basicUrl,start+maxValue,maxValue));
request.setAttribute(LAST_PAGE, CreateURL(basicUrl,(totalPages-1)*maxValue,maxValue));
}
if(showflag)
request.setAttribute(SHOW_NAVIGATOR, "true");
}
/**
* @param path
* @param start
* @param maxValue
* @return
*/
private static String CreateURL(String path, int start, int maxValue)
{
String url = path + "?start=" + start + "&limit=" + maxValue;
return url;
}
}
4,page.jsp 分页的jsp页面,在跳转时,调用主页面的action
<%--该页面是pager2的分页导航条页面。需要使用导航条的页面使用include包含本页面。 --%>
<%--包含本页面之前应该使用《% String actionName="xxxx"; %》,其中的xxxx为点击导航条链接时跳转的页面 -->
<%--xxx使用的路径为跟context无关路径 --%>
<%--hef --%>
<%@ page language="java" pageEncoding="UTF-8"
contentType="text/html; charset=UTF-8"%>
<%@ taglib uri="/WEB-INF/struts-bean.tld" prefix="bean" %>
<%@ taglib uri="/WEB-INF/struts-html.tld" prefix="html" %>
<%@ taglib uri="/WEB-INF/struts-logic.tld" prefix="logic" %>
<script language="javascript" type="text/javascript">
function firstPage(){
document.form.start.value = 1;
document.form.submit();
}
function prePage(){
document.form.start.value = parseInt(document.form.start.value) - 1;
document.form.submit();
}
function nextPage(){
document.form.start.value = parseInt(document.form.start.value) + 1;
document.form.submit();
}
function lastPage(){
document.form.start.value = (document.form.totlePage.value);
document.form.submit();
}
function prCheck(){
if(!/^\d*$/.test(document.form.pageNo.value))
{ alert('只能输入大于零的整数');
return false;}
if (parseInt(document.form.pageNo.value)<1){
alert('请输入大于零的整数');
return false;}
if(document.form.pageNo.value > document.form.totlePage.value){
alert('输入的值不能大于总页数');
return false;
}
document.form.start.value = (document.form.pageNo.value);
document.form.submit();
}
</script>
<%
String contextPath = request.getContextPath();
String pageNo = String.valueOf(request.getAttribute("totalPages"));
String start = String.valueOf(request.getAttribute("start"));
if("null".equals(start))
start="0";
%>
<logic:present name="showNavigator">
<table border="0" cellpadding="0" cellspacing="0" >
<input type="hidden" name="start" value="<%=start %>"/>
<input type="hidden" name="totlePage" value="<%=pageNo %>"/>
<tr>
<td >
<logic:present name="firstPage">
<a href="javascript:firstPage();"><div class="page-first">首页</a>
</logic:present>
<logic:notPresent name="firstPage">
<div class="page-first-disable"></div>
</logic:notPresent>
<logic:present name="prePage">
<a href="javascript:prePage();">上一页</div></a>
</logic:present>
<logic:notPresent name="prePage">
<div class="page-prev-disabled"></div>
</logic:notPresent>
<div class="grid-blue-split"></div>
</td>
<td>
<div class="page-input">第
<input class="input-page-text" type="text" name="pageNo" size="2" value="<bean:write name='currentPage'/>"> 页, <a href="#" οnclick="javascript:prCheck();">跳转</a>,共<bean:write name='totalPages'/>页
</div>
<div class="grid-blue-split"></div>
</td>
<td>
<logic:present name="nextPage">
<a href="javascript:nextPage();">下一页 </div></a>
</logic:present>
<logic:notPresent name="nextPage">
<div class="page-next-disabled"></div>
</logic:notPresent>
</td>
<td >
<logic:present name="lastPage">
<a href="javascript:lastPage();">最后一页</div></a>
</logic:present>
<logic:notPresent name="lastPage">
<div class="page-last-disabled"></div>
</logic:notPresent>
</td>
<td>
<div class="grid-blue-split"></div>
<div class="page-refresh"></div>
<div class="grid-blue-split"></div>
<div>共<bean:write name="totalRecords"/>条记录</div>
</td>
</tr>
</table>
</logic:present>
5 业务jsp页面,
在业务jsp页面 在适当的位置插入<jsp:include page="../common/page.jsp" /> 即可 ,../common/page.jsp 是你分页jsp的位置
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/applezhengxd/archive/2009/09/02/4509779.aspx