jsp+MVC分页读取数据库中的数据

今天要用到分页，以前没写过，我是个菜鸟，再网上找了，有的确实好，但不会用，最后在网上找了一个自己再修改了一下，顺便记录下来，以考初学者们参考
用法很简单，只要你修改一下DbUtil这个类中的数据库名和用户名密码，修改成你自己的的数据库名，和用户名密码，
                    DbUtil类，作用负责连接数据据库，返回一个sql.Connection
===============================================================================
package com.xeeder.sql;//这个包名自己改一下，不要一起复制进去了
import java.sql.*;
public class DbUtil {
        driverName="com.mysql.jdbc.Driver";
static String dburl="jdbc:mysql://";
static String user="root";//用户名
static String password="root";//密码
public DbUtil(){}
/*这个函数有三个重载函数
*hostName为数据库所在的主机，我们一般都在本机，用localhost就可以了
*databaseName是你创建的数据库名
        */
public static java.sql.Connection connectToDb(String hostName,String databaseName)throws Exception{
  Connection con=null;
  String connName=dburl+hostName+":3306"+"/"+databaseName;//databaseName为你创建的数据库名,
  Class.forName("com.mysql.jdbc.Driver").newInstance();
  con=DriverManager.getConnection(connName,user,password);
  return con;
}
public static java.sql.Connection connectToDb(String databaseName)throws Exception{
  return(connectToDb("localhost",databaseName));
}
public static java.sql.Connection connectToDb()throws Exception{
  return(connectToDb("localhost","mydatabase"));
}
}
==============================================================================
下面这个类是控制类，也是主要的类，他有两个方法，一个是统计总记录数并返回它，一个是把数据库中的数查询出来，保存并返加，这里定义了一个对象，这个对象就是用来保存数据库中的记录的，他的属性包括数据中的列名，
================================================================================
package com.xeeder.struts.fenye.form;//包名记得改

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import com.xeeder.sql.DbUtil;//这里是引用上面那个类的包
public class FenyeAction {
Connection con=null;
PreparedStatement pstmt = null;
ResultSet rs = null;
int count;
//这个方法返回一个容器，这个容器包含了所有数据库中的记录
public Collection fenye()throws Exception{
  List list = new ArrayList();
  try {
   con = DbUtil.connectToDb();
   String sql="select * from personinfo";
   pstmt=con.prepareStatement(sql);
   rs=pstmt.executeQuery();
   while(rs.next()){
    PersoninfoForm ps=new PersoninfoForm();
    ps.setAddress(rs.getString("address"));
    ps.setMobile(rs.getString("mobile"));
    ps.setSex(rs.getString("sex"));
    ps.setName(rs.getString("name"));
    list.add(ps);
   }

  } catch (SQLException e) {

   e.printStackTrace();
  }finally{
   try {
    if(rs!=null)
     rs.close();
    if(pstmt!=null)
     pstmt.close();
    if(con!=null)
     con.close();
   } catch (SQLException e) {

    e.printStackTrace();
   }
  }
  return list;
}

//这是返回查询的总记录数，这里设计的不好，你可以想办法不要这个方法，把他加到上面一个方法中，这样会好一点
public int count()throws Exception{
  try {
   con = DbUtil.connectToDb();
   pstmt=con.prepareStatement("select count(*) from personinfo");
   rs=pstmt.executeQuery();
   while(rs.next())
   count=rs.getInt(1);
   System.out.println(count);
  }catch(SQLException e){
   e.printStackTrace();
  }finally{
   try {
    if(pstmt!=null)
     pstmt=null;
    if(con!=null)
     con.close();
   } catch (SQLException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
   }
  }
  System.out.println(count);
  return count;
}
}
===============================================================================================================
下面就是一个form类，上面相当于一个action类,这个类很简单,建议这三个类都放在一个包中，这样就不用导包麻烦了
===============================================================================================================
package com.xeeder.struts.fenye.form;

