项目实战：从零构建Java+MySQL图书借阅系统（Swing版）
1. 项目概述与需求分析图书借阅系统是图书馆日常运营的核心工具也是Java初学者练手的经典项目。这个Swing版系统主要面向高校课程设计或毕业设计场景需要实现以下核心功能用户角色分离管理员负责图书/用户管理读者完成借阅/归还操作基础CRUD图书信息增删改查、用户信息维护借阅逻辑包含借书、还书、续借、逾期计算等完整流程数据统计图书库存、借阅排行榜等可视化报表我去年指导学生做这个项目时发现新手常卡在三个地方数据库设计不合理导致后期逻辑混乱、Swing事件绑定不熟练、借还书的事务处理出错。下面我们就从最关键的数据库设计开始一步步避开这些坑。2. 数据库设计与实现2.1 表结构设计用Navicat新建名为library_db的数据库字符集选utf8mb4。核心四张表的设计要点-- 管理员表 CREATE TABLE admin ( id INT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(20) NOT NULL UNIQUE, password CHAR(32) NOT NULL -- 存储MD5加密结果 ); -- 图书表注意索引设计 CREATE TABLE book ( id INT PRIMARY KEY AUTO_INCREMENT, isbn VARCHAR(17) NOT NULL UNIQUE, name VARCHAR(100) NOT NULL, author VARCHAR(50) NOT NULL, press VARCHAR(50) COMMENT 出版社, stock INT DEFAULT 1 CHECK(stock 0) -- 库存不能为负 ); CREATE INDEX idx_book_name ON book(name); -- 读者表 CREATE TABLE reader ( id INT PRIMARY KEY AUTO_INCREMENT, card_id VARCHAR(20) NOT NULL UNIQUE COMMENT 借书证号, name VARCHAR(20) NOT NULL, password CHAR(32) NOT NULL ); -- 借阅记录表核心业务表 CREATE TABLE borrow ( id INT PRIMARY KEY AUTO_INCREMENT, book_id INT NOT NULL, reader_id INT NOT NULL, borrow_date DATETIME DEFAULT CURRENT_TIMESTAMP, return_date DATETIME COMMENT 应还日期, actual_return DATETIME COMMENT 实际归还, FOREIGN KEY (book_id) REFERENCES book(id), FOREIGN KEY (reader_id) REFERENCES reader(id) );避坑指南密码字段一定要用CHAR定长存储加密结果ISBN字段建议遵循978-7-04-048911-2这种带分隔符格式借阅表的日期字段要区分应还和实还2.2 初始化测试数据插入示例数据时注意业务规则-- 管理员账号 INSERT INTO admin VALUES(null, admin, MD5(123456)); -- 图书数据真实ISBN INSERT INTO book VALUES (null, 978-7-121-35412-6, Java核心技术, Cay S. Horstmann, 电子工业出版社, 5), (null, 978-7-302-48527-9, MySQL必知必会, Ben Forta, 清华大学出版社, 3); -- 读者账号 INSERT INTO reader VALUES (null, 2023001, 张三, MD5(111111)), (null, 2023002, 李四, MD5(222222));3. Java项目搭建3.1 Maven项目初始化在IDEA中新建Maven项目pom.xml关键依赖dependencies !-- MySQL驱动 -- dependency groupIdmysql/groupId artifactIdmysql-connector-java/artifactId version8.0.28/version /dependency !-- 日期处理 -- dependency groupIdjoda-time/groupId artifactIdjoda-time/artifactId version2.10.10/version /dependency !-- 单元测试 -- dependency groupIdjunit/groupId artifactIdjunit/artifactId version4.13.2/version scopetest/scope /dependency /dependencies3.2 数据库工具类封装JDBC操作时要注意连接池和异常处理public class DBUtil { private static final String URL jdbc:mysql://localhost:3306/library_db?useSSLfalseserverTimezoneAsia/Shanghai; private static final String USER root; private static final String PASSWORD yourpassword; // 获取连接实际项目建议用HikariCP public static Connection getConnection() { try { return DriverManager.getConnection(URL, USER, PASSWORD); } catch (SQLException e) { throw new RuntimeException(数据库连接失败, e); } } // 关闭资源 public static void close(Connection conn, Statement stmt, ResultSet rs) { try { if(rs ! null) rs.close(); if(stmt ! null) stmt.close(); if(conn ! null) conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }4. 登录模块开发4.1 登录界面设计使用Swing构建登录窗口时建议用JPanelGridBagLayout实现响应式布局public class LoginFrame extends JFrame { private JTextField tfUsername; private JPasswordField pfPassword; public LoginFrame() { setTitle(图书管理系统登录); setSize(400, 300); setDefaultCloseOperation(EXIT_ON_CLOSE); setLocationRelativeTo(null); JPanel panel new JPanel(new GridBagLayout()); GridBagConstraints gbc new GridBagConstraints(); gbc.insets new Insets(5, 5, 5, 5); // 用户名标签 gbc.gridx 0; gbc.gridy 0; panel.add(new JLabel(用户名:), gbc); // 用户名输入框 gbc.gridx 1; gbc.fill GridBagConstraints.HORIZONTAL; tfUsername new JTextField(15); panel.add(tfUsername, gbc); // 密码标签gridy1 gbc.gridx 0; gbc.gridy 1; panel.add(new JLabel(密码:), gbc); // 密码输入框 gbc.gridx 1; pfPassword new JPasswordField(15); panel.add(pfPassword, gbc); // 登录按钮事件绑定在4.2节 gbc.gridx 0; gbc.gridy 2; gbc.gridwidth 2; JButton btnLogin new JButton(登录); panel.add(btnLogin, gbc); add(panel); } }4.2 登录验证逻辑密码验证要使用MD5加密比对防止SQL注入// 在LoginFrame类中添加事件监听 btnLogin.addActionListener(e - { String username tfUsername.getText().trim(); String password new String(pfPassword.getPassword()); if(username.isEmpty() || password.isEmpty()) { JOptionPane.showMessageDialog(this, 用户名密码不能为空); return; } // 密码MD5加密 String encryptedPwd DigestUtils.md5Hex(password); Connection conn null; PreparedStatement ps null; ResultSet rs null; try { conn DBUtil.getConnection(); String sql SELECT * FROM admin WHERE username? AND password?; ps conn.prepareStatement(sql); ps.setString(1, username); ps.setString(2, encryptedPwd); rs ps.executeQuery(); if(rs.next()) { new MainFrame().setVisible(true); // 跳转主界面 dispose(); // 关闭登录窗口 } else { JOptionPane.showMessageDialog(this, 用户名或密码错误); } } catch (SQLException ex) { ex.printStackTrace(); JOptionPane.showMessageDialog(this, 数据库查询失败); } finally { DBUtil.close(conn, ps, rs); } });5. 图书管理模块5.1 图书查询功能实现多条件模糊查询时注意SQL拼接的安全性问题public ListBook searchBooks(String name, String author) { ListBook books new ArrayList(); StringBuilder sql new StringBuilder(SELECT * FROM book WHERE 11 ); ListObject params new ArrayList(); if(!name.isEmpty()) { sql.append(AND name LIKE ? ); params.add(% name %); } if(!author.isEmpty()) { sql.append(AND author LIKE ? ); params.add(% author %); } Connection conn null; PreparedStatement ps null; ResultSet rs null; try { conn DBUtil.getConnection(); ps conn.prepareStatement(sql.toString()); for(int i0; iparams.size(); i) { ps.setObject(i1, params.get(i)); } rs ps.executeQuery(); while(rs.next()) { Book book new Book(); book.setId(rs.getInt(id)); book.setIsbn(rs.getString(isbn)); book.setName(rs.getString(name)); book.setAuthor(rs.getString(author)); book.setPress(rs.getString(press)); book.setStock(rs.getInt(stock)); books.add(book); } } catch (SQLException e) { e.printStackTrace(); } finally { DBUtil.close(conn, ps, rs); } return books; }5.2 借书业务实现借书操作需要处理事务确保库存和借阅记录同步更新public boolean borrowBook(int bookId, int readerId) { Connection conn null; try { conn DBUtil.getConnection(); conn.setAutoCommit(false); // 开启事务 // 1. 检查库存 PreparedStatement ps1 conn.prepareStatement( SELECT stock FROM book WHERE id? FOR UPDATE); ps1.setInt(1, bookId); ResultSet rs ps1.executeQuery(); if(!rs.next() || rs.getInt(stock) 0) { return false; } // 2. 减少库存 PreparedStatement ps2 conn.prepareStatement( UPDATE book SET stockstock-1 WHERE id?); ps2.setInt(1, bookId); ps2.executeUpdate(); // 3. 添加借阅记录应还日期当前时间30天 PreparedStatement ps3 conn.prepareStatement( INSERT INTO borrow(book_id, reader_id, return_date) VALUES(?, ?, DATE_ADD(NOW(), INTERVAL 30 DAY))); ps3.setInt(1, bookId); ps3.setInt(2, readerId); ps3.executeUpdate(); conn.commit(); return true; } catch (SQLException e) { if(conn ! null) { try { conn.rollback(); } catch (SQLException ex) { ex.printStackTrace(); } } e.printStackTrace(); return false; } finally { if(conn ! null) { try { conn.setAutoCommit(true); } catch (SQLException e) { e.printStackTrace(); } } DBUtil.close(conn, null, null); } }6. 报表统计功能6.1 使用JFreeChart生成图表在pom.xml添加依赖后实现借阅排行榜dependency groupIdorg.jfree/groupId artifactIdjfreechart/artifactId version1.5.3/version /dependency生成饼图的代码示例public JPanel createBookRankChart() { // 获取借阅量前5的图书 MapString, Integer data new LinkedHashMap(); String sql SELECT b.name, COUNT(*) as cnt FROM borrow br JOIN book b ON br.book_idb.id GROUP BY b.name ORDER BY cnt DESC LIMIT 5; try (Connection conn DBUtil.getConnection(); Statement stmt conn.createStatement(); ResultSet rs stmt.executeQuery(sql)) { while(rs.next()) { data.put(rs.getString(name), rs.getInt(cnt)); } } catch (SQLException e) { e.printStackTrace(); } // 创建数据集 DefaultPieDataset dataset new DefaultPieDataset(); data.forEach(dataset::setValue); // 创建图表 JFreeChart chart ChartFactory.createPieChart( 图书借阅排行榜, dataset, true, true, false); // 返回图表面板 return new ChartPanel(chart); }7. 系统打包与部署7.1 使用Maven打包可执行JAR在pom.xml中添加maven-assembly-pluginbuild plugins plugin artifactIdmaven-assembly-plugin/artifactId version3.3.0/version configuration archive manifest mainClasscom.yourpackage.MainClass/mainClass /manifest /archive descriptorRefs descriptorRefjar-with-dependencies/descriptorRef /descriptorRefs /configuration executions execution phasepackage/phase goals goalsingle/goal /goals /execution /executions /plugin /plugins /build运行mvn package后在target目录会生成包含所有依赖的jar文件直接通过java -jar命令即可运行。7.2 常见问题解决中文乱码问题MySQL连接字符串添加useUnicodetruecharacterEncodingUTF-8Swing界面设置默认字体UIManager.put(Button.font, new Font(微软雅黑, Font.PLAIN, 12)); UIManager.put(Label.font, new Font(微软雅黑, Font.PLAIN, 12));界面卡顿优化耗时操作放在SwingWorker中执行列表数据超过1000条时启用分页查询数据库连接泄漏使用try-with-resources确保资源关闭定期检查SHOW PROCESSLIST
