Java Web图书管理系统需要的模板

JDBC（增删改查借）

使用的是PreparedStatement接口实现

JDBCUtil 工具类

工具类负责所有调用mysql数据库的建立连接、执行sql语句、回收的所有功能

public class JdbcUtil {
	public static String driverName = "com.mysql.jdbc.Driver";
	public static String url = "jdbc:mysql://localhost:3306/test";
	public static String username = "root";
	public static String password = "sa";

	static {
		try {
			Class.forName(driverName);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	public static Connection getConn() {
		try {
			return DriverManager.getConnection(url, username, password);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	public static void close(Connection conn, PreparedStatement ps, ResultSet res) {
		if (res != null) {
			try {
				res.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (ps != null) {
			try {
				ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}

创建图书

public void create(Book b) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		try {
			conn = JdbcUtil.getConn();
			String sql = "insert into book(bookid,bookname,author,price,note,borrow,appoint,renew,username,time)values(?,?,?,?,?,?,?,?,?,?)";
			ps = conn.prepareStatement(sql);
			ps.setString(1, b.getBookId());
			ps.setString(2, b.getBookname());
			ps.setString(3, b.getAuthor());
			ps.setString(4, b.getPrice());
			ps.setString(5, b.getNote());
			ps.setInt(6, 0);
			ps.setInt(7, 0);
			ps.setInt(8, 0);
			ps.setString(9, "");
			ps.setInt(10, 0);
			ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.close(conn, ps, res);
		}
	}

删除图书

public void Delete(String bookid) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		try {
			conn = JdbcUtil.getConn();
			String sql = "delete from book where bookid=?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, bookid);
			ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.close(conn, ps, res);
		}
	}

查询图书

public Book Query(String bookid) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		try {
			conn = JdbcUtil.getConn();
			String sql = "select * from book where bookid=?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, bookid);
			res = ps.executeQuery();
			if (res.next()) {
				Book b = new Book(bookid);
				b.setBookname(res.getString("bookname"));
				b.setAuthor(res.getString("author"));
				b.setPrice(res.getString("price"));
				b.setNote(res.getString("note"));
				b.setBorrow(res.getInt("borrow"));
				b.setAppoint(res.getInt("appoint"));
				b.setRenew(res.getInt("renew"));
				b.setUsername(res.getString("username"));
				b.setTime(res.getInt("time"));
				JdbcUtil.close(conn, ps, res);
				return b;
			} else {
				Book b = new Book("No");
				JdbcUtil.close(conn, ps, res);
				return b;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.close(conn, ps, res);
		}
		return null;
	}

修改图书

public int Modify(Book b) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		try {
			conn = JdbcUtil.getConn();
			String sql = "update book set bookname=?,author=?,price=?,note=?,borrow=?,appoint=?,renew=?,username=?,time=? where bookid=?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, b.getBookname());
			ps.setString(2, b.getAuthor());
			ps.setString(3, b.getPrice());
			ps.setString(4, b.getPrice());
			ps.setInt(5, b.getBorrow());
			ps.setInt(6, b.getAppoint());
			ps.setInt(7, b.getRenew());
			ps.setString(8, b.getBookId());
			ps.setString(9, b.getUsername());
			ps.setInt(10, b.getTime());
			int n = ps.executeUpdate();
			JdbcUtil.close(conn, ps, res);
			return n;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.close(conn, ps, res);
		}
		return 0;
	}

图书借阅

public String Borrow(String username, String bookid) {
		Connection conn = null;
		PreparedStatement ps = null;
		PreparedStatement ps2 = null;
		ResultSet res = null;
		try {
			conn = JdbcUtil.getConn();
			String sql = "select * from book where bookid=?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, bookid);
			res = ps.executeQuery();
			if (res.next()) {
				if (res.getInt("borrow") == 0) {
					if (res.getInt("appoint") == 0) {
						String sql1 = "update book set borrow=?,username=?,time=? where bookid=?";
						ps2 = conn.prepareStatement(sql1);
						ps2.setInt(1, 1);
						ps2.setString(2, username);
						ps2.setInt(3, 1);
						ps2.setString(4, bookid);
						ps2.executeUpdate();
						ps2.close();
						JdbcUtil.close(conn, ps, res);
						return "借阅成功";
					} else {
						JdbcUtil.close(conn, ps, res);
						return "该书已经被预约";
					}
				} else {
					JdbcUtil.close(conn, ps, res);
					return "该书已经被借阅";
				}
			} else {
				JdbcUtil.close(conn, ps, res);
				return "查无此书";
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.close(conn, ps, res);
		}
		return "error";
	}

Servlet中的request、response的设置中文

request.setCharacterEncoding("UTF-8");
response.setContentType("text/html;charset=UTF-8");