One STRUTS + SQL Server Page (Stored Procedure Edition)

It is more efficient to use stored procedures on SQL Server pages.
The following stored procedure is found in the SQL area.

Certificate -----------------------------------------------------------------------------------------------------------------------------------
Create proc p_show
@ Querystr varchar (8000), -- table name, view name, query statement
@ Pagesize Int = 10, -- size of each page (number of rows)
@ Pagecurrent Int = 1, -- the page to be displayed
@ Fdshow varchar (8000) = '', -- List of fields to be displayed. If the query result contains an ID field, you need to specify this value without the ID field.
@ Fdorder nvarchar (3000) = ''-- List of sorting Fields
As
Declare @ fdname nvarchar (550) -- primary key or column name in the table or temporary table
, @ Id1 varchar (80), @ Id2 varchar (80) -- start and end record numbers
, @ Obj_id int -- Object ID
-- Processing of compound primary keys in the table
Declare @ strfd nvarchar (4000) -- composite Primary Key List
, @ Strjoin varchar (8000) -- connection Field
, @ Strwhere nvarchar (4000) -- Query Condition

Select @ obj_id = object_id (@ querystr)
, @ Fdshow = case isnull (@ fdshow, '') When ''then'' * 'else' + @ fdshow end
, @ Fdorder = case isnull (@ fdorder, '') When ''then'' else 'ORDER BY' + @ fdorder end
, @ Querystr = case when @ obj_id is not null then ''+ @ querystr else '(' + @ querystr + ') a' end

-- If the first page is displayed, top can be used directly.
If @ pagecurrent = 1
Begin
Select @ id1 = cast (@ pagesize as varchar (50 ))
Exec ('select top '+ @ id1 + @ fdshow + 'from' + @ querystr + @ fdorder)
Return
End

-- If it is a table, check whether there is an identifier or a primary key in the table.
If @ obj_id is not null and objectproperty (@ obj_id, 'istable') = 1
Begin
Select @ id1 = cast (@ pagesize as varchar (50 ))
, @ Id2 = cast (@ PageCurrent-1) * @ pagesize as varchar (50 ))

Select @ fdname = Name from syscolumns where id = @ obj_id and status = 0x80
If @ rowcount = 0 -- if the table has no ID column, check whether the table has a primary key.
Begin
If not exists (select 1 from sysobjects where parent_obj = @ obj_id and xtype = 'pk ')
Goto lbusetemp -- if the table has no primary key, use a temporary table for processing.

Select @ fdname = Name from syscolumns where id = @ obj_id and colid in (
Select colid from sysindexkeys where @ obj_id = ID and indid in (
Select indid from sysindexes where @ obj_id = ID and name in (
Select name from sysobjects where xtype = 'pk' and parent_obj = @ obj_id
)))
If @ rowcount> 1 -- check whether the primary key in the table is a composite primary key
Begin
Select @ strfd = '', @ strjoin ='', @ strwhere =''
Select @ strfd = @ strfd + ', [' + name + ']'
, @ Strjoin = @ strjoin + 'and A. [' + name + '] = B. [' + name + ']'
, @ Strwhere = @ strwhere + 'and B. [' + name + '] Is null'
From syscolumns where id = @ obj_id and colid in (
Select colid from sysindexkeys where @ obj_id = ID and indid in (
Select indid from sysindexes where @ obj_id = ID and name in (
Select name from sysobjects where xtype = 'pk' and parent_obj = @ obj_id
)))
Select @ strfd = substring (@ strfd, 2,2000)
, @ Strjoin = substring (@ strjoin, 5)
, @ Strwhere = substring (@ strwhere, 5)
Goto lbusepk
End
End
End
Else
Goto lbusetemp

/* -- Use the identification column or primary key as a single field --*/
Lbuseidentity:
Exec ('select top '+ @ id1 + @ fdshow + 'from' + @ querystr
+ 'Where' + @ fdname + 'not in (select top'
+ @ Id2 + ''+ @ fdname + 'from' + @ querystr + @ fdorder
+ ')' + @ Fdorder
)
Return

/* -- The table has a composite primary key --*/
Lbusepk:
Exec ('select' + @ fdshow + 'from (select top' + @ id1 + 'a. * from
(Select Top 100 percent * from '+ @ querystr + @ fdorder +')
Left join (select top '+ @ Id2 + ''+ @ strfd +'
From '+ @ querystr + @ fdorder +') B on '+ @ strjoin +'
Where '+ @ strwhere +')'
)
Return

