JSP + MySQL + Java optimized paging instance

In JSP, data in the query database is often used. In the same way, we use the SQL statement "select * From tablename order by ID DESC". This method has a disadvantage, when the database is very large, the query speed will become very slow, there is a way in ASP to "select top" & recpage & "* From tablename where id not in (select top" & (recpage * (currentpage-1 )) & "id from products order by id desc) order by id desc", recpage indicates the number of entries displayed on each page, and currentpage indicates the current page number. however, there is no "select top *" statement in the MySQL database, but the statement that can be replaced is "select * From tablename limit position, counter" POS Ition indicates where to start the query. If it is 0, it starts from scratch. Counter indicates the number of queries, and queries the database through JSP + Java, the data obtained by the query is temporarily stored in the JSP in the memory. by calling the Java class, the data is directly extracted from the memory, greatly improving the speed.

The following example is a program for commenting on netizens. If you have a website dedicated to browsing by netizens and want to interact with them again, adding a comment is a good idea, then you can add the following program, create a table, and add a photo_id field to your table, so that users can comment on your picture.

Comment. Java is a comment class.

// <-------- Comment. Java ------->
Package dbconnection;
Public class comment
{
Private string ID;
Private string album_id;
Private String title;
Private string content;
Private string modi_time;
Private string user;
Public void setid (string IDs)
{
This. ID = IDs;
}
Public void setalbum_id (string album_ids)
{
This. album_id = album_ids;
}
Public void settitle (string titles)
{
This. Title = titles;
}
Public void setcontent (string contents)
{
This. content = contents;
}
Public void setmodi_time (string modi_times)
{
This. modi_time = modi_times;
}
Public void setuser (string Users)
{
This. User = users;
}
Public String GETID ()
{
Return ID;
}
Public String getalbum_id ()
{
Return album_id;
}
Public String gettitle ()
{
Return title;
}
Public String getcontent ()
{
Return content;
}
Public String getmodi_time ()
{
Return modi_time;
}
Public String getuser ()
{
Return user;
}
}

Testsql. Java is the class we need to query the database. For specific calls, see the following comment. jsp file.

/**
* Title JSP + MySQL paging optimization example
* @ Author: Cyd
* Copyright: Copyright (c) 2003
* @ Version 1.0
* Date 2004-9-22
*/

// <-------- Testsql. Java ------->

Package dbconnection;
Import java. SQL .*;
Import java. util .*;
Public class testsql
{
Statement stmt = NULL;
Resultset rs = NULL;
Conn c = NULL;
Comment comments [] = NULL;
Vector v = NULL;
Int total;
Int pagesize;
Int pagecount;
Public testsql (connection CN) throws sqlexception
{
Stmt = cn. createstatement ();
}
// Query and retrieve records
Public Comment [] getcomment (INT pagesize, int page) throws sqlexception
{
This. pagesize = pagesize;
String SQL = "select * from comment order by ID DESC limit" + (page-1) * pagesize + "," + pagesize;
Comment comments [] = NULL;
V = new vector ();
Try
{
Rs1_stmt.exe cutequery (SQL );
While (Rs. Next ())
{
Comment P = new comment ();
P. setid (Rs. getstring ("ID "));
P. settitle (Rs. getstring ("title "));
P. setcontent (Rs. getstring ("content "));
P. setmodi_time (Rs. getstring ("modi_time "));
P. setuser (Rs. getstring ("user "));
V. Add (P );
}
}
Catch (sqlexception E)
{
System. Err. println ("Err ");
}
Comments = New Comment [v. Size ()];
V. copyinto (comments );
Return comments;
}

// Obtain the total number of records
Public int gettotal ()
{
Return total;
}
// Obtain the total number of pages
Public int getpagecount ()
{
Try
{
Rs1_stmt.exe cutequery ("select count (*) from comment ");
Rs. Next ();
This. Total = Rs. getint (1 );
This. pagecount = (Rs. getint (1) + The PageSize-1)/pagesize;
}
Catch (sqlexception E)
{
System. Err. println ("Err ");
}
Return pagecount;
}
// Release resources
Public void close () throws sqlexception
{
If (stmt! = NULL)
{
Stmt. Close ();
Stmt = NULL;
}
If (RS! = NULL)
{
Rs. Close ();
Rs = NULL;
}
}
}

<! -- Comment. jsp -------------------------------------------------------------------->

<% @ Page contenttype = "text/html; charset = gb2312" Language = "Java" Import = "Java. SQL. *" %>
<% @ Page import = "Java. Io. *" %>
<% @ Page import = "dbconnection. dbconnectionmanager" %>
<%
Dbconnectionmanager connmgr; // This is the class of the database connection pool. You can find the source code on the Internet.
Connmgr = dbconnectionmanager. getinstance ();
Connection con = connmgr. getconnection ("IDB"); // a connection obtained from the connection pool

