重庆市建设工程信息网行业协会/seo排名公司

该项目来自书上，此文章仅作学习笔记与分享使用。
准备：MySql，Navicat，Tomcat，idea
使用Navicat创建连接
用户名：root
密码：自己电脑上的Mysql的登录密码
在创建连接时若出现以下错误，则可能是用户名或密码出错，修正成自己的Mysql的账户密码即可

新建数据库连接：

新建数据库

在新建的数据库中新建该表格：
表名：book
主键：id且设为自动增长

表中的数据：

数据库新建完成，以下是程序实现：
使用idea创建Web项目（动态网站工程）命名为bookstore
以下是项目的目录结构

Book.java：该类用于存储书本数据

package com.bean;public class Book {private int id;private String bookname;private String author;private String press;private String pubdate;private float price;private String isbn;public void setId(int id) {this.id = id;}public void setBookname(String bookname) {this.bookname = bookname;}public void setAuthor(String author) {this.author = author;}public void setPress(String press) {this.press = press;}public void setPubdate(String pubdate) {this.pubdate = pubdate;}public void setPrice(float price) {this.price = price;}public void setIsbn(String isbn) {this.isbn = isbn;}public int getId() {return id;}public String getBookname() {return bookname;}public String getAuthor() {return author;}public String getPress() {return press;}public String getPubdate() {return pubdate;}public float getPrice() {return price;}public String getIsbn() {return isbn;}
}

以上javabean类中所有的属性都必须设为private，所有的属性都必须舍友get与set方法，该类还需要设有无参构造函数（没有写构造方式，就默认带有一个无参构造函数）
pubdate表示出版日期，本应设为Date类型更为合理，但考虑到用户通过文本框输入出版日期时，JSP只能识别为文本类型，无法将其转为java.sql.Date类型，但用户按照yyyy-MM-dd格式输入出版日期，后台数据库可以自动转换为java.sql.Data类型与数据库字段pudbdate类型一致所以这里pubdate设为String类型。
DB.java：对数据库连接

package com.util;import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.Properties;public class DB {private Properties p;private String driver;private String url;private String username;private String password;private Connection conn;public Connection getConn(){return conn;}public DB(){try {p = new Properties();InputStream is = this.getClass().getResourceAsStream("/config.ini");p.load(is);driver = p.getProperty("driver");url = p.getProperty("url");username = p.getProperty("username");password = p.getProperty("password");Class.forName(driver);conn = DriverManager.getConnection(url,username,password);} catch (Exception e) {e.printStackTrace();}}
}

进行数据库连接需要导入JDBC的jar包，若无jdbc则到官网自行下载（jdbc下载步骤）

在DB.java类连接数据库中使用的是Properties类读取config.ini文件里的获取数据库驱动，数据库的url，连接的用户名，连接密码
config.ini中的配置信息：
driver = com.mysql.cj.jdbc.Driver
url = jdbc:mysql://localhost:3306/bookdb? useUnicode = true&characterEncoding = UTF-8
username = root
password = root
若config.ini文件无法被解析，则表示编译器没有安装ini插件（安装ini插件步骤）虽然即使不安装也并不影响使用

config.ini该文件放在src文件夹下
BookDAO.java：用于处理对图书的增删改查操作

