One STRUTS + sqlserver page (Stored Procedure Edition)

Source: Internet
Author: User
Tags rowcount

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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.