Int currentpage = 1;
Int intpagecount, introwcount;
If (request. getparameter ("page ")! = NULL)
Currentpage = integer. parseint (request. getparameter ("page "));
If (currentpage <1)
Currentpage = 1;
Int intpagesize = 5; // you can specify 5 entries for each page.
%>
<HTML>
<Head>
<Title> untitled document </title>
<Meta http-equiv = "Content-Type" content = "text/html; charset = gb2312">
<Style type = "text/CSS">
<! --
. Style3 {color: # ff0000}
Body {
Margin-left: 0px;
Margin-top: 0px;
Margin-Right: 0px;
Margin-bottom: 0px;
Background-color: # fffddf;
}
-->
</Style>
<Script language = "JavaScript">
Function GOTO (FRM)
{
VaR gourl = "comment. jsp? ";
Gourl + = "& page =" + (FRM. Page. value );
VaR hid = parseint (FRM. Hid. value );
If (parseint (FRM. Page. Value)> hid | frm. Page. value <= 0 ){
Alert ("error! Make sure that the number you entered is between 1-"+ hid + ");
Return false;
}
Window. Location. href (gourl );
} </SCRIPT>
</Head>
<Body>
<%
Comment [] P = NULL;
Testsql Ts = NULL;
Try
{
TS = new testsql (CON );
P = ts. getcomment (intpagesize, currentpage); // Ts =. getcomments (pagesize (number of entries per page), page (number of pages ))
Intpagecount = ts. getpagecount (); // page number obtained
Introwcount = P. length;
If (currentpage> intpagecount)
Currentpage = intpagecount;
Int Total = ts. gettotal (); // obtain the total number of records
%>
<Table width = "748" border = "0" align = "center" cellpadding = "0" cellspacing = "0">
<Tr>
<TD>
<Table width = "100%" border = "0" align = "center" cellpadding = "0" cellspacing = "0">
<Tr>
<TD Height = "17"> <Table width = "100%" border = "0" cellpadding = "0" cellspacing = "0" bgcolor = "# ebeadf">
<Tr>
<TD Height = "25" bgcolor = "# a7e081"> <Div align = "center" class = "style3"> comments </div> </TD>
</Tr>
<! -- Start loop by tr -------------------------->
<%
If (introwcount> 0)
{
For (INT I = 0; I <introwcount; I ++)
{
%>
<Tr>
<TD Height = "20">
<Table width = "100%" border = "0" cellpadding = "0" cellspacing = "0" bgcolor = "# ebeadf">
<Tr>
<TD Height = "20"> <% = P [I]. getuser () %> at <% = P [I]. getmodi_time () %> message </TD>
</Tr>
<Tr>
<TD bgcolor = "# fbfbf9" style = "padding: 5px 5px 5px 5px; line-Height: 18px;"> <% = P [I]. getcontent () %> </TD>
</Tr>
</Table>
</TD>
</Tr>
<%
}
}
Else
{
%>
<Tr>
<TD Height = "20" bgcolor = "# ebeadf">
<%
Out. Print ("no comments for now ");
}
%>
</TD>
</Tr>
<! -- End loop by tr -------------------------->
</Table> </TD>
</Tr>
<Tr>
<TD Height = "17" bgcolor = "# fbfbf9">
<Div align = "center">
<Form style = "margin: 0 0 0">
<Div align = "center"> page <% = currentpage %> total <% = intpagecount %>
<% IF (currentpage> 1) {%>
<A href = "comment. jsp? Page = CurrentPage-1 %> "> previous </a>
<%} Else {%>
Previous Page
<% }%>
<% IF (currentpage> = intpagecount) {%>
Next Page
<%} Else {%>
<A href = "comment. jsp? Page = <% = currentpage + 1%> "> next page </a>
<% }%>
Jump
<Input type = "hidden" name = "hid" value = "<% = intpagecount %>">
<Input name = "page" type = "text" size = "2" onchange = "Goto (this. Form)">
Page
<Input type = "button" name = "button2" value = "go->" style = "font-size: 12px">
</Div>
</Form>
</Div> </TD>
</Tr>
</Table>
</TD>
</Tr>
</Table>
</Body>
</Html>
<%
}
Catch (exception E)
{
E. printstacktrace ();
}
Finally {
Connmgr. freeconnection ("IDB", con );
Connmgr. Release ();
TS. Close ();
P = NULL;
}
%>

Note: Win2000 + tomcat5.0 debugging passed; redhat9 + tomcat5.0 debugging passed