JSP+MYSQL+Java类优化分页的实例

类别:Java 点击:0 评论:0 推荐:
在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="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调试通过

本文地址:http://com.8s8s.com/it/it12777.htm