Java图书管理系统CRUD实战与数据库设计

1. 图书管理系统中的增删改查实战指南每次看到新入行的开发者在面试中被实现一个图书管理系统这类题目难住时我都想起自己早年用记事本写Java连接MySQL的囧事。增删改查CRUD就像编程界的四则运算——看似简单却暗藏玄机。以图书管理为例一个完整的CRUD实现需要前后端配合、数据库设计、业务逻辑验证等多维度考量。下面我就用十年来踩过的坑带你从零实现一套健壮的图书管理系统。2. 数据库设计与建表2.1 表结构设计要点图书表(books)基础字段设计CREATE TABLE books ( id int(11) NOT NULL AUTO_INCREMENT COMMENT 主键, isbn varchar(20) NOT NULL COMMENT 国际标准书号, title varchar(100) NOT NULL COMMENT 书名, author varchar(50) NOT NULL COMMENT 作者, publisher varchar(50) DEFAULT NULL COMMENT 出版社, publish_date date DEFAULT NULL COMMENT 出版日期, price decimal(10,2) DEFAULT NULL COMMENT 定价, stock int(11) NOT NULL DEFAULT 0 COMMENT 库存量, create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, update_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间, PRIMARY KEY (id), UNIQUE KEY idx_isbn (isbn), KEY idx_author (author) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT图书信息表;关键设计原则使用自增主键提高插入性能ISBN设置唯一索引避免重复录入作者字段添加普通索引优化查询价格字段使用decimal避免浮点精度问题自动维护创建/更新时间2.2 关联表设计图书分类关联表(book_category)示例CREATE TABLE book_category ( id int(11) NOT NULL AUTO_INCREMENT, book_id int(11) NOT NULL COMMENT 图书ID, category_id int(11) NOT NULL COMMENT 分类ID, PRIMARY KEY (id), UNIQUE KEY uk_book_category (book_id,category_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;3. 增删改查核心实现3.1 新增图书CreateJavaMyBatis实现示例// 使用Valid注解进行参数校验 PostMapping(/books) public Result addBook(Valid RequestBody Book book) { // ISBN唯一性校验 if(bookService.checkIsbnExist(book.getIsbn())){ return Result.error(ISBN已存在); } // 设置默认库存 if(book.getStock() null){ book.setStock(0); } // 事务处理 return transactionTemplate.execute(status - { try { bookMapper.insert(book); // 处理分类关联 if(!CollectionUtils.isEmpty(book.getCategoryIds())){ bookCategoryMapper.batchInsert(book.getId(), book.getCategoryIds()); } return Result.success(book.getId()); } catch (Exception e) { status.setRollbackOnly(); log.error(添加图书失败, e); return Result.error(系统异常); } }); }常见问题处理批量插入性能优化使用MyBatis的foreach标签事务边界控制非必要字段不要放在事务内唯一索引冲突先查询再插入存在并发问题推荐使用INSERT IGNORE3.2 查询图书Retrieve3.2.1 基础查询GetMapping(/books/{id}) public Result getBook(PathVariable Integer id) { Book book bookMapper.selectById(id); if(book null){ return Result.error(图书不存在); } // 查询关联分类 ListInteger categoryIds bookCategoryMapper.selectCategoryIdsByBookId(id); book.setCategoryIds(categoryIds); return Result.success(book); }3.2.2 分页查询GetMapping(/books) public Result listBooks( RequestParam(required false) String title, RequestParam(required false) String author, RequestParam(defaultValue 1) Integer pageNum, RequestParam(defaultValue 10) Integer pageSize) { PageHelper.startPage(pageNum, pageSize); ListBook books bookMapper.selectByCondition(title, author); PageInfoBook pageInfo new PageInfo(books); return Result.success(pageInfo); }性能优化技巧避免使用SELECT *只查询必要字段大数据量表使用延迟关联优化分页模糊查询LIKE %关键词%会导致索引失效3.3 更新图书UpdatePutMapping(/books/{id}) public Result updateBook(PathVariable Integer id, Valid RequestBody Book book) { Book existBook bookMapper.selectById(id); if(existBook null){ return Result.error(图书不存在); } // ISBN不可修改 if(!existBook.getIsbn().equals(book.getIsbn())){ return Result.error(ISBN不允许修改); } book.setId(id); bookMapper.updateById(book); // 更新分类关联先删后增 bookCategoryMapper.deleteByBookId(id); if(!CollectionUtils.isEmpty(book.getCategoryIds())){ bookCategoryMapper.batchInsert(id, book.getCategoryIds()); } return Result.success(); }更新操作的注意事项重要字段如ISBN应禁止修改关联数据更新需要保持原子性记录修改日志用于审计追踪3.4 删除图书DeleteDeleteMapping(/books/{id}) public Result deleteBook(PathVariable Integer id) { // 存在性校验 if(bookMapper.selectById(id) null){ return Result.error(图书不存在); } // 检查是否有借阅记录 if(borrowRecordMapper.countByBookId(id) 0){ return Result.error(存在借阅记录不可删除); } transactionTemplate.execute(status - { bookMapper.deleteById(id); bookCategoryMapper.deleteByBookId(id); return null; }); return Result.success(); }删除策略选择物理删除直接DELETE需处理关联数据逻辑删除使用is_deleted字段标记归档删除移动到历史表4. 高级功能实现4.1 批量导入导出使用Apache POI实现Excel导入public Result importBooks(MultipartFile file) { try (InputStream is file.getInputStream()) { Workbook workbook new XSSFWorkbook(is); Sheet sheet workbook.getSheetAt(0); ListBook books new ArrayList(); for (Row row : sheet) { if(row.getRowNum() 0) continue; // 跳过标题行 Book book new Book(); book.setIsbn(row.getCell(0).getStringCellValue()); book.setTitle(row.getCell(1).getStringCellValue()); // 其他字段处理... books.add(book); } // 批量插入 bookMapper.batchInsert(books); return Result.success(books.size()); } catch (Exception e) { log.error(导入失败, e); return Result.error(文件解析错误); } }4.2 缓存优化方案Redis缓存实现示例Cacheable(value books, key #id) public Book getBookWithCache(Integer id) { return bookMapper.selectById(id); } CachePut(value books, key #book.id) public Book updateBookWithCache(Book book) { bookMapper.updateById(book); return book; } CacheEvict(value books, key #id) public void deleteBookWithCache(Integer id) { bookMapper.deleteById(id); }缓存策略建议读多写少的数据适合缓存设置合理的过期时间如30分钟高并发场景考虑使用互斥锁防止缓存击穿5. 安全与性能考量5.1 SQL注入防护错误示范GetMapping(/books/unsafe) public ListBook unsafeQuery(RequestParam String title) { String sql SELECT * FROM books WHERE title LIKE % title %; return jdbcTemplate.query(sql, new BeanPropertyRowMapper(Book.class)); }正确做法GetMapping(/books/safe) public ListBook safeQuery(RequestParam String title) { return bookMapper.selectByTitle(% title %); }5.2 接口幂等性设计POST请求幂等处理PostMapping(/books/idempotent) public Result addBookIdempotent(RequestHeader(X-Request-Id) String requestId, Valid RequestBody Book book) { // 检查请求ID是否已处理 if(redisTemplate.opsForValue().setIfAbsent(req:requestId, 1, 24, TimeUnit.HOURS)){ return bookService.addBook(book); } return Result.error(请勿重复提交); }6. 前端交互建议6.1 Vue.js实现示例图书列表组件template div el-table :databooks border el-table-column proptitle label书名/el-table-column el-table-column propauthor label作者/el-table-column el-table-column label操作 template #default{row} el-button clickhandleEdit(row)编辑/el-button el-button typedanger clickhandleDelete(row.id)删除/el-button /template /el-table-column /el-table el-pagination current-changehandlePageChange :current-pagepagination.page :page-sizepagination.size :totalpagination.total /el-pagination /div /template script export default { data() { return { books: [], pagination: { page: 1, size: 10, total: 0 } } }, mounted() { this.fetchBooks(); }, methods: { async fetchBooks() { const res await this.$http.get(/books, { params: { pageNum: this.pagination.page, pageSize: this.pagination.size } }); this.books res.data.list; this.pagination.total res.data.total; }, handlePageChange(page) { this.pagination.page page; this.fetchBooks(); } } } /script6.2 表单验证策略Element UI表单验证示例el-form :modelbook :rulesrules refform el-form-item labelISBN propisbn el-input v-modelbook.isbn/el-input /el-form-item el-form-item label书名 proptitle el-input v-modelbook.title/el-input /el-form-item !-- 其他字段 -- /el-form script export default { data() { return { book: { isbn: , title: , // 其他字段 }, rules: { isbn: [ { required: true, message: 请输入ISBN, trigger: blur }, { pattern: /^\d{13}$/, message: ISBN必须为13位数字 } ], title: [ { required: true, message: 请输入书名, trigger: blur }, { min: 1, max: 100, message: 长度在1到100个字符 } ] } } } } /script7. 测试方案设计7.1 单元测试用例JUnit测试示例SpringBootTest public class BookServiceTest { Autowired private BookService bookService; Test public void testAddBook() { Book book new Book(); book.setIsbn(9787115470662); book.setTitle(测试驱动开发); book.setAuthor(Kent Beck); Integer id bookService.addBook(book); assertNotNull(id); Book savedBook bookService.getBook(id); assertEquals(测试驱动开发, savedBook.getTitle()); } Test public void testAddDuplicateIsbn() { Book book1 new Book(); book1.setIsbn(9787121377963); book1.setTitle(代码整洁之道); bookService.addBook(book1); Book book2 new Book(); book2.setIsbn(9787121377963); book2.setTitle(重复ISBN测试); assertThrows(BusinessException.class, () - { bookService.addBook(book2); }); } }7.2 性能测试要点使用JMeter测试建议并发查询测试模拟100-1000并发查询图书列表混合场景测试读写比例按8:2配置长时间稳定性测试持续运行12小时以上重点关注指标平均响应时间(500ms)错误率(0.1%)吞吐量(每秒请求数)8. 部署与监控8.1 数据库连接池配置Druid推荐配置spring: datasource: type: com.alibaba.druid.pool.DruidDataSource druid: url: jdbc:mysql://localhost:3306/library?useSSLfalse username: root password: 123456 initial-size: 5 min-idle: 5 max-active: 20 max-wait: 60000 time-between-eviction-runs-millis: 60000 min-evictable-idle-time-millis: 300000 validation-query: SELECT 1 test-while-idle: true test-on-borrow: false test-on-return: false8.2 Prometheus监控指标自定义业务指标示例RestController public class BookController { private final Counter addBookCounter Counter.build() .name(library_books_add_total) .help(Total number of books added) .register(); PostMapping(/books) public Result addBook(RequestBody Book book) { // 业务逻辑... addBookCounter.inc(); return Result.success(); } }监控看板应包含接口QPS/耗时数据库连接池状态缓存命中率JVM内存/GC情况9. 项目演进方向9.1 微服务化改造建议拆分方向图书服务核心CRUD功能库存服务独立管理库存逻辑搜索服务Elasticsearch实现全文检索推荐服务基于用户行为的智能推荐9.2 弹性设计考虑应对高并发的策略数据库读写分离热点数据多级缓存请求限流与降级异步处理非核心流程十年经验告诉我CRUD看似简单但魔鬼藏在细节里。一个图书ISBN校验的疏忽可能导致数据混乱一次不加事务的更新可能引发关联数据不一致。建议在开发过程中建立完整的校验机制和监控体系把简单的增删改查做出工业级可靠性。