分页查询功能(包含pageBean封装)

PageQuerryServlet.java

package com.exp.servlet;

import java.io.IOException;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.exp.model.Book;
import com.exp.model.PageResult;
import com.exp.service.BooksServiceImpl;

/**
 * 分页查询demo 
 */
@WebServlet("/PageQuerryServlet")
public class PageQuerryServlet extends HttpServlet {
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        //1.获取请求参数
        int page = Integer.parseInt(request.getParameter("page"));
        //2.调用服务层
        BooksServiceImpl service = new BooksServiceImpl();
        //3.调起服务层方法
        PageResult<Book> pr = service.findBookByPage(page);
        System.out.println(pr.toString());
        System.out.println(pr.getList());
    }

}

 

BooksDaoImpl.java

package com.exp.dao;

import java.sql.SQLException;
import java.util.List;

import javax.sql.DataSource;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.exp.model.Book;
import com.exp.model.PageResult;

import utils.C3P0Utils;


//import com.exp.utils.C3P0Utils;

public class BooksDaoImpl {
    private static DataSource ds = C3P0Utils.getDataSource();
    /*
     * 方法：添加书本信息
     */
    public void addBook(Book book) throws SQLException{
        QueryRunner qr = new QueryRunner(ds);
        String sql = "insert into books(name,pnum,price,category,description) values(?,?,?,?,?)";
        Object[] params = new Object[5];
//        params[0] = UUID.randomUUID().toString();
        params[0] = book.getName();
        params[1] = book.getPnum();
        params[2] = book.getPrice();
        params[3] = book.getCategory();
        params[4] = book.getDescription();
        int r = qr.update(sql,params);
        System.out.println("r:"+r);
    }
    /*
     * 方法：获取所有书本列表
     */
    public List<Book> getbookList() throws SQLException{
        QueryRunner qr = new QueryRunner(ds);
        String sql = "select * from books";
        return qr.query(sql, new BeanListHandler<Book>(Book.class));
    }
    /*
     * 方法：获取书本id
     */
    public Book findBookId(int id) throws SQLException{
        QueryRunner qr = new QueryRunner(ds);
        String sql = "select * from books where id =?";
        return qr.query(sql, new BeanHandler<Book>(Book.class),id);
    }
    /*
     * 方法：更新修改当前书本信息
     */
    public void editBook(Book book) throws SQLException{
        QueryRunner qr = new QueryRunner(ds);
        String sql = "update books set name=?,pnum=?,price=?,category=?,description=? where id=?";
        int r = qr.update(sql,book.getName(),book.getPnum(),book.getPrice(),book.getCategory(),book.getDescription(),book.getId());
        System.out.println("受影响结果："+r+"条");
    }
    /*
     * 方法：删除当前书本信息
     */
    public void deleteBook(int id) throws SQLException{
        QueryRunner qr = new QueryRunner(ds);
        String sql = "delete from books where id=?";
        int r = qr.update(sql,id);
        System.out.println("受影响结果："+r+"条");
    }
    /*
     * 方法：批量删除书本  (执行多次sql)
     */
    public void BatchBookByids1(String ids) {
        QueryRunner qr = new QueryRunner(ds);
        String[] idArr = ids.split(",");
        String sql = "delete from books where id=?";
        for(String id:idArr){
            try {
                int r = qr.update(sql,id);
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        
    }
    /*
     * 方法：批量删除书本  (执行一次sql)
     */
    public void BatchBookByids(String ids) {
        QueryRunner qr = new QueryRunner(ds);
        String[] idArr = ids.split(",");
        String sql = "delete from books where id=?";
        Object[][] params = new Object[idArr.length][];
        for(int i=0;i<idArr.length;i++){
            params[i] = new Object[]{idArr[i]};
        }
        
        //批量删除
        try {
            qr.batch(sql, params);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        
    }
    /*
     * 方法：多条件查询书本
     */
    public List<Book> findBookBySearchkey(String id, String name, String category, String minprice, String maxprice) throws SQLException {
        QueryRunner qr = new QueryRunner(ds);
        String sql = "select * from books where 1=1";
        if(!"".equals(id)){
            sql += " and id="+ id;
        }
        if(!"".equals(name)){
            sql += " and name like'%"+ name +"%'";
        }
        if(!"".equals(category)){
            sql += " and category='"+ category +"'";
        }
        if(!"".equals(minprice)){
            sql += " and price >="+ minprice;
        }
        if(!"".equals(maxprice)){
            sql += " and price <="+ maxprice;
        }
        System.out.println(sql);
        return qr.query(sql, new BeanListHandler<Book>(Book.class));
    }
    /*
     * 方法：分页查询
    */
    public PageResult findBookByPage(int page) throws SQLException{
        
        QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
        
        //1.创建PageResult对象
        PageResult<Book> pr = new PageResult<Book>();
        
        //2.设置totalCount【总记录数】
        long totalCount = (long) qr.query("select count(*) from books", new ScalarHandler());
        pr.setTotalCount(totalCount);;
        
        //3.设置总页数 5/10
        long totalPage = totalCount % pr.getLimit() == 0 ? totalCount/pr.getLimit() :totalCount/pr.getLimit() + 1;
        pr.setTotalPage(totalPage);
        
        //4.设置当前页码
        pr.setCurrentPage(page);
        
        //5.设置pageresult里的list数据【库存排序】
        String sql = "select * from books order by pnum limit ?,?";
        int start = (page -1)*pr.getLimit();
        List<Book> books =  qr.query(sql, new BeanListHandler<Book>(Book.class),start,pr.getLimit());
        pr.setList(books);
        
        return pr;
    }
}

PageResult.java

package com.exp.model;

import java.util.List;

public class PageResult<T> {
    private List<T> list;//当前页数据集合
    private int currentPage;//当前页码
    private int limit = 3;//每页条数
    private long totalCount;//总记录数
    private long totalPage;//总页数
    
    public PageResult(List<T> list, int currentPage, int limit, long totalCount, long totalPage) {
        super();
        this.list = list;
        this.currentPage = currentPage;
        this.limit = limit;
        this.totalCount = totalCount;
        this.totalPage = totalPage;
    }

    public PageResult() {
        super();
        // TODO Auto-generated constructor stub
    }

    public List<T> getList() {
        return list;
    }

    public void setList(List<T> list) {
        this.list = list;
    }

    public int getCurrentPage() {
        return currentPage;
    }

    public void setCurrentPage(int currentPage) {
        this.currentPage = currentPage;
    }

    public int getLimit() {
        return limit;
    }

    public void setLimit(int limit) {
        this.limit = limit;
    }

    public long getTotalCount() {
        return totalCount;
    }

    public void setTotalCount(long totalCount) {
        this.totalCount = totalCount;
    }

    public long getTotalPage() {
        return totalPage;
    }

    public void setTotalPage(long totalPage) {
        this.totalPage = totalPage;
    }

    @Override
    public String toString() {
        return "PageResult [list=" + list + ", currentPage=" + currentPage + ", limit=" + limit + ", totalCount="
                + totalCount + ", totalPage=" + totalPage + "]";
    }
    
}