大项目之网上书城（八）——数据库大改&添加图书

喜欢ヅ旅行 2021-12-05 06:26 280阅读 0赞

*  大项目之网上书城（八）——数据库大改&添加图书
    
     *  主要改动
     *  1.数据库新增表
        
         *  代码
     *  2.数据库新增触发器
     *  3.其他对BookService和BookDao的修改
        
         *  代码
     *  4.addBook.jsp
        
         *  代码
         *  效果图
     *  5.AddNewBookServlet
        
         *  代码

# 大项目之网上书城（八）——数据库大改&添加图书 #

## 主要改动 ##

> 今天也是各种各种各种出错的一天啊，经历了各种方法的重写，各种触发器的重写。
> 
> 那么book表一分为n，多个子表更新数据的时候会联动book表更新数据。然后顺势写了个增加图书的方法。内容不多，错误不少、

## 1.数据库新增表 ##

### 代码 ###

> 以clothing为例，为各个类都新增了一个表。

DROP TABLE IF EXISTS `clothing`; CREATE TABLE `clothing` ( `book_name` varchar(40) NOT NULL, `price` double NOT NULL, `describtion` varchar(200) DEFAULT NULL, `clazz` varchar(40) NOT NULL, `second_id` int(11) NOT NULL AUTO_INCREMENT, `book_img` blob, `click_num` int(11) NOT NULL, `buy_num` int(9) NOT NULL, `re_du` int(12) DEFAULT NULL, `count` int(6) NOT NULL, `is_new` int(1) NOT NULL, `insert_date` date NOT NULL, `book_id` int(11) DEFAULT NULL, PRIMARY KEY (`second_id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

## 2.数据库新增触发器 ##

> 还是以clothing表为例，两个方法，一个是当子表插入数据时，book表插入一条同样的数据，2是子表更新时，book也做出相应更新。

DROP TRIGGER IF EXISTS `c_insert`; DELIMITER ;; CREATE TRIGGER `c_insert` AFTER INSERT ON `clothing` FOR EACH ROW begin insert into book(book_name,price,describtion,clazz,second_id,click_num,buy_num,count,is_new,insert_date) values(NEW.book_name,NEW.price,NEW.describtion,NEW.clazz,NEW.second_id,0,0,NEW.count,1,NEW.insert_date); end ;; DELIMITER ; DROP TRIGGER IF EXISTS `c_update`; DELIMITER ;; CREATE TRIGGER `c_update` AFTER UPDATE ON `clothing` FOR EACH ROW begin update book set book.re_du = NEW.click_num + NEW.buy_num * 100,book.click_num = NEW.click_num,book.buy_num = NEW.buy_num where clazz = new.clazz and second_id = new.second_id; end ;; DELIMITER ;

## 3.其他对BookService和BookDao的修改 ##

### 代码 ###

> 因为改得代码太多，太乱了，不好发，于是重新都发一下实现类好了。

#### daoimpl ####

package cn.edu.bdu.mc.daos.impls; import java.io.File; import java.io.FileInputStream; import java.io.IOException; import java.io.InputStream; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.Date; import java.util.List; import javax.sql.DataSource; import org.apache.commons.dbutils.QueryRunner; import org.apache.commons.dbutils.handlers.BeanHandler; import org.apache.commons.dbutils.handlers.BeanListHandler; import cn.edu.bdu.mc.beans.Book; import cn.edu.bdu.mc.daos.BookDao; import cn.edu.bdu.mc.utils.JDBCUtil; public class BookDaoImpl implements BookDao { private DataSource dataSource = JDBCUtil.getDataSource(); private QueryRunner queryRunner = new QueryRunner(dataSource); @Override public Book findNewBookByPaiMing(int shu) throws SQLException { // TODO Auto-generated method stub String sql = "select * from (select * from book where is_new = 1 order by re_du desc)as book1 limit "+(shu-1)+",1"; return queryRunner.query(sql, new BeanHandler<Book>(Book.class)); } @Override public List<Book> findBookReMen(int page) throws SQLException { String sql = "select * from (select * from book order by re_du desc)as book1 limit "+(page-1)*2+",2"; return queryRunner.query(sql, new BeanListHandler<Book>(Book.class)); } @Override public void insert(Book book) throws SQLException { // TODO Auto-generated method stub String sql = "insert into "+book.getClazz()+"(book_name,price,describtion,clazz,click_num,buy_num,count,is_new,insert_date) values(?,?,?,?,0,0,?,1,?)"; queryRunner.update(sql,book.getBook_name(),book.getPrice(),book.getDescribtion(),book.getClazz(),book.getCount(),new Date()); } @Override public Book findBookById(int book_id) throws SQLException{ String sql = "select * from book where book_id = ?"; return queryRunner.query(sql, new BeanHandler<Book>(Book.class),book_id); } @Override public List<Book>findAllBook() throws SQLException { String sql = "select * from book"; return queryRunner.query(sql, new BeanListHandler<Book>(Book.class)); } @Override public void deleteById(int book_id) throws SQLException { String sql = "delete from book where book_id = ?"; queryRunner.update(sql,book_id); } @Override public void update(Book book) throws SQLException { String sql = "update book set book_name = ?, price = ?, describtion = ?, clazz = ?, second_id = ?, click_num = ?, buy_num = ?, count = ?, is_new = ? where book_id = ?"; queryRunner.update(sql,book.getBook_name(),book.getPrice(),book.getDescribtion(),book.getClazz(),book.getSecond_id(),book.getClick_num(),book.getBuy_num(),book.getCount(),book.getIs_new(),book.getBook_id()); } @Override public void addImgByName(String book_name, String path) throws SQLException, IOException { // TODO Auto-generated method stub Connection conn = null; PreparedStatement ps = null; FileInputStream in = null; in = new FileInputStream(new File(path)); conn = JDBCUtil.getConn(); String sql = "update book set book_img = ? where book_name = ?"; ps = conn.prepareStatement(sql); ps.setBinaryStream(1, in, in.available()); ps.setString(2, book_name); int count = ps.executeUpdate(); if (count > 0) { System.out.println("插入成功！"); } else { System.out.println("插入失败！"); } JDBCUtil.release(conn, ps); } @Override public InputStream getImgById(int book_id) throws SQLException { // TODO Auto-generated method stub Connection conn = null; PreparedStatement ps = null; ResultSet rs = null; InputStream in = null; try { conn = JDBCUtil.getConn(); String sql = "select book_img from book where book_id = ?"; ps = conn.prepareStatement(sql); ps.setInt(1, book_id); rs = ps.executeQuery(); while (rs.next()) { in = rs.getBinaryStream("book_img"); } } catch (Exception e) { e.printStackTrace(); } JDBCUtil.release(conn, ps, rs); return in; } @Override public Book findBookByClazzAndEr_id(String clazz, int er_id) throws SQLException { // TODO Auto-generated method stub String sql = "select * from "+clazz+" where second_id = ?"; Book book = queryRunner.query(sql, new BeanHandler<Book>(Book.class),er_id); if(book.getBook_id()==0) { sql = "select * from book where clazz = ? and second_id = ?"; book.setBook_id(queryRunner.query(sql,new BeanHandler<Book>(Book.class),clazz,er_id).getBook_id()); sql = "update "+clazz+" set book_id = ?"; queryRunner.update(sql,book.getBook_id()); } return book; } @Override public void updateClazz(Book book) throws SQLException { // TODO Auto-generated method stub String sql = "update "+book.getClazz()+" set book_name = ?, price = ?, describtion = ?, clazz = ?, book_id = ?, click_num = ?, buy_num = ?, count = ?, is_new = ? where second_id = ?"; queryRunner.update(sql,book.getBook_name(),book.getPrice(),book.getDescribtion(),book.getClazz(),book.getBook_id(),book.getClick_num(),book.getBuy_num(),book.getCount(),book.getIs_new(),book.getSecond_id()); } }

#### serviceimpl ####

package cn.edu.bdu.mc.services.impls; import java.io.IOException; import java.io.InputStream; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import javax.sql.DataSource; import cn.edu.bdu.mc.beans.Book; import cn.edu.bdu.mc.daos.BookDao; import cn.edu.bdu.mc.daos.impls.BookDaoImpl; import cn.edu.bdu.mc.services.BookService; import cn.edu.bdu.mc.utils.JDBCUtil; public class BookServiceImpl implements BookService { private DataSource dataSource = JDBCUtil.getDataSource(); private BookDao dao = new BookDaoImpl(); @Override public Book findNewBookByPaiMing(int shu) throws SQLException { // TODO Auto-generated method stub return dao.findNewBookByPaiMing(shu); } @Override public void click(int book_id) throws SQLException { // TODO Auto-generated method stub Book book = dao.findBookById(book_id); book.setClick_num(book.getClick_num()+1); dao.update(book); dao.updateClazz(book); } @Override public void buy(int book_id) throws SQLException { // TODO Auto-generated method stub Book book = dao.findBookById(book_id); book.setBuy_num(book.getBuy_num()+1); dao.update(book); } @Override public List<Book> findBookReMen(int page) throws SQLException { return dao.findBookReMen(page); } @Override public void addImgByName(String book_name, String path) throws SQLException, IOException { // TODO Auto-generated method stub dao.addImgByName(book_name, path); } @Override public InputStream getImgById(int book_id) throws SQLException { // TODO Auto-generated method stub return dao.getImgById(book_id); } @Override public Book findBookByClazzAndEr_id(String clazz, int er_id) throws SQLException { // TODO Auto-generated method stub return dao.findBookByClazzAndEr_id(clazz, er_id); } @Override public List<Book> findBookByClazz(String clazz) throws SQLException { List<Book> list = dao.findAllBook(); List<Book> newList = new ArrayList<>(); //lambda表达式，emmmm，的确可读性不太好的样子。 list.forEach(book->{if(book.getClazz().equals(clazz)){newList.add(book);}}); /* 相当于 for (Book book : newList) { if(book.getClazz().equals(clazz)){ newList.add(book); } } */ return newList; } @Override public void insert(String book_name, double price, String describtion, String clazz, int count) throws SQLException { // TODO Auto-generated method stub Book book = new Book(book_name,price,describtion,clazz,count); dao.insert(book); } }

## 4.addBook.jsp ##

### 代码 ###

<%@ page language="java" contentType="text/html; charset=utf-8"
        pageEncoding="utf-8"%>
    <!DOCTYPE html>
    <html>
    <head>
    <title>添加图书</title>
    </head>
    <body style="background-color:#bbb;width:1400px;margin:0 auto">
    
    <div style="width:100%;height:100px;float:left">
    <jsp:include page="/admin/head.jsp"></jsp:include>
    </div>
    
    <div style="width:70%;height:600px;float:left;margin-left:15%;">
        
        <div style="width:55%;height:100%;float:left;margin-top:10%;">
            <img alt="拿书男孩" src="${pageContext.request.contextPath }/client/img/admin.jpg" style="width:90%;">
        </div>
        
        <div style="width:45%;height:80%;float:left;margin-top:7%">
            <form action="${pageContext.request.contextPath }/AddNewBook" method="post"
            enctype="multipart/form-data" class="form-horizontal" role="form">
            <div class="form-group">
                <label for="lastname" class="col-sm-3 control-label input-lg">书名</label>
                <div class="col-sm-9">
                    <input type="text" name="book_name" class="form-control input-lg"
                           placeholder="请输入书名" style="float:left"/>
                </div>
            </div>
            <div class="form-group">
                <label for="lastname" class="col-sm-3 control-label input-lg">价格</label>
                <div class="col-sm-9">
                    <input type="text" name="price" class="form-control input-lg"
                           placeholder="请输入价格" style="float:left"/>
                </div>
            </div>
            <div class="form-group">
                <label for="lastname" class="col-sm-3 control-label input-lg">描述</label>
                <div class="col-sm-9">
                <textarea class="form-control input-lg" name="describtion" rows="2"
                           placeholder="请输入描述" style="float:left"></textarea>
                </div>
            </div>
            <div class="form-group">
                <label for="lastname" class="col-sm-3 control-label input-lg">类别</label>
                <div class="col-sm-9">
                    <select name="clazz" class="form-control input-lg" style="float:left">
                    <option value="clothing">服装</option>
                    <option value="food">食品</option>
                    <option value="net_literature">网络文学</option>
                    <option value="nursery">育婴童</option>
                    <option value="pai">好书拍卖</option>
                    <option value="residence">家居</option>
                    <option value="sport">运动户外</option>
                </select>
                </div>
            </div>
            <div class="form-group">
                <label for="lastname" class="col-sm-3 control-label input-lg">数量</label>
                <div class="col-sm-9">
                    <input type="text" name="count" class="form-control input-lg"
                           placeholder="请输入数量" style="float:left"/>
                </div>
            </div>
            <div class="form-group">
                <label for="exampleInputFile" class="col-sm-3 control-label input-lg" style="float:left;">图片</label>
                <div class="col-sm-9">
                    <input type="file" name="img" class="form-control input-lg" style="float:left">
                </div>
            </div>
            <div class="form-group">
                <label for="firstname" class="col-sm-1 control-label input-lg"></label>
                <div class="col-sm-5">
                    <input type="submit" name="submit" value="提交"
                    class="form-control input-lg btn btn-primary"style="width:100%;float:left"/>
                </div>
                <div class="col-sm-5">
                    <input type="reset" name="reset" value="重置" id="re"
                    class="form-control input-lg btn btn-warning"style="width:100%;float:left"/>
                </div>
            </div>
          </form>
      </div>
    
    </div>
    
    <div style="width:100%;height:60px;float:left">
    <jsp:include page="/admin/foot.jsp"></jsp:include>
    </div>
    </body>
    </html>

### 效果图 ###

![1513080-20190621235418489-1788307391.png][]

## 5.AddNewBookServlet ##

### 代码 ###

package cn.edu.bdu.mc.servlets; import java.io.File; import java.io.FileOutputStream; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; import java.io.PrintWriter; import java.sql.SQLException; import java.util.List; import java.util.UUID; 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 org.apache.commons.fileupload.FileItem; import org.apache.commons.fileupload.FileUploadException; import org.apache.commons.fileupload.disk.DiskFileItemFactory; import org.apache.commons.fileupload.servlet.ServletFileUpload; import cn.edu.bdu.mc.services.BookService; import cn.edu.bdu.mc.services.impls.BookServiceImpl; /** * Servlet implementation class AddNewBookServlet */ @WebServlet("/AddNewBook") public class AddNewBookServlet extends HttpServlet { private static final long serialVersionUID = 1L; /** * @see HttpServlet#HttpServlet() */ public AddNewBookServlet() { super(); // TODO Auto-generated constructor stub } /** * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response) */ protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { BookService bookService = new BookServiceImpl(); // String book_name = request.getParameter("book_name"); // String price = request.getParameter("price"); // String describtion = request.getParameter("describtion"); // String clazz = request.getParameter("clazz"); // String count = request.getParameter("count"); // //测试成功 // response.getWriter().write(book_name+"<br>"+price+"<br>"+describtion+"<br>"+clazz+"<br>"+count); //把enctype="multipart/form-data"之后好像不能用普通方法获取了。。 DiskFileItemFactory factory = new DiskFileItemFactory(); File file = new File("d:\\Target"); if(!file.exists()) { file.mkdirs(); } factory.setRepository(file); ServletFileUpload fileUpload = new ServletFileUpload(factory); fileUpload.setHeaderEncoding("utf-8"); try { List<FileItem> fileItems = fileUpload.parseRequest(request); String value = null; String book_name = fileItems.get(0).getString("utf-8"); Double price = Double.parseDouble(fileItems.get(1).getString("utf-8")); String describtion = fileItems.get(2).getString("utf-8"); String clazz = fileItems.get(3).getString("utf-8"); int count = Integer.parseInt(fileItems.get(4).getString("utf-8")); for (FileItem fileItem : fileItems) { if(!fileItem.isFormField()) { String filename = fileItem.getName(); filename = filename.substring(filename.lastIndexOf("\\")+1); filename = UUID.randomUUID().toString()+"_"+value+"_"+filename; String webPath = "/upload/"; String filepath = getServletContext().getRealPath(webPath+filename); File file2 = new File(filepath); File file3 = new File("d:\\upload\\"+filename); file3.getParentFile().mkdirs(); file3.createNewFile(); file2.getParentFile().mkdirs(); file2.createNewFile(); InputStream inputStream = fileItem.getInputStream(); OutputStream outputStream = new FileOutputStream(file2); OutputStream outputStream2 = new FileOutputStream(file3); byte[] buffer = new byte[2048]; int len; while((len = inputStream.read(buffer)) > 0) { outputStream.write(buffer, 0, len); outputStream2.write(buffer, 0, len); } inputStream.close(); outputStream.close(); outputStream2.close(); fileItem.delete(); try { bookService.insert(book_name, price, describtion, clazz, count); String path = "d:/upload/"+filename; bookService.addImgByName(book_name, path); String htmlCode="<!DOCTYPE html>\n" + "<html>" + "<head>" + "<link rel=\"stylesheet\" href=\""+request.getContextPath()+"/bootstrap-3.3.7-dist/css/bootstrap.min.css\">" + "</head>" + "<body>" + "<div style=\"position:absolute;left:44%;top:46%;height:100px;width:240px;background-color:rgba(145, 162, 196, 0.9);border:1px;text-align:center;\"id=\"quit1\">\r\n" + " <h3>添加成功！</h3><a class=\"btn btn-info\" href=\""+request.getContextPath()+"/admin/addBook.jsp\">继续添加</a>    " +"<a class=\"btn btn-info\" href=\""+request.getContextPath()+"/client/index.jsp\">去主页</a>\n" + "</div>" + "</body>" + "</html>"; response.getWriter().write(htmlCode); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } } catch (FileUploadException e) { // TODO Auto-generated catch block e.printStackTrace(); } } /** * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response) */ protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { // TODO Auto-generated method stub doGet(request, response); } }

转载于:https://www.cnblogs.com/zhangA/p/11067356.html

[1513080-20190621235418489-1788307391.png]: /images/20211205/d5d7f3f336bb44b9a31373d84d533bb2.png