public class PersoninfoForm {
private String name;
private String sex;
private String mobile;
private String address;
private int count;
public String getAddress() {
  return address;
}
public void setAddress(String address) {
  this.address = address;
}
public String getMobile() {
  return mobile;
}
public void setMobile(String mobile) {
  this.mobile = mobile;
}
public String getName() {
  return name;
}
public void setName(String name) {
  this.name = name;
}
public String getSex() {
  return sex;
}
public void setSex(String sex) {
  this.sex = sex;
}
public int getCount() {
  return count;
}
public void setCount(int count) {
  this.count = count;
}
}
=====================================================================================================================
下面就是一个jsp页面了,用myeclipse写的所以有很多系统自动生成的东西，不用管他
=====================================================================================================================
<%@ page language="java" pageEncoding="UTF-8"%>
<%@ taglib uri="http://jakarta.apache.org/struts/tags-bean"
prefix="bean"%>
<%@ taglib uri="http://jakarta.apache.org/struts/tags-html"
prefix="html"%>
<%@ taglib uri="http://jakarta.apache.org/struts/tags-logic"
prefix="logic"%>
<%@ taglib uri="http://jakarta.apache.org/struts/tags-tiles"
prefix="tiles"%>
<%@ taglib uri="http://jakarta.apache.org/struts/tags-template"
prefix="template"%>
<%@ taglib uri="http://jakarta.apache.org/struts/tags-nested"
prefix="nested"%>
<%@ page import="java.util.*"%>//下面这些是导包和javabean
<%@ page import="com.xeeder.struts.fenye.form.PersoninfoForm"%>
<jsp:useBean id="FyeBean"
class="com.xeeder.struts.fenye.form.FenyeAction" scope="page"></jsp:useBean>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html:html locale="true">
<head>
<html:base />

<title>MyJsp.jsp</title>

</head>
<body>

<%
  //变量声明
  int intPageSize; //一页显示的记录数
  int intRowCount; //记录总数
  int intPageCount; //总页数
  int intPage; //待显示页码
  java.lang.String strPage;
  int i;
  //设置一页显示的记录数
  intPageSize = 4;
  //取得待显示页码
  strPage = request.getParameter("page");
  if (strPage == null) {//表明在QueryString中没有page这一个参数，此时显示第一页数据
   intPage = 1;
  } else {//将字符串转换成整型
   intPage = java.lang.Integer.parseInt(strPage);
   if (intPage < 1)
    intPage = 1;
  }

  intRowCount = FyeBean.count();//获得总记录数。用javabean调用了FenyeAction中的方法，得到总记录数，
  //记算总页数
  intPageCount = (intRowCount + intPageSize - 1) / intPageSize;
  //调整待显示的页码,如果大于总页数就等于总页数
  if (intPage > intPageCount)
   intPage = intPageCount;
%>
<form method="POST" action="MyJsp.jsp">
  <p>
   第
   <%=intPage%>
   页共
   <%=intPageCount%>
   页

   <%
  if (intPage < intPageCount) {
  %>
   <a href="MyJsp.jsp?page=<%=intPage + 1%>">下一页 </a>
   <%
   }
   %>
   <%
   if (intPage > 1) {
   %>
   <a href="MyJsp.jsp?page=<%=intPage - 1%>"> 上一页</a>
   <%
   }
   %>
   转到第:
   <input type="text" name="page" size="8">
   页
   <span> <input class=buttonface type="submit" value="GO"
     name="cndok"> </span>
  </p>
  <table width="169" border="1" cellpadding="0" cellspacing="0">
   <tr>
    <th width="28">
     姓名
    </th>
    <th width="82">
     性别
    </th>
    <th width="51" >
     电话
    </th>
    <th width="51" >
     地址
    </th>
   </tr>
   <%
   //查询数据库,获得记录数据
   Collection c;
   PersoninfoForm psinfo;
   c = FyeBean.fenye();
   Object o[] = c.toArray();

   System.out.println(intPage);//测试用
   System.out.println(intPageSize);
   System.out.println(intPageCount);
   int start = (intPage - 1) * (intPageSize);
   int end = (intPage - 1) * (intPageSize) + intPageSize;
   int j;
    for (j = start; j < end; j++) {
     psinfo = (PersoninfoForm) o[j];
     String name = psinfo.getName();
     String sex = psinfo.getSex();
     String mobile = psinfo.getMobile();
     String adress = psinfo.getAddress();
   %>
   <tr>
    <td>
     <%=name%>
    </td>
    <td>
     <%=sex%>
    </td>
    <td>
     <%=mobile%>
    </td>
    <td>
     <%=adress%>
    </td>
   </tr>
   <%
   }
   %>
  </table>

</form>