package com.dao;import com.bean.Book;
import com.util.DB;import java.sql.*;
import java.util.ArrayList;public class BookDAO {private String sql = "";private PreparedStatement pstmt;private ResultSet rs;private ArrayList<Book>books = new ArrayList<Book>();private  Book book;private Connection conn = null;public BookDAO(){}private Connection getConn(){try {if((conn == null) || conn.isClosed()){DB db = new DB();conn = db.getConn();}} catch (SQLException e) {e.printStackTrace();}return  conn;}public ArrayList<Book>getAll(){sql = "select * from book";try {pstmt = getConn().prepareStatement(sql);rs = pstmt.executeQuery();} catch (SQLException e) {e.printStackTrace();}return getByRS(rs);}public ArrayList<Book>getByRS(ResultSet rs){try {if(rs == null || !rs.next()){return null;}books.clear();do {book = new Book();book.setId(rs.getInt("id"));book.setBookname(rs.getString("bookname"));book.setAuthor(rs.getString("author"));book.setPress(rs.getString("press"));book.setPubdate(rs.getString("pubdate"));book.setPrice(rs.getFloat("price"));book.setIsbn(rs.getString("isbn"));books.add(book);}while (rs.next());} catch (SQLException e) {e.printStackTrace();} finally {close();}return books;}public void close(){try {if(rs!=null) {rs.close();}if(pstmt != null){pstmt.close();}if(conn != null){conn.close();}} catch (Exception e) {e.printStackTrace();}finally {rs = null;pstmt = null;conn = null;}}//查询方法public ArrayList<Book>getByName(String bookname){getConn();if (bookname == null){bookname="";}try {CallableStatement cs = conn.prepareCall("{call getByNameProc(?)}");cs.setString(1,bookname);rs = cs.executeQuery();} catch (SQLException e) {e.printStackTrace();}return getByRS(rs);}//通过书本id获取图书资料public Book getById(int id){getConn();sql = "select * from book where id=?";try {pstmt = conn.prepareStatement(sql);pstmt.setInt(1,id);rs = pstmt.executeQuery();} catch (SQLException e) {e.printStackTrace();}return getByRS(rs).get(0);}//修改图书public boolean updateBook(Book book){sql = "update book set bookname=?,author=?,press=?,pubdate=?,price=? where id=?";int result = -1;try {pstmt = getConn().prepareStatement(sql);pstmt.setString(1,book.getBookname());pstmt.setString(2,book.getAuthor());pstmt.setString(3,book.getPress());pstmt.setString(4,book.getPubdate());pstmt.setFloat(5,book.getPrice());pstmt.setInt(6,book.getId());result = pstmt.executeUpdate();} catch (Exception e) {e.printStackTrace();}finally {close();}if (result>0){return true;}else {return false;}}//图书删除public boolean delete(int id){sql = "delete from book where id=?";int result = 0;try {pstmt = getConn().prepareStatement(sql);pstmt.setInt(1,id);result = pstmt.executeUpdate();} catch (Exception e) {e.printStackTrace();}finally {close();}if (result>0){return true;}else {return false;}}//增加图书public boolean add(Book book){sql = "insert into book(bookname,author,press,pubdate,price,isbn)values(?,?,?,?,?,?)";int result = 0;try {pstmt = getConn().prepareStatement(sql);pstmt.setString(1,book.getBookname());pstmt.setString(2,book.getAuthor());pstmt.setString(3,book.getPress());pstmt.setString(4,book.getPubdate());pstmt.setFloat(5,book.getPrice());pstmt.setString(6,book.getIsbn());result = pstmt.executeUpdate();} catch (SQLException e) {e.printStackTrace();}finally {close();}if(result>0){return true;}else {return false;}}
}

addbook.jsp：添加图书操作

<%--Created by IntelliJ IDEA.User: LcyDate: 2021/5/5Time: 11:06To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html><head><title>添加图书</title></head><body><h1>添加图书</h1><form action="handle.jsp?action=add" method="post">书名：<input type="text" name="bookname" required="required"><br>作者：<input type="text" name="author"><br>出版社：<input type="text" name="press"><br>出版时间：<input type="date" name="pubdate">格式如：2017-10-01 <br>价格：<input type="text" name="price" value="0"><br>ISBN号：<input type="text" name="isbn"><br><input type="submit" value="提交"></form></body>
</html>

edit.jsp：修改图书操作

