先来一个jsp效果显示图：
下面开始代码部分：
在Dao中添加一个具有安照筛选条件的查询方法：
Sql查询语句：select * from goods ( where ...........)
//通过要筛选的条件查找符合条件的商品 public List<Goods> findFiltrate(String cate,String brand,double minPrice,double maxPrice,int page) throws SQLException { if(page < 1){ page = 1; } String sql = "select * from tdb_goods"; ArrayList<Object> plist = new ArrayList<Object>(); //通过传入的筛选条件拼接sql查询语句（最好写在Service中，可直接传拼接好的sql语句和需要替换的参数数组Object[] params） if(cate != null || brand != null || (minPrice >= 0 && maxPrice > minPrice)){ sql += " where "; if(cate != null){ if(brand == null && !(minPrice >=0 && maxPrice > minPrice)){ sql += " goods_cate=? "; }else{ sql += " goods_cate=? and"; } plist.add(cate); } if(brand != null){ if(!(minPrice >=0 && maxPrice > minPrice)){ sql += " brand_name=? "; }else{ sql += " brand_name=? and"; } plist.add(brand); } if(minPrice >=0 && maxPrice > minPrice){ sql += " goods_price > ? and goods_price < ? "; plist.add(minPrice); plist.add(maxPrice); } } sql +=" limit ?,?"; plist.add((page - 1) * pagesize); plist.add(pagesize); Object[] params = plist.toArray(); QueryRunner queryRunner = new QueryRunner(JDBCUtil_DBCP.getDataSource()); List<Goods> list = null; try { list = (List<Goods>) queryRunner.query(sql, new BeanListHandler(Goods.class),params); } catch (SQLException e) { throw new SQLException(e); } return list; }
Dao中省略了获取筛选条件的代码
中间省略Service（调用方法就行了）
Servlet：
import java.io.IOException; import java.util.List; import javax.servlet.ServletException; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import com.hyl.bean.Goods; import com.hyl.service.Service; public class MyServlet extends HttpServlet { @Override protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { req.setCharacterEncoding("utf-8"); resp.setContentType("text/html;charset=utf-8"); Service service = new Service(); List<String> cate = service.findAllcate(); List<String> brand = service.findAllbrand(); String ncate = null; String nbrand = null; double minPrice = 0; double maxPrice = 0; if(req.getParameter("cate")==null||"All".equals(req.getParameter("cate"))){ req.setAttribute("ncate","All"); ncate = null; }else{ ncate = req.getParameter("cate"); req.setAttribute("ncate", ncate); } if(req.getParameter("brand")==null||"All".equals(req.getParameter("brand"))){ req.setAttribute("nbrand","All"); nbrand = null; }else{ nbrand = req.getParameter("brand"); req.setAttribute("nbrand", nbrand); } try{ minPrice = Double.parseDouble(req.getParameter("minPrice")); req.setAttribute("minPrice", minPrice); }catch(Exception e){ minPrice = 0; } try{ maxPrice = Double.parseDouble(req.getParameter("maxPrice")); req.setAttribute("maxPrice", maxPrice); }catch(Exception e){ maxPrice = 0; } int maxpage = service.getmaxPage(ncate,nbrand,minPrice,maxPrice); if(req.getParameter("page")==null){ req.setAttribute("page", 1); }else if(Integer.parseInt(req.getParameter("page"))>maxpage){ req.setAttribute("page", maxpage); }else if(Integer.parseInt(req.getParameter("page"))==0){ req.setAttribute("page", maxpage); }else{ req.setAttribute("page", Integer.parseInt(req.getParameter("page"))); } List<Goods> list = service.show_goos(ncate,nbrand,minPrice,maxPrice,Integer.parseInt(req.getAttribute("page").toString())); req.setAttribute("maxpage", maxpage); req.setAttribute("list", list); req.setAttribute("cate", cate); req.setAttribute("brand", brand); getServletContext().getRequestDispatcher("/goods.jsp").forward(req, resp); } @Override protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { doPost(req, resp); } }
jsp：
<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%> <%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head> <title>goods page</title> <meta http-equiv="pragma" content="no-cache"> <meta http-equiv="cache-control" content="no-cache"> <meta http-equiv="expires" content="0"> <meta http-equiv="keywords" content="keyword1,keyword2,keyword3"> <meta http-equiv="description" content="This is my page"> <!-- <link rel="stylesheet" type="text/css" href="styles.css"> --> </head> <!-- 请忽略，include 的另一个登录的头部 --> <jsp:include page="head.jsp"></jsp:include> <body> <div align="center"><h3 style="color: blue">商 品 信 息 表</h3></div> <div style="margin-top: 25px" align="center"> <form id="form_id" action="myservlet" method="post"> <table style="width: 1300px" border="1px"> <tr> <td>编号</td> <td>商品</td> <td>类型<select name="cate" onchange="select(1)"> <option>${ncate}</option> <option value="All">All</option> <c:forEach var="c" items="${cate}"> <option value="${c}">${c}</option> </c:forEach> </select></td> <td>厂商<select name="brand" onchange="select(1)"> <option>${nbrand}</option> <option value="All">All</option> <c:forEach var="b" items="${brand}"> <option value="${b}">${b}</option> </c:forEach> </select></td> <td>价格<input type="text" name="minPrice" style="width:50px;" value="${minPrice}">--<input type="text" style="width:50px;" name="maxPrice" value="${maxPrice}"><input type="submit" value="搜索"></td> <td>是否出售</td> <td>是否售空</td> </tr> <c:choose> <c:when test="${empty list}"> <tr> <td colspan="7">未找到符合条件的数据</td> </tr> </c:when> <c:otherwise> <c:forEach var="g" items="${list}"> <tr> <td>${g.goods_id}</td> <td>${g.goods_name}</td> <td>${g.goods_cate}</td> <td>${g.brand_name}</td> <td>${g.goods_price}</td> <td>${g.is_show}</td> <td>${g.is_saleoff}</td> </tr> </c:forEach> </c:otherwise> </c:choose> </table> </form> <c:if test="${page!=1}"> <span onclick="select(1)">首页</span> </c:if> <c:if test="${page>1}"> <span onclick="select('${page-1}')">上一页</span> </c:if> <c:forEach begin="1" step="1" end="${maxpage}" var="a"> <c:choose> <c:when test="${page==a}"> <span onclick="select('${a}')" style="color: red">${a}</span> </c:when> <c:otherwise> <span onclick="select('${a}')">${a}</span> </c:otherwise> </c:choose> </c:forEach> <c:if test="${page<maxpage}"> <span onclick="select('${page+1}')">下一页</span> </c:if> <c:if test="${page!=maxpage}"> <span onclick="select('${maxpage}')">尾页</span> </c:if> </div> </body> <script type="text/javascript"> //当select组件中值发生了变化就会调用该函数 标签的onchange属性 function select(page) { var form = document.getElementById("form_id"); form.action = "myservlet?page=" + page; form.submit(); }; </script> </html>
筛选结果如下：
补：模糊查找
Sql = "select * from table where colnum like ? "
params = new Object[]{ "%" + value + "%"};//中间含有value；
params = new Object[]{ value + "%" };//以value开头；
params = new Object[]{ "%" + value };//以value结尾；
在MySql中 ‘_’ 占一个字符 '%' 占0个或者0个以上；
‘_’与‘%’可结合使用；
Over
