【转】java调用sybase存储过程 实现分页

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="#"  onclick="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">
  &nbsp;&nbsp;<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