【JavaWeb】实现动态多条件查询

 实体类对象：

package com.ysw.web.entity;

public class Book {

    private Integer id;                 //编号
    private String name;                //书名
    private Double price;               //价格
    private String category;            //类型
    private Integer pnum;               //库存
    private String imgurl;              //图片路径
    private String description;         //描述
    private String author;              //作者
    private Integer sales;              //销售量

    public Book() {
    }

    public Book(String name, Double price, String category, Integer pnum, String imgurl, String description, String author, Integer sales) {
        this.name = name;
        this.price = price;
        this.category = category;
        this.pnum = pnum;
        this.imgurl = imgurl;
        this.description = description;
        this.author = author;
        this.sales = sales;
    }

    public Book(Integer id, String name, Double price, String category, Integer pnum, String imgurl, String description, String author, Integer sales) {
        this.id = id;
        this.name = name;
        this.price = price;
        this.category = category;
        this.pnum = pnum;
        this.imgurl = imgurl;
        this.description = description;
        this.author = author;
        this.sales = sales;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Double getPrice() {
        return price;
    }

    public void setPrice(Double price) {
        this.price = price;
    }

    public String getCategory() {
        return category;
    }

    public void setCategory(String category) {
        this.category = category;
    }

    public Integer getPnum() {
        return pnum;
    }

    public void setPnum(Integer pnum) {
        this.pnum = pnum;
    }

    public String getImgurl() {
        return imgurl;
    }

    public void setImgurl(String imgurl) {
        this.imgurl = imgurl;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }

    public String getAuthor() {
        return author;
    }

    public void setAuthor(String author) {
        this.author = author;
    }

    public Integer getSales() {
        return sales;
    }

    public void setSales(Integer sales) {
        this.sales = sales;
    }

    @Override
    public String toString() {
        final StringBuffer sb = new StringBuffer("Book{");
        sb.append("id=").append(id);
        sb.append(", name='").append(name).append('\'');
        sb.append(", price=").append(price);
        sb.append(", category='").append(category).append('\'');
        sb.append(", pnum=").append(pnum);
        sb.append(", imgurl='").append(imgurl).append('\'');
        sb.append(", description='").append(description).append('\'');
        sb.append(", author='").append(author).append('\'');
        sb.append(", sales=").append(sales);
        sb.append('}');
        return sb.toString();
    }


}

Service层：

//多条件动态查询
    public List<Book> search(Integer id, String name, Double maxPrice, Double minPrice,
                             String category, Integer maxPnum, Integer minPnum, String imgurl,
                             String description, String author, Integer maxSales,Integer minSales) {
        return bookDao.search(id, name, maxPrice, minPrice, category, maxPnum, minPnum, imgurl,
                description, author, maxSales, minSales);
    }

 Dao层：

//复杂条件查询
    public List<Book> search(Integer id, String name, Double maxPrice, Double minPrice,
                             String category, Integer maxPnum, Integer minPnum, String imgurl,
                             String description, String author, Integer maxSales,Integer minSales){

        //这个是用于存储查询的结果的
        List<Book> books = new ArrayList<Book>();
        //这个用于存储查询的条件参数的
        List list = new ArrayList();

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {

            //创建资源链接对象
            conn = getConnection();
            //定义sql语句
            String sql = "select * from book where 1 = 1";

            //书本编号不为空的时候
            if (id != 0) {
                sql = sql + " and id = ?";
                list.add(id);
            }

            //去除掉姓名的空白位置
            if (!"".equals(name.trim())){
                sql = sql + " and name like ?";
                list.add("%" + name.trim() + "%");
            }

            //最高价格
            if (maxPrice != 0.0) {
                sql = sql + " and price < ?";
                list.add(maxPrice);
            }

            //最低价格
            if (minPrice != 0.0) {
                sql = sql + " and price > ?";
                list.add(minPrice);
            }

            //如果类别名不为空
            if (!"".equals(category.trim())) {
                sql = sql + " and category like ?";
                list.add("%" + category.trim() + "%");
            }

            //最大库存
            if (maxPnum != 0) {
                sql = sql + " and pnum < ?";
                list.add(maxPnum);
            }

            //最小库存
            if (minPnum != 0) {
                sql = sql + " and pnum > ?";
                list.add(minPnum);
            }

            //作品封面
            if (!"".equals(imgurl.trim())) {
                sql = sql + "and imgurl like ?";
                list.add("%" + imgurl.trim() + "%");
            }

            //作品描述
            if (!"".equals(description.trim())){
                sql = sql + " and description like ?";
                list.add("%" + description.trim() + "%");
            }

            //作者
            if (!"".equals(author.trim())){
                sql = sql + " and author like ?";
                list.add("%" + author.trim() + "%");
            }

            //最大销量
            if (maxSales != 0){
                sql = sql + " and sales < ?";
                list.add(maxSales);
            }

            //最低销量
            if (minSales != 0){
                sql = sql + " and sales > ?";
                list.add(minSales);
            }

            //创建sql执行对象
            pstmt = conn.prepareStatement(sql);

            //给?参数进行赋值
            if (list.size() > 0) {
                for (int i = 0; i < list.size(); i++) {
                    pstmt.setObject(i+1,list.get(i));
                }
            }

            //执行sql
            rs = pstmt.executeQuery();
            //遍历查询
            while (rs.next()){

                Book book = new Book();

                book.setId(rs.getInt("id"));
                book.setName(rs.getString("name"));
                book.setPrice(rs.getDouble("price"));
                book.setCategory(rs.getString("category"));
                book.setPnum(rs.getInt("pnum"));
                book.setImgurl(rs.getString("imgurl"));
                book.setDescription(rs.getString("description"));
                book.setAuthor(rs.getString("author"));
                book.setSales(rs.getInt("sales"));

                books.add(book);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //关闭资源链接对象
            close(rs,pstmt,conn);
        }
        //返回一个带有参数的list集合
        return books;
    }

Jsp页面：

<%--
  Created by IntelliJ IDEA.
  User: Simon
  Date: 2020/2/1
  Time: 23:11
  To change this template use File | Settings | File Templates.
--%>
<%@ 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">
                    &laquo;
                </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">
                    &raquo;
                </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}">--%>
<%--&it;&it;上一页--%>
<%--</a>&nbsp;&nbsp;--%>

<%--第${pageBean.currentPage}页/共${pageBean.totalPage}页&nbsp;&nbsp;--%>

<%--<a href="pageServlet?currentPage=${pageBean.currentPage == pageBean.totalPage?pageBean.totalPage:pageBean.currentPage+1}">--%>
<%--下一页&gt;&gt;--%>
<%--</a>--%>

<%--</div>--%>

</body>
</html>