跨資料庫通用的標準 SQL 必知必會
JDBC 應用需編寫標準 SQL,避免方言鎖定。本文梳理 **建表/刪表、增刪改查 (CRUD)、條件查詢、參數化** 規範寫法,適用 MySQL/PostgreSQL/SQL Server/Oracle 等主流資料庫。
DDL: 資料庫與表結構
-- 建立資料庫 (權限需求: CREATE DATABASE)
CREATE DATABASE emp_db CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; -- MySQL
-- SQL Server: CREATE DATABASE emp_db; (Collation 由實例/模型庫決定)
-- 刪除資料庫 (謹慎!)
DROP DATABASE emp_db;
-- 建立表 (標準類型: INT, VARCHAR(n), DATE, TIMESTAMP, DECIMAL(p,s), BOOLEAN/BIT)
CREATE TABLE employees (
id INT NOT NULL PRIMARY KEY, -- 主鍵
age INT NOT NULL,
first_name VARCHAR(255),
last_name VARCHAR(255),
hire_date DATE,
salary DECIMAL(10,2),
is_active BOOLEAN DEFAULT TRUE -- MySQL/PG: BOOLEAN; SQL Server: BIT
);
-- 刪除表
DROP TABLE employees;
DML: 資料操作 (參數化佔位符 `?` 為 JDBC 標準)
-- 插入 (明確欄位順序)
INSERT INTO employees (id, age, first_name, last_name, hire_date, salary, is_active)
VALUES (?, ?, ?, ?, ?, ?, ?);
-- 查詢 (投影欄位、條件、排序、分頁)
SELECT id, first_name, last_name, age
FROM employees
WHERE id = ?; -- 單值參數
SELECT first_name, last_name, age
FROM employees
WHERE first_name LIKE ?; -- 模糊查詢: 參數傳 "%Zara%"
-- 更新
UPDATE employees
SET age = ?, salary = ?
WHERE id = ?;
-- 刪除
DELETE FROM employees WHERE id = ?;
WHERE 子句操作符完整表
| 類別 | 操作符 | 範例 | 備註 |
| 比較 | = | age = ? | |
| <> / != | status <> ? | 標準用 <>,!= 為擴展 |
| <, >, <=, >= | salary > ? | |
| BETWEEN ... AND ... | age BETWEEN ? AND ? | 包含邊界 |
| IN (..., ...) | dept_id IN (?, ?, ?) | 值列表,參數數量動態需動態 SQL |
| IS NULL / IS NOT NULL | email IS NULL | NULL 不能用 = 比較 |
| 邏輯 | AND / OR / NOT | age > ? AND dept = ? | 優先級: NOT > AND > OR,善用括號 |
| LIKE | name LIKE ? | % 任意長度, _ 單字元, [範圍] (SQL Server), ESCAPE '\' |
| EXISTS (subquery) | EXISTS (SELECT 1 FROM orders WHERE user_id = e.id) | 半連接,存在即真,效能優於 IN |
JDBC 參數化最佳實踐 (防注入、重用計畫)
String sql = "SELECT id, name FROM users WHERE status = ? AND created_at > ?";
try (Connection conn = ds.getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setInt(1, 1); // status = 1
ps.setTimestamp(2, Timestamp.valueOf("2023-01-01 00:00:00")); // 日期時間
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
System.out.println(rs.getInt("id") + ": " + rs.getString("name"));
}
}
}
類型映射對照表 (Java ↔ JDBC ↔ SQL 標準)
| Java 類型 | setXXX / getXXX | JDBC Type | SQL 標準類型 |
| String | setString / getString | VARCHAR / NVARCHAR | VARCHAR(n) / CHAR(n) |
| int / Integer | setInt / getInt | INTEGER | INT / INTEGER |
| long / Long | setLong / getLong | BIGINT | BIGINT |
| double / Double | setDouble / getDouble | DOUBLE | DOUBLE PRECISION |
| BigDecimal | setBigDecimal / getBigDecimal | DECIMAL / NUMERIC | DECIMAL(p,s) / NUMERIC(p,s) |
| boolean / Boolean | setBoolean / getBoolean | BIT / BOOLEAN | BOOLEAN / BIT |
| java.sql.Date | setDate / getDate | DATE | DATE |
| java.sql.Timestamp | setTimestamp / getTimestamp | TIMESTAMP | TIMESTAMP / DATETIME |
| byte[] | setBytes / getBytes | VARBINARY / BLOB | VARBINARY(MAX) / BLOB |
| InputStream / Reader | setBinaryStream / setCharacterStream | BLOB / CLOB | BLOB / CLOB / TEXT |
進階:批次執行與獲取自增鍵
// 批次插入 (addBatch + executeBatch)
String sql = "INSERT INTO logs (msg, created_at) VALUES (?, NOW())";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
for (String msg : messages) {
ps.setString(1, msg);
ps.addBatch();
}
int[] counts = ps.executeBatch(); // 每條影響行數
}
// 獲取自增主鍵 (Statement.RETURN_GENERATED_KEYS)
try (PreparedStatement ps = conn.prepareStatement(
"INSERT INTO users(name) VALUES (?)", Statement.RETURN_GENERATED_KEYS)) {
ps.setString(1, "Alice");
ps.executeUpdate();
try (ResultSet keys = ps.getGeneratedKeys()) {
if (keys.next()) System.out.println("New ID: " + keys.getLong(1));
}
}