书籍管理系统
書(shū)籍管理系統(tǒng)
項(xiàng)目描述
實(shí)現(xiàn)對(duì)數(shù)據(jù)庫(kù)中的書(shū)籍進(jìn)行增、刪、改、查操作。其中涉及到MVC,jsp,servlet等。項(xiàng)目代碼
項(xiàng)目中是在web下開(kāi)發(fā),所使用的IDE為eclipse,Tomcat和jdk版本均為8.0
1.由于要連接數(shù)據(jù)庫(kù),所以新建工具類包即基本數(shù)據(jù)庫(kù)連接類,切記導(dǎo)入數(shù)據(jù)庫(kù)驅(qū)動(dòng)連接包并放置在工程目錄WebContent/WEB-INF/lib下。溫馨提示:類編寫(xiě)完成后可以進(jìn)行本地?cái)?shù)據(jù)庫(kù)連接測(cè)試,確保數(shù)據(jù)庫(kù)連接成功。package cn.mxf.utils;import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException;//數(shù)據(jù)庫(kù)連接類 public class BaseDao {//聲明Connection連接類private Connection conn;//創(chuàng)建PreparedStatement對(duì)象,用來(lái)執(zhí)行SQL語(yǔ)句private PreparedStatement ps;//用來(lái)存放數(shù)據(jù)的結(jié)果集private ResultSet rs;// 連接數(shù)據(jù)庫(kù)public void getConnection() {// 加載驅(qū)動(dòng)程序try {Class.forName("com.mysql.jdbc.Driver");String url = "jdbc:mysql://localhost:3306/booksys?characterEncoding=utf-8";//通過(guò)getConnection方法連接數(shù)據(jù)庫(kù)conn = DriverManager.getConnection(url, "root", "");} catch (ClassNotFoundException e) {e.printStackTrace();} catch (SQLException e) {e.printStackTrace();}}// 關(guān)閉數(shù)據(jù)庫(kù) 從后往前依次關(guān)閉public void close() {try {if (ps != null) {ps.close();}} catch (SQLException e) {e.printStackTrace();}try {if (rs != null) {rs.close();}} catch (SQLException e) {e.printStackTrace();}try {if (conn != null) {conn.close();}} catch (SQLException e) {e.printStackTrace();}}// 實(shí)現(xiàn)對(duì)數(shù)據(jù)的更新--增加,刪除,修改操作;其中Object...為可變參數(shù)public int executeUpdate(String sql, Object... objects) {try {this.getConnection();ps = conn.prepareStatement(sql);if (objects != null) {for (int i = 0; i < objects.length; i++) {ps.setObject(i + 1, objects[i]);}return ps.executeUpdate();}} catch (SQLException e) {e.printStackTrace();} finally {this.close();}return -1;}// 實(shí)現(xiàn)對(duì)數(shù)據(jù)的查詢操作public ResultSet executeQuery(String sql, Object... objects) {try {this.getConnection();ps = conn.prepareStatement(sql);if (objects != null) {for (int i = 0; i < objects.length; i++) {ps.setObject(i + 1, objects[i]);}return rs = ps.executeQuery();}} catch (SQLException e) {e.printStackTrace();}return null;} } 2.由于我們需要做的是對(duì)書(shū)籍的增刪改查等操作,所以新建書(shū)籍實(shí)體類包里面包含數(shù)據(jù)實(shí)體類和書(shū)籍分類類;同時(shí)在mysql數(shù)據(jù)庫(kù)中新建book(書(shū)籍信息)和category(書(shū)籍分類)表。 注意:其中book表的id設(shè)置為自動(dòng)遞增
書(shū)籍類
package cn.mxf.entity;import java.util.Date;//書(shū)籍類 public class Book {// 對(duì)所有的屬性生成get和set方法private int id;// 書(shū)籍編號(hào)private String name;// 書(shū)籍名稱private double price;// 價(jià)格private String author;// 作者private Date pubDate;// 出版日期private int categoryId;// 書(shū)籍分類// 無(wú)參構(gòu)造方法public Book() {}// 不帶id的有參構(gòu)造方法public Book(String name, double price, String author, Date pubDate, int categoryId) {super();this.name = name;this.price = price;this.author = author;this.pubDate = pubDate;this.categoryId = categoryId;}// 帶id的有參構(gòu)造方法public Book(int id, String name, double price, String author, Date pubDate, int categoryId) {super();this.id = id;this.name = name;this.price = price;this.author = author;this.pubDate = pubDate;this.categoryId = categoryId;}public int getId() {return id;}public void setId(int id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public double getPrice() {return price;}public void setPrice(double price) {this.price = price;}public String getAuthor() {return author;}public void setAuthor(String author) {this.author = author;}public Date getPubDate() {return pubDate;}public void setPubDate(Date pubDate) {this.pubDate = pubDate;}public int getCategoryId() {return categoryId;}public void setCategoryId(int categoryId) {this.categoryId = categoryId;}}書(shū)籍分類類
package cn.mxf.entity;public class Category {// 書(shū)籍分類編號(hào)private int id;// 書(shū)籍對(duì)應(yīng)分類名private String name;// 生成帶參構(gòu)造方法public Category(int id, String name) {super();this.id = id;this.name = name;}public Category(String name) {super();this.name = name;}// 生成無(wú)參構(gòu)造方法public Category() {}// 并對(duì)屬性進(jìn)行設(shè)置set和get方法public int getId() {return id;}public void setId(int id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}}3.接著實(shí)現(xiàn)基于BaseDao的書(shū)籍?dāng)?shù)據(jù)庫(kù)連接類和書(shū)籍分類數(shù)據(jù)庫(kù)連接類。
書(shū)籍?dāng)?shù)據(jù)庫(kù)連接類
package cn.mxf.dao;import java.sql.ResultSet; import java.sql.SQLException; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.List;import cn.mxf.entity.Book; import cn.mxf.utils.BaseDao; //本類為繼承于BaseDao public class BookDao extends BaseDao {// 實(shí)現(xiàn)查詢所有書(shū)籍的功能public List<Book> getAll() {List<Book> list = new ArrayList<Book>();// 查詢的SQL語(yǔ)句String sql = "select * from book";// 調(diào)用查詢數(shù)據(jù)的方法對(duì)書(shū)籍進(jìn)行查詢操作ResultSet rs = this.executeQuery(sql);try {// 將查詢的結(jié)果存放于List<Book>中while (rs.next()) {list.add(new Book(rs.getInt(1), rs.getString(2), rs.getDouble(3), rs.getString(4), rs.getDate(5),rs.getInt(6)));}} catch (SQLException e) {e.printStackTrace();} finally {// 最后別忘記關(guān)閉this.close();}return list;}// 實(shí)現(xiàn)增加書(shū)籍的功能public int addBook(Book book) {String sql = "insert into book(name,price,author,pubDate,categoryId) values(?,?,?,?,?)";return this.executeUpdate(sql, book.getName(), book.getPrice(), book.getAuthor(),new SimpleDateFormat("yyyy-MM-dd").format(book.getPubDate()), book.getCategoryId());}// 實(shí)現(xiàn)通過(guò)ID找到對(duì)應(yīng)書(shū)籍的功能--在此之后才能進(jìn)行修改操作public Book selectBookById(int id) {// 通過(guò)id值查詢對(duì)應(yīng)書(shū)籍的SQL語(yǔ)句String sql = "select * from book where id=" + id;// 執(zhí)行查詢操作ResultSet rs = this.executeQuery(sql);try {if (rs.next()) {return new Book(rs.getInt(1), rs.getString(2), rs.getDouble(3), rs.getString(4), rs.getDate(5),rs.getInt(6));}} catch (SQLException e) {e.printStackTrace();} finally {// 最后別忘記關(guān)閉this.close();}return null;}// 實(shí)現(xiàn)修改書(shū)籍的功能--在此之前需要找到要修改的書(shū)籍信息public int modifyBook(Book book) {// 更新書(shū)籍信息的SQL語(yǔ)句String sql = "update book set name=?,price=?,author=?,pubDate=?,categoryId=? where id=?";return this.executeUpdate(sql, book.getName(), book.getPrice(), book.getAuthor(),new SimpleDateFormat("yyyy-MM-dd").format(book.getPubDate()), book.getCategoryId(), book.getId());}// 實(shí)現(xiàn)刪除書(shū)籍的功能--直接通過(guò)id找到對(duì)應(yīng)書(shū)籍然后刪除public int deleteBook(int id) {// 刪除書(shū)籍的SQL語(yǔ)句String sql = "delete from book where id=?";return this.executeUpdate(sql, id);} }書(shū)籍分類數(shù)據(jù)庫(kù)連接類
package cn.mxf.dao;import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List;import cn.mxf.entity.Category; import cn.mxf.utils.BaseDao; //本類為繼承于BaseDao public class CategoryDao extends BaseDao {// 實(shí)現(xiàn)查詢所有書(shū)籍分類的功能public List<Category> getAll() {List<Category> list = new ArrayList<Category>();// 查詢所有分類的SQL語(yǔ)句String sql = "select * from category";// 根據(jù)SQL語(yǔ)句執(zhí)行查詢操作ResultSet rs = this.executeQuery(sql);try {while (rs.next()) {// 將查詢的結(jié)果存放于List<Category>中list.add(new Category(rs.getInt(1), rs.getString(2)));}} catch (SQLException e) {e.printStackTrace();} finally {// 最后別忘記關(guān)閉連接this.close();}return list.size() > 0 ? list : null;}} 4.準(zhǔn)備工作完成后,現(xiàn)在才是真正的開(kāi)始:新建BookServlet類(所有的操作在這里完成),在其中完成對(duì)書(shū)籍的增刪改查操作。package cn.mxf.servlet;import java.io.IOException; import java.text.ParseException; import java.text.SimpleDateFormat; import java.util.Date; import java.util.List;import javax.servlet.ServletException; import javax.servlet.annotation.WebServlet; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse;import cn.mxf.dao.BookDao; import cn.mxf.dao.CategoryDao; import cn.mxf.entity.Book; import cn.mxf.entity.Category;// 通過(guò)注解的方式配置Servlet @WebServlet("/book") public class BookServlet extends HttpServlet {private static final long serialVersionUID = 1L;// 獲取BookDao對(duì)象private BookDao bookDao = new BookDao();// 獲取CategoryDao對(duì)象private CategoryDao categoryDao = new CategoryDao();public BookServlet() {super();}protected void doGet(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {// 解決亂碼request.setCharacterEncoding("utf-8");response.setContentType("text/html;charset=utf-8");/*** 獲取操作的參數(shù),確定好下一步該做什么 * op = list :查詢所有的書(shū)籍信息 * op = toAdd :跳轉(zhuǎn)到添加書(shū)籍的頁(yè)面 * op = add :添加書(shū)籍 * op = delete :刪除書(shū)籍 * op = getById :根據(jù)id值找到需要更改的書(shū)籍信息 * op = modify :修改書(shū)籍信息*/// 根據(jù)傳入的op判斷執(zhí)行那一個(gè)操作String op = request.getParameter("op");if (op == null || "list".equals(op)) {// 封裝為方法list(request, response);} else if ("toadd".equals(op)) {toadd(request, response);} else if ("add".equals(op)) {add(request, response);} else if ("getById".equals(op)) {getById(request, response);} else if ("modify".equals(op)) {modify(request, response);} else if ("delete".equals(op)) {delete(request, response);}} private void list(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {// 獲取所有書(shū)籍List<Book> list = bookDao.getAll();// 獲取所有書(shū)籍分類List<Category> clist = categoryDao.getAll();// 用來(lái)在同一個(gè)request周期中保存list變量使用request.setAttribute("list", list);// 用來(lái)在同一個(gè)request周期中保存clist變量使用request.setAttribute("clist", clist);// response.sendRedirect()方法是通過(guò)瀏覽器對(duì)象重定向的,在兩個(gè)不同的頁(yè)面間傳值會(huì)生成兩個(gè)不同的request對(duì)象。// 由于是在不同頁(yè)面間傳值,所以有使用RequestDispatcher接口的forward()方法。request.getRequestDispatcher("list.jsp").forward(request, response);}protected void doPost(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {doGet(request, response);}} 5.在WebContent目錄下新建list.jsp用來(lái)獲取書(shū)籍列表并進(jìn)行顯示。 <!-- 顯示數(shù)據(jù)庫(kù)中所有書(shū)籍的JSP頁(yè)面 ,其中涉及到了JSTL,EL表達(dá)式;所以需要自己導(dǎo)入jstl.jar和standard.jar包到WebContent/WEB-INF/lib下; --> <%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <!-- eclipse下使用JSTL需要導(dǎo)入java.sun.com/jsp/jstl/core,才能使用JSTL表達(dá)式 --> <%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> <title>書(shū)籍信息頁(yè)面</title> </head> <body><h1>書(shū)籍信息列表</h1><hr><table width="80%" align="center"><tr><td>編號(hào)</td><td>名稱</td><td>分類</td><td>價(jià)格</td><td>作者</td><td>出版日期</td><td>操作</td></tr><!-- 通過(guò)EL表達(dá)式獲取傳遞過(guò)來(lái)的list,并且通過(guò)JSTL中的循環(huán)獲取對(duì)應(yīng)的屬性值 --><c:forEach items="${list }" var="bean" varStatus="status"><!-- 1.隔行變色2.分別取得對(duì)應(yīng)的書(shū)籍屬性值并且顯示出來(lái)3.當(dāng)書(shū)籍中的分類編號(hào)和分類表中的編號(hào)相同時(shí),顯示分類表中的分類名--><tr <c:if test="${status.index%2==0 }">style="background:#0f0"</c:if>><td>${bean.id }</td><td>${bean.name }</td><td><c:forEach items="${clist }" var="category"><c:if test="${category.id == bean.categoryId }">${category.name }</c:if></c:forEach></td><td>${bean.price }</td><td>${bean.author }</td><td>${bean.pubDate }</td><!-- 1.這里以瀏覽器的URL方式直接將書(shū)籍的對(duì)應(yīng)id傳遞過(guò)去,可以對(duì)書(shū)籍進(jìn)行查詢操作和刪除操作--><td><a href="book?op=getById&id=${bean.id }">修改</a> <a href="book?op=delete&id=${bean.id }">刪除</a></td></tr></c:forEach></table> </body> </html>
到這里的話你就可以執(zhí)行你的代碼來(lái)查詢所有書(shū)籍了
6.然后就是給數(shù)據(jù)庫(kù)中進(jìn)行添加書(shū)籍的操作了。
private void add(HttpServletRequest request, HttpServletResponse response) throws IOException {String bookName = request.getParameter("name");// 書(shū)名double price = Double.parseDouble(request.getParameter("price"));// 價(jià)格String author = request.getParameter("author");// 作者SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");Date pubDate = null;// 出版日期try {pubDate = sdf.parse(request.getParameter("pubDate"));} catch (ParseException e) {e.printStackTrace();}int categoryId = Integer.parseInt(request.getParameter("categoryId"));// 書(shū)籍分類// 根據(jù)獲取到的書(shū)籍屬性新創(chuàng)建一個(gè)書(shū)籍對(duì)象并調(diào)用BookDao中的addBook方法進(jìn)行書(shū)籍的添加Book b = new Book(bookName, price, author, pubDate, categoryId);if (bookDao.addBook(b) > 0) {response.sendRedirect("book?op=list");} else {response.getWriter().print("添加失敗!!");}}private void toadd(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {// 獲取所有的書(shū)籍分類名稱List<Category> clist = categoryDao.getAll();request.setAttribute("clist", clist);request.getRequestDispatcher("add.jsp").forward(request, response);}7.在WebContent目錄下新建add.jsp用來(lái)獲取書(shū)籍列表并進(jìn)行顯示。
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> <title>添加書(shū)籍</title> </head> <body><h1>添加書(shū)籍</h1><hr><!-- 這里需要注意:提交的時(shí)候需要交給當(dāng)op=add時(shí)BookServlet中的add方法執(zhí)行 --><form action="book?op=add" method="post"><table width="60%" align="center"><tr><td>書(shū)名:</td><td><input type="text" name="name"></td></tr><tr><td>分類:</td><!-- 添加書(shū)籍的時(shí)候需要將書(shū)籍分類通過(guò)下拉框的形式顯示出來(lái) --><td><select name="categoryId"><c:forEach items="${clist }" var="bean"><option value="${bean.id }">${bean.name }</option></c:forEach></select></td></tr><tr><td>價(jià)格:</td><td><input type="text" name="price"></td></tr><tr><td>作者:</td><td><input type="text" name="author"></td></tr><tr><td>出版日期:</td><td><input type="text" name="pubDate"></td></tr><tr><td colspan="2" align="center"><input type="submit" value="提交"></td></tr></table></form> </body> </html>到這里的話你就可以執(zhí)行你的代碼來(lái)查詢所有書(shū)籍和添加書(shū)籍了
8.接著就是修改獲取到的書(shū)籍信息的操作了。private void modify(HttpServletRequest request, HttpServletResponse response) throws IOException, ServletException {// 獲取idint id = 0;if (request.getParameter("id") != null) {id = Integer.parseInt(request.getParameter("id"));}String name = request.getParameter("name");// 書(shū)名double price = Double.parseDouble(request.getParameter("price"));// 價(jià)格String author = request.getParameter("author");// 作者Date pubDate = null;// 出版日期try {pubDate = new SimpleDateFormat("yyyy-MM-dd").parse(request.getParameter("pubDate"));} catch (ParseException e) {e.printStackTrace();}int categoryId = Integer.parseInt(request.getParameter("categoryId"));// 書(shū)籍分類// 根據(jù)獲取到的書(shū)籍屬性新創(chuàng)建一個(gè)書(shū)籍對(duì)象并調(diào)用BookDao中的modifyBook方法進(jìn)行書(shū)籍的更新Book b = new Book(id, name, price, author, pubDate, categoryId);if (bookDao.modifyBook(b) > 0) {response.sendRedirect("book?op=list");} else {response.getWriter().print("修改失敗!!");}}private void getById(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {// 獲取idint id = 0;if (request.getParameter("id") != null) {id = Integer.parseInt(request.getParameter("id"));}// 根據(jù)id查詢對(duì)應(yīng)記錄Book book = bookDao.selectBookById(id);List<Category> clist = categoryDao.getAll();request.setAttribute("clist", clist);request.setAttribute("book", book);request.getRequestDispatcher("modify.jsp").forward(request, response);}
9.在WebContent目錄下新建modify.jsp用來(lái)更新書(shū)籍并進(jìn)行顯示。
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> <title>修改書(shū)籍</title> </head> <body><h1>修改書(shū)籍</h1><hr><form action="book?op=modify" method="post"><table width="60%" align="center"><tr><!-- 這里需要將id傳遞過(guò)去 --><td>書(shū)名: <input type="hidden" name="id" value="${book.id }" /></td><td><input type="text" name="name" value="${book.name}" /></td></tr><tr><td>分類:</td><!-- 修改書(shū)籍的時(shí)候需要將書(shū)籍分類通過(guò)下拉框的形式顯示出來(lái) --><td><select name="categoryId"><c:forEach items="${clist }" var="bean"><option value="${bean.id }"<c:if test="${bean.id == book.categoryId }">selected</c:if>>${bean.name }</option></c:forEach></select></td></tr><tr><td>價(jià)格:</td><td><input type="text" name="price" value="${book.price}" /></td></tr><tr><td>作者:</td><td><input type="text" name="author" value="${book.author}" /></td></tr><tr><td>出版日期:</td><td><input type="text" name="pubDate" value="${book.pubDate}" /></td></tr><tr><td colspan="2" align="center"><input type="submit" value="提交"></td></tr></table></form> </body> </html>到這里的話你就可以執(zhí)行你的代碼來(lái)查詢所有書(shū)籍、添加書(shū)籍和修改書(shū)籍了
10.剩下的就只有刪除了,其實(shí)刪除是這里面最簡(jiǎn)單的了,只需要獲取到id值之后就可以進(jìn)行刪除操作。private void delete(HttpServletRequest request, HttpServletResponse response) throws IOException {// 獲取idint id = 0;if (request.getParameter("id") != null) {id = Integer.parseInt(request.getParameter("id"));}// 根據(jù)獲取到的書(shū)籍id調(diào)用BookDao中的deleteBook(id)方法進(jìn)行書(shū)籍的刪除if (bookDao.deleteBook(id) > 0) {response.sendRedirect("book?op=list");}}
總結(jié):
- 通過(guò)這幾天的學(xué)習(xí)也對(duì)之前的JDBC進(jìn)行了復(fù)習(xí)與回顧;
- 新使用的MVC(Model View Control)模式使得界面與邏輯處理分離開(kāi)來(lái),很大程度上使得我們的修改變得簡(jiǎn)單;
- 通過(guò)EL、JSTL表達(dá)式的方式大大簡(jiǎn)化了編程代碼的冗余。
- 最后附上整個(gè)項(xiàng)目的源代碼
總結(jié)
- 上一篇: Unity 网格合并MeshBaker(
- 下一篇: (专升本)PowerPoint(设置幻灯