毓秀Java实验六（下）：数据库操作强化—图书管理系统
6月1日更新
文章存在问题，内容与题目对不上，原程序找不到，没时间修改。抱歉。
实验目的
强化数据库开发的基本操作
实验内容
实现一个图书管理借阅系统
实验要求
1、管理员操作：能够实现图书的增、删、改、查操作
2、普通注册用户操作：
借阅图书（借阅成功后，对应图书在馆数量相应减1）
归还图书（归还成功后，对应图书在馆数量加1）
查阅自己借阅的图书
续借图书（借阅图书以3个月为限，可以在期间内续借1个月）
如果预借图书在馆数量为零，或者图书不存在，则无法借阅
可以为所借阅的图书进行评论
3、自行设计数据库表
实验代码
package experiment_six;
import java.awt.*;
import java.awt.event.*;
import javax.swing.*;
import javax.swing.event.*;
import javax.swing.table.DefaultTableModel;
import java.sql.*;
class LoginFrame extends Frame {
private static String DBDRIVER = "com.mysql.cj.jdbc.Driver" ;
protected static String DBURL = "jdbc:mysql://数据库地址:3306/bookmanagesys? usessl=false" ;
protected static String DBUSER = "BookManageSYS";
protected static String DBPASS = "密码" ;
private static final long serialVersionUID = 1L;
private JFrame jFrame = new JFrame("登录窗口");
private Container c = jFrame.getContentPane();
private JLabel a1 = new JLabel("用户名");
// 账号
private JTextField username = new JTextField();
private JLabel a2 = new JLabel("密 码");
// 密码
private JPasswordField password = new JPasswordField();
private JRadioButton reader = new JRadioButton("读者");
private JRadioButton manager = new JRadioButton("管理员");
private ButtonGroup group = new ButtonGroup();
private JButton okbtn = new JButton("登录");
private JButton regbtn = new JButton("注册");
public LoginFrame() {
try {
Class.forName(DBDRIVER);
} catch (ClassNotFoundException e2) {
// TODO Auto-generated catch block
System.out.println("加载驱动失败");
//e2.printStackTrace();
}
// 设置窗体的位置及大小
jFrame.setBounds(600, 200, 300, 220);
// 设置一层相当于桌布的东西
c.setLayout(new BorderLayout());// 布局管理器
// 设置按下右上角X号后关闭
jFrame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
// 初始化--往窗体里放其他控件
jFrame.setLocationRelativeTo(null);// 窗
// 标题部分
JPanel titlePanel = new JPanel();
titlePanel.setLayout(new FlowLayout());
titlePanel.add(new JLabel("通讯录系统"));
c.add(titlePanel, "North");
// 输入部分-
JPanel fieldPanel = new JPanel();
fieldPanel.setLayout(null);
a1.setBounds(50, 20, 50, 20);
a2.setBounds(50, 60, 50, 20);
fieldPanel.add(a1);
fieldPanel.add(a2);
username.setBounds(110, 20, 120, 20);
password.setBounds(110, 60, 120, 20);
fieldPanel.add(username);
fieldPanel.add(password);
//用户身份
reader.setBounds(70, 100, 60, 20);
manager.setBounds(130, 100, 100, 20);
group.add(reader);
group.add(manager);
fieldPanel.add(reader);
fieldPanel.add(manager);
c.add(fieldPanel, "Center");
// 按钮部分
JPanel buttonPanel = new JPanel();
buttonPanel.setLayout(new FlowLayout());
buttonPanel.add(okbtn);
buttonPanel.add(regbtn);
c.add(buttonPanel, "South");
// 设置窗体可见
jFrame.setVisible(true);
//注册按钮
regbtn.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
String name = username.getText();
String pwd = String.valueOf(password.getPassword());
String regist;
try {
Connection con=DriverManager.getConnection(DBURL,DBUSER,DBPASS);
if(reader.isSelected()) {
regist = "insert into reader(account,password) values(?,?)";
}
else if(manager.isSelected()){
regist = "insert into manager(account,password) values(?,?)";
}
else
return;
PreparedStatement sql = con.prepareStatement(regist);
sql.setString(1, name);
sql.setString(2, pwd);
//int i = sql.executeUpdate();
if(sql.executeUpdate() > 0)
JOptionPane.showMessageDialog(null, "注册成功");
con.close();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
System.out.println("注册失败");
}
}
});
// 登陆按下去获取
okbtn.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent e) {
String name = username.getText();
String pwd = String.valueOf(password.getPassword());
String login;
boolean flag = false;
try {
Connection con = DriverManager.getConnection(DBURL,DBUSER,DBPASS);
if(reader.isSelected()) {
login = "SELECT account,password FROM reader where account = ? and password = ?";
}
else if(manager.isSelected()) {
login = "SELECT account,password FROM manager where account = ? and password = ?";
}
else
return;
PreparedStatement sql = con.prepareStatement(login);
sql.setString(1, name);
sql.setString(2, pwd);
ResultSet rs = sql.executeQuery();
while (rs.next()) {
if(rs.getString("account").equals(name) && rs.getString("password").equals(pwd))
flag = true;
}
if (flag && manager.isSelected()) {
jFrame.setVisible(false);
JOptionPane.showMessageDialog(null, "管理员,登录成功!");
MainMenuFrame m = new MainMenuFrame();
}
else if(flag && reader.isSelected()){
jFrame.setVisible(false);
JOptionPane.showMessageDialog(null, "读者,登录成功!");
UserPanel userPanel = new UserPanel();
}
else {
JOptionPane.showMessageDialog(null, "登录失败，账号或密码错误");
}
con.close();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
username.setText("");
password.setText("");
JOptionPane.showMessageDialog(null, "登录失败");
}
}
});
}
}
class MainMenuFrame extends JFrame implements ActionListener{
/*
* 登陆成功后主界面
*/
private JFrame mainmenu;
//private JTextArea text;
private JMenuBar menuBar1;
public MainMenuFrame() {
mainmenu();
}
public void mainmenu() {
mainmenu = new JFrame("通讯录系统");
setLocationRelativeTo(null);// 将容器显示在屏幕中央
mainmenu.setSize(750, 650);
//mainmenu.getContentPane().add(new JScrollPane(text));
JPanel jContentPane = new JPanel();
jContentPane.setLayout(null);
// 添加学生按钮
JButton jButtonAdd = new JButton();
jButtonAdd.setBounds(new Rectangle(320, 100, 100, 52));
jButtonAdd.setText("添加学生");
// 查询学生按钮
JButton jButtonSelect = new JButton();
jButtonSelect.setBounds(new Rectangle(320, 200, 100, 52));
jButtonSelect.setText("查询学生");
//删除学生按钮
JButton jButtonDelete = new JButton();
jButtonDelete.setBounds(new Rectangle(320, 300, 100, 52));
jButtonDelete.setText("删除学生");
// 退出系统按钮
JButton jButtonExit = new JButton();
jButtonExit.setBounds(new Rectangle(320, 400, 100, 52));
jButtonExit.setText("退出系统");
jContentPane.add(jButtonAdd, null);
jContentPane.add(jButtonSelect, null);
jContentPane.add(jButtonDelete, null);
jContentPane.add(jButtonExit, null);
// 添加Label到Frame
mainmenu.getContentPane().add(jContentPane);
// 标题栏
menuBar1 = new JMenuBar();
// 添加学生
jButtonAdd.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent e) {
AddStudent addStudent = new AddStudent();
System.out.println("------添加学生-----");
}
});
// 查询学生
jButtonSelect.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent e) {
SelectStudent selectStudent = new SelectStudent();
System.out.println("------查询学生-----");
}
});
//删除学生
jButtonDelete.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent e) {
// TODO Auto-generated method stub
DeleteStudent deleteStudent = new DeleteStudent();
}
});
// 退出系统
jButtonExit.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent e) {
System.out.println("------退出系统-----");
System.exit(1);
}
});
// 关闭窗口监控
mainmenu.addWindowListener(new WindowAdapter() {
public void windowClosing(WindowEvent arg0) {
System.exit(1);
}
});
mainmenu.setJMenuBar(menuBar1);
mainmenu.setVisible(true);
mainmenu.setLocation(250, 50);
}
@Override
public void actionPerformed(ActionEvent arg0) {
}
}
class AddStudent extends JFrame{
JTextField text,text2,text3;
JButton button;
JCheckBox checkBox1,checkBox2,checkBox3;
JRadioButton radio1,radio2;
ButtonGroup group;
JComboBox<String> comBox;
//JTextArea area;
public AddStudent(){
init();
setVisible(true);
setDefaultCloseOperation(JFrame.DISPOSE_ON_CLOSE);
setBounds(100,100,400,240);
setTitle("添加页面");
}
void init() {
// TODO Auto-generated method stub
setLayout(new FlowLayout());
JLabel label = new JLabel("姓名：");
add(label);
text = new JTextField(10);
add(text);
JLabel label1 = new JLabel("电话：");
add(label1);
text3 = new JTextField(10);
add(text3);
radio1 = new JRadioButton("男");//单选按钮
radio2 = new JRadioButton("女");
group = new ButtonGroup();
group.add(radio1);
group.add(radio2);
add(radio1);
add(radio2);
checkBox1 = new JCheckBox("喜欢音乐");
checkBox2 = new JCheckBox("喜欢旅游");
checkBox3 = new JCheckBox("喜欢篮球");
add(checkBox1);
add(checkBox2);
add(checkBox3);
comBox = new JComboBox();//下拉列表
comBox.addItem("居住城市");
comBox.addItem("北京");
comBox.addItem("上海");
comBox.addItem("成都");
comBox.addItem("长春");
add(comBox);
JSlider slider = new JSlider(5, 25, 18);
slider.setMajorTickSpacing(5);
slider.setMinorTickSpacing(1);
slider.setPaintTicks(true);
slider.setPaintLabels(true);
add(slider);
slider.addChangeListener(new ChangeListener() {
@Override
public void stateChanged(ChangeEvent e) {
text2.setText(Integer.toString(slider.getValue()));
}
});
text2 = new JTextField("年龄",4);
add(text2);
text2.setEditable(false);
//area = new JTextArea(6,12);
//add(new JScrollPane(area));
button = new JButton("确定");
add(button);
button.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent e) {
String[] allof = new String[6];
String name = text.getText();
allof[0] = name;
String phone = text3.getText();
allof[5] = phone;//后添加
String city = comBox.getSelectedItem().toString();
allof[1] = city;
String sex = null;
if (radio1.isSelected())
sex = radio1.getText();
else
sex = radio2.getText();
allof[2] = sex;
String like ="";
if(checkBox1.isSelected())
like = checkBox1.getText();
if(checkBox2.isSelected())
like = checkBox2.getText()+" "+like;
if(checkBox3.isSelected())
like = checkBox3.getText()+" "+like;
allof[3] = like;
String age = Integer.toString(slider.getValue());
allof[4] = age;
for(int i = 0;i<=5;i++){
System.out.println(allof[i]);
}
try {
Connection con = DriverManager.getConnection(LoginFrame.DBURL,LoginFrame.DBUSER,LoginFrame.DBPASS);
//Statement stmt = con.createStatement();
String addstudent = "insert into communication(name,sex,age,phone,city,favorite) values (?,?,?,?,?,?)";
PreparedStatement sql = con.prepareStatement(addstudent);
sql.setString(1, name);
sql.setString(2, sex);
sql.setString(3, age);
sql.setString(4, phone);
sql.setString(5, city);
sql.setString(6, like);
//int i = sql.executeUpdate();
if(sql.executeUpdate() > 0)
JOptionPane.showMessageDialog(null, "添加成功");
sql.close();
con.close();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
JOptionPane.showMessageDialog(null, "添加失败");
}
System.out.println("------添加学生-----");
}
});
}
}
class SelectStudent extends JFrame implements ActionListener{
JTextField text;
DefaultTableModel model= new DefaultTableModel();
JButton button;
public SelectStudent() {
setTitle("查询窗口");
setVisible(true);
setBounds(100,100,460,260);
setLayout(new FlowLayout());
JLabel label = new JLabel("姓名:");
add(label);
text = new JTextField(10);
add(text);
button = new JButton("确认");
add(button);
JButton button1 = new JButton("查询所有");
add(button1);
button.addActionListener(this);
button1.addActionListener(this);
//model = new DefaultTableModel();
JTable table = new JTable(model);
add(new JScrollPane(table));
}
public void actionPerformed(ActionEvent e) {
try {
String name = text.getText();
String sql = null;
Connection con = DriverManager.getConnection(LoginFrame.DBURL,LoginFrame.DBUSER,LoginFrame.DBPASS);
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
if(e.getSource()== button) {
sql = "select * from communication where name ='"+name+"'";
}else {
sql = "select * from communication";
}
ResultSet rs = stmt.executeQuery(sql);
ResultSetMetaData metaData = rs.getMetaData();
int columnCount = metaData.getColumnCount();
String[] columnName = new String[columnCount];
for(int i = 1;i<=columnCount;i++) {
columnName[i-1] = metaData.getColumnName(i);
}
rs.last();
int recordAmount = rs.getRow();
String[][] record = new String[recordAmount][columnCount];
int i = 0;
rs.beforeFirst();
while(rs.next()) {
for(int j = 1;j<=columnCount;j++) {
record[i][j-1] = rs.getString(j);
}
i++;
}
if(recordAmount>0) {
JOptionPane.showMessageDialog(null, "查询成功");
}else
JOptionPane.showMessageDialog(null, "没有此联系人");
model.setDataVector(record, columnName);
stmt.close();
con.close();
//JTable table = new JTable(record,columnName);
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
JOptionPane.showMessageDialog(null, "查询失败");
}
}
}
class DeleteStudent extends JFrame{
public DeleteStudent(){
//JFrame DeleteWin = new JFrame("删除窗口");
setTitle("删除窗口");
setVisible(true);
setBounds(100,100,300,260);
setLayout(new FlowLayout());
JLabel label = new JLabel("姓名:");
add(label);
JTextField text = new JTextField(10);
add(text);
JButton button = new JButton("确认");
add(button);
button.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e){
String name = text.getText();
Connection con;
try {
con = DriverManager.getConnection(LoginFrame.DBURL,LoginFrame.DBUSER,LoginFrame.DBPASS);
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
String sql = "delete from communication where name = '" + name + "'";
int rs = stmt.executeUpdate(sql);
if(rs > 0)
JOptionPane.showMessageDialog(null, "删除成功");
else
JOptionPane.showMessageDialog(null, "删除失败");
setVisible(false);
stmt.close();
con.close();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}
});
}
}
public class project6 {
public static void main(String[] args) {
LoginFrame loginFrame = new LoginFrame();
}
}数据库表结构
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for book
-- ----------------------------
DROP TABLE IF EXISTS `book`;
CREATE TABLE `book` (
`户型` varchar(30) NOT NULL COMMENT 'BookName',
`月租` varchar(20) NOT NULL COMMENT 'writer',
`数量` int(11) NOT NULL DEFAULT '0' COMMENT 'Count',
PRIMARY KEY (`户型`) USING BTREE,
KEY `BookId` (`户型`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Table structure for borrow
-- ----------------------------
DROP TABLE IF EXISTS `borrow`;
CREATE TABLE `borrow` (
`BorrowId` int(20) NOT NULL AUTO_INCREMENT,
`reader` varchar(50) NOT NULL,
`BookId` varchar(20) NOT NULL,
`BookName` varchar(50) NOT NULL,
`IsReturn` varchar(10) NOT NULL,
PRIMARY KEY (`BorrowId`),
KEY `userid` (`reader`),
KEY `BookId` (`BookId`,`BookName`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Table structure for manager
-- ----------------------------
DROP TABLE IF EXISTS `manager`;
CREATE TABLE `manager` (
`account` varchar(20) NOT NULL COMMENT '用户名',
`password` varchar(20) NOT NULL COMMENT '密码',
PRIMARY KEY (`account`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Table structure for reader
-- ----------------------------
DROP TABLE IF EXISTS `reader`;
CREATE TABLE `reader` (
`account` varchar(20) NOT NULL COMMENT '用户名',
`password` varchar(20) NOT NULL COMMENT '密码',
PRIMARY KEY (`account`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
SET FOREIGN_KEY_CHECKS = 1;1.腾龙梦屋文章内容无特殊注明皆为源儿原创，转载请注明来源，谢谢！
2.若有相关文章侵犯您的权益，请联系源儿删除，谢谢！
3.相关软件、资料仅供学习参考使用，在24h内务必删除！
腾龙梦屋 » Java实验六（下）：数据库操作强化—图书管理系统
2.若有相关文章侵犯您的权益，请联系源儿删除，谢谢！
3.相关软件、资料仅供学习参考使用，在24h内务必删除！
腾龙梦屋 » Java实验六（下）：数据库操作强化—图书管理系统
加速支持