Java calls Oracle paging stored procedures

Java programs

 Packagecom.test;Importjava.sql.CallableStatement;Importjava.sql.Connection;ImportJava.sql.ResultSet;Importjava.sql.SQLException;Importjava.sql.Types;ImportCom.jdbc.BaseJdbcDAO;Importoracle.jdbc.OracleTypes; Public classTest {/*** @paramargs*/ Public Static voidMain (string[] args) {Connection conn= Basejdbcdao.getconnection2 ("com"); String Sqlsel= "SELECT * FROM POSITION"; String SqlCount= "Select COUNT (*) from POSITION"; String SQL= "{call Sp_page (?,?,?,?,?,?,?)}"; ResultSet RS=NULL;Try{callablestatement call=conn.preparecall (SQL); Call.setint (1, 20); Call.setint (2, 100); Call.setstring (3, Sqlsel); Call.setstring (4, SqlCount); Call.registeroutparameter (5, Types.integer); Call.registeroutparameter (6, Types.integer); Call.registeroutparameter (7, Oracletypes.cursor); Call.execute ();//Remove result setintPageCount = Call.getint (5);//Total PagesintTotal = Call.getint (6);//Total Record CountRS= (ResultSet) call.getobject (7);//Information List SetSystem.out.println ("Total pages:" +PageCount); System.out.println ("Total Records:" +Total );inti = 1; while(Rs.next ()) {System.out.println (">>" + i + "number:" + rs.getstring (2) + "Name:" + rs.getstring (3) + "Serial number:" + rs.getint (1)); I++;}} Catch(SQLException e) {//TODO auto-generated Catch blocke.printstacktrace (); }}}/*how to ask Hovertree.com*/

--Create a cursor package

Create or Replace  as  iscursor- the cursor type definition, used to return the data set END; /* */

--Stored procedures

Create or Replace procedureSp_page (p_pagesizeint,--number of records per pageP_pagenoint,--Current page number, starting from 1P_sqlselectvarchar2,--query statements, with sort partsP_sqlcountvarchar2,--query statement that gets the total number of recordsP_pagecount outint,--total number of pagesP_outrecordcount outint,--returns the total number of recordsp_outcursor out Pkg_query.refcursortype) asV_sqlvarchar2( the); V_countint; V_heirownumint; V_lowrownumint; begin  ----Fetch the total number of records      ExecuteImmediate P_sqlcount intoV_count; P_outrecordcount:=V_count; --Calculate mypagecount--    ifMoD (v_count,p_pagesize)=0  ThenP_pagecount:=V_count/p_pagesize; ElseP_pagecount:=V_count/P_pagesize+1; End if; ----Perform paged queryV_heirownum:=P_pageno*p_pagesize; V_lowrownum:=V_heirownum-P_pagesize+ 1; V_sql:= 'Select b.* from (select A.*, RowNum rn from ('||P_sqlselect||') A WHERE rownum <='||To_char (V_heirownum)|| ') B WHERE rn >=' ||To_char (V_lowrownum); --note the use of rownum aliases, the first direct use of rownum, the second must use the alias RNOPENP_outcursor forV_sql;EndSp_page;/*how to ask Hovertree.com*/

Recommendation: http://www.cnblogs.com/roucheng/p/3504465.html

Java calls Oracle paging stored procedures