One STRUTS + sqlserver page (Stored Procedure Edition)

It is more efficient to use stored procedures on SQL Server pages.
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 * From usertable ";

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.