Java calls the paging stored procedure of the Oracle database

Recently, the project progress is relatively slow. I have studied the Oracle stored procedure and pasted the code.

 

First, paste the paging Stored Procedure Code of oracle.

1. This is the package code

 

Create or replace package db_oper is

-- Author: liubing
-- Created: 2009-10-28 15:01:30
-- Purpose: test the paging Stored Procedure of Oracle

-- Public type declarations
Type ref_dataset is ref cursor;

-- Public Function and procedure declarations
Function getcount (mtablename varchar2, mterm varchar2) return number;
Procedure return_dataset (
Mtablename in varchar2, -- table name
Mterm in varchar2, -- Condition
Mpagesize in number, -- number of records displayed per page
Mpageindex in number, -- current page
Morderfield in varchar2, -- Sort Field
Morderstyle in number, -- sort by 0 Ascending Order 1 descending order
Mtotalrecords out number, -- total number of records
Mdateset out ref_dataset -- Record
);

End db_oper;

 

2. This is the package body code.

 

Create or replace package body db_oper is

----- Obtain the number of records ------
Function getcount (mtablename varchar2, mterm varchar2) return number is
I number;
Vsql varchar2 (1000 );
Begin
I: = 0;
Vsql: = 'select count (*) from' | mtablename;

If length (mterm)> 0 then
Vsql: = vsql | 'where' | mterm;
End if;

Execute immediate vsql into I;

Return (I );
End;

----- Data paging ----------
Procedure return_dataset (
Mtablename in varchar2, -- table name
Mterm in varchar2, -- Condition
Mpagesize in number, -- number of records displayed per page
Mpageindex in number, -- current page
Morderfield in varchar2, -- Sort Field
Morderstyle in number, -- sort by 0 Ascending Order 1 descending order
Mtotalrecords out number, -- total number of records
Mdateset out ref_dataset -- Record set
) Is
Begin
Declare
Invalid_input exception;
Vsql varchar2 (1000 );
Start_page number;
End_page number;
N_pageindex number;
N_pagesize number;
-- Mtotalrecords number;
Begin
Mtotalrecords: = getcount (TRIM (mtablename), trim (mterm ));
Vsql: = 'select * from' | mtablename;
If length (TRIM (mterm)> 0 then
Vsql: = vsql | 'where' | trim (mterm );
End if;
If length (TRIM (morderfield)> 0 then
Begin
Vsql: = vsql | 'ORDER BY' | trim (morderfield );
If morderstyle> 0 then
Vsql: = vsql | 'desc ';
End if;
End;
End if;

--
N_pagesize: = mpagesize;
If mpagesize <= 0 then
N_pagesize: = 10;
End if;
N_pageindex: = mpageindex;
If mpageindex <= 0 then
N_pageindex: = 1;
End if;
If mtotalrecords> 0 and n_pageindex> 1 then
Begin
-- If n_pageindex is greater than the actual page number, the actual page number is used.
If n_pageindex> round (mtotalrecords/n_pagesize) + 0.5) then
N_pageindex: = round (mtotalrecords/n_pagesize) + 0.5 );
End if;
End;
End if;
Start_page: = (n_pageindex-1) * n_pagesize + 1;
End_page: = n_pageindex * n_pagesize;
Vsql: = 'select * from (select. *, rownum R from ('| vsql |') A where rownum <= '| end_page |') B where r> = '| start_page;

Open mdateset for vsql;

Exception
When invalid_input then
Open mdateset for select null from dual;
End;
End return_dataset;
End db_oper;

3. This is the Java test code.

 

Public class conntooracle {<br/> final static logger = logger. getlogger (conntooracle. class); <br/> Public static connection getconnection (string URL, string username, string password) {<br/> connection conn = NULL; <br/> try {<br/> drivermanager. registerdriver (New Oracle. JDBC. driver. oracledriver (); <br/> conn = drivermanager. getconnection (URL, username, password); </P> <p> system. out. print Ln ("Connection Starting ....... "); <br/>} catch (sqlexception e) {<br/> logger. error ("connection error ...... "); <br/>}</P> <p> return conn; <br/>}</P> <p> Public static void select () throws exception {<br/> connection conn = NULL; <br/> try {<br/> conn = getconnection ("JDBC: oracle: thin: @ 192.168.6.80: 1521: c2sserver "," d1xn_cmc "," d1xn_cmc "); <br/> // string call =" {call db_oper.return_dataset (?,?, ?,?,?,?,?,?)} "; This method is also OK <br/> string call =" begin db_oper.return_dataset (?,?,?,?,?,?,?,?); End; "; <br/> callablestatement proc = Conn. preparecall (CALL); <br/> Proc. setstring (1, "game_goods"); <br/> Proc. setstring (2, null); <br/> Proc. setstring (3, "20"); <br/> Proc. setstring (4, "1"); <br/> Proc. setstring (5, "n_id"); <br/> Proc. setstring (6, "0"); <br/> Proc. registeroutparameter (7, oracletypes. number); // register the Data Type of the total number of returned records <br/> Proc. registeroutparameter (8, oracletypes. cursor); // register the Data Type of the returned data result set <Br/> proc.exe cute (); </P> <p> int a1 = Proc. getint (7); // obtain the total record set <br/> system. out. println (A1); <br/> resultset rs = (resultset) Proc. getObject (8); // obtain the returned dataset <br/> while (RS. next () {<br/> system. out. println (RS. getstring (1); <br/>}< br/>} catch (sqlexception e) {<br/> E. printstacktrace (); <br/>}finally {<br/> If (Conn! = NULL) {<br/> conn. Close (); <br/>}< br/>