网上图书商城项目学习笔记-012BOOK模块查询2

一、分析

> 按图名查询（模糊）（分页）
> 按作者查询（分页）
> 按出版社查询（分页）
> 按id查询
> 多条件组合查询（分页）

二、代码

1.view层

(1）gj.jsp等

<form action="<c:url value='/BookServlet'/>" method="get">
    <input type="hidden" name="method" value="findByCombination" />
    <table align="center">
        <tr>
            <td>书名：</td>
            <td>
                <input type="text" name="bname" />
            </td>
        </tr>
        <tr>
            <td>作者：</td>
            <td>
                <input type="text" name="author" />
            </td>
        </tr>
        <tr>
            <td>出版社：</td>
            <td>
                <input type="text" name="press" />
            </td>
        </tr>
        <tr>
            <td>&nbsp;</td>
            <td>
                <input type="submit" value="搜　　索" />
                <input type="reset" value="重新填写" />
            </td>
        </tr>
    </table>
</form>

 

2.servlet层

(1）BookServlet.java

 

/**
     * 按作者查
     * @param req
     * @param resp
     * @return
     * @throws ServletException
     * @throws IOException
     */
    public String findByAuthor(HttpServletRequest req, HttpServletResponse resp)
            throws ServletException, IOException {
        int currentPage = getCurrentPage(req);
        String url = getUrl(req);
        String author = req.getParameter("author");
        PageBean<Book> pb = bookService.findByAuthor(author, currentPage);
        pb.setUrl(url);
        req.setAttribute("pb", pb);
        return "f:/jsps/book/list.jsp";
    }
    
    /**
     * 按书名查
     * @param req
     * @param resp
     * @return
     * @throws ServletException
     * @throws IOException
     */
    public String findByBname(HttpServletRequest req, HttpServletResponse resp)
            throws ServletException, IOException {
        int currentPage = getCurrentPage(req);
        String url = getUrl(req);
        String bname = req.getParameter("bname");
        PageBean<Book> pb = bookService.findByBname(bname, currentPage);
        pb.setUrl(url);
        req.setAttribute("pb", pb);
        return "f:/jsps/book/list.jsp";
    }
    
    /**
     * 按出版社查询
     * @param req
     * @param resp
     * @return
     * @throws ServletException
     * @throws IOException
     */
    public String findByPress(HttpServletRequest req, HttpServletResponse resp)
            throws ServletException, IOException {
        int currentPage = getCurrentPage(req);
        String url = getUrl(req);
        String press = req.getParameter("press");
        PageBean<Book> pb = bookService.findByPress(press, currentPage);
        pb.setUrl(url);
        req.setAttribute("pb", pb);
        return "f:/jsps/book/list.jsp";
    }
    
    /**
     * 多条件组合查询
     * @param req
     * @param resp
     * @return
     * @throws ServletException
     * @throws IOException
     */
    public String findByCombination(HttpServletRequest req, HttpServletResponse resp)
            throws ServletException, IOException {
        int currentPage = getCurrentPage(req);
        String url = getUrl(req);
        Book criteria = CommonUtils.toBean(req.getParameterMap(), Book.class);
        PageBean<Book> pb = bookService.findByCombination(criteria, currentPage);
        pb.setUrl(url);
        req.setAttribute("pb", pb);
        return "f:/jsps/book/list.jsp";
    }
    
    /**
     * 按bid查询
     * @param req
     * @param resp
     * @return
     * @throws ServletException
     * @throws IOException
     */
    public String load(HttpServletRequest req, HttpServletResponse resp)
            throws ServletException, IOException {
        String bid = req.getParameter("bid");
        Book book = bookService.load(bid);
        req.setAttribute("book", book);
        return "f:/jsps/book/desc.jsp";
    }

 

3.service层

(1）BookService.java

 

/**
     * 按书名查
     * @param bname
     * @param currentPage
     * @return
     */
    public PageBean<Book> findByBname(String bname, int currentPage) {
        try {
            return bookDao.findByBname(bname, currentPage);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    
    /**
     * 按作者查
     * @param author
     * @param currentPage
     * @return
     */
    public PageBean<Book> findByAuthor(String author, int currentPage) {
        try {
            return bookDao.findByAuthor(author, currentPage);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    
    /**
     * 按出版社查
     * @param author
     * @param currentPage
     * @return
     */
    public PageBean<Book> findByPress(String press, int currentPage) {
        try {
            return bookDao.findByPress(press, currentPage);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    
    /**
     * 多条件组合查询
     * @param criteria
     * @param currentPage
     * @return
     */
    public PageBean<Book> findByCombination(Book criteria, int currentPage) {
        try {
            return bookDao.findByCombination(criteria, currentPage);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    
    /**
     * 加载图书
     * @param bid
     * @return
     */
    public Book load(String bid) {
        try {
            return bookDao.findById(bid);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

 

4.dao层

(1）BookDao.java

    /**
     * 按书名模糊查询
     * @param bname
     * @param currentPage
     * @return
     * @throws SQLException
     */
    public PageBean<Book> findByBname(String bname, int currentPage) throws SQLException {
        List<Expression> exprList = new ArrayList<Expression>();
        exprList.add(new Expression("bname", "like", "%" + bname + "%"));
        return findByCriteria(exprList, currentPage);
    }
    
    /**
     * 按作者查
     * @param author
     * @param currentpage
     * @return
     * @throws SQLException
     */
    public PageBean<Book> findByAuthor(String author, int currentpage) throws SQLException {
        List<Expression> exprList = new ArrayList<Expression>();
        exprList.add(new Expression("author", "like", "%" + author + "%"));
        return findByCriteria(exprList, currentpage);
    }
    
    /**
     * 按出版社查
     * @param press
     * @param currentpage
     * @return
     * @throws SQLException
     */
    public PageBean<Book> findByPress(String press, int currentpage) throws SQLException {
        List<Expression> exprList = new ArrayList<Expression>();
        exprList.add(new Expression("press", "like", "%" + press + "%"));
        return findByCriteria(exprList, currentpage);
    }
    
    /**
     * 多条件组合查询
     * @param criteria
     * @param currentPage
     * @return
     * @throws SQLException
     */
    public PageBean<Book> findByCombination(Book criteria, int currentPage) throws SQLException {
        List<Expression> exprList = new ArrayList<Expression>();
        exprList.add(new Expression("bname", "like", "%" + criteria.getBname() + "%"));
        exprList.add(new Expression("author", "like", "%" + criteria.getAuthor() + "%"));
        exprList.add(new Expression("press", "like", "%" + criteria.getPress() + "%"));
        return findByCriteria(exprList, currentPage);
    }
    
    /**
     * 通用的查询方法
     * @param exprList
     * @param currentPage
     * @return
     * @throws SQLException 
     */
    private PageBean<Book> findByCriteria(List<Expression> exprList,
            int currentPage) throws SQLException {
        /*
         * 1. 得到pageSize
         * 2. 得到totalRecords
         * 3. 得到beanList
         * 4. 创建PageBean，返回
         */
        /*
         * 1. 得到pageSize
         */
        int pageSize = PageConfig.BOOK_PAGE_SIZE;
        /*
         * 2. 通过exprList来生成where子句
         */
        StringBuilder whereSql = new StringBuilder(" where 1=1");
        List<Object> params = new ArrayList<Object>();
        for(Expression expr : exprList) {
            /*
             * 添加一个条件上，
             * 1) 以and开头
             * 2) 条件的名称
             * 3) 条件的运算符，可以是=、!=、>、< ... is null，is null没有值
             * 4) 如果条件不是is null，再追加问号，然后再向params中添加一与问号对应的值
             */
            whereSql.append(" and ").append(expr.getName())
                .append(" ").append(expr.getOperator()).append(" ");
            // where 1=1 and bid = ?
            if(!expr.getOperator().equalsIgnoreCase("is null")) {
                whereSql.append("?");
                params.add(expr.getValue());
            }
        }
        
        /*
         * 3. 总记录数 
         */
        String sql = "select count(*) from t_book" + whereSql;
        Number count = (Number) qr.query(sql, new ScalarHandler(), params.toArray());
        int totalRecords = count.intValue();//得到了总记录数
        /*
         * 4. 得到beanList，即当前页记录
         */
        sql = "select * from t_book" + whereSql + " order by orderBy limit ?,?";
        params.add((currentPage - 1) * pageSize);//当前页首行记录的下标
        params.add(pageSize);//每页记录数
        
        List<Book> beanList = qr.query(sql, new BeanListHandler<Book>(Book.class), params.toArray());
        
        /*
         * 5. 创建PageBean，设置参数
         */
        PageBean<Book> pb = new PageBean<Book>();
        /*
         * 其中PageBean没有url，这个任务由Servlet完成
         */
        pb.setBeanList(beanList);
        pb.setCurrentPage(currentPage);
        pb.setPageSize(pageSize);
        pb.setTotalRecords(totalRecords);
        
        return pb;
    }
    
    /**
     * 按bid查询
     * @param bid
     * @return
     * @throws SQLException
     */
    public Book findById(String bid) throws SQLException {
        String sql = "select * from t_book where bid=?";
        return qr.query(sql, new BeanHandler<Book>(Book.class), bid);
    }