環境準備
- MySQL Server 5.7/8.0
- JDBC 驅動:
mysql-connector-java-8.0.x.jar → 放入 WEB-INF/lib 或 Tomcat lib
- 測試資料庫:
CREATE DATABASE testdb CHARSET utf8mb4;
- 測試表:
CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50), email VARCHAR(100));
標準連線代碼模板
<%@ page import="java.sql.*, javax.sql.DataSource" %>
<%@ page contentType="text/html;charset=UTF-8" %>
<%
// 1. 連線參數(實務應放在 context.xml JNDI 或屬性檔)
String url = "jdbc:mysql://localhost:3306/testdb?useSSL=false&serverTimezone=Asia/Taipei&characterEncoding=UTF-8";
String user = "root";
String pass = "your_password";
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
// 2. 載入驅動(JDBC 4.0+ 可省略,SPI 自動發現)
// Class.forName("com.mysql.cj.jdbc.Driver");
// 3. 建立連線
conn = DriverManager.getConnection(url, user, pass);
// 4. 準備語句(防 SQL 注入、預編譯優化)
String sql = "SELECT id, username, email FROM users WHERE id > ?";
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, 0);
// 5. 執行查詢
rs = pstmt.executeQuery();
// 6. 處理結果集
out.println("<table border='1'><tr><th>ID</th><th>用戶名</th><th>Email</th></tr>");
while (rs.next()) {
out.println("<tr><td>" + rs.getInt("id") + "</td>");
out.println("<td>" + rs.getString("username") + "</td>");
out.println("<td>" + rs.getString("email") + "</td></tr>");
}
out.println("</table>");
} catch (SQLException e) {
e.printStackTrace();
out.println("<font color='red'>資料庫錯誤:" + e.getMessage() + "</font>");
} finally {
// 7. 逆序關閉資源(Java 7+ try-with-resources 更優雅)
try { if (rs != null) rs.close(); } catch (SQLException e) {}
try { if (pstmt != null) pstmt.close(); } catch (SQLException e) {}
try { if (conn != null) conn.close(); } catch (SQLException e) {}
}
%>
進階:使用連線池
Tomcat context.xml 配置 JNDI DataSource
<Resource name="jdbc/testdb" auth="Container" type="javax.sql.DataSource"
maxTotal="20" maxIdle="10" maxWaitMillis="-1"
username="root" password="your_password"
driverClassName="com.mysql.cj.jdbc.Driver"
url="jdbc:mysql://localhost:3306/testdb?useSSL=false&serverTimezone=Asia/Taipei"/>
JSP 中查找 DataSource
<%@ page import="javax.naming.*, javax.sql.DataSource, java.sql.*" %>
<%
Context initCtx = new InitialContext();
DataSource ds = (DataSource) initCtx.lookup("java:comp/env/jdbc/testdb");
try (Connection conn = ds.getConnection();
PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM users");
ResultSet rs = pstmt.executeQuery()) {
// 處理結果...
}
%>
CRUD 操作範例
| 操作 | SQL 範例 | 關鍵 API |
| Insert | INSERT INTO users(username,email) VALUES(?,?) | pstmt.executeUpdate() 返回影響行數 |
| Update | UPDATE users SET email=? WHERE id=? | pstmt.executeUpdate() |
| Delete | DELETE FROM users WHERE id=? | pstmt.executeUpdate() |
| Select | SELECT * FROM users WHERE username LIKE ? | pstmt.executeQuery() 返回 ResultSet |
| 批次 | pstmt.addBatch() 累積 → executeBatch() | 大量寫入優化 |
關鍵最佳實踐
- 永遠使用 PreparedStatement,拒絕字串拼接 SQL
- 資源在 finally 中關閉,或 JDK 7+
try-with-resources
- 生產環境禁用 DriverManager,必須用連線池
- 異常堆疊不要直接輸出給用戶,記錄日誌、顯示友好提示
- 大結果集採用
setFetchSize(Integer.MIN_VALUE) 流式讀取(MySQL)
- 事務控制:
conn.setAutoCommit(false) → 操作 → commit()/rollback()