DB2 General paging Stored Procedure

Create   Procedure Db2admin. proc_getpage
(Inout currentpage Integer ,
In Strsql Varchar ( 500 ),
In Pagesize Integer ,
Out totalpage Integer ,
Out totalrecord Integer
)
Specific db2admin. sql080201103307110
Dynamic result sets 1
Language SQL
Not Deterministic
Called On   Null Input
Reads SQL data
Inherit special registers
Begin
Declare Tmpsql Varchar ( 1000 ); -- Subject sentence
Declare Orderfiled Varchar ( 50 );
Declare S Varchar ( 1000 );
Declare V_start Integer ;
Declare V_end Integer ;
Declare Result Cursor   With   Return   To Caller For S2;

Set Orderfiled = Getorderfield (strsql );

If (Length (orderfiled) > 0 ) Then
Set Orderfiled = ' Order '   | Orderfiled;
End   If ;



Set S = Getsql (strsql );
Set Tmpsql =   ' Select count (*) from ( '   | Strsql |   ' ) As ' ;
Prepare S2 From Tmpsql;
Open Result;
Fetch Result Into Totalrecord; -- Total number of records
Close Result;

If (Pagesize =   0 ) Then
Set Pagesize =   20 ; -- Number of entries per page
End   If ;

Set Totalpage = (Totalrecord -   1 ) / Pagesize +   1 ; -- Total number of pages



If (Currentpage <   1 ) Then
Set Currentpage =   1 ; -- Current page
Else
If (Currentpage > Totalpage) Then
Set Currentpage = Totalpage;
End   If ;
End   If ;

Set V_start = (Currentpage - 1 ) * Pagesize;
Set V_end = Currentpage * Pagesize;


Set Tmpsql = ' Select * from '   |
' (Select rownumber () over () as row, '   |
' W. * from (select * from ( '   | S | ' ) N '   | Orderfiled |   ' ) W) W1 where row ' |   Char (V_start) | ' And ' |   Char (V_end );

Prepare S2 From Tmpsql;
Open Result;

End ;

Two Java functions are used.

Import java. util. RegEx .*;

Import com. IBM. db2.app. UDF;

Public class functiondb2 extends UDF
{
Public static string getorderfield (string SQL)
{
Pattern RegEx = pattern. Compile ("select (. +) Order \ s * by \ s * (. + )",
Pattern. canon_eq | pattern. case_insensitive | pattern. unicode_case );
Matcher regexmatcher = RegEx. matcher (SQL );
If (regexmatcher. Find ()){
Return regexmatcher. Group (2 );
}
Return "";
}

Public static string getsql (string SQL)
{
Pattern RegEx = pattern. Compile ("(. +) Order \ s * by \ s * (. + )",
Pattern. canon_eq | pattern. case_insensitive | pattern. unicode_case );
Matcher regexmatcher = RegEx. matcher (SQL );
If (regexmatcher. Find ()){
Return regexmatcher. Group (1 );
}
Return SQL;
}



}