實(shí)操：JDBC連接MySQL與核心代碼詳解)
不搞花架子直接說(shuō)項(xiàng)目本身。圖書管理系統(tǒng)是Java入門到進(jìn)階階段出現(xiàn)頻率最高的練手項(xiàng)目同時(shí)也是很多計(jì)算機(jī)專業(yè)課程設(shè)計(jì)和畢業(yè)設(shè)計(jì)的首選題目。這個(gè)項(xiàng)目題目里有兩個(gè)關(guān)鍵詞一是完整代碼實(shí)現(xiàn)二是連接MYSQL數(shù)據(jù)庫(kù)。前者說(shuō)明需要的是能直接運(yùn)行、能看邏輯、能復(fù)現(xiàn)的整套代碼而不是零散的功能片段后者說(shuō)明整個(gè)系統(tǒng)的數(shù)據(jù)落點(diǎn)都在MySQL里所有增刪改查都要經(jīng)過真實(shí)的數(shù)據(jù)庫(kù)操作。這篇文章就圍繞這兩件事展開把表結(jié)構(gòu)設(shè)計(jì)、JDBC連接、核心業(yè)務(wù)邏輯、常見坑位全部過一遍適合正在做課設(shè)、準(zhǔn)備Java面試或者想搞清楚數(shù)據(jù)庫(kù)連接細(xì)節(jié)的同學(xué)參考。1. 項(xiàng)目整體設(shè)計(jì)與需求拆解1.1 這個(gè)系統(tǒng)到底在解決什么問題圖書館的日常運(yùn)營(yíng)非常依賴借還書登記、圖書庫(kù)存盤點(diǎn)、讀者借閱記錄查詢這些操作。傳統(tǒng)手寫登記的方式效率低、容易出錯(cuò)管理員想查一本書在哪、誰(shuí)借走了、什么時(shí)候該還都極其痛苦。圖書管理系統(tǒng)做的事情本質(zhì)上就是為管理員提供一套數(shù)字化工具讓圖書信息和借閱記錄全部落庫(kù)通過程序完成信息的增刪改查。從用戶視角來(lái)看最基礎(chǔ)的功能需求包括幾個(gè)方面管理員登錄系統(tǒng)身份校驗(yàn)要可靠。圖書信息的錄入、修改、刪除和按條件查詢。借書操作要能校驗(yàn)庫(kù)存和借閱狀態(tài)。還書操作要能更新庫(kù)存和借閱記錄。借閱記錄的查詢方便追蹤每一本書的去向。標(biāo)題里強(qiáng)調(diào)“連接MYSQL數(shù)據(jù)庫(kù)”意味著這些數(shù)據(jù)不能優(yōu)雅地躺在內(nèi)存里——程序一關(guān)就全沒了而是要永久的、結(jié)構(gòu)化的存在MySQL里。這樣才能保證重啟程序后圖書數(shù)據(jù)還在借書記錄也在系統(tǒng)才有真正的使用價(jià)值。1.2 技術(shù)選型為什么走JDBC直連而不是直接上框架很多人會(huì)問現(xiàn)在都用Spring Boot MyBatis為什么還要用純JDBC手寫連接數(shù)據(jù)庫(kù)我用這個(gè)項(xiàng)目回答你因?yàn)樘^了JDBC直接學(xué)框架你根本不知道數(shù)據(jù)庫(kù)連接背后發(fā)生了什么。JDBC是Java連接數(shù)據(jù)庫(kù)的標(biāo)準(zhǔn)接口MyBatis底層也是封裝了JDBC。你把JDBC這套流程——加載驅(qū)動(dòng)、獲取連接、創(chuàng)建語(yǔ)句、執(zhí)行SQL、處理結(jié)果集、釋放資源——走一遍框架一上手就能看懂它到底在幫你做什么。這個(gè)項(xiàng)目我建議采用“純Java控制臺(tái) JDBC MySQL”的經(jīng)典組合而不是上來(lái)就套JSP、Servlet或者Spring Boot。原因有三控制臺(tái)交互讓注意力集中在SQL語(yǔ)句和業(yè)務(wù)邏輯上不需要分心處理頁(yè)面跳轉(zhuǎn)、表單提交這些Web層的東西。JDBC連接數(shù)據(jù)庫(kù)的過程清晰暴露連接串怎么配、驅(qū)動(dòng)怎么加載、PreparedStatement怎么用全部看得見摸得著。代碼量適中適合一個(gè)人從零實(shí)現(xiàn)還能在面試時(shí)把項(xiàng)目講清楚。這套組合做出來(lái)的代碼結(jié)構(gòu)后續(xù)想遷移到Spring Boot版本時(shí)DAO層和實(shí)體類幾乎可以原封不動(dòng)搬過去改動(dòng)成本很低。1.3 數(shù)據(jù)庫(kù)表結(jié)構(gòu)設(shè)計(jì)三張表?yè)纹鹫麄€(gè)系統(tǒng)數(shù)據(jù)庫(kù)設(shè)計(jì)是這個(gè)項(xiàng)目的根基。我見過不少同學(xué)代碼寫了一半發(fā)現(xiàn)表結(jié)構(gòu)不合理回頭改表又改代碼非常痛苦。設(shè)計(jì)階段多花十分鐘后面能省下半天時(shí)間。整個(gè)系統(tǒng)用三張表就夠用戶表usersid主鍵自增用戶唯一標(biāo)識(shí)。username用戶名登錄時(shí)使用。password密碼落庫(kù)時(shí)存MD5散列值不存明文。role角色標(biāo)識(shí)管理員和普通讀者的權(quán)限區(qū)分。real_name真實(shí)姓名用于顯示借閱人信息。圖書表booksid主鍵自增圖書ID。book_name書名。author作者。publisher出版社。price價(jià)格用DECIMAL(10,2)類型避免浮點(diǎn)誤差。stock庫(kù)存數(shù)量整數(shù)類型。category分類方便按類別篩選。借閱記錄表borrow_recordsid主鍵自增。book_id關(guān)聯(lián)圖書表主鍵。user_id關(guān)聯(lián)用戶表主鍵。borrow_date借書日期。return_date應(yīng)還日期。status借閱狀態(tài)1表示借出中2表示已歸還。外鍵關(guān)系上borrow_records表的book_id和user_id分別指向books和users的主鍵。從表設(shè)計(jì)上保證數(shù)據(jù)可追溯。圖書庫(kù)存的扣減在借書時(shí)同步更新還書時(shí)同步恢復(fù)這個(gè)業(yè)務(wù)邏輯放在后面講。2. 核心代碼實(shí)現(xiàn)與關(guān)鍵細(xì)節(jié)2.1 數(shù)據(jù)庫(kù)連接工具類JDBC連接的全過程不管寫什么功能模塊第一步永遠(yuǎn)是拿到數(shù)據(jù)庫(kù)連接。把所有連接邏輯抽到一個(gè)工具類里是項(xiàng)目初期就該做好的事。來(lái)找JDBC連接的經(jīng)典五步加載驅(qū)動(dòng)類。定義數(shù)據(jù)庫(kù)連接地址URL。DriverManager獲取Connection對(duì)象。通過Connection創(chuàng)建Statement或者PreparedStatement。釋放資源。工具類代碼我直接給出一個(gè)項(xiàng)目里實(shí)測(cè)可用的版本import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class DBUtil { // 數(shù)據(jù)庫(kù)連接地址 private static final String URL jdbc:mysql://localhost:3306/library_db?useSSLfalseserverTimezoneAsia/ShanghaicharacterEncodingutf8; private static final String USERNAME root; private static final String PASSWORD 123456; private static Connection conn null; // 靜態(tài)代碼塊只加載一次驅(qū)動(dòng) static { try { Class.forName(com.mysql.cj.jdbc.Driver); } catch (ClassNotFoundException e) { e.printStackTrace(); } } // 獲取連接 public static Connection getConnection() throws SQLException { if (conn null || conn.isClosed()) { conn DriverManager.getConnection(URL, USERNAME, PASSWORD); } return conn; } // 釋放資源關(guān)閉ResultSet、Statement、Connection public static void close(ResultSet rs, Statement stmt, Connection conn) { try { if (rs ! null) rs.close(); if (stmt ! null) stmt.close(); if (conn ! null) conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }這里有幾個(gè)細(xì)節(jié)值得說(shuō)透驅(qū)動(dòng)類名。MySQL 5.x之前用的是com.mysql.jdbc.DriverMySQL 8.x后驅(qū)動(dòng)類改成了com.mysql.cj.jdbc.Driver。用錯(cuò)會(huì)直接報(bào)ClassNotFoundException這是高頻報(bào)錯(cuò)點(diǎn)。連接串參數(shù)。serverTimezoneAsia/Shanghai解決的是時(shí)區(qū)問題MySQL 8及以上版本服務(wù)器跟本機(jī)時(shí)區(qū)不一致會(huì)報(bào)錯(cuò)。characterEncodingutf8解決中文亂碼。useSSLfalse是關(guān)閉SSL校驗(yàn)本地開發(fā)不需要加密通道不關(guān)可能會(huì)報(bào)SSL連接錯(cuò)誤。靜態(tài)代碼塊 vs 每次獲取連接時(shí)加載驅(qū)動(dòng)。Class.forName加載驅(qū)動(dòng)只需要做一次放進(jìn)static代碼塊里是標(biāo)準(zhǔn)寫法不用每次getConnection都重復(fù)加載。驅(qū)動(dòng)注冊(cè)到DriverManager之后就常駐內(nèi)存了。2.2 用戶登錄模塊密碼校驗(yàn)與SQL注入防護(hù)登錄功能幾乎每個(gè)系統(tǒng)都有但我要在這單獨(dú)拉出來(lái)講是因?yàn)榈卿浤K最容易暴露安全意識(shí)問題。我見過不少項(xiàng)目源碼里用字符串拼接方式執(zhí)行SQL比如String sql SELECT * FROM users WHERE username username AND password password ;這種寫法非常危險(xiǎn)。用戶在用戶名輸入框輸入admin OR 11SQL語(yǔ)句會(huì)變成SELECT * FROM users WHERE username admin OR 11 AND password OR條件讓整條WHERE語(yǔ)句恒為真直接繞過密碼校驗(yàn)登錄成功。這就是最經(jīng)典的SQL注入攻擊幾乎所有數(shù)據(jù)庫(kù)安全類面試題都會(huì)問它。正確的做法是用PreparedStatement預(yù)編譯public User login(String username, String password) { String sql SELECT id, username, password, role, real_name FROM users WHERE username ? AND password ?; try (Connection conn DBUtil.getConnection(); PreparedStatement ps conn.prepareStatement(sql)) { ps.setString(1, username); ps.setString(2, md5(password)); try (ResultSet rs ps.executeQuery()) { if (rs.next()) { User user new User(); user.setId(rs.getInt(id)); user.setUsername(rs.getString(username)); user.setPassword(rs.getString(password)); user.setRole(rs.getString(role)); user.setRealName(rs.getString(real_name)); return user; } } } catch (SQLException e) { e.printStackTrace(); } return null; }注意兩個(gè)細(xì)節(jié)。第一PreparedStatement的參數(shù)用?占位setString傳值時(shí)由MySQL驅(qū)動(dòng)做參數(shù)轉(zhuǎn)義用戶輸入的單引號(hào)會(huì)被當(dāng)作普通字符處理注入語(yǔ)句就失效了。第二密碼存的是MD5散列值md5方法如下private static String md5(String input) { try { MessageDigest md MessageDigest.getInstance(MD5); byte[] bytes md.digest(input.getBytes(UTF-8)); StringBuilder sb new StringBuilder(); for (byte b : bytes) { sb.append(String.format(%02x, b)); } return sb.toString(); } catch (Exception e) { throw new RuntimeException(MD5加密出錯(cuò), e); } }密碼散列化之后的好處是即使數(shù)據(jù)庫(kù)文件泄露別人看到的也是一串固定長(zhǎng)度的32位十六進(jìn)制字符拿不到明文密碼。這讓登錄模塊多了一層安全保障。2.3 圖書管理核心業(yè)務(wù)借書流程和事務(wù)邊界圖書管理系統(tǒng)的核心業(yè)務(wù)流是借書和還書。借書不是簡(jiǎn)單插入一條記錄就完事它涉及多張表的數(shù)據(jù)聯(lián)動(dòng)。以借書為例完整流程是校驗(yàn)用戶存在且角色為讀者。校驗(yàn)圖書存在且?guī)齑娲笥??？蹨p圖書表的庫(kù)存。插入一條借閱記錄。這四個(gè)步驟里任何一步失敗整個(gè)操作都必須回滾。比如庫(kù)存扣減成功了但借閱記錄插入失敗就會(huì)出現(xiàn)庫(kù)存跟實(shí)際不相符的情況——書沒借出去庫(kù)存沒了。這是典型的事務(wù)場(chǎng)景。public synchronized boolean borrowBook(int bookId, int userId) { Connection conn null; PreparedStatement psUpdateStock null; PreparedStatement psInsertRecord null; try { conn DBUtil.getConnection(); // 關(guān)閉自動(dòng)提交開啟事務(wù) conn.setAutoCommit(false); // 查詢庫(kù)存并鎖定記錄 String checkSql SELECT stock FROM books WHERE id ? FOR UPDATE; try (PreparedStatement psCheck conn.prepareStatement(checkSql)) { psCheck.setInt(1, bookId); try (ResultSet rs psCheck.executeQuery()) { if (rs.next()) { int stock rs.getInt(stock); if (stock 0) { throw new RuntimeException(庫(kù)存不足借閱失敗); } } else { throw new RuntimeException(圖書不存在); } } } // 扣減庫(kù)存 String updateSql UPDATE books SET stock stock - 1 WHERE id ?; psUpdateStock conn.prepareStatement(updateSql); psUpdateStock.setInt(1, bookId); psUpdateStock.executeUpdate(); // 插入借閱記錄 String insertSql INSERT INTO borrow_records(book_id, user_id, borrow_date, return_date, status) VALUES(?, ?, NOW(), DATE_ADD(NOW(), INTERVAL 30 DAY), 1); psInsertRecord conn.prepareStatement(insertSql); psInsertRecord.setInt(1, bookId); psInsertRecord.setInt(2, userId); psInsertRecord.executeUpdate(); // 提交事務(wù) conn.commit(); return true; } catch (Exception e) { try { if (conn ! null) conn.rollback(); } catch (SQLException ex) { ex.printStackTrace(); } e.printStackTrace(); return false; } finally { try { if (conn ! null) { conn.setAutoCommit(true); conn.close(); } } catch (SQLException ex) { ex.printStackTrace(); } } }這段代碼有幾個(gè)非常關(guān)鍵的細(xì)節(jié)。conn.setAutoCommit(false)關(guān)閉自動(dòng)提交模式后后續(xù)的SQL不會(huì)立刻生效要等commit才真正寫入數(shù)據(jù)庫(kù)。rollback是回滾讓之前執(zhí)行的SQL全部作廢。關(guān)于行鎖SELECT ... FOR UPDATE它把查詢到的圖書記錄鎖住避免多線程并發(fā)借書時(shí)大家都讀到stock1然后同時(shí)扣減變成負(fù)數(shù)。我自己實(shí)際測(cè)試過去掉FOR UPDATE的情況下用兩個(gè)線程同時(shí)借同一本書且?guī)齑嬷皇?本兩個(gè)請(qǐng)求都可能成功最終庫(kù)存變成-1。加了行鎖之后第二個(gè)請(qǐng)求會(huì)阻塞等待第一個(gè)事務(wù)提交提交后重新讀取庫(kù)存發(fā)現(xiàn)已經(jīng)是0直接拋異常這才是正確行為。2.4 還書流程別忽略狀態(tài)校驗(yàn)還書邏輯比借書簡(jiǎn)單但有一個(gè)很容易踩的坑不校驗(yàn)借閱記錄的狀態(tài)就直接更新。public boolean returnBook(int recordId) { String sql UPDATE borrow_records SET status 2 WHERE id ? AND status 1; try (Connection conn DBUtil.getConnection(); PreparedStatement ps conn.prepareStatement(sql)) { ps.setInt(1, recordId); int rows ps.executeUpdate(); if (rows 0) { // 恢復(fù)庫(kù)存 String updateStockSql UPDATE books b JOIN borrow_records r ON b.id r.book_id SET b.stock b.stock 1 WHERE r.id ?; try (PreparedStatement ps2 conn.prepareStatement(updateStockSql)) { ps2.setInt(1, recordId); ps2.executeUpdate(); } return true; } } catch (SQLException e) { e.printStackTrace(); } return false; }SQL里帶AND status 1確保只有借出中的記錄才能執(zhí)行歸還。如果記錄已經(jīng)是已歸還狀態(tài)UPDATE影響行數(shù)為0不會(huì)出現(xiàn)重復(fù)恢復(fù)庫(kù)存的情況。3. 完整實(shí)操?gòu)慕◣?kù)建表到跑通項(xiàng)目3.1 環(huán)境準(zhǔn)備MySQL 8 IDEA 驅(qū)動(dòng)動(dòng)手之前先把環(huán)境準(zhǔn)備好。我以本機(jī)開發(fā)為例說(shuō)明每一步需要做什么。第一步安裝MySQL 8。Windows環(huán)境下直接去MySQL官網(wǎng)下載MySQL Installer只安裝Server和Command Line Client。安裝過程中的用戶名密碼記好默認(rèn)root用戶需要設(shè)置一個(gè)自己的密碼。安裝完成后在命令行用mysql -u root -p驗(yàn)證能登錄。第二步安裝Navicat或者直接用MySQL自帶的Workbench。Navicat勝在操作直觀建庫(kù)建表看數(shù)據(jù)都方便有條件的話可以用它快速核對(duì)數(shù)據(jù)是否寫入成功。第三步IDEA創(chuàng)建Java項(xiàng)目引入連接驅(qū)動(dòng)。兩種方式任選下載mysql-connector-java的jar包放在項(xiàng)目lib目錄下右鍵選擇Add as Library。這個(gè)方式不需要額外的構(gòu)建工具。Maven項(xiàng)目在pom.xml里添加依賴dependency groupIdmysql/groupId artifactIdmysql-connector-java/artifactId version8.0.33/version /dependency我用的是MySQL 8.0系列所以驅(qū)動(dòng)版本選8.0.33跟服務(wù)器兼容很穩(wěn)。如果你的MySQL是5.7及以下版本驅(qū)動(dòng)選5.1.x系列更省事。3.2 建庫(kù)建表與初始化數(shù)據(jù)打開Navicat新建數(shù)據(jù)庫(kù)library_db字符集選utf8mb4。用以下幾個(gè)建表語(yǔ)句直接執(zhí)行CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) NOT NULL UNIQUE, password CHAR(32) NOT NULL, role VARCHAR(10) NOT NULL DEFAULT user, real_name VARCHAR(50) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; CREATE TABLE books ( id INT AUTO_INCREMENT PRIMARY KEY, book_name VARCHAR(100) NOT NULL, author VARCHAR(50) DEFAULT NULL, publisher VARCHAR(100) DEFAULT NULL, price DECIMAL(10,2) DEFAULT 0.00, stock INT NOT NULL DEFAULT 0, category VARCHAR(50) DEFAULT NULL ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; CREATE TABLE borrow_records ( id INT AUTO_INCREMENT PRIMARY KEY, book_id INT NOT NULL, user_id INT NOT NULL, borrow_date DATETIME NOT NULL, return_date DATETIME DEFAULT NULL, status TINYINT NOT NULL DEFAULT 1, CONSTRAINT fk_book FOREIGN KEY (book_id) REFERENCES books(id), CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;初始化數(shù)據(jù)也一并寫入方便程序啟動(dòng)后有東西可操作INSERT INTO users(id, username, password, role, real_name) VALUES (NULL, admin, MD5(123456), admin, 系統(tǒng)管理員), (NULL, zhangsan, MD5(abc123), user, 張三); INSERT INTO books(book_name, author, publisher, price, stock, category) VALUES (Java核心技術(shù)卷I, 凱·S·霍斯特曼, 機(jī)械工業(yè)出版社, 119.00, 5, 計(jì)算機(jī)), (深入理解Java虛擬機(jī), 周志明, 機(jī)械工業(yè)出版社, 129.00, 3, 計(jì)算機(jī)), (MySQL必知必會(huì), Ben Forta, 人民郵電出版社, 59.00, 8, 數(shù)據(jù)庫(kù)), (活著, 余華, 作家出版社, 35.00, 10, 文學(xué));有一點(diǎn)需要提前說(shuō)清楚MD5(123456)這個(gè)函數(shù)在MySQL 8中默認(rèn)是可用的生成的密文存進(jìn)去之后Java代碼里用md5方法對(duì)用戶輸入的密碼加密再比對(duì)兩邊算法一致才能匹配。我自己踩過初始化數(shù)據(jù)里直接寫明文密碼的坑。初期偷懶把password字段存成123456登錄驗(yàn)證時(shí)也拿明文去比較數(shù)據(jù)庫(kù)里全是裸奔的用戶密碼。后來(lái)項(xiàng)目做完統(tǒng)一改用MD5才把這個(gè)問題處理干凈。建議從項(xiàng)目第一版開始就養(yǎng)成不落明文密碼的習(xí)慣。3.3 項(xiàng)目結(jié)構(gòu)搭建分包劃分與實(shí)體類設(shè)計(jì)代碼不是堆在一個(gè)類里就能叫完整項(xiàng)目。合理的包結(jié)構(gòu)讓代碼后期可維護(hù)也更容易講明白系統(tǒng)架構(gòu)。我建議用以下分包方式com.library ├── entity/ # 實(shí)體類 │ ├── User.java │ ├── Book.java │ └── BorrowRecord.java ├── util/ # 工具類 │ └── DBUtil.java ├── dao/ # 數(shù)據(jù)訪問層 │ ├── UserDAO.java │ ├── BookDAO.java │ └── BorrowDAO.java ├── service/ # 業(yè)務(wù)邏輯層 │ ├── UserService.java │ ├── BookService.java │ └── BorrowService.java └── ui/ # 界面展示與交互 └── MainMenu.java分層的好處是界面層只負(fù)責(zé)接收輸入和展示結(jié)果業(yè)務(wù)層處理業(yè)務(wù)規(guī)則DAO層只能跟數(shù)據(jù)庫(kù)打交道。舉個(gè)實(shí)際場(chǎng)景界面層需要顯示“借書成功”但它不應(yīng)該直接寫SQL而是調(diào)用service層的方法service內(nèi)部組織多個(gè)DAO方法協(xié)同完成借書流程。這樣改動(dòng)界面不影響數(shù)據(jù)庫(kù)代碼替換數(shù)據(jù)庫(kù)連接方式也不影響界面。實(shí)體類以Book為例public class Book { private int id; private String bookName; private String author; private String publisher; private BigDecimal price; private int stock; private String category; // 省略getter和setter方法 }價(jià)格字段用BigDecimal而不是doubledouble在數(shù)據(jù)庫(kù)和Java之間來(lái)回轉(zhuǎn)換的時(shí)候浮點(diǎn)誤差會(huì)讓人抓狂。數(shù)據(jù)庫(kù)DECIMAL對(duì)應(yīng)Java的BigDecimal這是標(biāo)準(zhǔn)對(duì)應(yīng)關(guān)系。3.4 圖書列表查詢分頁(yè)與排序?qū)嵅賵D書列表的展示不只是一條SELECT那么簡(jiǎn)單。系統(tǒng)里的圖書數(shù)量一旦上到幾十本控制臺(tái)一屏打不完前端展示的體驗(yàn)就很差。分頁(yè)是必須有的能力。public ListBook queryBooksByPage(int pageNum, int pageSize, String keyword) { ListBook bookList new ArrayList(); int offset (pageNum - 1) * pageSize; String sql SELECT id, book_name, author, publisher, price, stock, category FROM books; // 關(guān)鍵字搜索增強(qiáng)體驗(yàn) if (keyword ! null !keyword.isEmpty()) { sql WHERE book_name LIKE ?; } sql ORDER BY id LIMIT ? OFFSET ?; try (Connection conn DBUtil.getConnection(); PreparedStatement ps conn.prepareStatement(sql)) { int paramIndex 1; if (keyword ! null !keyword.isEmpty()) { ps.setString(paramIndex, % keyword %); } ps.setInt(paramIndex, pageSize); ps.setInt(paramIndex, offset); try (ResultSet rs ps.executeQuery()) { while (rs.next()) { Book book new Book(); book.setId(rs.getInt(id)); book.setBookName(rs.getString(book_name)); book.setAuthor(rs.getString(author)); book.setPublisher(rs.getString(publisher)); book.setPrice(rs.getBigDecimal(price)); book.setStock(rs.getInt(stock)); book.setCategory(rs.getString(category)); bookList.add(book); } } } catch (SQLException e) { e.printStackTrace(); } return bookList; }ORDER BY id LIMIT ? OFFSET ?是MySQL分頁(yè)的標(biāo)準(zhǔn)姿勢(shì)。offset的算法是(pageNum - 1) * pageSize第一頁(yè)偏移0條第二頁(yè)偏移pageSize條很容易理解。LIKE關(guān)鍵字搜索時(shí)預(yù)編譯的?參數(shù)值直接拼上%即可搜書名、作者、分類都通用。關(guān)于LIKE查詢有個(gè)性能細(xì)節(jié)如果數(shù)據(jù)量大LIKE %關(guān)鍵字%這種前置模糊匹配是走不了索引的全表掃描。但圖書管理系統(tǒng)這種量級(jí)的數(shù)據(jù)完全夠用不需要過度優(yōu)化。如果想優(yōu)化可以引入Elasticsearch或者M(jìn)ySQL全文索引那是后話。排序方面同樣用ORDER BY實(shí)現(xiàn)熱門借閱排名可以這樣查String sql SELECT b.id, b.book_name, COUNT(r.id) AS borrow_count FROM books b LEFT JOIN borrow_records r ON b.id r.book_id GROUP BY b.id, b.book_name ORDER BY borrow_count DESC;LEFT JOIN保證一本都沒被借過的圖書也能出現(xiàn)在列表里借書次數(shù)為0。COUNT(r.id)統(tǒng)計(jì)每本書的借閱記錄條數(shù)按降序排列得到熱門圖書排行。這個(gè)SQL在面試?yán)镆步?jīng)常被問到JOIN和GROUP BY的組合使用。4. 常見問題與排查技巧實(shí)錄4.1 數(shù)據(jù)庫(kù)連接失敗驅(qū)動(dòng)、時(shí)區(qū)、權(quán)限三類大坑連接數(shù)據(jù)庫(kù)是整套系統(tǒng)最容易出問題的地方而問題基本集中在三個(gè)方向第一類是ClassNotFoundException報(bào)錯(cuò)內(nèi)容是ClassNotFoundException: com.mysql.jdbc.Driver。這個(gè)100%是驅(qū)動(dòng)類名或者驅(qū)動(dòng)jar包的問題。MySQL 8.x的驅(qū)動(dòng)類名是com.mysql.cj.jdbc.Driver你還在用com.mysql.jdbc.Driver就會(huì)報(bào)這個(gè)錯(cuò)。另外確認(rèn)引用的jar包版本高版本驅(qū)動(dòng)確認(rèn)類名寫對(duì)了沒。第二類是時(shí)區(qū)錯(cuò)誤報(bào)錯(cuò)一般是The server time zone value й?? is unrecognized。原因是MySQL 8服務(wù)器默認(rèn)使用系統(tǒng)時(shí)區(qū)而JDBC連接串沒有指定時(shí)區(qū)兩端對(duì)不上。解決方式是在URL追加serverTimezoneAsia/Shanghai。第三類是Public Key Retrieval報(bào)錯(cuò)MySQL 8的默認(rèn)認(rèn)證方式是caching_sha2_password客戶端連接時(shí)需要從服務(wù)端獲取公鑰做加密JDBC默認(rèn)行為不允許這個(gè)操作。URL里加useSSLfalseallowPublicKeyRetrievaltrue即可解決。我自己在項(xiàng)目聯(lián)調(diào)階段把這三類錯(cuò)誤輪著遇了一遍總結(jié)成一條經(jīng)驗(yàn)連接串務(wù)必一次性寫完整不要遇到一個(gè)錯(cuò)改一處太折騰。4.2 中文亂碼三層設(shè)置缺一不可中文亂碼問題出現(xiàn)得相當(dāng)頻繁而且一亂就是一大片。排查要按三層來(lái)檢查第一層數(shù)據(jù)庫(kù)。建庫(kù)和建表時(shí)指定CHARSETutf8mb4就是告訴MySQL這個(gè)數(shù)據(jù)庫(kù)要存unicode字符中文完全沒問題。忘記指定的可以用ALTER TABLE命令補(bǔ)ALTER TABLE books CONVERT TO CHARACTER SET utf8mb4;第二層JDBC連接串。URL必須帶characterEncodingutf8注意這里跟數(shù)據(jù)庫(kù)里character_set_database的配置匹配起來(lái)。第三層控制臺(tái)輸出。Windows下IDEA的控制臺(tái)默認(rèn)編碼可能不是UTF-8在IDEA的Settings里把Global Encoding、Project Encoding、Console的編碼都設(shè)為UTF-8。命令行運(yùn)行時(shí)在編譯和運(yùn)行命令加上-Dfile.encodingUTF-8參數(shù)。這三個(gè)地方只要有一個(gè)不對(duì)柜臺(tái)就會(huì)顯示一排煩人的問號(hào)。連續(xù)探查這個(gè)問題的經(jīng)驗(yàn)教訓(xùn)是先確認(rèn)數(shù)據(jù)庫(kù)存進(jìn)去的中文是不是正常的用Navicat直接看表。庫(kù)里是亂的就是數(shù)據(jù)庫(kù)建表和連接串的問題庫(kù)是正常的但程序顯示亂就是控制臺(tái)和JVM編碼的問題。4.3 業(yè)務(wù)邏輯Bug排查事務(wù)沒提交、庫(kù)存負(fù)數(shù)、并發(fā)沖突數(shù)據(jù)庫(kù)連接層打通之后業(yè)務(wù)層的坑就輪到在運(yùn)行時(shí)慢慢暴露了。我整理一下最常遇到的幾個(gè)不執(zhí)行commit就斷言數(shù)據(jù)寫進(jìn)去了。忘了setAutoCommit(false)之后一定要手動(dòng)commit或者寫代碼的時(shí)候根本沒意識(shí)到事務(wù)已經(jīng)開啟數(shù)據(jù)在另一個(gè)連接里死活看不到更新。排查方式確認(rèn)是否有事務(wù)開啟查到commit或rollback了嗎。筆記本還剩0本借書還能成功。這是庫(kù)存校驗(yàn)缺失導(dǎo)致的。解決方式在前面borrowBook方法里講過校驗(yàn)和扣減必須在同一事務(wù)里用行鎖保護(hù)絕不能在SELECT和UPDATE之間留出空檔。對(duì)了損壞數(shù)據(jù)還可能來(lái)自重復(fù)借同一本書。如果借閱表里已經(jīng)有一條status1的同圖書同用戶記錄新借書請(qǐng)求不應(yīng)該再成功。在插入新借閱記錄前查詢?cè)撚脩粼搱D書是否存在借出中的記錄即可。4.4 資源泄漏連接不關(guān)系統(tǒng)遲早卡到死這個(gè)問題雖然常見但在新手代碼里幾乎是批量出現(xiàn)。寫完Connection不去close獲取了ResultSet不去close程序跑不了幾個(gè)來(lái)回就報(bào)Too many connections。MySQL默認(rèn)連接上限是100多個(gè)連接不釋放就會(huì)把連接池占滿。我建議把所有資源關(guān)閉統(tǒng)一放到finally代碼塊里或者直接使用JDK 7引入的try-with-resources語(yǔ)法。try (Connection conn DBUtil.getConnection(); PreparedStatement ps conn.prepareStatement(sql)) { ... } catch (SQLException e) { e.printStackTrace(); }用了try-with-resources之后Connection、Statement、ResultSet只要實(shí)現(xiàn)了AutoCloseable接口就會(huì)在try塊結(jié)束后自動(dòng)關(guān)閉代碼少而且不容易遺漏。如果用的是我前面寫的DBUtilclose方法也要在finally里確保調(diào)用。真實(shí)項(xiàng)目中連接不關(guān)閉屬于那種“今天不出事、明天不出事、上線第三天就出事”的問題。你測(cè)試的時(shí)候數(shù)據(jù)量小加上連接偶爾被GC回收感受不到嚴(yán)重性。等系統(tǒng)真的被多個(gè)人同時(shí)用起來(lái)連接數(shù)飆升服務(wù)直接就癱了。5. 項(xiàng)目擴(kuò)展方向與面試講解建議這個(gè)項(xiàng)目做完能跑通只是第一步。真正讓項(xiàng)目有價(jià)值的是你能夠擴(kuò)展它并且在面試時(shí)把它講成亮點(diǎn)。我給四個(gè)擴(kuò)展方向的思路。第一個(gè)方向是加上圖形界面?？刂婆_(tái)版本的邏輯代碼是完整的只是交互方式比較簡(jiǎn)陋?？梢杂肑ava Swing或JavaFX做界面把MainMenu替換成登錄窗口和主窗口BookDAO、UserDAO這些類不需要大改。這樣就變成了一個(gè)更像“系統(tǒng)”的東西。第二個(gè)方向是升級(jí)Web版本。把項(xiàng)目改造成Spring Boot MyBatis Vue的前后端分離版本數(shù)據(jù)庫(kù)表結(jié)構(gòu)幾乎原封不動(dòng)DAO層代碼翻譯成MyBatis的Mapper接口。這個(gè)升級(jí)過程你自然就理解了框架到底幫你做了什么。第三個(gè)方向是補(bǔ)充還書逾期和罰款邏輯。在BorrowRecord里加一個(gè)shouldReturnDate的字段還書時(shí)用當(dāng)前日期跟應(yīng)還日期比較超期天數(shù)乘以每日罰款金額算出一個(gè)逾期費(fèi)用記錄這樣業(yè)務(wù)面就比普通的課設(shè)項(xiàng)目完整很多。第四個(gè)方向是權(quán)限體系的強(qiáng)化。讀者只能看到自己的借閱記錄管理員可以看全部借書也有容量限制這些都用role字段和SQL條件就能實(shí)現(xiàn)。給面試講項(xiàng)目的時(shí)候不要停留在“我寫了一個(gè)圖書增刪改查的系統(tǒng)”這種層面。重點(diǎn)講三件事數(shù)據(jù)庫(kù)表怎么設(shè)計(jì)的表間關(guān)系是什么JDBC連接MySQL踩過哪些坑怎么定位和解決的借書的庫(kù)存扣減和借閱記錄的寫入為什么放在一個(gè)事務(wù)里。把這些問題想透了比堆砌功能列表管用得多。我個(gè)人在帶項(xiàng)目時(shí)最常跟新手說(shuō)的一句話是能跑通只是代碼的及格線知道自己為什么能跑通才算是學(xué)會(huì)。這個(gè)項(xiàng)目做完你至少應(yīng)該能回答為什么用PreparedStatement而不是Statement為什么借書要用事務(wù)為什么庫(kù)存要加行鎖為什么密碼要加密存儲(chǔ)。能在紙上畫出三張表的關(guān)系能說(shuō)出連接MySQL的URL各個(gè)參數(shù)的含義。這些才是這個(gè)項(xiàng)目真正的收獲。最后再分享一個(gè)我在調(diào)試階段的小習(xí)慣把SQL語(yǔ)句先在Navicat里跑一遍確認(rèn)結(jié)果集返回正確了再回到Java代碼里聯(lián)調(diào)。數(shù)據(jù)庫(kù)查詢這層走通了代碼層的排查范圍瞬間縮小問題定位效率非常高。這個(gè)習(xí)慣我用在所有的數(shù)據(jù)庫(kù)項(xiàng)目里實(shí)測(cè)下來(lái)很穩(wěn)。 Java圖書管理系統(tǒng)實(shí)操：JDBC連接MySQL與核心代碼詳解
不搞花架子直接說(shuō)項(xiàng)目本身。圖書管理系統(tǒng)是Java入門到進(jìn)階階段出現(xiàn)頻率最高的練手項(xiàng)目同時(shí)也是很多計(jì)算機(jī)專業(yè)課程設(shè)計(jì)和畢業(yè)設(shè)計(jì)的首選題目。這個(gè)項(xiàng)目題目里有兩個(gè)關(guān)鍵詞一是完整代碼實(shí)現(xiàn)二是連接MYSQL數(shù)據(jù)庫(kù)。前者說(shuō)明需要的是能直接運(yùn)行、能看邏輯、能復(fù)現(xiàn)的整套代碼而不是零散的功能片段后者說(shuō)明整個(gè)系統(tǒng)的數(shù)據(jù)落點(diǎn)都在MySQL里所有增刪改查都要經(jīng)過真實(shí)的數(shù)據(jù)庫(kù)操作。這篇文章就圍繞這兩件事展開把表結(jié)構(gòu)設(shè)計(jì)、JDBC連接、核心業(yè)務(wù)邏輯、常見坑位全部過一遍適合正在做課設(shè)、準(zhǔn)備Java面試或者想搞清楚數(shù)據(jù)庫(kù)連接細(xì)節(jié)的同學(xué)參考。1. 項(xiàng)目整體設(shè)計(jì)與需求拆解1.1 這個(gè)系統(tǒng)到底在解決什么問題圖書館的日常運(yùn)營(yíng)非常依賴借還書登記、圖書庫(kù)存盤點(diǎn)、讀者借閱記錄查詢這些操作。傳統(tǒng)手寫登記的方式效率低、容易出錯(cuò)管理員想查一本書在哪、誰(shuí)借走了、什么時(shí)候該還都極其痛苦。圖書管理系統(tǒng)做的事情本質(zhì)上就是為管理員提供一套數(shù)字化工具讓圖書信息和借閱記錄全部落庫(kù)通過程序完成信息的增刪改查。從用戶視角來(lái)看最基礎(chǔ)的功能需求包括幾個(gè)方面管理員登錄系統(tǒng)身份校驗(yàn)要可靠。圖書信息的錄入、修改、刪除和按條件查詢。借書操作要能校驗(yàn)庫(kù)存和借閱狀態(tài)。還書操作要能更新庫(kù)存和借閱記錄。借閱記錄的查詢方便追蹤每一本書的去向。標(biāo)題里強(qiáng)調(diào)“連接MYSQL數(shù)據(jù)庫(kù)”意味著這些數(shù)據(jù)不能優(yōu)雅地躺在內(nèi)存里——程序一關(guān)就全沒了而是要永久的、結(jié)構(gòu)化的存在MySQL里。這樣才能保證重啟程序后圖書數(shù)據(jù)還在借書記錄也在系統(tǒng)才有真正的使用價(jià)值。1.2 技術(shù)選型為什么走JDBC直連而不是直接上框架很多人會(huì)問現(xiàn)在都用Spring Boot MyBatis為什么還要用純JDBC手寫連接數(shù)據(jù)庫(kù)我用這個(gè)項(xiàng)目回答你因?yàn)樘^了JDBC直接學(xué)框架你根本不知道數(shù)據(jù)庫(kù)連接背后發(fā)生了什么。JDBC是Java連接數(shù)據(jù)庫(kù)的標(biāo)準(zhǔn)接口MyBatis底層也是封裝了JDBC。你把JDBC這套流程——加載驅(qū)動(dòng)、獲取連接、創(chuàng)建語(yǔ)句、執(zhí)行SQL、處理結(jié)果集、釋放資源——走一遍框架一上手就能看懂它到底在幫你做什么。這個(gè)項(xiàng)目我建議采用“純Java控制臺(tái) JDBC MySQL”的經(jīng)典組合而不是上來(lái)就套JSP、Servlet或者Spring Boot。原因有三控制臺(tái)交互讓注意力集中在SQL語(yǔ)句和業(yè)務(wù)邏輯上不需要分心處理頁(yè)面跳轉(zhuǎn)、表單提交這些Web層的東西。JDBC連接數(shù)據(jù)庫(kù)的過程清晰暴露連接串怎么配、驅(qū)動(dòng)怎么加載、PreparedStatement怎么用全部看得見摸得著。代碼量適中適合一個(gè)人從零實(shí)現(xiàn)還能在面試時(shí)把項(xiàng)目講清楚。這套組合做出來(lái)的代碼結(jié)構(gòu)后續(xù)想遷移到Spring Boot版本時(shí)DAO層和實(shí)體類幾乎可以原封不動(dòng)搬過去改動(dòng)成本很低。1.3 數(shù)據(jù)庫(kù)表結(jié)構(gòu)設(shè)計(jì)三張表?yè)纹鹫麄€(gè)系統(tǒng)數(shù)據(jù)庫(kù)設(shè)計(jì)是這個(gè)項(xiàng)目的根基。我見過不少同學(xué)代碼寫了一半發(fā)現(xiàn)表結(jié)構(gòu)不合理回頭改表又改代碼非常痛苦。設(shè)計(jì)階段多花十分鐘后面能省下半天時(shí)間。整個(gè)系統(tǒng)用三張表就夠用戶表usersid主鍵自增用戶唯一標(biāo)識(shí)。username用戶名登錄時(shí)使用。password密碼落庫(kù)時(shí)存MD5散列值不存明文。role角色標(biāo)識(shí)管理員和普通讀者的權(quán)限區(qū)分。real_name真實(shí)姓名用于顯示借閱人信息。圖書表booksid主鍵自增圖書ID。book_name書名。author作者。publisher出版社。price價(jià)格用DECIMAL(10,2)類型避免浮點(diǎn)誤差。stock庫(kù)存數(shù)量整數(shù)類型。category分類方便按類別篩選。借閱記錄表borrow_recordsid主鍵自增。book_id關(guān)聯(lián)圖書表主鍵。user_id關(guān)聯(lián)用戶表主鍵。borrow_date借書日期。return_date應(yīng)還日期。status借閱狀態(tài)1表示借出中2表示已歸還。外鍵關(guān)系上borrow_records表的book_id和user_id分別指向books和users的主鍵。從表設(shè)計(jì)上保證數(shù)據(jù)可追溯。圖書庫(kù)存的扣減在借書時(shí)同步更新還書時(shí)同步恢復(fù)這個(gè)業(yè)務(wù)邏輯放在后面講。2. 核心代碼實(shí)現(xiàn)與關(guān)鍵細(xì)節(jié)2.1 數(shù)據(jù)庫(kù)連接工具類JDBC連接的全過程不管寫什么功能模塊第一步永遠(yuǎn)是拿到數(shù)據(jù)庫(kù)連接。把所有連接邏輯抽到一個(gè)工具類里是項(xiàng)目初期就該做好的事。來(lái)找JDBC連接的經(jīng)典五步加載驅(qū)動(dòng)類。定義數(shù)據(jù)庫(kù)連接地址URL。DriverManager獲取Connection對(duì)象。通過Connection創(chuàng)建Statement或者PreparedStatement。釋放資源。工具類代碼我直接給出一個(gè)項(xiàng)目里實(shí)測(cè)可用的版本import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class DBUtil { // 數(shù)據(jù)庫(kù)連接地址 private static final String URL jdbc:mysql://localhost:3306/library_db?useSSLfalseserverTimezoneAsia/ShanghaicharacterEncodingutf8; private static final String USERNAME root; private static final String PASSWORD 123456; private static Connection conn null; // 靜態(tài)代碼塊只加載一次驅(qū)動(dòng) static { try { Class.forName(com.mysql.cj.jdbc.Driver); } catch (ClassNotFoundException e) { e.printStackTrace(); } } // 獲取連接 public static Connection getConnection() throws SQLException { if (conn null || conn.isClosed()) { conn DriverManager.getConnection(URL, USERNAME, PASSWORD); } return conn; } // 釋放資源關(guān)閉ResultSet、Statement、Connection public static void close(ResultSet rs, Statement stmt, Connection conn) { try { if (rs ! null) rs.close(); if (stmt ! null) stmt.close(); if (conn ! null) conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }這里有幾個(gè)細(xì)節(jié)值得說(shuō)透驅(qū)動(dòng)類名。MySQL 5.x之前用的是com.mysql.jdbc.DriverMySQL 8.x后驅(qū)動(dòng)類改成了com.mysql.cj.jdbc.Driver。用錯(cuò)會(huì)直接報(bào)ClassNotFoundException這是高頻報(bào)錯(cuò)點(diǎn)。連接串參數(shù)。serverTimezoneAsia/Shanghai解決的是時(shí)區(qū)問題MySQL 8及以上版本服務(wù)器跟本機(jī)時(shí)區(qū)不一致會(huì)報(bào)錯(cuò)。characterEncodingutf8解決中文亂碼。useSSLfalse是關(guān)閉SSL校驗(yàn)本地開發(fā)不需要加密通道不關(guān)可能會(huì)報(bào)SSL連接錯(cuò)誤。靜態(tài)代碼塊 vs 每次獲取連接時(shí)加載驅(qū)動(dòng)。Class.forName加載驅(qū)動(dòng)只需要做一次放進(jìn)static代碼塊里是標(biāo)準(zhǔn)寫法不用每次getConnection都重復(fù)加載。驅(qū)動(dòng)注冊(cè)到DriverManager之后就常駐內(nèi)存了。2.2 用戶登錄模塊密碼校驗(yàn)與SQL注入防護(hù)登錄功能幾乎每個(gè)系統(tǒng)都有但我要在這單獨(dú)拉出來(lái)講是因?yàn)榈卿浤K最容易暴露安全意識(shí)問題。我見過不少項(xiàng)目源碼里用字符串拼接方式執(zhí)行SQL比如String sql SELECT * FROM users WHERE username username AND password password ;這種寫法非常危險(xiǎn)。用戶在用戶名輸入框輸入admin OR 11SQL語(yǔ)句會(huì)變成SELECT * FROM users WHERE username admin OR 11 AND password OR條件讓整條WHERE語(yǔ)句恒為真直接繞過密碼校驗(yàn)登錄成功。這就是最經(jīng)典的SQL注入攻擊幾乎所有數(shù)據(jù)庫(kù)安全類面試題都會(huì)問它。正確的做法是用PreparedStatement預(yù)編譯public User login(String username, String password) { String sql SELECT id, username, password, role, real_name FROM users WHERE username ? AND password ?; try (Connection conn DBUtil.getConnection(); PreparedStatement ps conn.prepareStatement(sql)) { ps.setString(1, username); ps.setString(2, md5(password)); try (ResultSet rs ps.executeQuery()) { if (rs.next()) { User user new User(); user.setId(rs.getInt(id)); user.setUsername(rs.getString(username)); user.setPassword(rs.getString(password)); user.setRole(rs.getString(role)); user.setRealName(rs.getString(real_name)); return user; } } } catch (SQLException e) { e.printStackTrace(); } return null; }注意兩個(gè)細(xì)節(jié)。第一PreparedStatement的參數(shù)用?占位setString傳值時(shí)由MySQL驅(qū)動(dòng)做參數(shù)轉(zhuǎn)義用戶輸入的單引號(hào)會(huì)被當(dāng)作普通字符處理注入語(yǔ)句就失效了。第二密碼存的是MD5散列值md5方法如下private static String md5(String input) { try { MessageDigest md MessageDigest.getInstance(MD5); byte[] bytes md.digest(input.getBytes(UTF-8)); StringBuilder sb new StringBuilder(); for (byte b : bytes) { sb.append(String.format(%02x, b)); } return sb.toString(); } catch (Exception e) { throw new RuntimeException(MD5加密出錯(cuò), e); } }密碼散列化之后的好處是即使數(shù)據(jù)庫(kù)文件泄露別人看到的也是一串固定長(zhǎng)度的32位十六進(jìn)制字符拿不到明文密碼。這讓登錄模塊多了一層安全保障。2.3 圖書管理核心業(yè)務(wù)借書流程和事務(wù)邊界圖書管理系統(tǒng)的核心業(yè)務(wù)流是借書和還書。借書不是簡(jiǎn)單插入一條記錄就完事它涉及多張表的數(shù)據(jù)聯(lián)動(dòng)。以借書為例完整流程是校驗(yàn)用戶存在且角色為讀者。校驗(yàn)圖書存在且?guī)齑娲笥??？蹨p圖書表的庫(kù)存。插入一條借閱記錄。這四個(gè)步驟里任何一步失敗整個(gè)操作都必須回滾。比如庫(kù)存扣減成功了但借閱記錄插入失敗就會(huì)出現(xiàn)庫(kù)存跟實(shí)際不相符的情況——書沒借出去庫(kù)存沒了。這是典型的事務(wù)場(chǎng)景。public synchronized boolean borrowBook(int bookId, int userId) { Connection conn null; PreparedStatement psUpdateStock null; PreparedStatement psInsertRecord null; try { conn DBUtil.getConnection(); // 關(guān)閉自動(dòng)提交開啟事務(wù) conn.setAutoCommit(false); // 查詢庫(kù)存并鎖定記錄 String checkSql SELECT stock FROM books WHERE id ? FOR UPDATE; try (PreparedStatement psCheck conn.prepareStatement(checkSql)) { psCheck.setInt(1, bookId); try (ResultSet rs psCheck.executeQuery()) { if (rs.next()) { int stock rs.getInt(stock); if (stock 0) { throw new RuntimeException(庫(kù)存不足借閱失敗); } } else { throw new RuntimeException(圖書不存在); } } } // 扣減庫(kù)存 String updateSql UPDATE books SET stock stock - 1 WHERE id ?; psUpdateStock conn.prepareStatement(updateSql); psUpdateStock.setInt(1, bookId); psUpdateStock.executeUpdate(); // 插入借閱記錄 String insertSql INSERT INTO borrow_records(book_id, user_id, borrow_date, return_date, status) VALUES(?, ?, NOW(), DATE_ADD(NOW(), INTERVAL 30 DAY), 1); psInsertRecord conn.prepareStatement(insertSql); psInsertRecord.setInt(1, bookId); psInsertRecord.setInt(2, userId); psInsertRecord.executeUpdate(); // 提交事務(wù) conn.commit(); return true; } catch (Exception e) { try { if (conn ! null) conn.rollback(); } catch (SQLException ex) { ex.printStackTrace(); } e.printStackTrace(); return false; } finally { try { if (conn ! null) { conn.setAutoCommit(true); conn.close(); } } catch (SQLException ex) { ex.printStackTrace(); } } }這段代碼有幾個(gè)非常關(guān)鍵的細(xì)節(jié)。conn.setAutoCommit(false)關(guān)閉自動(dòng)提交模式后后續(xù)的SQL不會(huì)立刻生效要等commit才真正寫入數(shù)據(jù)庫(kù)。rollback是回滾讓之前執(zhí)行的SQL全部作廢。關(guān)于行鎖SELECT ... FOR UPDATE它把查詢到的圖書記錄鎖住避免多線程并發(fā)借書時(shí)大家都讀到stock1然后同時(shí)扣減變成負(fù)數(shù)。我自己實(shí)際測(cè)試過去掉FOR UPDATE的情況下用兩個(gè)線程同時(shí)借同一本書且?guī)齑嬷皇?本兩個(gè)請(qǐng)求都可能成功最終庫(kù)存變成-1。加了行鎖之后第二個(gè)請(qǐng)求會(huì)阻塞等待第一個(gè)事務(wù)提交提交后重新讀取庫(kù)存發(fā)現(xiàn)已經(jīng)是0直接拋異常這才是正確行為。2.4 還書流程別忽略狀態(tài)校驗(yàn)還書邏輯比借書簡(jiǎn)單但有一個(gè)很容易踩的坑不校驗(yàn)借閱記錄的狀態(tài)就直接更新。public boolean returnBook(int recordId) { String sql UPDATE borrow_records SET status 2 WHERE id ? AND status 1; try (Connection conn DBUtil.getConnection(); PreparedStatement ps conn.prepareStatement(sql)) { ps.setInt(1, recordId); int rows ps.executeUpdate(); if (rows 0) { // 恢復(fù)庫(kù)存 String updateStockSql UPDATE books b JOIN borrow_records r ON b.id r.book_id SET b.stock b.stock 1 WHERE r.id ?; try (PreparedStatement ps2 conn.prepareStatement(updateStockSql)) { ps2.setInt(1, recordId); ps2.executeUpdate(); } return true; } } catch (SQLException e) { e.printStackTrace(); } return false; }SQL里帶AND status 1確保只有借出中的記錄才能執(zhí)行歸還。如果記錄已經(jīng)是已歸還狀態(tài)UPDATE影響行數(shù)為0不會(huì)出現(xiàn)重復(fù)恢復(fù)庫(kù)存的情況。3. 完整實(shí)操?gòu)慕◣?kù)建表到跑通項(xiàng)目3.1 環(huán)境準(zhǔn)備MySQL 8 IDEA 驅(qū)動(dòng)動(dòng)手之前先把環(huán)境準(zhǔn)備好。我以本機(jī)開發(fā)為例說(shuō)明每一步需要做什么。第一步安裝MySQL 8。Windows環(huán)境下直接去MySQL官網(wǎng)下載MySQL Installer只安裝Server和Command Line Client。安裝過程中的用戶名密碼記好默認(rèn)root用戶需要設(shè)置一個(gè)自己的密碼。安裝完成后在命令行用mysql -u root -p驗(yàn)證能登錄。第二步安裝Navicat或者直接用MySQL自帶的Workbench。Navicat勝在操作直觀建庫(kù)建表看數(shù)據(jù)都方便有條件的話可以用它快速核對(duì)數(shù)據(jù)是否寫入成功。第三步IDEA創(chuàng)建Java項(xiàng)目引入連接驅(qū)動(dòng)。兩種方式任選下載mysql-connector-java的jar包放在項(xiàng)目lib目錄下右鍵選擇Add as Library。這個(gè)方式不需要額外的構(gòu)建工具。Maven項(xiàng)目在pom.xml里添加依賴dependency groupIdmysql/groupId artifactIdmysql-connector-java/artifactId version8.0.33/version /dependency我用的是MySQL 8.0系列所以驅(qū)動(dòng)版本選8.0.33跟服務(wù)器兼容很穩(wěn)。如果你的MySQL是5.7及以下版本驅(qū)動(dòng)選5.1.x系列更省事。3.2 建庫(kù)建表與初始化數(shù)據(jù)打開Navicat新建數(shù)據(jù)庫(kù)library_db字符集選utf8mb4。用以下幾個(gè)建表語(yǔ)句直接執(zhí)行CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) NOT NULL UNIQUE, password CHAR(32) NOT NULL, role VARCHAR(10) NOT NULL DEFAULT user, real_name VARCHAR(50) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; CREATE TABLE books ( id INT AUTO_INCREMENT PRIMARY KEY, book_name VARCHAR(100) NOT NULL, author VARCHAR(50) DEFAULT NULL, publisher VARCHAR(100) DEFAULT NULL, price DECIMAL(10,2) DEFAULT 0.00, stock INT NOT NULL DEFAULT 0, category VARCHAR(50) DEFAULT NULL ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; CREATE TABLE borrow_records ( id INT AUTO_INCREMENT PRIMARY KEY, book_id INT NOT NULL, user_id INT NOT NULL, borrow_date DATETIME NOT NULL, return_date DATETIME DEFAULT NULL, status TINYINT NOT NULL DEFAULT 1, CONSTRAINT fk_book FOREIGN KEY (book_id) REFERENCES books(id), CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;初始化數(shù)據(jù)也一并寫入方便程序啟動(dòng)后有東西可操作INSERT INTO users(id, username, password, role, real_name) VALUES (NULL, admin, MD5(123456), admin, 系統(tǒng)管理員), (NULL, zhangsan, MD5(abc123), user, 張三); INSERT INTO books(book_name, author, publisher, price, stock, category) VALUES (Java核心技術(shù)卷I, 凱·S·霍斯特曼, 機(jī)械工業(yè)出版社, 119.00, 5, 計(jì)算機(jī)), (深入理解Java虛擬機(jī), 周志明, 機(jī)械工業(yè)出版社, 129.00, 3, 計(jì)算機(jī)), (MySQL必知必會(huì), Ben Forta, 人民郵電出版社, 59.00, 8, 數(shù)據(jù)庫(kù)), (活著, 余華, 作家出版社, 35.00, 10, 文學(xué));有一點(diǎn)需要提前說(shuō)清楚MD5(123456)這個(gè)函數(shù)在MySQL 8中默認(rèn)是可用的生成的密文存進(jìn)去之后Java代碼里用md5方法對(duì)用戶輸入的密碼加密再比對(duì)兩邊算法一致才能匹配。我自己踩過初始化數(shù)據(jù)里直接寫明文密碼的坑。初期偷懶把password字段存成123456登錄驗(yàn)證時(shí)也拿明文去比較數(shù)據(jù)庫(kù)里全是裸奔的用戶密碼。后來(lái)項(xiàng)目做完統(tǒng)一改用MD5才把這個(gè)問題處理干凈。建議從項(xiàng)目第一版開始就養(yǎng)成不落明文密碼的習(xí)慣。3.3 項(xiàng)目結(jié)構(gòu)搭建分包劃分與實(shí)體類設(shè)計(jì)代碼不是堆在一個(gè)類里就能叫完整項(xiàng)目。合理的包結(jié)構(gòu)讓代碼后期可維護(hù)也更容易講明白系統(tǒng)架構(gòu)。我建議用以下分包方式com.library ├── entity/ # 實(shí)體類 │ ├── User.java │ ├── Book.java │ └── BorrowRecord.java ├── util/ # 工具類 │ └── DBUtil.java ├── dao/ # 數(shù)據(jù)訪問層 │ ├── UserDAO.java │ ├── BookDAO.java │ └── BorrowDAO.java ├── service/ # 業(yè)務(wù)邏輯層 │ ├── UserService.java │ ├── BookService.java │ └── BorrowService.java └── ui/ # 界面展示與交互 └── MainMenu.java分層的好處是界面層只負(fù)責(zé)接收輸入和展示結(jié)果業(yè)務(wù)層處理業(yè)務(wù)規(guī)則DAO層只能跟數(shù)據(jù)庫(kù)打交道。舉個(gè)實(shí)際場(chǎng)景界面層需要顯示“借書成功”但它不應(yīng)該直接寫SQL而是調(diào)用service層的方法service內(nèi)部組織多個(gè)DAO方法協(xié)同完成借書流程。這樣改動(dòng)界面不影響數(shù)據(jù)庫(kù)代碼替換數(shù)據(jù)庫(kù)連接方式也不影響界面。實(shí)體類以Book為例public class Book { private int id; private String bookName; private String author; private String publisher; private BigDecimal price; private int stock; private String category; // 省略getter和setter方法 }價(jià)格字段用BigDecimal而不是doubledouble在數(shù)據(jù)庫(kù)和Java之間來(lái)回轉(zhuǎn)換的時(shí)候浮點(diǎn)誤差會(huì)讓人抓狂。數(shù)據(jù)庫(kù)DECIMAL對(duì)應(yīng)Java的BigDecimal這是標(biāo)準(zhǔn)對(duì)應(yīng)關(guān)系。3.4 圖書列表查詢分頁(yè)與排序?qū)嵅賵D書列表的展示不只是一條SELECT那么簡(jiǎn)單。系統(tǒng)里的圖書數(shù)量一旦上到幾十本控制臺(tái)一屏打不完前端展示的體驗(yàn)就很差。分頁(yè)是必須有的能力。public ListBook queryBooksByPage(int pageNum, int pageSize, String keyword) { ListBook bookList new ArrayList(); int offset (pageNum - 1) * pageSize; String sql SELECT id, book_name, author, publisher, price, stock, category FROM books; // 關(guān)鍵字搜索增強(qiáng)體驗(yàn) if (keyword ! null !keyword.isEmpty()) { sql WHERE book_name LIKE ?; } sql ORDER BY id LIMIT ? OFFSET ?; try (Connection conn DBUtil.getConnection(); PreparedStatement ps conn.prepareStatement(sql)) { int paramIndex 1; if (keyword ! null !keyword.isEmpty()) { ps.setString(paramIndex, % keyword %); } ps.setInt(paramIndex, pageSize); ps.setInt(paramIndex, offset); try (ResultSet rs ps.executeQuery()) { while (rs.next()) { Book book new Book(); book.setId(rs.getInt(id)); book.setBookName(rs.getString(book_name)); book.setAuthor(rs.getString(author)); book.setPublisher(rs.getString(publisher)); book.setPrice(rs.getBigDecimal(price)); book.setStock(rs.getInt(stock)); book.setCategory(rs.getString(category)); bookList.add(book); } } } catch (SQLException e) { e.printStackTrace(); } return bookList; }ORDER BY id LIMIT ? OFFSET ?是MySQL分頁(yè)的標(biāo)準(zhǔn)姿勢(shì)。offset的算法是(pageNum - 1) * pageSize第一頁(yè)偏移0條第二頁(yè)偏移pageSize條很容易理解。LIKE關(guān)鍵字搜索時(shí)預(yù)編譯的?參數(shù)值直接拼上%即可搜書名、作者、分類都通用。關(guān)于LIKE查詢有個(gè)性能細(xì)節(jié)如果數(shù)據(jù)量大LIKE %關(guān)鍵字%這種前置模糊匹配是走不了索引的全表掃描。但圖書管理系統(tǒng)這種量級(jí)的數(shù)據(jù)完全夠用不需要過度優(yōu)化。如果想優(yōu)化可以引入Elasticsearch或者M(jìn)ySQL全文索引那是后話。排序方面同樣用ORDER BY實(shí)現(xiàn)熱門借閱排名可以這樣查String sql SELECT b.id, b.book_name, COUNT(r.id) AS borrow_count FROM books b LEFT JOIN borrow_records r ON b.id r.book_id GROUP BY b.id, b.book_name ORDER BY borrow_count DESC;LEFT JOIN保證一本都沒被借過的圖書也能出現(xiàn)在列表里借書次數(shù)為0。COUNT(r.id)統(tǒng)計(jì)每本書的借閱記錄條數(shù)按降序排列得到熱門圖書排行。這個(gè)SQL在面試?yán)镆步?jīng)常被問到JOIN和GROUP BY的組合使用。4. 常見問題與排查技巧實(shí)錄4.1 數(shù)據(jù)庫(kù)連接失敗驅(qū)動(dòng)、時(shí)區(qū)、權(quán)限三類大坑連接數(shù)據(jù)庫(kù)是整套系統(tǒng)最容易出問題的地方而問題基本集中在三個(gè)方向第一類是ClassNotFoundException報(bào)錯(cuò)內(nèi)容是ClassNotFoundException: com.mysql.jdbc.Driver。這個(gè)100%是驅(qū)動(dòng)類名或者驅(qū)動(dòng)jar包的問題。MySQL 8.x的驅(qū)動(dòng)類名是com.mysql.cj.jdbc.Driver你還在用com.mysql.jdbc.Driver就會(huì)報(bào)這個(gè)錯(cuò)。另外確認(rèn)引用的jar包版本高版本驅(qū)動(dòng)確認(rèn)類名寫對(duì)了沒。第二類是時(shí)區(qū)錯(cuò)誤報(bào)錯(cuò)一般是The server time zone value й?? is unrecognized。原因是MySQL 8服務(wù)器默認(rèn)使用系統(tǒng)時(shí)區(qū)而JDBC連接串沒有指定時(shí)區(qū)兩端對(duì)不上。解決方式是在URL追加serverTimezoneAsia/Shanghai。第三類是Public Key Retrieval報(bào)錯(cuò)MySQL 8的默認(rèn)認(rèn)證方式是caching_sha2_password客戶端連接時(shí)需要從服務(wù)端獲取公鑰做加密JDBC默認(rèn)行為不允許這個(gè)操作。URL里加useSSLfalseallowPublicKeyRetrievaltrue即可解決。我自己在項(xiàng)目聯(lián)調(diào)階段把這三類錯(cuò)誤輪著遇了一遍總結(jié)成一條經(jīng)驗(yàn)連接串務(wù)必一次性寫完整不要遇到一個(gè)錯(cuò)改一處太折騰。4.2 中文亂碼三層設(shè)置缺一不可中文亂碼問題出現(xiàn)得相當(dāng)頻繁而且一亂就是一大片。排查要按三層來(lái)檢查第一層數(shù)據(jù)庫(kù)。建庫(kù)和建表時(shí)指定CHARSETutf8mb4就是告訴MySQL這個(gè)數(shù)據(jù)庫(kù)要存unicode字符中文完全沒問題。忘記指定的可以用ALTER TABLE命令補(bǔ)ALTER TABLE books CONVERT TO CHARACTER SET utf8mb4;第二層JDBC連接串。URL必須帶characterEncodingutf8注意這里跟數(shù)據(jù)庫(kù)里character_set_database的配置匹配起來(lái)。第三層控制臺(tái)輸出。Windows下IDEA的控制臺(tái)默認(rèn)編碼可能不是UTF-8在IDEA的Settings里把Global Encoding、Project Encoding、Console的編碼都設(shè)為UTF-8。命令行運(yùn)行時(shí)在編譯和運(yùn)行命令加上-Dfile.encodingUTF-8參數(shù)。這三個(gè)地方只要有一個(gè)不對(duì)柜臺(tái)就會(huì)顯示一排煩人的問號(hào)。連續(xù)探查這個(gè)問題的經(jīng)驗(yàn)教訓(xùn)是先確認(rèn)數(shù)據(jù)庫(kù)存進(jìn)去的中文是不是正常的用Navicat直接看表。庫(kù)里是亂的就是數(shù)據(jù)庫(kù)建表和連接串的問題庫(kù)是正常的但程序顯示亂就是控制臺(tái)和JVM編碼的問題。4.3 業(yè)務(wù)邏輯Bug排查事務(wù)沒提交、庫(kù)存負(fù)數(shù)、并發(fā)沖突數(shù)據(jù)庫(kù)連接層打通之后業(yè)務(wù)層的坑就輪到在運(yùn)行時(shí)慢慢暴露了。我整理一下最常遇到的幾個(gè)不執(zhí)行commit就斷言數(shù)據(jù)寫進(jìn)去了。忘了setAutoCommit(false)之后一定要手動(dòng)commit或者寫代碼的時(shí)候根本沒意識(shí)到事務(wù)已經(jīng)開啟數(shù)據(jù)在另一個(gè)連接里死活看不到更新。排查方式確認(rèn)是否有事務(wù)開啟查到commit或rollback了嗎。筆記本還剩0本借書還能成功。這是庫(kù)存校驗(yàn)缺失導(dǎo)致的。解決方式在前面borrowBook方法里講過校驗(yàn)和扣減必須在同一事務(wù)里用行鎖保護(hù)絕不能在SELECT和UPDATE之間留出空檔。對(duì)了損壞數(shù)據(jù)還可能來(lái)自重復(fù)借同一本書。如果借閱表里已經(jīng)有一條status1的同圖書同用戶記錄新借書請(qǐng)求不應(yīng)該再成功。在插入新借閱記錄前查詢?cè)撚脩粼搱D書是否存在借出中的記錄即可。4.4 資源泄漏連接不關(guān)系統(tǒng)遲早卡到死這個(gè)問題雖然常見但在新手代碼里幾乎是批量出現(xiàn)。寫完Connection不去close獲取了ResultSet不去close程序跑不了幾個(gè)來(lái)回就報(bào)Too many connections。MySQL默認(rèn)連接上限是100多個(gè)連接不釋放就會(huì)把連接池占滿。我建議把所有資源關(guān)閉統(tǒng)一放到finally代碼塊里或者直接使用JDK 7引入的try-with-resources語(yǔ)法。try (Connection conn DBUtil.getConnection(); PreparedStatement ps conn.prepareStatement(sql)) { ... } catch (SQLException e) { e.printStackTrace(); }用了try-with-resources之后Connection、Statement、ResultSet只要實(shí)現(xiàn)了AutoCloseable接口就會(huì)在try塊結(jié)束后自動(dòng)關(guān)閉代碼少而且不容易遺漏。如果用的是我前面寫的DBUtilclose方法也要在finally里確保調(diào)用。真實(shí)項(xiàng)目中連接不關(guān)閉屬于那種“今天不出事、明天不出事、上線第三天就出事”的問題。你測(cè)試的時(shí)候數(shù)據(jù)量小加上連接偶爾被GC回收感受不到嚴(yán)重性。等系統(tǒng)真的被多個(gè)人同時(shí)用起來(lái)連接數(shù)飆升服務(wù)直接就癱了。5. 項(xiàng)目擴(kuò)展方向與面試講解建議這個(gè)項(xiàng)目做完能跑通只是第一步。真正讓項(xiàng)目有價(jià)值的是你能夠擴(kuò)展它并且在面試時(shí)把它講成亮點(diǎn)。我給四個(gè)擴(kuò)展方向的思路。第一個(gè)方向是加上圖形界面?？刂婆_(tái)版本的邏輯代碼是完整的只是交互方式比較簡(jiǎn)陋?？梢杂肑ava Swing或JavaFX做界面把MainMenu替換成登錄窗口和主窗口BookDAO、UserDAO這些類不需要大改。這樣就變成了一個(gè)更像“系統(tǒng)”的東西。第二個(gè)方向是升級(jí)Web版本。把項(xiàng)目改造成Spring Boot MyBatis Vue的前后端分離版本數(shù)據(jù)庫(kù)表結(jié)構(gòu)幾乎原封不動(dòng)DAO層代碼翻譯成MyBatis的Mapper接口。這個(gè)升級(jí)過程你自然就理解了框架到底幫你做了什么。第三個(gè)方向是補(bǔ)充還書逾期和罰款邏輯。在BorrowRecord里加一個(gè)shouldReturnDate的字段還書時(shí)用當(dāng)前日期跟應(yīng)還日期比較超期天數(shù)乘以每日罰款金額算出一個(gè)逾期費(fèi)用記錄這樣業(yè)務(wù)面就比普通的課設(shè)項(xiàng)目完整很多。第四個(gè)方向是權(quán)限體系的強(qiáng)化。讀者只能看到自己的借閱記錄管理員可以看全部借書也有容量限制這些都用role字段和SQL條件就能實(shí)現(xiàn)。給面試講項(xiàng)目的時(shí)候不要停留在“我寫了一個(gè)圖書增刪改查的系統(tǒng)”這種層面。重點(diǎn)講三件事數(shù)據(jù)庫(kù)表怎么設(shè)計(jì)的表間關(guān)系是什么JDBC連接MySQL踩過哪些坑怎么定位和解決的借書的庫(kù)存扣減和借閱記錄的寫入為什么放在一個(gè)事務(wù)里。把這些問題想透了比堆砌功能列表管用得多。我個(gè)人在帶項(xiàng)目時(shí)最常跟新手說(shuō)的一句話是能跑通只是代碼的及格線知道自己為什么能跑通才算是學(xué)會(huì)。這個(gè)項(xiàng)目做完你至少應(yīng)該能回答為什么用PreparedStatement而不是Statement為什么借書要用事務(wù)為什么庫(kù)存要加行鎖為什么密碼要加密存儲(chǔ)。能在紙上畫出三張表的關(guān)系能說(shuō)出連接MySQL的URL各個(gè)參數(shù)的含義。這些才是這個(gè)項(xiàng)目真正的收獲。最后再分享一個(gè)我在調(diào)試階段的小習(xí)慣把SQL語(yǔ)句先在Navicat里跑一遍確認(rèn)結(jié)果集返回正確了再回到Java代碼里聯(lián)調(diào)。數(shù)據(jù)庫(kù)查詢這層走通了代碼層的排查范圍瞬間縮小問題定位效率非常高。這個(gè)習(xí)慣我用在所有的數(shù)據(jù)庫(kù)項(xiàng)目里實(shí)測(cè)下來(lái)很穩(wěn)。