图书馆管理系_系统实现报告.docx

图书馆管理系统系统实现报告学号 38060620 姓名 吴恺1 实现环境：本系统是用java实现，数据库选用SQL Server 2008，使用Eclipse开发，最终系统可在windows7中运行。2 系统功能结构图3 基本表的定义,主外码等完整性约束定义，索引的定义Create table Admin_info (adminNo char(20),adminName varchar(20),passwordchar(20),primary key (adminNo);Create table Book_info(bookNamevarchar(50),pressvarchar(30),ISBNchar(20),Primarykey(ISBN);CreatetableBook_info(bookNochar(20),ownerNochar(20),borrowDatedate,returnDatedate,primarykey(bookNo);Create tablelib_Book(bookNochar(20),ISBNchar(20),bookStateint,Positionvarchar(30),primarykey(bookNo);CreateUser_info(cardNochar(20),namevarchar(10),passwordchar(20),primarykey(cardNo);4 系统的安全性设计，不同人员的外模式及相关权限4.1 管理员账号以管理员账号登录时，可以对系统中的表中的数据进行查询，插入，删除，修改操作4.2 普通用户账号以普通用户账号登录时，可以对系统中的lib_Book，Book_info表进行选择操作，可以对自己的账号进行修改5 存储过程、触发器和函数的代码说明5.1 存储过程1.Create procedure add_AdminadminNOchar(20),adminNamevarchar(20),passwordchar(20)asinsert into Admin_info (adminNo ,adminName,password)values(adminNO,adminNO,password);2.Create procedure add_BookbookNochar(20),bookNamevarchar(50),pressvarchar(30),ISBNchar(20),positionvarchar(30)asdeclare i int select i=COUNT(*) from Book_info where ISBN=ISBNif i=0begininsert into Book_info(bookName,press,ISBN)values(bookName,press,ISBN)insert into lib_Book(bookNo,ISBN,bookState,Position)values(bookNo,ISBN,1,position)endelse begininsert into lib_Book(bookNo,ISBN,bookState,Position)values(bookNo,ISBN,1,position)end3.Create procedure add_User cardNo char(20),namevarchar(10),password char(20)ASdeclare iint select i=COUNT(*) from User_info where cardNo=cardNoif i=0begin insert into User_info(cardNo,name,password) values(cardNo,name,password)return 1endelse if i=1beginreturn 0end4.Create proc borrow_BookbookNochar(20),cardNochar(20)asdeclare jintdeclare k intdeclare p intdeclare time1 dateselect j=COUNT(*) from Borrow_infowhere bookNo=bookNoselect k=COUNT(*) from User_infowhere cardNo=cardNoselect p=COUNT(*) from lib_Bookwhere bookNo=bookNoif j=0 and k=1 and p=1begin select time1=DATEADD(DAY,15,GETDATE()insert into Borrow_info(bookNo,ownerNo,borrowDate,returnDate)values(bookNo,cardNo,GETDATE(),time1)return 1endelse if j=1 return 2else if k=0return 3else if p=0return 45.Create proc dbo.logintypeint,IDchar(20),passwordchar(20),namevarchar(10)outputasdeclare i intif type=1beginSELECT i=count(*) from user_info where cardNo=ID and password=passwordendelse if type=2beginselect i=COUNT(*) from Admin_info where adminNo=ID and password=passwordendif i=1 and type=1beginselect name=name from User_info where cardNo=IDreturn 1endelse if i=1 and type=2beginselect name=adminName from Admin_info where adminNo=IDreturn 2endelse return 06.Create proc dbo.query_BookbookNamevarchar(50)asselect * from Book_infojoin lib_Book on Book_info.ISBN=lib_Book.ISBNwhere bookName=bookName7.Create proc dbo.return_BookbookNochar(20)asdeclare jintdeclare iintselect j=COUNT(*) from Borrow_infowhere bookNo=bookNoselect i=COUNT(*) from lib_Bookwhere bookNo=bookNoif j=1 and i=1begindelete from Borrow_info where bookNo=bookNoreturn 1endelse if i=0return 0else if j=0 return 28.Create proc dbo.user_OverallIDnochar(20),borrowedNoint output,expiredNoint outputasselect borrowedNo=COUNT(*) from Borrow_info where ownerNo=IDnoselect expiredNo=COUNT(*) from Borrow_info where returnDateGETDATE()123455.15.2 触发器1Create trigger update_stateON Borrow_infofor deleteasbegindeclare bookNochar(20)select bookNo=deleted.bookNo from deletedupdate lib_Book set bookState=1 where bookNo=bookNoend2Create trigger update_state2on Borrow_infofor insertasbegindeclare bookNochar(20)select bookNo=inserted.bookNo from insertedupdate lib_book set bookState=0 where bookNo=bookNoend6 实现过程中主要技术论述1编写界面时采用的是卡片式布局管理器。2还有其它一些技术7 系统功能展示7.1 登录界面7.2 普通用户主界面7.3 查询书籍界面7.4 查看借书详情7.5 修改密码界面7.6 管理员用户主界面7.7 添加普通账户界面8 源程序简要说明序号程序名详细说明1Void login()登陆界面2Void customMenu()普通用户主界面3Void queryBookMenu()查询书籍界面4Void borrowDetailMenu()借书详情界面5Void userChangPw()用户密码修改界面6Void adminMenu()管理员主界面7Void addUserMenu()添加普通用户界面8Void addBookMenu()添加书籍界面9Void borrowBookMenu()借书界面10Void returnBookMenu()还书界面11Int loginCheck()登陆检查12Void queryBook(String)查询书籍13Void queryBorrowDetail()查询借书详情14Void updateUserPw(String)更新密码15Boolean isNumeric(String)检查是否是数字16Int updateNewUser(String, String, String)添加新普通用户17Void updateNewBook(String, String, String, String, S