<%--Created by IntelliJ IDEA.User: LcyDate: 2021/5/4Time: 23:43To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html><head><title>edit</title></head><body><h1>修改图书信息</h1><form action="saveupdate.jsp" method="post"><input type="hidden" name="id" value="${id}">书名：<input type="text" name="bookname" value="${book.bookname}" size="50"><br>作者：<input type="text" name="author" value="${book.author}"><br>出版社：<input type="text" name="press" value="${book.press}"><br>出版时间：<input type="text" name="pubdate" value="${book.pubdate}">价格：<input type="text" name="price" value="${book.price}"><br>ISBN：<input type="text" name="isbn" value="${book.isbn}"><br><input type="submit" value="提交"></form></body>
</html>

handle.jsp：对于用户的操作起到一个中转作用

<%--Created by IntelliJ IDEA.User: LcyDate: 2021/5/4Time: 22:54To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java"pageEncoding="UTF-8" import="com.dao.*,com.bean.*" %>
<%request.setCharacterEncoding("utf-8");%>
<jsp:useBean id="bookDAO" class="com.dao.BookDAO"></jsp:useBean>
<jsp:useBean id="book" class="com.bean.Book"></jsp:useBean>
<jsp:setProperty property="*" name="book"></jsp:setProperty>
<%//删除String msg="";boolean result = false;int id = 0;String action = request.getParameter("action");if(request.getParameter("id")!=null){id = Integer.parseInt(request.getParameter("id"));}if("delete".equals(action)) {result = bookDAO.delete(id);if (result == true) {msg = "图书删除成功！";} else {msg = "图书删除失败！";}session.setAttribute("msg", msg);response.sendRedirect("result.jsp");return;}else if("add".equals(action)) {//添加图书System.out.println(book);result = bookDAO.add(book);if(result == true){msg = "图书添加成功！";}else {msg = "图书添加失败！";}session.setAttribute("msg",msg);response.sendRedirect("result.jsp");return;}else {//修改if (request.getParameter("id")!=null){id = Integer.parseInt(request.getParameter("id"));book = bookDAO.getById(id);request.setAttribute("book",book);request.setAttribute("id",id);request.getRequestDispatcher("edit.jsp").forward(request,response);}}
%>
<html><head><title>handle</title></head><body></body>
</html>

main.jsp：主页

<%@ page import="java.util.ArrayList" %>
<%@ page import="com.bean.Book" %><%--Created by IntelliJ IDEA.User: LcyDate: 2021/5/4Time: 13:58To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html><head><title>main</title><%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %><script type="text/javascript">function deleteBook(id) {if(confirm("你确定要删除该记录吗？")==true){window.location = "handle.jsp?action=delete&id="+id;}}</script></head><body><jsp:useBean id="bookDAO" scope="session" class="com.dao.BookDAO"></jsp:useBean><%request.setCharacterEncoding("utf-8");String keyword = request.getParameter("keyword");request.setAttribute("keyword",keyword);ArrayList<Book>books = bookDAO.getByName(keyword);request.setAttribute("books",books);%><form action="main.jsp" method="get"><input type="text" name="keyword" size="80" value="${requestScope.keyword}"><input type="submit" value="搜索"></form><br><table border="1"><tr><th>ID</th><th>书名</th><th>作者</th><th>出版社</th><th>出版时间</th><th>价格（元）</th><th>ISBN号</th><th colspan="2">操作</th></tr><c:forEach items="${books}" var="book"><tr><td>${book.id}</td><td>${book.bookname}</td><td>${book.author}</td><td>${book.press}</td><td>${book.pubdate}</td><td>${book.price}</td><td>${book.isbn}</td><td><a href="handle.jsp?id=${book.id}">修改${book.id}</a></td><td><a href="javascript:void(0);" onclick="deleteBook(${book.id})">删除</a></td></tr></c:forEach></table></body>
</html>

result.jsp：返回用户操作的结果

<%--Created by IntelliJ IDEA.User: LcyDate: 2021/5/4Time: 23:32To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html><head><meta http-equiv="Content-Type" content="text/html; charset="UTF-8><meta http-equiv="refresh" content="2;main.jsp"><title>Insert title here</title></head><body><h1>${sessionScope.msg}</h1></body>
</html>

