2.6
今天主要是对多条件查询进行巩固，因为模糊查询主要就是运用like和符号，而多条件查询则分为并和或，则是需要利用if语句来进行区别
这里主要是放Dao层和Servlet
1 public List<Book> search(Integer id, String name, Double maxPrice, Double minPrice, 2 String category, Integer maxPnum, Integer minPnum, String imgurl, 3 String description, String author, Integer maxSales,Integer minSales) { 4 return bookDao.search(id, name, maxPrice, minPrice, category, maxPnum, minPnum, imgurl, 5 description, author, maxSales, minSales); 6 } 7 8 9 10 //复杂条件查询 11 public List<Book> search(Integer id, String name, Double maxPrice, Double minPrice, 12 String category, Integer maxPnum, Integer minPnum, String imgurl, 13 String description, String author, Integer maxSales,Integer minSales){ 14 15 //这个是用于存储查询的结果的 16 List<Book> books = new ArrayList<Book>(); 17 //这个用于存储查询的条件参数的 18 List list = new ArrayList(); 19 20 Connection conn = null; 21 PreparedStatement pstmt = null; 22 ResultSet rs = null; 23 24 try { 25 26 //创建资源链接对象 27 conn = getConnection(); 28 //定义sql语句 29 String sql = "select * from book where 1 = 1"; 30 31 //书本编号不为空的时候 32 if (id != 0) { 33 sql = sql + " and id = ?"; 34 list.add(id); 35 } 36 37 //去除掉姓名的空白位置 38 if (!"".equals(name.trim())){ 39 sql = sql + " and name like ?"; 40 list.add("%" + name.trim() + "%"); 41 } 42 43 //最高价格 44 if (maxPrice != 0.0) { 45 sql = sql + " and price < ?"; 46 list.add(maxPrice); 47 } 48 49 //最低价格 50 if (minPrice != 0.0) { 51 sql = sql + " and price > ?"; 52 list.add(minPrice); 53 } 54 55 //如果类别名不为空 56 if (!"".equals(category.trim())) { 57 sql = sql + " and category like ?"; 58 list.add("%" + category.trim() + "%"); 59 } 60 61 //最大库存 62 if (maxPnum != 0) { 63 sql = sql + " and pnum < ?"; 64 list.add(maxPnum); 65 } 66 67 //最小库存 68 if (minPnum != 0) { 69 sql = sql + " and pnum > ?"; 70 list.add(minPnum); 71 } 72 73 //作品封面 74 if (!"".equals(imgurl.trim())) { 75 sql = sql + "and imgurl like ?"; 76 list.add("%" + imgurl.trim() + "%"); 77 } 78 79 //作品描述 80 if (!"".equals(description.trim())){ 81 sql = sql + " and description like ?"; 82 list.add("%" + description.trim() + "%"); 83 } 84 85 //作者 86 if (!"".equals(author.trim())){ 87 sql = sql + " and author like ?"; 88 list.add("%" + author.trim() + "%"); 89 } 90 91 //最大销量 92 if (maxSales != 0){ 93 sql = sql + " and sales < ?"; 94 list.add(maxSales); 95 } 96 97 //最低销量 98 if (minSales != 0){ 99 sql = sql + " and sales > ?"; 100 list.add(minSales); 101 } 102 103 //创建sql执行对象 104 pstmt = conn.prepareStatement(sql); 105 106 //给?参数进行赋值 107 if (list.size() > 0) { 108 for (int i = 0; i < list.size(); i++) { 109 pstmt.setObject(i+1,list.get(i)); 110 } 111 } 112 113 //执行sql 114 rs = pstmt.executeQuery(); 115 //遍历查询 116 while (rs.next()){ 117 118 Book book = new Book(); 119 120 book.setId(rs.getInt("id")); 121 book.setName(rs.getString("name")); 122 book.setPrice(rs.getDouble("price")); 123 book.setCategory(rs.getString("category")); 124 book.setPnum(rs.getInt("pnum")); 125 book.setImgurl(rs.getString("imgurl")); 126 book.setDescription(rs.getString("description")); 127 book.setAuthor(rs.getString("author")); 128 book.setSales(rs.getInt("sales")); 129 130 books.add(book); 131 } 132 133 } catch (Exception e) { 134 e.printStackTrace(); 135 } finally { 136 //关闭资源链接对象 137 close(rs,pstmt,conn); 138 } 139 //返回一个带有参数的list集合 140 return books; 141 }
jsp页面
<%@ page contentType="text/html;charset=UTF-8" language="java" %> <%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %> <html> <head> <meta charset="UTF-8"> <title>国际图书商城</title> <link rel="stylesheet" href="static/bootstrap/css/bootstrap.min.css"/> <script src="static/bootstrap/js/jquery-3.1.0.min.js"></script> <script src="static/bootstrap/js/bootstrap.min.js"></script> <style> table { border: 3px solid; margin: auto; width: 900px; text-align: center; } h3 { text-align: center; } </style> </head> <body> <h3>国际图书商城</h3> <h3>多条件动态查询</h3> <form action="searchServlet" method="post"> <table> <tr> <td> 编号: </td> <td> <input type="text" name="id"> </td> </tr> <tr> <td> 书名: </td> <td> <input type="text" name="name"> </td> </tr> <tr> <td> 最高价格: </td> <td> <input type="text" name="maxPrice"> </td> </tr> <tr> <td> 最低价格: </td> <td> <input type="text" name="minPrice"> </td> </tr> <tr> <td> 类别: </td> <td> <input type="text" name="category"> </td> </tr> <tr> <td> 最大库存: </td> <td> <input type="text" name="maxPnum"> </td> </tr> <tr> <td> 最小库存: </td> <td> <input type="text" name="minPnum"> </td> </tr> <tr> <td> 封面: </td> <td> <input type="text" name="imgurl"> </td> </tr> <tr> <td> 描述: </td> <td> <input type="text" name="description"> </td> </tr> <tr> <td> 作者: </td> <td> <input type="text" name="author"> </td> </tr> <tr> <td> 最高售量: </td> <td> <input type="text" name="maxSales"> </td> </tr> <tr> <td> 最低售量: </td> <td> <input type="text" name="minSales"> </td> </tr> </table> <center><input type="submit" value="查询"></center> </form> <table border="1" cellspacing="0"> <tr> <th>编号</th> <th>书名</th> <th>价格</th> <th>类别</th> <th>库存</th> <th>封面</th> <th>描述</th> <th>作者</th> <th>售量</th> <th></th> <th></th> <th></th> <th></th> </tr> <%-- 使用foreach循环进行遍历输出 我们重新来理解一下foreach: 当我们在requestScope中传入一个books集合的时候， 我们的foreach容器就多了一个books集合,对其进行遍历也就是遍历books容器里面的每一个book对象 这样的话我们每一个book对象就可以通过"."的方式,把具体的属性值取出来,这里类似于mybatis --%> <%-- 用于分页的： <c:forEach items="${pageBean.books}" var="book" varStatus="vs"> --%> <%--正常使用的/复杂查询使用的--%> <c:forEach items="${books}" var="book" varStatus="vs"> <tr> <td>${vs.count}</td> <td>${book.name}</td> <td>${book.price}</td> <td>${book.category}</td> <td>${book.pnum}</td> <td>${book.imgurl}</td> <td>${book.description}</td> <td>${book.author}</td> <td>${book.sales}</td> <td> <%--这里在路径上传了一个book的id=book.id过去给后台--%> <a href="deleteBooksServlet?id=${book.id}">删除</a> </td> <td> <a href="selectByIdServlet?id=${book.id}">更新</a> </td> <td> <a href="addCartServlet?id=${book.id}">添加到购物车</a> </td> <td> <a href="addDataServlet?id=${book.id}">查看详情</a> </td> </tr> </c:forEach> </table> <br> <br> <br> <center><b>请选择操作：</b></center> <br> <table> <tr> <td> <a href="addBook.jsp">新增图书</a> </td> </tr> <tr> <td> <a href="index.jsp">返回首页</a> </td> </tr> <tr> <td> <a href="showCartServlet">查看购物车</a> </td> </tr> <tr> <td> <a href="showDataServlet">查看浏览记录</a> </td> </tr> </table> <nav aria-label="Page navigation"> <ul class="pagination"> <c:if test="${pageBean.currentPage==1}"> <li class="disabled"> </c:if> <c:if test="${pageBean.currentPage!=1}"> <li> </c:if> <a href="pageServlet?currentPage=${pageBean.currentPage==1?1:pageBean.currentPage-1}" > <span aria-hidden="true"> « </span> </a> </li> <c:forEach begin="1" end="${pageBean.totalPage}" var="i"> <c:if test="${pageBean.currentPage == i}"> <li class="active"><a href="pageServlet?currentPage=${i}">${i}</a></li> </c:if> <c:if test="${pageBean.currentPage!=i }"> <li><a href="pageServlet?currentPage=${i}">${i}</a></li> </c:if> </c:forEach> <c:if test="${pageBean.currentPage==pageBean.totalPage}"> <li class="disabled"> </c:if> <c:if test="${pageBean.currentPage!=pageBean.totalPage}"> <li> </c:if> <a href="pageServlet?currentPage=${pageBean.currentPage == pageBean.totalPage?pageBean.totalPage:pageBean.currentPage+1}"> <span aria-hidden="true"> » </span> </a> </li> </ul> </nav> <span style="font-size:15px;margin-left:5px;"> 共${pageBean.count}条记录,共${pageBean.totalPage}页 </span> </div> <%--<div class="page">--%> <%--<a href="pageServlet?currentPage=${pageBean.currentPage==1?1:pageBean.currentPage-1}">--%> <%--⁢⁢上一页--%> <%--</a> --%> <%--第${pageBean.currentPage}页/共${pageBean.totalPage}页 --%> <%--<a href="pageServlet?currentPage=${pageBean.currentPage == pageBean.totalPage?pageBean.totalPage:pageBean.currentPage+1}">--%> <%--下一页>>--%> <%--</a>--%> <%--</div>--%> </body> </html>
