Java进阶-使用三层架构实现增删改查（十四）

文章所需资料下载

一、案例一：查询所有商品信息

1.1、流程分析

1.2、案例代码实现

(1) 案例结构：

（2）sql导入：

CREATE TABLE category (
  cid varchar(32) NOT NULL,
  cname varchar(20) DEFAULT NULL,
  PRIMARY KEY (cid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO category VALUES ('1','手机数码'),('2','电脑办公'),('3','家具家居'),('4','鞋靴箱包'),('5','图书音像'),('6','母婴孕婴');

CREATE TABLE product (
  pid varchar(32) NOT NULL,
  pname varchar(50) DEFAULT NULL,
  market_price double DEFAULT NULL,
  shop_price double DEFAULT NULL,
  pimage varchar(200) DEFAULT NULL,
  pdate date DEFAULT NULL,
  is_hot int(11) DEFAULT NULL,
  pdesc varchar(255) DEFAULT NULL,
  pflag int(11) DEFAULT NULL,
  cid varchar(32) DEFAULT NULL,
  PRIMARY KEY (pid),
  KEY sfk_0001 (cid),
  CONSTRAINT sfk_0001 FOREIGN KEY (cid) REFERENCES category (cid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO product VALUES ('1','小米 4c 标准版',1399,1299,'products/1/c_0001.jpg','2015-11-02',1,'小米 4c 标准版 全网通 白色 移动联通电信4G手机 双卡双待',0,'1'),('10','华为 Ascend Mate7',2699,2599,'products/1/c_0010.jpg','2015-11-02',1,'华为 Ascend Mate7 月光银 移动4G手机 双卡双待双通6英寸高清大屏，纤薄机身，智能超八核，按压式指纹识别！!选择下方“移动老用户4G飞享合约”，无需换号，还有话费每月返还！',0,'1'),('11','vivo X5Pro',2399,2298,'products/1/c_0014.jpg','2015-11-02',1,'移动联通双4G手机 3G运存版 极光白【购机送蓝牙耳机+蓝牙自拍杆】新升级3G运行内存·双2.5D弧面玻璃·眼球识别技术',0,'1'),('12','努比亚（nubia）My 布拉格',1899,1799,'products/1/c_0013.jpg','2015-11-02',0,'努比亚（nubia）My 布拉格 银白 移动联通4G手机 双卡双待【嗨11，下单立减100】金属机身，快速充电！布拉格相机全新体验！',0,'1'),('13','华为 麦芒4',2599,2499,'products/1/c_0012.jpg','2015-11-02',1,'华为 麦芒4 晨曦金 全网通版4G手机 双卡双待金属机身 2.5D弧面屏 指纹解锁 光学防抖',0,'1'),('14','vivo X5M',1899,1799,'products/1/c_0011.jpg','2015-11-02',0,'vivo X5M 移动4G手机 双卡双待 香槟金【购机送蓝牙耳机+蓝牙自拍杆】5.0英寸大屏显示·八核双卡双待·Hi-Fi移动KTV',0,'1'),('15','Apple iPhone 6 (A1586)',4399,4288,'products/1/c_0015.jpg','2015-11-02',1,'Apple iPhone 6 (A1586) 16GB 金色 移动联通电信4G手机长期省才是真的省！点击购机送费版，月月送话费，月月享优惠，畅享4G网络，就在联通4G！',0,'1'),('16','华为 HUAWEI Mate S 臻享版',4200,4087,'products/1/c_0016.jpg','2015-11-03',0,'华为 HUAWEI Mate S 臻享版 手机 极昼金 移动联通双4G(高配)满星评价即返30元话费啦；买就送电源+清水套+创意手机支架；优雅弧屏，mate7升级版',0,'1'),('17','索尼(SONY) E6533 Z3+',4099,3999,'products/1/c_0017.jpg','2015-11-02',0,'索尼(SONY) E6533 Z3+ 双卡双4G手机 防水防尘 涧湖绿索尼z3专业防水 2070万像素 移动联通双4G',0,'1'),('18','HTC One M9+',3599,3499,'products/1/c_0018.jpg','2015-11-02',0,'HTC One M9+（M9pw） 金银汇 移动联通双4G手机5.2英寸，8核CPU，指纹识别，UltraPixel超像素前置相机+2000万/200万后置双镜头相机！降价特卖，惊喜不断！',0,'1'),('19','HTC Desire 826d 32G 臻珠白',1599,1469,'products/1/c_0020.jpg','2015-11-02',1,'后置1300万+UltraPixel超像素前置摄像头+【双】前置扬声器+5.5英寸【1080p】大屏！',0,'1'),('2','中兴 AXON',2899,2699,'products/1/c_0002.jpg','2015-11-05',1,'中兴 AXON 天机 mini 压力屏版 B2015 华尔金 移动联通电信4G 双卡双待',0,'1'),('20','小米 红米2A 增强版 白色',649,549,'products/1/c_0019.jpg','2015-11-02',0,'新增至2GB 内存+16GB容量！4G双卡双待，联芯 4 核 1.5GHz 处理器！',0,'1'),('21','魅族 魅蓝note2 16GB 白色',1099,999,'products/1/c_0021.jpg','2015-11-02',0,'现货速抢，抢完即止！5.5英寸1080P分辨率屏幕，64位八核1.3GHz处理器，1300万像素摄像头，双色温双闪光灯！',0,'1'),('22','三星 Galaxy S5 (G9008W) 闪耀白',2099,1999,'products/1/c_0022.jpg','2015-11-02',1,'5.1英寸全高清炫丽屏，2.5GHz四核处理器，1600万像素',0,'1'),('23','sonim XP7700 4G手机',1799,1699,'products/1/c_0023.jpg','2015-11-09',1,'三防智能手机 移动/联通双4G 安全 黑黄色 双4G美国军工IP69 30天长待机 3米防水防摔 北斗',0,'1'),('24','努比亚（nubia）Z9精英版 金色',3988,3888,'products/1/c_0024.jpg','2015-11-02',1,'移动联通电信4G手机 双卡双待真正的无边框！金色尊贵版！4GB+64GB大内存！',0,'1'),('25','Apple iPhone 6 Plus (A1524) 16GB 金色',5188,4988,'products/1/c_0025.jpg','2015-11-02',0,'Apple iPhone 6 Plus (A1524) 16GB 金色 移动联通电信4G手机 硬货 硬实力',0,'1'),('26','Apple iPhone 6s (A1700) 64G 玫瑰金色',6388,6088,'products/1/c_0026.jpg','2015-11-02',0,'Apple iPhone 6 Plus (A1524) 16GB 金色 移动联通电信4G手机 硬货 硬实力',0,'1'),('27','三星 Galaxy Note5（N9200）32G版',5588,5388,'products/1/c_0027.jpg','2015-11-02',0,'旗舰机型！5.7英寸大屏，4+32G内存！不一样的SPen更优化的浮窗指令！赠无线充电板！',0,'1'),('28','三星 Galaxy S6 Edge+（G9280）32G版 铂光金',5999,5888,'products/1/c_0028.jpg','2015-11-02',0,'赠移动电源+自拍杆+三星OTG金属U盘+无线充电器+透明保护壳',0,'1'),('29','LG G4（H818）陶瓷白 国际版',3018,2978,'products/1/c_0029.jpg','2015-11-02',0,'李敏镐代言，F1.8大光圈1600万后置摄像头，5.5英寸2K屏，3G+32G内存，LG年度旗舰机！',0,'1'),('3','华为荣耀6',1599,1499,'products/1/c_0003.jpg','2015-11-02',0,'荣耀 6 (H60-L01) 3GB内存标准版 黑色 移动4G手机',0,'1'),('30','微软(Microsoft) Lumia 640 LTE DS (RM-1113)',1099,999,'products/1/c_0030.jpg','2015-11-02',0,'微软首款双网双卡双4G手机，5.0英寸高清大屏，双网双卡双4G！',0,'1'),('31','宏碁（acer）ATC705-N50 台式电脑',2399,2299,'products/1/c_0031.jpg','2015-11-02',0,'爆款直降，满千减百，品质宏碁，特惠来袭，何必苦等11.11，早买早便宜！',0,'2'),('32','Apple MacBook Air MJVE2CH/A 13.3英寸',6788,6688,'products/1/c_0032.jpg','2015-11-02',0,'宽屏笔记本电脑 128GB 闪存',0,'2'),('33','联想（ThinkPad） 轻薄系列E450C(20EH0001CD)',4399,4199,'products/1/c_0033.jpg','2015-11-02',0,'联想（ThinkPad） 轻薄系列E450C(20EH0001CD)14英寸笔记本电脑(i5-4210U 4G 500G 2G独显 Win8.1)',0,'2'),('34','联想（Lenovo）小新V3000经典版',4599,4499,'products/1/c_0034.jpg','2015-11-02',0,'14英寸超薄笔记本电脑（i7-5500U 4G 500G+8G SSHD 2G独显 全高清屏）黑色满1000減100，狂减！火力全开，横扫3天！',0,'2'),('35','华硕（ASUS）经典系列R557LI',3799,3699,'products/1/c_0035.jpg','2015-11-02',0,'15.6英寸笔记本电脑（i5-5200U 4G 7200转500G 2G独显 D刻 蓝牙 Win8.1 黑色）',0,'2'),('36','华硕（ASUS）X450J',4599,4399,'products/1/c_0036.jpg','2015-11-02',0,'14英寸笔记本电脑 （i5-4200H 4G 1TB GT940M 2G独显 蓝牙4.0 D刻 Win8.1 黑色）',0,'2'),('37','戴尔（DELL）灵越 飞匣3000系列',3399,3299,'products/1/c_0037.jpg','2015-11-03',0,' Ins14C-4528B 14英寸笔记本（i5-5200U 4G 500G GT820M 2G独显 Win8）黑',0,'2'),('38','惠普(HP)WASD 暗影精灵',5699,5499,'products/1/c_0038.jpg','2015-11-02',0,'15.6英寸游戏笔记本电脑(i5-6300HQ 4G 1TB+128G SSD GTX950M 4G独显 Win10)',0,'2'),('39','Apple 配备 Retina 显示屏的 MacBook',11299,10288,'products/1/c_0039.jpg','2015-11-02',0,'Pro MF840CH/A 13.3英寸宽屏笔记本电脑 256GB 闪存',0,'2'),('4','联想 P1',2199,1999,'products/1/c_0004.jpg','2015-11-02',0,'联想 P1 16G 伯爵金 移动联通4G手机充电5分钟，通话3小时！科技源于超越！品质源于沉淀！5000mAh大电池！高端商务佳配！',0,'1'),('40','机械革命（MECHREVO）MR X6S-M',6799,6599,'products/1/c_0040.jpg','2015-11-02',0,'15.6英寸游戏本(I7-4710MQ 8G 64GSSD+1T GTX960M 2G独显 IPS屏 WIN7)黑色',0,'2'),('41','神舟（HASEE） 战神K660D-i7D2',5699,5499,'products/1/c_0041.jpg','2015-11-02',0,'15.6英寸游戏本(i7-4710MQ 8G 1TB GTX960M 2G独显 1080P)黑色',0,'2'),('42','微星（MSI）GE62 2QC-264XCN',6199,5999,'products/1/c_0042.jpg','2015-11-02',0,'15.6英寸游戏笔记本电脑（i5-4210H 8G 1T GTX960MG DDR5 2G 背光键盘）黑色',0,'2'),('43','雷神（ThundeRobot）G150S',5699,5499,'products/1/c_0043.jpg','2015-11-02',0,'15.6英寸游戏本 ( i7-4710MQ 4G 500G GTX950M 2G独显 包无亮点全高清屏) 金',0,'2'),('44','惠普（HP）轻薄系列 HP',3199,3099,'products/1/c_0044.jpg','2015-11-02',0,'15-r239TX 15.6英寸笔记本电脑（i5-5200U 4G 500G GT820M 2G独显 win8.1）金属灰',0,'2'),('45','未来人类（Terrans Force）T5',10999,9899,'products/1/c_0045.jpg','2015-11-02',0,'15.6英寸游戏本（i7-5700HQ 16G 120G固态+1TB GTX970M 3G GDDR5独显）黑',0,'2'),('46','戴尔（DELL）Vostro 3800-R6308 台式电脑',3299,3199,'products/1/c_0046.jpg','2015-11-02',0,'（i3-4170 4G 500G DVD 三年上门服务 Win7）黑',0,'2'),('47','联想（Lenovo）H3050 台式电脑',5099,4899,'products/1/c_0047.jpg','2015-11-11',0,'（i5-4460 4G 500G GT720 1G独显 DVD 千兆网卡 Win10）23英寸',0,'2'),('48','Apple iPad mini 2 ME279CH/A',2088,1888,'products/1/c_0048.jpg','2015-11-02',0,'（配备 Retina 显示屏 7.9英寸 16G WLAN 机型 银色）',0,'2'),('49','小米（MI）7.9英寸平板',1399,1299,'products/1/c_0049.jpg','2015-11-02',0,'WIFI 64GB（NVIDIA Tegra K1 2.2GHz 2G 64G 2048*1536视网膜屏 800W）白色',0,'2'),('5','摩托罗拉 moto x（x+1）',1799,1699,'products/1/c_0005.jpg','2015-11-01',0,'摩托罗拉 moto x（x+1）(XT1085) 32GB 天然竹 全网通4G手机11月11天！MOTO X震撼特惠来袭！1699元！带你玩转黑科技！天然材质，原生流畅系统！',0,'1'),('50','Apple iPad Air 2 MGLW2CH/A',2399,2299,'products/1/c_0050.jpg','2015-11-12',0,'（9.7英寸 16G WLAN 机型 银色）',0,'2'),('6','魅族 MX5 16GB 银黑色',1899,1799,'products/1/c_0006.jpg','2015-11-02',0,'魅族 MX5 16GB 银黑色 移动联通双4G手机 双卡双待送原厂钢化膜+保护壳+耳机！5.5英寸大屏幕，3G运行内存，2070万+500万像素摄像头！长期省才是真的省！',0,'1'),('7','三星 Galaxy On7',1499,1398,'products/1/c_0007.jpg','2015-11-14',0,'三星 Galaxy On7（G6000）昂小七 金色 全网通4G手机 双卡双待新品火爆抢购中！京东尊享千元良机！5.5英寸高清大屏！1300+500W像素！评价赢30元话费券！',0,'1'),('8','NUU NU5',1288,1190,'products/1/c_0008.jpg','2015-11-02',0,'NUU NU5 16GB 移动联通双4G智能手机 双卡双待 晒单有礼 晨光金香港品牌 2.5D弧度前后钢化玻璃 随机附赠手机套+钢化贴膜 晒单送移动电源+蓝牙耳机',0,'1'),('9','乐视（Letv）乐1pro（X800）',2399,2299,'products/1/c_0009.jpg','2015-11-02',0,'乐视（Letv）乐1pro（X800）64GB 金色 移动联通4G手机 双卡双待乐视生态UI+5.5英寸2K屏+高通8核处理器+4GB运行内存+64GB存储+1300万摄像头！',0,'1');

（3）根据（1）进行
（4）代码实现

index.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<a href="/servletb/product?method=searchAll">查询所有产品</a>
</body>
</html>

Product.java

package com.zql.pojo;

import java.io.Serializable;
import java.util.Date;

/**

* author:Daniel

* version:1.0

*/

public class Product implements Serializable{
	
	private String pid;
	private String pname;
	private double market_price;
	private double shop_price;
	private String pimage;
	private Date pdate;
	private int is_hot;
	private String pdesc;
	private int pflag;
	private String cid;
	public Product() {
		super();
		// TODO Auto-generated constructor stub
	}
	public Product(String pid, String pname, double market_price, double shop_price, String pimage, Date pdate,
			int is_hot, String pdesc, int pflag, String cid) {
		super();
		this.pid = pid;
		this.pname = pname;
		this.market_price = market_price;
		this.shop_price = shop_price;
		this.pimage = pimage;
		this.pdate = pdate;
		this.is_hot = is_hot;
		this.pdesc = pdesc;
		this.pflag = pflag;
		this.cid = cid;
	}
	public String getPid() {
		return pid;
	}
	public void setPid(String pid) {
		this.pid = pid;
	}
	public String getPname() {
		return pname;
	}
	public void setPname(String pname) {
		this.pname = pname;
	}
	public double getMarket_price() {
		return market_price;
	}
	public void setMarket_price(double market_price) {
		this.market_price = market_price;
	}
	public double getShop_price() {
		return shop_price;
	}
	public void setShop_price(double shop_price) {
		this.shop_price = shop_price;
	}
	public String getPimage() {
		return pimage;
	}
	public void setPimage(String pimage) {
		this.pimage = pimage;
	}
	public Date getPdate() {
		return pdate;
	}
	public void setPdate(Date pdate) {
		this.pdate = pdate;
	}
	public int getIs_hot() {
		return is_hot;
	}
	public void setIs_hot(int is_hot) {
		this.is_hot = is_hot;
	}
	public String getPdesc() {
		return pdesc;
	}
	public void setPdesc(String pdesc) {
		this.pdesc = pdesc;
	}
	public int getPflag() {
		return pflag;
	}
	public void setPflag(int pflag) {
		this.pflag = pflag;
	}
	public String getCid() {
		return cid;
	}
	public void setCid(String cid) {
		this.cid = cid;
	}
	@Override
	public String toString() {
		return "Product [pid=" + pid + ", pname=" + pname + ", market_price=" + market_price + ", shop_price="
				+ shop_price + ", pimage=" + pimage + ", pdate=" + pdate + ", is_hot=" + is_hot + ", pdesc=" + pdesc
				+ ", pflag=" + pflag + ", cid=" + cid + "]";
	}
}

Category.java

package com.zql.pojo;

import java.io.Serializable;

/**

* author:Daniel

* version:1.0

* time:2022年6月26日

*/

public class Category implements Serializable{
	
	private String cid;
	private String cname;
	public Category() {
		super();
		// TODO Auto-generated constructor stub
	}
	public Category(String cid, String cname) {
		super();
		this.cid = cid;
		this.cname = cname;
	}
	public String getCid() {
		return cid;
	}
	public void setCid(String cid) {
		this.cid = cid;
	}
	public String getCname() {
		return cname;
	}
	public void setCname(String cname) {
		this.cname = cname;
	}
	@Override
	public String toString() {
		return "Category [cid=" + cid + ", cname=" + cname + "]";
	}
}

ProductServlet.java

package com.zql.servlet;

import java.io.IOException;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.zql.pojo.Product;
import com.zql.service.ProductService;

@WebServlet("/product")
public class ProductServlet extends HttpServlet {
	public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		//解决请求和响应乱码问题
		request.setCharacterEncoding("UTF-8");
		response.setContentType("text/html;charset=utf-8");
		//准备数据
		String method = request.getParameter("method");
		
		if("searchAll".equals(method)) {
			
			searchAll(request,response);
		}

	}

	private void searchAll(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		
		//System.out.println("searchAll");
		//业务处理
		ProductService service = new ProductService();
		List<Product> plist = service.searchAll();
		//页面展示
		//存到域中
		request.setAttribute("plist", plist);
		request.getRequestDispatcher("/plist.jsp").forward(request, response);
		
	}

	public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doGet(request, response);
	}
}

ProductService.java

package com.zql.service;

import java.util.List;

import com.zql.dao.ProductDao;
import com.zql.pojo.Product;

/**

* author:Daniel

* version:1.0

* time:2022年6月26日

*/

public class ProductService {
	
	ProductDao dao = new ProductDao();

	public List<Product> searchAll() {
		
		List<Product> plist = dao.searchAll();
		return plist;
	}
}

ProductDao.java

package com.zql.dao;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import com.zql.pojo.Product;
import com.zql.utils.C3P0Utils;

/**

* author:Daniel

* version:1.0

* time:2022年6月26日

*/

public class ProductDao {

	public List<Product> searchAll() {
		try {
			
		QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
		
		String sql = "select * from product";
		
		
			List<Product> plist = qr.query(sql, new BeanListHandler<Product>(Product.class));
			
			return plist;
			
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		return null;
	}
}

plist.jsp（资料中已有导入修改）

<%@ page language="java" contentType="text/html; charset=utf-8"
    pageEncoding="utf-8"%>
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Insert title here</title>
<link rel="stylesheet" type="text/css" href="${pageContext.request.contextPath}/css/imagetable.css">
</head>
<body></body>
	<table  border="1" width="40%" class="imagetable" align="center">
		<tr>
			<th>商品列表</th>
		</tr>
	</table>
	<hr/>
	<table border="1" width="100%" class="imagetable">
		<tr>
			<th colspan="7" align="right">
				<input type="button" value="添加商品"/>
				<input type="button" value="删除选中商品"/>
			</th>
		</tr>
		<tr>
			<th></th>
			<th>商品序号</th>
			<th>商品名称</th>
			<th>商品图片</th>
			<th>商品价格</th>
			<th>商品描述</th>
			<th>操作</th>
		</tr>
			
				<c:if test="${not empty plist}">
					<c:forEach items="${plist}" var="v" varStatus="ps">
						<tr>
							<td><input type="checkbox" name="tableData" value=""/>
							<td>${ps.count}</td>
							<td>${v.pname }</td>
							<td><img src="/servletb/${v.pimage }" width="100%" height="100%"/></td>
							<td>${v.shop_price}</td>
							<td>${v.pdesc}</td>
							<td align="center">
								<a href="">修改商品</a>
								<a href="">删除商品</a>
							</td>
						</tr>
					</c:forEach>	
				</c:if>
	</table>
</body>
</html>

效果显示：http://localhost:8080/servletb/index.jsp

二、案例二：添加商品（两步走）

2.1、流程分析

2.2、案例代码实现

2.2.1 第一步：先要查询添加页面出现的商品分类

（1）导入资料添加sava.jsp页面
（2）在plist.jsp添加处添加添加方法onclick="tosave()"

（3）在plist.jsp中实现页面跳转js代码，其它不变

<script type="text/javascript">
		function tosave() {
			//location.href="/servletb/save.jsp"
			location.href="/servletb/product?method=tosave";
		}
</script>

（4）编写后端代码

ProductServlet.java

package com.zql.servlet;

import java.io.IOException;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.zql.pojo.Category;
import com.zql.pojo.Product;
import com.zql.service.CategoryService;
import com.zql.service.ProductService;

@WebServlet("/product")
public class ProductServlet extends HttpServlet {
	public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		//解决请求和响应乱码问题
		request.setCharacterEncoding("UTF-8");
		response.setContentType("text/html;charset=utf-8");
		//准备数据
		String method = request.getParameter("method");
		
		if("searchAll".equals(method)) {
			
			searchAll(request,response);
		}else if("tosave".equals(method)) {
			tosave(request,response);
		}

	}
	//查询所有分类
	private void tosave(HttpServletRequest request, HttpServletResponse response)  throws ServletException, IOException{
		//业务处理
		CategoryService service = new CategoryService();
		List<Category> clist = service.searchCategory();
		//页面展示
		//存到域中
		request.setAttribute("clist", clist);
		//转发到plist.jsp页面
		request.getRequestDispatcher("/save.jsp").forward(request, response);
		
	}

	//查询所有产品
	private void searchAll(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		
		//System.out.println("searchAll");
		//业务处理
		ProductService service = new ProductService();
		List<Product> plist = service.searchAll();
		//页面展示
		//存到域中
		request.setAttribute("plist", plist);
		//转发到plist.jsp页面
		request.getRequestDispatcher("/plist.jsp").forward(request, response);
		
	}

	public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doGet(request, response);
	}
}

CategoryService.java

package com.zql.service;

import java.util.List;

import com.zql.dao.CategoryDao;
import com.zql.pojo.Category;
import com.zql.pojo.Product;

/**

* author:Daniel

* version:1.0

* time:2022年6月26日

*/

public class CategoryService {
	//查询所有分类
	CategoryDao dao = new CategoryDao();

	public List<Category> searchCategory() {
		List<Category> clist = dao.searchCategory();
		
		return clist;
	}
}

CategoryDao.java

package com.zql.dao;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import com.zql.pojo.Category;
import com.zql.utils.C3P0Utils;

/**

* author:Daniel

* version:1.0

* time:2022年6月26日

*/

public class CategoryDao {
	
	//查询所有分类

	public List<Category> searchCategory() {
		try {
			
		QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
		
		String sql = "select * from category";
		
		List<Category> clist = qr.query(sql, new BeanListHandler<Category>(Category.class));
		
		return clist;
		
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		return null;
	}
}

save.jsp页面代码变动部分

<tr>
	<th>商品分类</th>
	<th align="left">
		<select name="cid">
			<c:if test="${not empty clist}">
				<c:forEach items="${clist}" var="v">
					<option value="${v.cid}">${v.cname}</option>
				</c:forEach>
			</c:if>
		</select>
	</th>
</tr>

（5）效果实现

JavaWeb动态获取项目名 ${pageContext.request.contextPath}

2.2.1 第二步：添加商品

（1）将所有项目名换成动态获取项目名
（2）添加方法到save.jsp

<form action="${pageContext.request.contextPath}/product" method="post">
		<!-- 隐藏域 -->
		<input type="hidden" name="method" value="save">

(3)编写ProductServlet.java(提前导入工具类 UUIDUtils.java)

//添加商品
	private void save(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException  {
		try {		//获取前台数据
			Map<String, String[]> map = request.getParameterMap();
			Product p = new Product();
			p.setPid(UUIDUtils.getUUID());
			p.setPdate(new Date().toLocaleString());
			
			BeanUtils.populate(p, map);
			//业务处理
			ProductService service = new ProductService();
			Map<String,String[]> pm = service.save(p);
			//页面展示
			//存到域中
			request.setAttribute("pm", pm);
			//重定向到plist.jsp页面
			response.sendRedirect(request.getContextPath()+"/product?method=searchAll");
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (InvocationTargetException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

ProductService.java

//添加商品
	public Map<String, String[]> save(Product p) {
		Map<String,String[]> pm = dao.save(p);
		return pm;
	}

ProductDao.java

//添加商品
	public Map<String, String[]> save(Product p) {
		
		try {
			
			QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
			
			String sql = "insert into product values(?,?,?,?,?,?,?,?,?,?)";
			
			Object[] params = {p.getPid(),p.getPname(),p.getMarket_price(),
					p.getShop_price(),p.getPimage(),p.getPdate(),p.getIs_hot(),p.getPdesc(),
					p.getPflag(),p.getCid()};
					qr.update(sql, params);
			
			} catch (SQLException e) {
				
				e.printStackTrace();
			}
			return null;
	}

添加成功显示： http://localhost:8080/servletb/index.jsp

三、案例三：商品修改（分两步走）

3.1、流程分析

3.2、案例代码实现

3.2.1 第一步 根据pid回显数据

（1）将save.jsp页面复制一个做少量修改当修改页面update.jsp（实现后可看（6））

(2) 在 首页即plist.jsp页面修改处添加 跳转修改页路径方法

<a href="${pageContext.request.contextPath }/product?method=toUpdate&pid=${v.pid}">修改商品</a>

（3）编写ProductServlet.java

//修改商品(回显商品)
	private void toUpdate(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		
		String pid = request.getParameter("pid");
		//业务处理
		//查询商品pid
		ProductService ps = new ProductService();
		Product product = ps.findById(pid);
		//查询商品分类
		CategoryService cs = new CategoryService();
		List<Category> clist = cs.searchCategory();
		//存到域中
		request.setAttribute("product", product);
		request.setAttribute("clist", clist);
		页面展示(重定向)
		request.getRequestDispatcher( "/update.jsp").forward(request, response);	
	}

(4)编写ProductService.java

//修改商品(回显商品)
	public Product findById(String pid) {
		Product product = dao.findById(pid);
		
		return product;
	}

(5)编写ProductDao.java

//修改商品(回显商品)
	public Product findById(String pid) {
		
		try {
			
			QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
			
			String sql = "select * from product where pid = ?";
			
			Product product = qr.query(sql, new BeanHandler<Product>(Product.class),pid);
				
			return product;
				
			} catch (SQLException e) {
				
				e.printStackTrace();
			}
			return null;
	}

(6)update.jsp完整代码显示：

<%@ page language="java" contentType="text/html; charset=utf-8"
    pageEncoding="utf-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Insert title here</title>
<link rel="stylesheet" type="text/css" href="${pageContext.request.contextPath}/css/imagetable.css">
</head>
<body>
	<table border="1" width="40%" class="imagetable" align="center">
		<tr>
			<th>修改商品信息</th>
		</tr>
	</table>
	<hr/>
	<form action="${pageContext.request.contextPath}/product" method="post">
		<!-- 隐藏域 -->
		<input type="hidden" name="method" value="save">
		<table border="1" width="100%" class="imagetable">
			<tr>
				<th>商品名称</th>
				<th align="left">
					<input type="text" name="pname" value="${product.pname}"/>
				</th>
			</tr>
			<tr>
				<th>市场价</th>
				<th align="left">
					<input type="text" name="market_price" value="${product.market_price}"/>
				</th>
			</tr>
			<tr>
				<th>商城价</th>
				<th align="left">
					<input type="text" name="shop_price" value="${product.shop_price}"/>
				</th>
			</tr>
			<tr>
				<th>商品图片</th>
				<th align="left">
					<input type="text" name="pimage"/>
				</th>
			</tr>
			<tr>
				<th>是否热门商品</th>
				<th align="left">
					<input type="radio" name="is_hot" <c:if test="${product.is_hot == 0}">checked</c:if>  value="0" />不热门
					<input type="radio" name="is_hot"   <c:if test="${product.is_hot == 1}">checked</c:if> value="1"/>热门
				</th>
			</tr>
			<tr>
				<th>商品是否下架</th>
				<th align="left">
					<input type="radio" name="pflag" <c:if test="${product.pflag == 0}">checked</c:if> value="0"/>不下架
					<input type="radio" name="pflag" <c:if test="${product.pflag == 1}">checked</c:if>  value="1"/>下架
				</th>
			</tr>
			<tr>
				<th>商品分类</th>
				<th align="left">
					<select name="cid">
						<c:if test="${not empty clist}">
							<c:forEach items="${clist}" var="v">
								<option <c:if test="${product.cid == v.cid}">selected</c:if>  value="${v.cid}">${v.cname}</option>
							</c:forEach>
						</c:if>
						
					</select>
				</th>
			</tr>
			<tr>
				<th>商品描述</th>
				<th align="left">
					<textarea name="pdesc">${product.pdesc}</textarea>
				</th>
			</tr>
			<tr>
				<th></th>
				<th align="left">
					<input type="submit" value="修改商品"/>
				</th>
			</tr>
		</table>
	</form>
</body>
</html>

回显显示：

3.2.2 第二步 修改数据

（1）修改update.jsp页面路径方法

<form action="${pageContext.request.contextPath}/product?method=update&pid=${product.pid}" method="post">

（2）编写ProductServlet.java

else if("update".equals(method)) {
			//修改商品
			update(request,response);
		}

//修改商品
	private void update(HttpServletRequest request, HttpServletResponse response) {
		try {		
			//获取前台数据
			Map<String, String[]> map = request.getParameterMap();
			Product p = new Product();
			BeanUtils.populate(p, map);
			//业务处理
			ProductService service = new ProductService();
			service.update(p);
			//页面展示
			//重定向到plist.jsp页面
			response.sendRedirect(request.getContextPath()+"/product?method=searchAll");
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}	
	}

ProductService.java

//修改商品
	public void update(Product p) {
		
		dao.update(p);
		
	}

//修改商品
		public void update(Product p) {
		
		try {
					
				QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
				
				String sql = "update product set pname = ?,shop_price=?,"
						+ "market_price=?,is_hot=?,pflag=?,cid=?,pdesc=? where pid=?";
				
				Object[] params = {p.getPname(),p.getShop_price(),p.getMarket_price()
						,p.getIs_hot(),p.getPflag(),p.getCid(),p.getPdesc(),p.getPid()};
						qr.update(sql, params);
						
				qr.update(sql, params);
					
				} catch (SQLException e) {
					
					e.printStackTrace();
				}	
			}

修改前后对比

四、案例四：删除某个商品

4.1、流程分析

4.2、案例代码实现

（1）在plist.jsp中添加删除的方法

<a href="${pageContext.request.contextPath }/product?method=delete&pid=${v.pid}">删除商品</a>

（2）ProductServlet.java

else if("delete".equals(method)) {
			//根据pid删除商品
			delete(request,response);
		}

//根据pid删除商品
	private void delete(HttpServletRequest request, HttpServletResponse response) {
		try {		
			//获取前台数据
			String pid = request.getParameter("pid");
			//业务处理
			ProductService service = new ProductService();
			service.delete(pid);
			//页面展示
			//重定向到plist.jsp页面
			response.sendRedirect(request.getContextPath()+"/product?method=searchAll");
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}

ProductService.java

//根据pid删除商品
	public void delete(String pid) {
		
		dao.delete(pid);
		
	}

ProductDao.java

//根据pid删除商品
		public void delete(String pid) {
			
			try {
				
				QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
				
				String sql = "delete from product where pid=?";
				
				qr.update(sql, pid);
					
				} catch (SQLException e) {
					
					e.printStackTrace();
			}
			
		}

效果显示：上面修改的数据被删除了（下面不存在上面修改的数据）

五、Mysql中的分页

应用场景:

5.1、数据库分页

分页：将数据进行分割, 分割到不同的页面来显示

Limit参数1, 参数2

参数1: 查询的起始索引(索引是从0开始)
参数2: 查询的个数(每页显示的条数)

规律:

起始索引的计算公式 = (当前页 - 1) * 每页显示的个数

Select查询的顺序

Select * from 表 where 条件 group by(分组) xx having(过滤) xx order by(排序) xx limit x,y;

SELECT * FROM category LIMIT 0,2	#第一页
SELECT * FROM category LIMIT 2,2 	#第二页
SELECT * FROM category LIMIT 4,2	#第三页

# 起始索引的计算公式 = (当前页 - 1)* 每页显示的条数

起始索引的计算公式 = (当前页 - 1)* 每页显示的条数

5.2、分页代码实现（案例拓展）

（1）在index.jsp中添加分页入口

<a href="${pageContext.request.contextPath}/product?method=searchByLimit&pageNum=1">分页查询所有产品</a>

(2)PageBean.java

package com.zql.pojo;

import java.io.Serializable;
import java.util.List;

/**

* author:Daniel

* version:1.0

* time:2022年6月26日

*/

public class PageBean implements Serializable{
	
	private int pageNum;//当前页
	private int pageSize;//每页显示的条数
	private int startIndex;//起始页索引
	private List<Product> result; //将Product封装到PageBean中
	public int getPageNum() {
		return pageNum;
	}
	public void setPageNum(int pageNum) {
		this.pageNum = pageNum;
	}
	public int getPageSize() {
		return pageSize;
	}
	public void setPageSize(int pageSize) {
		this.pageSize = pageSize;
	}
	public int getStartIndex() {
		return startIndex;
	}
	public void setStartIndex(int startIndex) {
		
		this.startIndex = (getPageNum()-1)*getPageSize();
	}
	public List<Product> getResult() {
		return result;
	}
	public void setResult(List<Product> result) {
		this.result = result;
	}
	@Override
	public String toString() {
		return "PageBean [pageNum=" + pageNum + ", pageSize=" + pageSize + ", startIndex=" + startIndex + ", result="
				+ result + "]";
	}
}

（3）ProductServlet.java

else if("searchByLimit".equals(method)) {
			//分页查询所有商品
			searchByLimit(request,response);
		}

//分页查询所有商品
	private void searchByLimit(HttpServletRequest request, HttpServletResponse response) {
		try {		
			//获取前台数据
			String pageNum = request.getParameter("pageNum");
			//业务处理
			PageBean pb = new PageBean();
			pb.setPageNum(Integer.parseInt(pageNum));//类型转换
			pb.setPageSize(3);
			ProductService service = new ProductService();
			PageBean pageBean  = service.searchByLimit(pb);
			//存储到域中
			request.setAttribute("pageBean", pageBean);
			//页面展示
			//转发到plist.jsp页面
			request.getRequestDispatcher("/plist.jsp").forward(request, response);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

(4)ProductService.java

//分页查询所有商品
	public PageBean searchByLimit(PageBean pb) {
		
		List<Product> result = dao.searchByLimit(pb);
		
		pb.setResult(result);
		
		return pb;
	}

(5)ProductDao.java

//分页查询所有商品
		public List<Product> searchByLimit(PageBean pb) {
				try {
				
				QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
				
				String sql = "select * from product limit ?,?";
				
				Object[] param = {pb.getStartIndex(),pb.getPageSize()};
				
				List<Product> list = qr.query(sql, new BeanListHandler<Product>(Product.class),param);
				
				return list;
					
				} catch (SQLException e) {
					
					e.printStackTrace();
			}
			return null;
		}

最终显示：http://localhost:8080/servletb/index.jsp