web 开发中的条件查询实现

在form表单中有如下代码

商品名称<input type="text" id="pname" name="pname" value="${condition.pname }">  是否热门<select name="is_hot" id="is_hot"> <option value="">不限</option> <option value="1">是</option> <option value="0">否</option> </select>  商品类别<select name="cid" id="cid"> <option value="">不限</option> <c:forEach items="${categories }" var="category"> <option value="${category.cid }">${category.cname }</option> </c:forEach> </select>  <input type="submit" value="搜索">

        商品类别为数据库中查询来的，表单提交到如下地址

action="${pageContext.request.contextPath}/adminSearchProduct" method="post"

在AdminSearchProductServlet中代码如下

实现

protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {request.setCharacterEncoding("UTF-8");//请求参数中有中文，需设置为utf-8Map<String, String[]> properties = request.getParameterMap();//获取form表单中的所有数据Condition condition = new Condition();try {BeanUtils.populate(condition, properties);//将数据封装到Condition Bean中} catch (IllegalAccessException | InvocationTargetException e) {// TODO Auto-generated catch blocke.printStackTrace();}AdminProductListService service = new AdminProductListService();//将数据传递带service层List<Product> products = null;try {products = service.findProductByCondition(condition);} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}//获取商品类别List<Category> categories = null;try {categories = service.findAllCategoryList();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}request.setAttribute("condition", condition);request.setAttribute("categories", categories);request.setAttribute("products", products);request.getRequestDispatcher("/admin/product/list.jsp").forward(request, response);}

Condition类实现如下

package com.vo;public class Condition {private String pname;private String is_hot;private String cid;public String getPname() {return pname;}public void setPname(String pname) {this.pname = pname;}public String getIs_hot() {return is_hot;}public void setIs_hot(String is_hot) {this.is_hot = is_hot;}public String getCid() {return cid;}public void setCid(String cid) {this.cid = cid;}}

service层中

public List<Product> findProductByCondition(Condition condition) throws SQLException {ProductDao dao = new ProductDao();return dao.findProductByCondition(condition);}

dao层中

public List<Product> findProductByCondition(Condition condition) throws SQLException {QueryRunner qRunner = new QueryRunner(DataSourceUtils.getDataSource());

StringBuffer sql = new StringBuffer("select * from product where 1=1 ");//任意设置一个初始的条件，为了下面方便的添加条件

List<String> parms = new ArrayList<>();

//根据Condition对象判断要添加的查询条件，并添加到SQL语句中

//商品名字为模糊查询，所以使用likeif (condition.getPname()!=null&&!condition.getPname().trim().equals("")) {sql.append(" and pname like ?");parms.add("%"+condition.getPname()+"%");}if (condition.getIs_hot()!=null&&!condition.getIs_hot().trim().equals("")){sql.append(" and is_hot= ? ");parms.add(condition.getIs_hot());}if (condition.getCid()!=null&&!condition.getCid().trim().equals("")) {sql.append(" and cid= ? ");parms.add(condition.getCid());}List<Product> products = qRunner.query(sql.toString(), new BeanListHandler<Product>(Product.class), parms.toArray());return products;}