[摘要]在JSP中经常要用到查询数据库中的数据,同常我们的做法是使用SQL语句“select * from tablename order by id desc”,这样的做法有一个缺点,当数据库很大的时候查询的速度会变的很慢,在ASP中有一种方法 "select top "&r...
在JSP中经常要用到查询数据库中的数据,同常我们的做法是使用SQL语句“select * from tablename order by id desc”,这样的做法有一个缺点,当数据库很大的时候查询的速度会变的很慢,在ASP中有一种方法 "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为每页显示个数, currentpage为当前页数.不过在MYSQL数据库中没有“select top * " 语句,而可以代替的语句是”select * from tablename limit position, counter “position 指示从哪里开始查询,如果是0则是从头开始,counter 表示查询的个数,通过JSP+JAVA查询数据库,查询获取的数据暂时存放在内存中在JSP中通过调取JAVA类,直接从内存中提取数据,速度有了很大提高。
下面的例子是一个关于网友评论的部分程序,假如你有一个专门供网友浏览的网站,而现在又想和网友互动起来,加一个评论是不错的想法,那么你可以把下面的程序加上,建一个表其中加一个photo_id字段和你的表关联起来后,就可以让网友对你的图片点评了。
Comment.java是一个评论的类
//<--------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就是我们查询数据库要用到的类了,具体的调用请看下面的comment.jsp文件。
/**
* Title jsp+mysql优化分页的例子
* @author: cyd
* Copyright: Copyright (c) 2003
* @version 1.0
* 日期 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();
}
//查询获取记录
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
{
rs=stmt.executeQuery(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;
}
//获取总记录数
public int getTotal()
{
return total;
}
//获取总页数
public int getPageCount()
{
try
{
rs=stmt.executeQuery("select count(*) from comment ");
rs.next();
this.total=rs.getInt(1);
this.PageCount=(rs.getInt(1)+PageSize-1)/PageSize;
}
catch(SQLException e)
{
System.err.println("err");
}
return PageCount;
}
//释放资源
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;//这是数据库连接池的类,具体源码你可以在网找到。
connMgr = DBConnectionManager.getInstance();
Connection con = connMgr.getConnection("idb");//从连接池中获的一个连接
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;//设置每页显示5条
%>
<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("错误!请确定你输入的数字在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(每页显示个数),Page(页数))
intPageCount =ts.getPageCount(); //获的页数
intRowCount=p.length;
if(CurrentPage>intPageCount)
CurrentPage = intPageCount;
int total=ts.getTotal(); //获取记录总数
%>
<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">网友评论</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"> <img src="http://www.it.com.cn/f/edu/053/26/image/dot11.gif" width="9" height="9"> <%=p[i].getUser()%>于 < %=p[i].getModi_time()%> 留言 </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(" 暂时没有评论");
}
%>
</td>
</tr>
<!-- end loop by tr -------------------------->
</table></td>
</tr>
<tr>
<td height="17" bgcolor="#FBFBF9">
<div align="center">
<form style="margin:0 0 0 0 ">
<div align="center">第<%=CurrentPage%>页 共<%=intPageCount%>页
<%if(CurrentPage>1){%>
<a href="comment.jsp?page=<%=CurrentPage-1%>">上一页</a>
<%}else{%>
上一页
<%}%>
<%if(CurrentPage>=intPageCount){%>
下一页
<%}else{%>
<a href="comment.jsp?page=<%=CurrentPage+1%>">下一页</a>
<%}%>
跳至
<input type="hidden" name="hid" value="<%=intPageCount%>">
<input name="page" type="text" size="2" onChange="goto(this.form)">
页
<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;
}
%>
注:win2000+tomcat5.0调试通过;Redhat9+tomcat5.0调试通过
……