萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> 資料庫綜合

JDBC 核心 SQL 語法標準教程:DDL/DML 規範寫法與參數化最佳實踐

跨資料庫通用的標準 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 NULLemail IS NULLNULL 不能用 = 比較
邏輯AND / OR / NOTage > ? AND dept = ?優先級: NOT > AND > OR,善用括號
LIKEname 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 / getXXXJDBC TypeSQL 標準類型
StringsetString / getStringVARCHAR / NVARCHARVARCHAR(n) / CHAR(n)
int / IntegersetInt / getIntINTEGERINT / INTEGER
long / LongsetLong / getLongBIGINTBIGINT
double / DoublesetDouble / getDoubleDOUBLEDOUBLE PRECISION
BigDecimalsetBigDecimal / getBigDecimalDECIMAL / NUMERICDECIMAL(p,s) / NUMERIC(p,s)
boolean / BooleansetBoolean / getBooleanBIT / BOOLEANBOOLEAN / BIT
java.sql.DatesetDate / getDateDATEDATE
java.sql.TimestampsetTimestamp / getTimestampTIMESTAMPTIMESTAMP / DATETIME
byte[]setBytes / getBytesVARBINARY / BLOBVARBINARY(MAX) / BLOB
InputStream / ReadersetBinaryStream / setCharacterStreamBLOB / CLOBBLOB / 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));
    }
}
copyright © 萬盛學電腦網 all rights reserved