javaWEB项目——在线图书馆管理系统

package dao; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.List; import java.util.UUID; 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 org.junit.Test; import entity.Book; import utils.JdbcUtil; import utils.PageBean; public class BooksDao { public void listAllBooks(PageBean<Book> pb) throws Exception { //得到总共的记录数 int totalCount = this.getTotalCount(); pb.setTotalCount(totalCount); pb.setTotalPage(totalCount, pb.getPageCount()); if(pb.getCurrentPage()<=0){ pb.setCurrentPage(1); }else if(pb.getCurrentPage()>pb.getTotalPage()){ pb.setCurrentPage(pb.getTotalPage()); } int index = 4*(pb.getCurrentPage()-1); QueryRunner qr = JdbcUtil.getQueryRunner(); String sql = "SELECT *FROM books LIMIT ?,?"; List<Book> books = qr.query(sql, new BeanListHandler<Book>(Book.class),index,pb.getPageCount()); //这一句代码非常的关键，将查询到的数据封装进pageBean对象中。 pb.setPageData(books); } public int getTotalCount() throws Exception{ QueryRunner qr = JdbcUtil.getQueryRunner(); String sql = "SELECT COUNT(*) FROM books"; Long totalCount = qr.query(sql, new ScalarHandler<Long>()); return totalCount.intValue(); } @Test /* public List<Book> listAllBooks() throws Exception { QueryRunner qr = JdbcUtil.getQueryRunner(); String sql = "SELECT *FROM books"; List<Book> books = qr.query(sql, new BeanListHandler<Book>(Book.class)); return books; // System.out.println(books); }*/ public Book findById(String id) throws Exception{ /*Connection conn; conn = JdbcUtil.getConnection(); QueryRunner qr = new QueryRunner();*/ QueryRunner qr = JdbcUtil.getQueryRunner(); String sql = "SELECT *FROM books where id=?"; Book book = qr.query( sql, new BeanHandler<Book>(Book.class),id); return book; } public void addBook(Book book) { /* conn = JdbcUtil.getConnection(); QueryRunner qr = new QueryRunner();*/ QueryRunner qr = JdbcUtil.getQueryRunner(); String sql = "INSERT INTO books VALUES(?,?,?,?,?)"; String id = UUID.randomUUID().toString().replace("-",""); try { qr.update( sql, id,book.getBookName(),book.getAuthor(),book.getPublisher(),book.getPrice()); } catch (SQLException e) { e.printStackTrace(); } } public void deleteBook(String id) { QueryRunner qr = JdbcUtil.getQueryRunner(); String sql = "DELETE FROM books WHERE id=?"; try { qr.update( sql, id); } catch (SQLException e) { e.printStackTrace(); } } public void updateBook(Book book) { QueryRunner qr = JdbcUtil.getQueryRunner(); String sql = "UPDATE books SET bookName=?,author=?,publisher=?,price=? WHERE id=?"; try { qr.update( sql, book.getBookName(),book.getAuthor(),book.getPublisher(),book.getPrice(),book.getId()); } catch (SQLException e) { e.printStackTrace(); } } public List<Book> queryBook(String bookName) { try { QueryRunner qr = JdbcUtil.getQueryRunner(); String sql = "SELECT *FROM books WHERE bookName LIKE ? "; bookName = bookName+"%"; List<Book> books = qr.query(sql, new BeanListHandler<Book>(Book.class),bookName); return books; } catch (Exception e) { e.printStackTrace(); return null; } } }