java web 分页 的测试

package cn.javabs.book.entity;

/**
 *  分类的实体类
 */
public class Category {
    private Integer id;
    private String name;
    private String description;

    //  安捷 alt + insert      生成 setter and getter  Constructor    Override Methods toString  方法
    public Category() {
        super();
    }

    @Override
    public String toString() {
        return "Category{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", description='" + description + '\'' +
                '}';
    }

    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 String getDescription() {
        return description;
    }

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

    public Category(Integer id, String name, String description) {
        this.id = id;
        this.name = name;
        this.description = description;
    }
}

package cn.javabs.book.entity;

/**
 *  图书的分类
 */
public class Book {
    private Integer id;
    private String name;
    private  String author;
    private  String publish;
    private  double price;
    private String description;

    //  图书的路径    = d://adc/123/wangcai.jpg
    private  String path;
    private String photoName;

    public Book() {
        super();
    }

    @Override
    public String toString() {
        return "Book{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", author='" + author + '\'' +
                ", publish='" + publish + '\'' +
                ", price=" + price +
                ", description='" + description + '\'' +
                ", path='" + path + '\'' +
                ", photoName='" + photoName + '\'' +
                ", category=" + category +
                '}';
    }

    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 String getAuthor() {
        return author;
    }

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

    public String getPublish() {
        return publish;
    }

    public void setPublish(String publish) {
        this.publish = publish;
    }

    public double getPrice() {
        return price;
    }

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

    public String getDescription() {
        return description;
    }

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

    public String getPath() {
        return path;
    }

    public void setPath(String path) {
        this.path = path;
    }

    public String getPhotoName() {
        return photoName;
    }

    public void setPhotoName(String photoName) {
        this.photoName = photoName;
    }

    public Category getCategory() {
        return category;
    }

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

    public Book(Integer id, String name, String author, String publish, double price, String description, String path, String photoName, Category category) {
        this.id = id;
        this.name = name;
        this.author = author;
        this.publish = publish;
        this.price = price;
        this.description = description;
        this.path = path;
        this.photoName = photoName;
        this.category = category;
    }

    private Category category;  // 与分类有关联


}

package cn.javabs.book.dao;

import cn.javabs.book.entity.Category;

import java.util.List;

public interface CategoryDao {
    /**
     *  分类的天剑
     * @param category
     * @return
     */
    int addCategory(Category category);

    /**
     *  分类的删除
     * @param id
     * @return
     */
    int delCategory(int id);

    /**
     *  分类的修改
     * @param category
     * @return
     */
    int updateCategory(Category category);

    /**
     *  分类的全部查询
     * @return
     */
    List<Category> getAllCategory();

    /**
     *  分类的根据id查询
     * @param id
     * @return
     */
    Category getCategoryById(int id);
}

package cn.javabs.book.dao.impl;

import cn.javabs.book.dao.CategoryDao;
import cn.javabs.book.entity.Category;
import cn.javabs.book.util.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

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

