小型MIS开发实战：从数据库设计到JDBC事务控制的完整拆解

简介这是一份南京邮电大学数据库系统课程的实验报告三主题为小型MIS开发面向正在学习数据库原理、MySQL及C/S或B/S结构开发的本科生。报告基于航班信息管理场景完整呈现实验目的、软硬件环境、设计与实现思路包含创建flight数据库、table_fight、table_passenger、table_ticket等多张核心表添加唯一索引构建部分航班/乘客视图并实现查询航班是否已满的存储过程系统演示了从建库建表到界面访问数据库的典型流程。资源以1个doc文档打包压缩后约359KB内容集中SQL代码与实验结论一体呈现适合数据库课程设计、实验答辩或期末复习时快速参考。目前已有405人学习尤其适合需要完成同类小型MIS开发实验或想通过具体案例巩固MySQL操作的同学。1. 小型MIS开发实验从数据库设计到可运行系统的完整拆解很多人把数据库实验三当成一次普通的建表加查询练习但小型MIS管理信息系统实验的真正考点是把数据库设计和应用开发串成一条完整的链路。这份实验报告对应的场景是某高校数据库系统课程中的第三个实验项目要求用MySQL作为后端存储配合一种开发语言实现一个小型信息管理系统覆盖基本的数据录入、修改、删除、查询和简单的统计报表功能。它的价值不在于那几张表的DDL语句而在于它展示了从需求分析、E-R模型、关系模式规范化到连接池配置、事务控制、界面联调这一整套流程正好是把课堂理论和工程实践衔接起来的关键一步。我拆这份资源的时候最强烈的感受是实验报告里写的每一步都有明确的取舍理由比如为什么用三范式而不是反范式设计、为什么主键用自增整数而不是业务编号、为什么连接层要单独封装而不是散落在界面代码里。这些决策在教材里都有理论依据但在真实的MIS开发中它们会因为数据量、并发量和维护成本而被反复权衡。如果你正被实验报告卡住或者做完之后不确定自己的系统设计是否合理这篇拆解会帮你把每个环节的选型逻辑和落地细节讲透并且告诉你哪些地方是评分重点、哪些坑是每年都有人踩的。2. 需求分析与数据库建模先画E-R图还是先建表2.1 从题目要求倒推最小可行功能集拿到实验题目第一件事不是打开MySQL就开始敲CREATE TABLE而是把题目里提到的业务描述逐条拆解成功能需求。以常见的模拟项目X小型图书管理系统为例题目通常会要求管理图书信息、读者信息、借阅记录外加一个统计报表页面。把这几句话翻译成系统功能就是图书的增删改查、读者的增删改查、借书和还书操作、逾期未还列表和分类统计。我先列一张功能与数据表的对应关系表这张表在我做的每个MIS实验里都是第一步产出物功能模块涉及数据对应表关键字段图书管理图书基本信息bookbook_id, book_name, category, price, stock读者管理读者基本信息readerreader_id, name, phone, max_borrow借阅业务借书/还书记录borrowborrow_id, book_id, reader_id, borrow_date, return_date统计报表分类汇总/逾期视图或聚合查询category, count, overdue_days这张表的用处是让你在画E-R图之前就明确哪些是实体、哪些是实体间的关系、哪些是关系上的属性。借阅记录这里的borrow_date和return_date是关系属性不属于图书也不属于读者很多同学第一次建模会把它们放错位置后面做查询时才发现数据冗余和更新异常。我在做需求分析时有一个习惯把所有动词圈出来——录入、修改、删除、查询、统计——然后逐个对应到功能和表操作上确保没有遗漏。如果题目里有“逾期”两个字就必须有当前日期和return_date的比对逻辑这个在后端代码里要实现不是数据库层面能自动完成的。2.2 关系模式规范化到什么程度合适数据库设计的核心矛盾是三范式要求和查询性能之间的平衡。实验阶段数据量小范式化不会有明显的性能问题所以我的建议是实验报告里严格按照三范式设计把规范化的推导过程写清楚这是评分点之一然后在后面用视图或联表查询来解决多表关联的复杂度而不是在设计阶段就做反范式。以图书借阅系统为例图书表、读者表、借阅表三张核心表的建表语句通常是这个样子的CREATE TABLE reader ( reader_id INT PRIMARY KEY AUTO_INCREMENT COMMENT 读者ID自增主键, name VARCHAR(50) NOT NULL COMMENT 读者姓名, phone VARCHAR(20) UNIQUE COMMENT 手机号作为候选键, max_borrow INT DEFAULT 5 COMMENT 最大借阅数量 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; CREATE TABLE book ( book_id INT PRIMARY KEY AUTO_INCREMENT, book_name VARCHAR(100) NOT NULL, category VARCHAR(30) NOT NULL, price DECIMAL(10,2), stock INT DEFAULT 1, INDEX idx_category (category) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; CREATE TABLE borrow ( borrow_id INT PRIMARY KEY AUTO_INCREMENT, book_id INT NOT NULL, reader_id INT NOT NULL, borrow_date DATE NOT NULL, return_date DATE, FOREIGN KEY (book_id) REFERENCES book(book_id), FOREIGN KEY (reader_id) REFERENCES reader(reader_id), INDEX idx_reader (reader_id), INDEX idx_return (return_date) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;这段建表语句有三个设计决策需要说明。第一主键全部用自增整数而不是业务字段比如图书编号如果使用ISBN会有长度不稳定和重复的风险而且自增主键在InnoDB的B树索引中插入效率最高。第二外键约束在borrow表上明确声明这保证了应用层即使出现逻辑错误数据库层面也不会产生孤儿记录。第三category字段上有索引因为统计报表几乎必然按分类分组这个索引在实验数据量下看不到差别但设计习惯要养成。关于范式级别的选择二范式和三范式的区别在这场实验中通常体现为是否有字段依赖于主键的一部分而不是全部。比如把出版社地址放在图书表里而出版社地址实际依赖于出版社名称而不是图书ID这就违反了三范式。实验报告里要写出这句话然后说明你把出版社独立成表或暂时舍弃这个字段的决定。3. 开发语言与MySQL的连接层为什么我坚持封装一个DBUtil3.1 连接管理是MIS系统稳定性的基石小型MIS系统选择的开发语言五花八门Java用JDBC、Python用PyMySQL或mysql-connector、C#用ADO.NET。核心逻辑是一样的建立连接、执行SQL、处理结果集、关闭资源。初学者最容易犯的错误是在每个界面按钮的事件处理里都写一遍完整的连接代码导致连接无法复用、资源泄漏、SQL语句和业务逻辑纠缠在一起。我一般会在实验报告里先说明一个观点连接管理不是业务逻辑是基础设施必须单独封装。以Java为例DBUtil类的核心职责是提供统一的连接获取和关闭入口并且把数据库连接的配置参数外置到配置文件里。这样做的直接好处是期末检查时老师如果问到“你的数据库密码改一下系统要跑起来”你只需要改配置文件不需要改代码。public class DBUtil { private static String url jdbc:mysql://localhost:3306/library_db?useSSLfalseserverTimezoneAsia/ShanghaicharacterEncodingutf8; private static String user root; private static String password 123456; private static String driver com.mysql.cj.jdbc.Driver; static { try { Class.forName(driver); } catch (ClassNotFoundException e) { e.printStackTrace(); } } public static Connection getConnection() throws SQLException { return DriverManager.getConnection(url, user, password); } public static void close(Connection conn, PreparedStatement ps, ResultSet rs) { try { if (rs ! null) rs.close(); if (ps ! null) ps.close(); if (conn ! null) conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }这段封装代码里有几个关键参数值得展开。url里的serverTimezoneAsia/Shanghai是为了解决MySQL 8.0以上版本对时区的严格校验不设置会直接报错characterEncodingutf8是为了中文不乱码注意这里要跟建表时的utf8mb4配合mysql-connector/J 8.0版本里utf8mb4才是完整的中文支持useSSLfalse是因为本地开发环境没有配置SSL证书实验场景不需要加密连接。如果你用的是MySQL 5.7驱动名是com.mysql.jdbc.Driver注意版本差异。3.2 PreparedStatement为什么是唯一正确的选择在MIS系统的数据访问层执行SQL只有两个选项Statement和PreparedStatement。我在报告中会明确写所有涉及参数传递的SQL一律使用PreparedStatement禁止字符串拼接SQL。原因有两层。第一层是安全性字符串拼接会把单引号变成SQL注入的入口比如查询读者姓名时输入一个张 OR 11拼接出来的SQL会让WHERE条件永远为真。第二层是性能PreparedStatement有预编译机制同样结构的SQL只需要编译一次在循环批处理时效果明显。public int addBook(Book book) { String sql INSERT INTO book(book_name, category, price, stock) VALUES(?,?,?,?); try (Connection conn DBUtil.getConnection(); PreparedStatement ps conn.prepareStatement(sql)) { ps.setString(1, book.getBookName()); ps.setString(2, book.getCategory()); ps.setBigDecimal(3, book.getPrice()); ps.setInt(4, book.getStock()); return ps.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); return 0; } }这里使用了try-with-resources语法Java 7及以上资源自动关闭不需要手动调用close方法代码更干净。四个set方法对应SQL里的四个占位符注意setBigDecimal对应DECIMAL类型的字段如果用setString传数字会触发隐式转换虽然MySQL允许但会有精度隐患。executeUpdate的返回值是受影响的行数等于1说明插入成功这是业务层判断操作是否成功的依据。参数设置顺序必须与SQL中问号出现的顺序一致这是新手最容易忽略的密码不要明文写在代码里实验环境可以用配置文件加常量但要在报告中说明生产环境的加密方案时区参数和编码参数必须在连接串中显式声明依赖MySQL服务端默认配置会埋坑4. 业务功能实现图书借阅流程中的事务边界和状态判断4.1 借书操作多个SQL必须在一个事务里提交图书借阅的业务逻辑看起来简单插入一条借阅记录同时把图书的库存减一。这两个操作必须是一个原子性的整体任何一个失败都不能留下半成品状态。如果只插入了borrow记录但库存没有更新读者会以为借到了书系统里却没有这本书可借的数量。如果更新了库存但插入失败书少了一本但没有任何记录可查。这正是数据库事务的典型应用场景。在MySQL中InnoDB引擎支持事务JDBC默认是自动提交模式所以必须显式关闭自动提交在业务层控制事务边界public boolean borrowBook(int bookId, int readerId) { Connection conn null; try { conn DBUtil.getConnection(); conn.setAutoCommit(false); String sql1 UPDATE book SET stock stock - 1 WHERE book_id ? AND stock 0; PreparedStatement ps1 conn.prepareStatement(sql1); ps1.setInt(1, bookId); int updateCount ps1.executeUpdate(); if (updateCount 0) { conn.rollback(); return false; // 库存不足 } String sql2 INSERT INTO borrow(book_id, reader_id, borrow_date) VALUES(?,?,CURDATE()); PreparedStatement ps2 conn.prepareStatement(sql2); ps2.setInt(1, bookId); ps2.setInt(2, readerId); ps2.executeUpdate(); conn.commit(); return true; } catch (SQLException e) { try { if (conn ! null) conn.rollback(); } catch (SQLException ex) { ex.printStackTrace(); } return false; } finally { if (conn ! null) { try { conn.setAutoCommit(true); conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } }这段代码里最关键的设计是UPDATE语句中的AND stock 0条件。这不是多余的防御而是利用数据库的行级锁和条件更新来避免并发场景下的超借问题。在高并发下两个请求同时读到stock为1如果不在UPDATE层面做限制两个都会执行成功库存变成-1。加了条件之后第二个UPDATE影响行数为0事务回滚借书失败。这就是乐观锁思想的一种简化实现MIS系统的并发量虽然不高但这个写法在思路上是生产级的。catch块里的rollback要注意JDBC中rollback本身也可能抛出SQLException所以要在嵌套try-catch中处理。finally块里把autoCommit恢复为true是一个好习惯因为连接池复用时如果某个连接提交模式没复位下一个拿到这个连接的操作会在完全错误的事务模式下运行这是特别隐蔽的坑。4.2 还书操作计算逾期天数和更新库存的联动逻辑还书逻辑比借书多个一个维度逾期判断。实验要求里通常会有“超期按天计算罚金”这类描述。我的实现方案是在还书时读取系统当前日期和应还日期做差得到逾期天数。注意这里有个边界问题逾期天数的计算应该以自然日为单位归还当天是否计算在内不同系统定义不同实验报告里需要写明你的口径。-- 还书时查询借阅记录并计算逾期 SELECT b.borrow_id, b.borrow_date, DATEDIFF(CURDATE(), b.borrow_date) AS borrowed_days FROM borrow b WHERE b.borrow_id ? AND b.return_date IS NULL;DATEDIFF函数返回两个日期相差的天数CURDATE()取当前日期。如果题目定义的最长借阅期是30天那么borrowed_days大于30就属于逾期。这里要特别提醒一个日期类型陷阱如果borrow_date字段是DATETIME类型而CURDATE()返回的是DATE类型比较时会触发隐式转换结果容易被忽略。最简单的方法是统一用DATE类型存放日期时间部分在这个场景没有意义。还书操作的数据一致性诉求和借书类似需要把归还记录的更新和库存回补放在同一事务中。还有一个工程惯例值得在报告中体现借阅表不物理删除历史记录而是用return_date是否为NULL来区分在借和已还。这样做的目的是保留完整的借阅历史为后续的统计报表提供数据基础。-- 合法还书操作 UPDATE borrow SET return_date CURDATE() WHERE borrow_id ? AND return_date IS NULL; UPDATE book SET stock stock 1 WHERE book_id (SELECT book_id FROM borrow WHERE borrow_id ?);第二条UPDATE里的子查询在SQL标准中是可以的但在某些数据库或者某些版本中会有性能问题。另一个方案是先用SELECT把book_id查出来再执行UPDATE这样多一次查询但逻辑更直白。MIS系统的数据量级下两种方案没有肉眼可见的性能差异选哪种取决于你希望展示更高级的SQL写法还是更清晰的代码结构。我一般会用两条独立的SQL代码可读性优先。4.3 查询和统计参数校验在前端还是后端MIS系统的另一个核心功能是多条件组合查询和统计报表。图书查询通常支持按书名模糊搜索、按分类精确匹配、按价格区间筛选。这三个条件任意组合需要用动态SQL来实现。一个容易犯的错误是直接把所有条件拼进SQL用恒真条件11开头来规避拼接时的AND/WHERE问题。这种写法不是不能用但不太体面。我的做法是在Java层先构建一个List集合存放条件和参数最后统一拼装public ListBook searchBooks(String name, String category, Double minPrice, Double maxPrice) { StringBuilder sql new StringBuilder(SELECT * FROM book WHERE 11); ListObject params new ArrayList(); if (name ! null !name.isEmpty()) { sql.append( AND book_name LIKE ?); params.add(% name %); } if (category ! null !category.isEmpty()) { sql.append( AND category ?); params.add(category); } if (minPrice ! null) { sql.append( AND price ?); params.add(minPrice); } if (maxPrice ! null) { sql.append( AND price ?); params.add(maxPrice); } // 执行查询并返回结果 }LIKE查询的模糊匹配要写在参数里而不是SQL里这样PreparedStatement的预编译才能正常工作否则%符号在SQL中拼接可能导致语义混乱。category的精确匹配使用号不需要加LIKE。价格的上下限分别用和注意minPrice或maxPrice单独传值时逻辑不受影响。统计报表在业务中通常使用GROUP BY和聚合函数。按分类统计图书数量这个需求需要JOIN借阅表才能统计出真实的借阅次数SELECT b.category, COUNT(*) AS book_count, SUM(CASE WHEN br.borrow_id IS NOT NULL THEN 1 ELSE 0 END) AS borrowed_count FROM book b LEFT JOIN borrow br ON b.book_id br.book_id GROUP BY b.category;这里使用LEFT JOIN而不是INNER JOIN是因为没有被借过的书也要出现在统计结果中borrowed_count为0而不是被过滤掉。SUM配合CASE WHEN的写法相当于条件计数比COUNT(DISTINCT ...)更灵活。GROUP BY的分类字段需要在SELECT列表中明确写出这是SQL语法规范。5. 避坑指南MySQL 8.0、JDBC驱动和乱码问题的常见雷区5.1 连接报错Public Key Retrieval is not allowed现象用mysql-connector/J 8.0以上版本连接MySQL 8.0时第一次连接就抛异常错误信息包含Public Key Retrieval is not allowed。原因MySQL 8.0默认使用caching_sha2_password认证插件客户端第一次连接时需要从服务器获取公钥进行加密。JDBC驱动为了安全考虑默认禁止自动获取公钥必须显式在连接串中声明。很多教程里用的连接串还停留在5.x版本的习惯少了这个参数就会踩中。解决在JDBC URL末尾追加allowPublicKeyRetrievaltrue参数完整的URL看起来是jdbc:mysql://localhost:3306/library_db?useSSLfalseserverTimezoneAsia/ShanghaicharacterEncodingutf8allowPublicKeyRetrievaltrue。注意这个参数在生产环境需要谨慎使用因为它会降低连接初期握手的安全性但实验环境没有这个风险。5.2 中文乱码建表、连接串、控制台三层都要统一现象数据库插入中文后查询出来全是问号或者乱码单独改某一方面无法修复。原因字符集不一致发生在三个层面——MySQL服务端的character_set_server、数据库表的字符集、JDBC连接串的characterEncoding参数。任何一个环节不是utf8mb4中文就可能变形。而且更隐蔽的问题是如果你在建表时没有显式指定CHARSETMySQL会使用系统变量character_set_database的默认值有时候这个值在安装时被设成了latin1。解决建库的时候显式执行CREATE DATABASE library_db DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci建表语句中的DEFAULT CHARSETutf8mb4必须写在每张表后面。连接串的characterEncodingutf8对应的是Java侧的字符编码不要漏掉。如果控制台打印时还是乱码那是操作系统编码问题不代表数据库存储错误用可视化客户端查看表数据来确认是否真的乱码。5.3 MySQL 8.0驱动类名变更导致ClassNotFoundException现象明明导入了驱动jar包运行程序却报ClassNotFoundException: com.mysql.jdbc.Driver。原因MySQL 8.0的JDBC驱动把类名从com.mysql.jdbc.Driver改成了com.mysql.cj.jdbc.Driver。老代码里的Class.forName(com.mysql.jdbc.Driver)在新驱动版本中无法加载。同样旧版连接串里的其它参数也可能因为驱动版本升级而失效。解决使用mysql-connector-java 8.0.x时把Class.forName中的驱动名改为com.mysql.cj.jdbc.Driver。如果你的项目不需要加载注册这一步比如使用JDBC 4.0自动注册机制可以省略Class.forName驱动jar包在类路径下会自动扫描注册。5.4 外键约束检查导致批量删除失败现象删除一个读者时报错提示Cannot delete or update a parent row: a foreign key constraint fails但这个读者明明没有借阅记录在借。原因MySQL的FOREIGN KEY约束要求如果存在任何关联引用无论引用记录是否处于“已还”状态父表记录都不能删除。很多同学认为删了借阅记录就可以删读者了但实际上历史借阅记录仍然引用着该读者的reader_id。解决两种做法。第一种是逻辑删除在reader表增加一个status字段删除操作变成UPDATE reader SET status0 WHERE reader_id?这是生产系统推荐做法。第二种是物理删除前先删除所有引用记录但这会丢失数据分析的历史价值。实验报告里推荐第一种同时需要在界面上区分“禁用”和“删除”的语义。5.5 事务中SELECT语句的隔离级别问题现象事务中先查询库存是否充足再执行扣减并发场景下仍然出现超借。原因默认的REPEATABLE READ隔离级别下事务中的普通SELECT读取的是快照数据不会对读取的行加锁。两个事务同时读到库存为1都认为自己可以扣减但都不会阻塞对方最终超借。解决把库存扣减放在UPDATE中通过条件更新实现就像前面借书代码中的AND stock 0那样让数据库的锁机制在UPDATE层面生效。不要在代码里先SELECT判断再UPDATE这中间存在竞态窗口。如果非要先查询后更新可以使用SELECT ... FOR UPDATE给行加锁但实验场景没必要。6. 把实验报告变成课程设计验证系统健壮性的五个测试方向做完功能开发之后系统能跑起来只是最低要求。实验报告拿高分和以后做课程设计、毕业设计的差别在于你有没有主动验证系统的边界条件和异常处理路径。我每次做完类似资源里的MIS系统都会强制自己走一遍下面五个测试方向这套方法已经成为我的习惯。第一个方向是重复操作测试。对同一个按钮快速点击两次借书操作是否会插入两条借阅记录我一般会在借书按钮的点击事件中设置一个boolean返回值校验第二次点击时因为第一次已经借出库存条件不满足事务回滚。测试方法是打开两个窗体实例同时操作同一个读者的借书流程观察库存变化。第二个方向是参数边界测试。书名搜索的关键词长度为零、价格筛选的minPrice大于maxPrice、借阅日期在当前日期之后——这些输入虽然在界面上不一定能触发但通过构造HTTP请求或直接调用数据访问层方法可以模拟。我在测试时发现minPrice大于maxPrice时需要前端校验数据访问层也要做一次防御性判断否则返回空结果而不是报错。第三个方向是异常恢复测试。在借书过程中手动杀掉数据库服务观察应用是否抛出友好的错误提示而不是白屏或堆栈信息。真正能用的系统会把SQLException包装成业务异常在界面上显示“操作失败请检查数据库连接”这样明确的反馈。这个测试能暴露你有没有在DAO层做了异常转换。第四个方向是数据一致性测试。借书成功后查看库存在borrow表新增记录是否同步。我的验证脚本是查询borrow表用到的book_id再对应查询book表的stock值相加应该等于原始库存量。如果事务有漏洞这两组数据会不一致。第五个方向是权限和SQL注入测试。在登录页面输入 OR 11 -- 这样的字符串观察系统是否被绕过。使用PreparedStatement之后这个风险基本被堵住了但如果你在排序字段、表名这些位置使用了字符串拼接仍然可能被注入。测试完把这个用例写进实验报告能体现出安全意识。从头做完这一遍我才算对一个MIS系统松一口气。在那之前我总是觉得“能跑就行”结果被测试用例打脸了好几次。特别是事务回滚那一块第一次翻车就是因为在finally里忘了恢复autoCommit导致连接池复用后所有查询都不出数据了。从那以后我每次做完都强制走一遍上述五类测试才敢提交也希望你拿到这份实验报告资源后不只是照抄代码而是把这些验证思路一起吸收进去。希望帮到你。本文还有配套的精品资源点击获取