图书借阅子系统数据库设计
南昌航空大学项目报告
二0 21 年 12 月 4 日
课程名称： 数据库原理 名称： 图书借阅子系统数据库设计
指导教师评定： 签名：
一、 项目环境
1． Windows2000或以上版本；
2． SQLServer 2005或以上版本。
二、 项目目的
掌握数据库设计的基本方法，了解C/S与B/S结构应用系统的特点与适用场合，了解C/S与B/S结构应用系统的不同开发设计环境与开发设计方法，综合运用前面实验掌握的数据库知识与技术设计某小型数据库应用系统。
三、 项目要求
完成实验指导书中p255 (2)。
四、 项目设计开发过程及参考源代码
1、 需求分析
功能需求：
1.浏览功能
列出当前数据文件中的图书信息，读者信息，借阅信息，还书信息等记录
2.查询功能
书目匹配查询
读者匹配查询
书目和读者匹配查询
3.添加功能
添加书目和读者记录及借还书记录
添加系统设置相关信息
4.修改功能
修改书目和读者记录，提供相关确认机制
修改系统设置相关信息，提供相关确认机制
5.删除功能
删除拿出书目和读者记录，提供相关确认机制
删除系统设置相关信息，提供相关确认机制
性能需求：
1.数据精确度
保证查询的查全率和查准率为100%，所在相应域中包含查询关键字的记录都能查到，所有在相应域中不包含查询关键字的记录都不能查到。
2.适应性
满足运行环境在允许操作系统之间的安全转换和与其他应用软件的独立运行要求。
2、 数据库概念结构设计
概念模型如下：
3、 数据库逻辑结构设计
借书证表stu：
|
属性名 |
类型 |
备注 |
|
姓名sna |
Varchar(20) |
不允许空 |
|
系别sde |
Varchar(20) |
不允许空 |
|
专业ssp |
Varchar(20) |
不允许空 |
|
学号sid |
Varchar(20) |
不允许空，唯一 |
|
性别sex |
Varchar(2) |
男或女不允许空 |
|
借书上限sup |
Int |
4 |
|
借书证号sno |
Varchar(20) |
主键 |
|
联系方式ste |
Varchar(20) |
不允许空 |
|
能否借书scan |
Varchar(5) |
能或不能 |
图书表book1：
|
属性名 |
类型 |
备注 |
|
书名bna |
Varchar(50) |
主键 |
|
类型bki |
Varchar(20) |
不允许空 |
|
出版社bpu |
Varchar(50) |
不允许空 |
|
出版日期bda |
Varchar(20) |
不允许空 |
|
存放位置bpl |
Varchar(20) |
不允许空 |
|
存放数量bum |
Int |
大于0 |
图书详细表book2：
|
属性名 |
类型 |
备注 |
|
书名bna |
Varchar(50) |
主键 |
|
借阅状态byn |
Varchar(5) |
已借或未借不允许空 |
|
书编号bno |
Varchar(20) |
主键 |
图书借阅表lend:
|
属性 |
类型 |
备注 |
|
借书证号sno |
Varchar(20) |
主键 |
|
书名bna |
Varchar(50) |
不允许空 |
|
书编号bno |
Varchar(20) |
主键 |
|
借书时间lbo |
Date time |
不允许空 |
|
借书期限lda |
Int |
62 |
|
还书时间lre |
Date time |
允许空 |
超期处罚表punishment：
|
属性 |
类型 |
备注 |
|
借书证号sno |
Varchar(20) |
主键 |
|
书编号bno |
Varchar(20) |
主键 |
|
超出时间pda |
Int |
不允许空 |
|
罚款金额pmo |
Money |
不允许空 |
管理人员表manager：
|
属性 |
类型 |
备注 |
|
账号mno |
Varchar(20) |
主键 |
|
姓名mna |
Varchar(20) |
不允许空 |
|
密码mpa |
Varchar(20) |
不允许空 |
4、 表及视图的创建代码
创表：
create table stu
(
sno varchar(20) primary key,
sna varchar(20) not null,
sde varchar(20) not null,
ssp varchar(20) not null,
sid varchar(20) not null unique,
sex varchar(2) check(sex='男' or sex='女') not null,
sup int default(4) not null,
ste varchar(20) not null，
scan varchar(5) check(scan='能' or scan='不能') not null,
);
create table book1
(
bna varchar(50) primary key,
bki varchar(20) not null,
bpu varchar(50) not null,
bda varchar(20) not null,
bpl varchar(20) not null,
bnum int check(bnum >=0)
);
create table book2
(
bno varchar(20) primary key,
bna varchar(50) not null,
byn varchar(5) check(byn='已借' or byn='未借') not null
);
create table manager
(
mno varchar(20) primary key,
mna varchar(20) not null,
mpa varchar(20) not null
);
create table lend
(
sno varchar(20),
bna varchar(20),
bno varchar(20),
lbo Datetime not null,
lda int default(62),
lre Datetime,
primary key (sno,bno),
foreign key (sno) references stu(sno),
foreign key (bno) references book2(bno),
);
create table punishment
(
sno varchar(20),
bno varchar(20),
pda int not null,
pmo money not null,
primary key (sno,bno),
foreign key (sno) references stu(sno),
foreign key (bno) references book2(bno),
);
创建视图：
create view Find_Book1
as
select *
from book1
where bnum>0;
create view Find_Book2
as
select *
from book2
where byn='未借';
/*插入数据
insert into book1(bna,bki,bpu,bda,bpl,bnum)
values('数据库系统概论','数据库','高等教育出版社','2020年4月','A-1-3',3);
insert into book1(bna,bki,bpu,bda,bpl,bnum)
values('Java程序设计','编程语言','机械工业出版社','2020年11月','B-2-4',2);
insert into book1(bna,bki,bpu,bda,bpl,bnum)
values('Java程序设计2','编程语言','机械工业出版社','2020年11月','B-2-4',1);
insert into book2(bno,bna,byn)
values('000001','数据库系统概论','未借');
insert into book2(bno,bna,byn)
values('000002','数据库系统概论','未借');
insert into book2(bno,bna,byn)
values('000003','数据库系统概论','未借');
insert into book2(bno,bna,byn)
values('000004','Java程序设计','未借');
insert into book2(bno,bna,byn)
values('000005','Java程序设计','未借');
insert into book2(bno,bna,byn)
values('000006','Java程序设计2','未借');
insert into stu(sno,sna,sde,ssp,sid,sex,sup,ste,scan)
values('000001','张三','软件','物联网工程','19207201','男','4','13109876543','能');
insert into stu(sno,sna,sde,ssp,sid,sex,sup,ste,scan)
values('000002','李红','软件','软件工程','19201601','女','4','13199999999','能');
insert into stu(sno,sna,sde,ssp,sid,sex,sup,ste,scan)
values('000003','王五','软件','软件工程','19201602','男','4','13188888888','能');
*/
5、 存储过程和触发器的设计
借书触发器：
create trigger t_lend
on lend for insert
as
declare @bna varchar(50),@bno varchar(20),@sno varchar(20)
select @bna=bna,@bno=bno,@sno=sno from inserted
begin
update book1 set bnum=bnum-1 where bna=@bna
update book2 set byn='已借' where bno=@bno
update stu set sup=sup-1 where sno=@sno
End
例子：
insert into lend(sno,bna,bno,lbo)
values('000001','数据库系统概论','000001','2005-12-16 23:32:48')
还书触发器：
create trigger t_back
on lend for update
as if update(lre)
declare @bna varchar(50),@bno varchar(20),@sno varchar(20)
select @bna=bna,@bno=bno,@sno=sno from inserted
begin
update book1 set bnum=bnum+1 where bna=@bna
update book2 set byn='未借' where bno=@bno
update stu set sup=sup+1 where sno=@sno
End
例子：
update lend
set lre='2006-3-16 23:32:48'
where bno='000001' and sno='000001'
罚款触发器：
create trigger t_punish
on lend for update
as
if update(lre)
begin
declare @lre datetime,
@lbo datetime,
@lda int,
@pda int,
@days int,
@pmo money,
@sno varchar(20),
@bno varchar(20)
select @lre=lre from inserted
select @lbo=lbo from inserted
select @lda=lda from inserted
select
@days=convert(int,@lre)-convert(int,@lbo)
select @sno=sno from inserted
select @bno=bno from inserted
if @days>@lda
begin
select @pda=@days-@lda
select @pmo=@pda*0.02
insert into
punishment(sno,bno,pda,pmo)
values(@sno,@bno,@pda,@pmo)
end
end
例子：
上个还书的例子已经超时，结果如下：
是否能借书触发器：
create trigger t_ifcanlend
on lend for insert
as
declare @sno varchar(10),
@scan varchar(5),
@sup int
select @sno=sno from inserted
select @scan=scan,@sup=sup from stu where sno=@sno
if @scan='不能'
begin
print ' 您有超期罚款！ '
rollback transaction
end
if @sup<0
begin
print ' 超过最大借书上限！ '
rollback transaction
end
例子：
是否能删除书籍：
create trigger t_deletebook
on book1 for delete
as
declare @bna varchar(20),
@bno varchar(20)
select @bna=bna from deleted
select @bno
from book1,book2
where book1.bna=book2.bna and book1.bna=@bna and book2.byn='已借';
if @bno is null
begin
print ' 还有未归还的书籍！ '
rollback transaction
end
例子：
insert into lend(sno,bna,bno,lbo)
values('000001','Java程序设计2','000006','2006-12-16 23:32:48');
delete from book1 where bna='Java程序设计2';
存储过程：
借书：
create procedure p1
@sno varchar(20),
@bna varchar(20),
@bno varchar(20),
@lbo datetime
as
begin
insert into
lend(sno,bna,bno,lbo)
values(@sno,@bna,@bno,@lbo)
End
例子：
还书：
create procedure p2
@sno varchar(20),
@bno varchar(20),
@lre datetime
as
begin
update lend set lre=@lre where sno=@sno and bno=@bno
End
例子：
查询书：
create procedure p3
@bna varchar(50)
as
begin
select *
from book1
where bna=@bna
End
例子：
查询学生正在借阅书籍的信息：
create procedure p4
@sno varchar(50)
as
begin
select sno 借阅编号,lend.bna 书名,lbo 借阅时间,book2.byn 归还状态
from lend,book2
where sno=@sno and book2.byn='已借' and lend.bno=book2.bno
End
例子：
6、 前台系统连接数据库代码
1.学生的增删：
package 图书管理系统;
import java.awt.*;
import java.awt.event.*;
import javax.swing.*;
import java.sql.*;
public class UserAdd
extends JFrame
implements ActionListener {
DataBaseManager db = new DataBaseManager();
ResultSet rs;
Container c;
JPanel panel1, panel2;
JLabel user_ID_Label,userLabel, passwordLabel, passwordConfirmLabel, loginPrivelegeLabel;
JTextField user_ID_TextField,userTextField;
JPasswordField passwordTextField, passwordConfirmTextField;
JComboBox loginPrivelegeComboBox;
JButton addBtn, cancelBtn;
public UserAdd() {
super("添加用户");
c = getContentPane();
c.setLayout(new BorderLayout());
user_ID_Label = new JLabel("用户ID", JLabel.CENTER);
userLabel = new JLabel("用户名", JLabel.CENTER);
passwordLabel = new JLabel("密码", JLabel.CENTER);
passwordConfirmLabel = new JLabel("确认密码", JLabel.CENTER);
loginPrivelegeLabel = new JLabel("登录权限", JLabel.CENTER);
user_ID_TextField = new JTextField(25);
userTextField = new JTextField(25);
passwordTextField = new JPasswordField(25);
passwordConfirmTextField = new JPasswordField(25);
loginPrivelegeComboBox = new JComboBox();
loginPrivelegeComboBox.addItem("系统管理员");
loginPrivelegeComboBox.addItem("书籍管理员");
loginPrivelegeComboBox.addItem("学生");
addBtn = new JButton("添加");
cancelBtn = new JButton("取消");
addBtn.addActionListener(this);
cancelBtn.addActionListener(this);
panel1 = new JPanel();
panel1.setLayout(new GridLayout(5, 2));
panel1.add(user_ID_Label);
panel1.add(user_ID_TextField);
panel1.add(userLabel);
panel1.add(userTextField);
panel1.add(passwordLabel);
panel1.add(passwordTextField);
panel1.add(passwordConfirmLabel);
panel1.add(passwordConfirmTextField);
panel1.add(loginPrivelegeLabel);
panel1.add(loginPrivelegeComboBox);
c.add(panel1, BorderLayout.CENTER);
panel2 = new JPanel();
panel2.add(addBtn);
panel2.add(cancelBtn);
c.add(panel2, BorderLayout.SOUTH);
setSize(300, 300);
}
public void actionPerformed(ActionEvent e) {
if (e.getSource() == cancelBtn) {
db.closeConnection();
this.dispose();
}
else if (e.getSource() == addBtn) {
try {
String strSQL = "select * from userTable where userName='" +
userTextField.getText().trim() + "'";
if (userTextField.getText().trim().equals("")) {
JOptionPane.showMessageDialog(null, "用户名不能为空！");
}
else if (new String(passwordTextField.getPassword()).trim().equals("")) {
JOptionPane.showMessageDialog(null, "密码不能为空！");
}
else if (!new String(passwordTextField.getPassword()).trim().equals(
new String(passwordConfirmTextField.getPassword()).trim())) {
JOptionPane.showMessageDialog(null, "两次输入的密码不一致！");
}
else {
if (db.getResult(strSQL).first()) {
JOptionPane.showMessageDialog(null, "此用户已经存在，请重新输入用户名！");
}
else {
strSQL = "insert into userTable values('" +
user_ID_TextField.getText().trim() + "','" +
userTextField.getText().trim() + "','" +
new String(passwordTextField.getPassword()).
trim() + "','" + loginPrivelegeComboBox.getSelectedItem() +
"')";
this.dispose();
rs = db.getResult(strSQL);
JOptionPane.showMessageDialog(null, "添加用户成功！");
}
}
}
catch (SQLException sqle) {
System.out.println(sqle.toString());
}
catch (Exception ex) {
System.out.println(ex.toString());
}
}
}
}
package 图书管理系统;
import java.awt.*;
import java.awt.event.*;
import javax.swing.*;
import java.sql.*;
public class UserDelete
extends JFrame
implements ActionListener {
DataBaseManager db = new DataBaseManager();
ResultSet rs;
JPanel panel1, panel2;
Container c;
JLabel userLabel, passwordLabel;
JTextField userTextField;
JPasswordField passwordTextField;
JButton yesBtn, cancelBtn;
public UserDelete() {
super("删除用户");
c = getContentPane();
c.setLayout(new BorderLayout());
userLabel = new JLabel("用户名", JLabel.CENTER);
passwordLabel = new JLabel("密码", JLabel.CENTER);
userTextField = new JTextField(25);
passwordTextField = new JPasswordField(25);
yesBtn = new JButton("确定");
cancelBtn = new JButton("取消");
yesBtn.addActionListener(this);
cancelBtn.addActionListener(this);
panel1 = new JPanel();
panel1.setLayout(new GridLayout(2, 2));
panel1.add(userLabel);
panel1.add(userTextField);
panel1.add(passwordLabel);
panel1.add(passwordTextField);
panel2 = new JPanel();
panel2.add(yesBtn);
panel2.add(cancelBtn);
c.add(panel1, BorderLayout.CENTER);
c.add(panel2, BorderLayout.SOUTH);
setSize(300, 300);
}
public void actionPerformed(ActionEvent e) {
try {
if (e.getSource() == cancelBtn) {
db.closeConnection();
this.dispose();
}
else if (e.getSource() == yesBtn) {
char[] password = passwordTextField.getPassword();
String passwordSTR = new String(password);
String strSQL = "select * from userTable where userName='" +
userTextField.getText().trim() + "' and password='" +
passwordSTR + "'";
if (userTextField.getText().trim().equals("")) {
JOptionPane.showMessageDialog(null, "用户名不能为空！");
}
else if (passwordTextField.equals("")) {
JOptionPane.showMessageDialog(null, "密码不能为空！");
}
else if (db.getResult(strSQL).first()) {
strSQL = "delete userTable where username='" +
userTextField.getText().trim() + "'";
db.getResult(strSQL);
JOptionPane.showMessageDialog(null, "删除成功!");
this.dispose();
db.closeConnection();
}
else {
JOptionPane.showMessageDialog(null, "不存在此用户或者密码错误！");
}
}
}
catch (SQLException sqle) {
System.out.println(sqle.toString());
}
catch (Exception ex) {
System.out.println(ex.toString());
}
}
}
2.借书还书：
package 图书管理系统;
import java.awt.*;
import java.awt.event.*;
import javax.swing.*;
import java.sql.*;
import javax.swing.table.*;
import java.util.Vector;
public class BorrowBookList
extends JFrame
implements ActionListener {
/**
*
*/
private static final long serialVersionUID = 1L;
DataBaseManager db = new DataBaseManager();
ResultSet rs;
Container c;
JPanel panel1, panel2;
JLabel bookNameLabel, studentNameLabel;
JTextField bookNameTextField, studentNameTextField;
JButton searchBtn, exitBtn;
JTable table = null;
DefaultTableModel defaultModel = null;
public BorrowBookList() {
super("借阅信息查询！");
c = getContentPane();
c.setLayout(new BorderLayout());
bookNameLabel = new JLabel("书名 ", JLabel.CENTER);
studentNameLabel = new JLabel("借阅者", JLabel.CENTER);
bookNameTextField = new JTextField(15);
studentNameTextField = new JTextField(15);
searchBtn = new JButton("查询");
exitBtn = new JButton("退出");
searchBtn.addActionListener(this);
exitBtn.addActionListener(this);
Box box1 = Box.createHorizontalBox();
box1.add(studentNameLabel);
box1.add(studentNameTextField);
box1.add(searchBtn);
Box box2 = Box.createHorizontalBox();
box2.add(bookNameLabel);
box2.add(bookNameTextField);
box2.add(exitBtn);
Box boxH = Box.createVerticalBox();
boxH.add(box1);
boxH.add(box2);
boxH.add(Box.createVerticalGlue());
panel1 = new JPanel();
panel1.add(boxH);
panel2 = new JPanel();
String[] name = {
"借阅者ID", "借阅者","书籍ID", "书名", "借阅日期", "还入日期", "备注"};
String[][] data = new String[0][0];
defaultModel = new DefaultTableModel(data, name);
table = new JTable(defaultModel);
table.setPreferredScrollableViewportSize(new Dimension(600, 120));
JScrollPane s = new JScrollPane(table);
panel2.add(s);
c.add(panel1, BorderLayout.NORTH);
c.add(panel2, BorderLayout.SOUTH);
}
public void actionPerformed(ActionEvent e) {
if (e.getSource() == exitBtn) {
db.closeConnection();
this.dispose();
}
else if (e.getSource() == searchBtn) {
// String strSQL =
// "select studentname,bookname,borrowdate,returndate,com from bookbrowse";
String strSQL =
"select ID,Username,BookID,BookName,borrowdate,returndate,com from Student_Borrow";
String strSql = null;
if (studentNameTextField.getText().trim().equals("") &&
bookNameTextField.getText().trim().equals("")) {
strSql = strSQL;
}
else if (studentNameTextField.getText().trim().equals("")) {
strSql = strSQL + " where BookName='" +
bookNameTextField.getText().trim() + "'";
}
else if (bookNameTextField.getText().trim().equals("")) {
strSql = strSQL + " where Username='" +
studentNameTextField.getText().trim() + "'";
}
else {
strSql = strSQL + " where Username='" +
studentNameTextField.getText().trim() + "'and bookName='" +
bookNameTextField.getText().trim() + "'";
}
try {
//首先要删除table中的数据：
int rowCount = defaultModel.getRowCount() - 1; //取得table中的数据行；
int j = rowCount;
for (int i = 0; i <= rowCount; i++) {
defaultModel.removeRow(j); //删除rowCount行的数据；
defaultModel.setRowCount(j); //重新设置行数；
j = j - 1;
}
rs = db.getResult(strSql);
while (rs.next()) {
Vector data = new Vector();
data.addElement(rs.getString(1));
data.addElement(rs.getString(2));
data.addElement(rs.getString(3));
data.addElement(rs.getString(4));
data.addElement(rs.getString(5));
data.addElement(rs.getString(6));
data.addElement(rs.getString(7));
defaultModel.addRow(data);
}
table.revalidate();
}
catch (SQLException sqle) {
System.out.println(sqle.toString());
}
catch (Exception ex) {
System.out.println(ex.toString());
}
}
}
}
package 图书管理系统;
import java.awt.*;
import java.awt.event.*;
import javax.swing.*;
import java.sql.*;
public class ReturnBook
extends JFrame
implements ActionListener {
DataBaseManager db = new DataBaseManager();
ResultSet rs;
JPanel panel1, panel2;
Container c;
JLabel returnedBookStudentLabel, returnedBookNameLabel,
returnedDateLabel, returnedCommentLabel;
JTextField returnedBookStudentTextField,
returnedDateTextField, returnedCommentTextField;
JButton clearBtn, yesBtn, cancelBtn;
JComboBox bookNameComboBox = new JComboBox();
public ReturnBook() {
super("书籍还入");
c = getContentPane();
c.setLayout(new BorderLayout());
returnedBookStudentLabel = new JLabel("还书者姓名", JLabel.CENTER);
returnedBookNameLabel = new JLabel("书名", JLabel.CENTER);
returnedDateLabel = new JLabel("日期", JLabel.CENTER);
returnedCommentLabel = new JLabel("备注", JLabel.CENTER);
returnedBookStudentTextField = new JTextField(15);
returnedDateTextField = new JTextField(15);
returnedCommentTextField = new JTextField(15);
try {
String s = "";
String strSQL = "select bookName from bookBrowse where is_returned='否'";
rs = db.getResult(strSQL);
while (rs.next()) {
bookNameComboBox.addItem(rs.getString(1));
}
}
catch (SQLException sqle) {
System.out.println(sqle.toString());
}
catch (Exception ex) {
System.out.println(ex.toString());
}
panel1 = new JPanel();
panel1.setLayout(new GridLayout(4, 2));
panel1.add(returnedBookStudentLabel);
panel1.add(returnedBookStudentTextField);
panel1.add(returnedBookNameLabel);
panel1.add(bookNameComboBox);
panel1.add(returnedDateLabel);
panel1.add(returnedDateTextField);
panel1.add(returnedCommentLabel);
panel1.add(returnedCommentTextField);
c.add(panel1, BorderLayout.CENTER);
panel2 = new JPanel();
panel2.setLayout(new GridLayout(1, 3));
clearBtn = new JButton("清空");
yesBtn = new JButton("确定");
cancelBtn = new JButton("取消");
clearBtn.addActionListener(this);
yesBtn.addActionListener(this);
cancelBtn.addActionListener(this);
panel2.add(clearBtn);
panel2.add(yesBtn);
panel2.add(cancelBtn);
c.add(panel2, BorderLayout.SOUTH);
}
public void actionPerformed(ActionEvent e) {
if (e.getSource() == cancelBtn) {
this.dispose();
}
else if (e.getSource() == clearBtn) {
returnedBookStudentTextField.setText("");
returnedDateTextField.setText("");
returnedCommentTextField.setText("");
}
else if (e.getSource() == yesBtn) {
if (returnedBookStudentTextField.getText().trim().equals("")) {
JOptionPane.showMessageDialog(null, "请输入还书者的姓名。。。");
}
else if (bookNameComboBox.getSelectedItem().equals("")) {
JOptionPane.showMessageDialog(null, "图书馆没有出借过书！");
}
else {
try {
String strSQL = "update bookBrowse set returnDate='" +
returnedDateTextField.getText().trim() + "',com='" +
returnedCommentTextField.getText().trim() +
"',is_returned='是' where studentName='" +
returnedBookStudentTextField.getText().trim() + "'and bookName='" +
bookNameComboBox.getSelectedItem() + "'";
rs = db.getResult(strSQL);
JOptionPane.showMessageDialog(null, "还书完成！");
strSQL =
"update books set borrowed_count=borrowed_count-1 where bookname='" +
bookNameComboBox.getSelectedItem() + "'";
db.getResult(strSQL);
db.closeConnection();
this.dispose();
}
catch (Exception ex) {
System.out.println(ex.toString());
}
}
}
}
}
3.主界面设计代码：
package 图书借阅管理系统;
import java.awt.*;
import java.awt.event.*;
import javax.swing.*;
public class MainFrame
extends JFrame
implements ActionListener {
private static final long serialVersionUID = 1L;
JPanel panel1;
Container c;
JMenuBar menuB;
JMenu systemMenu, bookMGRMenu, borrowBookMenu, returnBookMenu,
infoBrowseMenu, userMGRMenu,aboutMenu;
JMenuItem adminLoginMenuItem, userLoginMenuItem, userAddMenuItem, userModifyMenuItem,
userDeleteMenuItem, exitMenuItem, bookAddMenuItem, bookModifyMenuItem,
bookDeleteMenuItem,
borrowBookMenuItem, borrowInfoMenuItem, returnBookMenuItem,
returnInfoMenuItem,
bookListMenuItem, borrowBookListMenuItem, userListMenuItem,
aboutMenuItem;
public MainFrame() {
super("图书馆管理系统");
//--系统管理菜单--
menuB = new JMenuBar();
systemMenu = new JMenu("系统管理");
userMGRMenu = new JMenu("用户管理");
adminLoginMenuItem = new JMenuItem("管理员登录");
userLoginMenuItem = new JMenuItem("用户登录");
userAddMenuItem = new JMenuItem("添加用户");
userModifyMenuItem = new JMenuItem("修改密码");
userDeleteMenuItem = new JMenuItem("删除用户");
exitMenuItem = new JMenuItem("退出");
systemMenu.add(adminLoginMenuItem);
systemMenu.add(userLoginMenuItem);
userMGRMenu.add(userAddMenuItem);
userMGRMenu.add(userModifyMenuItem);
userMGRMenu.add(userDeleteMenuItem);
systemMenu.add(userMGRMenu);
systemMenu.add(exitMenuItem);
adminLoginMenuItem.addActionListener(this);
userLoginMenuItem.addActionListener(this);
userAddMenuItem.addActionListener(this);
userModifyMenuItem.addActionListener(this);
userDeleteMenuItem.addActionListener(this);
exitMenuItem.addActionListener(this);
menuB.add(systemMenu);
//---书籍管理菜单--
bookMGRMenu = new JMenu("书籍管理");
bookAddMenuItem = new JMenuItem("添加书籍");
bookModifyMenuItem = new JMenuItem("修改书籍");
bookDeleteMenuItem = new JMenuItem("删除书籍");
bookMGRMenu.add(bookAddMenuItem);
bookMGRMenu.add(bookModifyMenuItem);
bookMGRMenu.add(bookDeleteMenuItem);
bookAddMenuItem.addActionListener(this);
bookModifyMenuItem.addActionListener(this);
bookDeleteMenuItem.addActionListener(this);
menuB.add(bookMGRMenu);
//--借书管理菜单--
borrowBookMenu = new JMenu("借书管理");
borrowBookMenuItem = new JMenuItem("书籍出借");
borrowInfoMenuItem = new JMenuItem("出借信息修改");
borrowBookMenu.add(borrowBookMenuItem);
borrowBookMenu.add(borrowInfoMenuItem);
borrowBookMenuItem.addActionListener(this);
borrowInfoMenuItem.addActionListener(this);
menuB.add(borrowBookMenu);
//--还书管理菜单--
returnBookMenu = new JMenu("还书管理");
returnBookMenuItem = new JMenuItem("书籍还入");
returnInfoMenuItem = new JMenuItem("书籍还入信息修改");
returnBookMenu.add(returnBookMenuItem);
returnBookMenu.add(returnInfoMenuItem);
returnBookMenuItem.addActionListener(this);
returnInfoMenuItem.addActionListener(this);
menuB.add(returnBookMenu);
//--信息一览菜单--
infoBrowseMenu = new JMenu("信息查询");
bookListMenuItem = new JMenuItem("书籍信息查询");
borrowBookListMenuItem = new JMenuItem("借阅信息查询");
userListMenuItem = new JMenuItem("用户列表");
infoBrowseMenu.add(bookListMenuItem);
infoBrowseMenu.add(borrowBookListMenuItem);
infoBrowseMenu.add(userListMenuItem);
bookListMenuItem.addActionListener(this);
borrowBookListMenuItem.addActionListener(this);
userListMenuItem.addActionListener(this);
menuB.add(infoBrowseMenu);
//--关于菜单--
aboutMenu=new JMenu("帮助");
aboutMenuItem = new JMenuItem("关于");
aboutMenuItem.addActionListener(this);
aboutMenu.add(aboutMenuItem);
menuB.add(aboutMenu);
//----------------------------------
setJMenuBar(menuB);
c = getContentPane();
c.setLayout(new BorderLayout());
panel1 = new JPanel();
panel1.setLayout(new BorderLayout());
c.add(panel1, BorderLayout.CENTER);
//--设置初始功能:--
userMGRMenu.setEnabled(false);
bookMGRMenu.setEnabled(false);
borrowBookMenu.setEnabled(false);
returnBookMenu.setEnabled(false);
infoBrowseMenu.setEnabled(false);
aboutMenu.setEnabled(false);
}
//--设置每个菜单点击后出现的窗口和窗口显示的位置--
public void actionPerformed(ActionEvent e) {
if (e.getActionCommand() == "管理员登录") {
AdminLogin AdminLoginFrame = new AdminLogin(this);//创建用例类实例对象
Dimension FrameSize = AdminLoginFrame.getPreferredSize();//子窗口大小
Dimension MainFrameSize = getSize();//主窗口大小
Point loc = getLocation();//主窗口位置
AdminLoginFrame.setLocation( (MainFrameSize.width - FrameSize.width) / 2 +
loc.x,
(MainFrameSize.height - FrameSize.height) / 2 +
loc.y);//设置子窗口的位置位于主窗口的中央
AdminLoginFrame.pack();
AdminLoginFrame.show();
}
if (e.getActionCommand() == "用户登录") {
UserLogin UserLoginFrame = new UserLogin(this);//创建用例类实例对象
Dimension FrameSize = UserLoginFrame.getPreferredSize();//子窗口大小
Dimension MainFrameSize = getSize();//主窗口大小
Point loc = getLocation();//主窗口位置
UserLoginFrame.setLocation( (MainFrameSize.width - FrameSize.width) / 2 +
loc.x,
(MainFrameSize.height - FrameSize.height) / 2 +
loc.y);//设置子窗口的位置位于主窗口的中央
UserLoginFrame.pack();
UserLoginFrame.show();
}
else if (e.getActionCommand() == "添加用户") {
UserAdd UserAddFrame = new UserAdd();
Dimension FrameSize = UserAddFrame.getPreferredSize();
Dimension MainFrameSize = getSize();
Point loc = getLocation();
UserAddFrame.setLocation( (MainFrameSize.width - FrameSize.width) / 2 +
loc.x,
(MainFrameSize.height - FrameSize.height) / 2 +
loc.y);
UserAddFrame.pack();
UserAddFrame.show();
}
else if (e.getActionCommand() == "修改密码") {
UserModify UserModifyFrame = new UserModify();
Dimension FrameSize = UserModifyFrame.getPreferredSize();
Dimension MainFrameSize = getSize();
Point loc = getLocation();
UserModifyFrame.setLocation( (MainFrameSize.width - FrameSize.width) / 2 +
loc.x,
(MainFrameSize.height - FrameSize.height) / 2 +
loc.y);
UserModifyFrame.pack();
UserModifyFrame.show();
}
else if (e.getActionCommand() == "删除用户") {
UserDelete UserDeleteFrame = new UserDelete();
Dimension FrameSize = UserDeleteFrame.getPreferredSize();
Dimension MainFrameSize = getSize();
Point loc = getLocation();
UserDeleteFrame.setLocation( (MainFrameSize.width - FrameSize.width) / 2 +
loc.x,
(MainFrameSize.height - FrameSize.height) / 2 +
loc.y);
UserDeleteFrame.pack();
UserDeleteFrame.show();
}
else if (e.getActionCommand() == "添加书籍") {
BookAdd BookAddFrame = new BookAdd();
Dimension FrameSize = BookAddFrame.getPreferredSize();
Dimension MainFrameSize = getSize();
Point loc = getLocation();
BookAddFrame.setLocation( (MainFrameSize.width - FrameSize.width) / 2 +
loc.x,
(MainFrameSize.height - FrameSize.height) / 2 +
loc.y);
BookAddFrame.pack();
BookAddFrame.show();
}
else if (e.getActionCommand() == "修改书籍") {
BookModify BookModifyFrame = new BookModify();
Dimension FrameSize = BookModifyFrame.getPreferredSize();
Dimension MainFrameSize = getSize();
Point loc = getLocation();
BookModifyFrame.setLocation( (MainFrameSize.width - FrameSize.width) / 2 +
loc.x,
(MainFrameSize.height - FrameSize.height) / 2 +
loc.y);
BookModifyFrame.pack();
BookModifyFrame.show();
}
else if (e.getActionCommand() == "删除书籍") {
BookDelete BookDeleteFrame = new BookDelete();
Dimension FrameSize = BookDeleteFrame.getPreferredSize();
Dimension MainFrameSize = getSize();
Point loc = getLocation();
BookDeleteFrame.setLocation( (MainFrameSize.width - FrameSize.width) / 2 +
loc.x,
(MainFrameSize.height - FrameSize.height) / 2 +
loc.y);
BookDeleteFrame.pack();
BookDeleteFrame.show();
}
else if (e.getActionCommand() == "书籍出借") {
BorrowBook BorrowBookFrame = new BorrowBook();
Dimension FrameSize = BorrowBookFrame.getPreferredSize();
Dimension MainFrameSize = getSize();
Point loc = getLocation();
BorrowBookFrame.setLocation( (MainFrameSize.width - FrameSize.width) / 2 +
loc.x,
(MainFrameSize.height - FrameSize.height) / 2 +
loc.y);
BorrowBookFrame.pack();
BorrowBookFrame.show();
}
else if (e.getActionCommand() == "出借信息修改") {
BorrowInfo BorrowInfoFrame = new BorrowInfo();
Dimension FrameSize = BorrowInfoFrame.getPreferredSize();
Dimension MainFrameSize = getSize();
Point loc = getLocation();
BorrowInfoFrame.setLocation( (MainFrameSize.width - FrameSize.width) / 2 +
loc.x,
(MainFrameSize.height - FrameSize.height) / 2 +
loc.y);
BorrowInfoFrame.pack();
BorrowInfoFrame.show();
}
else if (e.getActionCommand() == "书籍还入") {
ReturnBook ReturnBookFrame = new ReturnBook();
Dimension FrameSize = ReturnBookFrame.getPreferredSize();
Dimension MainFrameSize = getSize();
Point loc = getLocation();
ReturnBookFrame.setLocation( (MainFrameSize.width - FrameSize.width) / 2 +
loc.x,
(MainFrameSize.height - FrameSize.height) / 2 +
loc.y);
ReturnBookFrame.pack();
ReturnBookFrame.show();
}
else if (e.getActionCommand() == "书籍还入信息修改") {
ReturnInfo ReturnInfoFrame = new ReturnInfo();
Dimension FrameSize = ReturnInfoFrame.getPreferredSize();
Dimension MainFrameSize = getSize();
Point loc = getLocation();
ReturnInfoFrame.setLocation( (MainFrameSize.width - FrameSize.width) / 2 +
loc.x,
(MainFrameSize.height - FrameSize.height) / 2 +
loc.y);
ReturnInfoFrame.pack();
ReturnInfoFrame.show();
}
else if (e.getActionCommand() == "书籍信息查询") {
BookList BookListFrame = new BookList();
Dimension FrameSize = BookListFrame.getPreferredSize();
Dimension MainFrameSize = getSize();
Point loc = getLocation();
BookListFrame.setLocation( (MainFrameSize.width - FrameSize.width) / 2 +
loc.x,
(MainFrameSize.height - FrameSize.height) / 2 +
loc.y);
BookListFrame.pack();
BookListFrame.show();
}
else if (e.getActionCommand() == "借阅信息查询") {
BorrowBookList BorrowBookListFrame = new BorrowBookList();
Dimension FrameSize = BorrowBookListFrame.getPreferredSize();
Dimension MainFrameSize = getSize();
Point loc = getLocation();
BorrowBookListFrame.setLocation( (MainFrameSize.width - FrameSize.width) /
2 + loc.x,
(MainFrameSize.height - FrameSize.height) /
2 + loc.y);
BorrowBookListFrame.pack();
BorrowBookListFrame.show();
}
else if (e.getActionCommand() == "用户列表") {
UserList UserListFrame = new UserList();
Dimension FrameSize = UserListFrame.getPreferredSize();
Dimension MainFrameSize = getSize();
Point loc = getLocation();
UserListFrame.setLocation( (MainFrameSize.width - FrameSize.width) / 2 +
loc.x,
(MainFrameSize.height - FrameSize.height) / 2 +
loc.y);
UserListFrame.pack();
UserListFrame.show();
}
else if (e.getActionCommand() == "退出") {
this.dispose();
System.exit(0);
}
}
//--设置登录用户的权限--
public void setEnable(String powerType) {
if (powerType.trim().equals("系统管理员")) {
userMGRMenu.setEnabled(true);
bookMGRMenu.setEnabled(true);
borrowBookMenu.setEnabled(true);
returnBookMenu.setEnabled(true);
infoBrowseMenu.setEnabled(true);
userListMenuItem.setEnabled(true);
aboutMenu.setEnabled(true);
}
else if (powerType.trim().equals("书籍管理员")) {
userMGRMenu.setEnabled(false);
bookMGRMenu.setEnabled(true);
borrowBookMenu.setEnabled(false);
returnBookMenu.setEnabled(false);
infoBrowseMenu.setEnabled(true);
userListMenuItem.setEnabled(false);
aboutMenu.setEnabled(true);
}
else if (powerType.trim().equals("学生")) {
userMGRMenu.setEnabled(false);
bookMGRMenu.setEnabled(false);
borrowBookMenu.setEnabled(true);
returnBookMenu.setEnabled(true);
infoBrowseMenu.setEnabled(true);
userListMenuItem.setEnabled(false);
aboutMenu.setEnabled(true);
}
else if (powerType.trim().equals("else")) {
userMGRMenu.setEnabled(false);
bookMGRMenu.setEnabled(false);
borrowBookMenu.setEnabled(false);
returnBookMenu.setEnabled(false);
infoBrowseMenu.setEnabled(false);
aboutMenu.setEnabled(false);
}
}
public static void main(String args[]) {
try {
UIManager.setLookAndFeel(UIManager.getSystemLookAndFeelClassName());
}
catch (Exception e) {
e.printStackTrace();
}
MainFrame mainFrame = new MainFrame();
mainFrame.setSize(500,400);
Dimension d = Toolkit.getDefaultToolkit().getScreenSize();
mainFrame.setLocation( (d.width - mainFrame.getSize().width) / 2,
(d.height - mainFrame.getSize().height) / 2);
mainFrame.show();
}
}
- 登录
package 图书管理系统;
import java.awt.*;
import java.awt.event.*;
import javax.swing.*;
import java.sql.*;
public class AdminLogin
extends JFrame
implements ActionListener {
MainFrame mainFrame;
JPanel panel1, panel2;
JLabel userLabel, passwordLabel;
JTextField userTextField;
JPasswordField passwordTextField;
JButton yesBtn, cancelBtn;
Container c;
ResultSet rs;
static String username,passwordSTR;
static Statement stmt;
public AdminLogin(MainFrame mainFrame) {
super("管理员登录");
this.mainFrame = mainFrame;
userLabel = new JLabel("用户名", JLabel.CENTER);
passwordLabel = new JLabel("密码", JLabel.CENTER);
userTextField = new JTextField(20);
passwordTextField = new JPasswordField(20);
yesBtn = new JButton("确定");
cancelBtn = new JButton("取消");
yesBtn.addActionListener(this);
cancelBtn.addActionListener(this);
panel1 = new JPanel();
panel1.setLayout(new GridLayout(2, 2));
panel2 = new JPanel();
c = getContentPane();
c.setLayout(new BorderLayout());
panel1.add(userLabel);
panel1.add(userTextField);
panel1.add(passwordLabel);
panel1.add(passwordTextField);
c.add(panel1, BorderLayout.CENTER);
panel2.add(yesBtn);
panel2.add(cancelBtn);
c.add(panel2, BorderLayout.SOUTH);
setSize(400, 400);
}
public void actionPerformed(ActionEvent e) {
if (e.getSource() == cancelBtn) {
mainFrame.setEnable("else");
this.dispose();
}
else {
DataBaseManager1();
char[] password = passwordTextField.getPassword();
username = userTextField.getText().trim();
passwordSTR = new String(password);
if (userTextField.getText().trim().equals("")) {
JOptionPane.showMessageDialog(null, "用户名不可为空!");
return;
}
if (passwordSTR.equals("")) {
JOptionPane.showMessageDialog(null, "密码不可为空!");
return;
}
JOptionPane.showMessageDialog(null, "登陆成功");
mainFrame.setEnable("系统管理员");
this.dispose();
}
}
public static void DataBaseManager1() {
try {
String url = "jdbc:sqlserver://127.0.0.1:1433;DatabaseName=图书管理系统";
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection con=DriverManager.getConnection(url,username,passwordSTR);//连接数据库对象
stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
}
catch (SQLException sqle) {
System.out.println(sqle.toString());
}
catch (ClassNotFoundException cnfex) {
cnfex.printStackTrace();
}
}
}
package 图书管理系统;
import java.sql.*;
public class DataBaseManager {
Connection con = null;
ResultSet rs = null;
Statement stmt;
public DataBaseManager() {
try {
String url = "jdbc:sqlserver://127.0.0.1:1433;DatabaseName=图书管理系统";
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
String user="DH";
String password="123456";
Connection con=DriverManager.getConnection(url,user,password);//连接数据库对象
stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
}
catch (SQLException sqle) {
System.out.println(sqle.toString());
}
catch (ClassNotFoundException cnfex) {
cnfex.printStackTrace();
}
}
public ResultSet getResult(String strSQL) {
try {
rs = stmt.executeQuery(strSQL);
return rs;
}
catch (SQLException sqle) {
System.out.println(sqle.toString());
return null;
}
}
public int updateSql(String strSQL) {
try {
int i = stmt.executeUpdate(strSQL);
con.commit();
return i;
}
catch (SQLException sqle) {
System.out.println(sqle.toString());
return -1;
}
}
public void closeConnection() {
try {
con.close();
}
catch (SQLException sqle) {
System.out.println(sqle.toString());
}
}
}
五、 项目总结
这个项目作业是对这个学期所的进行的一个检测，从项目的分析设计再到具体实现，我一步步完善漏缺，一步步改进代码，这不仅仅对这个项目作业进行了查漏补缺，更加对我自己所掌握的不足进行了查漏补缺。项目作业的主要难点我认为主要有以下几个方面：一个是触发器考虑是否周全，比如对书籍的删除，你得在这里加上一个触发器以防还有学生未进行还书；一个是表的设计，这个不仅难，还很重要，稍微有点错，就得重新设计，写好的代码也要全部重改，我就经历了一两次，每次都感觉自己考虑的很全面但是写到后面就突然发现这里那里的有点问题，一改动便是大改；最后还有一个便是与Java连接，光是折腾这个连接变花了我很多时间，碰到了很多问题，比如：“java：驱动程序无法通过使用安全套接字层(SSL)加密与 SQL Server 建立安全连接:”，按照百度教程试了很多方法都没用，有个文件始终说不存在，最后还是重装Java jdk和eclipse才解决，另外便是对Javafx使用不熟练，导致界面设计不好，因为和别人设计的表不同所以虽然参照着别人的Java代码来设计但是改动起来还是很困难。
浙公网安备 33010602011771号