JSP:实现数据分页查询

准备工作[实体层]

此示例在Linux下演示没有问题,并且此处假设你已经安装了MySQL和Eclipse.
请使用以下代码登录你的数据库:

mysql -u USER -p ->ENTER
password:PASSWORD
//->表示按下回车,USER:用户名,PASSWORD:密码.
//如果是远程数据库请使用以下语句[IP表示数据库IP地址,若数据库不是默认的3306端口,请按照格式:IP:3306连接]:
mysql -h IP -p ->ENTER
password:PASSWORD


接着开始创建数据库和表:


CREATE DATABASE Test;
CREATE TABLE Student (
NUM int (4) primary key not null auto_increment,
NAME varchar(20),
ADDRESS varchar(200),
EMAIL varchar(50)
SEX varchar(2);
AGE int
);


关于插入数据:可以使用Java进行数据插入:


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

/**
* 连接MySQL范本.
* @author Yefeng.
* @version 2013.2.19
*/

public class MySQLDemo {
private static final String DRIVE ="com.mysql.jdbc.Driver";//设置驱动
private static final String URL = "jdbc:mysql://localhost/";//设置连接
private static final String USER = "root";//设置用户名
private static final String PASSWORD ="password";//设置密码

public static void main (String []args) throws Exception{ //仅在测试时才将异常信息交给main()方法处理.
Class.forName(DRIVE);//加载驱动
Connection conn = null;
int result = null;
try{
conn=DriverManager.getConnection(URL,USER,PASSWORD);//获得连接.
for (int i=0;i<100;i++){ StringBuffer sql = new StringBuffer(); sql.append(" INSERT INTO Student "); sql.append(" (NAME,ADDRESS,EMAIL,SEX,AGE) "); sql.append(" VALUES ( "); sql.append(" test , "); sql.append(" dslakfjdslakfjlksda , "); sql.append(" fdsafdas@000.com ,"); sql.append(" NV ,"); sql.append(i); sql.append(" ); "); result=conn.createStatement().executeUpdate("");// Receive the } System.out.println(result); }catch(SQLException e){ e.printStackTrace(); }finally{ conn.close();//关闭连接. } } }

逻辑处理部分

然后,就按照下面的步骤来了:

1.负责存放属性的Bean.

package bean;

/**
* 7.4.5
*
* @author puruidong
* @version 2013.7.3
*/

public class Stu745 {

private String name;

public String getName() {
return name;
}

public void setName(String name) {
this.name = name;
}

public int getAge() {
return age;
}

public void setAge(int age) {
this.age = age;
}

public String getAddress() {
return address;
}

public void setAddress(String address) {
this.address = address;
}

public String getEmail() {
return email;
}

public void setEmail(String email) {
this.email = email;
}

public String getSex() {
return sex;
}

public void setSex(String sex) {
this.sex = sex;
}

private int age;
private String address;
private String email;
private String sex;

}

2.连接数据库的操作Bean.


package bean;

import java.sql.Connection;
import java.sql.DriverManager;

/**
* 7.4.5
*
* @author puruidong
* @version 2013.7.3
*/

public class GetConn745 {

private static final String DRIVER = "com.mysql.jdbc.Driver";
private static final String URL = "jdbc:mysql://localhost:3306/Test";
private static final String USER = "USERID";
private static final String PASSWORD = "PASSWORD";

/**
* 获取连接.
*
* @return
*/
public static Connection getConn() {
Connection conn = null;
try {
Class.forName(DRIVER);
conn = DriverManager.getConnection(URL, USER, PASSWORD);
} catch (Exception e) {
e.printStackTrace();
conn = null;
}
return conn;
}
}

3.处理数据的Bean.


package bean;

import java.util.List;
import java.util.ArrayList;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Connection;
import java.sql.ResultSet;

/**
* 在该类中定义根据当前分页页数和每页显示的记录数定义分页方法
*
* @author puruidong
* @version 2013.7.4
*/

public class PaginationUtil745 {

/**
* 获取查询结果
*
* @param page
* @param pagesize
* @return
*/
public List findGrade(int page, int pagesize) {
String strSql = "select * from Student limit " + (page - 1)
* pagesize + "," + pagesize + "";
// 定义SQL查询语句
Statement pstmt = null;
ResultSet rs = null;
List lstList = new ArrayList();
try {
if (GetConn745.getConn() != null) {
pstmt = (GetConn745.getConn()).createStatement();
rs = pstmt.executeQuery(strSql);
while (rs.next()) {
Stu745 orderBean = new Stu745();
orderBean.setName(rs.getString("NAME"));
orderBean.setAddress(rs.getString("ADDRESS"));
orderBean.setAge(rs.getInt("AGE"));
orderBean.setEmail(rs.getString("EMAIL"));
orderBean.setSex(rs.getString("SEX"));
lstList.add(orderBean);

}

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

return lstList;
}

/**
* 总行数字
*
* @param row
* @return
*/
public int allPage(int row) {
int allp = 0;
try {
if ((GetConn745.getConn()) != null) {
Statement pstmt = (GetConn745.getConn()).createStatement();
pstmt.execute("select count(*) from Student ");
ResultSet rs = pstmt.getResultSet();
rs.next();
int all = rs.getInt(1);
allp = (all - 1) / row + 1;
}
} catch (SQLException e) {
e.printStackTrace();
}
return allp;
}

}


视图部分

一个JSP页面搞定.


<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ page import="bean.PaginationUtil745,java.util.List,bean.Stu745"%>







<% PaginationUtil745 pageUtil = new PaginationUtil745(); int pageNo = 0; if (request.getParameter("No") == null) { pageNo = 1; //当前页面为第一页 } else { pageNo = Integer.parseInt(request.getParameter("No")); } List list = pageUtil.findGrade(pageNo, 5);//获取分页数据
%>

<% for (int i = 0; i < list.size(); i++) { Stu745 stu = list.get(i); %>

<% } %>
<% int all = pageUtil.allPage(3); //获取总页码 %>

<% } %>

<% } %>