Web图书管理系统 |
您所在的位置：网站首页 › web列表分页 › Web图书管理系统 |
Web图书管理系统
|
一：数据库设计
1.默认访问时分页显示出图书列表，进行隔行变色，并且对上一页和下一页进行限定 当前页数为1时，点击上一页给出提示”已经是第一页了，请点击下一页进行查看” 当前页数和总页数相等时，点击下一页给出提示“已经是最后一页了，请点击上一页进行查看” 如图一所示：
2.可以根据图书名称和图书分类进行模糊查询，并且分页展示数据，进行隔行变色，并且对上一页和下一页进行限定，当前页数为1时，点击上一页给出提示”已经是第一页了，请点击下一页进行查看”，当前页数和总页数相等时，点击下一页给出提示“已经是最后一页了，请点击上一页进行查看” 连接数据库工具类：BaseDao package cn.book.util; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; //数据库连接与关闭工具类 public class BaseDao { public static final String driver = "com.mysql.jdbc.Driver"; public static final String url = "jdbc:mysql://localhost:3306/bookmanager"; public static final String username = "root"; public static final String password = ""; // 1.3 创建和数据库交互的三大对象 protected Connection con; // 连接对象 protected PreparedStatement ps;// 命令对象 protected ResultSet rs; // 结果集对象（读取器对象） // 1.4 获取数据库连接对象 public Connection getConnection() { try { Class.forName(driver); // 如果连接对象为空 ，或者连接被关闭，重新构建连接对象 if (con == null || con.isClosed()) { con = DriverManager.getConnection(url, username, password); } } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } return con; } // 1.5 释放数据库连接对象 public void closeAll() { // 若结果集对象不为空，则关闭 try { if (rs != null) { rs.close(); } if (ps != null) { ps.close(); } if (con != null) { con.close(); } } catch (Exception ex) { ex.printStackTrace(); } } // 1.6 写一个方法：执行增删改操作 public int executeUpdate(String sql, Object... objs) throws Exception { con = getConnection(); ps = con.prepareStatement(sql); for (int i = 0; i < objs.length; i++) { ps.setObject(i + 1, objs[i]); } int count = ps.executeUpdate(); return count; } // 1.7 写一个方法 ，执行查询操作 public ResultSet executeSelect(String sql, Object... prams) { con = getConnection(); try { ps = con.prepareStatement(sql); for (int i = 0; i < prams.length; i++) { ps.setObject(i + 1, prams[i]); } rs = ps.executeQuery(); } catch (SQLException e) { e.printStackTrace(); } finally { } return rs; } }图书接口：BookDao /** * * 图书接口 * */ public interface BookDao { //查询图书列表(分页) public List selectbook(int pageIndex,int pageSize) throws Exception; //查询Book表的记录数 public int getCount() throws Exception; //按图书名称查询(分页) public List likebook(int category,String name,int pageIndex, int pageSize) throws Exception; //查询Book表的记录数 public int getselectCount(int category,String name) throws Exception; //删除book public int deletebook(int id) throws Exception; //查询book public Book uploadbook(int id) throws Exception; //查询book分类 public List selectcategory() throws Exception; //修改图书 public int bookupdate(Book book,int id) throws Exception; //添加图书 public int addbook(Book book) throws Exception; } 简单分页和多条件分页的方法： // 查询图书列表（分页） @Override public List selectbook(int pageIndex, int pageSize) throws Exception { // 创建list集合存放book对象 List list = new ArrayList(); String sql = "select * from book limit ?,?"; Object[] obj = { pageIndex, pageSize }; ResultSet rs = executeSelect(sql, obj); if (rs != null) { while (rs.next()) { // 创建book对象 Book book = new Book(); book.setBookid(rs.getInt("bookid")); book.setBookname(rs.getString("bookname")); book.setBookpicture(rs.getString("bookpicture")); book.setBookprice(rs.getDouble("bookprice")); book.setBookabout(rs.getString("bookabout")); book.setBookauthor(rs.getString("bookauthor")); book.setBookcategory(rs.getInt("bookcategory")); book.setBookdatatime(rs.getDate("bookdatetime")); list.add(book); } } return list; } // 查询book表中的记录数 @Override public int getCount() throws Exception { int result = 0; String sql = "select count(*) as num from book"; ResultSet rs = executeSelect(sql); if (rs != null) { if (rs.next()) { result = rs.getInt("num"); } closeAll(); } return result; } // 按名称模糊查询（分页） @Override public List likebook(int category, String name,int pageIndex, int pageSize) throws Exception { // 创建list集合存放book对象 List list = new ArrayList(); StringBuffer sb=new StringBuffer("select * from book where 1=1"); if(category!=0) { sb=sb.append(" and bookcategory='"+category+"' "); } if(name!="") { sb=sb.append(" and bookname like '%"+name+"%'"); } sb=sb.append(" limit ?,?"); Object[] obj = { pageIndex, pageSize }; ResultSet rs = executeSelect(sb.toString(), obj); if (rs != null) { while (rs.next()) { // 创建book对象 Book book = new Book(); book.setBookid(rs.getInt("bookid")); book.setBookname(rs.getString("bookname")); book.setBookpicture(rs.getString("bookpicture")); book.setBookprice(rs.getDouble("bookprice")); book.setBookabout(rs.getString("bookabout")); book.setBookauthor(rs.getString("bookauthor")); book.setBookcategory(rs.getInt("bookcategory")); book.setBookdatatime(rs.getDate("bookdatetime")); list.add(book); } } return list; } @Override public int getselectCount(int category,String name) throws Exception { int result = 0; StringBuffer sb=new StringBuffer("select count(*) as num from book where 1=1 "); if(category!=0) { sb=sb.append(" and bookcategory='"+category+"' "); } if(name!="") { sb=sb.append(" and bookname like '%"+name+"%'"); } ResultSet rs = executeSelect(sb.toString()); if (rs != null) { if (rs.next()) { result = rs.getInt("num"); } closeAll(); } return result; } |
【本文地址】
今日新闻 |
推荐新闻 |