public class CategoryDaoImpl implements CategoryDao {
                      QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
    /**
     * 分类的天剑
     *
     * @param category
     * @return
     */
    @Override
    public int addCategory(Category category) {
        try {
          int  rows = qr.update("insert into category(name,descrtion)value (?,?)",category.getName(),category.getDescription());
            return rows;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }

    /**
     * 分类的删除
     *
     * @param id
     * @return
     */
    @Override
    public int delCategory(int id) {
        try {
            int  rows = qr.update("delete from category where  id=?",id);
            return rows;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }

    /**
     * 分类的修改
     *
     * @param category
     * @return
     */
    @Override
    public int updateCategory(Category category) {
        try {
            return qr.update("update category set nmae=?,description=? where id=?",
                    category.getName(),
                    category.getDescription(),
                    category.getId());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 分类的全部查询
     *
     * @return
     */
    @Override
    public List<Category> getAllCategory() {
       try {
           List<Category> categoryList = qr.query("select*from category", new BeanListHandler<Category>(Category.class));
           return categoryList;
       } catch (SQLException e){
           throw new RuntimeException(e);
       }
    }

    /**
     * 分类的根据id查询
     *
     * @param id
     * @return
     */
    @Override
    public Category getCategoryById(int id) {
        try {
           Category category = qr.query("select * from  category where id =?",new BeanHandler<Category>(Category.class),id);
            return category;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }
}

package cn.javabs.book.dao;

import cn.javabs.book.entity.Book;

import java.util.List;

/**
 *
 */
public interface BookDao {
    /**
     *  图书的添加
     * @param book
     * @return
     */
    int  addBook(Book book);

    /**
     * 删除图书
     * @param id
     * @return
     */
    int delBook(int id);

    /**
     * 修改图书
     * @param book
     * @return
     */
    int updateBook(Book book);
    Book getBookById(int id);

    /**
     *   记录有几条
     * @param startIndex   索引
     * @param pageSize  页数
     * @return
     */
    List<Book> getAllBookRecords(int startIndex,int pageSize );

    int getBooksNumber();


    /**
     *  共有几页
     * @param categoryId
     * @return
     */
    int getPageBookNumber(int categoryId);

    /**
     *  
     * @param starIndex
     * @param pageSize
     * @param categoryId
     * @return
     */
    List<Book> getPageBooks(int starIndex ,int pageSize,int categoryId);
}
package cn.javabs.book.dao.impl;

import cn.javabs.book.dao.BookDao;
import cn.javabs.book.entity.Book;
import cn.javabs.book.exe.BookDaoImplException;
import cn.javabs.book.util.DruidUtils;
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 java.sql.SQLException;
import java.util.List;

public class BookDaoImpl implements BookDao {
    QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());

    /**
     * 图书的添加
     *
     * @param book
     * @return
     */
    @Override
    public int addBook(Book book) {
        try {
            int rows = qr.update("insert into book (name,author,publish,price,description,path,photoName,categoryId)value (?,?,?,?,?,?,?,?)",
                    book.getName(),
                    book.getAuthor(),
                    book.getPublish(),
                    book.getPrice(),
                    book.getDescription(),
                    book.getPath(),
                    book.getPhotoName(),
                    book.getCategory().getId());
            return rows;
        } catch (SQLException e) {
            throw new BookDaoImplException(e);
        }

    }

    /**
     * 删除图书
     *
     * @param id
     * @return
     */
    @Override
    public int delBook(int id) {
        try {
            int rows = qr.update("delete from book where id=?", id);
            return rows;
        } catch (SQLException e) {
            throw new BookDaoImplException(e);
        }

    }

    /**
     * 修改图书
     *
     * @param book
     * @return
     */
    @Override
    public int updateBook(Book book) {
        try {
            int rows = qr.update("update  book set  name=?,author=?,publish=?,price=?,description=?,path=?,photoName=?,categoryId=? where id=?",
                    book.getName(),
                    book.getAuthor(),
                    book.getPublish(),
                    book.getPrice(),
                    book.getDescription(),
                    book.getPath(),
                    book.getPhotoName(),
                    book.getCategory().getId(),
                    book.getId());
            return rows;
        } catch (SQLException e) {
            throw new BookDaoImplException(e);
        }

    }

    /****************************查询********************************************/
    @Override
    public Book getBookById(int id) {

        try {
            Book book = qr.query("select * from book where id=?", new BeanHandler<>(Book.class), id);
            return book;
        } catch (SQLException e) {
            throw new BookDaoImplException(e);
        }
    }

    /**
     * 记录有几条
     *
     * @param startIndex 开始索引
     * @param pageSize   查询几条
     * @return
     */
    @Override
    public List<Book> getAllBookRecords(int startIndex, int pageSize) {
        try {
            List<Book> bookList = qr.query("select * from book limit", new BeanListHandler<Book>(Book.class), startIndex, pageSize);
            return bookList;
        } catch (SQLException e) {
            throw new BookDaoImplException(e);
        }
    }
    @Override
       public  int getBooksNumber(){
           try {
                      Object obj = qr.query("select  count (*) from book", new ScalarHandler<>(1));
                       Long num = (Long) obj;
                        int number = num.intValue();
                        return number;
                   } catch (SQLException e) {
                       throw new BookDaoImplException(e);
                   }
       }


    /**
     * 共有几页
     *
     * @param categoryId
     * @return
     */
    @Override
    public int getPageBookNumber(int categoryId) {
        try {
            Object obj = qr.query("select  count (*) from book where categoryId =?", new ScalarHandler<>(1), categoryId);
            Long num = (Long) obj;
            int number = num.intValue();
            return number;
        } catch (SQLException e) {
            throw new BookDaoImplException(e);
        }

    }



    /**
     * @param starIndex
     * @param pageSize
     * @param categoryId
     * @return
     */
    @Override
    public List<Book> getPageBooks(int starIndex, int pageSize, int categoryId) {
      try {
              List<Book> bookList = qr.query("select * from book where catagoryId =? ,limit ?,?", new BeanListHandler<Book>(Book.class), categoryId, starIndex, pageSize);
         return bookList;
          } catch (SQLException e) {
              throw new BookDaoImplException(e);
          }
      }
}

package cn.javabs.book.service;

import cn.javabs.book.entity.Category;

import java.util.List;

public interface CategoryService {
    /**
     *  分类添加
     * @param category
     * @return
     */
    int addCategory(Category category);

    /**
     * 修改分类
     * @param category
     * @return
     */
    int updateCategory(Category category);

    /**
     * 删除分类
     * @param id
     * @return
     */
    int delCategory (int id);

    /**
     * 根据id查询
     * @param id
     * @return
     */
    Category getCategoryById(int id);

    /**
     *  分类全部查询
     * @return
     */
    List<Category> getAllCategory();
}

package cn.javabs.book.service.impl;

import cn.javabs.book.dao.CategoryDao;
import cn.javabs.book.dao.impl.CategoryDaoImpl;
import cn.javabs.book.entity.Category;
import cn.javabs.book.service.CategoryService;

import java.util.List;

public class CategoryServiceImpl implements CategoryService {
  CategoryDao categoryDao = new CategoryDaoImpl();
    /**
     * 分类添加
     *
     * @param category
     * @return
     */
    @Override
    public int addCategory(Category category) {

        return categoryDao.addCategory(category);
    }

    /**
     * 修改分类
     *
     * @param category
     * @return
     */
    @Override
    public int updateCategory(Category category) {
        return categoryDao.updateCategory(category);
    }

    /**
     * 删除分类
     *
     * @param id
     * @return
     */
    @Override
    public int delCategory(int id) {
        return categoryDao.delCategory(id);
    }

    /**
     * 根据id查询
     *
     * @param id
     * @return
     */
    @Override
    public Category getCategoryById(int id) {
        return categoryDao.getCategoryById(id);
    }

    /**
     * 分类全部查询
     *
     * @return
     */
    @Override
    public List<Category> getAllCategory() {
        return categoryDao.getAllCategory();
    }
}

package cn.javabs.book.service;

import cn.javabs.book.entity.Book;
import cn.javabs.book.util.Page;

public interface BookService {
    /**
     *  添加的图书
     * @param book
     * @return
     */
    int addBook(Book book);

    /**
     * 修改图书
     * @param book
     * @return
     */
    int updateBook(Book book);

    /**
     * 删除图书
     * @param id
     * @return
     */
    int delBook(int id);

    /**
     *  查询所有图书
     * @param pageNum 页面
     * @return
     */
    Page  findAllBooks(int pageNum);

    /**
     *  格局图书分类的id 及分页当前页面进行查询的图书
     * @param pageNum
     * @param categoryId
     * @return
     */
    Page findAllBookPageRecords(int pageNum,int categoryId );
}

package cn.javabs.book.service.impl;

import cn.javabs.book.dao.BookDao;
import cn.javabs.book.dao.impl.BookDaoImpl;
import cn.javabs.book.entity.Book;
import cn.javabs.book.service.BookService;
import cn.javabs.book.util.Page;

import java.util.List;

public class BookServiceImpl implements BookService {
    BookDao bookDao = new BookDaoImpl();
    /**
     * 添加的图书
     *
     * @param book
     * @return
     */
    @Override
    public int addBook(Book book) {
        return bookDao.addBook(book);
    }

    /**
     * 修改图书
     *
     * @param book
     * @return
     */
    @Override
    public int updateBook(Book book) {
        return bookDao.updateBook(book);
    }

    /**
     * 删除图书
     *
     * @param id
     * @return
     */
    @Override
    public int delBook(int id) {
        return bookDao.delBook(id);
    }

    /**
     * 查询所有图书
     *
     * @param pageNum 页面
     * @return
     */
    @Override
    public Page findAllBooks(int pageNum) {
        // 判断是否为空   是引用类型
//        Integer number = pageNum;
//        if (number != null){}

        pageNum = 1;
        //  1. 查询数据有多少条| 假设 booksNumber = 7
        int bookNumber = bookDao.getBooksNumber();
        // 2. 实例化 page
        Page page = new Page(pageNum, bookNumber);
        //  3.作为page 中的定义为变量  每条的数据 |  固定的值为 3
        int startIndex = page.getStartIndex();
        // 4. 查询所有条数因为他要分页  需要索引值 和 条数
         int pagePageSize = page.getPageSize();
         // 5. 将已经查询的所有数据放到Page类中
        List<Book> list = bookDao.getAllBookRecords(startIndex, pagePageSize);
        page.setRecords(list);
        return page;

    }

    /**
     * 格局图书分类的id 及分页当前页面进行查询的图书
     *
     * @param pageNum
     * @param categoryId
     * @return
     */
    @Override
    public Page findAllBookPageRecords(int pageNum, int categoryId) {
        return null;
    }
}

package cn.javabs.book.exe;

import java.sql.SQLException;

public class BookDaoImplException extends RuntimeException {
    /**
     * Constructs a new runtime exception with {@code null} as its
     * detail message.  The cause is not initialized, and may subsequently be
     * initialized by a call to {@link #initCause}.
     */
    public BookDaoImplException() {
        super();
    }

    /**
     * Constructs a new runtime exception with the specified detail message.
     * The cause is not initialized, and may subsequently be initialized by a
     * call to {@link #initCause}.
     *
     * @param message the detail message. The detail message is saved for
     *                later retrieval by the {@link #getMessage()} method.
     */
    public BookDaoImplException(String message) {
        super(message);
    }

    /**
     * Constructs a new runtime exception with the specified detail message and
     * cause.  <p>Note that the detail message associated with
     * {@code cause} is <i>not</i> automatically incorporated in
     * this runtime exception's detail message.
     *
     * @param message the detail message (which is saved for later retrieval
     *                by the {@link #getMessage()} method).
     * @param cause   the cause (which is saved for later retrieval by the
     *                {@link #getCause()} method).  (A <tt>null</tt> value is
     *                permitted, and indicates that the cause is nonexistent or
     *                unknown.)
     * @since 1.4
     */
    public BookDaoImplException(String message, Throwable cause) {
        super(message, cause);
    }

    /**
     * Constructs a new runtime exception with the specified cause and a
     * detail message of <tt>(cause==null ? null : cause.toString())</tt>
     * (which typically contains the class and detail message of
     * <tt>cause</tt>).  This constructor is useful for runtime exceptions
     * that are little more than wrappers for other throwables.
     *
     * @param cause the cause (which is saved for later retrieval by the
     *              {@link #getCause()} method).  (A <tt>null</tt> value is
     *              permitted, and indicates that the cause is nonexistent or
     *              unknown.)
     * @since 1.4
     */
    public BookDaoImplException(Throwable cause) {
        super(cause);
    }

    /**
     * Constructs a new runtime exception with the specified detail
     * message, cause, suppression enabled or disabled, and writable
     * stack trace enabled or disabled.
     *
     * @param message            the detail message.
     * @param cause              the cause.  (A {@code null} value is permitted,
     *                           and indicates that the cause is nonexistent or unknown.)
     * @param enableSuppression  whether or not suppression is enabled
     *                           or disabled
     * @param writableStackTrace whether or not the stack trace should
     *                           be writable
     * @since 1.7
     */
    protected BookDaoImplException(String message, Throwable cause, boolean enableSuppression, boolean writableStackTrace) {
        super(message, cause, enableSuppression, writableStackTrace);
    }
}

package cn.javabs.book.util;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;

public class DruidUtils {
    public static DataSource dataSource;
static {
    try {
    InputStream in = DruidUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");
    Properties properties = new Properties();
    properties.load(in);
    dataSource  = DruidDataSourceFactory.createDataSource(properties);
    } catch (Exception e) {
        throw new RuntimeException(e);
    }
}
public static DataSource getDataSource(){
    return dataSource;
}
public static  Connection getConnection() throws SQLException {
    return dataSource.getConnection();
}
}

package cn.javabs.book.util;

import java.util.List;

/**
 * 分页
 */
public class Page {
    private List  records;// 记录的条数

    private int pageSize = 3; // 每页显示的几条

    private  int currentPage ; // 当前页
    
    private  int totalPage; // 总条数

    private  int  prePageNum; // 上一页

    private  int  nextPageNum; // 下一页

    private  int startIndex; // 开始索引
    
    private  int totalRecord; // 总条数

    private  String url;// 连接

    public List getRecords() {
        return records;
    }

    public void setRecords(List records) {
        this.records = records;
    }

    public int getPageSize() {
        return pageSize;
    }

    public void setPageSize(int pageSize) {
        this.pageSize = pageSize;
    }

    public int getCurrentPage() {
        return currentPage;
    }

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

    public int getTotalPage() {
        return totalPage;
    }

    public void setTotalPage(int totalPage) {
        this.totalPage = totalPage;
    }
      // 上一页
    public int getPrePageNum() {
        prePageNum = currentPage-1>0? currentPage -1 : 1;
        return prePageNum;
    }

    public void setPrePageNum(int prePageNum) {
        this.prePageNum = prePageNum;
    }
      // 下一页
    public int getNextPageNum() {
        nextPageNum = currentPage + 1 > totalPage ? totalPage : currentPage + 1;
        return nextPageNum;
    }

    public void setNextPageNum(int nextPageNum) {
        this.nextPageNum = nextPageNum;
    }

    public int getStartIndex() {
        return startIndex;
    }

    public void setStartIndex(int startIndex) {
        this.startIndex = startIndex;
    }

    public int getTotalRecord() {
        return totalRecord;
    }

    public void setTotalRecord(int totalRecord) {
        this.totalRecord = totalRecord;
    }

    public String getUrl() {
        return url;
    }

    public void setUrl(String url) {
        this.url = url;
    }

    /**
     *  有参的构造方法
     * @param currentPage    当前页
     * @param totalRecord     总条数
     */

    public Page(int currentPage, int totalRecord) {
        this.currentPage = currentPage;
        this.totalRecord = totalRecord;

        totalPage = totalRecord % pageSize == 0? totalRecord / pageSize : totalRecord /pageSize+ 1 ;
    }
}

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql//localhost:3306/bookstore
username=root
password=root

create DataBase BookStore;
create table   Category(
id int  primary key,
name varchar(20) not null,
description varchar (20) not null
);

create table book(
id int primary key auto_increment,
name varchar(40) not null,
author varchar (30),
publish varchar (30),
price  varchar (30),
description varchar(50),
path varchar(30),
photoName varchar(30),
categoryId int,
);


alter  table book add foreign key(category)  references category(id);

![在这里插入图片描述](https://img-blog.csdnimg.cn/20210108112922834.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzQ2MjA3NzM