3.设计实现一个图书管理系统。图书信息存放到一个数据库中。图书包含信息:图书号、图书名、作者、价格、备注字段。
技术要求:基于JSP+Servlet+JavaBean+JDBC+DAO的Web架构。(利用EL+JSTL显示)
业务功能要求:系统要实现如下的基本管理功能:
(1)用户分为两类:管理员和普通用户。
(2)提供用户注册和用户登录验证功能;其中一个登录用户的信息有:登录用户名,登录密码。
(3)管理员可以实现对注册用户的管理(删除),并实现对图书的创建、查询、修改和删除等有关的操作
(4)普通用户,只能查询图书,并进行借书、还书操作,每个用户最多借阅8本,即当目前借书已经是8本,则不能再借书了,只有还书后,才可以再借阅。
只是存一下代码,很多功能没有实现(借阅模块、搜索模块)
add-book-.jsp:
<%@ page language="java" import="java.sql.*" pageEncoding="utf-8"%>
<%@ page import="Dao.BreaderDao" %>
<%@ page import="Dao.BookDao" %>
<%@page import="beans.b_reader"%>
<%@page import="beans.b_reader"%>
<%@page import="beans.book"%>
<%@ page import="java.util.List" %>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>Title</title>
</head>
<body>
<center>
<p1><font size="5" color="aqua">欢迎来到图书管理系统</font></p1>
<hr color="red" size="3">
<a href="book-register.jsp">读者注册</a>  
<a href="book-login.jsp">读者登陆</a>  
<a href="book-manager.jsp">管理员登陆</a>  
<hr color="red" size="3">
<form action="add-book-.jsp" method="post">
id:<input type="text" name="id" ></br>
书名:<input type="text" name="name" ></br>
作者:<input type="text" name="author" ></br>
价格:<input type="text" name="price"></br>
备注:<input type="text" name="info"></br>
<input type="submit" value="添加">
</form>
</center>
</body>
</html>
add-book-.jsp:
<%@ page language="java" import="java.sql.*" pageEncoding="utf-8"%>
<%@ page import="Dao.BreaderDao" %>
<%@ page import="Dao.BookDao" %>
<%@page import="beans.b_reader"%>
<%@page import="beans.b_reader"%>
<%@page import="beans.book"%>
<%@ page import="java.util.List" %>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>Title</title>
</head>
<body>
<%
request.setCharacterEncoding("utf-8");
response.setContentType("text/html;charset=utf-8");
String id_ = request.getParameter("id");
String name=request.getParameter("name");
String author=request.getParameter("author");
String price_=request.getParameter("price");
String info=request.getParameter("info");
if(id_!=null && id_!="")
{
int id=Integer.parseInt(id_);
float price=Float.parseFloat(price_);
BookDao book = new BookDao();
book b=new book();
b.setBid(id);
b.setBname(name);
b.setBauthor(author);
b.setPrice(price);
b.setBinfo(info);
book.add(b);
}
request.getRequestDispatcher("edit-book.jsp").forward(request,response);
%>
</body>
</html>
book-login.jsp:
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>Title</title>
</head>
<body>
<center>
<p1><font size="5" color="aqua">欢迎来到图书管理系统</font></p1>
<hr color="red" size="3">
<a href="book-register.jsp">读者注册</a>  
<a href="book-login.jsp">读者登陆</a>  
<a href="book-manager.jsp">管理员登陆</a>  
<hr color="red" size="3">
<p1><font size="5" color="black"> 读者登陆</font></p1></br>
<form action="book-login-.jsp" method="post">
账号:<input type="text" name="id"></br>
密码:<input type="password" name="pwd"></br>
<input type="submit" value="登陆">
</form>
</center>
</body>
</html>
book-login-.jsp:
<%@ page language="java" import="java.sql.*" pageEncoding="utf-8"%>
<%@ page import="Dao.BreaderDao" %>
<%@ page import="Dao.BookDao" %>
<%@page import="beans.b_reader"%>
<%@page import="beans.book"%>
<%@ page import="java.util.List" %>
<html>
<head>
<title>Title</title>
</head>
<body>
<%
request.setCharacterEncoding("utf-8");
response.setContentType("text/html;charset=utf-8");
String id = request.getParameter("id");
String pwd=request.getParameter("pwd");
if(id!=null&&pwd!=null)
{
BreaderDao reader=new BreaderDao();
try
{
if(reader.Match(id,pwd))
{%>
<jsp:include page="reader-main.jsp">
<jsp:param name="id" value="<%=id%>" />
<jsp:param name="pwd" value="<%=pwd%>" />
</jsp:include><%}
else{ out.write("账号密码错误<br>");}
}catch (Exception e) { out.write(e.getMessage()); }
} else{ out.write("不能为空<br>");}
%>
</body>
</html>
book-main.jsp:
<%--
Created by IntelliJ IDEA.
User: ssssssbbbbbb
Date: 2022/10/21
Time: 2:34 PM
To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>图书管理系统</title>
</head>
<body>
<center>
<p1><font size="5" color="aqua">欢迎来到图书管理系统</font></p1>
<hr color="red" size="3">
<a href="book-register.jsp">读者注册</a>  
<a href="book-login.jsp">读者登陆</a>  
<a href="book-manager.jsp">管理员登陆</a>  
</center>
</body>
</html>
book-manager.jsp:
<%--
Created by IntelliJ IDEA.
User: ssssssbbbbbb
Date: 2022/10/21
Time: 2:34 PM
To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>图书管理系统</title>
</head>
<body>
<center>
<p1><font size="5" color="aqua">欢迎来到图书管理系统</font></p1>
<hr color="red" size="3">
<a href="book-register.jsp">读者注册</a>  
<a href="book-login.jsp">读者登陆</a>  
<a href="book-manager.jsp">管理员登陆</a>  
</center>
</body>
</html>
book-register.jsp:
<%--
Created by IntelliJ IDEA.
User: ssssssbbbbbb
Date: 2022/10/21
Time: 3:37 PM
To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>读者注册页面</title>
</head>
<body>
<center>
<p1><font size="5" color="aqua">欢迎来到图书管理系统</font></p1>
<hr color="red" size="3">
<a href="book-register.jsp">读者注册</a>  
<a href="book-login.jsp">读者登陆</a>  
<a href="book-manager.jsp">管理员登陆</a>  
<hr color="red" size="3">
<p1><font size="5" color="black"> 读者注册</font></p1></br>
<form action="book-register-.jsp" method="post">
账号:<input type="text" name="id"></br>
密码:<input type="password" name="pwd"></br>
<input type="submit" value="注册">
</form>
</center>
</body>
</html>
book-register-.jsp:
<%@ page language="java" import="java.sql.*" pageEncoding="utf-8"%>
<%@ page import="Dao.BreaderDao" %>
<%@ page import="Dao.BookDao" %>
<%@page import="beans.b_reader"%>
<%@page import="beans.book"%>
<%@ page import="java.util.List" %>
<html>
<head>
<title>读者主页</title>
</head>
<body>
<center>
<%
request.setCharacterEncoding("utf-8");
response.setContentType("text/html;charset=utf-8");
String id = request.getParameter("id");
String pwd=request.getParameter("pwd");
if(id!=null && id!=""&&pwd!=null&&pwd!="")
{
BreaderDao userDao = new BreaderDao();
b_reader user=new b_reader();
user.setRid(id);
user.setRpass(pwd);
try
{
userDao.add(user);
}catch (Exception e){out.print("id已存在");}
}
%>
<jsp:include page="reader-main.jsp">
<jsp:param name="id" value="<%=id%>" />
<jsp:param name="pwd" value="<%=pwd%>" />
</jsp:include>
</center>
</body>
</html>
delete-book.jsp:
<%@ page language="java" import="java.sql.*" pageEncoding="utf-8"%>
<%@ page import="Dao.BreaderDao" %>
<%@ page import="Dao.BookDao" %>
<%@page import="beans.b_reader"%>
<%@page import="beans.b_reader"%>
<%@page import="beans.book"%>
<%@ page import="java.util.List" %>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>删除图书</title>
</head>
<body>
<%
request.setCharacterEncoding("utf-8");
response.setContentType("text/html;charset=utf-8");
String name = request.getParameter("id");
out.print(name);
if(name!=null&&name!="")
{
int id=Integer.parseInt(name);
BookDao book = new BookDao();
book.delete(id);
request.getRequestDispatcher("edit-book.jsp").forward(request,response);
}
%>
</body>
</html>
delete-reader.jsp:
<%@ page language="java" import="java.sql.*" pageEncoding="utf-8"%>
<%@ page import="Dao.BreaderDao" %>
<%@ page import="Dao.BookDao" %>
<%@page import="beans.b_reader"%>
<%@page import="beans.b_reader"%>
<%@page import="beans.book"%>
<%@ page import="java.util.List" %>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>删除读者</title>
</head>
<body>
<%
request.setCharacterEncoding("utf-8");
response.setContentType("text/html;charset=utf-8");
String name = request.getParameter("id");
if(name!=null&&name!="")
{
BreaderDao reader = new BreaderDao();
reader.delete(name);
request.getRequestDispatcher("edit-reader.jsp").forward(request,response);
}
%>
</body>
</html>
edit-book.jsp:
<%@ page language="java" import="java.sql.*" pageEncoding="utf-8"%>
<%@ page import="Dao.BreaderDao" %>
<%@ page import="Dao.BookDao" %>
<%@page import="beans.b_reader"%>
<%@page import="beans.b_reader"%>
<%@page import="beans.book"%>
<%@ page import="java.util.List" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>编辑图书</title>
</head>
<body>
<center>
<p1><font size="5" color="aqua">欢迎来到图书管理系统</font></p1>
<hr color="red" size="3">
<a href="book-register.jsp">读者注册</a>  
<a href="book-login.jsp">读者登陆</a>  
<a href="book-manager.jsp">管理员登陆</a>  
<hr color="red" size="3">
<%
String id = request.getParameter("id");
%>
<p1><font size="5" color="aqua">欢迎,<%=id%></font></p1>
<hr color="red" size="3">
<a href="add-book.jsp">添加图书</a>
<%! List<book> b =null; %>
<% BookDao bookDao = new BookDao();
b = bookDao.queryAll();
request.setAttribute("result",b);
%>
<h4>库中共有<font size="5" > <%=b.size()%></font>本书</h4>
<table border="2" width="650">
<tr align="center">
<td>id</td> <td>书名</td><td>作者</td> <td>价格</td><td>备注</td><td>操作</td>
</tr>
<c:forEach items="${result}" var="books" >
<tr align="center">
<td>${books.bid}</td>
<td>${books.bname}</td>
<td>${books.bauthor}</td>
<td>${books.price}</td>
<td>${books.binfo}</td>
<td><a href="delete-book.jsp?id=${books.bid}">删除</a>
<a href="update-book.jsp?id=${books.bid}">修改</a>
</td>
</tr>
</c:forEach>
</table>
<%// request.getRequestDispatcher("Main.jsp").forward(request,response);
%>
</center>
</body>
</html>
edit-reader.jsp:
<%@ page language="java" import="java.sql.*" pageEncoding="utf-8"%>
<%@ page import="Dao.BreaderDao" %>
<%@ page import="Dao.BookDao" %>
<%@page import="beans.b_reader"%>
<%@page import="beans.b_reader"%>
<%@page import="beans.book"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%@ page import="java.util.List" %>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>编辑读者</title>
</head>
<body>
<center>
<p1><font size="5" color="aqua">欢迎来到图书管理系统</font></p1>
<hr color="red" size="3">
<a href="book-register.jsp">读者注册</a>  
<a href="book-login.jsp">读者登陆</a>  
<a href="book-manager.jsp">管理员登陆</a>  
<hr color="red" size="3">
<%
String id = request.getParameter("id");
%>
<%! List<b_reader> reader =null; %>
<% BreaderDao readerDao = new BreaderDao();
reader = readerDao.queryAll();
request.setAttribute("result",reader);
%>
<h4>共有<font size="5" > <%=reader.size()%></font>位读者</h4>
<table border="2" width="650">
<tr align="center">
<td>id</td> <td>密码</td><td>借书数量</td> <td>操作</td>
</tr>
<c:forEach items="${result}" var="books" >
<tr align="center">
<td>${books.rid}</td>
<td>${books.num}</td>
<td><a href="delete-reader.jsp?id=${books.num}">删除</a>
</td>
</tr>
</c:forEach>
</table>
<%// request.getRequestDispatcher("Main.jsp").forward(request,response);
%>
</center>
</body>
</html>
manager.jsp:
<%--
Created by IntelliJ IDEA.
User: ssssssbbbbbb
Date: 2022/10/22
Time: 12:59 PM
To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>管理员主页</title>
</head>
<body>
<center>
<p1><font size="5" color="aqua">欢迎来到图书管理系统</font></p1>
<hr color="red" size="3">
<a href="book-register.jsp">读者注册</a>  
<a href="book-login.jsp">读者登陆</a>  
<a href="book-manager.jsp">管理员登陆</a>  
<hr color="red" size="3">
<a href="edit-book.jsp">图书编辑</a>  
<a href="edit-reader.jsp">读者编辑</a>  
</center>
</body>
</html>
reader-bow.jsp:
<%--
Created by IntelliJ IDEA.
User: ssssssbbbbbb
Date: 2022/10/22
Time: 11:00 AM
To change this template use File | Settings | File Templates.
--%>
<%@ page language="java" import="java.sql.*" pageEncoding="utf-8"%>
<%@ page import="Dao.BreaderDao" %>
<%@ page import="Dao.BookDao" %>
<%@page import="beans.b_reader"%>
<%@page import="beans.b_reader"%>
<%@page import="beans.book"%>
<%@ page import="java.util.List" %>
<html>
<head>
<title>借阅</title>
</head>
<body>
<%
String rid=request.getParameter("id");
if(rid!=null) {
BreaderDao userDao = new BreaderDao();
try{
b_reader reader=userDao.findUserById(rid);
if(reader!=null) {
int num=reader.getNum();
out.print(num);
if(num>=8) {
out.print("借书已达到上限!");
request.getRequestDispatcher("book-register-.jsp").forward(request,response);
}
else {
//out.print("111");
userDao.Addnum(rid);
}
}}catch (Exception e){
out.print("出错。。。");
}
}
%>
</body>
</html>
reader-main.jsp:
<%@ page language="java" import="java.sql.*" pageEncoding="utf-8"%>
<%@ page import="Dao.BreaderDao" %>
<%@ page import="Dao.BookDao" %>
<%@page import="beans.b_reader"%>
<%@page import="beans.b_reader"%>
<%@page import="beans.book"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%@ page import="java.util.List" %>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>Title</title>
</head>
<body>
<%
String id = request.getParameter("id");
String pwd=request.getParameter("pwd");
%>
<p1><font size="5" color="aqua">欢迎,<%=id%></font></p1>
<hr color="red" size="3">
<a href="book-main.jsp">返回主页</a>
<%! List<book> b =null; %>
<% BookDao bookDao = new BookDao();
b = bookDao.queryAll();
request.setAttribute("result",b);
%>
<h4>库中共有<font size="5" > <%=b.size()%></font>本书</h4>
<table border="2" width="650">
<tr align="center">
<td>id</td> <td>书名</td><td>作者</td> <td>价格</td><td>备注</td><td>操作</td>
</tr>
<c:forEach items="${result}" var="books" >
<tr align="center">
<td>${books.bid}</td>
<td>${books.bname}</td>
<td>${books.bauthor}</td>
<td>${books.price}</td>
<td>${books.binfo}</td>
<td><a href=reader-bow.jsp?id=${books.bid}">借阅</a>
</tr>
</c:forEach>
</table>
<%// request.getRequestDispatcher("Main.jsp").forward(request,response);
%>
</body>
</html>
update-book.jsp:
<%@ page language="java" import="java.sql.*" pageEncoding="utf-8"%>
<%@ page import="Dao.BreaderDao" %>
<%@ page import="Dao.BookDao" %>
<%@page import="beans.b_reader"%>
<%@page import="beans.b_reader"%>
<%@page import="beans.book"%>
<%@ page import="java.util.List" %>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>修改图书</title>
</head>
<body>
<center>
<p1><font size="5" color="aqua">欢迎来到图书管理系统</font></p1>
<hr color="red" size="3">
<a href="book-register.jsp">读者注册</a>  
<a href="book-login.jsp">读者登陆</a>  
<a href="book-manager.jsp">管理员登陆</a>  
<hr color="red" size="3">
<%
request.setCharacterEncoding("utf-8");
response.setContentType("text/html;charset=utf-8");
String name = request.getParameter("id");
int id=0;
if(name!=null && name!="")
{
id=Integer.parseInt(name);
BookDao book = new BookDao();
%>
<form action="update-book-.jsp" method="post">
id:<input type="text" name="id" value=<%=name%> ></br>
书名:<input type="text" name="name" ></br>
作者:<input type="text" name="author" ></br>
价格:<input type="text" name="price"></br>
备注:<input type="text" name="info"></br>
<input type="submit" value="修改">
</form>
<%
}
%>
</center>
</body>
</html>
update-book-.jsp:
<%@ page language="java" import="java.sql.*" pageEncoding="utf-8"%>
<%@ page import="Dao.BreaderDao" %>
<%@ page import="Dao.BookDao" %>
<%@page import="beans.b_reader"%>
<%@page import="beans.b_reader"%>
<%@page import="beans.book"%>
<%@ page import="java.util.List" %>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>Title</title>
</head>
<body>
<%
request.setCharacterEncoding("utf-8");
response.setContentType("text/html;charset=utf-8");
String id_ = request.getParameter("id");
String name=request.getParameter("name");
String author=request.getParameter("author");
String price_=request.getParameter("price");
String info=request.getParameter("info");
if(id_!=null && id_!="")
{
int id=Integer.parseInt(id_);
float price=Float.parseFloat(price_);
BookDao book = new BookDao();
book b=new book();
b.setBid(id);
b.setBname(name);
b.setBauthor(author);
b.setPrice(price);
b.setBinfo(info);
book.update(b);
}
request.getRequestDispatcher("edit-book.jsp").forward(request,response);
%>
</body>
</html>
BreaderDao.java:
package Dao;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import beans.b_reader;
import jdbc.JDBCUtil;
public class BreaderDao {
//向数据库中添加用户记录的方法add()
public void add(b_reader reader) throws Exception{
Connection conn = null;
PreparedStatement ps = null;
try {
conn = JDBCUtil.getConnection();
String sql = "insert into reader values (?,?,?)";
ps = conn.prepareStatement(sql);
ps.setString(1, reader.getRid());
ps.setString(2,reader.getRpass());
ps.setInt(3,reader.getNum());
ps.executeUpdate();
}finally {JDBCUtil.free(null,ps, conn);}
}
//修改数据库用户记录的方法update()
public void update(b_reader reader) throws Exception{
Connection conn = null;
PreparedStatement ps = null;
try {
conn = JDBCUtil.getConnection();
String sql = "update reader set rpass=?, num=? where rid=? ";
ps = conn.prepareStatement(sql);
ps.setString(1,reader.getRpass());
ps.setInt(2,reader.getNum());
ps.setString(3, reader.getRid());
ps.executeUpdate();
}finally {JDBCUtil.free(null,ps, conn);}
}
//删除数据库用户记录的方法delete()
public void Addnum(String rid) throws Exception{
Connection conn = null;
PreparedStatement ps = null;
try {
conn = JDBCUtil.getConnection();
String sql = "update reader set num=num+1 where rid=?";
ps = conn.prepareStatement(sql);
ps.setString(1,rid);
ps.executeUpdate();
}finally {JDBCUtil.free( null,ps, conn);}
}
//借书数量加一
public void delete(String rid) throws Exception{
Connection conn = null;
PreparedStatement ps = null;
try {
conn = JDBCUtil.getConnection();
String sql = "delete from reader where rid=?";
ps = conn.prepareStatement(sql);
ps.setString(1,rid);
ps.executeUpdate();
}finally {JDBCUtil.free( null,ps, conn);}
}
public boolean Match(String rid,String pwd) throws Exception{
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs;
try {
conn = JDBCUtil.getConnection();
String sql = "select * from reader where rid=? and rpass=?";
ps = conn.prepareStatement(sql);
ps.setString(1,rid);
ps.setString(2,pwd);
rs=ps.executeQuery();
if(rs.next()) return true;
else return false;
}finally {JDBCUtil.free( null,ps, conn);}
}
public boolean HasRid(String rid) throws Exception{
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs=null;
try {
conn = JDBCUtil.getConnection();
String sql = "select * from reader where rid=?";
ps = conn.prepareStatement(sql);
ps.setString(1,rid);
rs=ps.executeQuery();
if(rs.next()) return true;
else return false;
}finally {JDBCUtil.free( null,ps, conn);}
}
//根据id查询用户的方法findUserById()
public b_reader findUserById(String rid) throws Exception{
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
b_reader reader=null;
try {
conn = JDBCUtil.getConnection();
String sql = "select * from reader where rid=? ";
ps = conn.prepareStatement(sql);
ps.setString(1, rid);
rs=ps.executeQuery();
if(rs.next()){
reader=new b_reader();
reader.setRid(rs.getString(1));
reader.setRpass(rs.getString(2));
reader.setNum(rs.getInt(3));
}
}finally {JDBCUtil.free(rs, ps, conn);}
return reader;
}
//查询全部用户的方法queryAll()
public List<b_reader> queryAll() throws Exception{
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
List<b_reader> userList=new ArrayList<b_reader>();
try {
conn = JDBCUtil.getConnection();
String sql = "select * from reader ";
ps=conn.prepareStatement(sql,
ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
rs=ps.executeQuery();
while(rs.next()){
b_reader reader=new b_reader();
reader.setRid(rs.getString(1));
reader.setRpass(rs.getString(2));
reader.setNum(rs.getInt(3));
userList.add(reader);
}
}finally {JDBCUtil.free(rs, ps, conn);}
return userList;
}
}
BookDao.java:
package Dao;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import beans.book;
import jdbc.JDBCUtil;
public class BookDao {
//向数据库中添加用户记录的方法add()
public void add(book b) throws Exception{
Connection conn = null;
PreparedStatement ps = null;
try {
conn = JDBCUtil.getConnection();
String sql = "insert into books values (?,?,?,?,?)";
ps = conn.prepareStatement(sql);
ps.setInt(1, b.getBid());
ps.setString(2,b.getBname());
ps.setString(3,b.getBauthor());
ps.setFloat(4,b.getPrice());
ps.setString(5,b.getBinfo());
ps.executeUpdate();
}finally {JDBCUtil.free(null,ps, conn);}
}
//修改数据库用户记录的方法update()
public void update(book b) throws Exception{
Connection conn = null;
PreparedStatement ps = null;
try {
conn = JDBCUtil.getConnection();
String sql = "update books set bname=?, bauthor=?, price=?, Binfo=? where bid=? ";
ps = conn.prepareStatement(sql);
ps.setString(1,b.getBname());
ps.setString(2,b.getBauthor());
ps.setFloat(3,b.getPrice());
ps.setString(4,b.getBinfo());
ps.setInt(5, b.getBid());
ps.executeUpdate();
}finally {JDBCUtil.free(null,ps, conn);}
}
//删除数据库用户记录的方法delete()
public void delete(int id) throws Exception{
Connection conn = null;
PreparedStatement ps = null;
try {
conn = JDBCUtil.getConnection();
String sql = "delete from books where bid=?";
ps = conn.prepareStatement(sql);
ps.setInt(1,id);
ps.executeUpdate();
}finally {JDBCUtil.free( null,ps, conn);}
}
public List<book> findBookByName(String name) throws Exception{
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
List<book> bookList=new ArrayList<book>();
try {
conn = JDBCUtil.getConnection();
String sql = "select * from books where bname like ? ";
ps = conn.prepareStatement(sql);
ps.setString(1, "%" + name + "%");
rs=ps.executeQuery();
if(rs.next()){
book b=new book();
b.setBid(rs.getInt(1));
b.setBname(rs.getString(2));
b.setBauthor(rs.getString(3));
b.setPrice(rs.getFloat(4));
b.setBinfo(rs.getString(5));
bookList.add(b);
}
}finally {JDBCUtil.free(rs, ps, conn);}
return bookList;
}
//根据id查询用户的方法findUserById()
public book findUserById(int id) throws Exception{
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
book b=null;
try {
conn = JDBCUtil.getConnection();
String sql = "select * from books where bid=? ";
ps = conn.prepareStatement(sql);
ps.setInt(1, id);
rs=ps.executeQuery();
if(rs.next()){
b=new book();
b.setBid(rs.getInt(1));
b.setBname(rs.getString(2));
b.setBauthor(rs.getString(3));
b.setPrice(rs.getFloat(4));
b.setBinfo(rs.getString(5));
}
}finally {JDBCUtil.free(rs, ps, conn);}
return b;
}
//查询全部用户的方法queryAll()
public List<book> queryAll() throws Exception{
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
List<book> bookList=new ArrayList<book>();
try {
conn = JDBCUtil.getConnection();
String sql = "select * from books ";
ps=conn.prepareStatement(sql,
ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
rs=ps.executeQuery();
while(rs.next()){
book b=new book();
b.setBid(rs.getInt(1));
b.setBname(rs.getString(2));
b.setBauthor(rs.getString(3));
b.setPrice(rs.getFloat(4));
b.setBinfo(rs.getString(5));
bookList.add(b);
}
}finally {JDBCUtil.free(rs, ps, conn);}
return bookList;
}
}
book.java:
package beans;
public class book {
private String bname;
private int bid;
private String bauthor;
private float price;
private String binfo;
/*public book(int bid,String name,String author,Float price,String binfo)
{
this.bid=bid;
this.bname=name;
this.bauthor=author;
this.price=price;
this.binfo=binfo;
}*/
public void setBname(String bname) { this.bname=bname; }
public void setBid(int bid) { this.bid=bid; }
public void setBauthor(String bauthor) { this.bauthor=bauthor; }
public void setPrice(float price) { this.price=price; }
public void setBinfo(String info) {
this.binfo=info;
}
public int getBid(){return this.bid;}
public String getBname(){return this.bname;}
public String getBauthor(){return this.bauthor;}
public float getPrice(){return this.price;}
public String getBinfo(){return this.binfo;}
}
b_reader.java:
package beans;
public class b_reader {
private String rid;
private String rpass;
private int num;
public void setRid(String rid){
this.rid=rid;
}
public void setRpass(String rpass){
this.rpass=rpass;
}
public void setNum(int num){
this.num=num;
}
public void addNum(){this.num=this.num+1;}
public String getRid(){return this.rid;}
public String getRpass(){return this.rpass;}
public int getNum(){return this.num;}
}
jdbc.properties:
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/book
user=root
password=12345678
数据库结构如下:
运行book-main.jsp文件即可