数据库-条件查询和分页

条件和分页

1 条件查询

Dao

这里有两种方式处理模糊查询,

用集合封装sql语句
直接把if里面的? 改成传参的方式

         public List<Product> findProductListByCondition(Condition condition) throws SQLException {

                  QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());

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

                  String sql = "select * from product where 1=1 ";

                 

                  if(condition.getPname()!=null && !condition.getPname().trim().equals("")){

                         

                          sql+=" and pname like  "+"'%"+condition.getPname()+"%'";

                          //list.add("%"+condition.getPname()+"%");

                  }

                  if(condition.getIsHot()!= null && !condition.getIsHot().trim().equals("")){

                          sql+=" and is_hot= "+condition.getIsHot().trim();

                          //list.add(condition.getIsHot().trim() );

                  }

                  if(condition.getCid()!= null && !condition.getCid().trim().equals("")){

                          sql+=" and cid= "+condition.getCid();

                          //list.add(condition.getCid());

                  }

                 

                 

                  List<Product> productList = runner.query(sql, new BeanListHandler<Product>(Product.class)/*list.toArray()*/);

                  return productList;

         }

Jsp

1 回显 (下拉列表显示你最后选择的)

                          $(function(){

                                   $("#isHot option[value='${condition.isHot}']").prop("selected",true);

                                   $("#cid option[value='${condition.cid}']").prop("selected",true);

                          });

2 模糊查询后的显示

         <c:forEach items="${productList }" var="pro" varStatus="vs">

                                                            

                                                                      <tr onmouseover="this.style.backgroundColor = 'white'"

                                                                               onmouseout="this.style.backgroundColor = '#F5FAFE';">

                                                                               <td style="CURSOR: hand; HEIGHT: 22px" align="center"

                                                                                        width="18%">${vs.count }</td>

                                                                               <td style="CURSOR: hand; HEIGHT: 22px" align="center"

                                                                                        width="17%">

                                                                                        <img width="40" height="45" src="${pageContext.request.contextPath }/${pro.pimage }">

                                                                               </td>

                                                                               <td style="CURSOR: hand; HEIGHT: 22px" align="center"

                                                                                        width="17%">${pro.pname }</td>

                                                                               <td style="CURSOR: hand; HEIGHT: 22px" align="center"

                                                                                        width="17%">${pro.shop_price }</td>

                                                                               <td style="CURSOR: hand; HEIGHT: 22px" align="center"

                                                                                        width="17%">${pro.is_hot==1?"是":"否" }</td>

                                                                               <td align="center" style="HEIGHT: 22px"><a

                                                                                        href="${ pageContext.request.contextPath }/adminUpdateProductUI?pid=${pro.pid}">

                                                                                                <img

                                                                                                 src="${pageContext.request.contextPath}/images/i_edit.gif"

                                                                                                border="0" style="CURSOR: hand">

                                                                               </a></td>

        

                                                                               <td align="center" style="HEIGHT: 22px">

                                                                                        <a href="javascript:void(0);" onclick="delProduct('${pro.pid}')">

                                                                                                <img src="${pageContext.request.contextPath}/images/i_del.gif"

                                                                                                width="16" height="16" border="0" style="CURSOR: hand">

                                                                                        </a>

                                                                               </td>

                                                                      </tr>

                                                            

                                                             </c:forEach>

2 分页

难点:是bean 和service

bean

public class PageBean<T> {
    
    //当前页
    private int currentPage;
    //当前页显示的条数
    private int currentCount;
    //总条数
    private int totalCount;
    //总页数
    private int totalPage;
    //每页显示的数据
    private List<T> productList = new ArrayList<T>();
    
    
    public int getCurrentPage() {
        return currentPage;
    }
    public void setCurrentPage(int currentPage) {
        this.currentPage = currentPage;
    }
    public int getCurrentCount() {
        return currentCount;
    }
    public void setCurrentCount(int currentCount) {
        this.currentCount = currentCount;
    }
    public int getTotalCount() {
        return totalCount;
    }
    public void setTotalCount(int totalCount) {
        this.totalCount = totalCount;
    }
    public int getTotalPage() {
        return totalPage;
    }
    public void setTotalPage(int totalPage) {
        this.totalPage = totalPage;
    }
    public List<T> getProductList() {
        return productList;
    }
    public void setProductList(List<T> productList) {
        this.productList = productList;
    }

Service

ProductDao dao = new ProductDao();

                 

                  //目的：就是想办法封装一个PageBean 并返回

                  PageBean pageBean = new PageBean();

                  //1、当前页private int currentPage;

                  pageBean.setCurrentPage(currentPage);

                  //2、当前页显示的条数private int currentCount;

                  pageBean.setCurrentCount(currentCount);

                  //3、总条数private int totalCount;

                  int totalCount = dao.getTotalCount();

