Oracle 分页存储过程Java代码

伊文硕Oracle

2014-04-29

关注关注

这是我的存储过程（Oracle）

CREATEORREPLACEPROCEDUREquery_findpage

(p_tableNameinvarchar2,--表名

p_columninvarchar2,--查询的列

p_Whereinvarchar2,--查询条件

p_orderinvarchar2,--排序的条件

p_curPageinoutNumber,--当前页

p_pageSizeinoutNumber,--每页显示记录条数

p_totalRecordsoutNumber,--总记录数

p_totalPagesoutNumber,--总页数

v_curoutpkg_findpage.cur_query)--返回的结果集

v_sqlVARCHAR2(1000):='';--sql语句

v_startRecordNumber(4);--开始显示的记录条数

v_endRecordNumber(4);--结束显示的记录条数

BEGIN

--记录中总记录条数

v_sql:='SELECTTO_NUMBER(COUNT('||p_column||'))FROM'||p_tableName||'WHERE1=1';

IFp_WhereISNOTNULLorp_Where<>''THEN

v_sql:=v_sql||p_Where;

ENDIF;

EXECUTEIMMEDIATEv_sqlINTOp_totalRecords;

--验证页面记录大小

IFp_pageSize<0THEN

p_pageSize:=0;

ENDIF;

--根据页大小计算总页数

IFMOD(p_totalRecords,p_pageSize)=0THEN

p_totalPages:=p_totalRecords/p_pageSize;

ELSE

p_totalPages:=p_totalRecords/p_pageSize+1;

ENDIF;

--验证页号

IFp_curPage<1THEN

p_curPage:=1;

ENDIF;

IFp_curPage>p_totalPagesTHEN

p_curPage:=p_totalPages;

ENDIF;

--实现分页查询

v_startRecord:=(p_curPage-1)*p_pageSize+1;

v_endRecord:=p_curPage*p_pageSize;

v_sql:='SELECT*FROM(SELECTA.*,rownumrFROM'||

'(SELECT'||p_column||'FROM'||p_tableName;

IFp_WhereISNOTNULLorp_Where<>''THEN

v_sql:=v_sql||'WHERE1=1'||p_Where;

ENDIF;

IFp_orderISNOTNULLorp_order<>''THEN

v_sql:=v_sql||'ORDERBY'||p_order;

ENDIF;

v_sql:=v_sql||')AWHERErownum<='||v_endRecord||')BWHEREr>='

||v_startRecord;

DBMS_OUTPUT.put_line(v_sql);

OPENv_curFORv_sql;

ENDquery_findpage;

这是我的程序（Java）

/*****

*分页查询方法，支持多表联合查询，注意查询条件

@SuppressWarnings("rawtypes")

publicListfindPage(finalStringtable,finalStringcolumn,finalStringwhere,finalStringorder,finalPagepage){

finalListlist=newArrayList();

this.getSession().doWork(newWork(){

@SuppressWarnings("unchecked")

publicvoidexecute(Connectionconn)throwsSQLException{

CallableStatementcst=conn.prepareCall("{callquery_findpage(?,?,?,?,?,?,?,?,?)}");

cst.setString(1,table);//表明

cst.setString(2,column);//列明

cst.setString(3,where);//条件

cst.setString(4,order);//排序条件

cst.setInt(5,page.getP());//当前页

cst.setInt(6,page.getPagesize());//每页显示记录条数

cst.registerOutParameter(7,java.sql.Types.INTEGER);//总记录数

cst.registerOutParameter(8,java.sql.Types.INTEGER);//总页数

cst.registerOutParameter(9,OracleTypes.CURSOR);//返回的结果集

cst.execute();

ResultSetrs=(ResultSet)cst.getObject(9);

ResultSetMetaDatarsmd=rs.getMetaData();

intcolumncount=rsmd.getColumnCount();

MaprowData=newHashMap();

while(rs.next()){

rowData=newHashMap(columncount);

for(inti=1;i<=columncount;i++){

rowData.put(rsmd.getColumnName(i),rs.getObject(i));

}

list.add(rowData);

}

page.setRecords(cst.getInt(7));

page.setPages(cst.getInt(8));

list.add(page);

}

});

returnlist;

}

请问：我在传参数时，比如列：只能是一个或者*，不能用逗号隔开的列，否者报错

list=systemuseradminservice.findPageLog("system_loginlog","adminid,adminname,logintime,loginstatus","","",page);报错

list=systemuseradminservice.findPageLog("system_loginlog","*","","",page);可以

35390INFO2014-04-2915:30:34com.text.www.system.service.impl.SystemUserAdminServiceImplfindPageLog方法执行开始

51006WARN2014-04-2915:30:50org.hibernate.engine.jdbc.spi.SqlExceptionHelperSQLError:909,SQLState:42000

51021ERROR2014-04-2915:30:50org.hibernate.engine.jdbc.spi.SqlExceptionHelperORA-00909:参数个数无效

ORA-06512:在"ZYNYW.QUERY_FINDPAGE",line21

ORA-06512:在line1

51037ERROR2014-04-2915:30:50org.apache.struts2.dispatcher.DispatcherExceptionoccurredduringprocessingrequest:errorexecutingwork

org.hibernate.exception.SQLGrammarException:errorexecutingwork

atorg.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:123)

atorg.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)

atorg.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126)

atorg.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:112)

atorg.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.coordinateWork(JdbcCoordinatorImpl.java:318)

atorg.hibernate.internal.SessionImpl.doWork(SessionImpl.java:2059)

atorg.hibernate.internal.SessionImpl.doWork(SessionImpl.java:2044)

atcom.text.www.dao.impl.SuperDAOImpl.findPage(SuperDAOImpl.java:65)

atcom.text.www.system.service.impl.SystemUserAdminServiceImpl.findPageLog(SystemUserAdminServiceImpl.java:134)

atsun.reflect.NativeMethodAccessorImpl.invoke0(NativeMethod)

atsun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)

atsun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

atjava.lang.reflect.Method.invoke(Method.java:597)

atorg.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)

atorg.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)

atorg.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)

atorg.springframework.aop.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:85)

atcom.text.www.util.LogInterceptor.Around(LogInterceptor.java:19)

atsun.reflect.NativeMethodAccessorImpl.invoke0(NativeMethod)

atsun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)

atsun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

atjava.lang.reflect.Method.invoke(Method.java:597)

atorg.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:621)

atorg.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:610)

atorg.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:68)

atorg.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)

atorg.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)

atorg.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)

atorg.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)

atorg.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)

atorg.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207)

at$Proxy12.findPageLog(UnknownSource)

atcom.text.www.system.action.SystemUserAdminAction.findPageLog(SystemUserAdminAction.java:163)

atsun.reflect.NativeMethodAccessorImpl.invoke0(NativeMethod)

atsun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)

atsun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

atjava.lang.reflect.Method.invoke(Method.java:597)

atcom.opensymphony.xwork2.DefaultActionInvocation.invokeAction(DefaultActionInvocation.java:450)

atcom.opensymphony.xwork2.DefaultActionInvocation.invokeActionOnly(DefaultActionInvocation.java:289)

atcom.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:252)

atorg.apache.struts2.interceptor.DeprecationInterceptor.intercept(DeprecationInterceptor.java:41)