saveupdate.jsp：保存用户对数据的操作

package com.dao;import com.bean.Book;
import com.util.DB;import java.sql.*;
import java.util.ArrayList;public class BookDAO {private String sql = "";private PreparedStatement pstmt;private ResultSet rs;private ArrayList<Book>books = new ArrayList<Book>();private  Book book;private Connection conn = null;public BookDAO(){}private Connection getConn(){try {if((conn == null) || conn.isClosed()){DB db = new DB();conn = db.getConn();}} catch (SQLException e) {e.printStackTrace();}return  conn;}public ArrayList<Book>getAll(){sql = "select * from book";try {pstmt = getConn().prepareStatement(sql);rs = pstmt.executeQuery();} catch (SQLException e) {e.printStackTrace();}return getByRS(rs);}public ArrayList<Book>getByRS(ResultSet rs){try {if(rs == null || !rs.next()){return null;}books.clear();do {book = new Book();book.setId(rs.getInt("id"));book.setBookname(rs.getString("bookname"));book.setAuthor(rs.getString("author"));book.setPress(rs.getString("press"));book.setPubdate(rs.getString("pubdate"));book.setPrice(rs.getFloat("price"));book.setIsbn(rs.getString("isbn"));books.add(book);}while (rs.next());} catch (SQLException e) {e.printStackTrace();} finally {close();}return books;}public void close(){try {if(rs!=null) {rs.close();}if(pstmt != null){pstmt.close();}if(conn != null){conn.close();}} catch (Exception e) {e.printStackTrace();}finally {rs = null;pstmt = null;conn = null;}}//查询方法public ArrayList<Book>getByName(String bookname){getConn();if (bookname == null){bookname="";}try {CallableStatement cs = conn.prepareCall("{call getByNameProc(?)}");cs.setString(1,bookname);rs = cs.executeQuery();} catch (SQLException e) {e.printStackTrace();}return getByRS(rs);}//通过书本id获取图书资料public Book getById(int id){getConn();sql = "select * from book where id=?";try {pstmt = conn.prepareStatement(sql);pstmt.setInt(1,id);rs = pstmt.executeQuery();} catch (SQLException e) {e.printStackTrace();}return getByRS(rs).get(0);}//修改图书public boolean updateBook(Book book){sql = "update book set bookname=?,author=?,press=?,pubdate=?,price=? where id=?";int result = -1;try {pstmt = getConn().prepareStatement(sql);pstmt.setString(1,book.getBookname());pstmt.setString(2,book.getAuthor());pstmt.setString(3,book.getPress());pstmt.setString(4,book.getPubdate());pstmt.setFloat(5,book.getPrice());pstmt.setInt(6,book.getId());result = pstmt.executeUpdate();} catch (Exception e) {e.printStackTrace();}finally {close();}if (result>0){return true;}else {return false;}}//图书删除public boolean delete(int id){sql = "delete from book where id=?";int result = 0;try {pstmt = getConn().prepareStatement(sql);pstmt.setInt(1,id);result = pstmt.executeUpdate();} catch (Exception e) {e.printStackTrace();}finally {close();}if (result>0){return true;}else {return false;}}//增加图书public boolean add(Book book){sql = "insert into book(bookname,author,press,pubdate,price,isbn)values(?,?,?,?,?,?)";int result = 0;try {pstmt = getConn().prepareStatement(sql);pstmt.setString(1,book.getBookname());pstmt.setString(2,book.getAuthor());pstmt.setString(3,book.getPress());pstmt.setString(4,book.getPubdate());pstmt.setFloat(5,book.getPrice());pstmt.setString(6,book.getIsbn());result = pstmt.executeUpdate();} catch (SQLException e) {e.printStackTrace();}finally {close();}if(result>0){return true;}else {return false;}}
}