目录

• • 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>

效果图

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>&nbsp;&nbsp;&nbsp;&nbsp;"
                                        +"<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);
    }

}