JSP_分页查询.doc

JSP 分页查询1. 首先创建数据库，以图书信息查询系统为例，在SQL Server 中创建一个名为 BOOKDB 的数据库，并在其库中创建一个 TBL_BOOK 表，存储图书信息。SQL 脚本如下：/*-设置当前数据库为master ，以便访问sysdatabases 表-*/USE masterGO/*-检测是否已经存在bookDB 数据库：查询master 数据库中的系统表sysdatabases ，如果存在则删除该数据库-*/IF EXISTS (SELECT * FROM sysdatabases WHERE name = BOOKDB)DROP DATABASE BOOKDB-删除数据库GO/*-创建数据库-*/CREATE DATABASE BOOKDBON primary(/*-数据文件的具体描述-*/NAME = BOOKDB_data,-主数据文件的逻辑名称FILENAME = D:BOOKDB_data.mdf,-主数据文件的物理名称SIZE = 5mb,-主数据文件的初始大小MAXSIZE = 80mb,-主数据文件增长的最大值FILEGROWTH = 15%-主数据文件的增长率)LOG ON(/*-日志文件的具体描述-*/NAME = BOOKDB_log,-日志文件的逻辑名称FILENAME = d:BOOKDB_log.ldf,-日志文件的物理名称SIZE = 3mb,-日志文件的初始大小MAXSIZE = 20mb,-日志文件增长的最大值FILEGROWTH = 2mb-日志文件的增长率)GO/*-设置当前数据库为bookDB ，以便访问bookDB 数据库-*/USE BOOKDBGO/*-检测在bookDB 数据库中是否已经存TBL_BOOK 表：查询系统表sysobjects ，如果存在则删除该表-*/IF EXISTS (SELECT * FROM sysobjects WHERE name = TBL_BOOK)DROP TABLE TBL_BOOK-删除数据库/*-创建数据库表TBL_BOOK (图书信息)-*/CREATE TABLE TBL_BOOK(book_id INT IDENTITY(1000,1) PRIMARY KEY,-标识列，自增，主键book_name VARCHAR(50),-图书名称book_num VARCHAR(50) NOT NULL,-图书编号，非空book_author VARCHAR(50) NOT NULL,-图书作者，非空book_price MONEY,-图书价格book_synopsis TEXT,-图书简介book_publishTime DATETIME-图书出版日期)GO/*-添加唯一约束(将bookNum 作为唯一键)-*/ALTER TABLE TBL_BOOKADD CONSTRAINT UQ_book_num UNIQUE(book_num)/*-添加默认约束(如果publishTime 不填，默认当前时间)-*/ALTER TABLE TBL_BOOKADD CONSTRAINT DF_book_publishTime DEFAULT(GETDATE() FOR book_publishTimeGO/*-添加数据-*/INSERT INTO TBL_BOOKSELECT SQL Server 高级编程,XV001001,张无忌,86,重点讲解T-SQL 编程,2010-08-08 UNIONSELECT MySQL 高级编程,XV001002,张国荣,36,重点讲解MySQL 的高级运用,2010-08-07 UNIONSELECT JAVA 高级编程,XV001003,周杰伦,130,零基础学JAVA,2010-02-06 UNIONSELECT C# 高级编程,XV001004,张三丰,99,c# 从入门到精通,2010-07-07 UNIONSELECT JSP 高级编程,XV001005,陆逊,65,JSP 新手入门新教材,2010-02-03 UNIONSELECT PHP 高级编程,XV001006,周瑜,45,好学好用PHP 经典教材,2010-04-07 UNIONSELECT ASP 高级编程,XV001007,诸葛亮,180,最牛的c# 教程体系,2010-09-08 UNIONSELECT ASP.ENT 高级编程,XV001008,曹操,250,一看就会的教材,2010-08-23 UNIONSELECT Oracle 高级编程,XV001009,李白,140,Oracle 新手解惑,2010-08-18 UNIONSELECT DB2 高级编程,XV001010,孔子,860,db2 程序员的宝典,2010-08-01 UNIONSELECT MySQL 手册,XV001011,孙子,43,MySQL 编程最佳助手,2010-02-02 UNIONSELECT SQL 手册,XV001012,老子,100,数据库通用手册，程序员必备,2010-01-07 UNIONSELECT JAVA API 文档,XV001013,周仓,110,JAVA 程序员必备手册,2010-02-05 UNIONSELECT C# 完全手册,XV001014,鲁迅,92,c# 程序员的良伴,2010-03-07 UNIONSELECT C 高级编程,XV001015,和珅,120,一切程序的起点,2010-02-13 UNIONSELECT C+ 高级编程,XV001016,纪晓岚,145,学会C+，走片天下都不怕,2010-04-27 UNIONSELECT C+ 完全手册,XV001017,薛宝钗,118,选手册，C+ 完全手册才是好手册,2010-09-18 UNIONSELECT C 语言入门到精通,XV001018,雷锋,150,不要让程序把你拒之门外,2010-08-21 UNIONSELECT 数据结构与算法C+ 版,XV001019,雄霸,108,最简单的设计思维,2010-08-11 UNIONSELECT 设计模式,XV001020,徐峥,148,最具权威的程序设计书籍,2010-09-21 UNIONSELECT 数据结构与算法JAVA 版,XV001021,李寻欢,240,最好的程序设计书籍,2010-05-18GO2. 在 Eclipse 中新建一个项目名为 BookPagin ，并在其项目的 src 下创建 mons (公共包)、com.book.entity(实体包)、com.book.pagin(JavaBean 包) ，然后将 SQL Server 的 JDBC 驱动包复制到 WebRoot 下的 WEB-INF 下的 lib 目录中。3. 在公共包中编写一个名为 GetConncetion 的数据库连接的基类：package mons;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;public class GetConnection protected Connection conn = null;/连接字符串protected PreparedStatement ps = null;/预编译并存储 SQL 指令protected ResultSet rs = null;/查询结果集private static final String DRIVER = com.microsoft.sqlserver.jdbc.SQLServerDriver;/加载数据库驱动的字符串private static final String URL = jdbc:sqlserver:/localhost:1433;databaseName=BOOKDB;/连接数据库的字符串private static final String USERNAME = sa;/数据库用户名private static final String PASSWORD = accp;/数据库用户密码/* * 获得数据库连接 * return */public Connection getConn() Connection conn = null;try Class.forName(DRIVER);/加载数据库驱动conn = DriverManager.getConnection(URL,USERNAME,PASSWORD);/连接数据库 catch (ClassNotFoundException e) / TODO Auto-generated catch blocke.printStackTrace(); catch (SQLException e) / TODO Auto-generated catch blocke.printStackTrace();return conn;/* * 释放资源 */public void closeAll()try if(rs != null)rs.close();if(ps != null)ps.close();if(conn != null)conn.close(); catch (SQLException e) e.printStackTrace();finallyconn = null;ps = null;rs = null;4. 根据数据库中的 TBL_BOOK 表编写实体类：package com.book.entity;public class Book private int book_id;private String book_name;private String book_num;private String book_author;private double book_price;private String book_synopsis;private String book_publishTime;public String getBook_author() return book_author;public void setBook_author(String book_author) this.book_author = book_author;public int getBook_id() return book_id;public void setBook_id(int book_id) this.book_id = book_id;public String getBook_name() return book_name;public void setBook_name(String book_name) this.book_name = book_name;public String getBook_num() return book_num;public void setBook_num(String book_num) this.book_num = book_num;public double getBook_price() return book_price;public void setBook_price(double book_price) this.book_price = book_price;public String getBook_publishTime() return book_publishTime;public void setBook_publishTime(String book_publishTime) this.book_publishTime = book_publishTime;public String getBook_synopsis() return book_synopsis;public void setBook_synopsis(String book_synopsis) this.book_synopsis = book_synopsis;5. 编写分页查询类：package com.book.pagin;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;import mons.GetConnection;import com.book.entity.Book;public class BookPaginQuery extends GetConnection private final int PAGEROW = 8;/每页显示的行数 private int countRow;/总行数private int countPage;/总页数private int currentlyPage;/当前第几页/* * 得到总页数 * return */public int getCountPage() return countPage;/* * 设置总页数 * param countPage */public void setCountPage() /通过总行数设置总页数if (this.countRow % this.PAGEROW = 0) /如果总行数除以每页显示的行数余数为零时，总页数等于它们的商this.countPage = this.countRow / this.PAGEROW; else /否则，总页数等于它们的商加1this.countPage = this.countRow / this.PAGEROW + 1;/* * 得到总行数 * return */public int getCountRow() return countRow;/* * 设置总行数 * param countRow */public void setCountRow() /通过聚合函数查询 TBL_BOOK 表中一共有多少条数据，并把值存储到 countRow 中String sql = SELECT COUNT(*) FROM TBL_BOOK;try PreparedStatement ps = super.getConn().prepareStatement(sql);ResultSet rs = ps.executeQuery();if (rs.next() this.countRow = rs.getInt(1); catch (SQLException e) / TODO Auto-generated catch blocke.printStackTrace();/* * 得到当前页数 * return */public int getCurrentlyPage() return currentlyPage;/* * 设置当前页数 * param currentlyPage */public void setCurrentlyPage(int currentlyPage) this.currentlyPage = currentlyPage;/* * 分页查询 * param page 当前页数 * return */public List myBookPaginQuery(int page)List bookList = new ArrayList();int num = (page-1) * this.PAGEROW; /要被排除的行数String sql = SELECT top(+this.PAGEROW+) * FROM TBL_BOOK WHERE book_id NOT IN (SELECT TOP(+num+) book_id FROM TBL_BOOK);try PreparedStatement ps = super.getConn().prepareStatement(sql);/预编译 SQL 指令并把预编译好的 SQL 存储在 PreparedStatement 对象中ResultSet rs = ps.executeQuery(); /执行预编译好的 SQL 指令，并把获得的查询结果集存储在 ResultSet 对象中while (rs.next() /通过 while 循环迭代出结果集中的所有数据，并把它们存储在 List 集合中Book book = new Book();book.setBook_id(rs.getInt(book_id);book.setBook_name(rs.getString(book_name);book.setBook_num(rs.getString(book_num);book.setBook_author(rs.getString(book_author);book.setBook_price(rs.getDouble(book_price);book.setBook_synopsis(rs.getString(book_synopsis);book.setBook_publishTime(rs.getString(book_publishTime);bookList.add(book); catch (SQLException e) / TODO Auto-generated catch blocke.printStackTrace();return bookList; 6. 编写 JSP 页面，完成 分页操作： 图书信息 !- % request.setCharacterEncoding(UTF-8); /设置编码集 String strPageNum = request.getParameter(pageNum); /获得当前页数的字符串 int pageNum = 1; /把当前页数的字符串转化为数字，如果转化失败，则设置当前页数为 1 ，即首页 try pageNum = Integer.parseInt(strPageNum); catch (Exception e) pageNum = 1; BookPaginQuery bookPaginQuery = new BookPaginQuery();/实例化 BookPaginQuery 类，得到 bookPaginQuery 对象 bookPaginQuery.setCountRow(); /设置总行数 bookPaginQuery.setCountPage(); /设置总页数 int countPage = bookPaginQuery.getCountPage(); /获得总页数 /如果当前页数小于 0 或大于总页数，则把当前页重新设为 1 ，即首页 if (pageNumcountPage) pageNum = 1; bookPaginQuery.setCurrentlyPage(pageNum); /设置当前页 int cur