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

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

主要改动

今天也是各种各种各种出错的一天啊，经历了各种方法的重写，各种触发器的重写。

那么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.class));

}

@override

public list 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.class));

}

@override

public void insert(book book) throws sqlexception {

// todo auto-generated me