                  pageBean.setTotalCount(totalCount);

                  //4、总页数private int totalPage;

                  /*

                   * 总条数               当前页显示的条数        总页数

                   * 10                4                                 3

                   * 11                4                                 3

                   * 12                4                                 3

                   * 13                4                                 4

                   *

                   * 公式：总页数=Math.ceil(总条数/当前显示的条数)

                   *

                   */

                  int totalPage = (int) Math.ceil(1.0*totalCount/currentCount);

                  pageBean.setTotalPage(totalPage);

                  //5、每页显示的数据private List<T> productList = new ArrayList<T>();

                  /*

                   * 页数与limit起始索引的关系

                   * 例如 每页显示4条

                   * 页数           其实索引                 每页显示条数

                   * 1          0                        4

                   * 2          4                        4

                   * 3          8                        4

                   * 4          12                      4

                   *

                   * 索引index = (当前页数-1)*每页显示的条数

                   *

                   */

                  int index = (currentPage-1)*currentCount;

                 

                  List<Product> productList = dao.findProductListForPageBean(index,currentCount);

                  pageBean.setProductList(productList);

                 

                  return pageBean;/

dao

//获得全部的商品条数

         public int getTotalCount() throws SQLException {

                  QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());

                  String sql = "select count(*) from product";

                  Long query = (Long) runner.query(sql, new ScalarHandler());

                  return query.intValue();

         }

 

         //获得分页的商品数据

         public List<Product> findProductListForPageBean(int index,int currentCount) throws SQLException {

                  QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());

                  String sql = "select * from product limit ?,?";

                  return runner.query(sql, new BeanListHandler<Product>(Product.class), index,currentCount);

         }

Jsp

显示商品

<c:forEach items="${pageBean.productList }" var="product">

                          <div class="col-md-2" style="height:250px">

                                   <a href="product_info.htm">

                                            <img src="${pageContext.request.contextPath }/${product.pimage}" width="170" height="170" style="display: inline-block;">

                                   </a>

                                   <p>

                                            <a href="product_info.html" style='color: green'>${product.pname }</a>

                                   </p>

                                   <p>

                                            <font color="#FF0000">商城价：&yen;${product.shop_price }</font>

                                   </p>

                          </div>

                  </c:forEach>

分页

<!--分页 -->

         <div style="width: 380px; margin: 0 auto; margin-top: 50px;">

                  <ul class="pagination" style="text-align: center; margin-top: 10px;">

                          <!-- 上一页 -->

                          <!-- 判断当前页是否是第一页 -->

                          <c:if test="${pageBean.currentPage==1 }">

                                   <li class="disabled">

                                            <a href="javascript:void(0);" aria-label="Previous">

                                                     <span aria-hidden="true">&laquo;</span>

                                            </a>

                                   </li>

                          </c:if>

                          <c:if test="${pageBean.currentPage!=1 }">

                                   <li>

                                            <a href="${pageContext.request.contextPath }/productList?currentPage=${pageBean.currentPage-1}" aria-label="Previous">

                                                     <span aria-hidden="true">&laquo;</span>

                                            </a>

                                   </li>

                          </c:if>     

                         

                         

                         

                 

                          <c:forEach begin="1" end="${pageBean.totalPage }" var="page">

                                   <!-- 判断当前页 -->

                                   <c:if test="${pageBean.currentPage==page }">

                                            <li class="active"><a href="javascript:void(0);">${page}</a></li>

                                   </c:if>

                                   <c:if test="${pageBean.currentPage!=page }">

                                            <li><a href="${pageContext.request.contextPath }/productList?currentPage=${page}">${page}</a></li>

                                   </c:if>

                         

                          </c:forEach>

                         

                          <!-- 判断当前页是否是最后一页 -->

                          <c:if test="${pageBean.currentPage==pageBean.totalPage }">

                                   <li class="disabled">

                                            <a href="javascript:void(0);" aria-label="Next">

                                                     <span aria-hidden="true">&raquo;</span>

                                            </a>

                                   </li>

                          </c:if>

                          <c:if test="${pageBean.currentPage!=pageBean.totalPage }">

                                   <li>

                                            <a href="${pageContext.request.contextPath }/productList?currentPage=${pageBean.currentPage+1}" aria-label="Next">

                                                     <span aria-hidden="true">&raquo;</span>

                                            </a>

                                   </li>

                          </c:if>

                 

                  </ul>

         </div>

         <!-- 分页结束 -->

3 源码

链接：https://pan.baidu.com/s/1zsWljXJsGrf-ET0t6kgySw 密码：2n4r

posted @ 2018-03-21 13:03 8亩田阅读(317) 评论(0) 编辑收藏举报

刷新页面返回顶部

8亩田

数据库-条件查询和分页

1 条件查询

2 分页

3 源码

公告