实现公告板和本周热卖功能

任务：

公告板用于发布与网站相关的信息，本周热卖用于展示本周内销售数量最多的两本图书

一、创建Servlet

index.jsp中存在<jsp:forward page="ShowIndexServlet"></jsp:forward>

/**
 *	前台页面展示的servlet 
 *	1、展示最新添加或修改的一条公告
 *  2、展示本周热销商品
 */
public class ShowIndexServlet extends HttpServlet{
	private static final long serialVersionUID = 1L;
	public void doGet(HttpServletRequest req, HttpServletResponse resp)
			throws ServletException, IOException {
		this.doPost(req, resp);
	}
	
	public void doPost(HttpServletRequest req, HttpServletResponse resp)
			throws ServletException, IOException {
		//查询最近一条公告，传递到index.jsp页面进行展示
		NoticeService nService = new NoticeService();
		Notice notice = nService.getRecentNotice();
		req.setAttribute("n", notice);
		
		//查询本周热销的两条商品，传递到index.jsp页面进行展示
		ProductService pService = new ProductService();
		List<Object[]> pList =  pService.getWeekHotProduct();
		/*for(Object[] os:pList){
			for(Object o:os){
				System.out.println(o);
			}
			System.out.println("---------------------");
		}*/
		req.setAttribute("pList", pList);
		
		//请求转发
		req.getRequestDispatcher("/client/index.jsp").forward(req, resp);
	}
}

二、编写dao层代码

NoticeDao

//前台系统，查询最新添加或修改的一条公告
	public Notice getRecentNotice() throws SQLException {
		String sql = "select * from notice order by n_time desc limit 0,1";
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		return runner.query(sql, new BeanHandler<Notice>(Notice.class));
	}

ProductDao

//前台，获取本周热销商品
	public List<Object[]> getWeekHotProduct() throws SQLException {
		String sql = "SELECT products.id,products.name, "+
                             " products.imgurl,SUM(orderitem.buynum) totalsalnum "+
                     " FROM orderitem,orders,products "+
                     " WHERE orderitem.order_id = orders.id "+
                             " AND products.id = orderitem.product_id "+
                             " AND orders.paystate=1 "+
                             " AND orders.ordertime > DATE_SUB(NOW(), INTERVAL 7 DAY) "+
                     " GROUP BY products.id,products.name,products.imgurl "+
                     " ORDER BY totalsalnum DESC "+
                     " LIMIT 0,2 ";
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		return runner.query(sql, new ArrayListHandler());
	}