1.列表查询
1.完成条件查询
2.完成翻页功能,翻页带条件,导航栏保持5个
3.当前页是选中状态。第一页时首页和上一页不能点,最后一页时末页下一页不能点
4.删除
2.修改和添加
1.修改回填数据
2.进货日期不能修改
3.修改成功回列表页,修改失败还在当前页
代码
实体类
/**
* 商品
*/
public class Goods {
private Integer id; // 商品编号
private String name; // 商品名称
private Double price; // 价格
private Date purchaseDate; // 上货时间
private Integer categoryId; // 商品种类编号
private String categoryName;
private Integer status; // 商品状态 1销售 0下架
public Goods() {
}
// 省略带参构造和get set
/**
* 商品分类
*/
public class Category {
private Integer id; // 分类编号
private String name; // 分类名称
public Category() {
}
// 省略带参构造和get set
/**
* 进货时间条件查询
*/
public class Criteria extends Goods{
private Date startTime; // 起始时间
private Date endTime; // 终止时间
public Criteria() {
}
// 省略带参构造和get set
/**
* 查询列表分页显示
*/
public class Page {
private Integer pageNum; //当前第几页
private Integer pageSize; //每页几条
private Integer totalCount; //总共几条
private Integer totalPages; // 总共几页
private Integer startRows; //从第几行开始
public Page() {
}
public Page(Integer pageNum, Integer pageSize) {
this.setPageNum(pageNum);
this.setPagesize(pageSize);
this.startRows = (this.pageNum - 1) * this.pageSize;
}
public Integer getPageNum() {
return pageNum;
}
public void setPageNum(Integer pageNum) {
if (pageNum == null || pageNum < 0) {
pageNum = 1;
}
this.pageNum = pageNum;
}
public Integer getPageSize() {
return pageSize;
}
public void setPagesize(Integer pageSize) {
//当pageSize过大过小时,默认为2
if (pageSize == null || pageSize < 0 || pageSize > 5) {
pageSize = 2;
}
this.pageSize = pageSize;
}
public Integer getTotalCount() {
return totalCount;
}
public void setTotalCount(Integer totalCount) {
this.totalCount = totalCount;
//计算总页数
if (this.totalCount % this.pageSize == 0) {
this.totalPages = this.totalCount / this.pageSize;
} else {
this.totalPages = this.totalCount / this.pageSize + 1;
}
}
public Integer getTotalPages() {
return totalPages;
}
public void setTotalPages(Integer totalPages) {
this.totalPages = totalPages;
}
public Integer getStartRows() {
return startRows;
}
public void setStartRows(Integer startRows) {
this.startRows = startRows;
}
}
Dao包
public class GoodsDao {
private QueryRunner queryRunner = new QueryRunner(DBUtils.getDataSource());
/**
* 条件查询所有货物信息
*
* @param criteria 时间范围
* @param offset 该页起始数据
* @param pageSize 该页显示数据条数
* @return
*/
public List<Goods> getGoodsList(Criteria criteria, Integer offset, Integer pageSize) {
String sql = "select g.id,g.name,g.price,g.purchase_date purchaseDate,g.status,c.id categoryId,c.name categoryName " +
" from goods g inner join category c on g.category_id=c.id " +
" where 1=1 ";
List param = new ArrayList();
sql = getSql(sql, criteria, param);
sql += "limit ?,? ";
param.add(offset);
param.add(pageSize);
try {
return queryRunner.query(sql, new BeanListHandler<>(Goods.class), param.toArray());
} catch (SQLException e) {
e.printStackTrace();
}
return new ArrayList<>();
}
/**
* 计算数据数量
* @param criteria
* @return
*/
public long getCount(Criteria criteria){
String sql = "select count(*) " +
" from goods g inner join category c on g.category_id=c.id where 1=1 ";
List param = new ArrayList();
sql = getSql(sql,criteria,param);
try {
return queryRunner.query(sql,new ScalarHandler<>(),param.toArray());
} catch (SQLException e) {
e.printStackTrace();
}
return 0;
}
/**
* 拼接SQL语句,用于条件查询
*
* @param sql 查询语句
* @param criteria 时间范围
* @param param 其他条件
* @return
*/
public String getSql(String sql, Criteria criteria, List param) {
if (criteria.getCategoryId() != null) {
sql += " and g.category_id=? ";
param.add(criteria.getCategoryId());
}
if (criteria.getStatus() != null) {
sql += " and g.status=? ";
param.add(criteria.getStatus());
}
if (criteria.getStartTime() != null) {
sql += " and g.purchase_date>=? ";
param.add(criteria.getStartTime());
}
if (criteria.getEndTime() != null) {
sql += " and g.purchase_date<=? ";
param.add(criteria.getEndTime());
}
if (criteria.getName() != null) {
sql += " and g.name like ? ";
param.add("%" + criteria.getName() + "%");
}
return sql;
}
/**
* 添加货物
* @param goods
* @return
*/
public int addGoods(Goods goods){
String sql = "insert into goods(name,price,purchase_date,category_id,status) values(?,?,?,?,?)";
try {
return queryRunner.update(sql,goods.getName(),goods.getPrice(),goods.getPurchaseDate(),goods.getCategoryId(),goods.getStatus());
} catch (SQLException e) {
e.printStackTrace();
}
return 0;
}
/**
* 查询一条货物数据
* 用于更新时回填
* @param id
* @return
*/
public Goods getGoodsById(Integer id){
String sql = "select g.id,g.name,g.price,g.purchase_date purchaseDate,g.status,c.id categoryId,c.name categoryName " +
" from goods g inner join category c on g.category_id=c.id " +
" where g.id=? ";
try {
return queryRunner.query(sql,new BeanHandler<>(Goods.class),id);
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
/**
* 更新货物信息
* @param goods
* @return
*/
public int updateGoods(Goods goods){
String sql = "update goods set name=?,price=?,category_id=?,status=? where id=?";
try {
return queryRunner.update(sql,goods.getName(),goods.getPrice(),goods.getCategoryId(),goods.getStatus(),goods.getId());
} catch (SQLException e) {
e.printStackTrace();
}
return 0;
}
/**
* 删除货物
* @param id
* @return
*/
public int deleteGoods(Integer id){
String sql = "delete from goods where id=?";
try {
return queryRunner.update(sql,id);
} catch (SQLException e) {
e.printStackTrace();
}
return 0;
}
}
public class CategoryDao {
private QueryRunner queryRunner = new QueryRunner(DBUtils.getDataSource());
/**
* 获取所有商品类别
* @return
*/
public List<Category> getAllCategory(){
String sql = "select id,name from category";
try {
return queryRunner.query(sql,new BeanListHandler<>(Category.class));
} catch (SQLException e) {
e.printStackTrace();
}
return new ArrayList<>();
}
}
service包
public class GoodsService {
public List<Goods> getGoodsList(Criteria criteria, Page page){
GoodsDao goodsDao = new GoodsDao();
int total = (int) goodsDao.getCount(criteria);
page.setTotalCount(total);
return goodsDao.getGoodsList(criteria,page.getStartRows(),page.getPageSize());
}
public int addGoods(Goods goods){
GoodsDao goodsDao = new GoodsDao();
return goodsDao.addGoods(goods);
}
public Goods getGoodsById(Integer id){
GoodsDao goodsDao = new GoodsDao();
return goodsDao.getGoodsById(id);
}
public int updateGoods(Goods goods){
GoodsDao goodsDao = new GoodsDao();
return goodsDao.updateGoods(goods);
}
public int deleteGoods(Integer id){
GoodsDao goodsDao = new GoodsDao();
return goodsDao.deleteGoods(id);
}
}
public class CategoryService {
/**
* 获取所有商品类别
* @return
*/
public List<Category> getAllCategory(){
CategoryDao categoryDao = new CategoryDao();
return categoryDao.getAllCategory();
}
}
servlet包
@WebServlet(name = "BaseServlet", urlPatterns = "/base")
public class BaseServlet extends HttpServlet {
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
response.setContentType("text/html;charset=utf-8");
String methodName = request.getParameter("methodName");
try {
Method method = this.getClass().getMethod(methodName, HttpServletRequest.class, HttpServletResponse.class);
if (null != method) {
//处理返回值,为了资源跳转
String returnValue = (String) method.invoke(this, request, response);
System.out.println(returnValue);
//有的方法有返回值
//登录成功:重定向 redirect:/day61/user?methodName=showIndex
//登录失败: 请求转发 forward:/login.html
//有的方法没有返回值
//null,不用做任何处理
if (returnValue != null) {
//有返回值,实现资源跳转,需要资源的路径
if (returnValue.lastIndexOf(":") != -1) {
String path = returnValue.split(":")[1];
System.out.println("path : " + path);
//有":"
//实现资源跳转:重定向、请求转发
if (returnValue.startsWith("redirect")) {
//重定向
response.sendRedirect(request.getContextPath() + path);
} else if (returnValue.startsWith("forward")) {
//请求转发
request.getRequestDispatcher(path).forward(request, response);
}
} else {
//没有":",默认就是转发 /login.html
request.getRequestDispatcher(returnValue).forward(request, response);
}
} else {
//不做任何处理
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doPost(request, response);
}
}
servlet包
@WebServlet(name = "GoodsServlet", urlPatterns = "/goods")
public class GoodsServlet extends BaseServlet {
private CategoryService categoryService = new CategoryService();
private GoodsService goodsService = new GoodsService();
/**
* 获取商品列表
*
* @param request
* @param response
* @throws ServletException
* @throws IOException
*/
public String goodsList(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
Integer categoryId = ParameterUtil.getInteger(request, "categoryId");
Integer status = ParameterUtil.getInteger(request, "status");
String name = ParameterUtil.getString(request, "name");
Date start = ParameterUtil.getDate(request, "start", ParameterUtil.DATE_FORMAT);
Date end = ParameterUtil.getDate(request, "end", ParameterUtil.DATE_FORMAT);
Criteria criteria = new Criteria();
criteria.setStartTime(start);
criteria.setEndTime(end);
criteria.setStatus(status);
criteria.setName(name);
criteria.setCategoryId(categoryId);
Integer pageNum = ParameterUtil.getInteger(request, "pn");
Page page = new Page(pageNum, 5);
List<Category> categories = categoryService.getAllCategory();
request.setAttribute("categories", categories);
request.setAttribute("page", page);
List<Goods> goodsList = goodsService.getGoodsList(criteria, page);
request.setAttribute("goodsList", goodsList);
// request.getRequestDispatcher("/WEB-INF/list.jsp").forward(request,response);
return "forward:/WEB-INF/list.jsp";
}
/**
* 添加前查询所有种类,转发到新增页面
*
* @param request
* @param response
* @throws ServletException
* @throws IOException
*/
public String toAddGoods(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
List<Category> categories = categoryService.getAllCategory();
request.setAttribute("categories", categories);
// request.getRequestDispatcher("add.jsp").forward(request,response);
return "forward:/add.jsp";
}
/**
* 添加商品
*
* @param request
* @param response
* @throws ServletException
* @throws IOException
*/
public String addGoods(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
Integer categoryId = ParameterUtil.getInteger(request, "categoryId");
String name = ParameterUtil.getString(request, "name");
Double price = ParameterUtil.getDouble(request, "price");
Date purchaseDate = ParameterUtil.getDate(request, "purchaseDate", ParameterUtil.DATE_FORMAT);
Integer status = ParameterUtil.getInteger(request, "status");
Goods goods = new Goods(name, price, purchaseDate, categoryId, status);
int res = goodsService.addGoods(goods);
// response.sendRedirect("goods?methodName=goodsList");
return "redirect:/goods?methodName=goodsList";
}
public String toUpdate(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
Integer id = ParameterUtil.getInteger(request, "id");
Goods goods = goodsService.getGoodsById(id);
List<Category> categories = categoryService.getAllCategory();
request.setAttribute("categories", categories);
request.setAttribute("goods", goods);
// request.getRequestDispatcher("update.jsp").forward(request,response);
return "forward:/update.jsp";
}
public String update(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
Integer id = ParameterUtil.getInteger(request, "id");
Integer categoryId = ParameterUtil.getInteger(request, "categoryId");
String name = ParameterUtil.getString(request, "name");
Double price = ParameterUtil.getDouble(request, "price");
Integer status = ParameterUtil.getInteger(request, "status");
Goods goods = new Goods(id, name, price, categoryId, status);
int res = goodsService.updateGoods(goods);
if (res > 0) {
// response.sendRedirect("goods?methodName=goodsList");
return "redirect:/goods?methodName=goodsList";
} else {
// request.getRequestDispatcher("goods?methodName=toUpdate&id="+id).forward(request,response);
return "forward:/goods?methodName=toUpdate&id=" + id;
}
}
public void delete(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
response.setCharacterEncoding("utf-8");
Integer id = ParameterUtil.getInteger(request, "id");
int res = goodsService.deleteGoods(id);
System.out.println(id);
// response.sendRedirect("goods?methodName=goodsList");
if(res>0){
response.getWriter().write("OK");
response.getWriter().flush();
response.getWriter().close();
}
// return "redirect:/goods?methodName=goodsList";
}
}
util包
DBUtils类、ParameterUtil类
前端
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt" %>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<meta charset="UTF-8">
<title>商品列表</title>
<link rel="stylesheet" href="<%=request.getContextPath()%>/css/bootstrap.min.css"></link>
<link rel="stylesheet" href="<%=request.getContextPath()%>/css/bootstrap-datetimepicker.css"></link>
<!-- 最新的 Bootstrap 核心 JavaScript 文件 -->
<script src="<%=request.getContextPath()%>/jquery-1.10.2.min.js"></script>
<script src="<%=request.getContextPath()%>/bootstrap.min.js"></script>
<script src="<%=request.getContextPath()%>/bootstrap-datetimepicker.min.js" charset="UTF-8"></script>
</head>
<body>
<div class="row">
<div class="col-lg-6">
<form action="goods?methodName=goodsList" method="post" class="form-inline" id="searchForm">
<input type="hidden" name="pn" id="pageNum">
<div class="form-group">
<label>分类</label>
<select class="form-control" name="categoryId">
<option value="">请选择</option>
<c:forEach items="${requestScope.categories}" var="c">
<option value="${c.id}" ${param.categoryId==c.id?'selected':''}>${c.name}</option>
</c:forEach>
</select>
</div>
<div class="form-group">
<label>商品</label>
<input type="text" class="form-control" name="name" value="${param.name}">
</div>
<div class="form-group span6">
<label>进货时间</label>
<input class="form-control" size="10" type="text" id="startTime" name="start" value="${param.start}"> -
<input class="form-control" size="10" type="text" id="endTime" name="end" value="${param.end}">
</div>
<div class="form-group">
<label>状态</label>
<select class="form-control" name="status">
<option value="">请选择</option>
<option value="1" ${param.status==1?'selected':''}>销售</option>
<option value="0" ${param.status=='0'?'selected':''}>下架</option>
</select>
</div>
<div class="form-group">
<button type="submit" class="btn">查询</button> <button type="button" class="btn btn-success"><a href="<%=request.getContextPath()%>/goods?methodName=toAddGoods">+添加</a></button>
</div>
</form>
<table class="table table-striped table-bordered" style="margin-top:20px">
<tr>
<td>ID</td>
<td>商品</td>
<td>分类</td>
<td>价格</td>
<td>进货日期</td>
<td>状态</td>
<td>操作</td>
</tr>
<c:forEach items="${requestScope.goodsList}" var="goods">
<tr>
<td>${goods.id}</td>
<td>${goods.name}</td>
<td>${goods.categoryName}</td>
<td>${goods.price}</td>
<td><fmt:formatDate value="${goods.purchaseDate}" pattern="yyyy-MM-dd"/> </td>
<td>${goods.status}</td>
<td><a href="<%=request.getContextPath()%>/goods?methodName=toUpdate&id=${goods.id}">修改</a>
<a href="#" onclick="del(${goods.id})">删除</a></td>
</tr>
</c:forEach>
</table>
<ul class="pagination">
<c:choose>
<c:when test="${requestScope.page.pageNum>1}">
<li><a href="#" onclick="changePage(1)">首页</a></li>
<li><a href="#" onclick="changePage(${requestScope.page.pageNum-1})">上一页</a></li>
</c:when>
<c:otherwise>
<!--不可点击的样式-->
<li class="disabled"><a href="#">首页</a></li>
<li class="disabled"><a href="#">上一页</a></li>
</c:otherwise>
</c:choose>
<!--当前页-->
<c:forEach var="num" begin="1" end="${requestScope.page.totalPages}">
<li class="${num==page.pageNum?'active':''}"><a href="#" onclick="changePage(${num})">${num}</a></li>
</c:forEach>
<c:choose>
<c:when test="${requestScope.page.pageNum < requestScope.page.totalPages}">
<li><a href="#" onclick="changePage(${requestScope.page.pageNum+1})">下一页</a></li>
<li><a href="#" onclick="changePage(${requestScope.page.totalPages})">末页</a></li>
</c:when>
<c:otherwise>
<li><a href="#">下一页</a></li>
<li><a href="#">末页</a></li>
</c:otherwise>
</c:choose>
</ul>
</div>
</div>
<script>
$(function () {
$('#startTime').datetimepicker({
format: 'yyyy-mm-dd',
autoclose: true,
minView: 2
});
$('#endTime').datetimepicker({
format: 'yyyy-mm-dd',
autoclose: true,
minView: 2
});
})
function changePage(pn) {
$('#pageNum').val(pn)
$('#searchForm').submit()
}
function del(id) {
if(confirm("确认删除")){
$.ajax({
url:"${pageContext.request.contextPath}/goods?methodName=delete&id="+id,
type:"get",
success:function (ret) {
if(ret=='OK'){
alert("删除成功");
}else {
alert("删除失败")
}
location.href="${pageContext.request.contextPath}/goods?methodName=goodsList"
}
})
}
}
</script>
</body>
</html>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ page import="java.util.Date" %>
<%@ page import="java.util.Calendar" %>
<%@ page import="java.text.SimpleDateFormat" %>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<meta charset="UTF-8">
<title>添加商品</title>
<link rel="stylesheet" href="css/bootstrap.min.css"></link>
<link rel="stylesheet" href="css/bootstrap-datetimepicker.css"></link>
<!-- 最新的 Bootstrap 核心 JavaScript 文件 -->
<script src="jquery-1.10.2.min.js"></script>
<script src="bootstrap.min.js"></script>
<script src="./bootstrap-datetimepicker.min.js" charset="UTF-8"></script>
</head>
<body>
<div class="row">
<div class="col-lg-4">
<form method="post" id="registForm" class="form-horizontal" action="goods?methodName=addGoods">
<div class="form-group">
<label class="col-sm-2 control-label">分类:</label>
<div class="col-sm-10">
<select class="form-control" name="categoryId">
<option value="">请选择</option>
<c:forEach items="${requestScope.categories}" var="c">
<option value="${c.id}">${c.name}</option>
</c:forEach>
</select>
</div>
</div>
<div class="form-group">
<label class="col-sm-2 control-label">商品:</label>
<div class="col-sm-10">
<input type="text" class="form-control" name="name">
</div>
</div>
<div class="form-group">
<label class="col-sm-2 control-label">价格:</label>
<div class="col-sm-10">
<input type="text" class="form-control" name="price">
</div>
</div>
<div class="form-group">
<label class="col-sm-2 control-label">进货日期:</label>
<div class="col-sm-10">
<input type="text" class="form-control" name="purchaseDate" value="<%=new SimpleDateFormat("yyyy-MM-dd").format(Calendar.getInstance().getTime())%>" >
</div>
</div>
<div class="form-group">
<label class="col-sm-2 control-label">状态:</label>
<div class="col-sm-10">
<label class="control-label"><input type="radio" name="status" value="1" checked>销售</label>
<label class="control-label"><input type="radio" name="status" value="0">下架</label>
</div>
</div>
<div class="form-group">
<div class="col-sm-offset-2 col-sm-2">
<button type="submit" id="btn" class="btn btn-success">提交</button>
</div>
</div>
</form>
</div>
</div>
</body>
</html>
<%@ page import="java.text.SimpleDateFormat" %>
<%@ page import="java.util.Calendar" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<meta charset="UTF-8">
<title>更新商品</title>
<link rel="stylesheet" href="css/bootstrap.min.css"></link>
<link rel="stylesheet" href="css/bootstrap-datetimepicker.css"></link>
<!-- 最新的 Bootstrap 核心 JavaScript 文件 -->
<script src="jquery-1.10.2.min.js"></script>
<script src="bootstrap.min.js"></script>
<script src="./bootstrap-datetimepicker.min.js" charset="UTF-8"></script>
</head>
<body>
<div class="row">
<div class="col-lg-4">
<form method="post" id="registForm" class="form-horizontal" action="<%=request.getContextPath()%>/goods?methodName=update">
<div class="form-group">
<input type="hidden" name="id" value="${requestScope.goods.id}">
<label class="col-sm-2 control-label">分类:</label>
<div class="col-sm-10">
<select class="form-control" name="categoryId">
<option value="">请选择</option>
<c:forEach items="${requestScope.categories}" var="c">
<c:if test="${c.id==goods.categoryId}">
<option value="${c.id}" selected>${c.name}</option>
</c:if>
<option value="${c.id}">${c.name}</option>
</c:forEach>
</select>
</div>
</div>
<div class="form-group">
<label class="col-sm-2 control-label">商品:</label>
<div class="col-sm-10">
<input type="text" class="form-control" name="name" value="${goods.name}">
</div>
</div>
<div class="form-group">
<label class="col-sm-2 control-label">价格:</label>
<div class="col-sm-10">
<input type="text" class="form-control" name="price" value="${goods.price}">
</div>
</div>
<div class="form-group">
<label class="col-sm-2 control-label">进货日期:</label>
<div class="col-sm-10">
<input type="text" class="form-control" name="purchaseDate" value="${goods.purchaseDate}" readonly>
</div>
</div>
<div class="form-group">
<label class="col-sm-2 control-label">状态:</label>
<div class="col-sm-10">
<label class="control-label"><input type="radio" name="status" value="1" ${goods.status==1?'checked':''}>销售</label>
<label class="control-label"><input type="radio" name="status" value="0" ${goods.status=='0'?'checked':''}>下架</label>
</div>
</div>
<div class="form-group">
<div class="col-sm-offset-2 col-sm-2">
<button type="submit" id="btn" class="btn btn-success">提交</button>
</div>
</div>
</form>
</div>
</div>
</body>
</html>
index.jsp 直接跳转到需要执行的servlet类的方法
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>$Title$</title>
<script src="jquery-1.10.2.min.js"></script>
<script>
$(function () {
location.href="${pageContext.request.contextPath}/goods?methodName=goodsList"
})
</script>
</head>
<body>
<%-- <a href="<%=request.getContxtPath()%>/goods?methodName=goodsList">商品列表</a>--%>
</body>
</html>