北京中科白癜风医院 http://pf.39.net/bdfyy/jdsb/180323/6122185.html课程设计报告
课程名称数据库原理与应用课程设计
选题名称网上书店管理系统
班级xxxxxxxx姓名xxxxx学号xxxxxxxxxxxx
实验组别同组实验者
完成时间年12月19日至年12月26日
指导教师xxxxxx
网上书店管理系统
1系统概述
本题的开发背景：利用网上管理实现虚拟书店的销售、进货、和邮寄操作。如今随着网络的发展，越来越少的人会去实体书店购书，而想要开书店的商家，如果开设一个线下的书店，即将面临的不仅仅是高昂的租房和装修费用，有可能还不赚钱。于是我们想到开发一个网上书店，更方便和我们一样想开书店的人群，也更方便喜欢网购的买家。
2数据库设计
2.1概念结构设计
图2-1E-R图
2.2逻辑结构设计
将E-R图转换后的关系模型应具有4个关系模式：
book(bid,bclass,bname,price,binf,bType)
consumer(cid,cname,password,sex,age,phone)
book_order(oid,cid,bid,num,address,payType,deliver,orderType)
书籍表（书编号，书类别，书名，书价格，书介绍，书类型）
顾客表（顾客编号，顾客名，密码，顾客性别，顾客电话）
订单表（订单编号，书编号，顾客编号，订单数量，订单地址，支付方式，运输方式，订单状态）
2.3物理结构设计
本系统拟采用sqlserver数据库管理系统，在word中将逻辑模型用如下表来实现：
表2-1书籍表（book）2
表2-2顾客表（consumer）
表2-3订单表(book_order)
3实现
3.1建立BookServer数据库及数据表：
createdatabaseBookServer
useBookServer
go
createtablebook
(
bidintnotnullprimarykeyidentity(1,1),
bclassvarchar(50)notnull,
bnamevarchar(50)notnull,
pricefloatnotnull,
binfvarchar(),
bTypeintnotnulldefault1
)
useBookServer
go
createtableconsumer
(
cidintnotnullprimarykeyidentity(1,1),
cnamevarchar(10)notnull,
passwordvarchar(10)notnull,
sexchar(2)notnulldefault男,
ageintnotnull,
phonevarchar(11)notnull,
)
useBookServer
go
createtablebook_order
(
oidintnotnullprimarykeyidentity(1,1),
cidintnotnullforeignkeyreferencesconsumer(cid),
bidintnotnullforeignkeyreferencesbook(bid),
numintnotnull,
addressvarchar()notnull,
payTypevarchar(20)notnull,
delivervarchar(20)default自取,
orderTypevarchar(10)default未发货
)
3.1.1数据库增加原始数据的语句：
INSERTintobook(bclass,bname,price,binf,bType)VALUES(计算机,C语言设计,49.5,学习C语言的程序设计,0)
INSERTintobook(bclass,bname,price,binf,bType)VALUES(计算机,Java程序设计,35.5,java程序设计的学习,1)
INSERTintobook(bclass,bname,price,binf,bType)VALUES(公共知识,大学英语,32.9,学习英语,1)
INSERTintobook(bclass,bname,price,binf,bType)VALUES(公共知识,高等数学,32,学习高等数学的知识,1)
INSERTintobook(bclass,bname,price,binf,bType)VALUES(文学小说,将进酒,,朝堂谋略,1)
INSERTintobook(bclass,bname,price,binf,bType)VALUES(文学小说,伪装学渣,98,校园青春,1)
INSERTintobook(bclass,bname,price,binf,bType)VALUES(文学小说,撒野,86,青春爱情,1)
INSERTintoconsumer(cname,password,sex,age,phone)VALUES(laoban,laoban,女,28,18851120)
INSERTintoconsumer(cname,password,sex,age,phone)VALUES(liuxin,liuxin,女,21,18851133)
INSERTintoconsumer(cname,password,sex,age,phone)VALUES(huangxin,huangxin,女,21,18851)
INSERTintoconsumer(cname,password,sex,age,phone)VALUES(wangjia,wangjia,女,21,18851124)
INSERTintoconsumer(cname,password,sex,age,phone)VALUES(wuxiaolei,wuxiaolei,女,21,18851125)
INSERTintobook_order(cid,bid,num,address,payType,deliver)VALUES(2,3,2,江苏南通,支付宝,自取)
3.2Java语句：
3.2.1建立bookdao
packagecn.dao;
publicclassBookDao{
privateintbid;
privateStringbclass;
privateStringbname;
privatefloatprice;
privateStringbinf;
privateintbOrder;
publicBookDao(){
}
publicintgetbOrder(){
returnbOrder;
}
publicvoidsetbOrder(intbOrder){
this.bOrder=bOrder;
}
publicintgetBid(){
returnbid;
}
publicvoidsetBid(intbid){
this.bid=bid;
}
publicStringgetBclass(){
returnbclass;
}
publicvoidsetBclass(Stringbclass){
this.bclass=bclass;
}
publicStringgetBname(){
returnbname;
}
publicvoidsetBname(Stringbname){
this.bname=bname;
}
publicfloatgetPrice(){
returnprice;
}
publicvoidsetPrice(floatprice){
this.price=price;
}
publicStringgetBinf(){
returnbinf;
}
publicvoidsetBinf(Stringbinf){
this.binf=binf;
}
publicvoidprint(){
System.out.println(bid+"《"+bname+"》"+bclass+""+binf+"单价："+price);
}
}
3.2.2建立orderdao
packagecn.dao;
publicclassOrderDao{
privateintoid;
privateintcid;//用户编号
privateintbid;//书籍编号
privateStringphone;//收货手机
privateintnum;//数量
privateStringaddress;//地址
privateStringpayType;//付款方式
privateStringdeliver;//送货方式
privateStringorderType;//是否已发货
publicOrderDao(){
}
publicOrderDao(intoid,intcid,intbid,Stringphone,Stringaddress,StringpayType,Stringdeliver,StringorderType){
this.cid=cid;
this.bid=bid;
this.phone=phone;
this.address=address;
this.payType=payType;
this.deliver=deliver;
this.orderType=orderType;
}
publicintgetNum(){
returnnum;
}
publicvoidsetNum(intnum){
this.num=num;
}
publicintgetOid(){
returnoid;
}
publicvoidsetOid(intoid){
this.oid=oid;
}
publicStringgetPhone(){
returnphone;
}
publicvoidsetPhone(Stringphone){
this.phone=phone;
}
publicStringgetAddress(){
returnaddress;
}
publicvoidsetAddress(Stringaddress){
this.address=address;
}
publicintgetCid(){
returncid;
}
publicvoidsetCid(intcid){
this.cid=cid;
}
publicintgetBid(){
returnbid;
}
publicvoidsetBid(intbid){
this.bid=bid;
}
publicStringgetPayType(){
returnpayType;
}
publicvoidsetPayType(StringpayType){
this.payType=payType;
}
publicStringgetDeliver(){
returndeliver;
}
publicvoidsetDeliver(Stringdeliver){
this.deliver=deliver;
}
publicStringgetOrderType(){
returnorderType;
}
publicvoidsetOrderType(StringorderType){
this.orderType=orderType;
}
publicvoidprint(){
System.out.println("订单编号："+oid+"用户id："+cid+"书籍id："+bid+"联系"+phone+"送货 }
}
3.2.3建立userdao
packagecn.dao;
publicclassUserDao{
privateintcid;//用户编号
privateStringcname;//用户名
privateStringpassword;//密码
privateStringsex;//性别
privateintage;//年龄
privateStringphone;//手机
publicUserDao(){
}
publicUserDao(intcid,Stringcname,Stringsex,intage,Stringphone,Stringpassword){
this.age=age;
this.cid=cid;
this.password=password;
this.cname=cname;
this.phone=phone;
this.sex=sex;
}
publicStringgetPassword(){
returnpassword;
}
publicvoidsetPassword(Stringpassword){
this.password=password;
}
publicintgetCid(){
returncid;
}
publicvoidsetCid(intcid){
this.cid=cid;
}
publicStringgetCname(){
returncname;
}
publicvoidsetCname(Stringcname){
this.cname=cname;
}
publicStringgetSex(){
returnsex;
}
publicvoidsetSex(Stringsex){
this.sex=sex;
}
publicintgetAge(){
returnage;
}
publicvoidsetAge(intage){
this.age=age;
}
publicStringgetPhone(){
returnphone;
}
publicvoidsetPhone(Stringphone){
this.phone=phone;
}
}
3.3增删改查
3.3.1book表的增删改查
packagecn.server;
importjava.sql.Connection;
importjava.sql.ResultSet;
importjava.sql.SQLException;
importjava.sql.Statement;
importjava.util.*;
importcn.dao.BookDao;
publicclassBookServer{
ConnectiondbConn=null;
Scannerin=newScanner(System.in);
Statementstate=null;
publicBookServer(ConnectiondbConn){
this.dbConn=dbConn;
try{
state=dbConn.createStatement();
}catch(SQLExceptione){
//TODOAuto-generatedcatchblock
e.printStackTrace();
}
}
publicBookDaosetBook(ResultSetres){
BookDaobook=null;
try{
book=newBookDao();
book.setBid(res.getInt("bid"));
book.setBname(res.getString("bname"));
book.setBclass(res.getString("bclass"));
book.setBinf(res.getString("binf"));
book.setPrice(res.getFloat("price"));
}catch(SQLExceptione){
//TODOAuto-generatedcatchblock
e.printStackTrace();
}
returnbook;
}
publicintselectAll(){
ArrayListBookDaobooks=null;
try{
books=newArrayList();
Stringsql="select*frombookwherebType=1";
ResultSetres=state.executeQuery(sql);
while(res.next()){
BookDaobook=setBook(res);
books.add(book);
book.print();
}
}catch(SQLExceptione){
//TODOAuto-generatedcatchblock
e.printStackTrace();
}
returnbooks.size();
}
publicvoidaddBook(){
Stringbname,bclass,binf;
floatprice;
System.out.print("请输入书名：");
bname=in.next();
System.out.print("请输入书籍分类：");
bclass=in.next();
System.out.print("请输入书籍介绍：");
binf=in.next();
System.out.print("请输入书籍价格：");
price=in.nextFloat();
Stringsql="insertintobook(bclass,bname,price,binf)values("+bclass+","+bname+","+price+","+binf+")";
try{
state.executeUpdate(sql);
System.out.println("增加成功！");
}catch(SQLExceptione){
//TODOAuto-generatedcatchblock
e.printStackTrace();
System.out.println("增加失败！");
}
}
publicvoidupdateBook(){
while(true){
System.out.print("输入要修改单价的书名：");
Stringbname=in.next();
intl=selectBookByName(bname);
if(l1){
System.out.println("您输入的书籍名称过于模糊，请重新输入！");
}elseif(l==0){
System.out.println("查不到这本书，请检查是否输错！");
}else{
System.out.print("请输入要修改的单价：");
floatprice=in.nextFloat();
Stringsql="updatebooksetprice="+price+"wherebnamelike%"+bname+"%";
try{
state.executeUpdate(sql);
System.out.println("更新成功！");
}catch(SQLExceptione){
//TODOAuto-generatedcatchblock
e.printStackTrace();
System.out.println("更新失败！");
}
}
System.out.println("是否还需更改，继续更改选择1，否则选择2：");
intn=in.nextInt();
if(n==2)
break;
}
}
publicintselectBookByName(Stringname){
Stringsql="select*frombookwherebType=1andbnamelike%"+name+"%";
ArrayListBookDaobooks=null;
try{
ResultSetbooksql=state.executeQuery(sql);
books=newArrayList();
while(booksql.next()){
BookDaobook=setBook(booksql);
books.add(book);
book.print();
}
}catch(SQLExceptione){
//TODOAuto-generatedcatchblock
e.printStackTrace();
}
returnbooks.size();
}
publicvoiddeleteBook(){
while(true){
System.out.print("请输入需要删除的书名：");
Stringbname=in.next();
inti=selectBookByName(bname);
if(i1){
System.out.println("书名较为模糊，请继续输入！");
}elseif(i==0){
System.out.println("查无此书！");
}else{
Stringsql="updatebooksetbType=0wherebnamelike%"+bname+"%";
try{
state.executeUpdate(sql);
System.out.println("删除成功！");
}catch(SQLExceptione){
//TODOAuto-generatedcatchblock
e.printStackTrace();
System.out.println("删除失败！");
}
}
System.out.println("是否还需删除，继续删除选择1，否则选择2：");
intn=in.nextInt();
if(n==2)
break;
}
}
publicintselectBuyBooks(){
Stringsql="selectbname,sum(num)asnumfrombook,book_orderwherebook.bid=book_order.bidgroupbybnameorderbynumdesc";
intsum=0;
try{
ResultSetres=state.executeQuery(sql);
while(res.next()){
Stringbname=res.getString("bname");
intnum=res.getInt("num");
System.out.println("《"+bname+"》卖出："+num);
sum+=num;
}
}catch(SQLExceptione){
//TODOAuto-generatedcatchblock
e.printStackTrace();
}
returnsum;
}
publicintseachBook(){
ArrayListBookDaobooks=null;
Stringsql="select*frombookwherebType=1and";
System.out.print("是否按照类别查找（1-yes，2-no）：");
Stringbclass=null;
if(in.nextInt()==1){
System.out.print("书籍的类别：");
bclass=in.next();
sql+="bclass="+bclass+"and";
}
System.out.print("输入要查找的书名：");
Stringbname=in.next();
sql+="bnamelike%"+bname+"%";
try{
books=newArrayList();
ResultSetres=state.executeQuery(sql);
System.out.println("类别："+bclass+"书名："+bname+"的查询结果");;
while(res.next()){
BookDaobook=setBook(res);
books.add(book);
book.print();
}
}catch(SQLExceptione){
//TODOAuto-generatedcatchblock
e.printStackTrace();
}
returnbooks.size();
}
}
3.3.2order表的增删改查
packagecn.server;
importjava.sql.Connection;
importjava.sql.ResultSet;
importjava.sql.SQLException;
importjava.sql.Statement;
importjava.util.ArrayList;
importjava.util.Scanner;
importcn.dao.OrderDao;
publicclassOrderServer{
ConnectiondbConn;
Scannerin=newScanner(System.in);
Statementstate=null;
publicOrderServer(ConnectiondbConn){
this.dbConn=dbConn;
try{
state=dbConn.createStatement();
}catch(SQLExceptione){
//TODOAuto-generatedcatchblock
e.printStackTrace();
}
}
publicOrderDaosetOrder(ResultSetres){
OrderDaoorder=newOrderDao();
try{
order.setOid(res.getInt("oid"));
order.setAddress(res.getString("address"));
order.setPhone(res.getString("phone"));
order.setCid(res.getInt("cid"));
order.setBid(res.getInt("bid"));
order.setPayType(res.getString("payType"));
order.setDeliver(res.getString("deliver"));
order.setOrderType(res.getString("orderType"));
order.setNum(res.getInt("num"));
}catch(SQLExceptione){
//TODOAuto-generatedcatchblock
e.printStackTrace();
}
returnorder;
}
publicintselectAllOrder(Integercid){
Stringsql=
"selectnum,oid,bid,book_order.cid,phone,payType,deliver,orderType,address
frombook_order,consumerwherebook_order.cid=consumer.cid";
if(cid!=null){
sql+="andbook_order.cid="+cid;
}
ArrayListOrderDaoorders=newArrayList();
try{
ResultSetres=state.executeQuery(sql);
while(res.next()){
OrderDaoorder=setOrder(res);
orders.add(order);
order.print();
}
}catch(SQLExceptione){
//TODOAuto-generatedcatchblock
e.printStackTrace();
}
returnorders.size();
}
publicvoidupdateOrder(){
while(true){
System.out.print("输入要修改的订单号：");
intoid=in.nextInt();
if(selectByOid(oid)==0){
System.out.println("输入的订单号错误,请重新输入！");
}else{
System.out.print("请输入该订单现在的状态：");
StringorderType=in.next();
Stringsql="updatebook_ordersetorderType="+orderType+"whereoid="+oid;
try{
state.executeUpdate(sql);
System.out.println("订单状态更新成功！");
}catch(SQLExceptione){
//TODOAuto-generatedcatchblock
e.printStackTrace();
System.out.println("订单状态更新失败！");
}
}
System.out.println("是否还需修改，继续修改选择1，否则选择2：");
intn=in.nextInt();
if(n==2)
break;
}
}
publicintselectByOid(intoid){
Stringsql="selectoidfrombook_orderwhereoid="+oid;
inti=0;
try{
ResultSetres=state.executeQuery(sql);
while(res.next())
i=res.getInt("oid");
}catch(SQLExceptione){
//TODOAuto-generatedcatchblock
e.printStackTrace();
}
returni;
}
publicintselectByOrderType(StringorderType){
ArrayListOrderDaoorders=newArrayList();
Stringsql=
"selectnum,oid,bid,book_order.cid,phone,payType,deliver,orderType,address
frombook_order,consumer
wherebook_order.cid=consumer.cidandorderType="+orderType+"";
try{
ResultSetres=state.executeQuery(sql);
while(res.next()){
OrderDaoorder=setOrder(res);
orders.add(order);
order.print();
}
}catch(SQLExceptione){
//TODOAuto-generatedcatchblock
e.printStackTrace();
}
returnorders.size();
}
publicvoiddeleteOrder(){
while(true){
System.out.print("输入需要删除的订单编号：");
intoid=in.nextInt();
if(selectByOid(oid)==0){
System.out.println("查不到此订单号！");
}else{
Stringsql="deletefrombook_orderwhereoid="+oid;
try{
state.executeUpdate(sql);
System.out.println("订单信息删除成功！");
}catch(SQLExceptione){
//TODOAuto-generatedcatchblock
e.printStackTrace();
System.out.println("订单信息删除失败！");
}
}
System.out.println("是否还需删除，继续删除选择1，否则选择2：");
intn=in.nextInt();
if(n==2)
break;
}
}
publicvoidaddOrder(intcid){
System.out.println("创建订单需要您提供相关信息：");
System.out.print("购买书籍的编号：");
intbid=in.nextInt();
floatprice=-1;
Stringsql="selectpricefrombookwherebid="+bid;
try{
ResultSetres=state.executeQuery(sql);
while(res.next())
price=res.getFloat("price");
}catch(SQLExceptione){
//TODOAuto-generatedcatchblock
e.printStackTrace();
}
if(price==0){
System.out.print("书籍编号输入错误，找不到该编号的书籍！");
return;
}
System.out.print("输入购买的数量：");
intnum=in.nextInt();
System.out.print("请选择取货形式（自取，邮寄）：");
Stringdeliver=in.next();
System.out.print("请选择付款方式（支付宝，