/* -- Use a temporary table --*/
Lbusetemp:
Select @ fdname = '[ID _' + Cast (newid () as varchar (80) + ']'
, @ Id1 = cast (@ pagesize * (@ PageCurrent-1) as varchar (50 ))
, @ Id2 = cast (@ pagesize * @ PageCurrent-1 as varchar (50 ))

Exec ('select' + @ fdname + '= identity (INT, 0, 1),' + @ fdshow +'
Into # TB from '+ @ querystr + @ fdorder +'
Select '+ @ fdshow +' from # TB where '+ @ fdname + 'beten'
+ @ Id1 + 'and' + @ Id2
)
Go
Slave --------------------------------------------------------------------------------------------------------------------------------

Here we need to create a paging class resultgatherpro. Java

Slave --------------------------------------------------------------------------------------------------------------------------------

Package com;

Import conn. dbconnmanager; // This is the database connection pool and can be replaced with your own
Import java. SQL .*;
Import java. util. enumeration;
Import java. util. List;
Import java. util. Map;
Import java. util. arraylist;
Import java. util. hashmap;
Import javax. servlet. http. httpservletrequest;
Import com. util. myredirect;

Public class resultgatherpro
{
Private string SQL;
Private int intpagesize; // number of rows per page
Private int introwcount;
Private int intpagecount;
Private int intpage; // page number
Private string counter;

Public resultgatherpro ()
{

}
Public resultgatherpro (string sqlcom, int rownum, int pagenum, string counter)
{
SQL = sqlcom;
Intpagesize = rownum;
Intpage = pagenum;
Counter = counter;
}
Public list selectrs (string sqlcom, int rownum, int pagenum, string counter)
{
This. SQL = sqlcom;
This. intpagesize = rownum;
This. intpage = pagenum;
This. Counter = counter;
Return selectrs ();
}
Public list selectrs ()
{
List rsall = new arraylist ();
Map rstree;
Dbconnmanager conn = NULL;
Connection con = NULL;
Callablestatement stmt = NULL;
Resultset rs = NULL;
Statement ST = NULL;
Resultset RSC = NULL;
Try {
Conn = dbconnmanager. getinstance ();
Con = conn. getconnection ("MSSQL ");
St = con. createstatement ();
Rsc1_st.exe cutequery (Counter );
While (RSC. Next ())
{
Introwcount = RSC. getint ("allrow ");
}

Stmt = con. preparecall ("{call p_show ('" + SQL + "'," + intpagesize + "," + intpage + ")}");
Rs = stmt.exe cutequery ();
Resultsetmetadata rsmd = Rs. getmetadata ();
Int numberofcolumns = rsmd. getcolumncount ();
Object [] AA = new object [numberOfColumns-1];
Intpagecount = (introwcount + intPageSize-1)/intpagesize;
While (Rs. Next ())
{
Rstree = new hashmap (numberofcolumns );
For (INT r = 1; r <numberofcolumns + 1; r ++)
{
Rstree. Put (rsmd. getcolumnname (R), RS. GetObject (R ));
}
Rsall. Add (rstree );
}
} Catch (Java. Lang. Exception ex ){
Ex. printstacktrace ();
} Finally {
Try {
If (RSC! = NULL)
RSC. Close ();
If (st! = NULL)
St. Close ();
If (RS! = NULL)
Rs. Close ();
If (stmt! = NULL)
Stmt. Close ();
If (Conn! = NULL)
Conn. releaseconnection ("MSSQL", con );
} Catch (exception e ){
System. Out. println (E );
}
}
Return rsall;
}
 
Public String changepage (httpservletrequest request)
{
String urlchange = NULL;
String pagename = request. getrequesturi (). substring (request. getrequesturi (). lastindexof ("/") + 1 );
String url = geturl (request );
If (intpage> 1 & intpage <intpagecount)
{
Urlchange = "<a href =" + pagename + "? Pagenum = "+ (intPage-1) + URL +"> previous page </a> <a href = "+ pagename + "? Pagenum = "+ (intpage + 1) + URL + "> next page </a> section" + intpage + "Page total" + intpagecount + "Page total" + introwcount + "bar ";
} Else if (intpage = 1 & introwcount <= intpagesize ){
Urlchange = "Previous Page next page" + intpage + "Page total" + intpagecount + "Page total" + introwcount + "bar ";
} Else if (intpage = intpagecount & intpage! = 1 ){
Urlchange = "<a href =" + pagename + "? Pagenum = "+ (intPage-1) + URL + "> previous page </a> next page" + intpage + "Page total" + intpagecount + "Page total" + introwcount + "bar ";
} Else if (intpage = 1 & introwcount> = intpagesize ){
Urlchange = "Previous Page <a href =" + pagename + "? Pagenum = "+ (intpage + 1) + URL + "> next page </a> section" + intpage + "Page total" + intpagecount + "Page total" + introwcount + "bar ";
} Else {
Urlchange = "<a href =" + pagename + "> page 1 </a> ";
}
Return urlchange;
}
Public String gotopage (httpservletrequest request)
{
String url = geturl (request );
String JavaScript = "<SCRIPT> function checksearch ()/n {If (find. pagenum. value =/"/" | find. pagenum. value =/"0/") {alert ('Enter the correct page number! '); Find. pagenum. Focus (); Return false;}/NIF (isnan (find. pagenum. Value) {alert (' enter a number! '); Find. pagenum. Focus (); Return false ;}</SCRIPT> ";
String form = "<Table border = '0' cellpadding = '0' cellspacing = '0'>/N" + JavaScript;
Form + = "<form name = 'Find 'action = ''' onsubmit = 'Return checksearch () '>/n <tr> <TD width = '40' align = 'center'>/n <input name = 'pagenum' type = 'text' size = '2'> /n ";
If (URL! = NULL)
{
If (URL. indexof ("&")> 0)
{
String [] Param = new string [(URL. Split ("&"). Length];
Param = URL. Split ("&");
For (INT I = 0; I <Param. length; I ++)
{
Form + = "<input type = 'den den 'name = '" + Param [I]. substring (0, Param [I]. indexof ("=") + "'value = '" + Param [I]. substring (Param [I]. indexof ("=") + 1) + "'>/N ";
}
} Else {
If (URL. indexof ("=")> 0)
{
Form + = "<input type = 'den den 'name = '" + URL. substring (0, URL. indexof ("=") + "'value = '" + URL. substring (URL. indexof ("=") + 1) + "'>/N ";
}
}
}
Form + = "</TD> <TD width = '25' align = 'center'> <input type = 'submit 'name = 'submit 'value = 'Go'>/ n </TD>/n </tr>/n </form>/n </table> ";
Return form;
}
Private string geturl (httpservletrequest request)
{
String url = "";
Enumeration Param = request. getparameternames ();
While (Param. hasmoreelements ())
{
String pname = Param. nextelement (). tostring ();
If (! Pname. inclusignorecase ("pagenum "))
URL + = pname + "=" + request. getparameter (pname) + "&";
}
If (URL. endswith ("&"))
{
Url = URL. substring (0, URL. lastindexof ("&"));
}
Return URL;
}
Public String intercept (string STR, int num, string last)
{
If (Str. Length () <= num)
Return STR;
Else
Return Str. substring (0, num) + last;
}
}

Bytes --------------------------------------------------------------------------------------------------------

You can call

Bytes --------------------------------------------------------------------------------------------------------------

String SQL = "select Top 100 percent Jake * From usertable"; // Add Top 100 percent
String sqlcount = "select count (*) from usertable"; // to get the total number of rows
Int pagesize = 18;
Int pagenum = 1;
If (request. getparameter ("pagenum ")! = NULL)
{
Pagenum = java. Lang. Integer. parseint (request. getparameter ("pagenum "));
}
Resultgatherpro rs = new resultgatherpro (SQL, pagesize, pagenum, sqlcount );
Request. setattribute ("liststill", Rs. selectrs ());
Request. setattribute ("changepage", Rs. changepage (request ));
Request. setattribute ("gotopage", Rs. gotopage (request ));

Certificate -----------------------------------------------------------------------------------------------------------------------------------

Finally, you can write in JSP as follows:

Bytes -------------------------------------------------------------------------------------------------------------------------

<Logic: iterate id = "listuser" name = "liststill" type = "map">
<Bean: write name = 'listuser' property = 'username'/>
<Bean: write name = 'listuser' property = 'usermail'/>
</Logic: iterate>

<Bean: write name = 'changepage' filter = "false"/>
<Bean: write name = 'gotopage' filter = "false"/>
You must set filter = "false" for the above two items. Otherwise, the HTML part will be filtered.