您现在的位置：程式碼之家 >> 編程語言 > >> Java 程式設計 >> Java 相關知識

JDBC Statement 與 PreparedStatement 深度對比：安全、效能與最佳實踐

編輯：Java 相關知識

核心差異一覽

特性	Statement	PreparedStatement
SQL 解析	每次執行皆解析、編譯、優化	預編譯，參數化執行計畫可重用
參數傳遞	字串拼接 (風險高)	佔位符 `?` + `setXxx()` (安全)
SQL 注入防護	無	內建防護
批次處理	`addBatch(sql)`	`addBatch()` 參數化 (效能優)
適用場景	靜態 SQL、DDL、一次性查詢	動態參數、高頻執行、用戶輸入

Statement 使用詳解

建立與基本方法

Statement stmt = conn.createStatement();

// 三大執行方法
boolean hasResult = stmt.execute(sql);           // 任意 SQL
int updateCount = stmt.executeUpdate(sql);       // INSERT/UPDATE/DELETE
ResultSet rs = stmt.executeQuery(sql);           // SELECT

資源關閉模式

try (Statement stmt = conn.createStatement()) {
    // 使用 stmt
} // 自動關閉 (JDK 7+ try-with-resources)

PreparedStatement 進階用法

參數綁定 (索引從 1 開始)

String sql = "UPDATE employees SET salary = ?, bonus = ? WHERE id = ?";
try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
    pstmt.setDouble(1, 50000.00);   // salary
    pstmt.setDouble(2, 5000.00);    // bonus
    pstmt.setInt(3, 101);           // id
    int rows = pstmt.executeUpdate();
}

型別對應表

Java 型別	setXxx()	SQL 型別範例
String	setString	VARCHAR, CHAR, TEXT
int/Integer	setInt	INT, INTEGER
long/Long	setLong	BIGINT
double/Double	setDouble	DOUBLE, DECIMAL
BigDecimal	setBigDecimal	NUMERIC, DECIMAL(精確金融)
Date	setDate	DATE
Timestamp	setTimestamp	TIMESTAMP, DATETIME
byte[]	setBytes	BLOB, VARBINARY
InputStream	setBinaryStream	BLOB (大物件流式寫入)
Reader	setCharacterStream	CLOB (大文本流式寫入)

批次處理效能優化

String sql = "INSERT INTO logs (level, message, created_at) VALUES (?, ?, ?)";
try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
    conn.setAutoCommit(false); // 關鍵：手動提交
    for (LogEntry log : logs) {
        pstmt.setString(1, log.getLevel());
        pstmt.setString(2, log.getMessage());
        pstmt.setTimestamp(3, Timestamp.valueOf(log.getTime()));
        pstmt.addBatch();
        // 每 1000 條執行一次，避免記憶體暴漲
        if (count % 1000 == 0) {
            pstmt.executeBatch();
            conn.commit();
        }
    }
    pstmt.executeBatch(); // 剩餘
    conn.commit();
}

完整範例：員工管理 CRUD

public class EmployeeDao {
    private static final String INSERT_SQL =
        "INSERT INTO employees (first_name, last_name, salary) VALUES (?, ?, ?)";
    private static final String UPDATE_SQL =
        "UPDATE employees SET salary = ? WHERE id = ?";
    private static final String SELECT_BY_ID =
        "SELECT id, first_name, last_name, salary FROM employees WHERE id = ?";
    private static final String SELECT_ALL =
        "SELECT id, first_name, last_name, salary FROM employees ORDER BY id";

    public int insert(Employee emp) {
        try (Connection conn = ds.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(INSERT_SQL, Statement.RETURN_GENERATED_KEYS)) {
            pstmt.setString(1, emp.getFirstName());
            pstmt.setString(2, emp.getLastName());
            pstmt.setInt(3, emp.getSalary());
            pstmt.executeUpdate();
            try (ResultSet keys = pstmt.getGeneratedKeys()) {
                return keys.next() ? keys.getInt(1) : -1;
            }
        } catch (SQLException e) { throw new DaoException(e); }
    }

    public void updateSalary(int id, int newSalary) {
        try (Connection conn = ds.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(UPDATE_SQL)) {
            pstmt.setInt(1, newSalary);
            pstmt.setInt(2, id);
            pstmt.executeUpdate();
        } catch (SQLException e) { throw new DaoException(e); }
    }

    public Optional<Employee> findById(int id) {
        try (Connection conn = ds.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(SELECT_BY_ID)) {
            pstmt.setInt(1, id);
            try (ResultSet rs = pstmt.executeQuery()) {
                return rs.next() ? Optional.of(mapRow(rs)) : Optional.empty();
            }
        } catch (SQLException e) { throw new DaoException(e); }
    }

    public List<Employee> findAll() {
        try (Connection conn = ds.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(SELECT_ALL);
             ResultSet rs = pstmt.executeQuery()) {
            List<Employee> list = new ArrayList<>();
            while (rs.next()) list.add(mapRow(rs));
            return list;
        } catch (SQLException e) { throw new DaoException(e); }
    }
}