JSP 分页查询Word格式文档下载.docx
《JSP 分页查询Word格式文档下载.docx》由会员分享,可在线阅读,更多相关《JSP 分页查询Word格式文档下载.docx(16页珍藏版)》请在冰豆网上搜索。
TBL_BOOK'
DROPTABLETBL_BOOK--删除数据库
/*-----创建数据库表TBL_BOOK(图书信息)-----*/
CREATETABLETBL_BOOK
book_idINTIDENTITY(1000,1)PRIMARYKEY,--标识列,自增,主键
book_nameVARCHAR(50),--图书名称
book_numVARCHAR(50)NOTNULL,--图书编号,非空
book_authorVARCHAR(50)NOTNULL,--图书作者,非空
book_priceMONEY,--图书价格
book_synopsisTEXT,--图书简介
book_publishTimeDATETIME--图书出版日期
/*--添加唯一约束(将bookNum作为唯一键)--*/
ALTERTABLETBL_BOOK
ADDCONSTRAINTUQ_book_numUNIQUE(book_num)
/*--添加默认约束(如果publishTime不填,默认当前时间)--*/
ADDCONSTRAINTDF_book_publishTimeDEFAULT(GETDATE())FORbook_publishTime
/*--添加数据--*/
INSERTINTOTBL_BOOK
SELECT'
SQLServer高级编程'
'
XV001001'
张无忌'
86'
重点讲解T-SQL编程'
2010-08-08'
UNION
MySQL高级编程'
XV001002'
张国荣'
36'
重点讲解MySQL的高级运用'
2010-08-07'
JAVA高级编程'
XV001003'
周杰伦'
130'
零基础学JAVA'
2010-02-06'
C#高级编程'
XV001004'
张三丰'
99'
c#从入门到精通'
2010-07-07'
JSP高级编程'
XV001005'
陆逊'
65'
JSP新手入门新教材'
2010-02-03'
PHP高级编程'
XV001006'
周瑜'
45'
好学好用PHP经典教材'
2010-04-07'
ASP高级编程'
XV001007'
诸葛亮'
180'
最牛的c#教程体系'
2010-09-08'
ASP.ENT高级编程'
XV001008'
曹操'
250'
一看就会的教材'
2010-08-23'
Oracle高级编程'
XV001009'
李白'
140'
Oracle新手解惑'
2010-08-18'
DB2高级编程'
XV001010'
孔子'
860'
db2程序员的宝典'
2010-08-01'
MySQL手册'
XV001011'
孙子'
43'
MySQL编程最佳助手'
2010-02-02'
SQL手册'
XV001012'
老子'
100'
数据库通用手册,程序员必备'
2010-01-07'
JAVAAPI文档'
XV001013'
周仓'
110'
JAVA程序员必备手册'
2010-02-05'
C#完全手册'
XV001014'
鲁迅'
92'
c#程序员的良伴'
2010-03-07'
C高级编程'
XV001015'
和珅'
120'
一切程序的起点'
2010-02-13'
C++高级编程'
XV001016'
纪晓岚'
145'
学会C++,走片天下都不怕'
2010-04-27'
C++完全手册'
XV001017'
薛宝钗'
118'
选手册,C++完全手册才是好手册'
2010-09-18'
C语言入门到精通'
XV001018'
雷锋'
150'
不要让程序把你拒之门外'
2010-08-21'
数据结构与算法C++版'
XV001019'
雄霸'
108'
最简单的设计思维'
2010-08-11'
设计模式'
XV001020'
徐峥'
148'
最具权威的程序设计书籍'
2010-09-21'
数据结构与算法JAVA版'
XV001021'
李寻欢'
240'
最好的程序设计书籍'
2010-05-18'
2.在Eclipse中新建一个项目名为BookPagin,并在其项目的src下创建mons(公共包)、com.book.entity(实体包)、com.book.pagin(JavaBean包),然后将SQLServer的JDBC驱动包复制到WebRoot下的WEB-INF下的lib目录中。
3.在公共包中编写一个名为GetConncetion的数据库连接的基类:
packagemons;
importjava.sql.Connection;
importjava.sql.DriverManager;
importjava.sql.PreparedStatement;
importjava.sql.ResultSet;
importjava.sql.SQLException;
publicclassGetConnection{
protectedConnectionconn=null;
//连接字符串
protectedPreparedStatementps=null;
//预编译并存储SQL指令
protectedResultSetrs=null;
//查询结果集
privatestaticfinalStringDRIVER="
com.microsoft.sqlserver.jdbc.SQLServerDriver"
;
//加载数据库驱动的字符串
privatestaticfinalStringURL="
jdbc:
sqlserver:
//localhost:
1433;
databaseName=BOOKDB"
//连接数据库的字符串
privatestaticfinalStringUSERNAME="
sa"
//数据库用户名
privatestaticfinalStringPASSWORD="
accp"
//数据库用户密码
/**
*获得数据库连接
*@return
*/
publicConnectiongetConn(){
Connectionconn=null;
try{
Class.forName(DRIVER);
//加载数据库驱动
conn=DriverManager.getConnection(URL,USERNAME,PASSWORD);
//连接数据库
}catch(ClassNotFoundExceptione){
//TODOAuto-generatedcatchblock
e.printStackTrace();
}catch(SQLExceptione){
}
returnconn;
}
/*
*释放资源
publicvoidcloseAll(){
if(rs!
=null){
rs.close();
}
if(ps!
ps.close();
if(conn!
conn.close();
}finally{
conn=null;
ps=null;
rs=null;
}
4.根据数据库中的TBL_BOOK表编写实体类:
packagecom.book.entity;
publicclassBook{
privateintbook_id;
privateStringbook_name;
privateStringbook_num;
privateStringbook_author;
privatedoublebook_price;
privateStringbook_synopsis;
privateStringbook_publishTime;
publicStringgetBook_author(){
returnbook_author;
publicvoidsetBook_author(Stringbook_author){
this.book_author=book_author;
publicintgetBook_id(){
returnbook_id;
publicvoidsetBook_id(intbook_id){
this.book_id=book_id;
publicStringgetBook_name(){
returnbook_name;
publicvoidsetBook_name(Stringbook_name){
this.book_name=book_name;
publicStringgetBook_num(){
returnbook_num;
publicvoidsetBook_num(Stringbook_num){
this.book_num=book_num;
publicdoublegetBook_price(){
returnbook_price;
publicvoidsetBook_price(doublebook_price){
this.book_price=book_price;
publicStringgetBook_publishTime(){
returnbook_publishTime;
publicvoidsetBook_publishTime(Stringbook_publishTime){
this.book_publishTime=book_publishTime;
publicStringgetBook_synopsis(){
returnbook_synopsis;
publicvoidsetBook_synopsis(Stringbook_synopsis){
this.book_synopsis=book_synopsis;
5.编写分页查询类:
packagecom.book.pagin;
importjava.util.ArrayList;
importjava.util.List;
importmons.GetConnection;
importcom.book.entity.Book;
publicclassBookPaginQueryextendsGetConnection{
privatefinalintPAGEROW=8;
//每页显示的行数
privateintcountRow;
//总行数
privateintcountPage;
//总页数
privateintcurrentlyPage;
//当前第几页
*得到总页数
publicintgetCountPage(){
returncountPage;
*设置总页数
*@paramcountPage
publicvoidsetCountPage(){
//通过总行数设置总页数
if(this.countRow%this.PAGEROW==0){//如果总行数除以每页显示的行数余数为零时,总页数等于它们的商
this.countPage=this.countRow/this.PAGEROW;
}else{//否则,总页数等于它们的商加1
this.countPage=this.countRow/this.PAGEROW+1;
*得到总行数
publicintgetCountRow(){
returncountRow;
*设置总行数
*@paramcountRow
publicvoidsetCountRow(){
//通过聚合函数查询TBL_BOOK表中一共有多少条数据,并把值存储到countRow中
Stringsql="
SELECTCOUNT(*)FROMTBL_BOOK"
PreparedStatementps=super.getConn().prepareStatement(sql);
ResultSetrs=ps.executeQuery();
if(rs.next()){
this.countRow=rs.getInt
(1);
*得到当前页数
publicintgetCurrentlyPage(){
returncurrentlyPage;
*设置当前页数
*@paramcurrentlyPage
publicvoidsetCurrentlyPage(intcurrentlyPage){
this.currentlyPage=currentlyPage;
*分页查询
*@parampage当前页数
publicList<
Book>
myBookPaginQuery(intpage){
List<
bookList=newArrayList<
();
intnum=(page-1)*this.PAGEROW;
//要被排除的行数
SELECTtop("
+this.PAGEROW+"
)*FROMTBL_BOOKWHEREbook_idNOTIN(SELECTTOP("
+num+"
)book_idFROMTBL_BOOK)"
//预编译SQL指令并把预编译好的SQL存储在PreparedStatement对象中
//执行预编译好的SQL指令,并把获得的查询结果集存储在ResultSet对象中
while(rs.next()){//通过while循环迭代出结果集中的所有数据,并把它们存储在List<
集合中
Bookbook=newBook();
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);
returnbookList;
}
6.编写JSP页面,完成分页操作:
<
%@pagelanguage="
java"
import="
java.util.*"
pageEncoding="
UTF-8"
%>
jsp:
directive.pageimport="
com.book.pagin.BookPaginQuery"
/>
com.book.entity.Book"
!
DOCTYPEHTMLPUBLIC"
-//W3C//DTDHTML4.01Transitional//EN"
>
html>
<
head>
title>
图书信息<
/title>
metahttp-equiv="
pragma"
content="
no-cache"
cache-control"
expires"
0"
keywords"
keyword1,keyword2,keyword3"
description"
Thisismypage"
--
linkrel="
stylesheet"
type="
text/css"
href="
styles.css"
-->
/head>
body>
<
%
request.setCharacterEncoding("
);
//设置编码集
StringstrPageNum=request.getParameter("
pageNum"
//获得当前页数的字符串
intpageNum=1;
//把当前页数的字符串转化为数字,如果转化失败,则设置当前页数为1,即首页
try{
pageNum=Integer