java swing mysql图书管理系统（附完整源代码）

1、 课程设计精品图书管理系统书生系统说明：本系统采用eclipse开发，IDEA，eclipse，myeclipse均可运行界面采用swing实现数据库：mysql，附sql代码，其余数据库可复制sql代码运行数据库连接文件mysql-connector-java-8.0.16.jar本系统的背景图片可根据自己的需求替换项目分为管理员和普通用户两种角色管理员有图书查询、图书借还、账户管理（修改密码、账户信息、借书记录）、图书管理（增加、修改、删除）的权限普通用户有图书查询、图书借还、账户管理（修改密码、借书记录）的权限项目结构：登录界面：管理员登录：账号root，密码123123 普通用户登录：

2、账号123，密码123123注册界面：管理员主界面：图书查询界面：图书借还界面：账户管理界面：图书管理界面：添加图书界面：修改图书界面：账户信息界面：借书记录界面：普通用户主界面：普通用户账户管理界面：背景图片：数据库文件（tsglsys.Sql）：SET NAMES utf8mb4;SET FOREIGN_KEY_CHECKS = 0;DROP TABLE IF EXISTS bookcategory;CREATE TABLE bookcategory ( Category varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT

3、NULL COMMENT 类别, PRIMARY KEY (Category) USING BTREE) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;INSERT INTO bookcategory VALUES (军事);INSERT INTO bookcategory VALUES (天文);INSERT INTO bookcategory VALUES (学术);INSERT INTO bookcategory VALUES (小说);INSERT INTO boo

