1、创建表和要添加的数据,直接在数据库运行
CREATE DATABASE `bookstore`;
USE `bookstore`;
DROP TABLE IF EXISTS `books`;
CREATE TABLE `books` (
`id` varchar(200) NOT NULL,
`name` varchar(100) NOT NULL,
`price` double DEFAULT NULL,
`pnum` int(11) DEFAULT NULL,
`category` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into `books`(`id`,`name`,`price`,`pnum`,`category`) values ('1001','java编程思想',98,100,'计算机'),('1002','西游记',10,20,'文学'),('1003','九阴真经',20,30,'武侠'),('1004','365夜睡前好故事',19.8,50,'少儿'),('1005','撒哈拉的故事',16.6,80,'文学'),('1006','三只小猪',9.8,50,'少儿'),('1007','中华上下五千年',28,100,'少儿'),('1008','金瓶梅',9.8,50,'文学'),('1009','平凡的世界',55,80,'文学'),('1010','心灵鸡汤',15,100,'文学');
2、分页的javabean的设置
package com.itheima.util;
import java.util.List;
/**
*
* 页面传过来
* currentPage 当前页
* 自己钉死的
*pageSize 页面的尺寸
*计算出来
*totalPage 总页数 pagesize totalRecodes 总记录数
*
*pageindex pagesize currentPage
*
*数据库查出来
*totalRecodes 总记录数
*页面要展示的内容
* list
*
*/
public class PageModel {
private int currentPage;
private int totalPage;
private int pageindex;
private int totalRecodes;
private int pageSize;
private List list;
public PageModel(int currentPage, int totalRecodes, int pageSize) {
super();
this.currentPage = currentPage;
this.totalRecodes = totalRecodes;
this.pageSize = pageSize;
pageindex =(currentPage-1)*pageSize;
totalPage=totalRecodes%pageSize==0?(totalRecodes/pageSize):(totalRecodes/pageSize)+1;
}
/**
* @return the currentPage
*/
public int getCurrentPage() {
return currentPage;
}
/**
* @param currentPage the currentPage to set
*/
public void setCurrentPage(int currentPage) {
this.currentPage = currentPage;
}
/**
* @return the totalPage
*/
public int getTotalPage() {
return totalPage;
}
/**
* @param totalPage the totalPage to set
*/
public void setTotalPage(int totalPage) {
this.totalPage = totalPage;
}
/**
* @return the pageindex
*/
public int getPageindex() {
return pageindex;
}
/**
* @param pageindex the pageindex to set
*/
public void setPageindex(int pageindex) {
this.pageindex = pageindex;
}
/**
* @return the totalRecodes
*/
public int getTotalRecodes() {
return totalRecodes;
}
/**
* @param totalRecodes the totalRecodes to set
*/
public void setTotalRecodes(int totalRecodes) {
this.totalRecodes = totalRecodes;
}
/**
* @return the pageSize
*/
public int getPageSize() {
return pageSize;
}
/**
* @param pageSize the pageSize to set
*/
public void setPageSize(int pageSize) {
this.pageSize = pageSize;
}
/**
* @return the list
*/
public List getList() {
return list;
}
/**
* @param list the list to set
*/
public void setList(List list) {
this.list = list;
}
}
3、与数据库的books表所对应的实体类设置
package com.itheima.domain;
public class Books {
/**
* `id` varchar(200) NOT NULL,
`name` varchar(100) NOT NULL,
`price` double DEFAULT NULL,
`pnum` int(11) DEFAULT NULL,
`category` varchar(50) DEFAULT NULL,
*/
private String id;
private String name;
private double price;
private int pnum;
private String category;
/**
* @return the id
*/
public String getId() {
return id;
}
/**
* @param id the id to set
*/
public void setId(String id) {
this.id = id;
}
/**
* @return the name
*/
public String getName() {
return name;
}
/**
* @param name the name to set
*/
public void setName(String name) {
this.name = name;
}
/**
* @return the price
*/
public double getPrice() {
return price;
}
/**
* @param price the price to set
*/
public void setPrice(double price) {
this.price = price;
}
/**
* @return the pnum
*/
public int getPnum() {
return pnum;
}
/**
* @param pnum the pnum to set
*/
public void setPnum(int pnum) {
this.pnum = pnum;
}
/**
* @return the category
*/
public String getCategory() {
return category;
}
/**
* @param category the category to set
*/
public void setCategory(String category) {
this.category = category;
}
}
4、页面的设置
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>bookStore列表</title>
<%--导入css --%>
<link rel="stylesheet" href="${pageContext.request.contextPath }/css/main.css" type="text/css" />
</head>
<body class="main">
<jsp:include page="head.jsp" />
<jsp:include page="menu_search.jsp" />
<div id="divpagecontent">
<table width="100%" border="0" cellspacing="0">
<tr>
<td>
<div style="text-align:right; margin:5px 10px 5px 0px">
<a href="index.jsp">首页</a> > 计算机 > 图书列表
</div>
<table cellspacing="0" class="listcontent">
<tr>
<td>
<h1>商品目录</h1>
<hr />
<h1>计算机</h1> 共100种商品
<hr />
<div style="margin-top:20px; margin-bottom:5px">
<img src="${pageContext.request.contextPath }/images/productlist.gif" width="100%" height="38" />
</div>
<%-- <table cellspacing="0" class="booklist">
<tr>
<c:forEach items="${pb.books }" var="b">
<td>
<div class="divbookpic">
<p>
<a href="#"><img src="" width="115" height="129"
border="0" /> </a>
</p>
</div>
<div class="divlisttitle">
<a href="${pageContext.request.contextPath }/servlet/findBookInfoServlet?id=${b.id}">书名:${b.name }<br />售价:${b.price } </a>
</div>
</td>
</c:forEach>
</tr>
</table> --%>
<table cellspacing="0" cellpadding="1" rules="all"
bordercolor="gray" border="1" id="DataGrid1"
style="BORDER-RIGHT: gray 1px solid; BORDER-TOP: gray 1px solid; BORDER-LEFT: gray 1px solid; WIDTH: 100%; WORD-BREAK: break-all; BORDER-BOTTOM: gray 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #f5fafe; WORD-WRAP: break-word">
<tr
style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #afd1f3">
<td align="center" width="15%"> 商品编号</td>
<td align="center" width="20%">商品名称</td>
<td align="center" width="15%">商品价格</td>
<td align="center" width="15%">商品数量</td>
<td width="15%" align="center">商品类别</td>
<td width="10%" align="center">编辑</td>
<td width="10%" align="center">删除</td>
</tr>
<c:forEach items="${pb.list }" var="b">
<tr οnmοuseοver="this.style.backgroundColor = 'white'"
οnmοuseοut="this.style.backgroundColor = '#F5FAFE';">
<td>${b.id }</td>
<td>${b.name }</td>
<td>${b.price }</td>
<td>${b.pnum }</td>
<td>${b.category }</td>
<td><a
href="${pageContext.request.contextPath}/servlet/findBookByIdServlet?id=${b.id }">
<img
src="${pageContext.request.contextPath}/admin/images/i_edit.gif"
border="0" style="CURSOR: hand"> </a>
</td>
<td ><a
href="javascript:delBook('${b.id }','${b.name }','${pb.currentPage }')">
<%-- <img
src="${pageContext.request.contextPath}/admin/images/i_del.gif"
width="16" height="16" border="0" style="CURSOR: hand"> --%>
删除
</a>
</td>
</tr>
</c:forEach>
</table>
<div class="pagination">
<ul>
<li class="nextPage"><a href="${pageContext.request.contextPath }/servlet/pageServlet?currentPage=${pb.currentPage==1?1:pb.currentPage-1}"><<上一页</a></li>
<li>第${pb.currentPage }页/共${pb.totalPage }页</li>
<li class="nextPage"><a href="${pageContext.request.contextPath }/servlet/pageServlet?currentPage=${pb.currentPage==pb.totalPage?pb.totalPage:pb.currentPage+1}">下一页>></a></li>
</ul>
</div></td>
</tr>
</table>
</td>
</tr>
</table>
</div>
<jsp:include page="foot.jsp" />
</body>
<script>
function delBook(id,name,currentPage){
var isDel=confirm("是否删除"+name+"这个书?");
if(isDel){
location.href="${pageContext.request.contextPath }/DelBooksServlet?id="+id+"¤tPage="+currentPage;
}
}
</script>
</html>
5servlet的代码
package com.itheima.web.servlet;
import java.io.IOException;
import java.sql.SQLException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.itheima.service.BookServiceImpl;
import com.itheima.util.PageModel;
/**
* Servlet implementation class PageServlet
*/
public class PageServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
int currentPage = Integer.parseInt(request.getParameter("currentPage"));
int pageSize =3;
BookServiceImpl bookServiceImpl = new BookServiceImpl();
PageModel pb=null;
try {
pb = bookServiceImpl.findPageModel(currentPage,pageSize);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
request.setAttribute("pb", pb);
request.getRequestDispatcher("/product_list.jsp").forward(request, response);
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request, response);
}
}
6service的代码
package com.itheima.service;
import java.sql.SQLException;
import java.util.List;
import com.itheima.dao.BookDaoImpl;
import com.itheima.domain.Books;
import com.itheima.util.PageModel;
public class BookServiceImpl {
/**
*1查总记录数
*new 实体类把currentPage、pageSize totalRecodes
*索引 list 存到PageModel
* @throws SQLException
*
*/
BookDaoImpl bookDaoImpl = new BookDaoImpl();
public PageModel findPageModel(int currentPage, int pageSize) throws SQLException {
int count =bookDaoImpl.findPageRecodes();
PageModel pageModel = new PageModel(currentPage,count,pageSize);
//获取list
List<Books> list=bookDaoImpl.findList(pageModel.getPageindex(),pageSize);
pageModel.setList(list);
return pageModel;
}
public void delBooksById(String id) throws SQLException {
bookDaoImpl.delBooksById(id);
}
}
7dao层的代码
package com.itheima.dao;
import java.sql.SQLException;
import java.util.List;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import com.itheima.domain.Books;
import com.itheima.util.C3P0Util;
public class BookDaoImpl {
public int findPageRecodes() throws SQLException {
QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
String sql="select count(*) from books";
Long count =(Long)qr.query(sql, new ScalarHandler());
return count.intValue();
}
public List<Books> findList(int pageindex, int pageSize) throws SQLException {
QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
String sql="select * from books limit ?,?";
Object[] obj ={pageindex,pageSize};
List<Books> list =qr.query(sql, new BeanListHandler<Books>(Books.class),obj);
return list;
}
}