前台的开发（二）

（二）商品分类的查询和展示

创建监听器

创建service和dao层的查询方法

1. 将商品分类的数据查询

public class TypeDao {

	public List<Type> selectAll() throws SQLException {
		//取得数据库连接
		QueryRunner r = new QueryRunner(DBUtils.getDataSource());
		String sql = "select * from type";
		return r.query(sql, new BeanListHandler<Type>(Type.class));
	}
}

2. 将查询结果返回并由类 TypeService 中的方法调用返回

public class TypeService {

	private TypeDao tDao = new TypeDao();
	
	public List<Type> selectAll(){
		List<Type> list = null;
		try {
			list = tDao.selectAll();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
}

3. ApplicationListener监听器在项目启动时，开始查询得到数据，将商品分类的数据显示

	//创建TypeService对象
	private TypeService tService = new TypeService();
	
	public void contextInitialized(ServletContextEvent arg0)  { 
         // TODO Auto-generated method stub
    	List<Type> list = tService.selectAll();
    	arg0.getServletContext().setAttribute("typeList", list);
    }

创建分页的数据模型

//分页的数据模型
public class Page {

	private int pageNumber;//当前页数
	private int pageSize;//每页显示的数据个数
	private int totalCount;//商品的总数
	private int totalPage;//总页数
	
	private List<Object> list;

根据 每页商品的个数 和 商品总数 计算 总页数 的方法

public void setPageSizeAndTotalCount(int pageSize,int totalCount) {
		this.pageSize = pageSize;
		this.totalCount = totalCount;
		
		totalPage = (int) Math.ceil((double)totalCount/pageSize);
	}

创建servlet控制商品分类的页面跳转

protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		
		int id = 0;//id为0时跳转的是全部系列
		if(request.getParameter("id") != null) {
			id = Integer.parseInt(request.getParameter("id"));
		}
		//默认跳转的页面
		int pageNo = 1;
		if(request.getParameter("pageNo") != null) {
			pageNo = Integer.parseInt(request.getParameter("pageNo"));
		}
		Page p = gService.getGoodsPage(id, pageNo);
		request.setAttribute("p", p);
		
		//跳转到goods_list.jsp
		request.getRequestDispatcher("/goods_list.jsp").forward(request, response);
	}

商品分类中系列的商品查询

public List<Goods> selectGoods(int typeId,int pageNo,int pageSize) throws SQLException{

		QueryRunner r = new QueryRunner(DBUtils.getDataSource());
		//查询全部系列
		if(typeId == 0) {
			String sql = "select * from goods limit ?,?";
			return r.query(sql, new BeanListHandler<Goods>(Goods.class),(pageNo - 1)*pageSize, pageSize);
		}else {//查询其他系列
			String sql = "select * from goods where type_id = ? limit ?,?";
			return r.query(sql, new BeanListHandler<Goods>(Goods.class), typeId, (pageNo - 1)*pageSize, pageSize);
		}
	}

查询商品的总数

public int getGoodsCount(int typeId) throws SQLException {
		QueryRunner r = new QueryRunner(DBUtils.getDataSource());
		String sql = "";
		if(typeId == 0) {
			sql = "select count(*) from goods";
			return r.query(sql, new ScalarHandler<Long>()).intValue();
		}else {//查询其他系列
			sql = "select count(*) from goods where type_id = ?";
			return r.query(sql, new ScalarHandler<Long>(),typeId).intValue();
		}
	}

在service层调用dao层的方法创建分页的数据模型并返回商品的页数

public Page getGoodsPage(int typeId,int pageNo) {
		Page p = new Page();
		p.setPageNumber(pageNo);
		int totalCount = 0;
		try {
			totalCount = gDao.getGoodsCount(typeId);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		p.setPageSizeAndTotalCount(8, totalCount);
		
		List list = null;
		try {
			list = gDao.selectGoods(typeId, pageNo, 8);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		p.setList(list);
		
		return p;
	}

最后在jsp页面中使用 c:forEach 标签遍历结果，在商品分类中点击系列商品可以跳转到各自的页面。