4、kcategory VALUES (文艺);INSERT INTO bookcategory VALUES (科幻);INSERT INTO bookcategory VALUES (经济);DROP TABLE IF EXISTS booktable;CREATE TABLE booktable ( bookid int(10) NOT NULL AUTO_INCREMENT COMMENT 书号, category varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 类别, bookname var

5、char(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 书名, author varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 作者, press varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 出版社, price float(10, 2) NOT NULL COMMENT 价格, s

6、tate varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 状态, PRIMARY KEY (bookid) USING BTREE, INDEX category(category) USING BTREE, CONSTRAINT booktable_ibfk_1 FOREIGN KEY (category) REFERENCES bookcategory (Category) ON DELETE RESTRICT ON UPDATE CASCADE) ENGINE = InnoDB AUTO_IN

7、CREMENT = 21 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;INSERT INTO booktable VALUES (1, 学术, JAVA程序设计教程, 刘志宏, 航空工业出版社, 45.00, 在馆);INSERT INTO booktable VALUES (2, 学术, 软件工程, 程成, 机械工业出版社, 31.50, 在馆);INSERT INTO booktable VALUES (3, 学术, 计算机文化, (美)帕森斯(Parsons J.J), 电子工业出版社, 28.0

8、0, 在馆);INSERT INTO booktable VALUES (4, 文艺, 我在未来等你, 刘同, 青年光影出版社, 36.50, 在馆);INSERT INTO booktable VALUES (5, 小说, 西游记, 吴承恩, 岳麓出版社, 33.50, 在馆);INSERT INTO booktable VALUES (6, 学术, 数据结构（c语言）, 严蔚敏, 清华大学出版社, 27.50, 在馆);INSERT INTO booktable VALUES (7, 学术, C语言程序设计教程, 谭浩强, 清华大学出版社, 40.00, 在馆);INSERT INTO b

9、ooktable VALUES (8, 学术, 计算机文化基础, 李永红, 对外经济贸易大学, 32.50, 在馆);INSERT INTO booktable VALUES (9, 学术, 数据库系统实现, 加西亚-莫利纳(, 机械工业出版社, 47.00, 在馆);INSERT INTO booktable VALUES (10, 文艺, 数据库系统基础教程, 厄尔曼, 机械工业出版社, 38.50, 在馆);INSERT INTO booktable VALUES (11, 学术, 数学思维导论, Keith Devlin, 人民邮电出版社, 40.00, 在馆);INSERT INTO

10、 booktable VALUES (12, 学术, 移动应用UI设计必修课, 余振华, 人民邮电出版社, 36.00, 在馆);INSERT INTO booktable VALUES (13, 学术, 快速软件开发, 史蒂夫迈克康奈尔, 清华大学出版社, 25.50, 在馆);INSERT INTO booktable VALUES (14, 科幻, 三体, 刘慈欣, 重庆出版社, 31.00, 在馆);INSERT INTO booktable VALUES (15, 科幻, 海底两万里, 儒尔凡尔纳, 译林出版社, 32.50, 在馆);INSERT INTO booktable VA

11、LUES (16, 军事, 狼牙, 刘猛, 北京联合出版公司, 35.50, 在馆);INSERT INTO booktable VALUES (17, 天文, 宇宙的琴弦, 布赖恩格林, 湖南科学技术出版社, 30.00, 在馆);INSERT INTO booktable VALUES (18, 经济, 货币经融学, 弗雷德里克S米什金, 中国人民大学出版社, 60.00, 在馆);DROP TABLE IF EXISTS borrowrecords;CREATE TABLE borrowrecords ( id int(10) NOT NULL AUTO_INCREMENT COMMEN

12、T 借书序列, user varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 用户名, bookid int(10) NOT NULL COMMENT 书号, bookname varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 书名, borrowtime date NOT NULL COMMENT 借书时间, returntime date NULL DEFAULT NULL COMMENT 还书时间, st

13、atus varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 状态, PRIMARY KEY (id) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 25 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;DROP TABLE IF EXISTS usertable;CREATE TABLE usertable ( user varchar(20) CHARACTER SE

14、T utf8 COLLATE utf8_general_ci NOT NULL COMMENT 用户名, studentid varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 学号, name varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 姓名, password varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT

15、 密码, admin int(10) NOT NULL COMMENT 是否为管理员账户 1为管理员，0为普通用户，默认为0, PRIMARY KEY (user) USING BTREE) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;INSERT INTO usertable VALUES (123, 123, 123, 123, 0);INSERT INTO usertable VALUES (root, root, root, 123, 1);SET FOREIGN

16、_KEY_CHECKS = 1;数据库表结构：Dao包下的AdminDao.java：package Dao;import java.sql.*;import javax.swing.JOptionPane;public class AdminDao public static boolean sureuser(String user) Connection con = DB.connectDB();PreparedStatement preSql;ResultSet rs;String sqlStr = select * from usertable where user = ?;try p

17、reSql = con.prepareStatement(sqlStr);preSql.setString(1, user);rs = preSql.executeQuery();boolean flag = false;while(rs.next() flag = true;return true;if(!flag) return false;con.close();return false; catch (SQLException e) return false;public static void modifypass(String user,String password) Conne

18、ction con = DB.connectDB();PreparedStatement preSql;String sqlStr = update usertable set password=? where user = ?;try preSql = con.prepareStatement(sqlStr);preSql.setString(1, password);preSql.setString(2, user);int ok = preSql.executeUpdate();con.close(); catch (SQLException e) public static Boole

19、an adduser(String user, String studentid, String name, String password) Connection con = DB.connectDB();PreparedStatement preSql;String sqlStr = insert into usertable values (?,?,?,?,0);try preSql = con.prepareStatement(sqlStr);preSql.setString(1, user);preSql.setString(2, studentid);preSql.setStrin

20、g(3, name);preSql.setString(4, password);int ok = preSql.executeUpdate();con.close();return true; catch (SQLException e) JOptionPane.showMessageDialog(null, 用户名已存在, 警告, JOptionPane.WARNING_MESSAGE);return false;BookDao.java：package Dao;import java.sql.*;import java.util.*;import javax.swing.*;import

21、 javax.swing.table.DefaultTableModel;public class BookDao public BookDao() public static void findcategory(JComboBox box) Connection con = DB.connectDB();PreparedStatement preSql;ResultSet rs;String sqlStr = select * from bookcategory;try preSql = con.prepareStatement(sqlStr);rs = preSql.executeQuer

22、y();while (rs.next() String category = rs.getString(1);box.addItem(category);con.close(); catch (SQLException e) /添加图书public static void addbook(String category,String bookname,String author,String press,String price) Connection con = DB.connectDB();PreparedStatement preSql;String sqlStr = insert in

23、to booktable(category,bookname,author,press,price,state) values (?,?,?,?,?,?);try preSql = con.prepareStatement(sqlStr);preSql.setString(1, category);preSql.setString(2, bookname);preSql.setString(3, author);preSql.setString(4, press);preSql.setString(5, price);preSql.setString(6, 在馆);int ok = preSq

24、l.executeUpdate();con.close(); catch (SQLException e) /修改图书public static void modifybook(int bookid,String category,String bookname,String author,String press,String state,String price) Connection con = DB.connectDB();PreparedStatement preSql;String sqlStr = update booktable set category = ? ,bookna

25、me = ? ,author = ?,press = ? ,price = ?,state = ? where bookid = ?;try preSql = con.prepareStatement(sqlStr);preSql.setString(1, category);preSql.setString(2, bookname);preSql.setString(3, author);preSql.setString(4, press);preSql.setString(5, price);preSql.setString(6, state);preSql.setInt(7, booki

26、d);int ok = preSql.executeUpdate();con.close(); catch (SQLException e) /删除图书public static void deletebook(int bookid) Connection con = DB.connectDB();PreparedStatement preSql;String sqlStr = delete from booktable where bookid = ?;try preSql = con.prepareStatement(sqlStr);preSql.setInt(1, bookid);int

27、 ok = preSql.executeUpdate();con.close(); catch (SQLException e) / 显示所有图书public static void allbook(DefaultTableModel model) Connection con = DB.connectDB();PreparedStatement preSql;ResultSet rs;String sqlStr = select * from booktable;try preSql = con.prepareStatement(sqlStr);rs = preSql.executeQuer

28、y();while (rs.next() int bookid = rs.getInt(1);String category = rs.getString(2);String bookname = rs.getString(3);String author = rs.getString(4);String press = rs.getString(5);String price = rs.getString(6);String state = rs.getString(7);model.addRow(new Vector(Arrays.asList(bookid, category, book

29、name, author, press,price, state);con.close(); catch (SQLException e) / 按类别查找图书public static void findcategory(DefaultTableModel model, String CateGory) Connection con = DB.connectDB();PreparedStatement preSql;ResultSet rs;CateGory = % + CateGory + %;String sqlStr = select * from booktable where cat

30、egory like ?;try preSql = con.prepareStatement(sqlStr);preSql.setString(1, CateGory);rs = preSql.executeQuery();boolean flag = false;while (rs.next() flag = true;int bookid = rs.getInt(1);String category = rs.getString(2);String bookname = rs.getString(3);String author = rs.getString(4);String press

31、 = rs.getString(5);String price = rs.getString(6);String state = rs.getString(7);model.addRow(new Vector(Arrays.asList(bookid, category, bookname, author, press, price,state);if (!flag) JOptionPane.showMessageDialog(null, 图书不存在, 警告, JOptionPane.WARNING_MESSAGE);con.close(); catch (SQLException e) /

32、按书号查找图书public static void findbookid(DefaultTableModel model, int BookId) Connection con = DB.connectDB();PreparedStatement preSql;ResultSet rs;String sqlStr = select * from booktable where bookid = ?;try preSql = con.prepareStatement(sqlStr);preSql.setInt(1, BookId);rs = preSql.executeQuery();boole

33、an flag = false;while (rs.next() flag = true;int bookid = rs.getInt(1);String category = rs.getString(2);String bookname = rs.getString(3);String author = rs.getString(4);String press = rs.getString(5);String price = rs.getString(6);String state = rs.getString(7);model.addRow(new Vector(Arrays.asLis

34、t(bookid, category, bookname, author, press, price,state);if (!flag) JOptionPane.showMessageDialog(null, 图书不存在, 警告, JOptionPane.WARNING_MESSAGE);con.close(); catch (SQLException e) BorrowDao.javapackage Dao;import java.sql.*;import java.sql.Date;import java.util.*;import javax.swing.table.DefaultTab

35、leModel;public class BorrowDao public BorrowDao() /借阅图书public static void Borrow(String user, int bookid, String bookname) Connection con = DB.connectDB();PreparedStatement preSql;String sqlStr = insert into borrowrecords(user,bookid,bookname,borrowtime,returntime,status) values (?,?,?,now(),null,?)

36、;try preSql = con.prepareStatement(sqlStr);preSql.setString(1, user);preSql.setInt(2, bookid);preSql.setString(3, bookname);preSql.setString(4, 未还);int ok = preSql.executeUpdate();con.close();ChangeBorrowState(bookid); catch (SQLException e) /借阅图书修改图书状态private static void ChangeBorrowState(int booki

37、d) Connection con = DB.connectDB();PreparedStatement preSql;String sqlStr = update booktable set state=? where bookid = ?;try preSql = con.prepareStatement(sqlStr);preSql.setString(1, 外借);preSql.setInt(2, bookid);int ok = preSql.executeUpdate();con.close(); catch (SQLException e) /还书public static vo

38、id Return(String user, int bookid) Connection con = DB.connectDB();PreparedStatement preSql;String sqlStr = select now();ResultSet rs;Date date=null;try preSql = con.prepareStatement(sqlStr);rs = preSql.executeQuery();while(rs.next() date = rs.getDate(1);sqlStr = update borrowrecords set returntime

39、= ? where bookid = ? and user = ? and status = ?;preSql = con.prepareStatement(sqlStr);preSql.setDate(1, date);preSql.setInt(2, bookid);preSql.setString(3, user);preSql.setString(4, 未还);int ok = preSql.executeUpdate();sqlStr = update borrowrecords set status = ? where bookid = ? and user = ? and sta

40、tus = ?;preSql = con.prepareStatement(sqlStr);preSql.setString(1, 已还);preSql.setInt(2, bookid);preSql.setString(3, user);preSql.setString(4, 未还);ok = preSql.executeUpdate();con.close();ChangeReturnState(bookid); catch (SQLException e) /还书图书修改图书状态private static void ChangeReturnState(int bookid) Conn

41、ection con = DB.connectDB();PreparedStatement preSql;String sqlStr = update booktable set state=? where bookid = ?;try preSql = con.prepareStatement(sqlStr);preSql.setString(1, 在馆);preSql.setInt(2, bookid);int ok = preSql.executeUpdate();con.close(); catch (SQLException e) /比对还书public static boolean

42、 comparison(String user,int bookid) Connection con = DB.connectDB();PreparedStatement preSql;ResultSet rs;String sqlStr = select * from borrowrecords where bookid = ? and status = ?;try preSql = con.prepareStatement(sqlStr);preSql.setInt(1, bookid);preSql.setString(2, 未还);rs = preSql.executeQuery();

43、while (rs.next() String user2 = rs.getString(2);if(user2.equals(user) return true;else return false;con.close();return false; catch (SQLException e) return false;public static void allborrow(DefaultTableModel model) Connection con = DB.connectDB();PreparedStatement preSql;ResultSet rs;String sqlStr

44、= select * from borrowrecords;try preSql = con.prepareStatement(sqlStr);rs = preSql.executeQuery();while (rs.next() String user = rs.getString(2);int bookid = rs.getInt(3);String bookname = rs.getString(4);Date date = rs.getDate(5);Date date2 = rs.getDate(6);String status = rs.getString(7);model.add

45、Row(new Vector(Arrays.asList(user,bookid,bookname,date,date2,status);con.close(); catch (SQLException e) /用户名借书记录public static void userborrow(DefaultTableModel model,String user) Connection con = DB.connectDB();PreparedStatement preSql;ResultSet rs;String sqlStr = select * from borrowrecords where

46、user = ?;try preSql = con.prepareStatement(sqlStr);preSql.setString(1, user);rs = preSql.executeQuery();while (rs.next() int bookid = rs.getInt(3);String bookname = rs.getString(4);Date date = rs.getDate(5);Date date2 = rs.getDate(6);String status = rs.getString(7);model.addRow(new Vector(Arrays.asL

47、ist(user,bookid,bookname,date,date2,status);con.close(); catch (SQLException e) /书号借书记录public static void bookidborrow(DefaultTableModel model,int bookid) Connection con = DB.connectDB();PreparedStatement preSql;ResultSet rs;String sqlStr = select * from borrowrecords where bookid = ?;try preSql = c

48、on.prepareStatement(sqlStr);preSql.setInt(1, bookid);rs = preSql.executeQuery();while (rs.next() String user = rs.getString(2);String bookname = rs.getString(4);Date date = rs.getDate(5);Date date2 = rs.getDate(6);String status = rs.getString(7);model.addRow(new Vector(Arrays.asList(user,bookid,book

49、name,date,date2,status);con.close(); catch (SQLException e) DB.java(数据库连接类)：package Dao;import java.sql.*;public class DB public static Connection connectDB() String url = jdbc:mysql:/ localhost:3306/tsglsys?serverTimezone=GMT%2B8;try Class.forName(com.mysql.cj.jdbc.Driver); catch (Exception e) Conn

50、ection con = null;try con = DriverManager.getConnection(url, root, 1234); catch (SQLException e) return con;LandDao.java:package Dao;import java.sql.*;import javax.swing.JOptionPane;public class LandDao public static Boolean test(String user, String password) Connection con = DB.connectDB();Prepared

51、Statement preSql;ResultSet rs;String sqlStr = select * from usertable where user = ?;try preSql = con.prepareStatement(sqlStr);preSql.setString(1, user);rs = preSql.executeQuery();boolean flag = false;while(rs.next() flag = true;String password2 = rs.getString(4);if (!(password.equals(password2) JOp

52、tionPane.showMessageDialog(null, 密码错误, 警告, JOptionPane.WARNING_MESSAGE);return false;if(!flag) JOptionPane.showMessageDialog(null, 用户名不存在, 警告, JOptionPane.WARNING_MESSAGE);return false;con.close();return true; catch (SQLException e) JOptionPane.showMessageDialog(null, 用户名不存在, 警告, JOptionPane.WARNING

53、_MESSAGE);return false;/确定是否为管理员public static boolean sureadmin(String user) Connection con = DB.connectDB();PreparedStatement preSql;ResultSet rs;String sqlStr = select * from usertable where user = ?;try preSql = con.prepareStatement(sqlStr);preSql.setString(1, user);rs = preSql.executeQuery();whi

54、le(rs.next() int admin = rs.getInt(5);if (admin=1) return true;else return false;con.close();return true; catch (SQLException e) return false;Test包LandTest.java:package Test;import java.awt.*;import java.awt.event.ActionEvent;import java.awt.event.ActionListener;import javax.swing.*;import Dao.LandD

55、ao;import View.LogonFace;import View.MainFace;public class LandTest private JLabel jLabel = new JLabel(图书管理系统);private JLabel jLabel2 = new JLabel(用户名： );private JLabel jLabel3 = new JLabel(密 码： );private Font font = new Font(宋体, Font.BOLD, 40);private Font font2 = new Font(宋体, Font.BOLD, 25);privat

56、e Font font3 = new Font(宋体, Font.BOLD, 20);private JTextField field = new JTextField(22);private JPasswordField field2 = new JPasswordField(22);private JButton button = new JButton(登陆);private JButton button2 = new JButton(注册);private JPanel jPanel = new JPanel();private JPanel jPanel2 = new JPanel(

57、);private JPanel jPanel3 = new JPanel();private JPanel jPanel4 = new JPanel();private JPanel jPanel5 = new JPanel();private JFrame frame = new JFrame(登陆);private Dimension dimension = new Dimension(30, 30);private Dimension dimension2 = new Dimension(100, 50);public String user;private String passwo

58、rd;public LandTest() frame.setTitle(登陆);frame.setSize(550, 500);frame.setLocationRelativeTo(null);frame.setLayout(null);Toolkit t = Toolkit.getDefaultToolkit();Image image = t.getImage(imglogin.jpg);frame.setIconImage(image);addassembly();transparent();addEvent();Icon i = new ImageIcon(imglogin.jpg)

59、;JLabel Label = new JLabel(i);Label.setBounds(0, 0, 550, 500);frame.add(Label);frame.setResizable(false);frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);frame.setVisible(true);private void addassembly() jLabel.setFont(font);jLabel2.setFont(font2);jLabel3.setFont(font2);button.setFont(font3);but

60、ton2.setFont(font3);field.setFont(font3);field2.setFont(font3);field.setPreferredSize(dimension);field2.setPreferredSize(dimension);button.setPreferredSize(dimension2);button2.setPreferredSize(dimension2);jPanel.add(jLabel);jPanel2.add(jLabel2);jPanel2.add(field);jPanel3.add(jLabel3);jPanel3.add(fie