Web-第十四天 基础加强-JDBC案例【悟空教程】
今日内容介绍
今日内容学习目标
后台管理程序中,添加商品已经完成,接着我们来完成查询所有。
之前我们已经完成前台商品的查询,后台功能类似,只是展示页面不同。
-- 创建数据库
drop database if exists `day20_db`;
create database `day20_db`;
-- 使用数据库
use day20_db;
-- 创建商品表
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, #是否热门:0=不热门,1=热门
`pdesc` varchar(255) DEFAULT NULL, #商品描述
`pflag` int(11) DEFAULT 0, #商品标记:0=未下架(默认值),1=已经下架
`cid` varchar(32) DEFAULT NULL, #分类id
PRIMARY KEY (`pid`)
) ;
public class Category {
private String cid;
private String cname;
public class Product {
private String pid;
private String pname;
private Double market_price;
private Double shop_price;
private String pimage;
private Date pdate;
private Integer is_hot; // 0 不是热门 1:热门
private String pdesc;
private Integer pflag; // 0 未下架 1:已经下架
// 分类
private String category_id;
位置:/day14_findall/WebContent/admin/frame/left.jsp
public class ProductFindAllServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
//1
//2 通知service,查询所有商品
ProductService productService = new ProductService();
List<Product> allProduct = productService.findAll();
//3 选择jsp
//3.1 将查询结果存放在request作用域
request.setAttribute("allProduct", allProduct);
//3.2 请求转发
request.getRequestDispatcher("/admin/product/product_list.jsp").forward(request, response);
}
<servlet>
<servlet-name>ProductFindAllServlet</servlet-name>
<servlet-class>cn.com.javahelp.web.servlet.ProductFindAllServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>ProductFindAllServlet</servlet-name>
<url-pattern>/productFindAllServlet</url-pattern>
</servlet-mapping>
作者:杨金才 主编
当当 广告
购买
/**
* 查询所有
* @return
*/
public List<Product> findAll(){
return productDao.findAll();
}
/**
* 查询所有
* @return
*/
public List<Product> findAll(){
try {
QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());
String sql = "select * from product";
Object[] params = {};
return queryRunner.query(sql, new BeanListHandler<Product>(Product.class), params);
} catch (Exception e) {
throw new RuntimeException(e);
}
}
页面位置:/day13_findall/WebContent/admin/product/product_list.jsp
<%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<%--列表项 start --%>
<c:forEach items="${allProduct}" var="product">
<tr onmouseover="this.style.backgroundColor = '#F5FAFE'"
onmouseout="this.style.backgroundColor = '#fff';">
<td style="CURSOR: hand; HEIGHT: 22px">
<img src="${pageContext.request.contextPath}/images/products/duanxue3.png" style="height: 140px;width:100px" />
</td>
<td style="CURSOR: hand; HEIGHT: 22px">
${product.category_id}
</td>
<td style="CURSOR: hand; HEIGHT: 22px">
${product.pname}
</td>
<td style="CURSOR: hand; HEIGHT: 22px">
${product.price}
</td>
<td style="CURSOR: hand; HEIGHT: 22px">
${product.description}
</td>
<td style="HEIGHT: 22px">
<a href="../user/edit.html?userID=15">
<img src="${pageContext.request.contextPath}/images/i_edit.gif" border="0" style="CURSOR: hand">
</a>
</td>
<td style="HEIGHT: 22px">
<a href="../user/view.html?userID=15">
<img src="${pageContext.request.contextPath}/images/button_view.gif" border="0" style="CURSOR: hand">
</a>
</td>
<td style="HEIGHT: 22px">
<a href="../user/list.html?userID=15">
<img src="${pageContext.request.contextPath}/images/i_del.gif" width="16" height="16" border="0" style="CURSOR: hand">
</a>
</td>
</tr>
</c:forEach>
<%--列表项 end --%>
日常生活中,我们浏览电商(京东、淘宝等)网站时,浏览器的商品会被后台管理员提前录入到系统中,接下来,我们一起学习后台管理员添加商品的整个流程。添加的过程需要注意进行分类的选择,如果没有需要分类,可以同时添加。
1. 页面执行“/productAddUIServlet”显示“product_add.jsp”JSP页面,在执行servlet时,需要通过查询所有分类。
2. 在“product_add.jsp”页面中,可以选择已有分类。
3. 提交添加表单到“/productAllServlet”,我们将表单数据封装到两个JavaBean(Product),然后执行添加商品(add(product))
4. 添加成功后,重定向到查询
文件位置:/day20/WebContent/admin/frame/left.jsp
public class ProductAddUIServlet extends HttpServlet {
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
//1 获得请求参数,并封装(此操作没有)
//2 查询所有分类
CategoryService categoryService = new CategoryService();
List<Category> allCategory = categoryService.findAll();
//3 选择视图,product_add.jsp
//3.1 将查询结果存放到request作用域
request.setAttribute("allCategory", allCategory);
//3.2 请求转发到jsp页面
request.getRequestDispatcher("/admin/product/product_add.jsp").forward(request, response);
}
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doGet(request, response);
}
}
<servlet>
<servlet-name>ProductAddUIServlet</servlet-name>
<servlet-class>cn.com.javahelp.web.servlet.ProductAddUIServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>ProductAddUIServlet</servlet-name>
<url-pattern>/productAddUIServlet</url-pattern>
</servlet-mapping>
public class CategoryService {
private CategoryDao categoryDao = new CategoryDao();
/**
* 查询所有
* @return
*/
public List<Category> findAll(){
return categoryDao.findAll();
}
}
public class CategoryDao {
/**
* 查询所有
* @return
*/
public List<Category> findAll(){
try {
QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());
String sql = "select * from category";
Object[] params = {};
return queryRunner.query(sql, new BeanListHandler<Category>(Category.class), params);
} catch (Exception e) {
throw new RuntimeException(e);
}
}
位置:/day20/WebContent/admin/product/product_add.jsp
<select id="categorySelectId" name="cid" style="width: 30%">
<option value="">---选择已有分类---</option>
<c:forEach items="${allCategory}" var="category">
<option value="${category.cid}">${category.cname}</option>
</c:forEach>
</select>
<form action="${pageContext.request.contextPath}/AddProductServlet" method="post">
商品名称:<input type="text" name="pname" />
是否热门:
<select name="is_hot">
<option value="1">是</option>
<option value="0">否</option>
</select>
市场价格:<input type="text" name="market_price" />
商城价格:<input type="text" name="shop_price" />
所属的分类:
<select name="cid">
<c:forEach var="c" items="${ list }">
<option value="${ c.cid }">${ c.cname }</option>
</c:forEach>
</select>
商品描述:<textarea name="pdesc" rows="5" cols="30"></textarea>
public class ProductAddServlet extends HttpServlet {
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
//0编码
request.setCharacterEncoding("UTF-8");
response.setContentType("text/html;charset=UTF-8");
//1 封装数据到商品
Product product = MyBeanUtils.populate(Product.class, request.getParameterMap());
//2 添加商品
ProductService productService = new ProductService();
productService.add(product);
//3 重定向到查询页
response.sendRedirect(request.getContextPath() + "/productFindAllServlet");
}
<servlet>
<servlet-name>ProductAddServlet</servlet-name>
<servlet-class>cn.com.javahelp.web.servlet.ProductAddServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>ProductAddServlet</servlet-name>
<url-pattern>/productAddServlet</url-pattern>
</servlet-mapping>
public class ProductService {
private ProductDao productDao = new ProductDao();
/**
* 给指定的分类添加商品
* @param product
*/
public void add(Product product) {
//2 给分类添加书籍
productDao.save(product);
}
}
public class ProductDao {
/**
* 保存商品
* @param category
*/
public void save(Product product){
try {
QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());
String sql = "insert into product(pid,pname,price, pimage,description,category_id) values(?,?,?, ?,?,?)";
Object[] params = {product.getPid() , product.getPname() , product.getPrice(),
product.getPimage() ,product.getDescription() ,product.getCategory_id()};
queryRunner.update(sql, params);
} catch (Exception e) {
throw new RuntimeException(e);
}
}
在日常的上网中,我们都修改的“用户信息”,首先可以看到上次填写的信息,然后将不符合的内容修改掉,最后保存修改。整个过程就是“修改”的操作流程。
1. 通过标识ID查询详细信息
2. 在修改表单中回显具体信息
3. 用户修改表单信息,并提交表单
4. 将表单数据,为ID为标识更新到数据库。
从查询所有列表点击编辑,浏览器发送请求“/productEditUIServlet?pid=1”并将需要查询的商品的id传递给服务器,servlet获得商品id,通过id查询商品详情,然后将查询结果存放到request作用域,使用请求转发,将数据显示在“product_edit.jsp”的表单中,并提供隐藏字存放商品id。当用户修改好数据后,提交表单到“/productEditServlet”进行编辑处理。
<%--编辑 --%>
<a href="${pageContext.request.contextPath}/productEditUIServlet?pid=${product.pid}">
<img src="${pageContext.request.contextPath}/images/i_edit.gif" … >
</a>
public class ProductEditUIServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
//1 获得商品id
String pid = request.getParameter("pid");
//2.1 通过id查询商品详情
ProductService productService = new ProductService();
Product product = productService.findById(pid);
//2.1 通过所有的分类
CategoryService categoryService = new CategoryService();
List<Category> allCategory = categoryService.findAll();
//3.1 将查询数据存放在request作用域
request.setAttribute("product", product);
request.setAttribute("allCategory", allCategory);
//3.2 请求转发到jsp页面
request.getRequestDispatcher("/admin/product/product_edit.jsp").forward(request, response);
}
<servlet>
<servlet-name>ProductEditUIServlet</servlet-name>
<servlet-class>cn.com.javahelp.web.servlet.ProductEditUIServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>ProductEditUIServlet</servlet-name>
<url-pattern>/productEditUIServlet</url-pattern>
</servlet-mapping>
/**
* 通过id查询详情
* @return
*/
public Product findById(String pid){
return productDao.findById(pid);
}
/**
* 通过id查询详情,含分类
* * 编辑时,所有分类列表中,当前分类需要选中
* @return
*/
public Product findById(String pid){
try {
QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());
String sql = "select * from product p , category c where p.category_id = c.cid and p.pid = ?";
Object[] params = {pid};
return queryRunner.query(sql, new BeanHandler<Product>(Product.class), params);
} catch (Exception e) {
throw new RuntimeException(e);
}
}
表单回显概要如下:
<form action="${pageContext.request.contextPath}/productEditServlet" method="post" >
<%--提供隐藏字段 --%>
<input type="hidden" name="pid" value="${product.pid}"/>
<select id="categorySelectId" name="cid" style="width: 30%">
<option value="">---选择已有分类---</option>
<c:forEach items="${allCategory}" var="category">
<%-- 将当前商品对应的分类选中 --%>
<option ${product.category.cname == category.cname ? "selected='selected'" : ""} value="${category.cid}">${category.cname}</option>
</c:forEach>
</select>
商品名称:<input type="text" name="pname" value="${product.pname}" …/>
商品价格:<input type="text" name="price" value="${product.price}" …/>
商品描述:<textarea name="description" …>${product.description}</textarea>
public class ProductEditServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
// 0 编码
request.setCharacterEncoding("UTF-8");
// 1 获得数据,并封装
Product product =MyBeanUtils.populate(Product.class, request.getParameterMap());
// 2 编辑操作
ProductService productService = new ProductService();
productService.edit(product);
// 3 重定向查询所有
response.sendRedirect(request.getContextPath() + "/productFindAllServlet");
}
<servlet>
<servlet-name>ProductEditServlet</servlet-name>
<servlet-class>cn.com.javahelp.web.servlet.ProductEditServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>ProductEditServlet</servlet-name>
<url-pattern>/productEditServlet</url-pattern>
</servlet-mapping>
/**
* 编辑商品
* @param category
*/
public void edit(Product product){
productDao.update(product);
}
/**
* 更新商品
* @param category
*/
public void update(Product product){
try {
QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());
String sql = "update product set pname=?,price=?, pimage=?,description=?,category_id=? where pid=?";
Object[] params = {product.getPname() , product.getPrice(),
product.getPimage() ,product.getDescription() ,product.getCategory().getCid(),
product.getPid()};
queryRunner.update(sql, params);
} catch (Exception e) {
throw new RuntimeException(e);
}
}
<a href="${pageContext.request.contextPath}/ProductDeleteServlet?pid=${product.pid}">
public class ProductDeleteServlet extends HttpServlet {
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
//1 获得所有的id
String pid = request.getParameter("pid");
//2 删除
ProductService productService = new ProductService();
productService.delete(pid);
//3重定向查询所有
response.sendRedirect(request.getContextPath() + "/productFindAllServlet");
}
/**
* 删除所有
* @param pids
*/
public void delete(String pid) {
productDao.delete(pid);
}
/**
* 通过id删除
* @param pid
*/
public void delete(String pid) {
try {
QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());
//删除sql语句
String sql = "delete from product where pid = ?";
queryRunner.update(sql, pid);
} catch (Exception e) {
throw new RuntimeException(e);
}
}
今日内容介绍
今日内容学习目标
<input type="checkbox" name="pid" value="${product.pid}" />
添加id属性:<a id="deleteAllId" href="javascript:void(0)">删除选中</a>
<script type="text/javascript">
$(function(){
//绑定点击事件
$("#deleteAllId").click(function(){
//所有选中的checkbox
var all = $("input[name='pid']:checked");
if(all.length == 0){
alert("请选择需要上传的商品");
return false;
}
//
if(window.confirm("您确定要删除所选"+all.length+"个商品吗?")){
//获得所有的id串
//注:serialize()为jquery的方法 将元素序列化成 id=xx&id=yy的格式
var ids = all.serialize();
location.href="${pageContext.request.contextPath}/productDeleteServlet?" + ids;
} else {
$("input[name='pid']").removeProp("checked");
}
});
});
</script>
public class ProductDeleteAllServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
//1 获得所有的id
String[] pids = request.getParameterValues("pid");
//2 删除
ProductService productService = new ProductService();
productService.deleteAll(pids);
//3重定向查询所有
response.sendRedirect(request.getContextPath() + "/productFindAllServlet");
}
/**
* 删除所有
* @param pids
*/
public void deleteAll(String... pids) {
productDao.delete(pids);
}
/**
* 删除所有
* @param pids
*/
public void deleteAll(String... pids) {
try {
QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());
//删除sql语句
String sql = "delete from product where pid = ?";
//将所有的pid拼凑一个二维数组,列表示id,行表示个数
Object[][] params = new Object[pids.length][];
for (int i = 0; i < pids.length; i++) {
params[i] = new Object[]{pids[i]};
}
queryRunner.batch(sql, params);
} catch (Exception e) {
throw new RuntimeException(e);
}
}
实际开发中,条件查询时非常常见的一种功能,上一个案例中我们已经完成。接着我们再来学习另一种也非常常见的功能:分页查询。
格式:select * from 表 limit ?,?;
参数1:开始索引 startIndex ,默认值:0。必须是正数。
参数2:每页显示个数 pageSize
例如:
select * from products limit 0,5; #第一页,每页显示5条。
select * from products limit 5,5; #第二页,每页显示5条。
select * from products limit 10,5; #第三页,每页显示5条。
select * from products limit ?,5; #第pageNumber页,每页显示5条。
startIndex = (pageNumber - 1) * pageSize;
public class PageBean<T> {
private int pageNumber; //当前页(浏览器传递)
private int pageSize; //每页显示个数(固定值,也可以是浏览器传递)
private int totalRecord; //总记录数(数据库查询)
private int totalPage; //总分页数
private int startIndex; //开始索引
private List<T> data; //分页数据(数据库查询)
/**
* 为了约束使用者,必须传递两个参数,不提供无参构造
* @param pageNumber
* @param pageSize
*/
public PageBean(int pageNumber, int pageSize) {
super();
this.pageNumber = pageNumber;
this.pageSize = pageSize;
}
public int getStartIndex() {
//开始索引计算
startIndex = (pageNumber - 1) * pageSize;
return startIndex;
}
public int getTotalPage() {
//总页数计算
if(totalRecord % pageSize == 0){
//整除
totalPage = totalRecord / pageSize ;
} else {
//有余数 + 1 (半页)
totalPage = totalRecord / pageSize + 1;
}
return totalPage;
}
//其他getter和setter方法省略
http://localhost:8080/day14_page/productFindAllServlet?pageNumber=2
public class ProductFindAllServlet extends HttpServlet {
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
//1 获得分页参数
String pageNumberStr = request.getParameter("pageNumber");
Integer pageNumber = 1 ;//处理没有传递参数的情况
try {
pageNumber = Integer.parseInt(pageNumberStr);
} catch (Exception e) {
}
Integer pageSize = 2;
//固定值,Integer.parseInt(request.getParameter("pageSize"));
//2 通知service,查询所有商品
ProductService productService = new ProductService();
PageBean<Product> pageBean = productService.findAll(pageNumber,pageSize);
//3 选择jsp
//3.1 将查询结果存放在request作用域
request.setAttribute("pageBean", pageBean);
//3.2 请求转发
request.getRequestDispatcher("/admin/product/product_list.jsp").forward(request, response);
}
/**
* 查询所有,分页
* @return
*/
public PageBean<Product> findAll(int pageNumber , int pageSize){
//1 总分页数、开始索引,已经在PageBean中处理了
PageBean<Product> pageBean = new PageBean<Product>(pageNumber, pageSize);
//2 查询总记录数
int totalRecord = productDao.getTotalRecord();
//将总记录数封装到pageBean中
pageBean.setTotalRecord(totalRecord );
//3 查询分页数据
List<Product> data = productDao.findAll(pageBean.getStartIndex(),pageBean.getPageSize());
// * 将分页数据封装到PageBean中
pageBean.setData(data);
//4 返回封装好的数据
return pageBean;
}
/**
* 查询总记录数
* @return
*/
public int getTotalRecord() {
try {
QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());
String sql = "select count(*) from product";
Object[] params = {};
Long numLong = (Long) queryRunner.query(sql, new ScalarHandler(), params);
return numLong.intValue();
} catch (Exception e) {
throw new RuntimeException(e);
}
}
/**
* 分页查询所有
* @return
*/
public List<Product> findAll(int startIndex, int pageSize) {
try {
QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());
String sql = "select * from product limit ?,?";
Object[] params = {startIndex ,pageSize};
return queryRunner.query(sql, new BeanListHandler<Product>(Product.class), params);
} catch (Exception e) {
throw new RuntimeException(e);
}
}
位置:/day14_page/WebContent/admin/product/product_list.jsp
第${ pageBean.pageNumber }/${ pageBean.totalPage }页
总记录数:${pageBean.totalRecord } 每页显示:${ pageBean.pageSize }
<c:if test="${ pageBean.pageNumber gt 1 }">
<a href="${ pageContext.request.contextPath }/productListPageServlet?pageNumber=1">[首页]</a> |
<a href="${ pageContext.request.contextPath }/productListPageServlet?pageNumber=${pageBean.pageNumber-1}">[上一页]</a>|
</c:if>
<c:forEach var="i" begin="1" end="${pageBean.totalPage }">
<c:if test="${ pageBean.pageNumber == i }">
${ i }
</c:if>
<c:if test="${ pageBean.pageNumber != i }">
<a href="${ pageContext.request.contextPath }/productListPageServlet?pageNumber=${ i}">${ i }</a>
</c:if>
</c:forEach>
<c:if test="${ pageBean.pageNumber lt pageBean.totalPage }">
<a href="${ pageContext.request.contextPath }/productListPageServlet?pageNumber=${pageBean.pageNumber+1}">[下一页]</a>|
<a href="${ pageContext.request.contextPath }/productListPageServlet?pageNumber=${pageBean.totalPage}">[尾页]</a>
</c:if>
我们已经完成了“查询所有商品”功能,如果我们希望从所有数据中筛选出需要的部分数据,会发现通过肉眼比较困难,且没有必要。在开发中,我们采用“条件查询”来解决此类问题。
“条件查询”和“查询所有”,两个功能非常相似,不同出就在条件的筛选上,通过页面将分类和商品名称传递给服务器,服务器获得查询条件,然后通知service拼凑sql语句进行具体查询。
我们发现如果要完成“条件查询”,必须在之前的“查询所有”时,查询所有的分类,从而可以进行条件的选择。
修改:/day14_condition/src/cn/com/javahelp/web/servlet/ProductFindAllServlet.java
public class ProductFindAllServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
//1
//2.1 通知service,查询所有商品
ProductService productService = new ProductService();
List<Product> allProduct = productService.findAll();
//2.2 通过分类service,查询所有分类
CategoryService categoryService = new CategoryService();
List<Category> allCategory = categoryService.findAll();
//3 选择jsp
//3.1 将查询结果,所有商品,存放在request作用域
request.setAttribute("allProduct", allProduct);
//3.2 将查询结果,所有分类,存放在request作用域
request.setAttribute("allCategory", allCategory);
//3.3 请求转发
request.getRequestDispatcher("/admin/product/product_list.jsp").forward(request, response);
}
修改:/day14_condition/WebContent/admin/product/product_list.jsp
<c:forEach items="${allCategory}" var="category">
<option value="${category.cid}">${category.cname}</option>
</c:forEach>
<form action="${pageContext.request.contextPath}/productFindAllServlet" method="post">
分类 <select name="category_id">
商品名称:<input type="text" name="pname" size="15"/>
<button type="submit" value="查询">查询</button>
位置:/day14_condition/src/cn/con/javahelp/web/servlet/ProductFindAllServlet.java
此时ProductService的findAll(product) 不存在所以抛异常。
public class ProductFindAllServlet extends HttpServlet {
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
//1 获得请求参数
Product product = MyBeanUtils.populate(Product.class, request.getParameterMap());
//2.1 通知service,查询所有商品
ProductService productService = new ProductService();
List<Product> allProduct = productService.findAll(product);
//2.2 ...省略
/**
* 查询所有(含条件)
* @return
*/
public List<Product> findAll(Product product) throws SQLException {
return productDao.findAll(product);
}
/**
* 查询所有
* @return
*/
public List<Product> findAll(Product product) throws SQLException {
//1.1 拼凑sql语句,格式:and 属性 符号 ?
StringBuilder builder = new StringBuilder();
//1.2 拼凑实际参数。实际参数可重复、参数需要顺序,所以选择List集合
List<Object> paramsList = new ArrayList<Object>();
//2.1 分类不为空 ,添加分类条件
if(product.getCategory_id() != null && !"".equals(product.getCategory_id())){
builder.append(" and category_id = ? ");
paramsList.add(product.getCategory_id());
}
//2.2 商品名不为空,添加商品名条件
if(product.getPname() != null && !"".equals(product.getPname())){
builder.append(" and pname like ? ");
//商品名需要模糊查询即可,需要手动的添加%
paramsList.add("%"+product.getPname()+"%");
}
//3 转换成需要的类型
String condition = builder.toString();
Object[] params = paramsList.toArray();
QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());
String sql = "select * from product where 1=1 " + condition;
return queryRunner.query(sql, new BeanListHandler<Product>(Product.class), params);
}
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
//1 获得请求参数
Product product = MyBeanUtils.populate(Product.class, request.getParameterMap());
//2.1 通知service,查询所有商品
ProductService productService = new ProductService();
List<Product> allProduct = productService.findAll(product);
//2.2 通过分类service,查询所有分类
CategoryService categoryService = new CategoryService();
List<Category> allCategory = categoryService.findAll();
//3 选择jsp
//3.1 将查询结果,所有商品,存放在request作用域
request.setAttribute("allProduct", allProduct);
//3.2 将查询结果,所有分类,存放在request作用域
request.setAttribute("allCategory", allCategory);
//3.3 存放条件
request.setAttribute("product", product);
//3.4 请求转发
request.getRequestDispatcher("/admin/product/product_list.jsp").forward(request, response);
}
/day14_condition/WebContent/admin/product/product_list.jsp
分类
<c:forEach items="${allCategory}" var="category">
<option value="${category.cid}"${product.category_id == category.cid ? "selected='selected'" : ""} >
${category.cname}
</option>
</c:forEach>
商品名称:
<input type="text" name="pname" size="15" value="${product.pname}"/>