4.11 Statement, PreparedStatement and CallableStatement

在JDBC的世界裡, 我們常常會透過Statement/PreparedStatement來對DB進行一些操作, 這篇主要是想記錄一下這兩者之間的差異(CallableStatement我沒用過, 以後有機會再補吧).

Statement

通常用於執行靜態的SQL語句並且獲得SQL產生之結果, 通常會看到以下三種執行SQL語句的方法:
- executeUpdate(String sql): 執行SQL insert/update/delete語句, 回傳受影響資料的數目或是0
- executeQuery(String sql): 執行回傳單個ResultSet的SQL語句, 回傳類型就是ResultSet
- execute(String sql): 執行可以回傳多個結果的SQL語句, 回傳類型是boolean, 若回傳的是更新的數目, 就回傳false, 若是ResultSet, 則回傳true

PreparedStatement

相較於Statement, PreparedStatement是一種pre-compiled語句, 可以使用預留位置("?")替SQL帶入變數值, 講白一點, 就是允許你使用參數化查詢.

PreparedStatement基本上是java.sql package下的一個介面, 用來執行SQL語句, 常見的使用起手式如下:

public class PreparedStatementDemo {
    public static void main(String args[]) throws SQLException {
        String queryBillingType = "select distinct billing_type from billing where bank = ?";
        Connection conn = DriverManager.getConnection("mysql:\\localhost:3303", "admin", "admin");
        PreparedStatement preparedStatement = conn.prepareStatement(queryBillingType);

        preparedStatement.setString(1, "Citibank");
        ResultSet result = preparedStatement.executeQuery();

        while(result.next()){
            System.out.println("Billing Type: " + result.getString("billing_type"));
        }       
    }
}

- 透過DriverManager取得connection
- 透過呼叫connection.preparedStatement(sql)獲得PreparedStatement物件
- DB會對sql進行pre-compile處理(前提是JDBC要支援), 之後這個PreparedStatement被預先編譯好後, 就可以在未來的查詢中重複利用了, 這在需要多次查詢的時候, 基本上是比Statement物件生成的的查詢速度要來得快的, 以下是一個簡單的範例:
- 在上面的範例中, 若還是使用PreparedStatement進行同樣的查詢, 儘管參數值不同, 譬如帶入其他銀行的名稱作為參數值, DB還是會去呼叫之前compiler已經compile過的執行語句
基本上, 在實際工作場合中, 應該都是使用PreparedStatement的場合多於Statement, 以下是PreparedStatement的一些優勢:
- 動態參數化查詢: 可以使用帶參數的sql語句, 通過使用相同的sql搭配不同的參數來做查詢, 總比建立一個不同的語句要來得好. 這個部分有另一個名詞叫做"同構(isomorphism)", 基本上PreparedStatement也是在解決同構問題.
- 比Statement快: 因為PreparedStatement會被pre-compile在DBMS中, 使用時一般來說會比普通的查詢更快, 做的工作也更少(因為DB對這個語句的分析/編譯/最佳化已經在第一次查詢之前就完成了). 這也是為什麼在production環境中, 使用PreparedStatement會比Statement更好的原因之一 --- 不要讓你的DB不開心或是有太多額外的負擔, 不然你遲早會倒大楣.
- 另外要注意的一點就是: 為了獲得性能上的優勢, 應該盡量使用參數化sql而不是使用字串連接的方式, 以下是兩個範例:
  - SQL1: 用字串連接的方式
    String loanType = getLoanType(); PreparedStatement prestmt = conn.prepareStatement("select banks from billing where billing_type = " + billingType);
  - SQL2: 參數化sql
    PreparedStatement prestmt = conn.prepareStatement("select banks from billing where billing_type = ?"); prestmt.setString(1, billingType);
- 可以防止SQL Injection攻擊:
  - 以下是一個很老梗的case, 某個網站驗證登入的SQL可能長這副樣子:
  - sql = "SELECT * FROM users WHERE name = '" + userName + "' and password = '"+ passWord +"';";
  - 然後故意填入這些東西:
  - userName = "1' OR '1' = '1"; passWord = "1' OR '1' = '1";
  - 這樣sql就會變成:
  - sql = "SELECT * FROM users WHERE name = '1' OR '1'='1' and password = '1' OR '1'='1';";
  - 這表示where條件恆為true, 就等於下了這條:
  - sql = "SELECT * FROM users";
  - 這樣就可能可以做到無帳密登入, 或是人家更壞一點, 在裡面綁上"DROP TABLE xxx"之類的東西...
  - 不過, 在使用PreparedStatement的參數化查詢時, 基本上可以避免大多數的SQL Injection. 在使用參數化查詢的情況下, DB不會將參數視為SQL指令的一部分來處理, 而是會於DB對SQL的compile結束後, 才套用參數運作, 故儘管含有破壞性指令, 也不會被DB執行.
  - 另外要補充的是, 前面那個塞入惡意指令的方式(包含單引號字元的那個), 可以透過字元取代來避免攻擊, 譬如:
  - sql = "SELECT * FROM users WHERE name = '" + userName + "';";
  - 傳入的字串可能是:
  - userName = " 1' OR 1=1";
  - 然後我們可以把一個單引號替換成兩個單引號:
  - userName = " 1'' OR 1=1";
  - 結果就會變這樣:
  - sql = "SELECT * FROM users WHERE name = '1'' OR 1=1';";
  - 所以DB會去找name = "1'' OR 1=1"的紀錄, 從而避免了攻擊.
- 比起散亂的字串連結查詢, PreparedStatement可讀性更高, 也比較安全.
雖然這樣看起來, PreparedStatement好像很不錯, 但其還是有一定的限制存在, 例如為了防止SQL Injection, 其不允許一個"?"參數裡包含多個值, 若你的查詢子句裡面使用到了"in(xxx,xxx...)"這種東西的話, 就有點麻煩了, 譬如下面這段SQL就不會回傳結果:
- sql = "SELECT * FROM billing WHERE billing_type IN (?)"; preparedSatement.setString(1, "'credit card', 'cash', 'apple pay'");
- 解法可以參考這篇: 點我
PreparedStatement跟java.sql.Connection是相關聯的, 記得用完PreparedStatement之後再關閉connection, 不然PreparedStatement就不能用了.
預留位置"?"的索引位置是從1開始不是從0開始, 你如果寫0會拋出"java.sql.SQLException invalid column index"這種exception.

CallableStatement

主要用於Stored Procedure

Previous4.10 HashMap v.s. HashTable Next4.12 Overflow of Digits

Last updated 4 years ago