Java connection Oracle Database call stored procedure Implementation paging query (EMP for example)

First step: Build a package with a cursor type

Sql>create or Replace package testpackage

As type test_cursor is REF CURSOR; --Define a cursor named Test_cursor

End Testpackage;

Step two: Write a paged stored procedure

Sql>create or replace procedure Fenye (

TableName in varchar2,--table name

PageSize in number,--records displayed per page

Pagenow in number,--current page

Myrows out number,--total records

Mypagecount out number,--total pages

P_cursor out testpackage.test_cursor--the recordset returned

) is

--Define a partial SQL statement string, and two variables

V_sql varchar (1000);

V_begin number:= (pageNow-1) *pagesize+1;

V_end Number:=pagesize*pagenow;

--Executive Section

Begin

V_sql:= ' SELECT * FROM (select A1.*,rownum rn from, select * from ' | | tablename| | ') A1 where

Rownum<= ' | | v_end| | ') where rn>= ' | | V_begin;

Open p_cursor for V_sql; --Cursor associated SQL

V_sql:= ' SELECT COUNT (*) from ' | | TableName;

Execute immediate v_sql into myrows;

If mod (myrows,pagesize) =0 Then

Mypagecount:=myrows/pagesize;

Else

mypagecount:=myrows/pagesize+1;

End If;

Close p_cursor;

End

/

Step three Java calls the process remember to introduce the JAR package

Import java.sql.*;

public class Fenye {

public static void Main (String [] args) {

try{

Class.forName ("Oracle.jdbc.driver.OracleDriver");

Connection ct=drivermanager.getconnection ("Jdbc:oracle:thin:@127.0.0.1:1521:orcl", "Scott", "Tiger"); ORCL-bit database name

CallableStatement Cs=ct.preparecall ("{Call Fenye (?,?,?,?,?,?)}");

To give? Assign value

Cs.setstring (1, "EMP");

Cs.setint (2,5);//Display 5 rows per page

Cs.setint (3,2);//Show Second page

Register return value

Cs.registeroutparameter (4,oracle.jdbc.oracletypes.integer);

Cs.registeroutparameter (5,oracle.jdbc.oracletypes.integer);

Cs.registeroutparameter (6,oracle.jdbc.oracletypes.cursor);

Cs.execute ();//execution

int Rownum=cs.getint (4);

int Pagecount=cs.getint (5);

ResultSet rs= (ResultSet) cs.getobject (6);

System.out.println ("RowNum" +rownum);

System.out.println ("Total pages:" +pagecount);

while (Rs.next ()) {

System.out.println ("Number:" +rs.getint (1) + "Name:" +rs.getstring (2));}

}catch (Exception e) {

E.printstacktrace ();}

}

}

Java connection Oracle Database call stored procedure Implementation paging query (EMP for example)