</body>
</html:html>
===========================================================================================================================
这个页面是在网上找的，小改了一下，呵呵！！不怎么好，我看过比这更好的，但不会用，因为这个java代码跟页面的混在一起了，不过可用就行呵呵！！
下面是建表，在你的数据库中建表。
==========================================================================================================================
CREATE TABLE personinfo (
id varchar(100) NOT NULL default '',
name varchar(50) NOT NULL default '',
sex tinyint(1) NOT NULL default '0',
mobile varchar(50) NOT NULL default '0',
address varchar(50) NOT NULL default '',
memo varchar(100) default '',
sysdate datetime default '0000-00-00 00:00:00',
PRIMARY KEY (id)
)
=============================================================================================================================
下面是插入数据，以备我们读取之用，这些都是在网上找的，不用麻烦自己插入数据了
insert into personinfo(id,name,sex,mobile,address,memo,sysdate) values
(2,'cwb1','0','win98win200','2000kb','rqwrqwr','2006-3-4'),(3,'cwb2','0','win98win200','2000kb','rqwrqwr','2006-3-4'),(4,'cwb3','0','win98win200','2000kb','rqwrqwr','2006-3-4'),(5,'cwb4','0','win98win200','2000kb','rqwrqwr','2006-3-4'),(6,'cwb5','0','win98win200','2000kb','rqwrqwr','2006-3-4'),(7,'cwb6','0','win98win200','2000kb','rqwrqwr','2006-3-4'),
(8,'cwb7','0','win98win200','2000kb','rqwrqwr','2006-3-4'),(9,'cwb8','0','win98win200','2000kb','rqwrqwr','2006-3-4'),(10,'cwb9','0','win98win200','2000kb','rqwrqwr','2006-3-4'),(11,'cwb10','0','win98win200','2000kb','rqwrqwr','2006-3-4'),(12,'cwb11','0','win98win200','2000kb','rqwrqwr','2006-3-4'),(13,'cwb12','0','win98win200','2000kb','rqwrqwr','2006-3-4'),(14,'cwb13','0','win98win200','2000kb','rqwrqwr','2006-3-4'),(15,'cwb14','0','win98win200','2000kb','rqwrqwr','2006-3-4'),(16,'cwb15','0','win98win200','2000kb','rqwrqwr','2006-3-4'),(17,'cwb16','0','win98win200','2000kb','rqwrqwr','2006-3-4'),(18,'cwb17','0','win98win200','2000kb','rqwrqwr','2006-3-4'),(19,'cwb18','0','win98win200','2000kb','rqwrqwr','2006-3-4'),(20,'cwb19','0','win98win200','2000kb','rqwrqwr','2006-3-4'),(21,'cwb20','0','win98win200','2000kb','rqwrqwr','2006-3-4'),(22,'cwb21','0','win98win200','2000kb','rqwrqwr','2006-3-4'),(23,'cwb22','0','win98win200','2000kb','rqwrqwr','2006-3-4'),(24,'cwb23','0','win98win200','2000kb','rqwrqwr','2006-3-4'),(25,'cwb24','0','win98win200','2000kb','rqwrqwr','2006-3-4'),(26,'cwb25','0','win98win200','2000kb','rqwrqwr','2006-3-4'),(27,'cwb26','0','win98win200','2000kb','rqwrqwr','2006-3-4'),(28,'cwb27','0','win98win200','2000kb','rqwrqwr','2006-3-4'),(29,'cwb28','0','win98win200','2000kb','rqwrqwr','2006-3-4'),(30,'cwb29','0','win98win200','2000kb','rqwrqwr','2006-3-4'),(31,'cwb30','0','win98win200','2000kb','rqwrqwr','2006-3-4'),(32,'cwb31','0','win98win200','2000kb','rqwrqwr','2006-3-4'),(33,'cwb32','0','win98win200','2000kb','rqwrqwr','2006-3-4'),(34,'cwb33','0','win98win200','2000kb','rqwrqwr','2006-3-4'),(35,'cwb34','0','win98win200','2000kb','rqwrqwr','2006-3-4'),(36,'cwb35','0','win98win200','2000kb','rqwrqwr','2006-3-4'),(37,'cwb36','0','win98win200','2000kb','rqwrqwr','2006-3-4');

秒客网

jsp+MVC分页读取数据库中的数据

相关文章