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<Book> selectbook(int pageIndex,int pageSize) throws Exception;
	//查询Book表的记录数
	public int getCount() throws Exception;
	//按图书名称查询(分页)
	public List<Book> 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<BookCategory> selectcategory() throws Exception;
	//修改图书
	public int bookupdate(Book book,int id) throws Exception;
	//添加图书
	public int addbook(Book book) throws Exception;
}

简单分页和多条件分页的方法：

	// 查询图书列表（分页）
	@Override
	public List<Book> selectbook(int pageIndex, int pageSize) throws Exception {
		// 创建list集合存放book对象
		List<Book> list = new ArrayList<Book>();
		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<Book> likebook(int category, String name,int pageIndex, int pageSize)
			throws Exception {
		// 创建list集合存放book对象
		List<Book> list = new ArrayList<Book>();
		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;
	}