JDBC (Java Database Connectivity) 是 Java 操作關係型資料庫的標準 API。本文以 MySQL 為例,涵蓋環境準備、驅動導入、Connection/PreparedStatement/ResultSet 核心 API 實戰、資源管理最佳實踐,以及連接池優化方向。
CREATE DATABASE samp_db;
USE samp_db;
CREATE TABLE students (
Id INT PRIMARY KEY AUTO_INCREMENT,
Name VARCHAR(50),
Sex VARCHAR(10),
Age VARCHAR(10)
);
INSERT INTO students (Name, Sex, Age) VALUES
('Ender','male','8'), ('Bean','male','6'), ('Petra','female','9');
mysql-connector-java-8.0.33.jar) 並導入專案 (Eclipse: Properties → Java Build Path → Add External JARs)static class Student {
private String id, name, sex, age;
// 建構子、Getter/Setter 略
}
private static Connection getConn() {
String driver = "com.mysql.cj.jdbc.Driver"; // 8.0+ 驅動類
String url = "jdbc:mysql://localhost:3306/samp_db?useSSL=false&serverTimezone=UTC";
String user = "root", pass = "";
try {
Class.forName(driver);
return DriverManager.getConnection(url, user, pass);
} catch (Exception e) { e.printStackTrace(); return null; }
}
public static int insert(Student s) {
String sql = "INSERT INTO students (Name, Sex, Age) VALUES (?, ?, ?)";
try (Connection conn = getConn();
PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setString(1, s.getName());
pstmt.setString(2, s.getSex());
pstmt.setString(3, s.getAge());
return pstmt.executeUpdate();
} catch (SQLException e) { e.printStackTrace(); return 0; }
}
public static int update(Student s) {
String sql = "UPDATE students SET Age=? WHERE Name=?";
try (Connection conn = getConn();
PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setString(1, s.getAge());
pstmt.setString(2, s.getName());
return pstmt.executeUpdate();
} catch (SQLException e) { e.printStackTrace(); return 0; }
}
public static void selectAll() {
String sql = "SELECT * FROM students";
try (Connection conn = getConn();
PreparedStatement pstmt = conn.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery()) {
int cols = rs.getMetaData().getColumnCount();
while (rs.next()) {
for (int i = 1; i <= cols; i++) {
System.out.print(rs.getString(i) + "\t");
}
System.out.println();
}
} catch (SQLException e) { e.printStackTrace(); }
}
public static int delete(String name) {
String sql = "DELETE FROM students WHERE Name=?";
try (Connection conn = getConn();
PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setString(1, name);
return pstmt.executeUpdate();
} catch (SQLException e) { e.printStackTrace(); return 0; }
}
| 介面 | 核心方法 | 說明 |
|---|---|---|
Connection | prepareStatement(sql), createStatement(), commit(), rollback(), setAutoCommit() | 資料庫會話、事務控制、元數據獲取 |
PreparedStatement | executeUpdate() (DML)、executeQuery() (SELECT)、setX(int, X) 綁參 | 預編譯 SQL、防注入、批次執行 addBatch()/executeBatch() |
ResultSet | next(), getX(int/string), getMetaData() | 結果集游標、類型安全取值、結果集元數據 |
Connection + SQL 字串conn.prepareStatement(sql) → PreparedStatementpstmt.setX()executeUpdate() / executeQuery()ResultSet → PreparedStatement → Connection (逆序,Java 7+ 建議 try-with-resources)答案:引入連接池 (HikariCP、Druid、DBCP2)。池化管理連接生命週期,獲取/歸還替代建立/關閉,配置核心參數:maximumPoolSize, minimumIdle, connectionTimeout, idleTimeout, maxLifetime。
答案:驅動嘗試隱式轉換,非數字字串轉數值型通常變 0 或拋異常。最佳實踐:DAO 層強類型映射,使用 pstmt.setInt()、setLong()、setTimestamp() 等對應方法,杜絕字串萬用。
PreparedStatement 綁參@Transactional 或手動 commit/rollbackJDBC 是所有 Java ORM 框架 (MyBatis, Hibernate, JPA) 的基石,掌握其核心機制有助於深度排查框架層面的疑難雜癥。